SQL 2005 warm site replication

Posted on 2011-10-27
Last Modified: 2012-05-12
We have an SQL 2005 Standard server we need replicated to our DR site. It is installed on Windows Server 2003 Standard. The DR site has an exact duplicate of this same machine (meaning the server was imaged to similar hardware and placed at our DR facility). The only difference between the two computers is location, subnet and machine name. The machine had to be renamed because our DR site also has our active directory schema. Both sites are in the same AD.

What I need to do is make sure the SQL server at DR is up to date and ready to use in the event of an emergency.

Any help or advice would be greatly appreciated.
Question by:JohnMac328
    LVL 7

    Expert Comment

    Ok, did you rename the SQL server internally after renaming the host server by doing a sp_dropserver sp_addserver?  Also, how are you copying data across?

    Author Comment

    No to the first part and actually I would like your advice as to best practice.  What do you think would be the best way to tackle this?
    LVL 7

    Accepted Solution

    First thing first, read this on what to do in SQL Server after renaming the host server:

    Something it doesn't go through is the users in SQL Server.  They will be referencing your old server name.  Make sure you create a backdoor SQL Server user for an admin, making them a part of the sysadmin user role and test the SA user to make sure you have the right password and can connect.  Go through and add the WINDOWS users you want after you renamed the server, remove any users that reference the old windows server name in SQL Server Management Studio - Server - Security.

    That's just a background that should be handled.  To answer your question, you'll most likely end up going with transactional replication, mirroring, or log shipping.  Transactional replication will keep you in near real time with the database on the primary but has 5 jobs you have to maintain and can cause issues such as your distribution db (which is created by transacational replication) growing fast.  This requires the most maintenance, but keeps you synched in near real time (seconds delay usually)

    Mirroring will also keep you near real time and provide application level failover if you setup a witness server.  The Witness server has some additional maintenance overhead but provides some auto failover.  You don't need to absolutely use this though, I'd say read up on it to see which you prefer.

    Log shipping is the easiest to do, has the least administrative overhead, but your database will probably be 10-15 minutes behind.

    All of these have tutorials on youtube as well which I'd recommend looking.  Let us know if you want more info about a specific technology.  SQL 2012 will provide "Always On" which is a combination of all of that and clustering, but that's going to be out in a few months.  Pick a technology you think suits you and let us know about your follow up questions if you'd like.

    Author Closing Comment

    Thanks - that is great advice

    Expert Comment

    MrAli, could it be possible to stop all SQL services on the DR serverand keep the server up to date using a replication software like peersync or rsync. copying the databases and transaction logs to the warm site then starting the SQL services when the server was needed?
    LVL 7

    Expert Comment


    Unfortunately no, that's not needed.  SQL Server works on a transactional level and the database data files (MDF/NDF/LDF) are really picky about how they are handled.  Just today I saw yet again, another issue of someone totally corrupting their database by moving the MDF files from fast to slow disks, having to restore from backups and start over again.

    You want to apply the transaction logs to the server and have it running, that way they are also immediately ready when needed.  What is the issue or business case you're trying to solve?  SQL Server offers many solutions, and there'll be a good safe one for your scenario.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Scenerio: You have a server running Server 2003 and have applied a retail pack of Terminal Server Licenses.  You want to change servers or your server has crashed and you need to reapply the Terminal Server Licenses. When you enter the 16-digit lic…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    737 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