Week of April 17th

“Seems so good… Hard to believe an end to it.. Warehouse is bare”

Greetings and Salutations..

My odyssey this week didn’t quite have the twists and turns of last week’s pilgrimage. Perhaps, it’s was a bit of hangover from “Holy Week” or just the sheer lassitude from the an over abundance of learning new skills during this time of quarantine?

Last weekend, I finally got a chance to tinker around with Raspberry PI. I managed to get it setup on my Wifi network.. Thanks to finding an HDMI cable to hook up to my 50′ Samsung TV. I also learned how to set VSCode on the Amanda’s Mac and connect via SSH to my PI with out using a password (creating a token instead) which is convenient little trick. In addition, I got to play around with the Sense HAT Python module and made the Raspberry PI display some trippy flashing lights with the on-top LED board add-on.

So after being on Cloud 9 most of the last week, I decided I should stay a little longer in the stratosphere and bang on the Google Cloud Platform or more endearing known as GCP and their Compute Engine. Again, I would rebuild my SQL Server 2016 Always On environment (previously created several weeks back on AWS and last week on Azure). Once again, the goal would be to compare and contrast now all 3 major cloud providers. In effort to avoid redundancy here, I won’t reiterate the same prerequisites taken to build out my SQL farm. However, I will share my observations with GCP

GCP – Compute Engine Issues – Observations:

  • More options than Azure but less then AWS – Interface was bit confusing to navigate through
  • 1st instance built (Domain Controller) got corrupt whenI upgraded my account from Free to Paid Instance need to be rebuilt
  • Windows Domain failed to be created as Administrator account was set to blank (disabled on AWS EC2 and Azure VMs)
  • Disks can only be detached from an instance that is an “Stopped” state

Here are my final rankings based on the the 3 Cloud providers I evaluated


1. Best UI: AWS2.

2. Most Features: AWS

3. Easiest to Navigate UI: Azure

4. Most suited for Microsoft workloads (i.e. SQL Server): Azure

5. Most enticing to use it: GCP (free offer)

6. Most potential: GCP


1. Hardest to Navigate UI: GCP

2. Hardest to configure (security): AWS

3. Least amount of features: Azure

So after burning out my fuse up there alone… it was time to touch down and bring me round again.. And that led me back to SQL Server and Columnstore Indexes. Here is what I did:

  • Downloaded/Restored modified AdventureWorksDW2012
    • Created 60 million rows for the Internet Sales Fact table
    • Created Columnstore Index and benchmarked performance vs old Row based Index
    • Created Table partition for Internet Sales Fact table and designed Partition Switching scenario (workaround used with SQL 2012 and non-clustered CIs) Old Lava Archive database design.

Next Steps.. 
Below are some topics I am considering for my voyage next week:

  •  SQL Server Advanced Features:
    • Best practices around SQL Server AlwaysOn (Snapshot Isolation/sizing of Tempdb, etc)
  • Data Visualization Tools (i.e. Looker)
  • ETL Solutions (Stitch, FiveTran) 
  • Process and Transforming data/Explore data through ML (i.e. Databricks) .
  • Getting Started with Kubernetes with an old buddy (Nigel)

Stay safe and Be well


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 –