Access on-premises Microsoft SQL Server tables from Microsoft SQL Server on Amazon EC2 using linked servers - AWS Prescriptive Guidance

Access on-premises Microsoft SQL Server tables from Microsoft SQL Server on Amazon EC2 using linked servers

Created by: AWS

Environment: PoC or pilot

Technology: NotApplicable, 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 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 Linux EC2 instance

Architecture

Source technology stack

  • On-premises Microsoft SQL Server database running on Windows

  • Amazon EC2 with Microsoft SQL Server running on Amazon 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

  • SSMS- 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 and SQL Server from the context (right-click) menu for the Windows SQL Server instance. DBA
Task Description Skills required
Open the context (right-click) menu for the Windows SQL Server instance and select Restart. 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 Miscellaneous 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

 

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 user name and password 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