October 30, 2014 at 7:28 am
We're refreshing our infrastructure and I want to take advantage of the situation to finally set up a high-safety/disaster recovery model for our databases. We are a small shop and don't see high volumes of traffic, but the data we store is of great importance and we can't afford much downtime. We're upgrading fro 2008 to 2012/14, and I'm pretty new to the dedicated DBA role, so I have a few questions I hope everyone here can help answer.
1. Does every AlwaysOn solution require windows clustering to be set up?
2. Is the 2-node support in Standard enough reason for upgrade to Enterprise? I was originally thinking of having one production box and one or two mirrors with auto failover.
3. Is a simple mirroring solution (with a witness) still an option, despite being deprecated? Is there a reason for an org our size not to do this? It seems like AlwaysOn requires a more significant investment of hardware and licenses.
I want to get this right the first time, so I'll gladly accept any advice you can provide.
Thanks for the help!
October 30, 2014 at 8:05 am
Mark Harley (10/30/2014)
We're refreshing our infrastructure and I want to take advantage of the situation to finally set up a high-safety/disaster recovery model for our databases. We are a small shop and don't see high volumes of traffic, but the data we store is of great importance and we can't afford much downtime. We're upgrading fro 2008 to 2012/14, and I'm pretty new to the dedicated DBA role, so I have a few questions I hope everyone here can help answer.1. Does every AlwaysOn solution require windows clustering to be set up?
2. Is the 2-node support in Standard enough reason for upgrade to Enterprise? I was originally thinking of having one production box and one or two mirrors with auto failover.
3. Is a simple mirroring solution (with a witness) still an option, despite being deprecated? Is there a reason for an org our size not to do this? It seems like AlwaysOn requires a more significant investment of hardware and licenses.
I want to get this right the first time, so I'll gladly accept any advice you can provide.
Thanks for the help!
1) Yes.
2) There is no support for Always On in Standard Edition of SQL Server. But you get database mirroring, and that works just fine for MANY needs!! So does Log Shipping. So does a traditional Failover Cluster, although with that you need shared storage (or SiOS DataKeeper Cluster Edition).
3) See above. I believe that many out there who decide they need AGs really just WANT them - and there is an almost 2X cost increase to get them over mirroring. They also have a SIGNIFICANT amount of gotchas, provisos, limitations, etc. Database Mirroring will work on your server for decades. I still have clients running SQL 2000, and many servers running SQL 2005.
If you TRULY want to get this right, you REALLY need to hire a qualified professional to assist you. There are SOOOO many topics/issues/etc involved with HA/DR, and if you screw it up you can literally lose the company (and yes, I have seen that happen).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 30, 2014 at 10:09 am
On top of what Kevin has already provided,
Mark Harley (10/30/2014)
2. Is the 2-node support in Standard enough reason for upgrade to Enterprise? I was originally thinking of having one production box and one or two mirrors with auto failover.
What you're referring to here is a Failover Cluster Instance of SQL server, this is vastly different to an AlwaysOn Availability group setup. They both require a Windows Server Failover Cluster and can combine together, but they work differently.
Standard edition of SQL Server will support only 2 nodes for a Failover Cluster Instance.
AlwaysOn Availability groups (which require Enterprise edition) support 1 primary and up to 4 secondarys for 2012 or 8 secondarys for 2014.
See more in my AlwaysOn Stairway series starting at this link
http://www.sqlservercentral.com/articles/FCI/107536/[/url]
Mark Harley (10/30/2014)
3. Is a simple mirroring solution (with a witness) still an option, despite being deprecated? Is there a reason for an org our size not to do this? It seems like AlwaysOn requires a more significant investment of hardware and licenses.I want to get this right the first time, so I'll gladly accept any advice you can provide.
Thanks for the help!
Mirroring does not provide the readable secondary option, although with Enterprise edition of SQL Server you may use database snapshots.
Do you require a readable secondary?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 30, 2014 at 11:11 am
A readable secondary is a nice-to-have for reporting purposes, but isn't strictly necessary. I don't think the load generated by reporting will have much impact on production since reports are only generated quarterly and we don't have a high number of transactions. We've gotten away with only having a single instance running everything for years, and while I want to get away from that configuration I do recognize that they haven't had any serious problems. Yet.
it sounds like, due to the small scale of our operations, mirroring may be the way to go. It would keep the costs down and be more manageable for us. I'll also explore the possibility of getting someone more experienced in to help with the design and set up.
Thanks!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply