Migrating a SQL Server 2000 database to 2008R2 (or higher) in one shot

Point of this article

I often face with problems when clients try to migrate their databases:

  • They implement new policies in new databases versions
  • They want to follow the best practices given by Microsoft
  • They want to change the collation
  • They don’t want to interrupt more than a few minutes, the activity for critical databases

To put everything in a nutshell, they want something almost impossible, and so they are procrastinating again and again…

But now as SQL Server 2000 is not supported anymore, they have no more choice: They must migrate !

You can not imagine the numbers of databases that are still running under SQL Server 2000, and it works, so why would they change anything, right?

In this article I provide a solution to migrate a database playing by the whole constraints listed above… In one shot you will migrate a database from SQL Server 2000, with SQL Server 7 compatibility to SQL Server 2008R2 (or higher)

All the Microsoft PFE will answer you this is impossible:

  • because you can just go through SQL Server 2005 as intermediate step
  • because if you change the collation, you have to import data, after creating a database from scratch
  • Because you must sometimes, remove the constraints and apply them again later (if you can)
  • Because the time taken by the 2 backups ans the 2 restore operations will take hours

Here is the solution: Use the transactional replication: it’s included in your product and amazingly powerfull

  • You will change the collation in one shot
  • You will migrate a big percentage of your data
  • At the last moment, you will synchronize the databases and you will finish the last in a few minutes, not even stopping your activity!

One more thing: As production servers are often at heir limit, you don’t want to add an overhead activity that could put your server down: in this case I use a pull replication, so you can make your boss confortable with this argument.

To be clear, there is no excuse to migrate it NOW!!!

In this example we have:

  • An SQL Server 2000 installed with latin collation
  • A source database on this SQL Server 2000, with 7.0 compatibility
  • A SQL Server 2008R2 installed with FRENCH_CI_AS Collation

So roll up your sleeves a little bit, take your coffee and follow that step by step !

How to set the publisher (SQL Server 2000)

Share folder creation

  • On your server, have to create a folder called ReplData that will be shared, and will contain the snapshots files of your future replication
  • Share this folder by right clicking the folder and clicking Properties
  • Click Sharing tab

  • Click Share this folder
  • In the Share this folder field, check that the name ReplData is entered (Save this name for later)
  • Click Apply
  • Click the Security tab

  • Click Add…

  • In Enter the object names to select, enter everyone
  • Click Check Names
  • Click OK

  • In Group or user names, click Everyone
  • In Permissions for Everyone, click Allow for Full Control
  • Click OK

Check sharing permissions

  • Click Permissions

  • Click Everyone
  • Click Allow for Full Control
  • Click OK
  • Click OK

 

Setting up your Publication

  • Elapse Replication folder
  • Right click on Publications
  • Click on New Publication…

  • Click Show advanced options in this wizard
  • Click Next >

  • Click Make ‘ServerName\InstanceName’ its own Distributor; SQL Server will create a distribution database and log. You can also click the other option to define an other server that will host the distribution database.
  • Click Next >

  • Click Yes, configure the SQL Server Agent service to start automatically
  • Click Next >

  • Enter a shared folder in the Snapshot folder field. It’s important to set a UNC name, that will be seen from other servers (subscription servers)
  • Click Next >

  • Click on the Data base you want to publish
  • Click Next >

  • Click Transactional publication
  • Click Next >

  • Click Next >

  • Click No, Subscribers receive data directly
  • Click Next >

  • Click Servers running SQL Server 2000
  • Click Next >

  • Click Show unpublished objects to see whether table have problems or not
  • In object type, click Show and Publish All to select and publish all the articles by object type (you can either check all or one by one the articles)
  • Click on Next >
  • You can click on … in front of an article to set a different owner on the target table

Exception for articles with no Primary Keys.

These articles symbolized by a stroked key and not by a check, will not be published because they don’t have primary key. You can anyway publish the other articles, and make modification to set a primary key in your database and republish these modified articles later.

  • Click Next >

  • In the Issues list, select each item in order to view what kind of potential issues you can have.
  • Click on Next >

  • In Publication name field, enter a name for the publication (this name will appear under Publications folder in SQL Server
  • Click on Next >

  • Click Next >

  • Click Finish

Troubleshooting

  • Click Start / Administrative tools / Computer management

  • Expand Local Users and Groups
  • Click Groups

  • Double click Administrators

  • Click Add…

  • Type the account used for your SQL Agent service
  • Click Check Names
  • Click OK

  • Click OK

Publication Properties

  • Click Subscriptions never expire… in order to avoid the subscriber to be dropped in any kind long issue

  • Verify that Allow pull subscriptions is checked
  • Click Snapshot tab

  • Check Do not lock tables during snapshot… in order to avoid any issue if the snapshot is made during a batch or long insert / update operation on the source database
  • Click Status tab

  • Click Start Service if the SQL Server Agent is not running (see troubleshoot section in case)
  • Click OK

Defining data filters

      Colum filters    

How to set the subscriber (SQL Server 2008R2)

  • Elapse Replication folder
  • Right click on Local Subscriptions
  • Click New Subscriptions…

  • Click Next >

  • Click <Find SQL Server Publisher…>

  • Click Connect

  • Select the database you want to subscribe to.
  • Click Next >

  • Click Run each agent at its Subscriber (pull subscriptions)
  • Click Next >

I made the choice of using pull subscription, just to avoid to add more activity on the server you want to migrate. These old servers are usually on production systems and really sensible (the clients often refuse to touch something that works, and…they are right!)

  • Select the subscriber server
  • Click <New database…>

  • In Database name field, enter the name of the new database
  • Set all the parameters you want about the new database (logs,filegroups,…)
  • Click OK

  • Click Next >

  • Click …

  • Click Run under the SQL Server Agent service account
  • In Connect to the Distributor panel, click By impersonating the process account
  • In Connect to the Subscriber panel, click By impersonating the process account
  • Click OK

  • Click Next >

  • Click Next >

  • Check Initialize
  • In Initalize When, select Immediately
  • Click Next >

  • Check Create the subscription(s)
  • Click Next >

  • Click Finish

  • Click Close

 

POC Validation

 

Compatibility mode

In the database properties, you can check the collation elements and compatibility mode

  • The target database under SQL Server 2008R2 has a 100 compatibility mode
  • The database collation is FRENCH_CI_AS

Collations

On SQL Server, collation is set within 3 levels (Instance, database and field)

  • The source database has a SQL_Latin1_General_CP1_CI_AS collation (database level)
  • The columns of this database were also with a SQL_Latin1_General_CP1_CI_AS collation