We're in the process of moving a database to a new data centre. My plan for the migration is to update the DNS record that all clients use when connecting to it.
I've tested this by starting a process that does the following in a loop:
While that's running, I update the DNS entry to point to the new server.
The query continues to be executed against the old server unless I stop and restart the process.
I've tried the same when sending web requests using HttpClient and that seems to refresh it's DNS every 2 minutes, but the SqlConnection object hangs on to the original resolution until the whole process is restarted. I assume this is because the connection is pooled, and it doesn't re-validate the DNS entry when it gets the connection from the pool.
Ideally I wanted to avoid having to recycle app pools and restart services as part of the migration. Has anyone else experienced this behaviour? If so, did you find a way to force SqlConnection to re-evaluate the DNS entry it uses as the server name?
With connection pooling, the connection and underlying session will be re-used until the connection is removed from the pool and a new one created. You can force clearing of the connection pool by invoking SqlConnection.ClearAllPools to clear the pools of the current app domain. You can also turn off connection pooling entirely with the Pooling=False connection string keyword but be aware the performance hit may be considerable.