Migrating SQL Server Databases – The DBA’s Checklist (Part 3)
- by Sadequl Hussain
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.
Continues…
