June 22, 2012 at 5:52 am
Hi,
I am planning a database migration from server-A to server-B. 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).
A "diagram" that depicts the above setup follows:
server-A ----> server-B
+----> subscr-1
+----> subscr-2
...
+----> subscr-N
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/-B 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 uses 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
June 22, 2012 at 12:38 pm
Might I recommend a shift in approach. In my opinion Log Shipping (LS) is better suited for this kind of work given that it is transactionally consistent. This property translates into you not having to worry about identity columns being synced, as well removing any risk of someone or something introducing data into databases on Server-B prior to the migration. In a LS migration your steps would be:
1. Setup LS from ServerA.Database1 to ServerB.Database1 where ServerB.Database1 is in Standby/Readonly mode.
--- at migration time ----
2. Backup tran log of ServerA.Database1 WITH STANDBY. This will sever all active connections and make ServerA.Database1 Standby/Readonly as well. Now both databases are in Standby/Readonly mode, i.e. no one can write anything to either database.
3. Sync Server Logins so you know ServerB has all the Logins ServerA has, including bringing the SIDs and passwords over intact.
4. Restore tran log backup taken in Step 3 WITH KEEP_REPLICATION, effectively bringing ServerB.Database1 online. You will re-enable replication from ServerB.Database1 as a publisher to the pre-existing subscr-1 - N as subscribers as you diagrammed in your initial post per this article: http://msdn.microsoft.com/en-us/library/ms151224(v=sql.90).aspx
5. Repoint your app to write to ServerB (and round-robin read from subscr-1 - N if those subscriber instances will be different post-migration).
All of these steps can be scripted ahead of time, and tested as such. Your backout plan will be to recover ServerA.Database1, re-setup replication to the subscribers and repointing the app to ServerA.Database1, effectively pushing ServerB.Database1 out of the loop once again.
Hopefully all of that made sense, it is Friday 🙂 If anything I said is unclear please let me know.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply