Easy replication for SQL Server: Can it be set up like mongodb or Oracle?

Posted on

Question :

This might look like a noob question… but it isn’t. (I have 18 years of SQL Server experience, and three years of MongoDB experience.)

The basic scenario is:

  • — Data Center has big, primary SQL Server 2014 Standard Edition instance
  • — Need a warm spare off site (actually, need one warm spare in rack, and one or two off site)

(“Warm Spare”: Can be up to 5 or 10 minutes behind. While the primary is live, the spare is not used operationally in any way. Is there, and automatically is within 5-10 mins of current. Manual cutover is needed to bring it on line and use it.)

With SQL Server, to set up a warm spare via log shipping, it is not a 15 minute task. I need to go through a lot of steps, to get log shipping set up:

  • — networking (open firewalls for multiple ports for windows file share) (this is ntfs/smb over WAN — non trivial)
  • — file system shares for SQL Agent users, local and remote rights.
  • — OS users with rights, rights locally and remotely
  • — SQL Agent
  • — Jobs to create tlogs, and retrieve and apply tlogs
  • — initial setup tasks (restoring backup and first kit of logs)
  • — and now is there an easy way to see that the replica has data within past 10 min? actually…. no. (and this needs to be monitored at application level… it is easy w mongo!)

In the past, I used redgate sql backup which helps many of these tasks. But with SQL 2012 (and backup compression), we have dropped the redgate tool.

But log shipping is still quite a bit of setup work. Further, in some environments (like the Amazon RDS SQL Server service), I have %100 access to SQL Server, but zero access to the OS. Without OS access, I can’t even begin to set up the backup server.

I was talking to a colleague (who is more Oracle centric), who said, “Either you have sipped too much Microsoft Coolaid, and sadly think that the limitations you describe are normal, or SQL Server has an easier way.”

I think (hopefully correctly!) that SQL Server is world class.

So the question: Is there an easier way to set up an off site, warm spare SQL Server?

On Mongodb, it is REALLY EASY, and zero access to the OS is needed:

  1. Make sure a single port is open between two servers
  2. Tweak a conf file (which can be done via Mongo commands)
  3. rs.initiate() (Mongo command)
  4. rs.add(“backup-db-server”) (mongo command)

Done!

My colleague indicates that Oracle has a similar mechanism.

So my SQL friends:
Can SQL Server 2012+ Standard Ed match Mongo, or get in sight of Mongo, for simplicity of replication setup? (And if not SQL Server 2012, then what version and how close?)

Caveats:

  • SQL Server Enterprise edition doesn’t count // for that $$, you can hire monkeys to write the data down and send off site in the mail
  • SQL Server DB mirroring not allowed — it is now deprecated. Further, you cannot do one-to-many with it (which log shipping can do fine)
  • SQL Server Always On does not apply. It costs HUGE $$, is fantastically complex, and requires extensive OS interaction (a consultant I spoke with said it is a $1 million project to do by the time all the SW, OS licensing and admin time are added up)
  • When I say “simple” I mean objectively simple. Simple like mongo mirroring is simple. Simple like redis mirroring is simple. (Simple for a full time SQL DBA does not count, unless the steps can be followed by a non-DBA following a few steps that always work!)

To reiterate: The model here is mongodb, which allows setting up 1-1 or 1-many replication quickly, easily, and simply. We are asking the question: Is there a path for SQL Server 2012 or higher, Standard Edition or lower, for 1-1 and/or 1-many replication that is quick, easy and simple.

Updated Provisional Answer: (Updated October, 2013) “Asynchronous Database Mirroring (High-Performance Mode)” appears to be the ticket. But it is not the real path because (a) It is not available in SQL 2008 Standard Edition; (b) Per MS docs (http://technet.microsoft.com/en-us/library/ms189852.aspx ) it is being phased out of the SQL Server product in favor of always-on. (And as mentioned, always-on is a complicated, expensive, non-starter.)

Bottom line: On SQL Server, you are stuck with log shipping and all the steps and infrastructure it implies. The SQL Server team does not seem to be paying attention to the growing number of open source solutions that really solve for easy setup of passive and active mirrors.

Answer :

There seems to be an MS Feedback suggestion of this kind:

https://connect.microsoft.com/SQLServer/feedback/details/3141149

Leave a Reply

Your email address will not be published. Required fields are marked *