An application can store its database server name in more places than anyone remembers. A SQL Server alias or DNS CNAME gives you a stable name to switch during a server move.
Choose the Stable SQL Server Alias First
Inventory application connection strings, scheduled jobs, linked servers, report services, and administrative tools. A SQL Server client alias lives on each client machine and can differ between 32-bit and 64-bit clients. A DNS CNAME is centrally managed but depends on DNS behavior and the SQL Server network endpoint. I choose one redirection method deliberately. Mixing aliases and CNAMEs without an inventory makes troubleshooting harder. Record the target port and whether the application uses a named instance.
Test the stable name against the old server before moving data. That proves clients actually use it. A name that exists in DNS but is absent from the application configuration offers no migration benefit.
Prepare the New Instance
Build the target instance with required collation, version compatibility, logins, jobs, credentials, linked servers, and server-level settings. Run a backup and restore rehearsal with the actual database sizes and recovery model. Use RESTORE FILELISTONLY to inspect logical files and assign safe target paths. Do not use WITH REPLACE as a shortcut; verify the destination database name and files. I test application permissions on the restored copy, not only a sysadmin login. Orphaned users and missing Agent jobs can make a perfect restore look broken.
SELECT @@SERVERNAME AS connected_server,
SERVERPROPERTY('ProductVersion') AS product_version;
SELECT name, state_desc, compatibility_level
FROM sys.databases ORDER BY name;Plan the Cutover and Catch-Up
Choose a cutover method that fits downtime: final log backups and restore, log shipping, or an approved replication path. Freeze writes before the final synchronization and record the last backup or log sequence applied. Keep the old server available for rollback but prevent both sides from accepting writes after the switch. I write a step-by-step checklist with exact owners for application stop, final restore, name change, and validation. DNS TTL and client connection pools affect how quickly the stable name reaches every process.
Do not change the name before the new database is ready and security is tested. A fast CNAME update cannot repair a missing login or a database left in restoring state.
Preserve Kerberos Authentication
Windows integrated authentication uses SPNs for the service name clients request. A DNS alias or CNAME can require an MSSQLSvc SPN for the alias and port on the SQL Server service account. Check for duplicate SPNs and coordinate registration with the identity team. A connection can succeed while silently falling back to NTLM, so I query the session's auth_scheme after the switch. Delegation scenarios, including multi-hop applications, need their own test. One successful local login is not enough. Run on the server itself over shared memory, the query below reports NTLM, so run it from a remote client that connects over TCP.
SELECT c.session_id, c.auth_scheme, c.net_transport,
s.host_name, s.program_name
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s ON s.session_id = c.session_id
WHERE c.session_id = @@SPID;
Test the SQL Server Alias From Each Client Class
Connect from application servers, batch hosts, report hosts, and administrative workstations. Verify the connected server identity, database, login, encryption, and auth scheme. Test a real read and a controlled write. Check both 32-bit and 64-bit alias configuration when legacy clients use them. I compare application logs before and after cutover for retries and timeouts. A green SQL connection test from one host cannot represent every stored connection string.
What will happen to long-lived pools? Recycle them according to the cutover plan so they do not keep old physical connections. Track active sessions on the old instance until only approved administrative sessions remain.
Pick a SQL Server Alias or CNAME by Client Behavior
A SQL Server client alias is configured on each client and points to a server and protocol. It can be a good bridge for one controlled application estate, but it requires inventory and rollout on every host, including different client bitness where applicable. A DNS CNAME centralizes the name change, yet DNS TTL, caching, TLS certificate names, and Kerberos SPNs must align. I test the method with each driver used by the application. One old ODBC component can behave differently from a current .NET service.
Keep the stable name in application configuration even after the move. If teams begin connecting directly to the new host, the alias becomes a false promise for the next migration. I verify sessions and logs for direct connections before retiring the old name.
Practice the Point of No Return
Before cutover, restore a rehearsal backup, apply the planned final log sequence, and run application checks. Record the time needed for each step from the rehearsal rather than guessing the window. During production cutover, freeze writes, apply the final changes, switch the name, recycle pools, and test business operations. Once the new server accepts writes, rollback requires a data plan, not merely pointing DNS backward. I mark that point clearly in the checklist.
What evidence releases the old server? Confirm no application sessions remain, all jobs run on the intended instance, Kerberos is active where required, and backups start on the new host. Keep the old instance read-only or isolated according to the rollback plan until the observation period ends. A migration is complete only when monitoring and recovery move with the database.
Include certificate names and connection encryption in the alias test. A client can resolve the new host and still reject its certificate because the stable name is missing from the certificate. I test the actual driver with validation enabled. Disabling certificate checks to make cutover succeed creates a security regression that can outlive the migration.
Keep a Reversal Path
Define when rollback is allowed and how writes made on the new server would be handled. A simple DNS reversal is unsafe once new production writes exist. Record the decision point and data reconciliation plan before cutover. I keep the old name mapping and target backup available until validation is complete, then retire them under the normal change process. The stable alias pays off on the next move only if its ownership and SPNs remain documented.
The important verification is from the client side. The new server can be perfect while one application still points to the old address. Follow the stable name through every layer.
Related reading on this blog: Strange Error Related to Alias and SCOM: Alert: SQL Server Cannot Authenticate Using Kerberos Because the Service Principal Name (SPN) is Missing, Misplaced, or Duplicated.
A server move is not a finished restore, it is every client reaching the new name securely.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
