WSFC implementation of the SQL Server 2012 High Availability Group (AlwaysOn Group) _win server based on Win2008 R2

Two years ago, SQL Server 2008 R2 Database Mirroring deployment, today, "Renew the front"--

Microsoft's new generation of database products SQL Server 2012 has been available for some time, whether functional or performance, compared to its early products have a great improvement. In particular, it introduces the concept and functionality of the High Availability Group (AlwaysOn Group, AG), which greatly enhances and enhances the availability of SQL Server, with qualitative changes based on the previous mirror database.

The SQL Server 2012 High-availability Group does not rely on a specially provided shared storage disk array in the implementation process, and each node stores a copy of the database independently of the earlier SQL Server failover cluster. Compared to the early-morning mirror database, provides multiple-node high availability and is readable for database-assisted node replicas and, in the case of a failure of the current readable node, ensures normal access to the database through the mechanism of the AG itself, without needing to switch through the access side like the previous mirror database.

Therefore, the implementation of the SQL Server 2012 High-availability Group is described in detail below.

One, the specific environment is as follows

In Windows domains, 3 member servers are deployed as SQL Server servers because the High availability group for SQL Server 2012 requires Windows Cluster Service support, so WSFC must be implemented on these member servers in advance. The server's operating system is Windows Server 2008 R2, so its WSFC quorum configuration has a majority (odd) node, a node (even) and a majority of disks, most nodes (even) and file shares, non-majority: 4 disks only. Here, in order to reflect the need for SQL Server AG to share storage devices with each node, it is ready to be implemented using 3 nodes. Because it is odd, the majority node quorum configuration is selected. Although this quorum configuration is not recommended by SQL Server AG, it does not affect the implementation and use of the AG.

In addition, because this article focuses on the implementation of SQL Server AG, the server base configuration and network settings, and the implementation of Windows Active Directory and domain are omitted.

Second, realize SQL Server The High Availability group must be WSFC Environment

First install and configure WSFC on each member server to configure SQL03 as an example:

1 , installation WSFC Cluster Components

1 Open Server Manager, select "Function" and click "Add function" in the right window.

2 in the Add Feature Wizard, check "Failover Clustering" and click "Next".

3 in the "Confirm Installation Selection" page, click "Install" to install the operation.

4 completes and closes the installation of the failover cluster.

2 , configure WSFC

After you have installed the Failover Cluster service for all nodes, expand failover Cluster Administrator in the Server Manager of any node server to configure WSFC.

1 through the Failover Cluster Manager window, click "Create a Cluster" to open the Create Cluster wizard

2 before the Start page, click Next.

3 Add all server nodes and add 3 member servers in the environment as cluster node servers. Click "Next".

4 in the "Validation Warning" page, you can choose "Yes" if you need to perform WSFC requirements validation on the basic environment (including hardware). In this example, select No because validation is not required. Click "Next".

5 Define access points for managing clusters, name the cluster, and specify the cluster IP address. Click "Next".

6 Confirm before the configuration information, if correct, click "Next", start the cluster creation.

7 Complete the cluster creation, click "Finish" to close the wizard.

8 in Failover Cluster Administrator displays the cluster that was successfully created, expands its node, and displays the existing node condition.

9) Right click on the cluster node, click "More Actions" in the context menu, and select "Configure cluster quorum setting" in the extended menu to configure the quorum for the cluster.

10 click "Next" on the "Before You start" page.

11 According to the previous design, because it is odd nodes, and there is no shared disk storage and shared folder exists, so you can select "Majority", click "Next".

please refer to the details: http://technet.microsoft.com/zh-cn/library/cc731739.aspx

12 in the "Confirmation" page, click "Next".

13) Complete the WSFC arbitration configuration.

At this point, the WSFC environment for SQL Server AG is complete.

install and configure for each node server SQL Server , in order to SQL03 For example, do the following.

1 , install SQL Server

1 Insert SQL Server 2012 installation media on the node server. Run SQL Server Installation Center, select Install, and click on the new SQL Server standalone installation or add functionality to an existing installation.

Note: Do not choose here " of the new SQL Server Failover cluster Installation " , if it is a failover cluster installation, it will eventually be achieved by getting up early SQL database cluster, and you need to share the data storage magnetic consolidation column, causing all SQL The node shares the same data store copy.

2 Use the installer to support the rule to verify that it is passed.

3 Specify the Product key, or select the evaluation version.

4) Acceptance of the license agreement

5 Run Check Setup support rules to verify that the SQL Server 2012 running environment meets the requirements.

As in previous versions, you need to open the Windows Firewall inbound rules for the necessary ports on SQL Server, such as the TCP-1433 port.

Special Note: as AG the default needs to be used TCP-5022 Port, so the port must be guaranteed Windows Open in the firewall to allow inbound access.

6 Set the SQL Server 2012 server role to "all features with default values" and click "Next".

7 Select the actual function you want in the feature selection list, where you can no longer customize it for simplicity. Use the default selected component directly. Also, you need to specify the appropriate shared feature directory, which is the installation location for SQL Server.

8 again check whether the installation rules can be carried out normally.

9 Select and specify the instance name, where the default instance is used to demonstrate, and the instance root directory is specified.

10 Check disk space requirements to meet the installation requirements of SQL Server 2012, click "Next".

11 Specify "Service Account" and "Collation", where default settings are selected. If you need to customize the configuration after completing the installation, click Next.

12 in the Database Engine configuration page, specify server configuration authentication as the default Windows Authentication mode, add current user as SQL Server administrator, and a custom configuration to respond to. You can also specify the default database file storage path for the instance in the Data Directory tab. Click "Next".

13) On the Analysis Services Configuration page and similar configurations on the Database Engine configuration page. Click "Next".

14 on the Reporting Services Configuration page, select the default setting and click Next.

15 "Add current User" as the administrator of the distributed replay controller. Click "Next".

16 Specify the appropriate controller and database directory for the distributed replay client. Here, with the default configuration, the controller name is not filled in. Click "Next".

Note: The above section 13-16 Step by paragraph 7 There is a difference in configuration between the selected features in the step.

17 whether to send error reports to Microsoft in the Error Reporting page.

18 perform the last "Install configuration rule" Check to confirm compliance with installation requirements prior to installation.

19 in the "Ready to Install" page, verify that the various parameters before the configuration is correct, if correct, click "Install" to start the installation process.

20 after the installation is completed, the page is successfully completed. If all of the features on this page are green, then it proves that SQL Server 2012 is installed correctly.

21 may be installed in some environments, you will receive the following prompts, just reboot the server operating system.

2 , configuring SQL Server for the High availability group Database Engine service.

1 Click "All Programs"-"Microsoft SQL Server 2012"-"Configuration Tools" on the Start menu to open SQL Server Configuration Manager.

2 Click the SQL Server service node in the console and right-click SQL Server (MSSQLSERVER) in the right window and select Properties from the context menu.

3 Select "Enable AlwaysOn availability Group" in the pop-up window by selecting the "AlwaysOn High Availability" tab. Click "Confirm".

4 in the Pop-up Warning dialog box prompts the changes need to restart the SQL Server Database engine service to take effect, click OK.

5 Again, right click SQL Server (MSSQLServer) and select "Reboot" in the context menu to restart the database engine.

6 to avoid complex permission settings when implementing the AlwaysOn group, it is recommended that the associated SQL service (such as SQL Server Database Engine service) Start the logon identity to the specified domain account by default "built-in account".

7 It is recommended that you grant each node SQL Server logon right for this domain account and assign each node server account as a SQL Server login account.

The above steps are performed in the same operation on the sql01/02.

Create a configuration High availability group

The High Availability group can be created in three ways: wizards, dialogs, and scripting, as an example of the DB01 created on the wizard SQL03 as the primary database.

1 , creating a configuration database for the High availability group

To create a high Availability group using the wizard, you must have a qualified database exists to perform the related operations. So what kind of conditions does the database have to meet?

1 The recovery mode of the primary database must be the "full" recovery model.

2 The primary database has been fully backed up.

3 requires a shared path to be provided to the backup package storage and allows all node servers (SQL Server Database engine service login account) to be able to read and write access through UNC traffic.

2 , use the wizard to create a new high availability group

1 Click "All Programs"-"Microsoft SQL Server 2012"-"Configuration Tools" on the Start menu to open SQL Server Management Studio.

2 Expand the AlwaysOn High Availability node, right-click Availability Group, and in the context menu, select the New Availability Group wizard.

3 Click "Next" On the "Introduction" page.

4 Specify a name for the high availability group that will be created.

5 Select the database that meets the high Availability group criteria and click Next.

6 add additional SQL Server server nodes as replicas for the high availability group. Click the "Add Copy" button to add the required SQL Server server one at a time, and specify the initial role to be "primary" or "secondary". You can choose up to 2 replicas for automatic failover, both of which will use synchronous commit availability mode. Up to 3 copies can be supported for synchronous commit availability mode, and manual failover is mandatory if you use asynchronous commits. For readable auxiliary copy settings, you can choose whether it is readable or read-only intent, and note that only the primary database copy is writable.

please refer to the details: http://msdn.microsoft.com/zh-cn/library/ff877931.aspx

7 Click on the "Endpoints" tab to set the endpoint URL of the high availability group. Use the default settings.

please refer to the details: http://msdn.microsoft.com/zh-cn/library/ff878441.aspx

8 in Backup preferences, specify the node that is the backup copy. The default is to make a backup in the preferred secondary copy. You can specify preference by replica backup priority. All nodes are 50 by default.

9 Create listeners for high availability groups, specify their DNS name host name and port, recommend IP addresses to use static IP, and add new cluster IP as a high-availability group, so you can specify the default port 1443 for SQL Server to simplify the configuration of client access.

10 Initialize the data synchronization preferences, we recommend that you select "Full" and specify the UNC shared path that you created previously. Click "Next"

11 Verify that the high Availability group creation requirements are met, and click Next if the results are all green.

12 check and confirm the previous configuration information, if correct, click "Finish". In addition, you can save the build group script for step-by-step troubleshooting.

13 Wait for the wizard to finish creating the SQL Server 2012 high-availability Group.

14 know the final completion, all the summaries are successfully completed, display green check. If a yellow warning appears, further judgment is required to see if it is successful. If a red error occurs, the creation of the AG is unsuccessful.

Here, a yellow alarm appears, click "Warning" in the pop-up dialog box to display the following:

You can indicate that the high availability group was created successfully, but the WSFC "majority" quorum mode is not recommended.

15) Return to the SSMs window and display the "AG01" panel under the "Availability groups" node to see the status of the High-availability group.

3 , the basic management of high availability groups

After you create a high availability group, you can use SSMS to perform basic management of the High Availability group.

1 Right-click the high availability group you want to manage, and in the context menu you can choose to add New database, add a copy, add listeners, and manually perform a failover operation. You can also edit various property values for the AG by clicking Properties.

2 on the Properties General tab of the High Availability group, you can add/remove databases, replica nodes, and configure parameter settings for each available replica node.

3 in the Backup Preferences tab, you can modify the backup priority of the corresponding replica node and the location of the backup execution.

4 for the specific replica node configuration in the High Availability group, you can expand the availability copy in the group, right-click the replica node that you want to configure, and click Properties in the context menu.

5 In its General tab, you can modify the configuration current replica node availability mode, failover mode, and session timeout time.

6 You can configure the listener by right-clicking the specific listener in the availability group listener and opening its properties.

7 The listener's listening port can be modified to specify the service port to which the client accesses the database required by the AG.

These questions are discussed here for a while, and there is time to follow up.

This article from the "Fat Brother Technology Hall" blog