Add a PI AF database

You can add a PI AF database to an existing SQL Server availability group.

On the primary replica machine, back up the PI AF SQL Server database and log file. (The database is typically named PIFD.) On each of the secondary replicas, restore the backups of the PIFD database and log file at one time, with the No Recovery option; and Restoring mode for the database. Then add the database to the availability group on the primary, using the Join Only option.

1. On the SQL Server machine that is the primary replica of the availability group, open the SQL Server Management tool and connect to the instance that will host the availability group.

2. Expand the AlwaysOn High Availability folder.

3. Expand and right-click the Availability Groups folder and select the existing availability group.

4. Locate the primary replica machine, and right-click the availability group and select Add Database.

   The Add Database to Availability Group wizard opens to the Select Databases window.

5. Review the list of databases in the Select Databases window. This list shows databases that are installed in the instance of SQL Server within which the availability group is included. To set up an availability group for use with PI AF:

   1. Review the status messages for each of the databases listed in the Select Databases window, to determine whether a database can be selected. For example, a database cannot be used in an availability group if it belongs to an existing availability group, does not meet the prerequisites for being added to an availability group.

   2. Select the check boxes for the PI AF SQL Server database that you want to include in the availability group.

   You can also add other databases to the availability group; you can choose as many databases as you want, provided that the PIFD is included and that all databases that you include meet the prerequisites.

   1. Resolve any such issues before you continue with the availability group creation. You do not need to close the New Availability Group wizard to make corrections; leave it open while you return to SQL Server Management Studio to make corrections. After the corrections have been made, return to the New Availability Group wizard and click Refresh. When the database statuses indicate Meets prerequisites, you can continue with the process.

   2. Click Next.

6. In the Select Data Synchronization window, select the Join Only option and click Next.

7. In the Connect to Replicas window, click Connect All. Enter the required credentials to connect to the replicas when prompted.

   Click Next to open the Validation window. Most of the validation checks will be skipped, due to the type of Data Synchronization selected. For any results that show a result other than Success, click the Status link of the result and review the details.

8. If there are any errors, make the required corrections and click Re-run Validation. Continue until all errors have been corrected.

9. Click Next. In the Summary window, verify that your choices are accurate.

10. Click Script, if you want to save or copy the SQL script required to add the PI AF SQL Server database to the availability group.