Walkthrough: Discovering and approving SQL Server Availability Groups
BMC Database Automation (BDA) enables you to create SQL Server Availability Groups beginning with SQL Server 2012. This topic describes how BDA discovers Availability Groups and how you can approve them so that you can begin performing database mirroring activities.
It includes the following sections:
Introduction
SQL Server AlwaysOn Availability Groups is a new high availability replication technology for SQL Server clustered instances that enables you to duplicate databases across instances in a Windows Server Failover Cluster (WSFC). An Availability Group maximizes the availability of a set of user databases, known as availability databases, that fail over together.
An Availability Group supports a set of read-write primary databases and one to four sets (in SQL Server 2012) or one to eight sets (in SQL Server 2014) of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access or some backup operations. An Availability Group consists of a primary availability replica and one to eight secondary replicas. Each replica is hosted by an instance of SQL Server on a different node of the WSFC cluster.
To illustrate another basic example, let's say you have a two-node cluster, with one instance named TOPGUN that contains database DB1, and another instance called TAILGATE that doesn't contain any databases. You can create a SQL Server AlwaysOn Availability Group that copies all the data from DB1 over to a new DB1 on TAILGATE and, as another copy is made on TOPGUN, the data will get replicated onto TAILGATE. If you have to take a node down for some reason, you can move the primary replica to the other instance. So, in our example, if DB1 on TOPGUN is replicated, a new database DB5 can be added to a different Availability Group on the same two instances. You can then designate TOPGUN as the primary, but designate TAILGATE as the primary for the other Availability Group that contains DB5.
Deviations from other database types
SQL Server AlwaysOn Availability Groups was introduced by Microsoft supporting SQL Server 2012 and later to support high availability technology. It is not used for other databases supported by BDA. BDA closely mirrors how the SQL Server Management Studio implements Availability Groups in its UI, although there are deviations from that model in certain cases.
Because Availability Groups are a feature of SQL Server Instances, you must patch the instances participating in an Availability Group in a particular order to maintain high availability for the group. When you select one or more instances participating in an Availability Group, you have the option to patch all the instances at once. If you do so, BDA will patch the instances and fail over the primary replica in the specific sequence required to maintain high availability.
Terminology
The following table details terms and definitions related to SQL Server Availability Groups to illustrate how terms have been changed in newer versions of SQL Server.
| Previous or obsolete term | New term | Definition |
|---|---|---|
| Clustered instance | SQL Server AlwaysOn Failover Cluster Instance | A Failover Cluster Instances (FCI) is a single instance of SQL Server that is installed across Windows Server Failover Clustering (WSFC) nodes and, possibly, across multiple subnets. On the network, an FCI appears to be an instance of SQL Server running on a single computer, but the FCI provides failover from one WSFC node to another if the current node becomes unavailable. An FCI will have an IP address resource, a network name resource, and one or more application resources in a WSFC group. |
| N/A | AlwaysOn Availability Group | Supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An Availability Group supports a set of read-write primary databases and one to four sets (in SQL2012) or one to eight sets (in SQL2014) of corresponding secondary databases. |
| Microsoft Clustering Services (MSCS) cluster (Windows Server 2000 and 2003) | Windows Server Failover Clustering (WSFC) cluster | A group of independent servers that work together to increase the availability of applications and services. |
| MSCS Node | WSFC Node | A Microsoft Windows Server system that is an active or inactive member of a server cluster. |
| MSCS Cluster Resource | WSFC Cluster Resource | A physical or logical entity that can be owned by a node, brought online and taken offline, moved between nodes, and managed as a cluster object. A cluster resource can be owned by only a single node at any point in time. |
| MSCS Resource Group | WSFC Resource Group | A collection of cluster resources managed as a single cluster object. Typically a resource group contains all of the cluster resources that are required to run a specific application or service. Failover and failback always act on resource groups. |
| failover | failover | Process of moving a group to another node in response to a node or resource failure. |
| high availability | high availability | Describes a resource that remains operational and usable by clients most of the time with a very low percentage of failures that interrupt service. |
Before you get started
This example assumes that you have performed all of the steps outlined in the following table:
| Task | Description |
|---|---|
| Plan your deployment. | Planning all aspects of your deployment, including system requirements and security, ensures a successful implementation. When planning access control, note that SQL Server Availability Groups capabilities have been mapped to existing SQL Server capabilities, and no new ones are required. |
| Download the installation files, including the Additional Utilities folder. | The installation program for the latest service pack or patch also includes the full installation of the product. To install the product for the first time or apply product updates, in EPD, select the Additional Products tab and download the latest service pack or patch. You can find the latest information about service packs and patches under Release notes. |
| Carefully review the system requirements for your platform and other tasks necessary for preparing for installation. | You must perform these tasks before you launch the installation program. |
| Install BDA. | If you are performing a first-time installation, see Performing the installation. If you are installing a service pack or patches, see Applying maintenance, patches, or fixes. If you are performing an upgrade, see Upgrading a Standalone Manager for standalone configurations or Upgrading a Multi-Manager configuration for megamesh configurations. |
| Install SQL Server media on the Manager using mediaprep. | Using the tool provided with the Additional Utilities downloaded from EPD enables the BDA Manager to automatically download the media to all servers as required when performing the creation of SQL Server instances. See Setting up SQL Server media. |
How to discover and approve SQL Server Availability Groups
For this walkthrough, you log on as a BDA Administrator. Domain users that are running the instances have login permissions to the databases.
| Procedure | Example screen | |
|---|---|---|
| 1 | Discover the SQL Server Availability Group.
|
|
| 2 | Approve the SQL Server Availability Group. SQL Server Instances, Databases, and Availability Groups discovered by BMC Database Automation appear in the Pending Approval section of the Management Console.
|
|
| 3 | Verify the configuration of the Availability Group, including its databases, replicas, and listeners.
|
|
Where to go from here
To create an Availability Group, see Creating a SQL Server Availability Group.
Comments
Log in or register to comment.