Post-Upgrade Steps

If you performed an upgrade (not a migration to another server), you need to run two checks on each system you have upgraded: a system configuration check and a security check. Work through the steps shown here even if you are certain everything worked properly.

Also, tune the system for performance after you have upgraded it, even if you migrated, restored, or attached the database from an older version to SQL Server 2005.

Whenever you upgrade, back up, or attach a database from an older version to SQL Server 2005, the databases are set to an earlier compatibility mode. This is a safe way to handle things, because arbitrarily making the changes to a higher version might break an application that depends on earlier features. To change the setting, it is a simple matter of opening the properties of the database in SQL Server Management Studio and setting it to 90.

System Configuration Check

Along with the databases, check the server settings within SQL Server Management Studio to make sure that the memory, CPU, and other settings are not just what they were, but are optimized for what you need now. See the Chapter 5, "Monitoring and Optimization," to learn more.

Another check to make is any target and linked server registrations your system has. They may still work, but you should run any queries that depend on them to make sure.

If you created any extended stored procedures in Windows 2000, make sure that they are instantiated with the fully qualified file path in the registration. Earlier versions of SQL Server allowed you to leave the path off as long as it could locate the dynamic link library (DLL), but SQL Server 2005 will not. Even better is to remove those stored procedures and rewrite them in the Common Language Runtime (CLR) layer. The CLR provides a greater degree of flexibility and is much safer.

Security Checks

I describe the Surface Area Configuration (SAC) tool and the Configuration Manager in many chapters in this book, with the most depth in Chapter 4, "Security." These tools deal primarily with security, but also with what SQL Server presents to the network from the ports, protocols, and other standpoints. Make sure you review these tools and then run them to open or secure your system appropriately. This is a departure from previous versions of SQL Server, which were largely open by default.

If you used the backup and restore or detach and attach methods to migrate the databases to a new installation, the security accounts in the database will no longer be connected to the security accounts on the server, even if the names are the same. There are a few ways to deal with this, and I cover those in Chapter 4.

Performance Tuning

When you are finally done, make sure you run through one maintenance cycle. The upgrade testing is not complete until you ensure that your maintenance runs, and you may even want to re-create the maintenance using SSIS so that you have a more effective, flexible plan. I cover that in Chapter 8, "Integration Services."

Another reason to run the maintenance on your system is that the statistics and indexes may not be accurate for the new system. All of the configuration settings on the new platform have an impact to these performance systems, so make sure you present the most optimized view of the data that you can for your users by running full maintenance before you let them on.

With the system upgraded, optimized, and running, you are ready to begin. Make sure you review the sections of this book from time to time that apply to what you are working on and keep your system safe, secure, and optimized.