SQL Multi Site Clustering and Replication

Calling all SQL Admins

Does anyone administer an SQL farm across a multi site environment. I'm keen to hear about peoples approach to the infrastructure design.

I'm researching SQLs ability to store DBs on highly available application shares, as I'd like to be able to fail over in the event of a site outage. Obviously SQL clustering is easy to do, and putting an extra node in multiple sites is easy, but the defacto standard has always been to use a single storage array. I want to use two, which is leaning me towards an HA share clustered at the windows level, and replicated as such.

There are third party products out there, but I think I might as well wait for 2016 and do it within the OS

Thoughts?

My biggest gripe against all the work that Microsoft has put into storage in the last few years is that it is insanely complex. It is all managed through PowerShell, with very little visibility of what is happening. When something has failed and you have the app users screaming "Sev 1" at you, the last thing you need is to be googling the correct syntax for the correct PowerShell commandlet.

rotor said:

My biggest gripe against all the work that Microsoft has put into storage in the last few years is that it is insanely complex. It is all managed through PowerShell, with very little visibility of what is happening. When something has failed and you have the app users screaming "Sev 1" at you, the last thing you need is to be googling the correct syntax for the correct PowerShell commandlet.

Click to expand...

isn't that all changing in 2016

Not as far as I've seen -- they keep introducing powerful new capabilities, but it's all managed from the command line, and there are so many layers (5 or 6 between NTFS and the spinning disks) that troubleshooting has got to be a nightmare.

rotor said:

Not as far as I've seen -- they keep introducing powerful new capabilities, but it's all managed from the command line, and there are so many layers (5 or 6 between NTFS and the spinning disks) that troubleshooting has got to be a nightmare.

Click to expand...

It does seem to be the case, that whilst you can configure something via the GUI....actually watching it work and/or troubleshooting is all powershell.

Why wouldn't you use a SQL Always-On availability group. This is storage independent and works well. I am using that on a fair few SQL servers that are also on different sites.

katana6434 said:

Why wouldn't you use a SQL Always-On availability group. This is storage independent and works well. I am using that on a fair few SQL servers that are also on different sites.

Click to expand...

so that can use 2 sans as well and ensure the data is kept in sync?

AOAG works well, but a note of caution. If you have an AOAG configuration of a cluster with 4 nodes, a pair in each site. Each pair have shared storage, SQL is then installed as a clustered resource on each pair. Which provides you with HA in each site, only requiring two sets of shared storage. Then use AOAG to replicate between the SQL resources in each site. Then currently you cannot use SMSQL to perform backups. It's unable to mount the SnapShot volumes to do the restore.

I see a lot of mention that you have to use Powershell to trouble shoot AOAG, I have never had to resort to PowerShell shell to fix AOAG. I've built quite a few AOAGs and they just work! Also PowerShell isn't that scary, download and install PowerGUI which makes life a lot easier to develop PowerShell scripts.

Another option if you can use Server 2016 is it’s capable of disk level replication between servers. So you don't need a SAN and the database are replicated.

My initial plan was for 2016 file replica, so should I stick with that or the sql AO group?

There are two sans at each site anyway with plenty of disk space. However!! There will be two physical server nodes at Site A, but only one in Site B

p.s. Thanks for your help with this!!