Access on-premises Microsoft SQL Server tables from Microsoft SQL Server on Amazon EC2 using linked servers
Created by Tirumala Dasari (AWS) and Eduardo Valentim (AWS)
Environment: PoC or pilot | Technologies: Databases | Workload: Microsoft |
Summary
This pattern describes how to access on-premises Microsoft SQL Server database tables running on Microsoft Windows, from Microsoft SQL Server databases running or hosted on Amazon Elastic Compute Cloud (Amazon EC2) Windows or Linux instances by using linked servers.
Prerequisites and limitations
Prerequisites
An active AWS account
Amazon EC2 with Microsoft SQL Server running on Amazon Linux AMI (Amazon Machine Image)
AWS Direct Connect between the on-premises Microsoft SQL Server (Windows) server and the Windows or Linux EC2 instance
Product versions
SQL Server 2016 or later
Architecture
Source technology stack
On-premises Microsoft SQL Server database running on Windows
Amazon EC2 with Microsoft SQL Server running on Windows AMI or Linux AMI
Target technology stack
Amazon EC2 with Microsoft SQL Server running on Amazon Linux AMI
Amazon EC2 with Microsoft SQL Server running on Windows AMI
Source and target database architecture
Tools
Microsoft SQL Server Management Studio (SSMS)
is an integrated environment for managing a SQL Server infrastructure. It provides a user interface and a group of tools with rich script editors that interact with SQL Server.
Epics
| Task | Description | Skills required |
|---|---|---|
Connect to Windows SQL Server through SSMS. | DBA | |
Change the authentication mode to Windows in SQL Server from the context (right-click) menu for the Windows SQL Server instance. | DBA |
| Task | Description | Skills required |
|---|---|---|
Restart the SQL service. |
| DBA |
| Task | Description | Skills required |
|---|---|---|
In the Security tab, open the context (right-click) menu for Login and select a new login. | DBA | |
In the General tab, choose SQL Server authentication, enter a user name, enter the password, and then confirm the password and clear the option for changing the password at the next login. | DBA | |
In the Server Roles tab, choose Public. | DBA | |
In the User Mapping tab, choose the database and schema you want to access, and then highlight the database to select database roles. | Select public and db_datareader to access data from the database tables. | DBA |
Choose OK to create a user. | DBA |
| Task | Description | Skills required |
|---|---|---|
Connect to the Linux SQL Server box through the terminal window. | DBA | |
Open the /etc/hosts file and add the IP address of the Windows machine with SQL Server. | DBA | |
Save the hosts file. | DBA |
| Task | Description | Skills required |
|---|---|---|
Create a linked server by using the stored procedures master.sys.sp_addlinkedserver and master.dbo.sp_addlinkedsrvlogin. | For more information about using these stored procedures, see the Additional information section. | DBA, Developer |
| Task | Description | Skills required |
|---|---|---|
In Linux SQL Server in SSMS, go to Linked Servers and refresh. | DBA | |
Expand the created linked servers and catalogs in the left pane. | You'll see the selected SQL Server databases with tables and views. | DBA |
| Task | Description | Skills required |
|---|---|---|
In the SSMS query window, run the query: "select top 3 * from [sqllin].dms_sample_win.dbo.mlb_data". | Note that the FROM clause uses a four-part syntax: computer.database.schema.table (e.g., SELECT name "SQL2 databases" FROM [sqllin].master.sys.databases). In our example, we 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. | DBA, Developer |
Related resources
Additional information
Using stored procedures to create linked servers
SSMS doesn't support the creation of linked servers for Linux SQL Server, so you have to use these stored procedures to create them:
EXEC master.sys.sp_addlinkedserver @server= N'SQLLIN' , @srvproduct= N'SQL Server' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'SQLLIN',@useself=N'False',@locallogin=NULL,@rmtuser=N'username',@rmtpassword='Test123$'
Note 1: Enter the sign-in credentials that you created earlier in Windows SQL Server in the stored procedure master.dbo.sp_addlinkedsrvlogin.
Note 2: @server name SQLLIN and host file entry name 172.12.12.4 SQLLIN should be the same.
You can use this process to create linked servers for the following scenarios:
Linux SQL Server to Windows SQL Server through a linked server (as specified in this pattern)
Windows SQL Server to Linux SQL Server through a linked server
Linux SQL Server to another Linux SQL Server through a linked server
