Real World DBA Episode 6 – Upgrading SQL Server 

This week:

In the news, Microsoft updates SQL Server Webcasts, in this week’s feature, we’ll cover the processes and procedures you need to follow for an upgrade.

The web link shows you where to find a graphic of the system tables in SQL Server 2005, and the tip of the week is on object dependencies.


News this week:

Microsoft has posted several new webcasts where you can learn more about SQL Server 2005 and 2008, as well as other Server applications. Find the updated lists at 



Performing an upgrade is actually much harder than performing an installation. In this Podcast, I’ll cover a few of the major items you need to think about when you’re upgrading SQL Server. I’ll focus on upgrading SQL Server 2000 to 2005, but there are broader concepts here that are generic to any upgrade, even from 2005 to the upcoming version of 2008.


First, it’s best to create an upgrade plan. This is a document, paper or electronic, that describes what you plan to do. Write down all the steps you’ll take, the tools you’ll need, and the people that need to be involved. After you listen to this podcast, you’ll have a better idea of what you need to write down, but the point is that thinking the process through first is essential. The more you think about your plan, the smoother the upgrade will go.


Next, just like in an installation, it’s important to read the documentation. The link for that is, and just towards the bottom there you’ll see the link for the upgrade. The documentation is decision-based, so you’ll only have to read the parts that apply to the choices you make.


You’ll need to decide whether you want a side-by-side installation, called a “Migration”, or an in-place upgrade. There are reasons for each approach. A migration, where you install the new version of SQL Server either on the same hardware or on a new system, has the advantage of safety, more verification through testing, and less downtime for the users. Of course, if you have a huge set of databases, or if the logic of the system locks the name of the systems in place, or if you have only one system and not a lot of space, you may want to upgrade a box in place. That means you take the system from one version to another, replacing everything along the way.


Something to note is that in any case, Clustering makes a difference. If your system is clustered, you want to take special note of the documentation where it calls that out. You have a specific order you need to follow, and other issues you need to deal with along the way. You should also check the information there on editions and operating systems. The general rule is that you can usually move from a lower version to a higher, but again there may be issues when you move from one to the other. In SQL Server 2000, you can’t move from 32-bit to 64-bit as an upgrade – that’s a side-by-side installation.


While you’re planning the upgrade, check the hardware and software requirements. You might find that the same hardware and operating system that runs a lower version isn’t adequate for the next release – or you might find that the hardware is just fine. Check the documentation and update your plan accordingly, making sure that you add the steps and people needed for those changes. Also adjust the time to include these tasks.


Next you’ll want to check your applications and features. In Books Online you can read about what features in SQL Server have been changed and how, and other features that have been deprecated and are no longer included. A notable example in SQL Server 2000 to 2005 is the removal of Data Transformation Services, or DTS. This has been replaced with SQL Server Integration Services, or SSIS, although many DTS packages can be upgraded and actually run in the newer version. Along with the feature testing, you should test your applications to make sure that all of the old code runs correctly in the new version. SQL Server 2005 Profiler actually includes a trace template that will show statements as they run that are deprecated, which is a great use for a test system.


To help you Run the Upgrade Advisor Tool. This is a wizard-based, read-only based tool that you can run (with about a 10% performance impact) on your production servers. That’s right, you can run it on the production server while it is in use. It won’t change anything, and you can run it multiple times. It will create a report for you that will look at live operations, script files if you want, or even a SQL Server 2000 Profiler Trace.  It’s a great tool and one you should run before you actually start your upgrade operation.


Now that you’ve run the tools, created the plan and read the documentation, you’re ready to get started.


The very first thing you need to do is stop the activity on the server and take a complete backup. That means one set of backups for each database, including the system databases, and then stop all of the SQL Server Services and take a complete Windows backup as well. Trust me, you won’t regret the time you take to do this.


Now start the upgrade program from the media. Follow the steps you developed in your plan, and check each step along the way. When you’re done, Update all the statistics on the databases, check all the logs, and run a few tests. Allow a set number of users back into the system, and have them perform some known tests to make sure everything is there from the latest point in time from before the upgrade, and have them test as many core functions as possible.


Now you’re able to let everyone back into the pool. Stay sharp, and watch the system as the users use it over the first production day after the upgrade. Put this into your plan as well for people and materials.


OK – there’s a quick rundown of an upgrade. The keys are to plan, plan, plan, and have lots of fallbacks along the way. With a good system backup, you’re never more than a few hours of putting the system back, and that buys a lot of peace of mind as you go. 


Web Link:

Microsoft has created a full map of the system views, suitable for framing – OK, maybe not, but it is a useful graphical reference. You can find it at



Tip of the Week:

SQL Server Management Studio can show you when objects are dependent on each other, meaning that they have a reference to other objects. You should always check these dependencies before you drop or alter an object. There are several ways to see these dependencies from code, and one of the easiest to use is the sp_depends stored procedure. To try it out using the AdventureWorks sample database, type the following in a query:  EXEC sp_depends 'Person.Contact';.


Skip to main content