One of the issues that is often raised with Windows Azure SQL Database is that you don't get to pick the name of your server, so you end up with a bizarre name such as:
yy2l95dk1k.database.windows.net.
I can understand why the team did this. Apparently when they first set it up, they allowed you to pick your own server name, so everyone started registering Coke, Pepsi, etc. Not wanting to have yet another place for people to argue about name ownership, they quickly removed that ability. I'm glad they did.
However, when working with a databases, I've been finding that I'm constantly looking at lists of Azure servers and having no idea which one is which. When I open SQL Server Management Studio (SSMS), and ask to connect, I'm greeted with a list of servers that looks like:
- yy2l95dk1k.database.windows.net
- ky4296dk1k.database.windows.net
- xy9914dk2j.database.windows.net
and so on. Now I'm sure there are people that can remember which one is which, but as the number of servers increases (particularly when I'm dealing with client servers as well as my own), I'm not one of those people that can.
Normally when I'm working with a bunch of servers and I only have IP addresses, I configure SQL Server Client Aliases for each address. However, when I first tried to configure an alias like AzureDemo for a server called yy2l95dk1k.database.windows.net, I found I couldn't connect to it. I received an error that said "Server name cannot be determined. It must appear as the first segment of the server's dns name (servername.database.windows.net). That led me to believe that using an alias wouldn't work.
However, in later versions of this error message, more information is provided. "Some libraries do not end the server name, in which case the server name must be included as part of the user name (username@servername). In addition, if both formats are used, the server names must match. (Microsoft SQL Server, Error: 40531). Once again, when I first saw this, I presumed that it would still stop me from using an alias but that is not the case.
To use a meaningful alias, what you need to do is:
- Using SQL Server Configuration Manager, in the SQL Native Client 11.0 Configuration\Aliases node, create an alias. Pick a meaningful name (ie; HRServer) for the Alias Name, leave the port at the default (ie: 1433), leave the protocol at the default (ie: TCP/IP) and provide your real server name (ie yy2195dk1k.database.windows.net) in the Server field.
- Create the same alias in the SQL Native Client 11.0 Configuration (32bit)\Aliases node.
- When connecting to the server in SSMS, enter your new alias (ie: HRServer) for the Server name, SQL Server Authentication for the Authentication method, and for the login, specify your login name followed by an @ symbol, followed by the first segment of the real server name (ie: mylogin@yy2195dk1k), then enter your password.
- If the user isn't an admin user that has the ability to connect to the master database, you'll also need to manually set the database name using the Options button. (Note that you won't be able to browse for database names).
Once you have done that with all your servers, finding and connecting to the right server should be easy.
In a separate post, I'll describe how to push out aliases to other users in your domain.
This is a great post, it's going to save me a lot of time looking up each alias against which client is which.
Looking forward to how to push it out the rest of my domain too!
Thanks very much.
Hi Greg thanks for a useful post. I am presuming you are talking about using group policy to push out the client alias registry entries which is something I first mentioned on my blog in 2009 http://bit.ly/9BXhDm and demoed at SQLBits 7 -video can be found here http://bit.ly/XCpnTF
I most recently discussed it during my PASS summit presentation on upgrading to SQL 2012, and I always find it amusing since until now 4 years later I have never heared any one else mention it.
Looking forward to seeing whether there are any differences or considerations with respect to SQL Azure database instances.
Yes, indeed. I'm surprised you haven't heard it mentioned before. That's how I've always done it. I used to teach the folk in the SQL Masters program about it back when the course ran in Redmond. I think it's a very straightforward way to deal with the issues.
After all, an alias is just a registry entry and so it can be pushed out by group policy like any other registry entry.
Alternatively, you could just store the connection information as a registered server in SSMS. Personally, I do not store passwords in my connection information, but it enables you to have a user defined display name for the server.
Brilliant!
Thx, you save my time!
Great post, very well explained