Amazon recently announced Multi-AZ support for SQL Server in Sydney, which provides high availability for SQL RDS instances using SQL Server mirroring technology. In an effort to make life simpler for myself, I figured I’d write a CloudFormation template for future provisioning requests, however it wasn’t as straight forward as I’d expected.
I began by trying to guess my way through the JSON resources, based on what I’d already knew for MySQL deployments. I figured the MultiAZ property was still relevant, so I hacked together a template and attempted to provision the stack, which failed, indicating the following error:
CREATE_FAILED | Invalid Parameter Combination: MultiAZ property cannot be used with SQL Server DB instances, use the Mirroring option in an option group associated with the DB instance instead.
The CloudFormation output clearly provides some guidance on the correct parameters required to enable mirroring in SQL Server. I had a bit of trouble tracking down documentation for the mirroring option, but after crawling the web for sample templates, I managed to put together the correct CloudFormation template, which can be seen below.
Excited and thinking I had finalised my template, I attempted to create the stack in ap-southeast-2 (Sydney, Australia), only for it to fail with a different error this time…
CREATE_FAILED | Unknown option: Mirroring
Finding this output strange, I attempted to run the CloudFormation template in eu-west-1, which completed successfully.
With no options left, I decided to contact AWS Support who indicated that this is a known issue in the ap-southeast-2 region, which is also evident when attempting to create an option group in the GUI and the dropdown box is greyed out, as shown below.
What it should look like:
What it currently looks like:
The suggested workaround is to manually create the SQL RDS instance in the GUI which provides the mirroring option in the deployment wizard. Although the limitation is being treated with priority, there’s no ETA for resolution at the moment.
Hopefully this assists someone out there banging their head against the wall trying to get this to work!