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.

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.

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 StringerApr 9 '12 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 HigginsApr 9 '12 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.
–
Gyrfalcon2138Apr 9 '12 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.
–
Gyrfalcon2138Apr 9 '12 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 HigginsApr 9 '12 at 18:10

1 Answer
1

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.

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.
–
Gyrfalcon2138Apr 9 '12 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♦Apr 9 '12 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)
–
Gyrfalcon2138Apr 9 '12 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♦Apr 16 '12 at 0:33