I am in the process of migrating to Amazon AWS and need a SQL server high availability solution. The current licence that I have is SQL standard 2016. At this time Amazon does not support shared volumes for Windows instances. Therefore, I am not able to do a regular SQL cluster fail over solution. This is the one where if the entire server goes down the stand by server picks up the slack and continues writing to the same storage. My only option is high availability always on basic groups. As I am starting to get familiar with this feature I find it very maintenance intensive and can see it becoming a problem when dealing with thousands of databases. In my case I have about 5k databases mostly small in size 600mb or less each. My question is Amazon not a viable hosting environment for a full SQL fail over solution. Is the high availability always on basic groups one per database a viable solution?

  • 2
    If your databases are used at any relative length and frequency... 5k databases on a single instance each in a basic availability group will be scary at best and non-functional at worst. This is regardless of where it is hosted. – Sean Gallardy Oct 30 '17 at 15:26
  • Most of the dbs are small in size and I am currently hosting this on SQL server 2008 and has been very stable. – CodeMilian Oct 30 '17 at 15:48
  • 1
    @SeanGallardy-Microsoft - Please clarify your statement - do you mean 5K (databases on a single instance each), [implying 5K instances]? Or, (5k databases) on a single instance, each [implying 1 instance with 5K DBs on it]? Either may be problematic, but I believe the OP is meaning the latter. – RDFozz Oct 30 '17 at 16:15
  • @RDFozz It shouldn't need clarifying, "5k databases on a single instance" is literally is what I wrote. – Sean Gallardy Oct 30 '17 at 16:19
  • 1
    Note that you can create a SQL Server FCI on Azure: docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/… And in Azure Single VMs using premium storage have a 99.9% uptime SLA: azure.microsoft.com/en-us/support/legal/sla/virtual-machines/… – David Browne - Microsoft Oct 30 '17 at 20:25

I did a presentation on this during the summer @ SQL Saturday - LA and you have a few options. In some cases, you need clustering if Mirroring isn't an option and AlwaysOn AGs require cross database (but same instance I believe) transactions using MSDTC on some versions of SQL Server.

Thus in order to provide failover clustering support in AWS I used an AWS Storage Gateway to act as a routable SAN. This was simple to setup and was secured easily via the AWS security groups. We then used mirroring to mirror to Azure and on Azure I used the new SQL and Windows 2016 shared storage direct solutions which are an option for AWS if you use Windows 2016.

Here are the relevant slides on setting up a storage gateway: Select Storage Gateway from your AWS console -Select the "Storage Gateway" option from the AWS console.

Create it as a cache volume -Select "Cached Gateway" from the options when you start

Then create a cache volume inside the cache gateway -Select "Cache Volume" inside the gateway.

Finally configure CHAP -CHAP has to be fully configured. It is fully supported though.

Please note I had to configure both secret and not secret auth for the configuration to work. YMMV

S2D Options For Windows 2016: Here are a few items I had to do with S2D on Windows 2016. Note the hypervisor in this case is Azure but same concept applies: S2D Reqs For Us, YMMV Read Docs linked above -These are the S2D requirements for us, read the docs for your environment.

Note you have to use the "Friendly Name" from Get-StoragePool in Powershell or else it'll error out -This tripped me up, be sure to find and use the 'Friendly Name'.

Note the command in Powershell works upon using the right friendly name.  This tripped me up earlier. -You can verify it now

Verify your steps on Node 2 and after this you will pass validation in WSFC -Verify your steps on Node 2 and after this you will pass validation in WSFC.

  • Thank you so much I will look into this and post back results! – CodeMilian Oct 31 '17 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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