Migration plan using replication

Question

Hi,
   I am planning a database migration from server-A to server-B. To achieve minimum downtime, 
I have setup server-B as a subscriber 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 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

Answer 1

Hi Elias,

>> I have setup server-B as a subscriber in a transactional replication scheme
According to your “diagram”, it seems that you want to synchronize the data from server-A to server-B which has many subscribers. If so, please try to use server-B as a distributor for copying initial snapshot files and the transactions to Subscribers. Please see this article for your reference: Replication Model.

>> My plan for the actual migration is that I need to action the below
Base on description, your plan which including three actions is regarding to SQL Server High Availability Solutions. High-availability options include: Failover clustering, Database mirroring, Log shipping, Replication, Scalable shared databases.

Replication lets a primary server, referred to as the Publisher, distribute data to one or more secondary servers, or Subscribers. Replication enables real-time availability and scalability across these servers. For more details, please refer to this article: High Availability Solutions Overview.

To achieve your plan, you can try to use the following High-availability options:
• Failover Clustering
A failover cluster is a combination of one or more nodes(servers) with two or more shared disks, known as a resource group. The combination of a resource group, along with its network name, and an internet protocol (IP) address that makes up the clustered application or server, is referred to as a failover cluster or a failover cluster instance. To install a failover cluster, you must be a local administrator with permission to log on as a service and to act as part of the operating system on all nodes in the failover cluster. Please see this article for installing a Failover Cluster: Installing a Failover Cluster.
• Database Mirroring
Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. Please see this article for setting up Database Mirroring: Setting Up Database Mirroring.
• Log Shipping
Log shipping allows you to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. Please see this article for enabling log shipping: How to: Enable Log Shipping (SQL Server Management Studio).

---

Best Regards, Ray Chen

Answer 2

You need to ensure that the indexes, triggers, views, etc and all jobs, linked servers,etc will be identical on the publisher (Server A) and Server B.

Then you need to ensure that the data is in sync.

That been said, I have done similar deployments/migrations to the one you are attempting and as long as you pay attention to the above caveates you should be good.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941