I am new to SQL Server (2 months) and learning at a gov company (europe).
The job is to make a HA solution for this 1 productive instance (1 database).
Following is given
- SQL Server 2012
- Windows Server 2008 R2
- single instance with single user database
- the instance uses an Agent job for backups (once per day)
- 24h dataloss is OK
- Disaster backup is not my job
- RTO is the typical ASAP but IMO 1 min is ok (which is insanely high?)
- Local Logins (is in Domain, but not AD)
- max 300 clients (Citrix Xen Mobile)
- Database is 300MByte with only 30 clients currently
- all local storage at the moment
- most likely more clusters for other applications in the future
What are my choices here and why is this one better?
I know about FCI and AG but I can't wrap my head around some things.
- what are the downsides with doing an WSFC without AD?
- whats the downside in using a SMBShare instead of Fibrechannel/IScsi?
The possibilities I see (up/downsides would be awesome for these):
-
put application server(s) into AD (we do have one),get SAN storage, install FCI
-
use WSFC to add future clustergroups for other applications + SQLServer with the 3 wizards in no time
-
downside I see is cost, upside is reusability and support is best
-
-
same as aboth with SMB which is way easier and cheaper
-
use AG without AD and replicate Logins, Agent Jobs between nodes manually (same with AD)
I'm kinda lost after stumbling into the topic.
Thanks to everybody helping!
Best Answer
If your RTO is ~ 1 minute and your RPO is 1 day of data loss then you could use log shipping. This would not require AD, a Windows Cluster or Availability Groups.
If you shipped your logs every 15 minutes the recovery time needed to bring the secondary should be well under a minute (Needs testing but I would be surprised if it wasn't from what you have explained above).
The only thing you would need to deal with would be how the application manages the connection to the secondary when the primary is down. This could be handled in the application, a load balancer (with static routes that would need to be altered on failure of the primary) or a clustered name resource (would probably need to be manually failed over).
A FCI would mean you wouldn't need to handle and monitor all the log backup/copy/restores of log shipping and your RTO would be lower. This is not possible without AD. Also you can't have Availability Groups without a Windows cluster which also requires AD.