SDU Tools: Create a linked server to Azure SQL Database from SQL Server
When I need to move small amounts of data between an on-premises SQL Server system and an Azure SQL Database, or run queries that involve both systems, the easiest option at present is to create a linked server to Azure SQL Database.
And the easiest way to do that (or if you can’t remember the commands) is with one of our free SDU Tools for developers and DBAs, called CreateLinkedServerToAzureSQLDatabase. You can read it to check out the code, or use it like this:
The parameters are as follows:
@LinkedServerName sysname - name that will be assigned to the linked server - defaults to AzureSQLDB @AzureSQLServerName nvarchar(max) - name for the Azure SQL Server eg: myserver.database.windows.net @AzureSQLServerTCPPort int - port number for the Azure SQL Server (defaults to 1433) @AzureSQLDatabaseName sysname - name of the database (defaults to master) @RemoteLoginName sysname - login name for the Azure database @RemotePassword nvarchar(max) - password for the Azure database @SetCollationCompatible bit - is the remote server collation compatible (default is true) @SetRPCIn bit - should rpc (remote procedure calls = stored procedure calls) be allowed (default is true) @SetRPCOut bit - should rpc output be allowed (default is true)
Once you’ve done that, you will see it in the list of Linked Servers as in the main image above. In that image, I’ve created one called AzurePublicData. It contains a database called PublicData, and the database contains a number of tables.
You can then query the tables using four part names:
You can see it in action here:
To become an SDU Insider and to get our free tools and eBooks, please just visit here:
http://sdutools.sqldownunder.com
2018-06-27