Data Synch to a Remote DataCenter

Posted on 2007-08-10
Last Modified: 2013-11-05
I am trying to plan a DataCenter move and hopefully someone that has been through this before can offer some advice.  I have 2 SQL Servers, both are SQL 2000, 1 Enterprise (200 GB of Databases) and 1 Standard (400 GB Database).  The new Datacenter (about 700 mi away) has 2 SQL Servers as well, both Enterprise 2005.  The new DataCenter contains all new hardware; the hardware at the current Datacenter will remain there.

My question is the synchonization.  The Databases are highly transactional and they drive websites, so the web servers will be shut down for a period of time to do the final synch.  Obviously this needs to be as small a window as possible.   Due to the size of the Databases and the fact that the DB servers dont have firewire or usb2.0, copying to external drive and flying it up isnt an option.  That seems to leave either log shipping or replication.  I will have a dedicated pipe from one location to the other.  All the DBs are currently set to Simple for the recovery model, the drives the t-logs are on arent that big (32 GB) and I would be afraid if I changed the recovery to FULL the T-Logs could fill up drive while I was enjoying my weekend.  I dont want it to turn into a management nightmare.  Is Replication my best option?  And if so, which flavor, Transactional?

Question by:99Times
    1 Comment
    LVL 21

    Accepted Solution

    Replication is a lousy option, since not everything can be replicated.

    Best solution is to take the downtime, detach, compress and robocopy all the files to the new place.

    Next best option is to backup, restore in the new datacenter, then use Red Gate's SQL Data Compare to synch the data, one db at a time, then offline the original.

    None of the options are pretty.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now