Upgrade SQL Server 2000 database to 2008 R2 and enable new features

I have recently upgraded SQL Server 2000 database to 2008 R2.

What I did was:

• Shutdown SQL Server 2000 (express) service on old machine,

• Move datafiles (mydatabase.mdf and mydatabase.ldf) to new machine,

• Run SQL Server Management Studio 2008,

• Connect to local database engine,

• Attach datafiles to database.

• Change the compatibility level of database to SQL 2008 (100).

Question: What else should I do to make migration complete?

I want to:

• use new features like checksumming and full recovery model,

• make this database to be exactly as it was created in SQL 2008 R2,

• make this database to be fully compatibile, correct and be perfect suited for new, SQL 2008 R2 database engine.

In other words: I just want to know how to correctly and completly convert old SQL 2000 database to new 2008 R2 database, be calm that everything is done right and be happy with all new features.

I'm asking this question, because I've found a lot of sites on the Internet that says so many different things that makes me confused: some say that it is required to rebuild indexes, another says to do other things... and now I know nothing so I want to hear experienced person opinion and clear, step-by-step instructions. I work for very small company, I'm on my own and I don't want to screw things up.

Sir, I'm really impressed with your answer, I wasn't expecting so much.

So some comments:

-
The database is now in production. As I said, it was upgraded using deattach-attach method as I desribed in first post and as described on MSDN: http://msdn.microsoft.com/en-us/library/ms189625.aspx
It had to be done quickly, so I was forced to do it that way. Let's forget about how inappropriate could it was and focus on current situation.

-
The users/persmission is not a problem here - there are only few and permissions are simple.

-
The application which use database is compatibile with SQL 2000 till 2012 so this is not a problem either.

-
The database file (MDF) isn't big -

The foremost step to do is to run the Upgrade Advisor on SQL Server 2000 database and address all the issues reported by it.

As a best practice, use the Upgrade Advisor tool on your SQL Server 2000 legacy database and import a trace file to the Upgrade Advisor tool for analysis. The trace file lets the Upgrade Advisor detect issues that might not show up in a simple scan of the database, such as TSQL embedded in applications. You can capture traces of TSQL using SQL Profiler on your SQL Server 2000 server during typical hours and analyze these traces using the Upgrade
Advisor.

So the rest of the steps would be :

On the day of migration :

• script our logins on 2000 server using sp_help_revlogin.

• Script out jobs and linked servers from sql 2000 server.

• stop webservers connecting to 2000 server. Make sure that no applications are connecting to the 2000 server.

• backup your databases and restore on destination sql 2008 R2 server.(note: Do not detach/attach as things could go wrong and you will end up with detached database and no backups !)

• Once your backups are restored on the 2008 R2 server, run the output from sp_help_revlogin on 2008 R2 server to recreate logins.

• Sync up orphan users (If any) and recreate sql agent jobs and linked servers on the new server.

• change compatibility level on the restored databases to 100.

• Dbcc checkdb with all_errormsgs and data_purity options turned on : DBCC CHECKDB ('' ) WITH ALL_ERRORMSGS,NO_INFOMSGS, DATA_PURITY

• run DBCC UPDATEUSAGE on the restored databases DBCC UPDATEUSAGE('database_name') WITH COUNT_ROWS

• Update statistics on all tables with full scan : Update Statistics table_name with FULLSCAN

• Optional : Check the fragmentation levels and depending on the fragmentation level, run a reorg/rebuild of all Indexes. You can use Ola's scripts.

• Recompile all SP's using sp_recompile 'procedureName'

• Refresh your views SP_REFRESHVIEW view_name

• make sure to change the database option : page verify to CHECKSUM.

• Change the recovery model (if different from sql 2000) to FULL. If you change to FULL recovery model, then MAKE SURE you do Transaction Log backups frequently. This will help you to recover point-in-time as well as not bloat your T-Log.

-
In SQL Server 2005 and up, Database Mail was introduced. So you have to migrate from SQLMail to Database Mail.

USE [master]
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE 
GO

Also, if you have any replication, then you have to reset it up. If any DR like logshipping or Mirroring (new in 2005 and up, but depreciated in 2012) then you have to reset it up as well.

Old DTS packages needs to be migrated to SSIS using C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSMigrationWizard.exe (command line) or using Package Migration Wizard.

Also, you can use my script found at https://dba.stackexchange.com/a/36701/8783. Though, it uses detach/attach method, I highly recommend you to use BACKUP/RESTORE method. Change the script accordingly.

As a side note:

• turn on Instant File Initialization on the new server.

• Have multiple tempdb data files with equal size.

• Enable Trace Flag 1118

• Configure max and min memory correctly. Especially Max memory away from default.

• Properly adjust the MAXDOP settings. Refer to https://dba.stackexchange.com/a/36578/8783 for more details.

• Best is to install sp_Blitz from Brent Ozar. Run it and address the critical and high priority issues reported by it.

• You can even use SQL Power Doc from kendalvandyke - SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. Also useful for Planning upgrades - see what hidden features are in use on an instance.

• Enable Optimize for ad-hoc workloads and Default backup compression options.

Lets address your questions ...


What else should I do to make migration complete?

Refer to my answer. It will help you properly come up with a migration plan. Always test your migration plan in a UAT (non production) along with proper application testing by business users.


use new features like checksumming and full recovery model.

CHECKSUM is new in SQL Server 2005 and up. I have covered it as a part of migration steps described above.

full recovery model is not new. It depends on your business type and dictates on how much data you can lose in case of disaster.

Full recovery mode with frequent Transaction log backups will allow you to restore point-in-time and there by reducing the amount of data loss.


make this database to be exactly as it was created in SQL Server 2008 R2.


make this database to be fully compatible, correct and be perfect suited for new, SQL 2008 R2 database engine.

Don't fully understand this ! But ab

StackExchange Database Administrators Q#50311, answer score: 39