Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

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

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

share|improve this question
    
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_dba Sep 24 '13 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 '13 at 13:06
    
@JonSeigel This is not about db server technologies.... it is about SQL Server. Thanks! –  samsmith Sep 25 '13 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? –  samsmith Sep 25 '13 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_dba Sep 25 '13 at 8:54

1 Answer 1

Based on your caveats and desired level of complexity SQL would not be a good choice for you. Mirroring is far and away the easiest of the SQL standby methods, using only T-SQL and one TCP port.

share|improve this answer
    
Jason, not a useful answer. The question is not whether "SQL Server is a good choice" The question is: "How can SQL Server set up a warm backup easily and quickly." –  samsmith Sep 24 '13 at 15:00
    
Jason, mirroring looks great, but async mirroring is only in enterprise edition, AND it is being phased out in favor of always-on (which is a complicated, expensive animal). Seems like the SQL Server product team leaves easy active and passive sparing to open source DB's. –  samsmith Oct 27 '13 at 19:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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