We have a server 2008 R2 with SQL 2008 instance. The underlying hardware RAID is failing and we need to migrate the databases to a stable piece of hardware with the same hostname on the network quickly. If we remove the existing server off the network to allow the new hardware to be joined to the domain with the same hostname will that allow us to reattach/restore the databases from the former server without having to change client based connection strings/ODBC? If this fails, will restarting the old server and rejoing it to the domain mean that the databases/aplications still connect ok?
I ask this because I do not fully understand how the clients connect to the instance…does it just use DNS and connect with the server hostname and instance name or does it use the underlying SID to identify what to connect to?
Apologies for my ignorance.
Best Answer
Yes, your plan will work. There are no hidden dependencies that will cause connections to fail. The client doesn't know anything about the SIDs of the server or server logins. Whatever the client might learn about the server is not retained from one session to the next.
If you want a bit of a smoother transition, you can run a script on the old server to create scripts that will create the logins on the new server with the same SIDs as the old server. If you do this, you won't have to remap the database users to the server logins after restoring the databases. See Sync users and logins.