Create a SQL Server availability group for use with PI AF

Complete this procedure to create a SQL Server AlwaysOn availability group for use with PI Asset Framework. For more information about how to create a SQL Server availability group, see the Microsoft article Use the Availability Group Wizard (SQL Server Management Studio).

1. On the SQL Server machine that will serve as 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 New Availability Group Wizard.

4. Enter a name for the availability group in the Specify Availability Group Name window and click Next.

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 (typically named PIFD) 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 PI AF SQL Server database 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.

   If you select Create an availability group listener now use the New Availability Group Wizard to:

   • Enter the Listener DNS Name.

   • Enter the Port number.

     For more information, see the Microsoft articles Configure a Server to Listen on a Specific TCP Port and Connect to an Always On availability group listener.

   • Set the Network Mode as Static IP

   • Click the Add button

   • Select the correct subnet and enter the static IP address as the IPv4 Address in the Add IP Address window.

6. Click Next in the Specify Replicas page:

   1. Add each SQL Server instance that is designated as a secondary replica for the availability group in the Replicas tab.

      For each added secondary replica, configure its Automatic Failover, Synchronous Commit, and Readable Secondary settings. Refer to Microsoft's Replicas Tab help for additional information.

   2. Verify that the SQL Server Service Account for each replica is set to the correct domain and user account in the Endpoints tab.

   Do not change the other settings on this tab. Refer to Microsoft's Endpoints Tab help for more information.

   1. Select the Prefer Secondary option as the location where backups will occur, or another selection if you prefer a different option on the Backup Preferences tab.

   Refer to Microsoft's Backup Preferences Tab help for more information.

   1. Choose one of two options in the Listener tab:

   • Create an availability group listener now

   • Create an availability group listener later

   If you select Create an availability group listener later use the New Availability Group Wizard to:

   • Enter the Listener DNS Name.

   • Enter the Port number.

   • Set the Network Mode as Static IP

   • Click the Add button

   • Select the correct subnet and enter the static IP address as the IPv4 Address in the Add IP Address window.

     See the Microsoft article Specify Replicas Page (New Availability Group Wizard: Add Replica Wizard) for more information.

7. Click Next in the Select Databases window.

8. Select the Full option in the Select Initial Data Synchronization window.

9. Enter, or browse to and select, the network share location created before you started the availability group creation process and click Next.

10. Review the information in the Validation window. For any results other than Success, click the Status link for the result and review the details. Some issues can be resolved before you continue and then the validation step can be re-run. Other issues must be resolved manually after the availability group is created. When you are satisfied with the validation results, click Next

11. Review the information about the choices you made in the Summary window.

12. If changes are required click Previous to move back through the wizard to make any required changes.

13. Click Finish to create the availability group.

    The Results window displays.

For any steps that result in an error, click the error link for the result and review the details. It might be necessary to manually complete the configuration of the availability group if there are failed steps.