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.