SQL Server – Setting Up CNAME Alias for AlwaysOn Listener

The requirement that a database have a full backup is only enforced by the Wizard. If you just Add the database after the Availability group is already created you can restore the same backup on both the Primary and Secondary and then add the database to the Availability Group. To save time during the maintenance window we usually restore Full backups ahead of time and take a differential backup during the maintenance window.

1. Take Full backup of the database being moved.
2. Restore the full backup on the New primary WITH NORECOVERY this ensures the database isn't used until the secondary is ready.
3. Restore the same full backup on the new secondary WITH NORECOVERY this is needed to add them to the AG.
4. Restore the primary database WITH RECOVERY. RESTORE DATABASE [MyBigDB] WITH RECOVERY;
5. On the Primary instance navigate to AlwaysOn High Availability > Availability Groups > MyAG > Right-Click on Availability Databases > Add Database
6. Select your database
7. Select Join only
8. Connect to the secondary instance
9. All the validation should be skipped
10. Verify everything and click finish or Save the script.
11. Verify syncronization is happening by viewing the Availability Group Dashboard

Summarizing what we discovered in the chat.

When connecting over the network if the Windows Server is registered in Active Directory (AD) with a Service Principal Name (SPN), clients that try to connect with Integrated Authentication will get the Kerberos token from AD for the AD object that matches the server name to attach to the authentication. Kerberos won't be used when either the server that is being connected doesn't have an SPN or is being connected using SQL Authentication.

In this case, because "OLD-SQL" is still registered in AD and has an SPN registered spoofing, the DNS in the local host file won't work when using Integrated/Windows Authentication, as there is nothing adjusting which token is returned/used from AD. There doesn't appear to be a way to have the client know to use Kerberos token for "NEW-SQL" with the original "OLD-SQL" server still being in service, except to remove the "OLD-SQL" SPN from AD to force the authentication to fall back to NTLM. Spoofing the DNS works for aliases in which are not actual computer names registered in AD or do not have an SPN registered, as AD doesn't return a token for them, so the authentication falls back to using NTLM.

References

Understanding Kerberos Keys

Registering an SPN