Sql-server – What’s the best way to migrate a database to a server of a lower SQL version

If you want to quickly migrate your data from MS SQL Server to MySQL, I suggest using the Migration tool from Oracle. This can really save you some time and effort.

This article can help you get started:

http://www.infoq.com/news/2012/08/MySQL-Migration

Is this pretty straight-forward?

Yes and No .. depends on what is your downtime (maintenance window), database size or amount of data you are replicating (whole or subset of articles).

Script out current replication setup (both create and drop).

First make sure that you don't have any pending commands to replicate and no user connections while you are doing a cut-over to new server.

Below are some of the ways you can choose to ease your migration :

• Easy and straight forward solution is to simply reset up replication. If the database is too big then you can reinitialize from backup as well followed by a snapshot and everything will be working fine.
• Set up log-shipping of current publisher databases that you need to migrate to new server or even you can set up the new server as a subscriber to the old one. Then on the day of cutover, just bring the secondary (new server) database online (restore with recovery) if log-shipping is used or just point your application to the new server and resetup replication.
• A more tedious and possibly error prone solution (if you miss some details) is to backup the publication database and restore it to the new publisher server with the KEEP_REPLICATION flag enabled. Go to the subscriber servers and update the dbo.syssubscription tables with the new publishers information. Update the subscription tables with the new publisher information. Edit all the jobs on the distributor to point to the new publisher. Update the tables in the distributor to point to the new distributor.
• Another way to do all of this is to stop SQL on the old server, copy all the system and user databases to the new SQL Server overwriting the system databases, then rename the new SQL Server so that it has the same name and IP as the old server. Then bring SQL on the new server online and everything should be working smooth.

Note:

By default, the generated scripts will have @sync_type = N'automatic' => the subscription will need initializing as a brand new subscription. Instead change it to @sync_type = N'none' or @sync_type = N'replication support only' => does not generate replication procs at subscriber.

Obviously, logins and users with appropriate permissions should be created before hand.

References :

• Minimize Downtime When Moving to a New SQL Server Environment
• SQL Server 2008 R2 Upgrade Technical Reference Guide