There are a number of different methods to migrate a SQL Server 7.0 database to SQL Server 2000. Before you decide which method to use, you need to know the collation differences between SQL Server 7.0 and 2000, and why these differences could lead to collation issues after migrating databases. The issues associated with the collation differences might help you decide which is the best migration method for moving your databases between SQL Server 7.0 and 2000. In addition to handling collation issues when copying databases between SQL Server versions, you will also need to consider how to migrate SQL Server logins. This article will discuss these different migration topics, as well as identifying a methodology for migrating databases to overcome the collation issues that might arise, depending on the method selected for migrating SQL Server 7.0 databases to SQL Server 2000.

Collation Difference between SQL Server 7.0 and SQL Server 2000

In SQL Server 7.0, there was a single collation setting for the server. Each database and column for each table used the same collation setting as the SQL Server 7.0 server setting. With SQL Server 2000, Microsoft has expanded the collation options available. In V2000, the server and each database can now have a different collation settings. Also, each column can have its own collation setting, which can be different from that of the server or database collation. Because SQL Server 2000 supports multiple collation settings, collation issues might arise when migrating databases from SQL Server 7.0 to SQL Server 2000 using the backup and restore method for copying databases.

Collation Issues When Migrating Databases between SQL Server 7.0 and SQL Server 2000 Using Database Backups

Now that SQL Server 2000 allows multiple collation settings, you are able to take a database backup of a SQL Server 7.0 database with one collation, and restore it to a SQL Server 2000 machine, where the default server collation is something different. This is something you were not able to do in SQL Server 7.0, since there was only a single server collation setting. Because SQL Server 2000 allows multiple collations, using a SQL Server 7.0 database backup to migrate to SQL Server 2000 allows you to create a V2000 database that might not have the same collation settings as the other databases on the same SQL 2000 server. Having different collation settings might cause your application to have collation conflicts. Conflicts occur if your application compares data across databases where the columns being compared have different collation settings. If you are migrating a SQL Server 7.0 database with a different collation setting than that of the default SQL Server 2000 collation setting, I suggest you don’t use a database backup to migrate your database from SQL Server 7.0 to SQL Server 2000, but instead migrate the data using database migration step outlined below.

Migrating Logins

SQL Server 7.0 and 2000 have the same encryption scheme for storing passwords associated with SQL Server authenticated logins. This fact allows you to move SQL Server authenticated logins from one server to another and still retain the passwords. Microsoft support staff have created a stored procedure to help you migrate logins, called sp_help_revlogin. This stored procedure and some additional information about it can be found at http://support.microsoft.com/default.aspx?scid=kb;en-us;246133. Basically, this SP uses the information in the sysxlogins table on the SQL Server 7.0 source machine to generate a script to migrate both Window and SQL Server authenticated logins. The stored procedure, as it comes from Microsoft, will generate a script to migrate a single login, or all the logins on a server. If you are only migrating a single database, this stored procedure can easily be modified to move only the logins for a single database. I used sp_help_revlogin to migrate all my database users from SQL Server 7.0 to SQL Server 2000.

Migrating Databases

As I stated earlier, databases can easily be migrated between SQL Server 7.0 and 2000 using a database backup, although there could be collation issues if the source and target server don’t have the same collation settings. DTS can also be used to import and export your database tables, but I often get dependent object conflicts when using DTS. The dependency errors are cause by referential integrity errors associated with foreign key constraints. Interestingly, when DTS determines the order in which to migrate data when using the input/export wizard, it has been designed to pay no attention to object dependencies. Transferring data in the wrong order is one of the main reasons I don’t use DTS to migrate database tables. Instead, I’ve worked out a multi-step process for migrating databases. This process not only migrates the databases, but also converts the collation setting to match the target server. Here are the steps I use:

Step 1: Migrate logins

Before I migrate the database, I migrate all logins associated with the database users. As mentioned previously, I use the sp_help_revlogin to accomplish this.

Step 2: Create the database on the target server

To ensure that the new database has the same collation setting as the target server, I create the database on the target server using either Enterprise Manager or T-SQL code, and I don’t specify a collation setting. If you don’t identify a collation setting when creating a database, the new database will take the collation setting of the model database.

Step 3: Create database users, defaults, user-defined data types, and database roles

Before you can create tables that might use default objects, you will need to create the defaults in your SQL Server 2000 databases. Normally, I create a script that not only creates the defaults but also creates database users and database roles if my database contains database roles. In addition to that, I make sure the appropriate database users and roles are defined as members to the correct database roles. I typically use Enterprise Manager to generate this script. Once I have generated the script, I run the script to create all the users, roles, defaults, and user-defined data types in my SQL Server 2000 database.

Step 4: Create tables on SQL Server 2000 Database

I normally use Enterprise Manager to generate a script of all tables in the source database. When requesting what to generate I only generate the create tables statements, and nothing else. All the permissions, foreign keys, triggers and indexes are created later. Once the script is created I run it to create all the tables in my SQL Server 2000 databases.

Step 5: Migrate the data from SQL Server 7.0 database to 2000 database

To migrate the data from SQL Server 7.0 to SQL Server 2000, I use the DTS Input/Export wizard. DTS doesn’t have the intelligence to copy SQL Server tables in the correct order, and avoid referential data integrity errors. This is why, in the previous “Create Table” step, I did not generate any foreign key constraints. Without having foreign key or check constraints, DTS has no problem populating the SQL Server 2000 database tables without receiving constraint errors.

Step 6: Build a foreign key to check constraints and indexes

To do this, once again, I use Enterprise Manager to generate a script to create a foreign key to check constraints as well all indexes on tables. Once I have built a script, I run it against my SQL Server 2000 database. It is possible that some of the constraints were created on your SQL Server 7.0 database, and run using the “WITH NOCHECK” option. If so, you will need to modify the generated script so it also has the “WITH NOCHECK” option. If you don’t use the “WITH NOCHECK” option, then some of the create constraint will fail because the table data violates the constraint rules.

Step 7: Create views

I normally don’t use Enterprise Manager to create my views on my new SQL Server 2000 database. Enterprise Manager generates views alphabetically, one after another. If one view depends on another, and the dependent view name alphabetically falls after the view that calls it, this causes the script that is generated to have errors when executed. For this reason, I use a third-party software package that correctly identifies dependencies and generates the create view statements in the correct order. There are a number of packages available that can do this.

Step 8: Create stored procedures and triggers

The last set of objects to create are all the stored procedures and triggers. Once again, Enterprise Manager does a good job of generating scripts to create these. Once these two scripts are generated, I run them against my SQL Server 2000 database.

Step 9: Grant permissions

The final step in migrating your SQL Server 7.0 database is to set all the permissions that will determine which users and roles have authority to select, insert, update, delete, and execute objects in the database. The script to do this can be generated using Enterprise Manager, and then run against your SQL Server 2000 database.

Step 10: Validate migration

I add one last step to validate the migration. My validation step verifies that all objects in SQL Server 7.0 were created in SQL Server 2000, and that the same record counts exist in both SQL Server 7.0 and SQL Server 2000 tables. The first part of this validation step ensures that all the objects migrated identically. I do this by using a third-party software package; try to find a package that compares object by object to make sure each database object has the same structure/code in SQL Server 2000 as it did in SQL Server 7. To accomplish the second part of the validation process, validating the record counts, I write a linked server SELECT script to compare the record counts of each table on my SQL Server 7.0 database with the record counts in my SQL Server 2000 database. There should be exactly the same number of records for each SQL Server 2000 and SQL Server 7.0 table. If the record counts are not the same for each table, then something must have gone wrong in the data migrate step (Step 5).

Conclusion

The method I’ve just described is just one way to migrate databases from a SQL Server 7.0 environment to SQL Server 2000. This method ensures that all your SQL Server 2000 database have the same collation setting, so that no collation issues will arise when comparing columns between database. I have used this method to successfully migrate a number of different databases.

Hopefully, this article has given you an understanding about collation issues that can arise when migrating databases from SQL Server 7.0 to SQL Server 2000, and you’re armed with a method to successfully convert the collation setting when migrating databases between SQL Servers versions.

--

Gregory A. Larsen is a DBA at Washington State Department of Health. He has been working with computers since the late 1970s, and has a BS in Computer Science, with a minor area of study in Mathematics. During Greg’s DBA career, he has managed databases on Windows, Unix, and IBM mainframes; before becoming a DBA, he developed and maintained applications. Greg has written a number of articles for different web publishing sites, as well as SQL Server Magazine. Greg also maintains a Website that contains a number of SQL Server examples to common DBA and SQL developer issues.