Occasionally we come across issues when moving databases from one SQL server to another. One of the most tricky situations is when you cannot get access to the applications configuration settings for the database you are required to move.
This can be for various reasons, like forgotten passwords, poor application design or where the configuration settings are hard coded into the application itself.
For situations like these you need to think outside the box, especially if there are time constraints involved.
There is a little known tool that comes with Windows called the SQL Server Client Network Utility. One of the handy things this tool allows you to do is register SQL server instances as aliases.
Therefore you can force all SQL traffic for a particular SQL server instance to another SQL server instance for the server hosting the application.
Please note, I think this tool should only be used only as a last resort when you cannot configure the application through the normal method.
To configure a SQL server instance as an alias, please follow the steps below:
- Log onto the server hosting the application as an administrator
- Stop any relevant services
- Start > Run > cliconfg
- Click on the Alias Tab > Click Add
- Under Server alias enter the old SQL server instance
- Under Server name enter the new SQL server instance
- Uncheck Dynamically determine port and enter port 1433 (or an appropriate SQL port if changed from standard)
- Click OK
The alias screen should then look similar to the following.
- Click Apply>Click OK
- Start any appropriate services
- Test the application that it is using the new SQL server via the activity monitor