Home > Articles > Databases > SQL Server

SQL Server Reference Guide

< Back Page 32 of 361 Next >

Upgrading SQL Server

Last updated Mar 28, 2003.

A data professional is probably one of the most cautious information technology workers there is. We know that our systems “live” for quite a long time — the platform they run (like SQL Server) are not upgraded every month, year or even every five years. We know that when the database server is down, it affects not only one or two users or applications, but potentially dozens, hundreds or even thousands of them.

So it follows that with an older system and lots of folks depending on it, a data professional shouldn’t just slide the DVD of a new version of SQL Server and type “Setup”. There’s a bit more work that we need to do before we decide to take the plunge and upgrade an Instance of SQL Server.

Faced with a new version of SQL Server, you should evaluate whether it makes sense for your organization and for that Instance of SQL Server in particular. In every upgrade to the product, SQL Server has more security enhancements and features, better performance and other features that you may want to implement. Depending on your licensing model, you may be able to upgrade your software without additional costs, or it may take more money to legally own the product.

Once you’ve determined that you want to upgrade your system, there are a few steps you should evaluate for the process. Even with the tools provided by Microsoft (which you should run) there are things you should do to make the upgrade successful. I break the steps down into six general phases of operation.

First Phase: System Review

I start by reviewing the documentation from Microsoft for the upgrade. I do this when the product comes out, even before I decide to upgrade or not. This is a data-point that helps me understand what would be involved — for instance, a particular hardware setup may not be supported any more, or perhaps a feature I’m using is no longer supported. In that case, knowing the documentation can help me to plan to buy new hardware or change over the technology I’m using to another strategy.

After I’ve read the upgrade documentation from Microsoft, I’ll often do a quick web search to see if anyone has documented their experiences from the same kind of upgrade. I’ll hit the social media sites (like FaceBook, Twitter or LinkedIn) and ask if anyone there has had any experiences with that situation.

From there, I take a look at my documentation for the SQL Server Instance. I’ve explained how to do a SQL Server “Application Profile” before, and that’s what I use here. This also tells me who is on my system, and who will be affected by the upgrade.

I develop an estimate of the time I think it will take to perform the upgrade, usually building in all of the steps I list out here. That’s what I communicate to my users, unless my system is using Database Mirroring, Log Shipping or a Cluster, in which case I might be able to do a non-downtime upgrade — but that’s another tutorial.

Next, I download and run the SQL Server Upgrade Advisor. This is a tool that has been around since SQL Server 2005, and it’s essential that you run it to gain confidence in the upgrade. It’s a fantastic tool, and I’ll reference the link for it at the end of this article.

Second Phase: Back-Out Strategy

With all of the information in hand and my users notified, I next focus on what happens if things don’t go as planned. After all, the data professional is a nervous soul with a focus on being paranoid, so we expect failure — and are thrilled when things actually work.

To that end, I start with a complete database backup for every database — I don’t rely on last night’s backup. If I can, I stop the applications and take the backups with nobody on the system. I backup all of the system databases, meta-data databases and user databases completely, and I verify the backups. This is my primary security if the upgrade just destroys the system, which of course is a pretty remote possibility. But I’ve had systems from mainframes to microcomputers and every platform from DB/2 to Oracle and SQL Server give me issues on an upgrade, although as time goes on that happens less and less.

There are times when this isn’t possible, or necessary. Once again, if you’re using a Database Mirror or Cluster, you may be able to perform the upgrade with little or no downtime. You might have a database so large that backing it up completely would take longer than you have available for the upgrade. In those cases, more planning and coordination is probably needed.

I also like to have a complete Operating System backup when and where I can. Sometimes that’s not possible, such as when the system is used for more than just SQL Server, or when that would lengthen the window that you need to do the upgrade. The reason I do like to have this is that in many cases the upgrade to SQL Server involves an upgrade to the Operating System as well, so I like to have a really good fallback. In some shops I’ve been able to take a “bare metal” backup, which is kind of a snapshot of the hard drive in a certain state. Love those. This is also a great place for Virtual Machine technology — a simple click and you’ve got a “snapshot” of the system to fall back to.

The point here is to think through (before you touch anything) what you would need to do to get the system back into the shape it is before you start. Taking the time to have a good plan here is essential. If things go south you can fix it, and you can give your nervous users an estimate of when your system will be available.

Third Phase: Perform the Upgrade

Users know what is going on, I’ve learned what I need to know, and I’ve made the system as safe as possible. I’m ready to make changes to my system.

Based on what I learned from the documentation and the Upgrade Advisor, I may or may not need to make changes in the Operating System such as patches, settings and so on. I make those next. Sometimes the upgrade process downloads and applies those automatically, which is another reason I like that OS backup.

Now I run the upgrade process, normally by just sliding the DVD in the drive and running the Autoplay feature, or by clicking on Setup. I watch the process, and note any messages. I’ll review those in a moment.

If there are any issues, the system normally rolls back pretty well. But just in case, I have all of those backups ready to restore.

Fourth Phase: Configure New Features

Just after the upgrade completes, I start the system and ensure everything comes up properly. I’ll evaluate that more fully in a moment, but I still have some changes to make.

The new version probably introduces features that need to be configured, from an operational or security standpoint. Based on the documentation I’ve read, I’ll know what those are. This is the time to make those changes — if I test first, and then make changes to the system I’ve negated the test. So I have to configure everything here first.

I normally also run maintenance again here. I don’t take a backup, since none of the data has changed, but I do run the standard DBCC commands and I update the statistics on the indexes. This last step isn’t always necessary, but I do it anyway. This is also a kind of test — it ensures that whatever maintenance I have still works.

Fifth Phase: Test and Verify

Now I can fully test the system. I start with a reboot, to ensure everything comes up. Again, this is only needed if I don’t have Mirroring or Clustering or some other in-place strategy.

After that I review the Windows Event logs, looking for anything out of the ordinary.

SQL Server setups and upgrades always create a log file of their actions. At the end of the setup and upgrade program the log locations are displayed, as well as in the documentation I read in the first step. I review those to ensure the upgrade didn’t report anything unusual.

I then check the SQL Server Error Logs, and look for anything unusual there.

After all my checks are made, I allow a small group of users back on the system and have them check the “main” parts of the software packages that run against the database. Depending on how critical and complex the database is, this might be a very formal set of tests or a series of major steps in the applications.

As a final check, I make sure I can get back in to the system with the Dedicated Administration Connection, or DAC. I have the system isolated, so it’s a great time to test that this still works.

Sixth Phase: Return to Production

Now I let everyone back on to the system, and stay close by for monitoring.

Speaking of monitoring, when the users come back online I start my baseline monitoring to compare against my earlier numbers — with any luck, the new version should be faster, and I should be able to prove that out here. If you do this as well, make sure you let the system run for a while with the users on before you monitor so that the cache buffers are all primed up.

Following this thought process will help you develop your own list of things to consider — again, you configuration will be different, probably for each server you upgrade. But thinking it through will help you have a successful upgrade.