Moving SQL server to AWS

Why?

Just a bit of background first - why move a SQL server to AWS, rather than using the Amazon RDS service?
If it is possible for you, I'd recommend using the Amazon RDS service, which comes in MS SQL flavour as well, rather than managing your own SQL server in AWS.  However, this will not always be possible.  My client made extensive use of SQL server features, including replication to a Enterprise SQL server used for BI, both reporting services (SSRS) and analysis services (SSAS).  As neither replication, nor reporting and analysis services are currently supported in RDS, we had to host our own SQL server.

What was being moved

A number of applications were hosted on the same server as the SQL Server 2008 server.  These included web services providing the data connectivity for a number of web sites, as well as the back office ERP system, responsible for all processing of orders, stock management, sales, customer support, etc.
In addition, there was extensive intergration with partner systems as well as with internally hosted Sage instance, including automated supplier payments.  Most of this was using SQL integration services (SSIS).
Most of the applications were .net web applications and were being moved to Elastic Beanstalk.  The SSIS services would be hosted on a separate SQL server in AWS - this would require some measure of manual configuration of several of the supporting services and extensions after the initial build and configuration.
Here I concentrate on building a SQL server.

Part 1: Building a SQL server in AWS

Amazon provides machine images with SQL server standard where licencing is included in the running costs.  You could alternatively bring your own licence (under certain conditions), but this did not provide the flexibility we were looking for.
I am not a specialist DBA, but I believe that the solution I put together provides a very reasonable SQL server installation, taking care of many of the standard configuration tasks that infrastructure engineers are probably not exposed to on a regular basis.  Parts of the solution may be unneccessary, and some are constrained by limitations of older versions of Windows Server.  An attempt was made to make it work with the Amazon AMI images of all versions of SQL server from 2008 SP3 to 2014, but images change, and the template and scripts changed with time, so nothing is guaranteed (use at own risk - i.e. test the build!).  You will find errors, and some things are unnecessary, but generally most of what's in there is / was required for an image somewhere.

The parts that are used

A cloudformation template is used to build the server.  While the template includes the majority of the configuration, there are a few additional scripts.  There is a PowerShell script, New-SQLServer.ps1 that is used to kick off the build, and five files are downloaded by the template and run - they could have been included, but downloading them from S3 seemed a better solution.

New-SQLServer.ps1

Please note, as I share this script, it includes a large number of default settings - you specify all parameters when calling the script, but many of them may as well have your defaults set (I had defaults for all except ComputerName).
Essentially, the script just does some checking on the parameters, finds the current AMI image for SQL server, and then calls New-CFNStack.
Just a few notes on security - the "DomainAdmin" should NOT be a domain admin, but a domain user with just sufficient rights to create computers in the required domain or even just the specific OU.
The user running the script needs to have AWS credentials loaded before running the script, with admin rights on AWS (you can lock this down to some extent, but it does require a lot), or assume a role that has the required rights.
Your Windows user has to have the rights to add the computer to the AD group.  This is not important, but if you have Read Only Domain Controllers in AWS, and they have been set to cache credentials for only one group of computers, you want the new server to be in that group.
Also note that the credentials specified (including domain user and password), will be visible to anybody with access to view CloudFormation stacks.

The template

The template is where most of the work happens.  Rather than having all of the scripts included in the template (which is how it started), or run by the New-SQLServer script, some are downloaded from S3 to the local file system, and executed from there.  I'll get to those files, but it means you have to have an S3 bucket set up with the correct scripts in there.  In addition, backups are stored in a different S3 buicket.
In addition to setting up the server, the template creates a policy that allows access to the two S3 buckets required, creates D: (data) and E: (backup) drives, and assigns the specified security group.  It's possible to create a security group during the creation, but it made more sense for me to have a single security group assigned to all SQL servers.
Note, the template won't work as it is - it contains mappings to specific subnets (you'll presumably want backend subnets) that will have to exist and match your environment.  Also note that (through pure laziness), I have hard coded the locale and time settings.
Essentially, the steps the template goes through on the server instance are the following:
1) Fix some basic issues (e.g. with firewalls), download files required later, and sets system locale and time zone, then restarts.
2) Rename the computer, then restarts.
3) Join the domain, then restarts.
4) Change SQL collation, and add Agent user to a few local groups to avoid issues later.
5) Do SQL configuration.  This runs SQLPS, as the SQLPS module isn't supported on old platforms.  Essentially, this script does the following:
- Set mixed mode authentication
- Calculate and set max server memory for SQL
- Turn on backup compression where supported
- Change the SQL Server name to match the computer name
- Set default file locations to the D: drive
- Change the service account for SQL server
- Enable TCP (not enabled by default on express)
- Change service account for SQL Server agent, enable and start agent
- Add tempdb files to any ephemeral drives you have
- Configure DB Mail and enable for SQL Server Agent
- Set up Ola Holgren's fantastic maintenance solution - you'll have to have it downloaded, and set some basic parameters, like saving to E:\SQLBackups, how long to keep backups on the server, etc.
- Schedule all the maintenance tasks - the schedule I use is as recommended.
- Create a scheduled task to upload backups to S3 on a daily basis.
6) Update Windows (strange that you have to go through as many contortions to get PowerShell to do an update).  As images are regularly updated, this is normally a small update, but is also one of the reasons you have to test!
7) Ensure remote management is running
8) Tell Amazon you're done.....

Backups

I mention the scheduled job.  The script triggered by the schedule looks for backups in the locations where the maintenance solution puts them, then uploads them to the defined S3 bucket.  It has three targets, Daily, Weekly and Monthly.  Set up lifecycle configuration for these three targets.  I have mine set up to keep daily backups for 15 days, weekly backups for 32 days, monthly backups for 32 days then move them to Glacier.

Files needed

The template downloads five files from a specified S3 bucket.
- SQL-Configmail.ps1 includes 5 additional parameters like SMTP server and credentials.  This could have been added, but I decided it's as easy to customise and fetch that from S3 instead.
- MaintenanceSolution.sql - download the newest version of Ola Holgren's solution and customise it if required.  Remember to change the backup location to E:\SQLBackups.
- SQL-ScheduleMaintenance.sql - a reasonable starting point for scheduling backup and maintenance jobs.
- Backup-Upload.ps1 - again, customise this file. 
- UploadBackups.xml - The template uses schtasks as a workaround for older versions of Windows Server, and this is the xml file that it wants as input.  Ideally create your own manually (or with PowerShell) then run schtasks /Query /XML /TN taskname.

What's missing?

If this was all there was to configuring SQL server, there'd be lots of DBAs out of work.  I hope this provides the basics - for me this provided the basic configuration that was then (with more scripts) used to mirror the live database to before failing over to this server.  I built a second server to act as mirror and a third (express) to act as witness.  A forth server hosted all SSIS jobs and acted as distributor.
All credentials were copied from the correct servers and pretty much everything else was scripted as well.
As a bonus, it was possible to automate a new test server by building it, downloading the most recent backup and anonymising the database.  It's also possible to script a rolling server upgrade (using mirroring), although halfway through you need to pause and update connection strings in all the applications.

I would appreciate any feedback.

PowerShell SMTP server for Elastic Beanstalk

Applications developed in .net commonly send email by creating mime files (.eml files) and dropping them into a specified folder.  The actual sending often depends on Microsoft's IIS 6 SMTP server.

SMTP server replacement in Elastic Beanstalk

When such applications get migrated to Elastic Beanstalk (EB), every instance of the application runs on it's own instance (server).  Any functionality that rights locally, writes to the local storage of that instance, which can be replaced at any time.  Each instance also has to have it's own SMTP server installation, and IIS 6 can't be easily scripted.

An alternative method is therefore required to pick up .eml files generated and emailing them.  Ideally, the applications should be changed, but as this is not always practical, I created a solution to schedule a task to regularly run a PowerShell script.  The script checks for files in a location (dropmail folder), and processes them.

The simplest way to make changes to Elastic Beanstalk instances is through “.config” files in the .ebextensions folder.  A file “schedulemail.config” is created in the .ebextensions folder.

The solution is a simple way that illustrates a number of things, including the use of ebextensions with Windows Elastic Beanstalk applications, scheduling tasks with PowerShell, and sending .eml files with PowerShell (more later).

The .config files you create in .ebextensions are YAML files.  The key things to know about these files are:

• indentation is critical (think Python)
• you can't use tabs, only spaces
• elastic beanstalk applications simply fail if there's anything wrong.

I have found that the most common reason for a simple, silent, failure of an EB deployment is a tab in a .config file.

This config file creates three files:
c:/software/sendFile.ps1
c:/software/archiveEmail.ps1
c:/software/scheduleMail.ps1

It then runs the last of these as a command.  This creates a scheduled task that runs sendFile.ps1 every minute and archiveEmail.ps1 every 10 minutes.

Emails are archived to an S3 bucket, where a lifecycle rule can be set up to delete anything older than 2 weeks, but that can be modified as required.  Of course, whether or not it is necessary to archive the email files would depend entirely on your application.

The actual sendFile.ps1 in this file sends email using Mandrill, but on the way there I also used two other methods for sending .eml files.  Any of them can very easily be substituted.

The config file can be downloaded from my github repository and modified.  You will have to add your own key, and if you want to receive notification in the event of failure, add your own email.

Sending .eml files using PowerShell

In this post, I provide three different scripts for sending a mime file (e.g. .eml file as is created by .net applications) via email.

I had to learn a few tricks along the way, and thought it worth documenting it here.

The scripts form the basis of a replacement for Microsoft's IIS 6 SMTP server.

Using Amazon SES

First ensure SES is properly configured for your domain. This involves the usual proving you own the domain etc., as well as requesting a removal of the limit that allows you initially to only test it.

Once SES is set up, you set up SNS to monitor the sending. The simplest way is to simply email SNS notifications to a dedicated mailbox where you can then search for specific email addresses on demand to see if emails were delivered, bounced or rejected.

Of course, you can set up something much more clever with SNS given time and inclination.

The work-horse of this method is Send-SESRawEmail. For me the problem was that Send-SESRawEmail takes a MemoryStream as input, and I struggled to find the documentation on how to do this. I'm not sure how efficient it is, but this script takes a file and converts it to a MemoryStream before passing it in.

Using SMTP

It is possible to send an email directly to an SMTP server.  I’ve not tried encrypted SMTP yet.  This obviously requires an SMTP server to be set up correctly, but you may well have this for other puposes.

Essentially, what you do is simply connect to the SMTP port and talk to it.  For this you create a TcpClient Object and a StreamWriter object and write the handshake, then the content to port 25.  In spite of both the sender and receiver being included in the .eml file, you still need to provide a sender and recipient in the initial handshake.

The script here will send an email file and takes 6 parameters (including the filename).  Download and use Get-Help to get a bit more information, or simply have a look at the script.  There's a lot more information on scripting network connections in PowerShell in this post by Lee Holmes.

Using Mandrill

We’ve been using Mandrill for some time to send emails.  You can send to Mandrill through SMTP, but in this solution I call the Mandrill restful API directly.  When I eventually worked out how to do it, this was the simplest solution, as well as keeping with what we already use. The SendFile-Mandrill.ps1 script requires just the filename and your Mandrill API key.

Connecting your AWS infrastructure to your internal network

Introduction

In some cases you will want to keep your infrastructure in AWS completely separate from your internal network.  I needed to integrate the infrastructure with the rest of our infrastructure, including connecting Windows server to our domain.

There are three obvious ways to connect the networks:

If you have an MPLS WAN, you probably want to extend this to your AWS VPC (Virtual Private Cloud).  For this you would use DirectConnect.

Another option is to use the Amazon Virtual Private Gateway (VPG) to set up a VPN connection to your edge device.

I opted for the third option, which is to create a server to provide the VPN connections.  The main drivers behind this decision were that we are planning on changing our edge device soon, we already use OpenSWAN to connect some of our networks, and we need to connect three different networks to AWS. I'll share a template and a script that will connect to a suitably configured OpenSWAN installation on your network, or connect to another instance in a different VPC (created with the same template).

Once the network connection is in place, add DHCP options to your VPC and you're ready to add Windows servers to your domain.  I added a Read Only Domain Controller to cache DNS and authentication.

Creating the VPN

This article walks you through connecting two VPCs using OpenSWAN.  You can read that for a lot of the background.  I have created a template that you can get here that will set up an OpenSWAN instance for you, using parameters you provide.  You can use this to connect to a VPN endpoint in your internal network, or, of course, to another instance in another VPC.

In my previous post, I shared a template to create a NAT instance.  There I used "UserData" to simply script the whole installation.  In this template I follow a different approach (although it would have been just as easy to do it the same way).  Using cfn-init and "Metadata" allows a more involved configuration, e.g. where reboots are required, such as adding a Windows server to a domain, but here, on linux, it's just another way of doing the same thing.

The template shows how to install a linux package openswan, create a number of configuration files (assembled from parameters provided), set the services to run (to make it persistent in case of a reboot) and run all the commands required to start the services.

The only resource created other than the single ec2 instance is a security group that allows access from private IP addresses (both internal and in AWS) only.

An alternative is to use "sources" instead of files.  This allows you to have zipped (or tarred) files downloaded from an S3 bucket and extracted to a location you specify.  This is ideal if you want to set up multiple VPN connections.

This snippet, inserted before the "files" section, will retrieve and unzip files into the /etc/ipsec.d folder

"sources" : {
  "/etc/ipsec.d" : { "Fn::Join" : ["", ["https://s3-eu-west-1.amazonaws.com/",
    { "Ref" : "SourceBucket" },
    "/",
    { "Ref" : "SourceFilesKey" }
    ]]} 
},

In my github account I have a "work in progress" template that uses this.

Although I'm using this blog mostly to record, for my own purposes, things I pick up, I also find it a lot of work - none of the scripts or templates I use here end up having much in common with what I actually use in anger - all part of trying to make it work with less things already there.

Scripting the creation

As in my previous post, I use a PowerShell script to actually create the VPN using the CloudFormation template above.

This should be simple, shouldn't it?  Actually, if you have a look at the script, you'll notice that there's 60 lines to guess the parameters if you haven't provided them, 30 lines of "help" and really only a little bit at the end that actually creates the VPN, assign the external IP and delete the previous VPN instance if there is one.

The idea of the script is to replace an existing instance, either to update settings or because something is not working as expected, with minimal disruption of the tunnel.

Preparing for extending you AD

Once you have your VPN up and running and you have created routes on your internal network to get to your VPC, you can start putting private servers in your private subnets.  This includes extending your Active Directory into your Virtual Private Cloud, should you have a reason to do so.

Once you know that instances in your VPC can happily access your on site resources, you can potentially add Windows instances to your domain.

Before it's possible to add an instance to your domain, you need to make one more change.  In the AWS Console, go to the VPC Management Console and DHCP Option Sets.  Create a new option set with a domain name and domain name servers set to your internal DNS servers (Domain Controllers).

Once this is saved, go to "Your VPCs", select your VPC, under Actions, Edit DHCP Option set and select the newly created DHCP Option set.

You probably want to create one or two read only domain controllers (RODC) in your VPC.  I've found that an RODC runs quite happily on a micro instance.  Once this is running, you need to create a new DHCP option set with your RODC(s) set as the first DNS server(s) (leave your internal ones later down the list).

You're ready to start building domain joined Windows servers in AWS now.