Defining a Linked Server with sp_addlinkedserverTo add a linked server, open your local database in SQL Server Management Studio and execute a sp_addlinkedserver statement. You'll need to specify a number of parameters:
- @server : the name you want to use to refer to the database in queries. Use a simple, memorable name. In our example, we'll use abc.
- @datasrc : the data source (Azure database server), such as clients.database.windows.net
- @catalog : the database to connect to. We'll use ABC_PROD in our example.
- @provider : set to SQLNCLI.
- @srvproduct : set to empty string.
Defining a Linked Server LoginTo go with your Azure linked server you'll need to define a login for it. Use a sp_addlinkedsrvlogin statement with these parameters:
- @rmtsrvname : name of the linked server (same value you used for @server in the previous section). Example: abc
- @useself : set to 'false'
- @rmtuser : username. Example: dbrpt
- @rmtpassword : password. Example: s4ssafr4ss!
Viewing Linked Server in SSMS Object ExplorerAfter performing the above steps, refresh your SSMS Object Explorer and you should see your linked server. If you defined everything correctly, you'll also see the database objects available, such as tables and views.
For troubleshooting, there's also a handy Test Connection option if you right-click the linked server name.
Querying the Azure Linked ServerTo query your linked database, use the prefix [server][database] before specifying schema/table, where server is the value your used for @server and database is the value you used for @catalog in the first step above. For example,
SELECT * FROM [abc].[ABC_PROD].[dbo].person
Now that you have a working linked server, you can include it in database queries that join to the local database or other linked databases. In the query below, the Azure linked database is joined to a local database table.
Including Azure Linked Server in Cross-Database Queries
There you have it: queries that combine cloud and local data through the use of a Linked Server.