SQL 2005 warm site replication

Posted on 2011-10-27
Medium Priority
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
  • 3
  • 2

Expert Comment

ID: 37041984
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

ID: 37042093
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?

Accepted Solution

MrAli earned 2000 total points
ID: 37042149
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.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Closing Comment

ID: 37042176
Thanks - that is great advice

Expert Comment

ID: 37042545
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?

Expert Comment

ID: 37042765

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.  

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

On July 14th 2015, Windows Server 2003 will become End of Support, leaving hundreds of thousands of servers around the world that still run this 12 year old operating system vulnerable and potentially out of compliance in many organisations around t…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

850 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