Question:

How to migrate from SQL server failover cluster to AlwaysOn availability Group cluster ?

Answer: 

Below is high level steps/guidelines on migration from SQL server failover cluster to AlwaysOn availability Group cluster and confirmed  by Engineering. 


Pre-requisites

1. SQL Server Availability Group has been pre-created

2. SQL Server nodes have been added to the Availability Replicas

3. Availability Group Listener has been added and customer has verified that they can connect to it using the SQL Server Management Studio

4. All the SQL server principals that are specific to DirectAudit (DA) e.g. Collector accounts, DA auditors, DA administrators that exist on the current database server have been copied-over/created on each of the nodes that are part of the Availability Replicas. This has to be done manually as neither the SQL backup-restore process nor the DirectAudit will automatically create these server principals on the new nodes. The simplest way to achieve this is by going to the SQL Management Studio > Security > Logins, then script "CREATE Login" for each of the collector machine accounts, DA auditors and DA administrators account and save it to a file and run it on each of the nodes in the Availability Replicas.

Estimated time to complete - Around 1 hour (depending on number of databases)

Steps for migration -

1. Stop all the Collector services so that the database is no longer receiving any new information (audit data)

2. Take a full backup of all the databases (Management database and all attached Audit Store databases)

3a.)Restore the databases on the primary SQL node in the Availability Replicas using the backups taken in step#2.

3b.)Add the MultiSubnetFailover=True under the AdditionalConnectionParameters column right after the databases are restored on the target server 

4. (Optional) - Take the original databases offline just to be on the safer side

5. Ensure that [sa] is the owner of the databases restored in step#4. If unsure, just run the following SQL command for each of the restored databases

ALTER AUTHORIZATION ON DATABASE :: [<db_name>] TO [sa]

6. Ensure that the trustworthy flag is on for each of the databases restored in step#4. If unsure, just run the following SQL command for each of the restored databases

ALTER DATABASE [<db_name>] SET TRUSTWORTHY ON

7. Change the database recovery mode to FULL for each of the databases restored in step#4

8. Take a full backup of each of the databases

9. Add all the databases to the Availability Group using the standard method (SQL Server Management Studio > Availability Groups > Availability Databases > Add Databases wizard) 

10. Modify the Vegas-Installation object - Customer will probably need your help on this step. In a nutshell, you need to find the Vegas-Installation-xxx object in Active Directory for this DA installation and modify the values of the serviceBindingInformation attribute so that the Data Source is the FQDN of the Availability Group Listener and not the FQDN of the original database server. Some additional tips on this... 

•  There could be multiple Vegas-Installation-xxx objects in the AD belonging to the same DA installation; you'll need to update all of them. 
•  Depending on the number of databases in the DA installation there could be more than two rows of values in the serviceBindingInformation attribute; you'll need to update each of them for the new Data Source. 
• If your SQL Availability group listener connection string contains port number, make sure you specify it as Data Source with an escaping backslash (e.g. Data Source=mylistener.acme.com\,1234) 

11. Modify the value of Server column of the dbo.AuditStoreDatabase and dbo.ManagementDatabase tables so that the Server column is the FQDN of the Availability Group Listener and not the FQDN of the original database server (similar to previous step). FYI... Both these table are part of the Management database. Unlike the previous step however, you don't need to escape the comma with a backslash if you're specifying port number (e.g. you can just put the value as mylistener.acme.com,1234 without the backslash) 

12. Start the collectors and make sure they're online. If the collector throws database permissions error, launch the Connector config wizard and see if re-configuring the collector fixes the issue or not. 

****Please note*****
Adding these values to DB does not automatically update the SCP in AD. To work around, Please open Audit Manager console > connect to the DA installation and sync the publication right after step#12