First published on MSDN on Jun 05, 2018
In continuation of the
on needing to provide the server name in your username when connecting, there are other scenarios and new features where this will need to be taken into consideration or make unnecessary!
Often times users will want to use custom domains for connecting to their servers, Example:
is our Azure SQL DB Server
is a CNAME record that points to the sqlprod01.database.windows.net record for our server.
This, like the previous blog scenario will result in an error when trying to connect if we do not provider our server name in the user.
As we are connecting to 'sql.mycompany.com' it is seen as 'sql' for the servername (highest level of the hostname provided). So while it can reach the
in Azure, we cannot find the server 'sql' as we want to connect to sqlprod01. Sure we can add @sqlprod01 to our username, myuser@sqlprod01, however this in many ways defeats the purpose of the hostname we are using in the first place.
With one of the new options available for Azure SQL DB Server, we are able to make a DNS Alias names for our server which can eliminate this need.
We can create an easily mappable alias that can be both an easier to read server name but also allow for an application to have a hostname using the alias which will not require a change even if the corresponding database was moved to a new server.
Let's take a scenario where we have three servers:
These servers are hosting many databases for many different clients. We will often move the databases around to balance for our needs but also may not want to give this name to our clients who want to connect from tools like SSMS or their applications. Using Powershell, we are able to add and manage aliases for these servers in order to make custom aliases for each tenant.
Here is an example script
on this but will follow the
cmdlets listed here
With this I can now have custom domain entries that are not only easier to read but can avoid having to put the server name in our connection string.
Now if for some reason we needed to move the customer's database to eastusserver002, the alias can be assigned to the eastusserver002 server using the
cmdlet. They can maintain the easy to read alias and do not need to update their connection strings.