SQL Server 2000 was a watershed release for Microsoft. It was in this version that SQL Server first became an enterprise-caliber database server.
As testimony to the solidity of the product, eleven years after its release and almost a year after Microsoft ended all support for SQL 2000, there are a very large number of SQL Server sites that still use it on some or all of their SQL Servers. However, for those who have not upgraded already, we believe it is important to do soon.
Upgrade to What?
Normally we recommend migrating to the most recent edition of SQL Server. In this way you put off your next upgrade for the longest possible time. However, in this case it might make sense to upgrade now to SQL Server 2008 R2 rather than wait for the next version, code name Denali, due sometime later this year.
The upgrade from SQL Server 2000 to Denali will be significantly more difficult than an upgrade to SQL Server 2008 R2. If you don’t currently have the time or budget for the more difficult upgrade, there are reasons why you might consider upgrading now to SQL Server 2008 R2 and leaving Denali until later.
What Do You Mean “significantly more difficult?”
Denali will not support moving SQL 2000 databases directly to the new version. You will need to upgrade first to SQL 2005 or 2008 then move the upgraded databases to Denali. If your company does not have a license for either of those versions, you might have to buy one just to upgrade the databases so they can be restored on Denali or some future version.
Data Transformation Services (DTS)
There will be NO support for DTS in Denali. This means that there will be no way to import DTS packages as you can now do (with some restrictions) in SQL 2005 and 2008.
Upgrading to SQL 2008 R2 now rather than migrating to Denali will give you a few years on a supported, high performance platform before you have to tackle the DTS conversion.
Eventually you will have to make the transition. Even DTS packages already imported into SQL 2005 or 2008 will have to be re-written as Integration Services packages before a SQL Server can be upgraded to Denali or some later version.
What Will I Miss By Not Upgrading to Denali?
Denali brings some significant changes to SQL Server. For example, the development environment is closely integrated with the new Windows Presentation Foundation (WPF). If you are going to embark on new development projects, you probably want to get on Denali.
However, most SQL 2000 upgrades I have seen involve legacy databases and legacy applications. These will not need much of what is new in Denali. And much of what is new is only available in Enterprise Edition. If you are targeting the Standard Edition, there is little that seems necessary for a typical SQL 2000 migration.
Jumping from SQL 2000 to 2008 is probably enough of a change to absorb at one time anyway.
Specifications for Denali are still evolving, but you might do a web search and find out a bit more about what the Enterprise and Standard editions contain for you
In what follows, we will discuss reasons to upgrade and ways to do it with little risk and significant cost savings, leaving to you the choice of the version to which you wish to upgrade.
Why Upgrade Now?
There will never be an easier time to upgrade your SQL Server. In fact, it will become much more difficult with the eventual disappearance of SQL Server 2008 R2 as an option.
Upgrade for Better Performance
There are many performance-related reasons to upgrade from SQL 2000. One of the most important performance advantages is the huge increase in the amount of memory available in Standard Edition.
SQL 2000 Standard Edition is limited to using no more than 2 GB of memory. If you upgrade to later versions of Standard Edition, you are allowed up to 32 GB of memory. This can make a HUGE difference in performance for your applications.
With SQL Server 2000, many organizations were forced to purchase the more expensive SQL 2000 Enterprise Edition to be free of this memory limitation. Some now find that they are able to downscale to the less expensive Standard Edition in later versions and still get dramatically better performance.
Remember however that this increased allowance of memory is only on x64 versions of Standard Edition. (A very good reason to upgrade your 32 bit hardware.)
Do You Still Need Enterprise Edition?
There are quite a number of features in the current Enterprise Edition that are not in Standard Edition. However, in my experience there are only three must- have features that commonly cause an organization to pay the considerable difference in price between Standard and Enterprise. They are:
- On-line indexing. This is a necessity for 24×7 applications that do not have a maintenance window to rebuild indexes. It allows you to rebuild indexes without seriously affecting performance for users on the system.
- Table partitioning. This is an important performance feature for databases with large, active tables.
- Transparent Data Encryption. Available only in SQL Server 2008 and above, this form of encryption requires no application changes and encrypts the entire database. Many regulatory standards like HIPAA, PCI, etc. are moving toward a hard requirement for data encryption and this is often the encryption method of choice.
If these features are not of critical importance to you, there is a very good chance that SQL Server Standard will be adequate for your needs.
Upgrade Considerations
When you upgrade, you are well-advised to move to a new hardware platform at the same time. This is a good idea for a number of reasons. The first is that there is much less risk in an upgrade on new hardware.
If you choose to upgrade in place on your old hardware, the upgrade will overwrite your production database server. If something goes wrong or if an application incompatibility is discovered too late, there is no simple rollback path.
On new hardware you have the luxury of testing your applications and practicing the cut-over process as often as necessary. You can restore or attach copies of your production databases to the new hardware platform with the new version of SQL Server installed. When you do, the copies of the databases will be upgraded successfully or else you will find out immediately if any databases have issues that cause the upgrade to fail.
You are free to import databases to the new machine as often as necessary and test them.
You can do this without affecting your production environment at all.
When you are ready to go live, you can restore the current versions of the databases to the new SQL Server one last time and shut down the old server. After this careful testing, it is much less likely that something will go wrong, but if it does you simply revert to the old server with little or no impact on your users.
Save Money on SQL Licensing
The first step in licensing is to decide whether you need the Enterprise Edition. It is much more expensive than it was when you purchased your SQL 2000 licenses. Under the per-processor license model the list price for SQL Server 2008 R2 Enterprise is $27,495 per processor as compared to $7,171 for Standard Edition.
Microsoft Licenses Sockets, Not Cores
SQL Server can be licensed by individual user license (CAL) or by the number of processors. Most large companies and all organizations using SQL Server to power a web site use the per-processor licensing mode.
If you are licensing per-processor it is important to understand that SQL Server is licensed by the number of populated processor sockets in the server regardless of the number of cores in each processor. This is a tremendous bargain that can often save you enough on licensing to pay for the new hardware you need.
When selecting hardware you should look for x64 hardware that supports the most number of cores per socket. For example, if your hardware has a single 8-core processor, you will need only one SQL Server license to get the benefit of 8 processors. By comparison, Oracle on the same machine would require 8 licenses.
Conclusion
Nobody wants to upgrade their database platform unnecessarily. It is expensive, risky and there is always a learning curve for the new edition. However, if you are still using SQL Server 2000 we have pointed out some compelling reasons to upgrade now.
One reason that we haven’t mentioned is the question of due diligence. Your data is currently riding on a 10 year old, completely unsupported database platform. Even if that platform still seems solid to you, it may appear differently to those who require compliance to standards like HIPAA, PCI, Sarbanes-Oxley, etc.
As always, if you have questions or comments about anything in this article, you are welcome to call or email me. We have done a number of SQL Server 2000 migrations recently and I will be happy to share what we have learned.