SQL Server Version Upgrades or Migrations

Upgrading an SQL Server version is something most DBAs have encountered or will encounter at some time in their careers. Whether you are upgrading the legacy Enterprise Resource Planning (ERP) system that was on SQL Server 6.5 and is finally being retired or migrating to a new SQL Server 2000 instance on new hardware because you outgrew capacity on your current server, you have to take into account the amount of downtime that will be caused and what effect it will have on your end users and the SLA.

Tip

Much of the information in an earlier section, Upgrading, Consolidating, and Migrating to SQL Server 2000, is applicable to any upgrade or migration process, including version upgrades for SQL Server. Use that in conjunction with the information in this section to help you plan your upgrades or migrations from another SQL Server version or instance to your target instance of SQL Server 2000.

Depending on which version you are starting from, you will have different options. Regardless of the version, you should have a dedicated testing environment as well as new hardware for the new SQL Server 2000 instances. When you are trying to upgrade on the same server, you increase your risk greatly for a few reasons. The most important reason is that should something go wrong, you might not only ruin the new environment, but also damage the old environment to the point that it can never be a fallback plan. Another reason is that while the upgrade is happening, you might not be able to use the server at all, whereas on different hardware, you might be able to keep things running while you are doing some, if not all, of the upgrade. From an availability perspective, that is crucial.

Another reason to keep the original hardware available is troubleshooting against the perceptions of the occasional change-fearing user . Consider a case in which you performed a flawless migration, only to have users complain about the performance of a particular task a few days, or even a week or two later. Keep in mind that this might be noise, as sometimes hearing that the system changed at all makes users think there actually will be a performance difference, causing perceived problems. Having the ability to run the query on the existing old system and prove that it is just as fast or even faster can save you much time and effort. It can also save your reputation and your performance review.

Depending on which version of SQL Server you are starting from, different native SQL-based tools are available to assist during an upgrade, consolidation, or migration effort. Other requirements also drive the decision process of what to use, such as the window of time that is open to perform the migration. Whatever tool you decide to use, realize that each has its strengths and weaknesses, and you might need to combine tools.

Warning

If you are using replication, it must be disabled or unconfigured prior to the upgrade. It would be necessary to have the replication configuration documented, as well as scripted, so that it can be set up after the upgrade. For more upgrade information with replication, see the topics Backing Up and Restoring Replication Databases, Scripting Replication, and Replication and Upgrading in SQL Server Books Online.

Caution

Do not assume your application running on a previous version of SQL Server will work or perform better than it did before the upgrade to SQL Server 2000. First, if it is from a third-party manufacturer, make sure the software is supported on SQL Server 2000. Second, make sure that syntax that is used by any application is still valid and that some variables or things like column names that you are using are not now reserved keywords in SQL Server 2000 (see Reserved Keywords in SQL Server Books Online under the topic Transact -SQL Reference) or are not being deprecated. The same goes for any applications, as Open Database Connectivity (ODBC) keywords are also listed.

More Info

For more information on upgrading your SQL Server version, some key topics in SQL Server Books Online you can reference (there are more that are not listed here) are Upgrading an Existing Installation of SQL Server, Upgrading from SQL Server 7.0 to SQL Server 2000, Preparing to Upgrade from SQL Server 6.5, and How to Upgrade from SQL Server 6.5.

Tools for Upgrading from SQL Server 6.5

The database formats of SQL Server 6.5 and SQL Server 2000 are incompatible, so it is not possible to use the backup and restore process to create the database on the target SQL Server 2000 instance, which can also jump start the process. You also cannot apply transaction logs of a SQL Server 6.5 database to a SQL Server 2000 database. There are a few options to consider when planning your upgrade or migration to SQL Server 2000:

• BCP/BULK INSERT One tried-and-true method of migrating data from one platform to another is the use of flat files. BCP has been in SQL Server since version 4.21a, and now there is not only the command-line version, but also a Transact-SQL command BULK INSERT. In some ways, this is easier to plan than using the Upgrade Wizard, and in other ways it is more difficult. For example, you now need to worry about creating all your databases in SQL Server 2000 with the proper size and devising a process to migrate the users. BCP only takes care of the data migration. Any indexes, views, and other elements would need to be re- created after the bulk insert because it might slow the process down if indexes are configured prior to inserting data.

• SQL Server Upgrade Wizard SQL Server 2000 has a built-in wizard to assist you with your migration from SQL Server 6.5. It takes into account all aspects of your SQL Server 6.5 configuration, including users. However, the Upgrade Wizard is not appropriate for all migrations to SQL Server 2000.

  More Info

  For more information and details about the specifics of using the Upgrade Wizard, see the topic Upgrading Databases from SQL Server 6.5 (Upgrade Wizard) in SQL Server Books Online.

Tools for Upgrading from SQL Server 7.0

If you are migrating from SQL Server 7.0 to SQL Server 2000, you have some different options than you would if you started with SQL Server 6.5.

• Log shipping It is possible to send and then apply transaction logs from a SQL Server 7.0 Service Pack 2 database to a SQL Server 2000 database. This is one of the better migration options, if not the best, as it not only provides a fallback plan by leaving the source server in place, but also the amount of downtime incurred will most likely be minimal.

• Backup/Restore Backup and restore needs no long introduction. It is possible to take a SQL Server 7.0 backup and restore it under SQL Server 2000; the restore process upgrades the database and performs other functions, such as rebuilding statistics.

• Copy Database Wizard This wizard uses the attach and detach functionality of SQL Server to do the work. The database files will be detached, copied , and then attached to the target server. When going from SQL Server 7.0 to SQL Server 2000, the attach process upgrades the database to SQL Server 2000, but statistics are not automatically rebuilt. That might be a consideration when deciding between backup/restore and the Copy Database Wizard. Also, consider if the collations are different between the SQL Servers, especially if you have an international application. For example, if you use char/varchar data types you could not store and retrieve any data except what can be represented by the code page on the destination SQL Server. Unicode would be a workaround.

  More Info

  For more information on the differences between attaching and detaching versus backup and restore, see the section Attaching and Detaching Databases Versus Backup and Restore later in this chapter.

• BCP/BULK INSERT BCP or BULK INSERT work the same as they do with a SQL Server 6.5 or SQL Server 2000 database; you take flat files and use them to import data into a database. You would need to find another method to import users, objects, and other items.

Upgrading Between Different Versions of SQL Server 2000

The same options for SQL Server 7.0 are valid for SQL Server 2000 to SQL Server 2000 migrations, and because all databases are at the same version level (sans service pack differences), there is less work that needs to be done because the databases do not need to be upgraded. Concerns about collations and service packs are still valid, but you do not need to worry as much about things like behavior differences and syntax changes.

Upgrading from Previous Versions of SQL Server Clustering

If you are looking to upgrade from previous versions of SQL Server clustering to SQL Server 2000 failover clustering, or even from a stand-alone SQL Server to a failover cluster, this section will help you. The same rules for a standard upgrade apply (such as having to unconfigure replication), but there are other considerations to take into account:

• You absolutely cannot mix different clustered versions of SQL Server on the same server cluster. If you are currently running SQL Server 6.5 or SQL Server 7.0 clustering, those absolutely cannot coexist with a SQL Server 2000 failover cluster unless they are physically installed on separate server clusters. This means you would potentially need new hardware for your upgrade to SQL Server 2000.

• If you are upgrading your operating system as well, see the section Windows Version Upgrades earlier in this chapter for information about Windows. SQL Server s health and stability in a clustered environment is directly dependent on a properly configured operating system.

• With SQL Server 6.5 and SQL Server 7.0, the binaries were put on the shared drive and possibly the quorum. Now all binaries will be on the local system drive in the same place on each node. Are the nodes configured the same?

• Where are the database files physically located on your shared disk array? Does your disk configuration meet your current and future needs? This is probably the most important factor in any SQL Server cluster upgrade, whether it is SQL Server 6.5 or SQL Server 7.0 to SQL Server 2000, or SQL Server 2000 to SQL Server 2000. In SQL Server 6.5 and SQL Server 7.0, files could be placed on the quorum drive with no warnings. In SQL Server 2000 this not a recommended configuration.

  As you learned, the quorum drive should be left alone and used only by the server cluster. Nothing else should be placed on it if at all possible. Another potential problem is that even if the data and log files are not on the quorum but on one LUN/drive letter and you are going to be using multiple instances, you now need dedicated drive letters to support each instance. Multiple SQL Server 2000 instances cannot share a drive letter. You need to move the data and log files for the databases that will live on other instances and not on that existing drive and reconfigure your disk subsystem by adding drives . Even with a modern storage area network (SAN), for which adding disks and space are relatively painless, adding additional disks to an existing server cluster is a regimented process detailed elsewhere in this book. Remember to try to add all your disk resources to be used on the server cluster before you install clustering to avoid this extra work. Finally, if you are on an old SCSI attached drive array, will it meet your growth needs? Chances are it will not in the long term , so you might even need a new disk subsystem.

  More Info

  For the exact technical steps for upgrading and more information, see the topics Upgrading to a SQL Server 2000 Failover Cluster, How to Upgrade from a Default Instance to a Default Clustered Instance of SQL Server 2000 (Setup), and How to Upgrade from a Local Default Instance to a Clustered, Named Instance of SQL Server 2000 (Setup) in SQL Server Books Online