4

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!

1 Answer 1

1

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.

4
  • i wanna use a solution which will be standard for future HA stuff as well so Log Shipping is not really ideal imo. A well documented, setup and forget solution like FCI would be better IMO. technet.microsoft.com/en-us/library/dn265970.aspx what about this solution to run it without AD?
    – Bengelnatz
    Commented Sep 15, 2015 at 14:19
  • I agree that a FCI would be a better solution for you but you will need AD for that. Commented Sep 15, 2015 at 14:47
  • what about the link? in sql 2012 there seems to be ad-detached clusters.
    – Bengelnatz
    Commented Sep 15, 2015 at 16:21
  • I thought you were only on Windows 2008 R2? If you can use 2012 R2 then this could be an option but not one I could recommend for SQL Server. Commented Sep 15, 2015 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.