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):
- Does SQL Server 2008 R2 Standard have all the replication features that we'd need to do off-site replication?
- 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)?
- 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)
- 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?
- Is there anything else Replication Virgins forget that you need to bring to my attention?
Best Answer
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.