Continuing from Part 2 of the Database Migration Checklist series:

Step 10: Full-text catalogs and full-text indexing

This is one area of SQL Server where people do not seem to take notice unless something goes wrong. Full-text functionality is a specialised area in database application development and is not usually implemented in your everyday OLTP systems.

Nevertheless, if you are migrating a database that uses full-text indexing on one or more tables, you need to be aware a few points.

First of all, SQL Server 2005 now allows full-text catalog files to be restored or attached along with the rest of the database. However, after migration, if you are unable to look at the properties of any full-text catalogs, you are probably better off dropping and recreating it. You may also get the following error messages along the way:

Msg 9954, Level 16, State 2, Line 1

The Full-Text Service (msftesql) is disabled. The system administrator must enable this service.

This basically means full text service is not running (disabled or stopped) in the destination instance. You will need to start it from the Configuration Manager.

Similarly, if you get the following message, you will also need to drop and recreate the catalog and populate it.

Msg 7624, Level 16, State 1, Line 1

Full-text catalog ‘catalog_name‘ is in an unusable state. Drop and re-create this full-text catalog.

A full population of full-text indexes can be a time and resource intensive operation. Obviously you will want to schedule it for low usage hours if the database is restored in an existing production server.

Also, bear in mind that any scheduled job that existed in the source server for populating the full text catalog (e.g. nightly process for incremental update) will need to be re-created in the destination.

Step 11: Database collation considerations

Another sticky area to consider during a migration is the collation setting. Ideally you would want to restore or attach the database in a SQL Server instance with the same collation. Although not used commonly, SQL Server allows you to change a database’s collation by using the ALTER DATABASE command:

ALTER DATABASE database_name COLLATE collation_name

You should not be using this command for no reason as it can get really dangerous.  When you change the database collation, it does not change the collation of the existing user table columns.  However the columns of every new table, every new UDT and subsequently created variables or parameters in code will use the new setting. The collation of every char, nchar, varchar, nvarchar, text or ntext field of the system tables will also be changed. Stored procedure and function parameters will be changed to the new collation and finally, every character-based system data type and user defined data types will also be affected.

And the change may not be successful either if there are dependent objects involved. You may get one or multiple messages like the following:

Cannot ALTER ‘object_name‘ because it is being referenced by object ‘dependent_object_name‘.

That is why it is important to test and check for collation related issues. Collation also affects queries that use comparisons of character-based data.  If errors arise due to two sides of a comparison being in different collation orders, the COLLATE keyword can be used to cast one side to the same collation as the other.