It has never been as easy to run and maintain SQL Server on AWS as it is with Azure, but if your organization uses AWS, then it makes sense to do so. Amazon Relational Database Service (RDS) for SQL Server now makes it much easier to set up, operate, and scale SQL Server deployments in AWS.  It is a hosted database platform that uses all the AWS services necessary for a SQL Server cloud platform. It deals with the DBA chores of provisioning, taking backups, patching software, monitoring. It will scale the hardware using all versions of SQL Server from 2008 v2. RDS can be controlled via either a wizard, web service, or PowerShell script. 

Multi-AZ Deployments – When you create or modify your DB Instance to run as a Multi-AZ deployment, Amazon RDS will automatically provision a primary database in one Availability Zone and maintain a synchronous “standby” replica in a different Availability Zone. In the event of planned database maintenance or unplanned service disruption, Amazon RDS will automatically failover the SQL Server database to the up-to-date standby. Database operations can resume quickly without any manual intervention.

Behind the scenes, it uses a SQL Server Database Mirroring technology that does not synchronize the SQL Server Jobs. In fact, it is generally the case with SQL Server RDS that the SQL Server agent jobs are missing after a failover. Actually these jobs are not missing, but just not synchronized; and AWS RDS documentation now says that it has always been the case that the synchronization must be done manually with RDS SQL Server. Before then, it wasn’t entirely obvious: Without wishing to blame myself, I only realized that I had to do it after three weeks using AWS.

In this article, I’ll be illustrating how you automate SQL Server RDS via lambda functions and PowerShell, by showing how to script out a high-availability SQL Server solution.

Phase 1: Scripting out the solution

The solution can be broken up into 2 phases:

The first phase consists of uploading the changes (scripting out) and the second phase consists of applying these changes whenever Failover occurs.

Step 1 – Setup the IAM Role to launch the EC2 Windows Instance and run the Lambda Functions

Setup the IAM Service Role on the EC2 Instance :

Create an AWS Identity and Access Management (IAM) role. You can name your IAM role anything as long as it fits the requirements for IAM roles. I called my IAM role: ‘SQLAutomation’

Select the AWS Service Roles. Select Amazon EC2 and choose the related policies to attach to the role. Attach the following policies to your role : AmazonEc2FullAccess ,AWSLambdaFullAccess and Administrator Access

To set up permissions to run a Lambda Function

Enter a role name (IE : start_stop_ec2_instance) and select AWS Lambda Service Roles.

Attach the policies AmazonEC2FullAcces and AWSLambdaFullAccess

Step 2 – Create a S3 Bucket, i.e. sqlagentjobs

Grant the appropriate permission to the user who will execute the solution:

Step 3- Launch an EC2 Windows Instance

• Select any Windows Server version higher than, or equal to, 2012 R2.You can use the free tier only with Windows Server 2012 R2 RTM
• Link the EC2 Instance to the IAM Role previously created in the Step 1 – IE : SQLAutomation
• Link the EC2 instance in a Security Group in the sameVirtual Private Cloud (VPC) as the RDS SQL Server instance
• Launch the Instance, and then download and install from the Microsoft® SQL Server® 2016 Feature Pack :
  • Microsoft® System CLR Types for Microsoft SQL Server® 2016
  • Microsoft® SQL Server® 2016 Shared Management Objects
  • Microsoft® Windows PowerShell Extensions for Microsoft SQL Server® 2016
• Create a folder called ‘Scripts’ and subfolders ‘Load’, ’Logs’ and ‘Save’ in the C: drive
• Copy the ‘master.ps1’ PowerShell script attached to this article to the folder ‘Scripts’

  

• Open the Master.ps1 and change the commented lines:
  • Block code 1 to your environment details
    1 2 3 4 5 6 7

    #Block code 1 - change the variables here $sqlserverendpoint = "SQLServerRDSEndpoint"   $Username = "SQLServerRDSUSer" $password = "PAssword" $bucketName = "Bucketname" #sqlagentjobs $region = "Region" #Block code 1 - change the variables here

  • Block code 2 to the lambda function ARN to stop the EC2 Instance
    1 2 3 4 5 6 7 8 9 10 11 12 13

    (detailed in the Step 4) #Block code 2 -#change to your lambda function ARN in the -functionname parameter if ($Operation -eq 'Failover' -or $operation -like 'Schedule') { $params = @{ FunctionName = 'ARN Function Name ' #'arn:aws:lambda:euwest-1:081765211525:function:SQLSTopInstance' InvocationType = 'RequestResponse' Force = $true } Invoke-LMFunction @params } #Block code 2

• Schedule the task in the Windows Task Scheduler as following :
  • Name : MasterSQL (or any other name)
  • Security Options : ‘Run as System’
  • Security Options : ‘Run with Highest Privileges’

    

  • Click on the ‘Triggers’ tab: Define the trigger as ‘At Startup’

    

• Click on the ‘Actions’ tab :
  • Action : select ‘Start a Program’
  • Program/Script : enter ‘PowerShell.exe’
  • In ‘Add Arguments’ enter ‘ -file “c:\scripts\master.ps1” –executionpolicy ByPass’ (where –file is the full path of the master.ps1 file)

    

• Stop the EC2 Instance

Step 4 – Create the three Lambda Functions :

To Stop EC2 Instance

Select

• Blank Function
• Name : SQLStopEC2Instance (or whatever name)
• Runtime : Python 2.7
• Role : Choose an existing role
• Existing Role : start_stop_ec2_instance (previously created in step 1 – To Lambda Functions)

Lambda Function Code :

To Start the EC2 Instance

Select :

• Blank Function o Name : SQLStartEC2Instance (or whatever name)
• Runtime : Python 2.7
• Role : Choose an existing role
• Existing Role : start_stop_ec2_instance (previously created in step 1 – ‘To set up permissions to run a Lambda Function’)
• Insert the following Lambda Function Code :
  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

  import boto3 import base64 //Your region region = 'eu-west-1' //ID of the EC2 Instance Created step 2 instance = ['i-03b4f750e45e274c4'] def lambda_handler(event, context):      // Saying to the EC2 Instance that was Schedule calling     NewValue = base64.b64encode('Schedule')       ec2 = boto3.client('ec2', region_name=region)     //modifying the user data (in the EC2 to know was Schedule Calling)     ec2.modify_instance_attribute(     DryRun=False,     InstanceId=str(instance[0]),     Attribute='userData',     Value=NewValue )     ec2.start_instances(InstanceIds=instance)

To Start EC2 Instance after failover,

Select

• Blank Function
• Name : SQLStartEC2InstanceFailover (or whatever name)
• Runtime : Python 2.7
• Role : Choose an existing role
• Existing Role : start_stop_ec2_instance (previously created in step 1 – ‘ To set up permissions to run a Lambda Function’)
• Insert the following Lambda Function Code :
  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

  import boto3 import base64 //Your region region = 'eu-west-1' //ID of the EC2 Instance Created instance = ['i-03b4f750e45e274c4'] def lambda_handler(event, context):      // Saying to the EC2 Instance that was Failover calling     NewValue = base64.b64encode('Failover')       ec2 = boto3.client('ec2', region_name=region)     //modifying the user data (in the EC2 to know was FailoverCalling)     ec2.modify_instance_attribute(     DryRun=False,     InstanceId=str(instance[0]),     Attribute='userData',     Value=NewValue )     ec2.start_instances(InstanceIds=instance)

Step 5 – Setup CloudWatch to Schedule the call of the Lambda Function

Create a CloudWatch rule IE : StartEc2Instance

Select ‘Schedule’ in the Event Select combo box and define the time configurations. In this example I am creating one with 365 days, but you can specify whatever time-ranges you want. Because SQL Server agent jobs are usually seldom changed, when some changes happens you can enable and disable the CloudWatch from the AWS console or by PowerShell

Select the Targets and choose the lambda function created in the step 4 to start the instance (not after failover one)

Step 6 – Setup Amazon SNS to call the Lambda Function Failover

Create a new Topic. I’ve used SQLMirroringFailover in the illustration.

Create a subscription to the SQLStartEC2InstanceFailover Lambda Function that we’ve created

Step 7: Setup a RDS Event Failover and link to the SNS

Create a new Event Subscription. We’ll call this FailoverSQLMirroring

• In the dropdown widget ‘Send notifications to’, specify the SNS that we previously created in step 6 called SQLMirroringFailover
• The dropdown called ‘Source Type’ should be set to Instances
• The ‘Event Categories’ listbox should have ’Failover’ selected.
• The ‘Instances’ listbox should have the name of the RDS SQL Server doing the Mirroring

  

Phase 2: Applying changes when Failover occurs.

Execute the PowerShell command to enable the CloudWatch Rule and start the process to do the scripting out and saving the script to the bucket by PowerShell:

disable the CloudWatch Rule after the process:

To launch the EC2 Instance and not run the PowerShell code (for maintenance of the code for example)

· Change the User Data from the EC2 Instance for something different from Schedule or Failover. IE: NULL

Next Steps: To do – Improvements

Set up an Amazon Simple Email Service (SES) to send email notifications of the status of the process (to inform the user whether the .sql script was upload and whether the jobs were successfully synchronized)

Create better error handling in the PowerShell script, along with scripting out the email notifications

Set up the EC2 Instance in Amazon RDS Multi-AZ to provide enhanced availability and durability for Database (DB) Instances

Create an AWS Key Management Service (KMS) key to store the credentials of the RDS SQL Server login rather than having it hard-coded in the PowerShell script