Captools/net Documentation

SQL Database

SQL Database

Previous topic Next topic  

SQL Database

Previous topic Next topic  

Captools/net data is stored in an SQL database.  In the "default" Captools/net installation, the database is driven by the Microsoft MS SQL-Express Database Engine.   MS SQL-Express provides the core functionality of Microsoft's SQL Server, but contains some limitations, such as use of only one CPU (can still run on multi-CPU system), 4 Gb total database size limit, maximum RAM use of 1GB (effective max table size limit), and requirement that the database and MS SQL-Express reside on the same computer.   The chief advantage of MS SQL-Express to the end user is that it is a Microsoft "re-distributable" application, meaning that there is no additional software cost to the end user to run MS SQL-Express with Captools/net.  Both the MS SQL-Express and MS-SQL Server implementations of the database are ODBC compliant.

 

Captools/net users whose database files approach or exceed 1GB in size (combined MDU and LOG file size) or who note that the "SQLserver.exe" process on their Windows Task Manager is consuming more than 1 GB of memory may find that they will need to upgrade their installation the Microsoft SQL Server application to MS-SQL Server "Workgroup" version or higher.  MS-SQL Server "Workgroup" and higher editions employ the same or similar database engine as MS SQL-Express, but without the MS SQL-Express data size limitation or requirement that the database engine and database files reside on the same computer.  This means that these versions of SQL Server can handle larger amounts of data.  Higher level MS-SQL Server versions also provide a number of useful database management tools that an I.T. department will find useful in managing the database.  Higher levels of MS-SQL Server do require that separate Microsoft licensing must be purchase to install and operate it.  More feature comparisons between MS-SQL Express vs other MS-SQL Server versions are described at the Microsoft website (click here).

 

Upgrading from MS SQL-Express to MS-SQL Server - The MS SQL-Express data size limits should not be of practical consequence for many Captools/net users.  We estimate that MS SQL-Express will typically be able to support up to 300 account portfolios at typical portfolio activity (20 securities/account) and valuation frequency.  However, over time, the need to support more users and/or accounts, or the desire for better database performance may indicate that you should upgrade to MS-SQL Server "Standard" edition or higher.  The MS-SQL Server "Standard" version (or higher versions) can be obtained at the Microsoft Store, or from a variety of third party resellers searchable on the web. Important Note: If you upgrade, be sure you are running a 64-bit version of Windows and also get a 64-bit version of SQL Server. Also, in general, a version with a Client Access License ("CAL") limitation will be the most cost-effective for most Captools/net users.

 

Since the MS SQL-Express database structures is compatible with the higher versions, upgrading is a relatively easy process, simply involving installing the higher level MS-SQL Server and linking it to your existing Captools/net database files. Be sure that  the version you are upgrading to is the same or later than the one you are upgrading from, e.g. upgrading from SQL Express 2008 to SQL Server 2012 "Standard" is "OK", but you cannot upgrade from SQL Server Express 2012 to SQL Server 2008 "Standard".  If installing on a new computer, these database files may be moved from your old system to your new system.

 

Compacting Database Files - If your MS SQL-Express database files are approaching their size limits, some additional room to the limits may be achieved by compacting your MS SQL-Express files.  This is possible because the MS SQL-Express database can allocate space which counts towards the limits even though it is unused.  Compacting can eliminate much of this space, providing you with additional room to the limits.  This process may take some time and will preclude users from accessing the data during the consolidation process, so you should perform the consolidation at a time when you can afford to have the system inaccessible. Frequent prompts for consolidation indicate it is time to move to the MS-SQL Server version of the database.