Every time I have to perform some major work on my production SQL Server clusters, I spend a lot of time researching and planning the task at hand. And when it comes to something as big as an upgrade from a SQL Server 7.0 Cluster to a SQL Server 2000 cluster, I even spend more time than usual. I can’t afford for mistakes on a server that needs to be up 24/7.
The purpose of this article is to explain how we recently upgraded a production SQL Server 7.0 cluster to a SQL Server 2000 cluster. Some, but not all that you read here, can be found in the SQL Server Books Online. If you need to perform an upgrade like we did, I suggest that you read everything you can find on this topic before you begin.
Why We Upgraded
If its not broke, why fix it? Ever since we put our SQL Server 7.0 cluster into production, we have not had any major problems. The cluster performed well. In many ways, there was no immediate reason why we needed to upgrade. But we did for a variety of reasons:
- Microsoft highly recommends it. No, I don’t always take every bit of advice that Microsoft suggests. In fact, I am kind of a skeptic. But after administrating both SQL Server 7.0 and SQL Server 2000 clusters (we have more than one), it was apparent that SQL Server 2000 clustering is more robust and easier overall to maintain.
- SQL Server 2000 offers some performance benefits over SQL Server 7.0, although they are not overwhelming in our particular situation.
- We had already moved virtually all SQL Server 2000 server in the company and we wanted to be consistent for ease of administration.
- We would have to eventually upgrade anyway, so now was as good a time as any.
None of these are compelling reasons on their own to upgrade, but all told, we decided it was the right time to make the move.
Deciding How to Perform the Upgrade
Of all the steps I took to perform the upgrade, the decision on how to best perform the upgrade took the most time and effort. Essentially, here are the options we considered:
- Purchase new cluster hardware, then copy (while upgrading) the databases from the SQL Server 7.0 cluster to the SQL Server 2000 cluster using the Copy Database Wizard. This option provides several benefits: 1) We would get new, faster hardware, 2) I would be able to install and test the software extensively before moving (and upgrading) the databases, and 3) Should the upgrade fail, the original cluster would still be working and we could easily fall back to it if necessary.
- Upgrade the SQL Server 7.0 cluster, in-place, to SQL Server 2000. This option has the advantage of not requiring new hardware. But its one major disadvantage is that if there is a problem with the upgrade and it does not work, then we would not have a backup cluster to take over. Essentially, a problem arising from the upgrade procedure would most likely require that the cluster be rebuilt from scratch, which could result in a lot of down time.
- First, break the cluster and remove SQL Server 7.0 from the second node of the cluster, but not the first. Second, install SQL Server 2000 on the second node. Third, copy the databases from the SQL Server 7.0 node to the SQL Server 2000 node using the Copy Database Wizard and test to see if the upgrade went correctly. If it did not, we could then fall back to the first node (although it would not be clustered, but it could be quickly brought back up). If the upgrade went correctly, then we would, fourth, remove SQL Server 2000 from the second node (but leaving the upgraded databases intact), then upgrade the first node from SQL Server 7.0 to SQL Server 2000 (while installing it in a clustered mode), then move the already upgraded databases from the second node to the newly clustered SQL Server 2000 cluster. While this option did reduce the risk some of the upgrade (we had a node to fall back on quickly), this option would be very time consuming and because of the many steps, prone to one or more mistakes.
Did I mention that I was only given a four hour window to complete the upgrade?
Given that the database was mission critical and had to be up 24/7, and that I was only given four hours to perform the upgrade, the first option made the most sense. I fact, it was my first choice. Unfortunately, it wasn’t the first choice of those who control the purse strings, so I had to choose from the last two choice above. Right away, I hated the third choice. It was much too complicated and would take too much time. That left me with the second option, which was the in-place upgrade.
Actually, in-place upgrades are great, if they work. They are probably the easiest and the fastest to perform, but a failure in any step means that you most likely would have to rebuild the cluster from scratch, which means a lot of unexpected down time. So my strategy was to reduce the risk of failure as much as I could.
Preliminary Steps
In order to reduce the potential failure of an in-place upgrade from a SQL Server 7.0 cluster to a SQL Server 2000 cluster, I began my research. I started by finding all the information I could find from Books Online and Microsoft’s website. While I found some pertinent data, it wasn’t a lot. Next, I called Microsoft support, asking their advice on what kinds of issues I might run across during such an upgrade: They told me that there were essentially two problems that people run across in an upgrade like this:
- First, the cluster hardware, the operating system (in this case, Windows 2000 Advanced Server, SP2), and the clustering service must all be working at 100%. In other words, if I was having any issues, any at all, I needed to correct them before proceeding with the upgrade. This meant checking all the logs, double-ensuring that there were no anomalies. Fortunately, there were none. As I mentioned previously, this cluster had been very reliable.
- Second, the database itself must be working 100%. There should not be any DBCC errors of any kind (after running the standard DBCC commands to check the database’s health), and no data issues of any kind. I verified this by running the appropriate DBCC commands) and everything looked great.
I was told by Microsoft that if the two above points were true, then the upgrade should go smoothly. (Where have I heard that before?) Well, at least this was good to hear, even if I didn’t believe it 100% myself.
Given what Microsoft told me, and based on my own experience, here’s what I did before performing the in-place upgrade in order to reduce the risk of problems to the lowest I could:
- I double-checked that all of the hardware (and their drivers) were on Microsoft’s Hardware Compatibility list for SQL Server 2000 clustering.
- I double-checked all log files, looking for potential problems of any kind.
- I took a copies of my most recent database backups on the SQL Server 7.0 cluster to be upgraded and restored them on another SQL Server 7.0 test server. I then ran all the appropriate DBCC commands on this restore. I did this for two reasons. First, to test my backups to be sure they worked successfully. In a worst case scenario I wanted to ensure that I could successfully recover my databases. And second, I didn’t want to run the DBCC commands on the production server as it would present too much of a load on that server, negatively affecting performance.
- While I didn’t have a test SQL Server 7.0 cluster available, I did have a test SQL Server 7.0 server (non-clustered) available. So after performing the previous step, I did an in-place upgrade of the SQL Server 7.0 test server databases to SQL Server 2000. While this wasn’t a complete test, it would be the closest I could get to the real conversion. Everything worked fine, which was reassuring.
After completing all of the above, I was ready for the actual upgrade.
Performing the Conversion
Here are the steps I followed to perform the actual in-place upgrade from a SQL Server 7.0 cluster to a SQL Server 2000 cluster.
- Prevent access by users to the SQL Server 7.0 cluster.
- Perform a full backup of each of the databases on the server to be upgraded. For best protection, these backups should be made to a server over the network, or to tape.
- Perform a test failover and failback of the nodes one last time before the upgrade to see if there are any cluster-related issues. Check the error logs to be sure there are no unexpected issues. Be sure that the node used to originally install SQL Server 7.0 clustering is the primary node after you have successfully performed your testing.
Once these preliminary steps were completed, the first major step of the in-place upgrade is to remove SQL Server 7.0 clustering from the current cluster. Here are the steps.
- Log into the primary node of the SQL Server 7.0 cluster using the account and password used for the mssqlserver service. (Recommended by Microsoft.)
- Start the SQL Server 7.0 Failover Cluster Wizard on the primary node of the cluster.
- Select the option to remove clustering.
- Once the SQL Server 7.0 Failover Cluster Wizard has competed removing the cluster, exit the Wizard.
- Reboot the primary node of the cluster. Then log into the primary node of the SQL Server 7.0 cluster using the account and password used for the mssqlserver service.
- Reboot the secondary node of the cluster. Wait until the secondary node fully restarts.
- Once the second node of the cluster has successfully rebooted, use Cluster Administrator on the primary node to verify that the primary node controls the resources of the Windows 2000 cluster. If all of the above steps have been successful, this will be the case.
Now that SQL Server 7.0 clustering has been removed from both nodes of the cluster, it is time to upgrade the SQL Server 7.0 files (they still remain on the primary node because only the clustering-related files were removed in the previous step).
In essence, in this step, you will be performing a stand-alone upgrade of SQL Server 7.0 to SQL Server 2000. Once this step is done, then you will convert the SQL Server 2000 stand-along version to a SQL Server 2000 clustered version. This section assumes that you are familiar with the general steps required to install SQL Server 2000.
- If you haven’t done so already, log into the primary node of the SQL Server 7.0 cluster using the account and password used for the mssqlserver service.
- From the SQL Server 2000 Enterprise CD, or from the local hard disk if the setup files are installed locally on drive C:, begin the SQL Server 2000 setup program.
- In the Welcome dialog box, click Next.
- When asked to select the SQL Server 2000 components to install, select all of the appropriate options for your unique situation.
- In the Computer Name dialog box, leave the defaults.
- In the Installation Selection dialog box, select “Upgrade, remove, or add components to an existing instance of SQL Server.”
- In the Instance Name dialog box, leave the defaults.
- In the Existing Installation dialog box, select “Upgrade your existing installation.”
- In the Upgrade dialog box, leave the defaults.
- In the Connect to Server dialog box, select the appropriate authentication mode.
- In the Start Copying Files dialog box, click Next. This starts the upgrade process. When done, you will be prompted to restart the computer. Go ahead and do so. As you might expect, this will cause the Windows 2000 clustering to fail over to the second node.
- Once the primary node of the cluster has successfully rebooted, log on using the mssqlserver service account and password, and then use Cluster Administrator to move the cluster resources from the secondary node back to the primary node.
At this point, SQL Server 7.0 has now been upgraded to SQL Server 2000. The next step is to add the SQL Server 2000 clustering support to the cluster, following these steps:
- If you have not done so already, log into the primary node of the SQL Server 2000 cluster using the account and password used for the mssqlserver service.
- From the SQL Server 2000 Enterprise CD, or from the local hard disk if the setup files are installed locally on drive C:, begin the SQL Server 2000 setup program.
- In the Welcome dialog box, click Next.
- In the Computer Name dialog box, select “Local Computer.”
- In the Installation dialog box, select “Upgrade, remove, or add components to an existing instance of SQL Server.”
- In the Existing Installation dialog box, select “Upgrade your existing installation to a clustered installation.”
- In the Virtual Server Name dialog box, enter the name you will be assigning to your virtual SQL Server cluster. In almost all cases, this will be the same virtual name you used for your SQL Server 7.0 cluster.
- In the Failover Clustering dialog box, enter the virtual IP address to be used for your virtual SQL Server cluster. In almost all cases, this will be the same virtual IP address you used for your SQL Server 7.0 cluster.
- In the Cluster Management dialog box, both the physical names of the nodes in your cluster should be selected to participate in the cluster. This is the default option, and should be left alone.
- In the Remote Information dialog box, enter the appropriate login credentials. Generally, this should be the name of the account and the password used for the mssqlserver service.
- In the Services Accounts dialog box, enter the name of the account and the password used for the SQL Server services. (Same as above, in most cases).
- In the Setup Completion screen, click Finish.
- Reboot the primary node of the cluster and let it restart fully.
- Reboot the secondary node of the cluster and let it restart fully.
Unfortunately, we are still not done, although we are close to being done. We still need to apply the latest SQL Server service pack to the SQL Server 2000 cluster. Here’s how:
- If you have not yet done so, get a copy of the latest SQL Server 2000 service pack and place it in a folder on drive C: of the primary node of the cluster.
- Start the setup.bat program to begin the service pack installation process.
- After clicking past the introductory screens, you will be asked to enter the virtual name of your SQL Server 2000 cluster.
- Next, you will be asked to select the appropriate authentication method.
- In the Remote Information dialog box, enter the appropriate login credentials. Generally, this should be the name of the account and the password used for the mssqlserver service.
- After this screen, the service pack installation process begins and updates both nodes of the SQL Server 2000 cluster.
- Reboot the primary node of the cluster and let it restart fully.
- Reboot the secondary node of the cluster and let it restart fully.
At this point, the upgrade is now complete. But before you put the server back into production, there are a few more steps you should perform.
Additional Final Steps
Performing this upgrade has been a significant event, and before you put your SQL Server 2000 cluster back into production, you should perform the following:
- It is very important to test that the newly upgrade SQL Server cluster can failover properly. This means that you should test your cluster, failing it back and forth several times to see that everything is working properly. For more advice on how to test a cluster, see this article.
- Take a look at the Event Logs to see if there are any unexpected messages. If there are, then you need to resolve them now before you release the server to production.
- Update all of the statistics in each of your databases. The upgrade process causes the current statistics to become invalid, and if you don’t update them now, performance could be very slow when you release the server to production. Sure, the statistics will eventually update themselves, but his is a slow process. You will want to budget some time for this as part of your upgrade plan.
The upgrade is complete, and assuming it went smoothly, you should now be able to return to production.
On the other hand, if you experience problems with the upgrade, you have several options. I would first recommend that you call Microsoft Support (for their fee-service) and ask them for help. But if you cannot do this (for whatever reason), I suggest you don’t spend too much time using trial and error to resolve your problems, as most likely, it will be a waste of time. Sure, spend enough trouble-shooting time to look for obvious, fixable problems. But if the problems you experience are unusual and don’t seem obvious, then the best way to spend your time is to rebuild your cluster from scratch. I know you hate to hear this, but based on my personal experience, once you begin to get clustering problems, this is the only real solution, unless you have an inside connection to Microsoft support. But if you plan well, as I recommend, the odds of having a major problem are slim, and your upgrade should go well.
First appeared at SQL-Server-Performance.com
Brad M. McGehee is a full-time DBA with a large manufacturing company, and the publisher of http://www.SQL-Server-Performance.Com, a website specializing in SQL Server performance tuning and clustering.
He is an MVP, MCSE+I, MCSD, and MCT (former).
Brad also runs another website called http://www.WorldClassGear.com It provides independent gear reviews for backpackers, trekkers, and adventure travelers.
