John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

Data Scotland 2019 presentation

13th September 2019 By John McCormack 2 Comments

Data Scotland 2019 presentation:

My Data Scotland 2019 presentation: You can run SQL Server on AWS

Data Scotland 2019: You can run SQL Server on AWS from John McCormack

Useful links:

https://aws.amazon.com/rds/sqlserver/

https://docs.aws.amazon.com/en_pv/AmazonRDS/latest/UserGuide/USER_ConnectToMicrosoftSQLServerInstance.html

https://aws.amazon.com/blogs/developer/handling-credentials-with-aws-tools-for-windows-powershell/

https://johnmccormack.it/?s=aws

 

Filed Under: AWS RDS, AWS SQL Server, front-page Tagged With: #datascotland, datascotland2019, ec2, rds, SQL server

You can run SQL Server on AWS?

18th June 2019 By John McCormack 1 Comment


Barbecue food at DataGrillenMy presentation

I presented on running SQL Server on AWS at DataGrillen in Germany. Specifically, I covered the differences between RDS and EC2, migration options and the AWS CLI. Despite being a daunting prospect, I enjoyed speaking at a major conference for the first time.

I received a few questions and managed to answer most. For the one I could not remember, I promised to provide an answer.

I was asked who provides the SSL certificates when using SSL connections with your RDS instance. The answer:  When you create a SQL Server DB instance, Amazon RDS creates an SSL certificate for it. The SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks. Read more.

Link to slides, please feel free to view or download.

The event

DataGrillen is a community Data Platform event which is free to attend and draws speakers and attendees from across the world. It is so inclusive and welcoming, it’s easy to see why it’s a can’t miss event for so many many people.

Speakers dinner DataGrillen 2019
Speakers dinner
DataGrillen 2019 welcomes new speakers
Welcoming new speakers
DataGrillen 2019 lunch
Lunch
Chris Taylor talks containers
Chris Taylor talks Azure Devops
John McCormack and Chrissy LeMaire
With Chrissy LeMaire
DataGrillen 2019 beer drinking
Beers and networking
BArbecue food at Data Grillen
Barbecue food
DataGrillen 2019 after party
Some post Grillen drinks

Links to my other AWS content

  • https://johnmccormack.it/2017/12/ec2-sql-server-backups-to-amazon-s3/
  • https://johnmccormack.it/2018/08/how-to-create-a-table-in-aws-athena/

AWS documentation

  • https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html
  • https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ConnectToMicrosoftSQLServerInstance.html
  • https://docs.aws.amazon.com/cli/latest/reference/rds/restore-db-instance-to-point-in-time.html
  • https://docs.aws.amazon.com/cli/latest/userguide/install-windows.html#install-msi-on-windows 
  • https://aws.amazon.com/blogs/database/powering-up-database-mail-on-amazon-rds-for-sql-server-how-under-armour-runs-database-mail-on-amazon-rds-for-sql-server/ 

Thanks

John

Filed Under: AWS RDS, AWS SQL Server Tagged With: aws, DataGrillen2019, ec2, rds, SQL server

Running SQL Server on RDS SQLBits presentation

2nd March 2019 By John McCormack Leave a Comment

Running SQL Server on RDS

I presented on Running SQL Server on RDS at SQLBits. It was  a lightning talk, so only 5 minutes.

You can download my slides from here:

Running SQL Server on RDS

Further interesting links:

My more detailed post on running SQL Server on AWS (RDS vs EC2)

Amazon RDS for SQL Server FAQs

SQL Server Agent Information

Filed Under: AWS RDS, AWS SQL Server, front-page Tagged With: presentation, rds, SQL server, sqlbits

How do I run SQL Server on AWS?

6th November 2018 By John McCormack 3 Comments

How do I run SQL Server on AWS?

Running SQL Server on AWS can be done in 2 ways.

  • Relation Database Service (RDS): AWS’s managed solution where some of the administration (maintenance, backups and patching) is handled for you.
  • EC2: Your very own virtual machine in the cloud. With EC2, you manage SQL Server, just like you would do on-premises. This gives you full control over your SQL instance.

Why not just use Azure?

Azure may be the right choice for you. This purpose of this post is to show that you do have options and that Azure is not the only choice. Really, you should weigh up what you need from your cloud provider and make a choice.  Managing a multi-cloud environment can be challenging. If you are working for a company already heavily invested it AWS, it may make more sense to stay with AWS. Even if not, AWS’s SQL Server offering is mature and RDS’s features are improving all the time so you should consider what you can achieve with AWS.

Differences/Similarities/Pros and Cons

Feature EC2 RDS
Use SSMS ✔ ✔
Use database as source or target for SSIS ✔ ✔
Full control over the instance ✔
Automated backups ✔
Automated patching ✔
Always On Availability groups ✔ ✔
AWS-managed Multi AZ deployment ✔
Log Shipping  ✔
Replication  ✔
Database mail  ✔
Linked Servers  ✔

A full list of included and excluded features can be viewed at https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html 

RDS key facts

  • Limited control
    • The master user name you use when you create a DB instance is a SQL Server Authentication login that is a member of the processadmin, public, and setupadmin fixed server roles. It is not a member of the sysadmin fixed server role.
    • Anyone who creates a db is assigned to db_owner role on that DB.
  • More administration tasks are handled automatically e.g. backups, OS patching and SQL patching
  • Bring Your Own licence is no longer available on RDS
  • License included (covers multi AZ)
    • Standard
    • Web (Must only be used for public and internet-accessible webpages, websites, web applications, and web services.)
    • Enterprise
  • Backups
    • Can still be pushed to S3
    • Native backup and restore  using .bak files to/from S3
    • Versions: 2008 R2 – 2017
  • Limited to 30 databases per instance
  • Window/Mixed authentication
    • http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_SQLServerWinAuth.html
  • Multi AZ is very simple to set up
  • TDE at rest
  • SSL for data in transit
  • No database Mail

EC2 approach

  • Full control of instance
  • Bring your own licence (BYOL) options
  • Licence included (AMI)
    • Standard
    • Enterprise
  • High Availability Disaster Recovery HADR
    • You can use Always On Availability Groups
      • Automatic failover between AZs
      • Synchronous commit
      • Latency about 1ms to 2 ms
    • Manual failover between Regions option for increase resiliency

Backups

EC2, you are responsible for all of your own backups. Backups can be pushed to S3 to save reduce costs. I have a full post dedicated to SQL Server backups on EC2. You could also store backups on EBS volumes attached to your instance however this is much more expensive than storing on S3.

RDS will handle backups for you. You can restore to a point in time in RDS withing the backup window you configure. Transaction logs are taken every 5 minutes so you may experience data loss up to 5 minutes. All databases are restored with 1 second of each other. The downside to this is that transactions spanning multiple databases may not be recovered consistently.

Summary

Features are being added to RDS all the time however if you need features in the excluded list, you would need to go with an EC2 installation. However if your business size doesn’t allow for DBAs or database specialists, RDS may be the best option as AWS take care of all the installation and maintenance for you.

Resources

  • Microsoft SQL Server on Amazon RDS
  • Importing and Exporting SQL Server Databases
  • Restoring a DB Instance to a Specified Time
  • EC2 SQL Server Backups to Amazon S3

Filed Under: AWS RDS, front-page

  • « Previous Page
  • 1
  • 2
  • 3
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2025

 

Loading Comments...