EC2 and SQL Server Micro

As we are learning the new EC2 Amazon Cloud Services, I am compelled to post a “how to” when setting up and connecting to an AWS EC2 SQL Server box. This will answer many questions for others so they can get around the few issues that will creep up when Launching a new EC2 SQL instance. This same information can be used for other server launches too.

Steps to Allow Access

  1. Open the SQL Server Configuration Management application, 1) start SQL Services and set to start automatically, 2) start SQL Browser and set to start automatically; these are not started or set to start automatically when a new AMI instance is launched
  2. While in the SQL Server Configuration Management application, go to the node, Network Protocols and find the Dynamic TCP port under the IP ALL section. Make note of the port to be used later.
  3. Open the same following ports in the Windows Firewall:
    1. TCP <dynamicport> (from step #2)
    2. UDP <1434> (for the SQL Browser Service)
  4. In the EC2 AWS Security Group of the server add the following ports:
    1. TCP <dynamicport> (from step #2)
    2. UDP <1434> (for the SQL Browser Service)
  5. Turn off EC2 server hostname naming from the EC2 Administration Services application, rename the server according to your own server naming standards in the Computer | Properties panel, restart the box

Accessing the Server through SSMS

It’s important to note that there are four different ways to access the EC2 AWS SQL Server box from your local machine. To connect to a SQL Server Instance it’s necessary to use the name of the server and the port. If a default instance is setup, then the port, by default is TCP 1433, but this is not the case with EC2 AWS AMI servers.

The standard method to attach to the database server from an application or SSMS is “[<servername>][<ip>][<instance>][,<port>]”

  1. In order to use <servername>, you need to have some form of DNS setup, which is done automatically for an EC2, or you have done through Elastic IPs and your own DNS server (like GoDaddy). If you don’t have DNS, use the <ip> or <elasticip>
  2. To access the <instance>, the SQL Browser Service must be started and the UDP 1434 port open in AWS Security Group and the Windows Firewall.

Examples

aws.dns.server.name” comes from the properties of the EC2 instance properties. “ec2sqlexpress” is an example name of the SQL Server Instance. Using the IP must be the IP as provided through a VPC or the Elastic IP (not part of this tutorial). The port is represented as an example 411xx is not a real number, it must be what you find in the SQL Server Configuration Application.

  • Example 1 using DNS and SQL Browser Service: “aws.dns.server.nameec2sqlexpress”
  • Example 2 using DNS and port: “aws.dns.server.name,411xx”
  • Example 3 using IP (VPC) or Elastic IP and SQL Browser Service: “50.234.x.xec2sqlexpress”
  • Example 4 using IP (VPC) or Elastic IP and port: “50.234.x.x,411xx”

Other Considerations

There have been some statements that SQL Server can’t run on a micro. Well, that can be true for the big SQL editions like Standard or Enterprise, and my considerations would be 2048 (my recommendation) to start.

As for another topic, I’m always pinning my CPU’s and my RAM at certain values.

For another topic, I’m always pinning my CPU’s and my RAM at certain values, because all too many times do I find problems with keeping the default installation settings. This is to say that the default installation uses all CPUs and all RAM. I will put turn on CPU affinity and take out 10% of the CPUs, if you have 10 cores, I remove 1 or 2 for the OS and Networking services and other support activities to run. The same goes for RAM.

Leave a Reply