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 2008-R2 Standard Edition instance
- -- Need a warm spare 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 backup server, 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)
- -- file system shares
- -- OS users with rights
- -- SQL Agent
- -- Jobs to create tlogs, and retrieve and apply tlogs
- -- initial setup tasks (restoring backup and first kit of logs)
I often use redgate sql backup which helps many of these tasks.
But it 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 there is 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:
- Make sure a single port is open between two servers
- Tweak a conf file (which can be done via Mongo commands)
- rs.initiate() (Mongo command)
- rs.add("backup-db-server") (mongo command)
My colleague indicates that Oracle has a similar mechanism.
So my SQL friends: Can SQL Server 2008/2008-R2 match Mongo, or get in sight of Mongo, for simplicity of replication setup? (And if not 2008, 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 -- the solution sought is for simple, warm off site backup that can be many minutes behind sync, not fancy stuff like mirroring
- 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 always on by the time all the SW, OS licensing and admin time are added up)
- When I say "simple" I mean objectively 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 2008 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.