SQL Server 2012 Read and write detach settings

Microsoft's new generation of database products SQL Server 2012 has been available for some time, both in terms of functionality and performance, compared to its earlier products have been greatly improved. In particular, the introduction of the concept and functionality of high availability groups (AlwaysOn Group, AG) greatly enhances and improves the availability of SQL Server, with qualitative changes on the basis of previous mirror databases.

SQL Server 2012 High availability groups do not rely on the special provision of shared storage disk arrays in the implementation process compared to earlier SQL Server failover clusters, where each node stores a copy of the database independently. It provides multi-node high availability compared to the earlier mirror database, and is readable for database secondary node replicas, and in the event of a failure of the current readable node, it is possible to ensure proper access to the database through the mechanism of the AG itself, rather than having to switch through the access side like the previous mirror database.

As a result, the implementation of the SQL Server 2012 High Availability group is described in detail below.

First, the specific environment is as follows

In a Windows domain, deploy 3 member servers as SQL Server servers, because the High availability group for SQL Server 2012 requires Windows Cluster Service support, so WSFC must be implemented on those member servers beforehand. The server's operating system is Windows Server R2, so its WSFC quorum configuration has a majority (odd) node, node (even) and disk majority, majority node (even) and file share, non-majority: disk only, and so on 4 kinds. Here, in order to reflect the SQL Server AG does not need to share the storage device of each node feature, ready to use 3 node implementation. Because it is odd, select the majority node quorum configuration. Although this quorum configuration is not recommended by SQL Server AG, it does not affect the implementation and use of AG.

In addition, because this article focuses on the implementation of SQL Server (AG), the implementation of server basic configuration and network settings, as well as Windows Active Directory and domain, is omitted.

Second, the WSFC environment that is required to implement the SQL Server 2012 High Availability Group

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

1. Install the WSFC Cluster component 1) Open Server Manager, select "Features" and click "Add Features" in the right window.

2) In the Add Features Wizard, tick "Failover Cluster" and click "Next".

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

4) Complete and close the installation of the failover cluster.

2. Configuring WSFC

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

1) Open the Create Cluster wizard by tapping create a cluster from the Failover Cluster Manager window

2) on the Before you begin page, click Next.

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

4) on the Validation warning page, select Yes if you need to validate the WSFC requirements for the basic environment, including the hardware. In this example, select No because there is no need for validation. Click "Next".

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

6) Confirm the previous configuration information, if it is correct, click "Next" to start the cluster creation.

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

8) in Failover Cluster Manager, displays the cluster that was successfully created, expands its nodes, and displays the existing node status.

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

10) Click "Next" on the "Before You Begin" page.

11) According to the previous design, because it is an odd number of nodes, and no shared disk storage and shared folders exist, so you can select "Most nodes", click "Next".

Note:
For more information, please refer to: http://technet.microsoft.com/zh-cn/library/cc731739.aspx

12) on the "Confirmation" page, click "Next".

13) Complete the quorum configuration of the WSFC.

At this point, the WSFC environment that was prepared for SQL Server AG was completed.

Third, install and configure SQL Server 2012 for each node server, take SQL03 as an example to do the following. 1. Install SQL Server 20121) on the node server, insert the SQL Server 2012 installation media. Run the SQL Server Installation Center, select Install, and click New SQL Server standalone installation or add features to an existing installation.

Note:
Note: Do not select "New SQL Server failover cluster Installation" Here, if it is a failover cluster installation, you will end up with an earlier SQL database cluster, and you need to share a data store magnetic consolidation column, causing all SQL nodes to share the same data store copy.

2) Use the Installer support rules to verify that the pass is passed.

3) Specify the product key, or select the evaluation version.

4) Accept the license agreement

5) Run the check Setup support rules to verify that the SQL Server 2012 operating environment meets the requirements.

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

Note:
Special NOTE: Since the AG is required to use the TCP-5022 port by default, the port must be guaranteed to be open in Windows Firewall, allowing its inbound access.

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

7) in the "Feature Selection" list, select the actual function you want, where you can no longer customize it for simplicity. Use the default selected component directly. Also, you need to specify the appropriate "Shared features directory", which is where SQL Server is installed.

8) Re-check the installation rules to see if the normal execution passes.

9) Select and specify the instance name, where the default instance is used for demonstration, and specify the instance root directory.

10) Check that the disk space requirements meet the installation requirements for SQL Server 2012 and click "Next".

11) Specify "service Account" and "collation", where the default setting is selected. If you need to customize the configuration after the installation is complete, click Next.

12) on the Database Engine configuration page, specify the server configuration authentication as the default "Windows Authentication Mode", "Add current User" as the administrator of SQL Server, if there is a custom configuration that needs to be responsive. You can also specify the default database file storage path for that instance in the Data Catalog tab. Click "Next".

13) On the Analysis Services Configuration page, similar to the configuration in the Database Engine configuration page. Click "Next".

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

15) "Add Current User" as "Distributed Replay Controller" Administrator. 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:
Note: The above 第13-16 steps differ according to the features selected in the 7th step, and the configuration varies.

17) Whether or not to send error reports to Microsoft.

18) Before installing, perform the last "Installation configuration rule" Check to confirm that the installation requirements are met.

19) on the "Ready to Install" page, verify that the various parameters have been configured correctly before, and if it is correct, click "Install" to start the installation process.

20) After the installation is complete, get the completed page successfully. If all features shown in 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 prompt, just restart the server operating system.

2. Configure the SQL Server 2012 Database Engine service for the high Availability group. 1) From the Start menu, tap All Programs-Microsoft SQL Server 2012-configuration tools to open SQL Server Configuration Manager.

2) In the console, click the SQL Server Services node, right-click SQL Server (MSSQLSERVER) in the right-hand window, and select Properties from the context menu.

3) in the pop-up window, select "Enable AlwaysOn availability groups" in the "AlwaysOn High Availability" tab. Click "Confirm".

4) in the pop-up warning dialog box, prompt that the modification requires the SQL Server Database engine service to be restarted to take effect, click OK.

5) Right-click SQL Server (MSSQLSERVER) again and select Restart in the context menu to perform a restart on the database engine.

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

7) It is recommended that the domain account be granted SQL Server logon rights for each node and that each node server account be assigned as a SQL Server login account.

The above steps perform the same operation on the sql01/02.

Iv. creating a configuration high Availability group

There are three ways to create a high Availability Group: Wizard mode, dialog box mode, and scripting, as described in DB01 as the primary database created on the wizard SQL03.

1. Creating a configuration database for a high availability group

To use the wizard to create a high availability group, you must have a qualifying database exists for related operations. So what are the criteria for the database to meet the conditions?

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

2) A full backup of the primary database has been made.

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

2. Create a new high Availability group using the wizard 1) on the Start menu, tap All Programs-Microsoft SQL Server 2012-configuration tools to open SQL server Management Studio.

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

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

4) Specify a name for the high availability group that you want to create.

5) Select the database that meets the criteria for joining the High Availability group and click Next.

6) Add additional SQL Server server nodes as replicas for the high availability group. Click the "Add Replica" button to add the required SQL Server server at a time, and specify the initial role as "primary" or "secondary". Up to 2 replicas can be automatically failed over, both of which will use synchronous-commit availability mode. Up to 3 replicas can be supported for synchronous-commit availability mode, and manual failover must be enforced if asynchronous commit is used. For a readable secondary replica setting, you can choose whether it is readable or read-only, and you need to be aware that only the primary database copy is writable.

Note:
For more information, please refer to: http://msdn.microsoft.com/zh-cn/library/ff877931.aspx

7) Click on the "Endpoints" tab to set the endpoint URL for the high availability group. You can use the default settings.

Note:
For more information, please refer to: 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 replica. You can specify preference by replica backup priority. All nodes are 50 by default.

9) Create a listener for the High Availability group, specify the host name and port of its DNS name, recommend that the IP address use a static IP, and add a 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) to initialize the data synchronization preferences, it is recommended that you select full and specify the UNC share path that you created earlier. Click "Next"

11) Verify that the High Availability group creation requirements are met, and if the results are all green tick, click "Next".

12) Check and confirm the previous configuration information, if correct, click "Done". In addition, you can save a set of scripts to diagnose faults in steps.

13) Wait for the wizard to complete the creation of the SQL Server 2012 High Availability Group.

14) Knowing the final completion, all summaries are completed successfully, showing a green checkmark. If a yellow warning is present, further judgment is required for success. If a red error occurs, the creation of AG is unsuccessful.

Here, a yellow alarm appears, click "Warning", in the Pop-up dialog box appears as follows:

You can indicate that the high availability group was created successfully, but the "majority node" quorum mode of WSFC is not recommended. 15) Return to the SSMs window and display the "AG01" panel under the availability Groups node to observe the status of the High Availability group.

3. 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 a new database, add a replica, add a listener, and manually perform a failover. You can also edit the various property values of the AG by clicking Properties.

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

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

4) for a specific replica node configuration in a high availability group, you can expand availability replicas in the group, right-click the replica node you want to configure, and click Properties in the context menu.

5) In its General tab, you can modify the configuration of the current replica node "availability mode", "Failover Mode", and "Session timeout" time, and so on.

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

7) You can modify the listener's listening port to specify the service ports that the client needs to connect to the database in the AG.

At this point, the main operations for the SQL Server 2012 High Availability group based on WSFC are described. The key steps are summarized as follows, configuring WSFC first, then installing the SQL Server 2012 instance (non-clustered instance) independently on each node, enabling the High Availability Group feature in SQL Server Configuration Manager for the Database engine service after installation, and finally through the SQL Server System Manager creates a maintenance high availability group.

SQL Server 2012 Read and write detach settings