July 1, 2013 at 4:26 pm
I'm looking for an easy failover technology, in the case where our primary SQL Server 2012 instance goes down.
I have the following limitations:
Two identical servers with internal drives.
No SAN
We have a NAS where backups are placed.
We do have full control over applications accessing the server, but prefer not to add too much custom logic for the failover.
Currently taking a daily full, hourly differential and 15min transaction log.
Our hardware is Dell, though is not certified for clustering. Though it is two servers of the same model and configurations.
I would love to use AlwaysOn, but I read that it prefers shared media (such as a SAN)
Mirroring also suggests shared media, though you you can set up a remote mirror. You can't have an automatic failover in async mode and sync mode would introduce a decent performance hit to the application. As I set up the Async Mirroring w/o a SAN, it struck me that it was practically a hack as it's not intended for two servers next to each other, but for servers in remote locations over a WAN.
Since I'm already taking backups as you would for log shipping that seems like a short step to use log shipping. But it's not automatic failover out of the box.
So why Log Shipping over Async Mirroring or visa versa?
And does AlwaysOn have a non-clustered Async (not shared media). It seemed easier than mirroring until I got to the part where it requires a hardware cluster.
On paper it seems like log shipping does most of what I need within my constraints... but Mirroring and AlwaysOn were developed to go beyond what Log Shipping does. So given my hardware contraints (no san, no clustered servers), are Mirroring/AlwaysOn viable? Do they provide much more than log shipping in this situation?
Also is there any software out there that would help out with this sort of configuration, like maybe using log shipping and adding a layer to handle a failover?
Thank you for any advice, I'll keep researching this 🙂
July 8, 2013 at 12:46 pm
Well I've been looking it over.
While I do have duplicate hardware, it wasn't certified for clustering. I also don't have a spare set of duplicate hardware to test the clustering. That leaves out AlwaysOn.
I'm looking at Red-Gate to help with compression and Log Shipping... I'm just a little torn about the limited availability restoring proprietary backups. 2 production machines and technically like 30 dev machines. If money weren't an issue, it seems like another point of failure/stop point where you have to do further work setting up a server for the first time.
Alternately there is Async Mirroring. looking at things for one db, that looks good. But there are 5+ production db's on the server that interelate a bit. One main one, but it goes to three others for history and two others look at the main db for some data. I really don't like the idea of setting up 5 mirrors to cover this. This is where AlwaysOn would have been great.
My boss wants me to look into this other software that ghosts the hard drive or portions of it, but I'm a little nervous that the failover would be handled totally outside of sql server.
July 8, 2013 at 1:13 pm
You can want it all, but you can't have it all unless you can spend some money on resources. You'll have to make tradeoffs.
If your app goes to other databases for history, how often does this change? If it's not near real time, then I'd use mirroring, and use manual failover. You can manually fail everything over.
Neither AlwaysOn nor mirroring need shared storage. For that matter, neither does log shipping. Log shipping has the issues of managing failover, since it's manual, for servers and clients.
I think if you have resource limitations, log shipping is the simplest. You'll want to play with the failover for clients and have a good procedure ready.
The Red Gate log shipping is intended to make the process easier, and reduce the bandwidth you need by compressing things. We have a utility that always converts our compressed stuff back to native, and you should test it in case you need to do this. You could always un-compress the backups for dev machines and not license those.
Disclosure: I work for Red Gate.
July 9, 2013 at 10:47 am
TL;DR: Use a combination of mirroring and log-shipping, depending on your needs; high-availability should not be the responsibility of the DBA, alone.
From a purely technical standpoint, I find that I always use a combination of Database Mirroring and Log Shipping, depending on the situation I am covering. I understand when a tight budget is a factor here -- we are typically a Standard Edition shop, for example, thanks to low budgets.
If the client wants a higher (perceived) uptime, or we have stricter constraints on maintenance windows, we'll run synchronous Database Mirroring between two servers, with a witness, all within the same LAN segment (don't try to do mirroring over the WAN). The witness component will run reasonably on an Express instance, on one of our VM hosts -- if you have no such host, you'll either want a cheap commodity server or you'll have to co-opt with another service (the number of domain controllers I've has to co-opt in my time, due to budget constraints, is laughable).
The witnessed mirror allows for a basic automatic failover should the primary die, and covers us if we need to manually take it down. At the exact point of failure, existing connections are still going to error out, but reconnection is usually successful.
It is possible to coordinate failover of multiple databases, by configuring alerts against the database mirroring WMI events and running a job to failover all databases, in response to that alert (but it is not immediate enough for any truly mission-critical stuff).
It is important to develop the connecting applications such that they are 'mirror-aware'. This is not particularly easy, but it helps if you can gracefully handle a dropped connection by automatically attempting reconnection; and can reduce any cross-databases dependencies in the system (particularly if modules in one database access objects in another -- you may have to look into abstracting these with linked servers whose connection strings include the Failover Partner tuple).
You should also make sure any Agent jobs are mirror-aware, by at least checking to see if the database is primary before you run your job steps.
If the client wants an offsite DR, we'll run log shipping to our secondary location, accepting the latency of data between the primary and recovery sites. We modify our log-shipping processes from the standard, by configuring the target share as \\localhost\LogShipping, rather than a dedicated NAS. By creating the share on both database servers, we can control the transfer rate of the log-ship (lookup: robocopy /ipg), so we don't flood our WAN.
As far as failover goes, our switch from primary to secondary site is always backed by a conscious, manual decision. There are always additional components to failover (e.g. connecting apps to re-point, users to inform) and the 'cost' of this is often higher than simply fixing the outage on the primary site. As such, automatic failover between sites is not a priority for us.
We do, however, have clients who believe they want the uptime and the recovery, for whom we tend to do a two-onsite-one-offsite setup combining the techniques described above. It's a pain to setup but it works well once it is.
Documentation is key, here. The important thing for us is making everyone aware what the limitations of the solution are -- the developers have to buy in and work as hard as they can to make their applications resilient to the idiosynchrases of the environment; and the client has to buy in, and not overestimate the resilience of the solution (if they do, you should be able to clearly show that they were informed of, and signed off on the limitations). It's not something we can always convey to our salespeople, mind!
Once you properly explain the expense of true high-availability (in both monetary and development-complexity terms) and let the business sponsors battle it out between them, you should find that some of your constraining factors are relaxed (be that, more budget allocated for hardware and/or development, or the RTO/RDO requirements for high-availability are relaxed).
Good luck.
J
November 6, 2013 at 6:45 pm
I think you are coming at this from the wrong angle. You need to define your requirements a bit more, rather than looking at your hardware and asking what you can do next.
Here's some random thoughts you may want to consider:
Log shipping:
It's Disaster Recovery, not High Availability. Make sure that whoever is paying for this knows the difference. Whenever you go to backups you are accepting data loss, and Logshipping is just an ongoing automated process of going to backup. Of course in the right situation you can avoid dataloss, but in a log shipped scenario you are saying "It's okay if I lose some data". The other big disadvantage of log shipping is it is one way. Once you fail over you can't just fail back. You need to re-establish log shipping back the other way, fail over to the original primary then re-establish log shipping back for the next 'disaster'.
That's all a bit negative, but I actually really like log shipping. As a standby you can bring online quickly it is an easy to configure and maintain solution. you just have to make sure everyone knows what they are getting.
Clusters:
Also cool, and they are higher availability. But with no SAN and unsupported hardware it sounds like you've already talked yourself out of this.
Mirroring:
So with mirroring you can fail over to the mirror server and then back again. That's nice for stuff like patching. You can also automate a lot of stuff and as mentioned above fail over in a group if you need to(Note: This is usually not as easy or as good an idea as it first sounds). Big issue with mirroring for me is it's deprecated. It's going to vanish somewhere down the track. Also be aware of the licensing - you need Enterprise if you want to go Async, and if you have standard and want to go synchronous that's going to result in a performance hit. You also are going to have to make some changes to the way your applications connect when you failover. Depending on your shop this can be done with a DNS redirect.
Always On Availability Groups:
From what you've said you are probably going to be thinking of AOAG. From the application side it's a smooth failover(Application is pointed at the listener) and you can fail backwards and forwards as much as you want. We've got a bunch of clients using AG's quite happily now and it's just going to keep getting better. However - you are committing to enterprise licensing, so it depends what you have to spend.
November 7, 2013 at 4:59 pm
What kind of SLA do you have for RTO and RPO? What kind of data loss is acceptable?
Any budget for virtualization?
How do your app servers connect to the databases? Are all the connection strings hard coded IP addresses, odbc, ect? Are the connections easy to change?
There are some folks that would argue Replication and Log Shipping may be acceptable for HA.
http://www.sqlskills.com/blogs/paul/in-defense-of-transactional-replication-as-an-ha-technology/
I'm just guessing here, but those may be combined with obfuscation applications like greensql might be able to handle automatic failover.
November 8, 2013 at 8:48 am
As you are already running SQL 2012, have you considered Window Server 2012? You could roll your own "SAN" by taking advantage of the SMB 3.0 improvements in Windows Server 2012 and the ability to create shared storage for a SQL 2012 cluster on an file share. MS had some numbers posted where they were getting near native storage performance with SQL running on file shares.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply