3

I need some assistance in developing a backup strategy for my SQL Server deployments. Currently I have two classes of customer: Hosted customers, whose data is housed in our datacenter, and On Premises customers who host their own data locally.

We are currently exploring whether to use Log Shipping or Database Mirroring for our Hosted customers as data loss of up to 15 minutes is acceptable (Log shipping interval would be 15 minutes, Mirroring would be in High Performance Mode), but we are still conducting testing on that.

My question is, if we choose a Mirroring strategy for our hosted customers, I doubt we would be able to do that with our our OnPrem customers - they will likely be using log shipping. Is it possible to have multiple recovery strategies database-by-database, or would I need a new instance of SQL Server for the OnPrem customers?

EDIT - Changed title to "Availability Strategies" because, well, this is more about availability than recovery.

Improve this question
8
  • Are you talking about availability or recoverability? Those are two different things, and depending on what you mean then Log Shipping and DB Mirroring might be way over kill.
    – Thomas Stringer
    Commented Apr 9, 2012 at 17:56
  • DB-by-DB, sure. So you're trying to build a separated server instance for each onprem customer and wondering what model to implement?
    – Eric Higgins
    Commented Apr 9, 2012 at 17:59
  • @shark I need a business continuity strategy, so would availability I guess availability would be a more apt title? Basic goal is to provide a Disaster Recovery solution with an RPO of 15 minutes and an RTO of an hour or less; and things other than DB recovery need to happen.
    – Gyrfalcon2138
    Commented Apr 9, 2012 at 18:04
  • @Eric Higgins Ideally it's one instance with everyone in it, but I'm not sure if I can have mirroring on some DBs and Log Shipping on others within the same instance.
    – Gyrfalcon2138
    Commented Apr 9, 2012 at 18:06
  • One instance you are hosting as "dr" for your offsite customers? Is that what you are thinking? Trying to understand what you intend.
    – Eric Higgins
    Commented Apr 9, 2012 at 18:10

1 Answer 1

Reset to default
0

Database Mirroring isn't a database recovery strategy. It's a high availability strategy. If you can afford to loose up to 15 minutes worth of data then you take transaction log backups every 15 minutes.

If you need to look into high availability that's a totally different conversation, but it doesn't remove the need to be taking transaction log backups.

As for your self-hosted customers, they are responsible for setting up their own backup solution unless your contract states that you'll work with them to set one up for them.

Improve this answer
5
  • We are already taking 15 minute T-Log backups, the question is more an availability one, so I apologize for the misleading title. We are contemplating offering DR services to OnPrem customers as part of this exploration - I'm basically doing an R&D effort right now.
    – Gyrfalcon2138
    Commented Apr 9, 2012 at 18:06
  • Then database mirroring or log shipping would be the way to go. If you need to control the amount of data loss then log shipping as with async mirroring you've got no control over the amount of data loss in the event of a failure.
    – mrdenny
    Commented Apr 9, 2012 at 18:14
  • I would like to do a lab experiment to determine what our sync-gap would be with Async Mirroring as we generally don't do big commits; just a lot of little ones (1 work item generates about 12 single-row insert statements and each database can have between 1-150 work items per 15 minute window depending on customer size)
    – Gyrfalcon2138
    Commented Apr 9, 2012 at 18:29
  • That is definitely do able, but you'll need to be able to generate a fully size production workload to properly test it.
    – mrdenny
    Commented Apr 16, 2012 at 0:33
  • Yeah, and I'm not sure that's in my budget, so we'll probably stick with T-Log Shipping.
    – Gyrfalcon2138
    Commented Apr 16, 2012 at 19:55

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.