Running SQL Server Linked Servers on AWS
Scott Zimmerman is a Partner Solutions Architect with AWS.
In this post, I’ll demonstrate how to set up SQL Server linked servers on Microsoft Windows Server in Amazon EC2. Linked servers allow you to join tables between database servers and distribute queries through stored procedures and views across servers, without even needing to change your application source code or manage multiple connection strings in your web tier.
Step-by-Step: Set up Linked Servers
To keep this brief, let’s deploy two Amazon EC2 instances with SQL Server and skip the details of setting up web or application tiers. Also, although we’d typically use Amazon Route 53 or Active Directory for DNS, or Windows Authentication in SQL Server, today we’re going to focus only on linked servers. For a smoke test, we’ll just run a simple query from one server against the other.
Note: Amazon EC2 offers a free tier for t2.micro instances, but running SQL Server requires a minimum of the m3.medium instance type. You can purchase these instances on an hourly basis from AWS, and even get them with SQL Server Standard Edition pre-installed (license cost included). If you choose to follow along with this article, you are responsible for any charges your account may incur.
Now let’s build a couple of linked servers in AWS:
- Log in to your AWS Management Console and click EC2. Click Launch Instance and select the AMI for Windows Server 2012 R2 with SQL Server Standard. If you plan to bring your own license for SQL Server Enterprise and install it yourself, you could instead pick the AMI for Windows Server 2012 R2 Base.
- In Step 2 of the wizard, Choose an Instance Type, select m3.medium.
- In Step 3 of the wizard, Configure Instance Details, change the number of instances from 1 to 2.
- Accept the defaults in Steps 4 and 5 of the wizard.
- In Step 6 of the wizard, Configure Security Group, leave the Create a new security group option selected, and you will see that an RDP rule has already been added. If you chose the SQL Server Standard AMI in Step 1, a rule is also added for SQL Server TCP port 1433, but if you chose the AMI without SQL Server Standard, then you need to click Add Rule here and choose MS SQL in the dropdown. You could also add a rule for All ICMP if you would like to test connectivity between servers with ping.
- Click Review and Launch, then click Launch. When you’re prompted for a key pair, either create one and download it to your workstation, or use a key pair you already have in AWS and on your workstation.
- After you launch the instances, it’s a very good idea to edit the Name column in the EC2 Dashboard to tag the instances as SQL1 and SQL2. The instructions below refer to the servers by those names.
- After a couple of minutes, the Instance State will change to running. Select only SQL1, and click Connect. Save the Remote Desktop file to your desktop as SQL1.rdp. Click Get Password (you may need to wait another minute for Windows to finish booting up). Browse to the key pair (.pem) file you saved earlier (this is probably in your downloads folder). Click Decrypt Password. Select the text of the password, copy it, paste it into a scratch text file, and save it on your desktop. If the text file includes a spurious space character after the password, delete that character.
- Repeat the above step for SQL2. You can save both administrator passwords in the same scratch text file.
- Open SQL1.rdp. (In Windows, it launches Remote Desktop Connection, hereafter called RDC. Alternative RDP client programs are available for Mac systems.) Log in as administrator using the SQL1 password you saved in the scratch file. Click Yes to connect without a remote certificate. Minimize that RDC window and launch another RDC window for SQL2, and log in as administrator using the second password you saved.
- If you plan to use your own SQL Server license and chose the AMI for Windows Server 2012 R2 Base in step 1, install SQL Server Enterprise now.
- SQL1 needs to be able to get the IP address of SQL2, but in this example, to keep our focus on SQL Server, we aren’t using Amazon Route 53 or Active Directory. On SQL1, open the file c:\windows\system32\drivers\etc\hosts in Notepad. Add the IP address and NetBIOS name of the SQL2 instance. To get the IP address, you can copy/paste the Public IP from the EC2 Dashboard (or run the ipconfig command in a Windows Command Prompt window on SQL2). Usually, you would want to list the actual NetBIOS name in the hosts file, found by running the hostname command on SQL2. But for this exercise you can simply list it as sql2, which will be a handy alias to use on sql1 when referring to the sql2 instance.
- On SQL2, start SQL Server Management Studio. You can find it by typing “sql server 2014 man” on the Start screen. In SSMS Object Explorer, right-click the server name and choose Properties. On the Security tab, change Server authentication to SQL Server and Windows Authentication mode (aka “mixed mode”). Right-click the server name again and restart the MSSQLSERVER service.
- In SQL2 SSMS Object Explorer, click Security. Right-click Logins | New Login. Since we’re not using Active Directory, let’s change the login type from Windows authentication to SQL Server authentication. Type a login name and password for the linked server to use. Clear the User must change password at next login check box. Don’t click OK yet. See Figure 1.
- There are many ways you should lock this down for tighter security, but for our quick experiment, let’s give this user permission to access the master database. Click the User Mapping page in the left navigation pane. Check the box for master. Click OK to save the user.
- Switch over to SQL1. In SSMS Object Explorer, click Server Objects. Right-click Linked Servers | New Linked Server. On the General tab, in the Linked server text box at the top, type the NetBIOS name of the SQL2 server. Remember, in the hosts file we simply used an alias name of “sql2” rather than the actual NetBIOS name. For Server type, check SQL Server. On the Security tab, check the Be made using this security context. Enter the user name/password that you created on SQL2 (see figure below). We could be more granular about impersonating local accounts as remote users, but this suffices without adding any rows in the upper grid. Note: If you plan to call stored procedures on SQL2, change RPC Out to true on the Server Options page.
- Click OK to create the linked server. If you get an error here, on the Security tab, ensure the Security Context name/password on sql1 match with the SQL user you created on sql2. On the General tab, ensure you checked the radio button for SQL Server for the Server type. Ensure that both instances are in the same EC2 security group with a rule that opens TCP 1433. Also, ensure that you can ping the NetBIOS name of SQL2 from a command prompt on SQL1. To verify that your new user login works on SQL2, you could disconnect your login in SSMS on SQL2 via your administrator account (using Windows Authentication) and then try to connect again in SSMS using your linked user name/password with SQL Server Authentication.
- On SQL1, open a query window and execute the query below. Note that the FROM clause uses a four-part syntax: computer.database.schema.table. Remember, in this exercise, we simply created an alias for SQL2 in the hosts file, so you don’t need to enter the actual NetBIOS name between the square brackets. If you do use the actual NetBIOS names, note that AWS defaults to NetBIOS names like Win-xxxx, and SQL Server requires square brackets for names with dashes.
SELECT name "SQL2 databases" FROM [sql2].master.sys.databases
You should see the list of tables in SQL2. Now that you’ve setup linked servers, and seen how the query syntax works, you should have an idea how to set up linked servers in your real applications. One advantage of doing this is that you could offload long-running queries to back-end databases without impacting the CPU on the primary server.
You can find many more resources for running Windows workloads in AWS, including whitepapers and Quick Starts, at this page: http://aws.amazon.com/windows/resources/.