Run SQL Server in the Cloud

Overview

We will create an Amazon Instance that runs SQL Server.
In these steps we will learn how to:

1. Create a new Amazon EC2 instance
2. (Optional) Create a new volume drive (that we will use to store our SQL data on). Do this if the data you are storing is less than a few gigabytes).
3. Connect to the new instance
4. Install SQL Server 2008 R2 CTP November build
5. Create a SQL user to be used remotely
6. Open ports to connect to SQL Server

Spinning up the instance

1. Login to the AWS Management portal at https://console.aws.amazon.com/
2. Select the region you want to spin an instance up. By default US East is selected. For this example, we selected US West to reduce latency issues with connecting to remote desktop and other services. You can change the region in the left (see Figure 1).

 

How to Run SQL Server in the Cloud - image 1Figure 1: Preparing to launch an instance using the AWS console

 

3. After selecting your region, click the Instances link in the menu to open up the My Instances tab.
4. Select Launch Instance to begin the create instance process (see Figure 2).

Figure 2

5. Select the My AMIs tab from the top menu and change the Viewing dropdown menu to: All Images, then scroll down to the preferred instance snapshot. For this run,weI want to spin up a Windows Server 2008 virtual machine on a 32-bit machine. The reason I choose 32-bit is so I could select the instance size as small (see Figure 3).

Figure 3

6. In this section, you will want to create one (1) instance, and the size determines the pricing model. In step 5 above, I chose a 32-bit machine, so under the Instance Type dropdown menu I can select the cheapest instance size, Small (m1.small) (see Figure 4). In the Availability Zone dropdown menu, we kept it as the default No Preference, but if you created your Volume drive ahead of time, you will want to make sure your instance zone and your volume zone are the same.

Figure 4: SQL Server in the Cloud:

7. We used the default options for the next section of instance details.
8. When asked about using a Key Pair, you will want to create a new Key Pair if you have not already done so. Doing so will generate a private key that we will want to keep, so download the file to a location where you can retrieve it later.

Figure 5: SQL Server in the Cloud:

9. Next we’ll create a set of firewall rules. If you have one already made, you can use that one. Otherwise, create a new security group and be sure to add “MS SQL Server” to your exception list as shown in Figure 6.

Figure 6

(Optional) Spinning up the volume

1. Select the Volumes link in the left menu under the Elastic Block Store. This opens the EBS Volumes window pane (see Figure 7).
2. From the EBS Volumes pane, click the Create Volume tab.

Figure 7

3. Choose the size of the volume you wish to connect. You want to make sure Availability Zone is the same zone as the instance you created (see Figure 8). For this purpose, we are using “No Snapshot.”

Figure 8

4. Next, click the Create button to begin creating the volume. This operation may take long to complete (see Figure 9). Be sure to click the refresh button on the top right of the menu to refresh the AMS controls so you know when the volume is created.

Figure 9

5. Now attach the volume to the AMI instance you created. From the Instances dropdown menu, select the Instance ID that you want to attach the volume to. In the Device field, enter the device drive – you can use drives xvdf through xvdp (see Figure 10). In this example, we used “xvdg” (not shown).

Figure 10

Connecting to the new instance

1. Return to the My Instances tab by clicking the Instances link on the left-side navigation menu. Select the instance you created by clicking on the checkbox and select the dropdown list called Instance Actions. Here you will first obtain the Administrator password and then connect to the instance.

Figure 11

2. Select the Get Windows Admin Password and follow the steps to obtain the password. The RSA key should be in the <keypair>.pem file you downloaded when you created a new key pair in the launch instance phase (Spinning the Instance, step 8).
3. Once you have the Admin password, select the instance action again and choose Connect.

Install SQL Server 2008 xx

1. Open up Internet Explorer and download SQL Server 2008 R2 CTP November. http://www.microsoft.com/sqlserver/2008/en/us/R2Downloads.aspx the 32-bit version (if you installed 32-bit).


NOTE: For some reason, extracting and running this install is painfully slow, it took almost 2 hours last time we ran this. Please be patient for the installer to pickup.

2. Running the executable (.exe) extracts the files into the folder it was contained in (unless specified differently). You will need to go to that directory and run the Setup executable (setup.exe) to begin the installation.
3. (Do this only if you attached a volume earlier) During the installation, the setup wizard will prompt you to select the directory to install into. Be sure to change the drive letter from C: to D: (or whichever drive letter your external volume was attached to).
4. Other parts of the installation will prompt to select who has access over services. In our example, we chose to set all of them to the Network service as owner.

Create a User to access SQL remotely

1. Open up SQL Studio Manager (Start -> All Programs -> SQL Server Studio 2008 R2 November CTP -> Sql Server Studio Manager (SSMS)

Login to your newly created server (it should be the default selected server when you start). Expand the Security tab and right-click on Logins and select New Login.

 

 

 

 

 

 

 

 

 

 

 

 

Figure 12

2. Create new credentials and be sure to select SQL Server authentication as the method (see Figure 13).

Figure 13

3. (If required) Select the User Mapping tab on the left. Select the master database and check the db_owner checkbox (see Figure 14). This should allow your user to create new databases.

Figure 14

Open up Firewalls

1. If you have not already added MS SQL Server to your Security rule list in the AWS Manager Console, do so now. (This was done before in the Create Instance stage)
2. Add an exception to Windows Firewall
   1. Start -> Administrative Tools -> Windows Firewall with Advanced Security
   2. Select Inbound Rules
   3. Add new Rule for port 1433 as SQL Server
3. Enable Remote connections to SQL Server
   1. Start -> All Programs -> SQL Server 2008 R2 November CTP -> Configuration Tools -> SQL Server Configuration Manager
   2. Expand SQL Server Network Configuration and select Protocols for <INSTANCE>
   3. Enable TCP/IP and Named Pipes (see Figure 15).
   
   
    Figure 15
   
   
4. Select SQL Server Services and restart SQL Server (<INSTANCE>)

Complete

At this point, you should be able to access SQL Server remotely.