7

We have our Primary SQL Servers in-house (15 primary servers at the moment (500 databases approx overall), most of the servers have hex core processors).

These are mirrored to other backup servers in a near-by building (via a dedicated 0ms fibre link).

What we'd like to do is maintain a live copy of our data in an off-site data centre miles away (as part of a much bigger DR project - i.e. if there is a comms issue in our area).

Currently we use SQL Server 2008 R2 - Standard edition.

Obviously using the "Standard Edition" of SQL Server 2008 R2 limits us to Synchronous mirroring as asynchronous is an "Enterprise Edition" feature.

We've done testing with offsite synchronous mirroring but the latency just made it a no-go.

I'd love to upgrade to SQL Server 2012 Enterprise and implement AlwaysOn availability groups, but the company is not willing to spend the £100,000+ to upgrade the SQL licensing (2012's per core licencing + our hex-core processors = epic fail) - they're not even willing to spend the money to upgrade to 2008 Enterprise so that’s asynchronous mirroring out of the window too.

So, with my hands tied by these financial constraints i'm stuck with 2008 R2 Standard edition.

The only options left open to me are log shipping and replication (correct me if I’ve missed anything) - Log Shipping is crude - but when managed properly can be feasible - so we've got that on the back burner for now.

My Question(s):

  1. Does SQL Server 2008 R2 Standard have all the replication features that we'd need to do off-site replication?
  2. Would the replication of in-house servers to an off-site data centre (20-30ms latency) be feasible without impacting performance on the primary server in any way (would we be ok setting the Replication Distributor on the "Principle" server)?
  3. Given that replication is not an instance level replication solution - would the management of this be more than 1 DBA could handle? (given that i'm already managing 15 Principle servers & their Mirror counterparts - admittedly with lots of automation)
  4. Would replication be possible from our primary without impacting the existing mirroring configuration - it's my understanding that best practice is to mirror the Subscriber as opposed to the Publisher - is this correct?
  5. Is there anything else Replication Virgins forget that you need to bring to my attention?
Improve this question
1
  • Is the idea to continue to maintain the local mirrors, or dump them when you implement the remote copy? What is your RPO and RTO for this project?
    – Jon Seigel
    Feb 14, 2013 at 18:39

2 Answers 2

Reset to default
5

Based on what you have described Log shipping is going to be the way to go. Log shipping is old school, tried and true.

Replication is pretty fragile, provides massive amounts of room for failure especially when it comes to adding new tables and getting those tables into the replication topology.

As for 2012 upgrade costs they may not be as bad as you think. If you have software assurance and an enterprise agreement that'll save you a bunch of money on licensing.

Improve this answer
3

Log shipping might be "crude" (I prefer to call it straightforward), but replication is complex and, in my experience, brittle. You don't want to add complexity if you can avoid it. You don't want to have to go through some sort of heroics to fix a broken replication setup.

Managing the replication configuration of all of those database would be a bear of a problem. You can't use the forms of replication that would alter database tables, as I suspect testing those changes on 500 databases is a very large project, if not a career, in and of itself. You can start to have performance problems on the subscriber side that require you to start looking at fillfactor, maintenance plans and other, similar complications. What happens if you need to flip over from your primary servers to your "local" secondary servers? Are you going to re-do all of the replication configuration? That's likely to be a big task. If you automate it, that is time spent creating and testing the automation, and then it needs to be looked at when you change those 500 databases.

Recall that all we had for DR was log shipping up until SQL Server 2005. It's not glamorous, but it works.

TL;DR- Simpler is better. Use log shipping.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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