2

I'm a software dev with little experience managing SQL Server so please bear with me.

We are developing a piece of software that needs to be highly available, with a significant degree of resilience. This application will use SQL Server to store much of its operational data.

This application will be running from 2 separate physical locations and no shared disk (SANs or whatnot) is allowed between these 2 locations, shared SMB is a possibility but not well liked by our security people.

This application won't commit loads of data to SQL Server, but there will be 26 instances of this system deployed in the real world, 52 SQL Server instances altogether so to cut costs we are pretty sure SQL standard is as expensive as we can afford.

What are my options, since Database Mirroring seems to be being obsoleted in the not too distant future?

EDIT We have the possibility for a SAMBA shared to be replicated between our sites using DFS-R, does this open up any windows? With SQL 2014 STD, you can set up AlwaysOn for 2 nodes is this a viable approach?

Basic Application Model

  • You are developing the application, so it may be best to get the application to write the data to both SQL databases, maybe making use of a transactional message queue. If entity framework makes this hard, then don’t use it for updates. – Ian Ringrose Aug 16 '14 at 20:17
3

As per this BOL reference, here are the only options you would have with Standard edition:

  • Log Shipping
  • Database Mirroring (sync/safety only)
  • 2-node Failover Cluster Instance

Because you don't want to use shared storage between different participants, that would automatically rule out the failover cluster instance, leaving you to choose between log shipping and database mirroring.

Those are two very different solutions, with two very different needs driving them. What are your requirements for failover? Reporting? How many copies of the data do you want? Until those questions are answered, you wouldn't be able to choose between these two rock-solid solutions.

EDIT

I must have completely missed your requirements in your question, but with Paul's excellent edit and graphic there, it seems as though a lot of the requirements are already there (although you don't seem to point out reporting or warm data, so I'll assume that's not necessary).

Physical Separation
You could get yourself into trouble here with database mirroring if you're talking about cross-datacenter or geographic separation type of distance, as the restriction for only sync/safety mode could introduce performance implications.

Failover Tolerant Application
Unless you have the logic in your application, log shipping wouldn't be able to help you here. With database mirroring, depending on your provider and capabilities you could specify the failover partner though.

Failover (preferably automatic)
This an easy one. Log shipping doesn't have this capability, you'd have to manually intervene. Database mirroring would be able to fulfill this.

No Shared Storage
Both of these solutions do not require shared storage.

Must work with EF
I don't see why EF would have a problem with database mirroring, but I couldn't say for sure on this without doing more EF research.

SQL Server 2014 Standard
If you mean Standard edition, that is answered above. If you mean "standard" as in "current", then database mirroring is currently deprecated for what it's worth.

| improve this answer | |
0

Database Mirroring is your option. The only other option is log shipping and I'd recommend Mirroring over log shipping any day of the week.

Shared SMB isn't a good option as if one site goes offline you may no longer have access to your production data.

| improve this answer | |
0

Although Mirroring is deprecated, it's almost textbook for what you want to do. Keep in mind though, if you want automatic failover, you'll need a witness server, as well as your two database servers.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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