General Guidelines

Your upgrade strategy depends on what you are trying to accomplish. If you are just replacing a previous version of SQL Server on the same system, that is often an easier process than changing hardware and applications at the same time as the database platform upgrade. I help you deal with each of those situations in a moment, but you can follow some general guidelines prior to starting the upgrade.

In most cases, but not all, you must have some downtime for the system before you begin your upgrade. Sometimes that is not possible. If you need to maintain availability of the system, you will need to follow the planning steps I mention here and then select the strategy that gives you the option of presenting the system during the upgrade.

As with any change to your production infrastructure, make sure you have a solid understanding of the options available to you, and then you need a good plan to implement the change. This Appendix will help you do that; when you upgrade other features or special installations such as clustered systems, however, you need to do more research. In all cases, start with planning.

Planning

You cannot substitute for planning the upgrade. Most often, a Relational Database Management System (RDBMS) is used in a critical area of your organization, and rushing headlong into the process is asking for trouble. Even if you are a seasoned veteran with lots of experience, at least read through this section to see what you are up against.

First, contact all the affected parties or their managers (called the stakeholders) and explain that you are planning an upgrade. After you read this Appendix, you will know more about what impacts the upgrade will have, and you can communicate that to them. After explaining what you know and soliciting their input, you will almost certainly find out things you did not know. One group might tell you that their application vendor will not support SQL Server 2005, and another might tell you they need to re-architect an in-house application to take advantages of the new features it provides. With that information, you are able to better coordinate your upgrade. It is also a matter of business professionalism to keep everyone informed.

Your planning document should include the following elements:

• Description of the change

• Timetable

• Impacts

• Costs

• Expected benefits

• Exit strategy

Document Your Current Systems

In a perfect world, you already have your current systems completely documented. Because we do not live in a perfect world, you may need to either create documentation from scratch or freshen up the documentation you already have. In either case, you need to collect at least the following information:

• Server name

• Server location

• Hard drive layout and configuration

• Amount of physical memory

• Exposed network ports and security constraints

• Operating system version and service packs

• Utility programs

• SQL Server version

• Database metadata

• Maintenance plans

• DTS packages

• A full script of the current permission schema (needed for restored or attached databases)

• List of stored procedures that reference system objects

Normally, this type of documentation is necessary because you are asked to gather it for the upgrade program. In the case of SQL Server 2005, Microsoft provides a tool that scans your system for any issues it can detect and tells you what needs to be corrected prior to the upgrade. I show you how to use that in a moment. But you still need the documentation.

Documenting your systems often leads to some new discoveries, which can help you for budgeting and other purposes. The information you are gathering here is for the planning process as well as the next phase: the exit strategy.

Create an Exit Strategy

Although Microsoft has done a great job of making the upgrade process as seamless as possible, things can still go wrong. Before you begin the upgrade, you need to create an exit strategy.

Also called a disaster recovery plan or backup strategy, an exit strategy is essentially what you will do if things do not go as planned. In small implementations of applications that depend on SQL Server, the exit strategy can be as simple as ensuring that you have a good backup and restoring that backup in case of an issue.

Even then, you need to document the process so that you can communicate it to the people affected by the upgrade. The exit strategy for a "simple" implementation should be documented and tested, to ensure that you know what you plan to do, that it will work, and how long it will take. You might also want to include a "risk factor" that explains which steps have the most potential (however small) to fail. A little paranoia here is a good thingassume the worst about any changes to a production system and you are safer than expecting everything to go well.

In a more complex environment, the exit strategy will necessarily be more complicated. Although this might seem like more of a formal process than you need, it is not. Having a plan is like having insurance. It never seems worth it until you don't have it.

The following table is a sample of the start of an upgrade plan that includes a simple restore as an exit strategy.

You add more columns to this chart as you move along in the planning. If you total the durations on the chart, it may add up to more than you originally thought to perform the upgrade on a simple system. If these sample numbers are accurate, for instance, your system will be down for the better part of an entire day for the upgrade.

If your system involves multiple departments or time zones, is a clustered system, or has multiple databases, your exit strategy has to include not only a risk factor but also a business impact. A business impact is a rating on how badly the business is hurt if some process is not resumed within a set period of time.

For instance, if an application processes payroll, the business impact might be less than if the application processes parts at the plant. This depends, of course, on how close you are to the time that you need to cut payroll checks! So for more complex environments, you will create the same type of documentation but for each part of the application. You will also need to have formal meetings with the process owners so that they are aware of the possible impacts to their function.

Documenting the steps like this forces you to defend them. Your company might not want a high level of downtime or may not tolerate a high degree of risk, and with documentation you might be able to justify another way to implement the solution.

Requirements

Other than the hardware requirements for any SQL Server 2005 system that I explain in Chapter 1, "Installation and Configuration," the primary thing you need to upgrade is the space of the database storage that you are using now. Depending on the type of upgrade you plan to do, SQL Server needs more room to do the conversion between the databases, and you will need it to retain the backups in case of a failure.

There are ways around this requirement. One is to back up some of the databases and upgrade the system only with the databases that have the most critical objects. You can then restore the databases that you backed up to the new version. SQL Server 2005 automatically upgrades a database during the restore process.

With these space requirements, you might be tempted to use Windows-based compression on the drives that hold the databases. Although this works in earlier versions (but is always a bad idea), it does not work in SQL Server 2005.

What Works and What Doesn't

As with all software, sometimes a simple upgrade is not possible. For the most part, Microsoft does a great job with the hardware and software that you have when you move to SQL Server 2005, but there are certain things that either do not upgrade well or do not upgrade at all. In this section, I explain well-known issues that you are likely to encounter.

That is not to say that you will not face any issues other than what I explain here. It is absolutely vital to have that exit strategy complete before you attempt any upgrades, because anything can happen.

Versions

You can upgrade from SQL Server version 7 or SQL Server version 2000. If you have something earlier than that, you can create Data Manipulation Language (DML) scripts of the objects and then use the Bulk Copy Program (bcp) to copy out the contents of the tables into text.

The following table lists which version upgrades to which.

As with any upgrade, you cannot upgrade a system to a higher version without considering the licensing issues. You are not allowed to replace a lower version with a higher one without paying for the license. For the most part, the upgrade tools prevent this anyway, but it is something you need to keep in mind.

Features

Although SQL Server 2005 added many amazing new features, not all previous components made the cut. English Query, a Natural Language Processing (NLP) system, is no longer a part of SQL Server. If you have never used this handy feature, you are not aloneMicrosoft did not see enough use in the community to include it in the new packaging.

Unless your databases are set to the 80 compatibility mode (more on that later), you will not be able to use the older ANSI 89 join syntax. For instance, this kind of statement does not work on a 2005 database:

SELECT column1, column2
FROM table1, table2, table3
WHERE table1.key=table2.key AND table2.key=table3.key

If you are still writing these kinds of joins, you are not taking full advantage of the speed of the newer T-SQL format anyway. If you need them, you can set the compatibility mode to 80 and they will still work.

Another feature that has changed dramatically from previous SQL Server versions is Data Transformation Services. I have an entire chapter in the book devoted to its replacement, SQL Server Integration Services (SSIS). The two tools differ completely. There are a few ways you can deal with the change, but to take full advantage of the power in the new platform, Microsoft suggests that you "migrate and modify" the packages. In practice, I have found it is better to completely rewrite the packages in SSIS because you have so many new tools within it that you can approach the problems they solve in new ways. If you have a lot of packages or they are particularly complex, that is difficult to do, so Microsoft includes a layer within SSIS that can still run older DTS packages. There is not enough room here to cover all the changes that you need to take into account, so I refer you to the SSIS section of this book to learn more.

Depending on your version of operating system, the Microsoft Database Access Components (MDAC) may change when you upgrade to SQL Server 2005. Because the MDAC level is also affected by service packs to both the operating system and SQL Server, I refer you to Microsoft's SQL Server site to find the latest version that is installed. This is one of the most important things to communicate to your developers before you upgrade, because the MDAC level is important in the way they write applications against the database.

Other Information

You need to know a few other things about upgrading your system. English versions upgrade to any locality, but localized versions upgrade only to that locality. If you are in a multinational organization, this can be an issue if you are using the upgrade as an opportunity to standardize on a language; keep this restriction in mind.

System catalog direct updates are not supported in SQL Server 2005. That is a bad practice anyway, but in this case the code that calls it will break. Along those same lines, code that references system objects does not work in SQL Server 2005.

You need to have your developers scan the new reserved words (a standard practice) to ensure that they have not named any database objects with those words or used them in their code.

Although it is a rare problem, the database ID (an internal number given to each database) of 32767 is reserved. You do not normally set this number; it is an automatic value assigned on database creation. The Upgrade Advisor (more on that later) will catch that error anyway.

If you have placed data or log files on compressed or read-only drives, they will not upgrade properly. It is normally bad practice to do this to begin with because it dramatically affects performance.