Automating the Synchronization of RDS SQL Server Agent Jobs in a Multi-AZ Environment

Although Azure is the obvious Cloud service to host SQL Server, Amazon Relational Database Service (RDS) for SQL Server is a good choice when your organisation uses AWS. RDS deals with maintenance and monitoring, and supports the use of PowerShell to automate routine tasks. What if a script needs to be triggered by an unscheduled event? Even in this case, RDS can be configured to run scripts to react when something like a failover happens. Laerte Junior shows how easy it is to set up Lambda functions and some PowerShell scripts to automatically synchronise agent jobs after a failover.

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

    • Block code 2 to the lambda function ARN to stop the EC2 Instance

  • 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 :

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 :

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

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue