3

We are going to migrate our data center from one state to another in next 3 months. I wanted to know, what is the best possible way to migrate one of our biggest database (i.e. ~7TB in size) as quickly as possible. Here is the information on the server and database:

  • Existing Data-Center: Georgia
  • New Data-Center: New Jersey

  • Server OS: Win 2008 R2 Datacenter

  • SQL Server: 2008 SP1 Enterprise Edition
  • Data Storage: XIO System (Which we are not going to get it at new data-center)
  • Database Size: ~7TB
  • How data gets into this database: via Transactional Replication from other 8 cluster servers.
  • How 8 cluster is getting data: via Merge Replication from all our 2000 store servers every 30mins.

We will have a network pipe between our existing and new data-center, but it is not going to be that big. It is also going to get consumed by all the VM's our infrastructure team is going to transfer on daily basis.

We ran a Full Backup (i.e. Ideara SQLSafe with iSpeed settings) on this ~7TB of database, it took ~9 hours and size was ~630 GB. We checked with the vendor who is managing our colo migration and network pipe between two data-center and they said, it won't be possible to Copy over 640 GB file faster to new data-center and we would have to do Full + Differential + TLOG to get up to date.

Our issue is, data we are getting from all our store systems every 30 mins (via Merge Replication to Home Office), it generates lot of data change. If we try to do Full + Differential + TLog, we will be having same sizing issue and lot of downtime to copy over Differential and also we would have to deal with the Log file getting full, which we have limited space on our existing database server (as we are not getting XIO Storage anymore). We were thinking about SAN Replication, but, as our SAN is going to be different at the new data-center, that won't work for us.

What approach you guys think we should take to migrate this ~7 TB of database from our existing data-center to new data-center with minimum downtime?

**Note:Please let me know if I miss anything from the information side.

Thank you,

HP

closed as too broad by Shawn Melton, Marco, mustaccio, joanolo, miracle173 Jun 21 '17 at 20:20

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 2
    What is your business tolerance for "minimum downtime"? Do you really mean hiccup barely noticeable to users? Or is weekend outage involving next-day FEDEX of the backup disk to the new datacenter acceptable? – BradC Jun 12 '17 at 20:02
  • 2
    Hi BradC, last time when this database server rebooted, it went into recovery for 12hours (we had VLF's issues, which we fixed it). Our online business was dependent upon this database, but, it was not the show stopper for our online application. So, we can say, 12 hours from our past experience, but business would like to bring this database back online in 6-8 hours. Thank you. – NismoGTR05 Jun 12 '17 at 20:08
6

One could write a book on this topic, but one option would be an out-of-band initial replication of your database to the new site by flying a copy of the server hard drive(s) to New Jersey, followed by log shipping until you catch up, followed by setting up your "transnational" replication to target both old and new sites until you're ready to switch.

  • First off, sorry guys, I was on vacation so didn't respond quickly. We are going to do the following, which is mixture of everybody's suggestion: 1. Build New Server in NJ, install SQL with same version 2. Add this new server as Subscriber under existing Publication under cluster servers 3. We will sync them up (i.e. 1 Cluster at a time) to the new server (i.e. new subscriber) 4. Once clusters are all caught up, we will pick one weekend and do a cut over for rest of the DB on the server Thank you so much everybody for your input and Great! suggestions. Thank you – NismoGTR05 Jun 26 '17 at 17:01
1

Suggested plan: (1)Set up the databases at new location and bring the online. (2)Perform SQL replication between old and new locations. (3)Bring the new location to production environment. (4)Bring the old location offline.

0

Since this is a planned move, you may want to look into using Mirroring to move the data to the new location. Yes, the initial set up will take time when you restore your FULL & DIFF, but once you configure mirroring use the ASYNCH option so the data can flow until you are ready to failover. Right before the planned window, switch to SYNCH and then fail over the db. This will also be beneficial if you are planning to move to a newer version of SQL. This way you can take the time to let your network pipe move the data. Your biggest issue will be to migrate the replication to this new DB.

Not the answer you're looking for? Browse other questions tagged or ask your own question.