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)


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?)


  • 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.

  • Why have you ruled out mirroring so quickly? It's very straightforward (and quick) to setup - I wouldn't describe it as complex at all.
    – georgeb
    Sep 24, 2013 at 11:11
  • 2
    I was going to suggest async mirroring, but that's Enterprise edition. Transactional replication is a possibility. It depends how clean and stable your schema is. I'm kind of questioning what you're doing here because mirroring is way easier than any kind of replication. One thing I would caution about, though -- different RDBMSs implement things differently, so trying to shoehorn one RDBMS solution model into another RDBMS may not work. Start with business requirements, then see which RDBMS can solve that problem most effectively.
    – Jon Seigel
    Sep 24, 2013 at 13:06
  • @JonSeigel This is not about db server technologies.... it is about SQL Server. Thanks! Sep 25, 2013 at 3:39
  • @georgeb_dba mirroring: Strike 1: Requires a witness server. Strike 2: Requires restoring a backup. Strike 3: Folks sometimes get reliability problems when things go funny. Strike 4: Isn't mirroring gone now in SQL 2012? Sep 25, 2013 at 3:41
  • 1
    @samsmith 1. No it doesn't - only if you need automatic failover 2. Yep, this is true, you would have to do a one-off backup/restore 3. I'd be genuinely interested to see what sort of problems - have been using it for 3/4 years in production environments without issues 4. No - although it will be removed in a future release. Still around to use at the moment though. I'm not saying it's perfect or even the right choice for your environment but I felt it warranted a bit more of a look. I'm not sure anything exists in SQL Server that provides exactly what you want.
    – georgeb
    Sep 25, 2013 at 8:54

1 Answer 1


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


  • Dead link now, unfortunately
    – andrewb
    Sep 12, 2018 at 0:00

Your Answer

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

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