Creating a SQL Server Availability Group

Before you begin

BMC Database Automation (BDA) can discover SQL Server Availability Groups which you can then approve to use in BDA. For a walkthrough, see Walkthrough: Discovering and approving SQL Server Availability Groups.

Prerequisites for creating a SQL Server Availability Group

The following are Microsoft's prerequisites for creating Availability Groups:

• Availability Groups can only be created for SQL Server 2012 or later.

• Computers hosting the SQL Server instance that is part of the Availability Group must be nodes in the same Windows Server Failover Cluster (WSFC).

• You cannot have more than one instance on the same node participating in an Availability Group.

• Each instance participating in an Availability Group can only host one replica of the Availability Group.

• Only a single SQL Server instance can host the primary replica of the databases in an Availability Group at one time.

• You cannot have instances of different SQL Server versions in the same Availability Group, and Microsoft only supports SQL Server Enterprise Edition Instances for creating Availability Groups.

To create a SQL Server Availability Group

1. From the Management Console, navigate to the cluster on which you want to add an Availability Group.

2. Select Provision > Add SQL Server Availability Group.

3. In the Naming page, populate the following fields, and click Next.

   Field	Description
   Domain	Displays the BDA domain for the Windows cluster hosting this Availability Group.
   Windows Cluster	Displays the Windows cluster hosting this new Availability Group.
   Primary Instance	Select the initial primary instance for this Availability Group. This instance will host the initial primary replica for the Availability Group you are about to create, and should contain the databases you want to replicate.
   Compatible Secondary Instances	Displays the instances in the cluster that can be used as secondary instances to form an Availability Group with the selected initial primary instance. Note: You cannot have instances of different SQL Server versions or editions in the same Availability Group. If there are no secondary instances with the same version or editions as the primary, the message, "There are no compatible secondary instances available for the selected instance" appears.
   Unavailable Instances	Click Show to display the list of instances in this cluster that are not available for this Availability Group and the reason for the incompatibility. Click Hide to no longer display the list.
   Availability Group Name	Type the name of the Availability Group. BDA validates the name to ensure that there are no duplicates or that correct syntax is used to name the group. Note Availability Group names must be valid SQL Server Regular Identifiers (as documented in ms175874) and must be unique within the WSFC that they are in.
   Template	(Optional) Select from the list of defined Mssql Availability Group templates to automate input during the wizard data collection. If you select a template, you may click Skip Ahead to advance directly to the next step that requires input. Any steps that have been configured by the information in the template will not appear.

4. In the Databases Selection page, populate the following fields, and click Next.

   Field	Description
   Available Databases	Select one or more databases from this list of databases on the selected instance that will participate in the Availability Group. BDA searches for the databases on the instance that meet the prerequisites for participating in an Availability Group.
   Unavailable Databases	Displays other databases in the instance that do not meet the prerequisites for participating in an Availability Group, the size of the databases, and the reason that they are not available.

5. In the Replicas Selection page, populate the following fields, and click Next.

   Section	Field	Description
   Available Instances	Instance	Select one or more SQL Server instances in the cluster that will host secondary replicas. Note Each instance in an Availability Group must reside on a different host.
   	Initial Role	Displays the initial role that this instance will have in the Availability Group (Primary or Secondary).
   	Automatic Failover (Up to 2)	Select if, In the event of an unplanned failover, you want this instance to become the primary replica of the Availability Group. A maximum of two instances may be selected for Automatic Failover.
   	Synchronous Commit (Up to 3)	Select if, when changes are committed to the primary database, the committed changes are not complete until they are written to all secondary instances that have Synchronous Commit set. Synchronous Commit is a prerequisite of automatic failover, and selecting Automatic Failover will automatically enable Synchronous Commit for the selected instance.
   	Readable Secondary	Select whether this instance will be available as a readable secondary. Yes. All connections are allowed to secondary databases of this replica, but only for read access. The secondary database(s) are all available for read access. No. No user connections are allowed to secondary databases of this replica. They are not available for read access. This is the default setting. Read-intent only. Only connections that specify read-only access are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
   Unavailable Instances	Instance	Displays instances that do not meet the prerequisites for participating in this Availability Group. This list will be updated as the user selects instances for secondary replicas if those selections preclude other instances from participating in this Availability Group.
   	Reason	Displays the reason that the instances cannot participate in the Availability Group, typically because one or more of the prerequisites have not been satisfied, or the selection of another instance on the same node precludes it.

6. In the Endpoints page, configure the endpoint settings for the selected instances and click Next.

   Field	Description
   Instance	Displays the selected primary and secondary instances.
   Initial Role	Displays the initial role that the selected instances will have in the Availability Group.
   Requires Restart	A value of Yes in the field indicates that this instance does not already have the AlwaysOn Availability Group feature enabled, thereby requiring that BMC Database Automation restart the instance to enable the feature. A value of No indicates that AlwaysOn is already enabled and doesn't require a restart.
   DNS Name	Specify the DNS name to use to reach the endpoint for high-availability data replication by the other instances in the cluster. This field is the only configurable field for an instance when that instance is already participating, or has participated in the past, in an Availability Group.
   Port Number	Specify the port number for the endpoint (the default is 5022). If the clustered instance on a node already has an endpoint for high availability data replication, the next available number greater than 5022 will be selected as the default. This field is read-only if that instance is already participating, or has participated, in an Availability Group.
   Endpoint URL	Displays the URL for the endpoint, based on the DNS Name and Port Number.
   Endpoint Name	Specify the endpoint name name (the default is Hadr_endpoint). This field is read-only if  that instance is already participating, or has participated, in an Availability Group.
   Encrypt Data	Select whether or not to encrypt the data being sent between this endpoint and other endpoints in the Availability Group. All the endpoints in a given Availability Group must have the same encryption setting. This field is read-only if that instance is already participating, or has participated, in an Availability Group.

   In the Backup Settings and Priorities page, populate the following fields, and click Next.

   Section	Option/Field	Description
   Backup Preference	Backup Preference	(Required) Select which replicas SQL Server should use for performing automatic backups. The default value is set based on the template that you selected in Step 3. If no template was specified, no default is selected. Prefer Secondary. Specify that SQL Server should prefer the secondary replica when performing a backup. If there are no secondary replicas online, the backup occurs on the primary. Secondary Only. Specify that SQL Server must always backup a secondary replica, where no write operations are occurring and the replica can be taken offline for the backup. If the primary replica is the only replica online, the backup should not occur. Primary. Specify that SQL Server should always back up from the primary replica. This option is useful if you need backup features, such as creating differential backups, that are not supported when backup is run on a secondary replica. Any Replica. Specify that SQL Server should ignore the role of the availability replicas when choosing the replica to perform backups. Note backup jobs might evaluate other factors such as backup priority of each availability replica in combination with its operational state and connected state.
   Replica Backup Priorities	Instance	Displays the selected primary and secondary instances.
   	Initial Role	Displays the initial role that the selected instances will have in the Availability Group.
   	Backup Priority (Lowest=1, Highest=100)	Type a number from 1 to 100 to represent the priority of each instance. The following are example settings and the result that those settings have on backup priority: 50. If all the secondary replicas are set to 50, SQL Server selects from them randomly. 100. If all the secondary replicas are set to 100, SQL Server selects from them randomly. 100 and 90. If you have one secondary replica with a backup priority of 100, and another secondary replica with a backup priority of 90, SQL Server chooses the replica with a backup priority of 100 over the secondary replica with a backup priority of 90, as long as the secondary replica with a backup priority of 100 is available.
   	Exclude from Backup	Select this option to exclude the replica from the backup. This will specify that SQL Server should never backup from that replica.

7. In the Listener Configuration page, configure the Availability Group listener, and click Next.

   Field	Description
   Create Availability Group Listener	(Optional) Select to create a listener for this Availability Group. An Availability Group listener responds to incoming client requests and it redirects the request to the SQL Server instance that is currently hosting the primary replica.
   Listener DNS Name	(Required if Create Availability Group Listener is selected) Type the name of the listener. The DNS name of the listener must be unique in the domain and in NetBIOS.
   Port	(Required if Create Availability Group Listener is selected) Type the number of the TCP/IP port on which the listener should listen. Only the TCP protocol is supported by availability group listeners.
   Network Mode	(Required if Create Availability Group Listener is selected) Select the network mode of the listener, which must be either DHCP or Static IP. DHCP. If an availability group resides on a single subnet, you can configure all the availability group listener IP addresses to use DHCP by selecting the appropriate subnet from the dropdown. For pre-production environments, DHCP offers an easy setup for an Availability Group that does not require disaster recovery to a remote site on a separate subnet. However, you should not use DHCP in conjunction with an Availability Group listener in a production environment. This is because, in the event of down time, if the DHCP IP lease expires, extra time is required to re-register the new DHCP IP address associated with the listener DNS name. The extra time will cause client-connection failure. Static IP. Selecting this mode displays the Static Addresses section where you select the network name and type the IP address for the instance in the IP Address field for the corresponding network subnet. This section lists all non-private network interfaces available in the Windows Cluster. The network names that appear in this section vary depending on your environment. In production environments, Microsoft recommends that Availability Group listeners use static IP addresses. Furthermore, where Availability Groups extend across subnets in a multi-subnet domain, an availability group listener must use static IP addresses.

8. In the Synchronization Settings page, select what type of synchronization is used to create the Availability Group, and click Next.

   Section	Option/Field	Description
   Synchronization Options	Full	Specifies that BDA use a network file share to back up the databases from the primary replica and restore them on the secondary replicas prior to creating the availability group. Selecting this option enables the remaining sections on the page.
   	Join Existing Databases Only	Specifies that BDA create the Availability Group only. This option presumes that you have already performed the backup and restore of the databases on each of the instances, or that you will do so during a pre-provisioning script. If databases do not exist at the time BDA attempts to join secondary instances to the availability group, BDA will fail the job. Click the Skip Database Verify option to specify that BDA run the job without first testing for databases. Note This is an advanced option that can be used to bypass database-related verification tests (for example, in jobs where the database will be created by a pre-provisioning script that you have configured).
   Network Share Options	Use existing network share	(Full synchronization only) Enables you to use an existing file share for the full synchronization. Type a fully_qualified network share URL in the Existing Network Share Name field.
   	Create new network share	(Full synchronization only) Enables you to create a file share for use in full synchonization on the node that is hosting the initial primary replica. Full access for this share will be granted to the users that each of the SQL Server instances participating in the availability group are running as. Selecting this option displays the following options: Host. Displays the name of the host that is hosting the primary replica and on which the network share will reside. Network Share Name. Type the name to assign the network share. Network Share URL. Displays the fully-qualified URL of the network share that will be created. Network Share Path. Type the name of the directory to create on the server to host the network share. Network Share Users. Click Add to add any additional users who will have full access to the network share. Domain: Type the name of the domain on which the users reside. Username: Type the name of the user As part of the job, BDA creates the network share and backup and restores the databases to that share.
   Move Databases to Instance Default Location	Move Databases to Instance Default Location	(Full synchronization only) Select this option to create the databases in the default location for that instance. It is deselected by default. Note Selecting this option enables you to avoid the impact of differing file path names between the primary and secondary instances. However, if you later add databases with a different path than the primary, you must remember to select this option again or the add operation may fail. You can ignore this option if you create databases with the same path on all instances.

9. In the Custom Scripts Configuration page, select any pre-provisioning or post-provisioning scripts that you want to run as follows, and click Next.

   Section	Description
   Pre-Provisioning Script	(Optional) Select the scripts that you want to run before this event and click the right arrow to move them to the Selected list box. The scripts in Selected are run in the order listed. Use Move Up and Move Down to re-order the scripts when necessary. See also Managing pre-provisioning and post-provisioning script files.
   Post-Provisioning Script	(Optional) Select the scripts that you want to run after this event and click the right arrow to move them to the Selected list box. See also Managing pre-provisioning and post-provisioning script files.

10. In the Job Options page, specify any of the following Job options, and click Next.

    Type	Option	Description
    Notification Emails	Email List	(Optional) Click Add Email to add email addresses to the notification list.
    Job Administration	Cleanup Agent Logs	(Optional) Select to automatically remove logs that are generated by BMC Database Automation on the Agent after the Job is complete.

11. If the Change Control page displays, populate the following fields, and click Next.

    Section	Field	Description
    	Bypass Change Control	(Optional) Select to bypass using BMC Remedy ITSM to control the change process. Selecting this option removes the rest of the fields from this page.
    	Change Control Options	Select to determine whether to open a new BMC Remedy ITSM change ticket, or to use an existing BMC Remedy ITSM change ticket to control the change process. Selecting Use Existing Change Ticket displays the fields in the Use Existing Change Ticket section on this page. Selecting Create New Change Ticket displays the fields in the Create New Change Ticket section on this page.
    Use Existing Change Ticket	ITSM Change ID	Specify the BMC Remedy ITSM change ticket number to associate with this change process.
    	ITSM Task ID	Specify the BMC Remedy ITSM task ID number to associate with this change process.
    Create New Change Ticket	Change Type	Assign the type of change for the new BMC Remedy ITSM change ticket.
    	Change Impact	Assign the impact level of this change process for the new BMC Remedy ITSM change ticket.
    	Change Urgency	Assign the urgency level for the new BMC Remedy ITSM change ticket.
    	Change Risk-Level	Assign the risk level of this change process for the new BMC Remedy ITSM change ticket.
    	Change Class	Assign the change class for the new BMC Remedy ITSM change ticket.
    	Change Summary	Type a summary of the change process.

    Note

    The Change Control page appears only when change control is configured for your environment. See Configuring change control.

12. In the Summary page, review the provisioning information.
    To make changes, do the following:
    1. Click Go to to return to the provisioning step that you want to edit.
    2. Make your changes.
    3. Click Save and Review to return to the Summary page.
13. Specify your verification preferences.
    For additional information, see Understanding pre-verification tests.
    • To continue creating the Availability Group after the pre-verification tests have been successfully run without manual intervention, select Automatically Continue If All Tests Succeed.

    • To create the Availability Group without having to first run the pre-verification steps, select Skip Pre-Verification Tests.

      Note

      The Skip Pre-Verification Tests option should only be used when you are certain all tests can succeed. The option skips verification and advances directly to the actual provisioning activity.

14. Click Create SQL Server Availability Group.
    A progress page appears followed by a success message upon completion. The databases, replicas, and listeners now appear in the Contents page for this SQL Server Availability Group. The new Availability Group appears as a child of a Windows Cluster in the Grid, designated by a double-database icon (). For more information about the information on the Availability Group Configuration page, see Walkthrough: Discovering and approving SQL Server Availability Groups.

    Note

    Detailed current and historical information about this action can be viewed in the Jobs section of the Management Console. See Monitoring jobs and viewing job history.

    To add a database to an Availability Group, see Adding a database to a SQL Server Availability Group. To configure failover, see Failing over a SQL Server Availability Group.