As the number of accounts increased for one of our client, it became difficult to mange the load by single SQL Server as SQL server can be vertically scaled but not horizontally. So we came up with the idea of adding another server in system and moving half of the accounts to the new server. But all this has to be done without down time and that was a big challenge.

This new process was designed to migrate some accounts and their related data from all the tables from Source Server into Destination Server.

The Account migration requirement can technically be broken into
A. Bulk Account Migration to be done initially where we want to move large number of accounts from a source server to a new destination Server.
B. Individual Account migration between the Servers, which can happen at any time because of various business reasons.

Below is the work flow for initial Bulk copy of accounts:

 

Below is the detailed work flow of the SSIS package used to Bulk copy the Accounts:

Detailed Design of SSIS Package:

 

Below diagram depicts the state of an individual account during migration:

Account State Diagram Across Servers