Week of March 13

Forget Corona! Take me to the cloud

Creating and Configuring a SQL Server AlwaysOn Configuration in AWS EC2

  • Created VPC in AWS
    • Designated Private Subnet address
    • Specified proper routes
  • Created Security Groups
    • Specified specific Protocols, Ports, Source/destination 
  • Created 6 micro AWS Instances of Windows 2016 DC Edition
    • 1 Domain Controller
    • 1 Primary Database Server
    • 4 Replica Database Servers
  • Configured Windows Active Directory, DNS
    • Joined Windows Domain
    • Created FID to be used with SQL Server Services
  • Established connectivity between “MCSSYSTEMS” AWS Instances
    • Configured virtual NICs to point internal DNS and use static IP
  • Configured additional storage volumes
    • 15 (300 GB) Volumes  – 3 volumes (Data, Log, TempDB ) to be assigned to 5 designated SQL Servers)
    • Attached storage to AWS Instances to be used with SQL Server
    • Brought Disks online as Dynamic disks
    • Formatted Disks (64K)
  • Migrated 5 AWS micros instances to medium t-shirt size Instance so to be used with SQL Server
    •  re-configured virtual NICs to point internal DNS and use static IP (as all settings were lost during migration)
  • Installed SQL Server 2016 w/ SP2 and SSMS 2018 on 5 Instances
    • Disabled Windows FW
    • enforced best practices (separation of Data, Log, Temp)
    • Created multiple Temp DB files to match v CPUs
    • Put SQL FID in Lock Pages in Memory Policy, instant file initialization
    • Configured static port # to be used with SQL Server instances
    • Configured SQL Server alias on instances can connect to friendly name (DNS is flaky)
  • Began SQL Server AlwaysOn Prep work
    • Install/Configured Windows Fail Over Cluster Services on 5 SQL Server AWS Instances
    • Enable each SQL Server for Always on availability groups
    • verified all SQL Servers are running properly and prepped for Always 

Next Steps.. 

  • Download and attach AdventureWorks database (Sample)
  • Configure AlwaysOn, Availability Group(s), Listener
  • Create Primary, Secondary, and Read Only Replicas, etc
  • Consult with the Big Guns

One comment

  1. Pingback: Week of July 17th | SQL Squirrels

Leave a Reply