October 18, 2015 at 6:39 am
I am currently exploring some high availability solutions so I dived into the world of High availability options for VMWare / Hyper-V or at a lower level. This leads me to the question if AlwaysOn is still an interesting option if you have a HA solution for your Virtual Servers. In other words: Why should I buy an Enterprise Version of SQL server if I have my servers protected at a different level? Isn't AlwaysOn something you should consider if you have physical servers?
Wilfred
The best things in life are the simple things
October 19, 2015 at 2:49 am
Firstly, AlwaysOn is a marketing term, not a feature. The two features are Always On Failover Clustering and Always On Availability Groups
I'm going to assume you mean Availability Groups, because Failover Clustering is available in Standard Edition.
Will your VMware solution allow readable secondary copies of the database?
Will it allow for complete failure of the storage array and still work?
Will it allow for an uncontrolled failover in a matter of seconds?
Will it allow for a synchronous copy of the database to be in a secondary data centre in case the entire primary data centre fails?
Will it automatically repair corrupt data pages?
Will it allow you to offload full and log backups to the secondary copy?
Will it allow for failover of individual databases or groups of databases?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2015 at 8:22 am
In addition to what Gail talks about, another thing to consider is a layered approach. Depending on just how responsive and thorough you want your DR process to be, it can be a good idea to have more than one method in place. Too often I've seen people say stuff like "Oh, we've got SAN snapshots, we don't need backups" only to find that they didn't plan for certain types of failures that backups can cover. Same thing with VM methods of DR. They work, and work well. They just don't cover all the possibilities. I'm not saying you have to, or should, purchase Enterprise in order to have additional DR and HA mechanisms. I'm just saying it gives you more tools and more layers to help ensure a complete DR scenario.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2015 at 8:32 am
The only person who can say whether it's worth buying Enterprise edition for Availability Groups is you (well, your business), but consider that HA/DR is like security. Defend in depth, don't place all trust in a single layer.
I've seen people say they don't need Mirroring because they have VMs and a mirrored SAN. Then the SAN controller glitches and both copies of the DB are fried. They only had one layer, so they were toast when that one layer fails.
Ultimately it's a cost- and risk-based decision. How much can you spend, how much risk can you tolerate.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 21, 2015 at 8:05 pm
Don't forget that your applications should support Availability Groups before you start using it.
They can't use MSDTC or cross database transactions. They should prefix agent jobs and copy them across to secondaries. If you use SSRS you need something to restart it after a failover and do job cleanup. You need to develop something to sync your logins and linked servers and anything else that needs to be the same across servers. And then you need to monitor it... and provision enough network bandwidth to send everything from one server all the time to another.
I think they're worthwhile but lots of people set them up without prior planning and work, which is great because it keeps us in jobs.
March 9, 2018 at 6:07 pm
We setup an AlwaysOn configuration on one of our production servers as a test and ran it for 6 months. For our system the expense, complexity and maintenance of AlwaysOn did not make any sense. We can have a crashed virtual server replaced in a hour using a snapshot of the server system drive and restoring the complete, differential and logs to a point in time no more than 10 minutes prior to the crash. For our system and data needs this works fine. If you have a 24x7 system with any data loss causing great expense AlwaysOn might be a good option for you.
In our test AlwaysOn configuration under SQL2012 we were going to use the ability to query against the secondary server as a way to offload reporting. This didn't work as queries conflicted with the AlwaysOn updating causing a backlog and pretty much making the secondary server pretty much unusable as a reporting server. Maybe this has been fixed or reworked in the newer versions of SQL but we were unimpressed with AlwaysOn under SQL2012.
Database disaster recovery solutions are not a one size fits all deal!
March 10, 2018 at 10:21 pm
oughtsix - Friday, March 9, 2018 6:07 PMWe setup an AlwaysOn configuration on one of our production servers as a test and ran it for 6 months. For our system the expense, complexity and maintenance of AlwaysOn did not make any sense. We can have a crashed virtual server replaced in a hour using a snapshot of the server system drive and restoring the complete, differential and logs to a point in time no more than 10 minutes prior to the crash. For our system and data needs this works fine. If you have a 24x7 system with any data loss causing great expense AlwaysOn might be a good option for you.In our test AlwaysOn configuration under SQL2012 we were going to use the ability to query against the secondary server as a way to offload reporting. This didn't work as queries conflicted with the AlwaysOn updating causing a backlog and pretty much making the secondary server pretty much unusable as a reporting server. Maybe this has been fixed or reworked in the newer versions of SQL but we were unimpressed with AlwaysOn under SQL2012.
Database disaster recovery solutions are not a one size fits all deal!
This is not the norm...just FYI. I have many clients with high traffic OLTP systems that utilize the secondary replicas for data warehouse workloads and reporting (one primary replica and two secondaries that handle the DW and reporting needs) that never have issue blocking the replication part of AGs. The version of SQL Server this type of workload is done in is both 2012 and 2014, no issues under either one.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply