Week of March 20th

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 –

Leave a Reply