Redirecting Database Queries to another Server

Question

I have to move a production SQL Server database onto another server soon, and I'm keen to catch any little programs which have been written by hard coding the server name into them. I have only recently inherited the system, and there seem to be a lot of Excel files scattered around connecting in using ODBC connections.

My initial thought was to find something that redirected any SQL server requests for a particular database to the new server, but I'm not aware of any such beastie. Anyone know if such a thing exists?

Ideally, it should redirect at server level and log the source so that I can go and change it to point to the new one.

Answer 1

If the server name is hard coded, why not give the new server the same hostname? If it's listed in DNS set the TTL to a very low value now (2 minutes) and then when the changeover happens the downtime of moving the actual database files across will help smooth over the transition.

Alternatively you can be evil, turn the other server off and force people to fix their apps when stuff breaks - which method you choose is down to your sysadmin style.