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.

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.

AWS Cloudformation - creating a NAT instance

Introduction

Having got the basic networking in place with public and private subnets, there is one more networking ingredient that is required.  If you want to be able to use many of the AWS services in your private subnets, instances in these subnets need internet access.

In this post, I'll create a NAT instances to give internet access to private subnets. 

To build the NAT instance I'll use Cloudformation, and then I'll create a Powershell script to create or replace the NAT instance.

NAT instance

You can create an EC2 NAT instance by using one of the community NAT AMIs - I'm sure that's OK, but the one I happened to pick didn't work, and using this approach is a lot more flexible, and just as easy.

Amazon provide a very good article on how to set up high availability NAT.  What I'm doing here is quite a bit simpler - it will not give the high availability of the Amazon solution, but will allow a fairly rapid replacement of a malfunctioning NAT instance (probably 10 minutes rather than 10 seconds). I also needed to NAT an incoming port, forwarding it to another instance, meaning I could only have one working instance.

Cloudformation

Cloudformation is a fantastic service.  There is no cost associated with Cloudformation - you pay for what you create.  In this case I'm creating an EC2 instance and will pay the hourly rate as soon as it starts.  With Cloudformation you use a template to create a Cloudformation "stack".

The stack I'll create here contains an EC2 instance doing NAT and a security group.

The template I use is quite simple, and is easy to deconstruct and see how templates work.  If you want to, feel free to download the template from here, modify it, and use Cloudformation to build it.  If you are familiar with linux and bash, you will see how "UserData" in the instance properties can be used to do virtually any configuration of a linux instance.  A note here - the template is in JSON format, which can be very easy to get wrong.  I use Notepad ++ in a Windows environment, and add the JSON viewer plug-in.  This allows you to select your whole template and quickly verify if the JSON is OK.

The instance you create with this template may be included in the free tier, costing nothing.  However, it's a pretty useless instance on it's own - without modification you won't be able to access it except from other instances in your VPC.  To allow external access, make a second copy of the line:

{ "IpProtocol" : "tcp", "FromPort" : {"Ref" : "ForwardPort" }, "ToPort" : {"Ref" : "ForwardPort" }, "CidrIp" : "0.0.0.0/0" } ,

replacing  "ForwardPort" with "SSHPort", and preferably locking down the CidrIp to your own IP address.

Once the stack has been created, I need to make it work.  In my previous post I mentioned the two routing tables.  Once I have the NAT instance in place, I create a routing table with the NAT instance as default route, and I associate this routing table with the Private subnets.

PowerShell

I use a Powershell script to create or replace the NAT instance.  This script can be called by a monitoring server, or manually.

PowerShell may seem an odd choice - while the PowerShell module for AWS is very good, I'm pretty sure it's development is behind the normal AWS CLI (Command Line Interface), that can be used from any platform.  The script would have been very easy to create in bash, python or whatever.  There are two reasons I'm using PowerShell - the first is that I quite like PowerShell, but the real reason is that I'm currently building a Windows environment.  In a future post I hope to share my script for creating a full mirrored SQL server environment.  For this you really do need PowerShell, so I'd rather give my clients a consistent tool set.

The script can be used to create an initial NAT instance, as well as to replace it, either after updating the template, to change the parameters, or if the instance stops working as expected.

To use the script, ensure you have initialised your AWS settings in Powershell as explained in a previous post.  Change to the directory where the script is saved. Upload the template to an S3 bucket (if you have a local copy, you can just use the following PowerShell commands):

New-S3Bucket <uniquebucketname>
Write-S3Object -File NATInstance.template -BucketName <uniquebucketname> -Key NatInstance.template

After this, you can use the template you have uploaded with the script.  You can get the url of the file from the AWS console, or use https://s3-eu-west-1.amazonaws.com/uniquebucketname/NATInstance.template (or similar, depending on your region).

To create an initial NAT instance that serves purely to allow outgoing internet access, make sure at least one subnet has "auto-assign Public IP" enabled, and you have created a second routing table (you don't need to have anything in the routing table). Then simply run:
./Replace-NAT.ps1 -NatTemplateURL https://.....
This should create a NAT instance.  For private subnets to start using it, associate your private subnets with this routing table.

If you need to use incoming NAT on a specific port, you can create and assign an EIP to this instance (that allows you to retain the same IP address for future use).

You can replace the instance at any time, whether because of issues, or because you want to make changes (e.g. to implement incoming NAT you can simply add the -ForwardHost and -ForwardPort parameters).  The script will, by default, replace the route and delete the previous stack (after confirming with you, or without confirmation if you specify -Force).

For more information on the script (help is very limited, but at least it will list available parameters if you don't feel like editing the script), run
Get-Help ./Replace-Nat.ps1

That's all for this post - next post I'm planning on showing an alternative way to customise a linux instance.

One step back - getting ready to use AWS

Although I've already set up a free AWS account, I'm just about to start using it to test what I'm posting about.  I also had to go and find a Windows laptop - I normally use only linux at home, but can't test my PowerShell scripts on that!  So I'll take the chance to document some basics - how do you start using AWS?

First, you have to sign up for an AWS account.  The email address you sign up with is the "root" account - unless you're the only one with access to the account, try not using this, but create an IAM (Identity and Access Management) user (one or more per person).

Make sure you save the credentials somewhere safe - this is what you'll use to sign in.  While you're there, when you click on the user, go down and click on "Manage Password" - create a strong password, this allows you to log in to the console as this user.  Rather than assigning rights to the user, I would normally create a group, assign rights to the group, and add the user to the group.  Click on Groups, Add a group, call it admin and select the policy "Administrator access".  Now click on the IAM Dashboard, and save the "IAM users sign-in link.  Please note that administrator access does not give you access to everything, a few rights have to be explicitly assigned, but administrator rights do give you the rights to edit your own access if you find you need more.

At this point IAM users won't have access to the billing and account information.  If they need it, go to Billing and account Management while still signed in to the root account (click your user name at the top right to get there).  On the billing dashboard you'll see a link to Account Management.  There "edit" the IAM user access to billing information and activate IAM access.

Once you've set up your account, to the AWS tools page, and download the PowerShell installer.  I accept and install everything (although I'm unlikely to need all the SDKs).

Also download the CLI.  Start a cmd prompt, run "aws configure" and specify your keys and region (eu-west-1).

Run PowerShell as administrator (or the PowerShell ISE, which is better).

Set-AWSCredentials -AccessKey YourAccessKey -SecretKey YourSecretKey -StoreAs MyProfileName
Initialize-AWSDefaults -ProfileName MyProfileName -Region eu-west-1
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned(Accept the warning )
Get-AWSCredentials

(This should return something, not a blank)

In future PowerShell sessions you run Initialise-AWSDefaults and it will load your default profile.  You can also add this to your PowerShell profile.

Networking in AWS – VPC, subnets and routing

The key part of your AWS infrastructure is your VPC (Virtual Private Cloud). It is important to get it right, as it can't be changed later, although you can have multiple VPCs in your AWS account. Think of the VPC as the container in which you build your whole AWS environment, so that's where I'll start this series.

Talking about the account, we already used AWS for customer facing web sites. When I started this project, there was a distinction between “front end” (customer facing web site) developers and “back end” developers. This has since changed, but it seemed reasonable to keep the distinction, so I created a second AWS account. If you do, set up an email alias to use as the root account, and consolidate the billing. I'm sure finance would rather have to deal with just the one bill...

If possible, when you create your VPC, use a CIDR in a range that does not overlap with any of your internal subnets. For the company (and group) I'm currently contracting for I used 10.128.0.0/16 – this means I could potentially have VPN set up to connect to 10.0.0.0/9 with one VPN. Use http://www.subnet-calculator.com/ to help you work it out.

Besides the normal public and private subnets, I decided also to have a defined “AD” subnet and and a “routing” subnet. Call me too careful, but if I'm going to have domain controllers in an environment, I want to control access to them very carefully. The two extra “subnets” mean I can set up a security group (what a firewall is called in AWS) to restrict access to the read only domain controllers (RODC) only to the routing subnet, not anything in the “public” subnet. Same for internal firewalls as well – I can restrict access from the private subnet, but obviously need the domain controllers to connect.

For each purpose, you create a subnet in each availability zone – in Ireland that's three availability zones, so you have 12 subnets you create. To make the firewalls etc. easier, create the subnets in easy CIDR blocks – e.g. if you have the public in 10.128.1.0/24, 10.128.2.0/24 and 10.128.3.0/24, put the private in 10.128.16.0/24 etc., so you can treat 10.128.0.0/20 as all being public.

Subnet summary:

Routing subnet – use for any routing services, such as VPN and NAT

Public subnet – for any services accessible from the internet, e.g. load balancers. No access to internal network through VPN.

Private subnet – No direct internet access, through NAT only. Access to internal networks through VPN.

AD subnet – Same as the private subnet, but can be treated different internally.

As an aside – I did not use Cloudformation to create my VPC and subnets because I'm not planning on recreating them and it's a pretty small job to do it manually, but if you're creating lots you will want to.

In the routing subnet, you will need to create a NAT instance. I will use Elastic Beanstalk to put applications in the private subnet, which fails if they don't have internet access (AWS health checks fail if the instance doesn't contact it). Hopefully in my next post I'll share something on this, including my Cloudformation template.

I also used a VPN instance (OpenSWAN on linux) to connect my internal network with AWS. More on that later – in most cases you probably want to use a VPG and Amazon's VPN. Again, a Cloudformation template and a simple script will bring it all up with no manual intervention (and replace the instance if anything goes wrong).

You'll also want to create two routing tables. The Main table has a local route to 10.128.0.0/16 and a default route to the internet gateway and probably doesn't need any modification.

To keep the private and AD subnets private, you create a routing table with a default route through the NAT instance and a route to your internal infrastructure through your VPN. Then associate this with the private and AD subnets.

Hopefully more coming soon...