By Tony Marasco

Precision.BI has been running at many sites on SQL Server 2000 successfully for many years. While Precision.BI continues to support SQL Server 2000 and it will continue to run on your existing hardware, some aspects of upgrading to a newer release of your database engine should be considered.

Microsoft Lifecycle
The Microsoft Website details the lifecycle of SQL Server 2000. As of April 8, 2008, “Mainstream Support” of SQL Server 2000 ended. SQL Server 2000 is considered in “Extended Support”. As of April 9, 2013, “Extended Support” of SQL Server 2000 ends. With regard to service packs, SQL Server Service Pack 4 is the last service pack to be released by Microsoft at the time of this writing.

The differences between “Mainstream Support” and “Extended Support” are as follows:

• Non-security hotfixes are available from Microsoft but you must purchase an extended hotfix agreement within 90 days of mainstream support ending.
• Incidents reported to Microsoft will incur a charge from Microsoft.
• Warranty claims are not available.
• No additional design changes will be applied nor will feature requests be accepted.

For more information on the Microsoft Lifecycle policy for SQL Server 2000, please see the external link here: http://support.microsoft.com/lifecycle/?LN=en-us&x=9&y=8&p1=2852.

Enhancements from SQL Server 2000 – Overview and Memory

Many enhancements have been added to SQL Server 2005 and SQL Server 2008 and several of these enhancements target business intelligence. One of these enhancements is indexing with respect to Precision.BI performance.

SQL Server 2000 Standard Edition allowed up to 2 GB of memory to be accessed. To access more memory in your SQL Server installation in a SQL Server 2000 environment, you must upgrade to SQL Server Enterprise Edition. With SQL Server 2005 Standard Edition and higher, SQL Server’s memory limitation is the amount of memory your operating system may access. This is a major benefit and cost savings, since SQL Server Enterprise Edition is very costly.

Enhancements from SQL Server 2000 - Indexing

Let us define the concept of indexing in a database. Indexing improves performance on data queries by using pre-sorted views of your data to optimize searching. Without indexing, SQL Server would be forced to perform an operation known as a table scan. An analogy of indexing is searching for the word “database” in the dictionary and using your knowledge of the alphabet to quickly locate the word in question. An analogy of a table scan would be using the same dictionary to find all the words that have a definition of “happy.” Every definition in the dictionary would be read – this takes a long time.

SQL Server 2005 improves indexing greatly over SQL Server 2000 through better use of indexes, index intersection (using multiple indexes on the same table in the same query), and the INCLUDE clause. In many cases, Precision.BI objects access similar data with similar queries – each index requires additional disk space consumption. In SQL Server 2000, each ad hoc query will need an index on every field used for optimal results. In addition, SQL Server 2000 is limited to sixteen fields in total. With SQL Server 2005, indexes are still limited to sixteen fields but may include over 1,000 data fields in the INCLUDE clause. This concept allows for reuse of optimally configured indexes as well as inclusion of additional fields from a new Precision.BI object.

Next Steps

Please note that an upgrade from SQL Server 2000 may require optimization by Precision.BI based on differences between the SQL Server releases. While not required, the service of optimizing and upgrading your indexes will provide optimal performance on your new SQL Server installation.
When your site is ready to upgrade to SQL Server 2005 or SQL Server 2008, please contact us at customer.care@precisionbi.com. We will discuss server specifications (if any changes are recommended) and develop a customized plan for your site to complete the project.