question

elias_chatzigeorgiou avatar image
elias_chatzigeorgiou asked

Database migration plan

Hi,
     I am planning a database migration from server-A (current server) to server-B (new server). I need to achieve minimum downtime, thus I came up with the below scenario:      I have setup server-B as a subscriber to server-A in a transactional replication scheme. Be adviced that mirroring was not an option in my current setup, since server-B is also a publisher with it's own subscribers (subscr-1, subscr-2, etc).      To ensure that server-B would have all database settings (including autonumber fields) same as server-A, I setup server-B subscriber as "initialize from backup".      The application that server-A supports is written such that it uses a single WRITE (update/insert/delete) server (currently server-A) and multiple servers for READ (select operations). That is it uses any of subscr-X in a round-robin schema for load balancing purposes.      My plan for the actual migration is that I need to action the below:
         a) **stop connections** on server-A          b) **update identity seeds** on server-B                  (e.g. run DBCC CHECKIDENT('tableName',reseed),                  on all database tables that contain an identity field)          c) **change the database connections** to point to server-B (instead of the current server-A)
     I would appreciate any comments/ideas on the above plan? Do you have any concern/suggestion?      Before actually acting this, I need to prepare a management report that describes possible risks and pitfalls. Do you have any comments on what could possibly go wrong? Note that all subscriptions are already in place, the only pending work is actions a,b,c above. Regards
Elias
replicationmigration
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Since I have not attempted moving between servers like this, I'm completely hesitant to answer.
0 Likes 0 ·
paschott avatar image
paschott answered
Elias, If Server B isn't going to be used right away, have you considered just doing a log-shipping scenario? Even if you're not using Enterprise, you can easily just backup database A, setup the transaction log backups to a location accessible by both (or otherwise copy those logs over to a place Server B can access), then apply the logs to Server B with NORECOVERY. We did this when we had to switch to a new set of SQL Servers and it worked really well to just take the final log backup from server A, shut down server A, apply the log to Server B WITH RECOVERY, modify whatever connection strings were needed, and we were good to go. If I recall correctly, we were also able to use DNS aliases for the servers so just changing the DNS alias and propagating that out was enough for the other servers to start using the new server.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
I, like grant, have never tried a migration like that. But I will say it makes sense in principle and I do not see any immediate reason it would not work. A couple of possible risks, all of which I think are unlikely would include: 1. If you shutdown A before all changes to A have been replicated on B, you may lose work. 2. If there are numerous programs/entities/links/etc pointing to A, there is a risk you will forget to migrate some to B. This is especially true if you have code that hardcodes the server name into the source code. 3. There may be unforseen complications in changing the identity of the write server that could take the system offline for longer than expected. If you have sufficient time and enough test servers, you may want to recreate something similar to this production environment and then try this migration technique to ensure it works. What I have done in the past that resulted in some, but limited downtime: 1. Take a full backup and restore the full backup to the new server, the old system can be running without interruption during this part. 2. Begin the downtime and then take a log backup from the original. 3. Restore the log backup and brign the new server into a ready state. 4. Change everything to point to the new server and end the downtime.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.