A year ago, Microsoft released SQL Server 2016, a cloud-enabled version of the popular database management software. Shortly after, Amazon Web Services (AWS) announced you could run SQL Server on AWS. Datavail just released a new white paper breaking down AWS for SQL DBAs. This blog post looks at the core concepts a Microsoft SQL Server DBA needs to know about using AWS. It boils down to six anagrams: AWS, SQL, RDS, EC2, S3, and VPC. Let’s get started!
Amazon Web Services (AWS) is a cloud hosting platform that offers inexpensive, scalable cloud services. As of July 2016, AWS offers support for Microsoft SQL Server. Amazon offers two ways to use SQL Server on AWS: using Amazon Elastic Compute Cloud (EC2) or Amazon Relational Database Service (RDS).
Supporting SQL Server on AWS is Amazon’s Simple Storage Solution (S3). In particular, there’s a simple way to back up your databases onto S3. The final anagram, VPC, stands for Virtual Private Cloud. AWS has tools for building and provisioning private cloud services for secure access to SQL Server in the cloud.
Amazon boasts that SQL server on AWS provides “new capabilities such as real-time analytics, visualizations across devices, deeper security, and new Big Data solutions for your mission-critical applications.”
You can purchase your Microsoft SQL Server 2016 license at the AWS Marketplace or you can transfer your existing license to AWS. Amazon supports four editions of SQL Server 2016: Express, Web, Standard, and Enterprise.
Using SQL Server on RDS, you will not be able to connect many of the tools you’re used to using, including OLEDB, ODBC and ADO.NET. That’s because RDS is a managed service where the software is patched, updated and maintained by Amazon. With EC2, maintaining the software is up to you.
Amazon offers a third choice for operating SQL Server on AWS, and that is “License Included.” With this option, you pay an hourly rate to use a licensed version of SQL Server running on RDS.
Amazon Relational Database Service (RDS) is a cloud-based managed service with user-friendly tools for setting up, managing and scaling relational databases. Security patches, software updates and other maintenance are handled by Amazon. If you want to get rid of the basic maintenance for your databases so you can focus on high-value reporting and analytics, RDS is the way to go.
SQL Server running on Amazon RDS allows you to use native backup and restore — especially useful during migrations. It offers data encryption and secure sockets layer (SSL) for data on the move. Automatic backups and failover are built into RDS.
With Amazon Elastic Cloud Compute (EC2), you are getting storage, primarily, and you are responsible for everything else. You must buy a Microsoft SQL Server license or transfer your existing license to AWS. You are responsible for updating the software, applying security patches, backups, replications, failover, and everything else that happens on the server. With EC2, you have complete control, including operating system access, which is not possible with RDS.
The biggest benefit of using EC2 is that you only pay for what you use. You can increase capacity automatically, so that your system scales for peak usage periods, and then take capacity offline when needs are low. There are price advantages to purchasing instances in advance, but the pay-as-you-go rates are also very attractive.
Amazon Simple Storage Solution (S3) offers scalable storage with a simple web interface.It provides a simple method for automatically backing up your databases. You can write the backup script using Microsoft SQL Server Management Studio or T-SQL. For step-by-step instructions on backing up SQL Server with S3, download the Datavail white paper, AWS for SQL Server DBAs.
Amazon Virtual Private Cloud (VPC) is a virtual network isolated from other virtual networks in the cloud. VPC allows you to create private clouds within AWS and launch AWS resources (such as EC2 or RDS) into a virtual network.
With AWS, you have the choice of using their default VPC configuration or creating your own using Amazon’s Cloud Formation tool. The choice of using Amazon’s VPC or creating your own is explored in greater detail in the Datavail white paper.
AWS provides two ways to install, operate and maintain Microsoft SQL Server deployments in the cloud. Amazon RDS offers a managed services solution with built-in support. Amazon EC2 offers a storage-only solution where you have operating system access to build your own SQL Server environment which you are responsible for maintaining. In both cases, Amazon S3 storage solution and VPC cloud creation tools are available to SQL Server DBAs.
Contact Datavail today for more information on how to set up and operate Microsoft SQL Server deployments in the cloud with Amazon RDS and Amazon EC2. Datavail, an AWS Consulting Partner, is a specialized IT services company focused on Data Management with solutions in BI/DW, analytics, database administration, custom application development, and enterprise applications. We provide both professional and managed services delivered via our global delivery model, focused on SQL Server, Microsoft, Oracle and other leading technologies. Our expert DBAs have helped dozens of companies migrate their SQL databases to the cloud, so you know you’re in good hands with Datavail.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.
Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.