Have you ever been required to update your SQL Server environment and needed to test it?  I know, rhetorical question.  I am in such a process currently.  We have the entire gambit of project killers in play as well.  We have short deadlines, requirements to maintain current technologies (i.e. DTS) in the new environment, consolidate rack space (ok not really a project killer), and wrong parts/equipment being shipped.  Through all of this we must test two different versions of operating system to determine which environment to build the new servers.  To accomplish this we had two test servers setup with SQL 2008, and each is on a different platform.  One is on SQL 2008 (32 Bit) and the other is SQL 2008 R2 (64 Bit).  The testing of these two environments is explicitly to determine that both will work appropriately and produce desirable results, particularly in the realm of our DTS packages.

I am not going to go into details on how to test all of that.  Rather, I want to discuss a quick way to get such environments ready to go without the need to manipulate each of the packages to have them redirect to entirely new servers.  Remember, the idea is to test these production packages in a test environment and ensure they will function properly.  The only way to do that is to mimic the environment.  When you create new test servers, you could be required to change log locations, connection strings and possibly some queries inside the packages in order to make these packages work in the test environment.  If the production boxes were no longer in play and all we had were these new servers, the easiest solution by far is to create a CNAME entry in DNS with the old server name and point that to the IP address of the new servers.  This does not work when you must test while the production server still exists.  There are also other options – such as a new Test network to perform this.  If that sort of environment exists – by all means use it.  We don’t have the time nor the equipment to create such a setup (maybe someday later – our dev environment is not a distinct network separate from the production network either).

What to Do?

The first thing that came to mind to try and cover all aspects of this type of situation was to alter the Hosts file on the servers.  In Server 2008 R2 that is a bit different to do due to the need to launch notepad first in administrator mode.  Once I had the Hosts file on each of the servers altered to redirect to the local box should the prod server name be encountered, I set off to begin testing.  It did not take long to discover that this would not work.  When trying to create a connection in SSMS an error was thrown.  The error was in relation to the user being from an untrusted domain.  Well that wasn’t possible since the user was a legitimate domain and there is only one domain in play.  Fileshares were redirected as desired, just SSMS did not want to play.

Since that was a failure, the next idea I had was to create an alias on each of the servers and see if that worked any better.

Notice in this screen from the Server 2008 R2 environment (not SQL 2008 R2) that there are two client configuration sections.  In order to create an alias for use by SSMS, the 32bit version must be configured.  Once this alias is in place, one is able to reference in SSMS the Alias Name and be redirected right back to the local databases.  This is progress!  When SQL Agent attempts to connect to a Servername encountered in a connection (database) string in a dts package, it will use the alias.  This is real progress!

This does not cure all things though for testing.  The alias and the Hosts reconfig don’t work in conjunction one with another.  Thus, if one has any logs being written out to the file system then the logs will be written out to the actual server and not the aliased server.  This is true of logs attached to job steps and logs from within the DTS package.  Thus, if you are doing either of those things – some job and package editing may have to take place.  For the logs being written from job steps – I recommend not writing out to the file system and rather write it out to a table.  Writing out to a table will also reduce significantly the changes required in packages and their Agent jobs.  This will help you get to quicker testing.

A final note about the aliases.  If you try to change the Alias in the SQL Native Client 10.0 configuration section, you will not get the desired results.  Be careful on where you make these sorts of changes.