Code post mortem
This code is not pretty, but it works. First, we get a list of
available servers using the EnumerateServers()
function. Next, we check the string for specific SQL Servers in the order
I want them to be used. Production if available; my laptop next; then my
desktop and finally my server. If none are found, it defaults to
production. Strangely enough, this is possible since the function to
enumerate network servers does not work for Workgroups and returns an empty
string unless there is a SQL Server running on the local machine.
Yes, it solved my problem, but I do not really like it that well. It
requires that the code in the application know about potential servers and, in
this case, at least, the connection string information is exposed in the
code, posing a security risk. It worked, yes, and I used it, yes,
but I kept my eyes open for a better solution ... and I found one, here in the
web pages of Database Journal.
Late one evening I was reading over my DBJ newsletter and I saw a reference
to a SQL Server article that mentioned the alias function of the SQL
Server Client Network Utility. Hmmm! What if I could create
an Alias to my SQL Server with the name of my client's SQL Server? That
would sure make things simple. The login password could be obfuscated by
the ADP, not being exposed in my code and as far as the application is
concerned, so long as it finds a server with the correct name and user login, it's
a GO for opening up.
Could it really be that simple? Yes it is. The two screen shots
below demonstrate how easy it is to create an alias server on a machine that is
already running SQL Server. Simply launch the Client Network Utility from
the SQL Server program item menu, select the Alias tab and click Add.
You will then be prompted with the following screen. Enter any alias
name and then provide the actual server name (or IP address as shown
below). Select TCP/IP as the protocol and click OK. That is all
there is to it. Now, my aliased server, RoofSQL, shows up in Query
Analyzer as an available SQL Server and when I open my ADP, it finds what it
thinks is the correct production server. Now THAT'S a solution.
This has not been the most elegant article I have ever written, though I
have mulled over its contents for weeks. The topic appealed to me
because, after searching the newsgroups for an answer, I came up empty and I
figure if I am asking this question, someone else may be also. Add to
that the even cleaner solution of creating an alias server and I figure I have
something worth writing about.
See All Articles by Columnist Danny J. Lesandrini