How to Provision EC2 Instance

Similar to your local electric utility company, Amazon Web Services (AWS) offers you the flexibility to run Microsoft SQL Server on demand. You can run your SQL Server instances for as much or as little time as you needed. It is the perfect example of elasticity in action.

There are two options to choose from when you want to run SQL Server on Amazon cloud:

1)     Run on Amazon EC2 (Elastic Compute Cloud)

2)     Amazon RDS (Relational Database Service) managed service

SQL Server on Amazon EC2

Using Amazon EC2 is pretty much like running SQL Server on-premises. This is because the database administrator of the organization will be responsible for database administrative tasks such as backup and recovery, security management, database tune-up, and configuration, in the EC2-hosted SQL Server. One other benefit is that you can easily provision and configure DB instances and storage, as well as scale the DB instances by changing the size of the instances or storage amount.

SQL Server is offered on Amazon EC2 in the following AWS regions:

• U.S. West
• South America
• Asia Pacific (Seoul/Sydney/Mumbai/Singapore/Tokyo)
• Asia Pacific (Sydney)
• EU
• AWS GovCloud

Following SQL Server versions are supported on Amazon EC2, with their editions:

• SQL Server 2016 (Express, Web, Standard, Enterprise)
• SQL Server 2014 (Express, Web, Standard, Enterprise)
• SQL Server 2012 (Express, Web, Standard, Enterprise)
• SQL Server 2008 R2 (Express, Web, Standard)
• SQL Server 2008 (Express, Web, Standard)

Before provisioning SQL Server on Amazon EC2…

You need to consider several infrastructural aspects before provisioning the SQL Server on Amazon EC2, as follows.

1. Network

By default, AWS instances use a single network card (NIC) for all traffic. You can choose AMI depending on your needs. There are different Windows AMI available and supported for SQL Servers:

• Windows Server 2016
• Windows Server 2012 R2
• Windows Server 2008
• Windows Server 2003

2. Storage

It is recommended to use EBS-optimized instances for your most demanding applications. You can change the storage device later. However, that would require restart of SQL server, so plan carefully.

3. Server size

Amazon offers several server size options to choose from. To identify the correct AWS instance, all you need to do is to map it to different SQL Server classes, by comparing the memory requirement of the SQL Server. AWS instances are grouped into instance types and there are several instance types.

• M3 General Purpose — Inexpensive, supports up to 8 cores and 30 GB memory
• R3 Memory Optimized — Supports up to 32 cores and 244 GB of memory
• I2 IO Optimized — Costs $7/hour. Offers 6.4TB of memory in local SSD

Installing the SQL Server on Amazon EC2

• • Download the SQL Server installable
  • Install SQL Server
  • Open port to the public
    • Start the “SQL Server Configuration Manager”
    • Click “SQL Server Network Configuration”
    • Open “Protocols for SQL Server”
    • Right-click “TCP/IP” –> Click “Properties”
    • Under “Protocol”: Set Enabled to Yes
    • Under “IP Addresses”: Set all “TCP Port” values to 1433
    • Restart the SQL Server instance
    • Go to the Windows firewall
    • Click Advanced Settings
    • Add new inbound rule which allows 1433 TCP connections

EC2 or RDS?

Having two options, there’s always the question on which one is better, and the typical answer would be: it depends on your application and the requirements. However, according to a white paper by Amazon, running SQL Server on EC2 is the better choice, if you:

• Require full control over the DB system, including the OS and the rest of the software stack
• Require host access for SSIS
• Need to make use of additional SQL Server features that are not supported by RDS
• Expect the database administrative tasks to be performed in-house

If you would like more information about how provision EC2 instance, please contact Datavail today. Datavail 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 Microsoft, Oracle and other leading technologies.

For additional resources please download white paper: Making the Move to SQL Server 2016.