Linking SQL Servers across Azure Virtual Machines

robot

From my perspective as a .NET software developer, Azure is the best thing to happen to us since the introduction of C#. If you’re not yet taking advantage of the many features of Azure, you’re missing out on a number of productivity enhancers. This is particularly true if your IDE is Visual Studio.

In my case, I needed to evaluate one of our client-server desktop applications in a SQL Server 2016 environment. In addition, I needed to test some behaviors of the app across linked servers running on separate Windows Server systems. Five years ago, this would have required expensive investments, and weeks to set up. Now, with Azure, it was accomplished in a day at very low cost.

Since I haven’t seen a post specifically describing how to create linked SQL Server instances running on separate VM’s in Azure, I’m including a description of the process here in hopes it might be of use to others. In my case I used SQL authentication for expediency, but with some extra work you can also configure an Active Directory environment in Azure.

Here’s an overview of the non-Active Directory process:

– Create an Azure account and login using the new Azure web portal
– Create a resource group to associate with both VM’s
– Select the appropriate Windows Server and SQL Server VM templates
– Set up two new VM’s in the same resource group
– From the portal, add an inbound security rule for MS SQL on both servers
– Use the portal “Connect” link to obtain RDP connections for both instances
– Use RDP to remote to the VM’s to carry out the remaining steps
– In SSMS, turn on SQL authentication for both SQL instances
– Restart SQL Server on both systems
– Create test databases on both systems as needed
– Create SQL logins for the accounts to be used for linking
– For these accounts, set “User Mappings” to allow access to the test databases
– Define linked servers using SSMS on both systems
– Set “Server Type” and “Provider” of the links based on your linking accounts

Once the links are established, you can evaluate queries from one system to the other using four-part naming or synonyms. With the VM’s running and SQL Server linked, you can install your applications and test any aspect of the system. All in one day for pennies!

Some additional notes:

  • Azure name resolution within the resource group allows you to reference systems by name
  • SQL authentication presents security risks, so stop or deallocate the systems when not in use
  • The inbound security roles permit remote access from SSMS on your desktop
  • Tools such as Red-Gate’s “SQL Compare” support syncing schemas into Azure databases
  • If your schema includes CLR procedures, remember to enable CLR in your databases
  • Starting and stopping your VM’s may result in new IP address assignments

 

References:

Azure name resolution: https://docs.microsoft.com/en-us/azure/virtual-network/virtual-networks-name-resolution-for-vms-and-role-instances

Creating SQL linked servers: https://msdn.microsoft.com/en-us/library/ff772782.aspx

(Original post on December 26, 2016)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s