Upgrading Databases to SQL
Server 2008 - a few lessons learned
article by Paul Ibison
Introduction
When upgrading a database from SQL
Server 2000 to SQL Server 2008 there are many considerations.
BOL
does a good job of documenting the general process and other blogs will mention
a script like the one below which can be used after the restore has completed to
finish the job.
use Yourdbname
go
EXEC sp_changedbowner 'sa'
ALTER DATABASE yourdbname SET COMPATIBILITY_LEVEL = 100
dbcc updateusage(0)
exec sp_updatestats
This article lists some of the many extra issues and
considerations we discovered during an upgrade project which were not so
straightforward or simple to find. Some of these relate to replication issues
but many are general ones you might find useful...
If merge replication
is involved you'll have to:
(a) upgrade the scripts to SQL 2008 syntax and then make
sure that the publication compatibility of
the replication scripts is set to SQL 2008 on the publication, assuming all the
subscribers are SQL 2008. NB this is NOT the same as setting the compatibility level for
the databases. If this is not done, you'll not be able to use the new @replicate_ddl
article property, drop merge articles or to replicate the new datatypes.
(b) make sure that the merge replication history on SQL 2005/8
is deleted. The MSmerge_history table may not be cleared up by the cleanup agent
as there is a known bug in SQL 2005/8 that causes this when using the
continuous schedule. When the merge agents are
set to run continuously the stored procedure (sp_MShistory_cleanup) that does
the delete relies on the MSMerge_sessions table to work out the records to be
deleted. If you are running the agents continuously, there is only a single
session until and unless the agent is restarted and the history records
will not be deleted. The only way around this is to periodically stop and restart
the Merge Agents if you use the continuous schedule.
(c) edit the properties for the
merge articles. When you are scripting out merge publications, be
sure to change certain defaults for the articles as required, especially because on SQL
2008 "compensating changes" are off by default, and identity range management is
on by default.
Issues related to TSQL syntax
(a) start out by using the Upgrade Advisor
to determine the required syntax changes. Some of these will be
more subtle than they seem, especially the *= ones. We can't assume that a
"straight swap" of *= with "Left
Outer Join" will produce the same behaviour. Advise the application team that
each *= one will need examining separately because of the following issues.
SELECT t.*
FROM dbo.titles t, dbo.sales s
WHERE t.title_id *= s.title_id
AND s.title_id IS NULL
-- this ANSI-89 syntax seems like it should simply translate to the following
SELECT t.*
FROM dbo.titles t
LEFT OUTER JOIN dbo.sales s
ON t.title_id = s.title_id
WHERE s.title_id IS NULL
-- try running them on a box with the Pub database though and you will see
different rowcounts returned!!!!
-- the two alternative syntaxes below will work.
-- NB this is only an issue if the code in the WHERE clause refers to the table
on the LHS of the *=
SELECT t.*
FROM dbo.titles t
LEFT JOIN (SELECT *
FROM dbo.sales
WHERE title_id IS NULL) s
ON t.title_id = s.title_id
SELECT t.*
FROM dbo.titles t
LEFT JOIN dbo.sales s
on s.title_id IS NULL
and t.title_id = s.title_id
(b) manually recreate the SQL
programming objects - stored procedures, views, functions etc. The
Upgrade Advisor won't give an exhaustive list of required changes so script out
the procedures and other code, delete them on the SQL 2008 box and then recreate
them back on the SQL 2008 database - this syntax checking is more
comprehensive than the Upgrade Advisor and will pick up things like reserved
keywords which might otherwise be missed (definitely the case on SQL 2005).
(c) be careful with your union
statements as there is an issue with data type precedence and the
UNION operator which might cause unexpected errors. Please see
this article which explains the issues you might encounter.
(d) if the application uses user-defined datatypes
in the tempdb and model databases, they must be scripted out from the old server and added in
to the SQL Server 2008 box. Be careful though as using sp_addtype will add the type
and assign the correct security privileges, while if your script uses the CREATE TYPE
syntax this will not add the
security and you'll need to subsequently issue the GRANT REFERENCES permissions for
this to work.
(e) if the new server is 64-bit and it tries to use
linked servers to a 32-bit SQL Server you might
hit this issue. There's a
simple proc referred to in the link which needs adding to the master database on
the SQL 2000 box which will fix the problem.
(f) if xp_sendmail is used then try to get the application team to use database mail
(SMTP) instead.
If this is not possible, then be careful here as only 32-bit will work, so if
the build is 64-bit MAPI will
not work!!!!
(g) if you use user-defined
functions and your UDF is not marked as "SchemaBound", there could be
a huge performance loss because incorrect join operators may be used - see
these articles for more info:
1, 2.
DTS packages - my preference is to try to
get any packages removed from the database server, placed on an application
server and run by an alternative scheduler e.g. Autosys. If this is not possible and the
packages
aren't going to be upgraded to SSIS i.e. they'll continue to be used as DTS
packages then install the legacy components to allow the use of dtsrun.exe.
Check this thoroughly as so far I have not been able to get the DTS designer
components working on all SQL 2008 DTS packages (in some cases the icons are not
visible despite all hacks mentioned on the newsgroups) but they worked fine for SQL
2005 DTS packages. In this case in your migration environment you might need to
advise the application team to maintain a separate SQL Server 2000 server to hold the DTS
packages which can be used to edit the packages if needed.
Check for any DBCC CHECKDB "DATA_PURITY"
errors before going live.
The DATA_PURITY checks are more rigorous on SQL 2008 and the DBCC CHECKDB check might
flag up some alerts you normally don't see. So, restore the user databases onto
SQL 2008 beforehand and run DBCC CHECKDB with DATA_PURITY - this will outline
which data will need fixing before the migration.
Database properties need deciding upon.
Discuss with the application team whether they want to use
READ_COMMITTED_SNAPSHOT or
not! Then check what is the standard policy being used for PAGE_VERIFY and
TRUSTWORTHY.
When importing the logins be wary of the standard sp_help_revlogin. If this is
used on SQL 2000 the sp_addlogin script which is produced has some flaws. The
main on is that the CREATE LOGIN command which is run behind the scenes has a
default of true for the policy checks and this means that password strength will be
enforced and some logins might fail to be created. Better to use the modified
version:
sp_help_revlogin_2000_to_2005
When transferring jobs:
(a) make sure that any references
to servernames are updated:
update msdb.dbo.sysjobsteps
set output_file_name = replace(output_file_name,'oldservername','newservername')
update msdb.dbo.sysjobsteps
set command = replace(command,'oldservername','newservername')
(b) be careful in the TSQL steps of user
jobs. If there is a step which runs TSQL and this TSQL is formatted as follows:
exec myproc go
there will be problems in the case where the procedure "myproc" takes a parameter
which is optional: in the case of the example above the "go" statement will
then be interpreted as a literal value. This will error if the parameter is an
incompatible datatype e.g. integer, or at worst it will be passed in as a
legitimate value. So, this needs to be rewritten with a
carriage return to work properly:
exec myproc
go
|
