Hey, you, get off of my cloud”! (that includes you Corona)
So after taking a brief victory lap for getting my virtual Windows Domain Sandbox up and running last week in AWS EC2. This week I attempted to get SQL Server AlwaysOn running with multiple Availability Groups and multiple Replicas (Async w/Read, Async w/ No read, Sync with Automatic Failover and Sync with Read Automatic Failover options)
After spending multiple days and countless attempts, I was left with numerous disappointments for most of the week as I fruitlessly chased numerous red herrings with non-useful Windows Cluster Log errors.
However, I refused to accept defeat! So I am happy to report that I finally persevered for this week’s mission
Here is what I did:
- Removed my flaky implementation of AlwaysOn AGs and Listener with some lame hacks that had my AlwaysOn Implementation sudo running earlier in the week
- Destroyed Windows Failover Cluster
Began the recovery mission:
- Allocated 5 new class C subsets to existing VPC
- Added additional Policies to Security Group in VPC
- Added UDP port for SQL Browser Service
- Added TCP ports used with AlwaysOn
- Added a second virtual NIC to designated 5 SQL Severs EC2 instances
- Configured 2nd IP for 2nd NIC using unique IP from designated Class C subnet
- Designated DC/DNS Server as my Jump Server as all SQL EC2 instances are no longer routable from internet thru RDP
- Recreated Windows Failover Cluster with 5 SQL Servers
- Added 5 virtual Network IPs (1 for each Class C subnet) to be used with Windows Cluster.
- Recreated Always On with 2 AGs with ASync w/Read, ASync w/ No read, Sync with AF and Sync with Read AF
- Configured SQL Listener to use 5 IPs in each subnet that hosts SQL Servers.
- Added 5 Network IP under AG resources in Windows Cluster to be used with SQL Listener
- Enabled SQL Browsing Service on all 5 SQL EC2 instances (As I tried to be cute and make all SQL instances a Named Instances instead using default)
- Removed all SQL Aliases previously created as they are no longer needed
- Resolved “nslookup” issues that were plaguing name resolution on my Network
- Added 5 new Class C subnets to Reverse Lookup Zones in DNS
- Added Windows Cluster and SQL Listener Names for each subnet to DNS Forward Lookup Zones
- Successfully Tested failover scenarios w/ SQL AlwaysOn
- Installed and configured Redgate SQL Data Generator software on Jump Server
- Successfully Tested various scenarios with pumping millions of records to the SQL Listener while initiating AlwaysOn failover
Next Steps..
- Play around a little bit more with SQL AlwaysOn
- Maybe take on Snakes and Bears (Start by watching some videos)…
- Stayed Tuned.
Peace Out –
–MCS