SQL Server Databases and AWS Cloud Solutions
My Data Scotland 2019 presentation: You can run SQL Server on AWS
https://www.slideshare.net/JohnMcCormack33/data-scotland-2019-you-can-run-sql-server-on-aws
Useful links:
https://aws.amazon.com/rds/sqlserver/
https://aws.amazon.com/blogs/developer/handling-credentials-with-aws-tools-for-windows-powershell/
https://johnmccormack.it/?s=aws
I’ve put together a short video series on running SQL Server on Amazon RDS. It covers some basics and can be completed in around an hour, with several very short videos. It’s intended for SQL professionals who are just getting started with AWS, but might also be enjoyed by AWS users, looking for a relational database management system.
It’s my first attempt at putting together a training plan. So any feedback will be gratefully received.
The full series will be online by end of July 2020. – Look out for new videos being released daily.
1. Introduction to SQL Server on Amazon RDS (Scroll down)
2. Benefits and limitations of AWS RDS
3. Create your first RDS instance using the AWS console
4. Connecting to your AWS RDS instance
5. Advanced Configurations using Parameter Groups
6. Securing your SQL Server AWS RDS Instance
7. Automating Deployments using PowerShell
8. Backup and restore for AWS RDS
9. Providing High-Availability through Multiple Availability Zones
10. Monitoring Your Instances using CloudWatch
This post specifically discusses how to write your EC2 SQL Server Backups to Amazon S3. It should not be confused with running SQL Server on RDS which is Amazon’s managed database service. To back up to S3, you will need to have an AWS account and a bucket in S3 that you want to write to. You’ll also need a way of authenticating into S3 such as an access key or IAM roles.
Recently, I have been investigating running SQL Server in Amazon EC2. One issue to resolve is where to store database backups. Amazon S3 is far cheaper and has much higher redundancy than Amazon EBS (Elastic Block Store) so storing the backups here is an attractive idea. Furthermore, we can use rules in S3 to auto delete old backups or move them to Glacier which is cheaper than S3 Standard. However, there is no obvious or inbuilt way to write your EC2 SQL Server Backups to Amazon S3 directly.
| EBS – General purpose SSD | Amazon S3 | Amazon Glacier |
| $1228.80 | $282.62 | $49.15 |
SQL Server expects to be able to write backups to a file location (block storage) but S3 is Object Storage. This makes S3 unsuitable for direct backups. Newer versions of SQL Server (2016 onwards) also allow a BACKUP DATABASE TO URL option but this is designed for Microsoft Azure Blob Storage and as such, an S3 bucket cannot be used as the URL.
As such, we need a method to get the file into S3, either directly or indirectly because the storage cost is so much cheaper than EBS. Amazon recommend using File Gateway but this involves running another EC2 machine. This isn’t cost effective or easy to implement so I’ve discarded this method for now. The two options which I have tried and tested are below, they both have their pros and cons. I’ve also listed 3 other methods which may help you write your EC2 SQL Server Backups to Amazon S3 directly but these are untested by me as I rejected the approach.
A note on Ola
The Ola Hallengren backup solution (link) works well in EC2 when writing to EBS storage as this is just treated like a local drive. It also works well if writing to Azure Blob Storage. Whilst it also works with 3rd party backup tools such as LiteSpeed, it doesn’t cater for the latest LiteSpeed functionality that does allow writes directly to S3. I have written to Ola to ask if this is something that could be considered for a future release but until then, I am unable to use Ola’s solution.
Option 1 – Write to EBS and Sync with S3
Take a database backup using t-sql. Better still, schedule a job via SQL Agent to run at a set interval.
BACKUP DATABASE [Test] TO DISK = N'D:\MSSQL\BACKUP\Test.bak' WITH NOFORMAT, NOINIT, NAME = N'Test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
Using the AWS command line, copy the backup from your EBS volume to your S3 bucket. This should be step 2 of your agent job.
aws s3 sync D:\MSSQL\BACKUP s3://sqlpoc-backups/prod-backups
Delete the backup on EBS so the space will be available for the next backup. I have used PowerShell to do this but you can choose any method you prefer. This should be step 3 of your agent job.
remove-item D:\MSSQL\BACKUP\* -Recurse
Note: When sizing your EBS volume, it needs to be at least the size of your biggest database backup (with some room for growth). If this is the size you choose, bear in mind you can only back up one database at a time. If you size the drive bigger, you can back up more databases in the same job before syncing with S3 however this will be more expensive. With EBS, you pay for the size of the volume allocated, not just what is used. This is different to S3 where you only pay for data stored.
Option 2 – Write directly using LiteSpeed
Quest LiteSpeed is a 3rd party product for backing up SQL Databases. One very useful feature of enterprise edition is that it allows you to backup directly to Amazon S3. This is huge because it takes away the need for any intermediate storage, VMs or other services. It pushes your backup directly to the desired bucket location in S3. To do this, you’ll need to add a few new parameters to your backup calls but overall, it’s saves a load of hassle.
Note: This isn’t an ad for LiteSpeed, my decision to use it is down to my company already having a license agreement with Quest. If I didn’t have this in place, I would more than likely go with option 1, ultimately cost would be the deciding factor unless the difference was small.
Before writing your t-sql for the backup job, you will need to add your cloud account details to the LiteSpeed desktop app. Here you will need to pass in your Cloud Vendor, Display name, AWS Access Key, AWS Secret Key, region, storage class and bucket name.
exec master.dbo.xp_backup_database @database = N'Test', @backupname = N'Test - Full Database Backup', @desc = N'Full Backup of Test on %Y-%m-%d %I:%M:%S %p', @compressionlevel = 7, @filename = 'test-backups/Test-Full.bkp', -- folder name within bucket defined here @CloudVendor = N'AmazonS3', @CloudBucketName = N' sqlpoc-backups', @CloudAccessKeyEnc = N'xxxxxxxxxxxx’, -- an encrypted version of your access key (generated by LiteSpeed app) @CloudSecretKeyEnc = N' xxxxxxxxxxxx, -- an encrypted version of your secret key (generated by LiteSpeed app) @CloudRegionName = N'us-east-1', @UseSSL = 1, @OLRMAP = 1 , @init = 1, @with = N'STATS = 10', @verify = 1
As Amazon S3 is object storage, it does not support appending to an existing file so if using the same name each time for your backups, the previous backup is deleted and replaced. If you turn on versioning in your S3 bucket, you can keep old versions of the same filename. Or if each backup has a unique name (due to time stamp being appended), it will save them side by side. Do remember to have Object Lifecycle Rules turned on for all or specific items in your bucket to either delete old backups or send them to the much cheaper Glacier storage tier. If you don’t, your bucket size will grow very quickly and costs will escalate.
Option 3 – Map s3 bucket as a drive
If only this was easy. Doing this natively is not possible as far as I can tell. This comes down to a difference in storage types. S3 is object storage and for a drive, we need block storage. That said, there a number of 3rd party solution which I have not tested that seem to offer this service. A quick google suggests CloudBerry and Tntdrive as the most popular providers.
Option 4 – File Gateway/Storage Gateway
When investigating methods for saving my backups to S3, AWS support recommended using File Gateway. This would certainly overcome the technical challenge of getting the files into object storage (S3) as it allows you to mount an NFS Volume, but it adds complexity and cost to the process. File gateway is very useful as a method of connecting an on-premises appliance to cloud based storage however as the instances were already in the cloud (EC2), I didn’t feel like this was a very satisfactory solution.
Option 5 – Other 3rd Party tools
Other 3rd party tools exist such as CloudBerry which allow but I have not looked into them in detail. Prices start around $150 per server but they do offer volume discounts.
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url
https://support.quest.com/technical-documents/litespeed-for-sql-server/8.6/user-guide/8
If you use another method listed not here, I’d be delighted to hear about it in the comments.
Hi. I’m John McCormack and I’ve been working professionally with SQL Server since 2012. I spent 6 years as a production DBA with Monster and now work as an DBA Contractor. I love working with SQL Server but also with other cloud solutions. It’s a job that I love and I’m constantly learning.
Prior to deciding I wanted to work with computers, I had a wide and varied career. I joined RBS and worked my way through various positions including mortgage adviser and finished up as a manager in a contact centre. I was always looking for other jobs and skipped through a few other sales and management jobs with British Gas, and T-Mobile/EE but I was ultimately looking for a change.
It was in 2008 that I decided a full change of direction was required and I managed to secure myself a place on a Web Development degree at UWS. I changed to evening/weekend work at T-Mobile and started the degree. At the time, I was pretty sure I wanted to be a web developer but I found I really enjoyed the database modules. It was upon hearing a talk from a Database Architect about various career routes in the database world that I knew I wanted to be a DBA. Fast forward 4 years and I had earned my degree and had landed a job with Monster.
Landing my first database job as an associate DBA was only step 1. I knew that the next thing I had to do was learn, learn and learn more to become a competent DBA. Coming into IT 10 years later than I perhaps could have, I felt I had some catching up to do. To do this, I’ve learned from my colleagues, taken on challenges in work, studied for and gained MCSA in SQL Server 2012, then MCSE Data Platform. My next aim is to learn more topics about SQL Server in greater detail and to share my knowledge with the community as I go about. I’m have also achieved the AWS Cloud Practitioner and AWS Certified Solutions Architect – Associate qualifications.
I’m married to Julie Ann and have 2 great boys, Graham and Johnny. We live in Paisley, Scotland. When I’m not working, I enjoy high impact exercise classes such as Boxfit and Metafit.
You can follow me on twitter but I can’t promise I’ll only talk shop. I’ve been known to tweet quite a lot about football, in particular, Saint Mirren FC of whom I am a big fan.
I left what I considered to be a great permanent job to become a DBA contractor in Glasgow. It was one where I could pick some of my own projects, work with new and interesting technology and work in a team which collaborated well, liked each other and were good at sharing knowledge.
My reasons for moving on were two-fold. First of all, I wanted some career progression and it unfortunately just wasn’t available in my permanent job. Secondly, I wanted to work more with SQL Server and Azure. My last job started out as a SQL DBA but as a lot of people in this line of work will know, the duties have evolved considerably over time. This is mostly due to large scale cloud adoption. In my case, my workplace started heavily using AWS along with other RDBMSs and data platforms. (RDS MySQL & Aurora, ElasticSearch, Elastic Map Reduce (EMR), Glue and Athena). This gives you a lot to learn. Don’t get me wrong, I threw myself into learning about cloud solutions and I loved working with AWS (hopefully I will again) but as time went on, I was worried I would start to lose some SQL Server knowledge.
I have been working exclusively on the Microsoft Data Platform. At my first client, I’ve worked with SQL Server on-premises, SQL Server running on Azure VMs, Azure SQL DB, Azure Analysis Services and Azure SQL Data Warehouse.
The on-prem SQL Servers are mostly for hosting the databases of legacy applications and 3rd party vendor products. Some of the versions are ‘rather old‘ although these instances actually give the business the fewest headaches. I’ve worked on decommissioning unused databases that were still online, server side traces and native backups.
These host the databases that have the most active development work. I’ve looked at performance issues and SQL Server configurations to help improve performance. I’ve shared best practice with the team in terms of tempdb and helped to reduce the volume of unnecessary emails from servers. (You know the ones which someone set up years ago but never induce any action from the DBAs).
This was fun. I only did some basic configuration and permissions but as I’ve never used Analysis Services much over the years, it was cool to see what was involved.
It was great to get my hands on tech like this. I spent time modernising their manual point and click refresh process with PowerShell. This was a bit of a learning curve but extremely rewarding. This process will make future refreshes much easier. (I’ll share the code once I’m confident it’s good, I’d still like a few more run-throughs before doing this). Next month, I’d like to try to implement a self service process for the developers that allows them to do their own refreshes, freeing up DBA time to work on more critical items.
So far, I haven’t had to do too much with Azure SQL DB on this project. I’ve listed out the instances and instance types using PowerShell. My plan is to review if these are right sized or if efficiencies can be made and I’ll also make sure the alerting is set up correctly.
I’d like to offer some in depth server health reviews and work with the developers to help them make their code run faster. There is a lot of blocking due to long running stored procedures and sub optimal code. Improving a few of the big hitters will make them much happier and will ease the strain on some of our servers – at least that’s the idea.