October 23, 2014 at 11:31 am
Hi all, I am a dba for a school district. We are purchasing a new application that has a sql backend for both sharepoint and the application. The instance has about 20 different databases. We have a "dr" site across our town at another data center for peace of mind. Presently we do file copies out there for our various systems. I was going to start a log shipping or mirroring strategy on this instance to a vm out there, but with 20+ databases don't know if this is the right path. I have spoken to our sysadmins about availability groups and they seem to not want to deal with the windows clusters. Is there any way to get instance level backups out there or do I have to set up mirroring and/or log shipping on each database? Thanks.
October 23, 2014 at 11:37 am
before you even consider alwayson groups do you actually have the appropriate windows server and sql server licences?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 23, 2014 at 11:38 am
mlorek (10/23/2014)
Hi all, I am a dba for a school district. We are purchasing a new application that has a sql backend for both sharepoint and the application. The instance has about 20 different databases. We have a "dr" site across our town at another data center for peace of mind. Presently we do file copies out there for our various systems. I was going to start a log shipping or mirroring strategy on this instance to a vm out there, but with 20+ databases don't know if this is the right path. I have spoken to our sysadmins about availability groups and they seem to not want to deal with the windows clusters. Is there any way to get instance level backups out there or do I have to set up mirroring and/or log shipping on each database? Thanks.
AGs require Enterprise Edition of SQL Server and given that you work for a school district I bet you don't have funding for that!
Mirroring with the Standard Edition of SQL Server is SYNCHRONOUS ONLY, and that can create SUBSTANTIAL delays in transaction processing on the primary!!
Log shipping is relatively easy to set up and works great. I have used it for a client that had over 7400 databases on one server (although I had to roll my own version of log shipping for that number of databases).
Please take into consideration that HA/DR is a CRITICAL part of your entity's livelyhood and continued existence. If you lose all your data because something is messed up in your HA/DR plan it can truly be disastrous. Make sure you have everything thought out and tested!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 23, 2014 at 11:46 am
Thanks for the replies. Yes we have the funding we actually have a rather large budget and get a educational price break that is amazing. We usually only install standard edition unless memory or functionality requires enterprise but the licensing is not a factor and we do have software assurance purchased. Being new to this I am overwhelmed with clustering, replication, AG, log shipping, mirroring etc and just want a solid way of replicating the db out for safe keeping but am getting so confused as to what the best option is, none of them seem very hard to setup I just want to pick the correct option.
October 23, 2014 at 8:01 pm
mlorek (10/23/2014)
Thanks for the replies. Yes we have the funding we actually have a rather large budget and get a educational price break that is amazing. We usually only install standard edition unless memory or functionality requires enterprise but the licensing is not a factor and we do have software assurance purchased. Being new to this I am overwhelmed with clustering, replication, AG, log shipping, mirroring etc and just want a solid way of replicating the db out for safe keeping but am getting so confused as to what the best option is, none of them seem very hard to setup I just want to pick the correct option.
You may not think they are hard to set up, but some of your options are indeed complex, and have LOTS of caveats, provisos, limitations, GOTCHAS, etc!!! I can't count the number of clients and forum posters who have messed up their HA/DR (WAY more than have gotten it right, I promise). PLEASE see if there is money in the budget to get a qualified consultant on board to help you a) figure out what you REALLY need and b) help you pick the right option(s) and c) help you plan, implement, test and set up maintenance/monitoring for it!! Remember, if you mess this up the BEST that can happen is that you are offline for an extended period, very likely with some data loss. The worst is a complete and total loss of all data - unrecoverably so.
Some thoughts: how long can you be off line if you lose your primary DC? How much data can you afford to lose? Do you want completely automatic failover? Can you afford your choices or do you have control over them? What is the bandwidth and latency of the network pipe between DCs? What is your backup and retention policy? Will you need to access the secondary for any purposes? The list goes on and on...
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 24, 2014 at 2:25 am
mlorek (10/23/2014)
Yes we have the funding we actually have a rather large budget and get a educational price break that is amazing.
Ok cool, just wanted to check. Most educational organisations do get extensive price breaks as do students themselves.
mlorek (10/23/2014)
Being new to this I am overwhelmed with clustering, replication, AG, log shipping, mirroring etc and just want a solid way of replicating the db out for safe keeping but am getting so confused as to what the best option is, none of them seem very hard to setup I just want to pick the correct option.
Kevin has given you a wealth of info but just to add to that.
Don't be phased by clustering, AlwaysOn and mirroring actually reduce the need for shared storage and the absence of shared storage makes it easier to stretch a cluster geographically. I have used Log shipping very successfully to replicate large databases (100s GBs) from the UK to USA, it all depends on what you require, whether the secondary has to be readable or just sitting by and waiting (restoring\recovery).
Get yourself a plan and have it signed off by all business stake holders, this will involve some requirements analysis.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply