AWS Prescriptive Guidance
Patterns

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

R Type :RePlatform

source :Databases: Relational

target :Microsoft SQL Server on Amazon EC2 (Linux/Windows)

tags :linux sql server, linked server, windows sql server

categories :Enterprise Applications, Independent Software Vendor, LifeCycle, Pattern Accreditation, Pattern Provenance, Pattern Type, Software Infrastructure

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.

Assumptions and Prerequisites

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 Used

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

Change authentication mode to Windows and SQL Server in Windows SQL Server

Tasks

Title Description Skills Predecessor
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

Restart the Windows MSSQL service

Tasks

Title Description Skills Predecessor
Open the context (right-click) menu for the Windows SQL Server instance and select Restart. DBA

Create new login and choose databases to access in Windows SQL Server

Tasks

Title Description Skills Predecessor
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

Add Windows SQL Server IP to Linux SQL Server host file

Tasks

Title Description Skills Predecessor
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

Create linked server on Linux SQL Server

Tasks

Title Description Skills Predecessor
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

Verify the created linked server and databases in SSMS

Tasks

Title Description Skills Predecessor
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

Verify that you can access Windows SQL Server database tables

Tasks

Title Description Skills Predecessor
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

Miscellaneous

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'tirulin',@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 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

References and Help

References

Contact and help

Migration Pattern Library Support: aws-mpl@amazon.com