Configuring SQL Server AlwaysOn

Data processing is the core of most enterprise application, in the production environment, the database always plays a very important role. In Microsoft's SQL Server, primarily through four traditional high availability and disaster recovery technologies: Failover Clustering (Cluster), log shipping (log Shipping), Replication (Replication), and database mirroring Mirroring) to ensure continuity of business. In SQL Server 2012, Microsoft has added a new high-availability technology to ensure business continuity in a production environment-sql Server always on.

The AlwaysOn Availability Group feature is a high availability and disaster recovery solution that provides an enterprise-class solution that replaces database mirroring. The AlwaysOn Availability Groups feature is introduced in SQL Server 2012, which maximizes the availability of a set of user databases to the enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, which collectively implement failover. An availability group supports a set of read-write primary databases and a corresponding secondary database of one to four groups. Optionally, the secondary database can have read-only access and/or some backup operations.

AlwaysOn leverages the health monitoring and automatic failover characteristics of Windows failover clustering, so it must be built on top of a Windows failover cluster. However, unlike SQL Server clusters, databases in an availability group are not necessarily required to be stored on shared storage (Shareddisk), and they can also be stored on a local disk. In addition, availability groups are health detection and failover as a collection of user databases, rather than as an entire instance of SQL Server clusters.

AlwaysOn availability groups provide a rich set of options to increase the availability of databases and improve resource usage.

1. Support up to five availability replicas. An availability replica is an instantiation of an availability group that is hosted by a specific instance of SQL Server that maintains a local copy of each availability database that belongs to this availability group. Each availability group supports one primary replica and a maximum of four secondary replicas.

2. Support for alternative availability modes:

1) Asynchronous commit mode. This availability mode is a disaster recovery solution that is suitable for situations where the availability replicas are distributed far away.

2) Synchronous commit mode. This availability mode emphasizes high availability and data protection in relation to performance, at the expense of increased transaction latency. A given availability group can support up to three synchronous-commit availability replicas, including the current primary replica.

3. Several forms of availability group failover are supported: Automatic failover, scheduled manual failover (usually referred to as "manual failover"), and forced manual failover (usually referred to as "forced failover").

4. Support Availability group listeners for each availability group. An availability group listener is a server name that clients can connect to to access databases in the primary or secondary replicas of an AlwaysOn availability group. An Availability group listener directs incoming connections to the primary replica or to a read-only secondary replica. A listener provides a rapid application failover after an availability group has failed over.

5. Support automatic page repair to avoid page corruption.

6. Support for flexible failover policies for better control of availability group failover.

7. Supports encryption and compression, which provides a safe and high-performance way to transfer.

8. Provides a set of integrated tools to simplify the deployment and management of availability groups, including:

1) Transact-SQL DDL statements for creating and managing availability groups.

2) SQL Server Management Studio tools:

A) The New Availability Group wizard creates and configures an availability group;

b) Add a database to an availability group to direct existing availability groups to add one or more primary databases. In some environments, the wizard can also automatically prepare the secondary database and initiate data synchronization for each database;

c) Add a replica to an availability group to add one or more secondary replicas to an existing availability group. In some environments, the wizard can also automatically prepare the secondary database and initiate data synchronization for each database;

D) The Failover Availability Group Wizard initiates a manual failover of the availability group. Depending on the configuration and status of the secondary replica that you specify as the failover target, the wizard can specify either a scheduled manual failover or a forced manual failover.

3) AlwaysOn Dashboards monitor AlwaysOn availability groups, availability replicas, and availability databases, and evaluate the results of alwayson policies.

9. The Object Explorer details pane displays basic information about an existing availability group.

10.PowerShell cmdlets.

Below is a demonstration of how to configure SQL Server

1. Log in to the SH-SQL-01 server, insert the installation media, and double-click the Setup.exe file. Installation mode selection is based on a new SQL Server standalone installation.

650) this.width=650; "height=" 222 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/30/ Wkiol1ubkfmarq6saaem8peggkm717.jpg "border=" 0 "/>

2. SQL Server Component Installation page, I choose to install all, this action is not recommended in production environment

3. Server Configuration page, click on sorting Rules, select "sql_latin1_general_cp1_ci_as"

650) this.width=650; "height=" 422 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/30/ Wkiol1ubkihrovgbaahdawkdp4o313.jpg "border=" 0 "/>

4. For installation of SQL Server, see "Installing the System Center R2 database" previously written

http://ericxuting.blog.51cto.com/8995534/1440305

5. Wait for SQL Server installation to complete

6. To facilitate testing, create two database,db01 and DB02

650) this.width=650; "height=" 462 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/30/wKioL1UBki2iqP1_ Aaifwt3__py303.jpg "border=" 0 "/>

7. Log in sh-sql-02 server repeat above, wait for SQL serve installation to complete

8. Install the Windows Server Failover Cluster (WSFC) feature in the SH-SQL-01/02 server

650) this.width=650; "height=" 395 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/30/ Wkiol1ubkjqcqns8aaffej3cnyi856.jpg "border=" 0 "/>

9. Create a new Windows Server Failover Cluster (WSFC) named Sh-sql-clu. However, this WSFC does not require a shared disk configuration.

650) this.width=650; "height=" 376 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/30/wKioL1UBkj_ A2zxzaadro1tuetw991.jpg "border=" 0 "/>

650) this.width=650; "height=" 378 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/30/ Wkiol1ubkkywkdazaaetu6qm8oi762.jpg "border=" 0 "/>

10. Click the Start button and click SQL Server Configuration Manager in the Start screen

650) this.width=650; "height=" 366 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/36/ Wkiom1ubkthb3w7laaeaals3xky482.jpg "border=" 0 "/>

11. In the right panel, right-click SQL Server (MSSQLSERVER) and select Properties

650) this.width=650; "height=" 222 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/30/ Wkiol1ubkpud09zgaaew3zgw-0w927.jpg "border=" 0 "/>

12. In the AlwaysOn High Availability tab in the SQL Server (MSSQLSERVER) Properties dialog box, check the Enable AlwaysOn Availability Group (E) option, and then click OK

650) this.width=650; "height=" 260 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/30/wKioL1UBkp2BOYD_ Aadfizpp8kq650.jpg "border=" 0 "/>

13. Click OK in the message box of the warning

650) this.width=650; "height=" 189 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/36/wKiom1UBkX-T0LI_ Aace62gjb54773.jpg "border=" 0 "/>

14. Right-click SQL Server (MSSQLSERVER) in SQL Server Configuration Manager and select restart

650) this.width=650; "height=" 178 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/30/ Wkiol1ubkrucqquqaaebixghkle826.jpg "border=" 0 "/>

15. Log in to sh-sql-02 server to repeat the above operation

650) this.width=650; "height=" 394 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/30/ Wkiol1ubkshwljp5aafpm5zhj98383.jpg "border=" 0 "/>

16. Return to the SH-SQL-01 server using SQL Server Management Studio to log in to SQL

17. In the Object Explorer panel in SQL Server Management Studio, expand the AlwaysOn High Availability node

650) this.width=650; "height=" 216 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/30/ Wkiol1ubktvdvdctaaet29r9-ga114.jpg "border=" 0 "/>

18. Right-click Availability Group, select New Availability Wizard

650) this.width=650; "height=" 265 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/30/ Wkiol1ubkvtwamjcaafjrffiovc236.jpg "border=" 0 "/>

19. New Availability Group Wizard page, read the relevant information and click Next

650) this.width=650; "height=" 504 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/30/ Wkiol1ubkxdcc51gaagc61xak8e204.jpg "border=" 0 "/>

20. Specify the Availability Group name page, type the availability group name "Sh-sql-ag"

650) this.width=650; "height=" 275 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/30/ Wkiol1ubkxvrtsv9aac1apw4wby757.jpg "border=" 0 "/>

21. Select the Database page and select the user database for the availability group

650) this.width=650; "height=" 272 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/30/ Wkiol1ubkzdyvqfvaad5atycvuq594.jpg "border=" 0 "/>

22. To configure an AlwaysOn database, note the following: User database, read/write database, multi-user database, non-auto_close, full backup mode, full database backup, in SQL Server instance, In this instance you will create an availability group and you can access it, do not belong to another availability group, and you have not configured database mirroring.

As prompted, you need to back up the database at this

650) this.width=650; "height=" 494 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/36/wKiom1UBkjSQX_ Ixaagakzr6wei333.jpg "border=" 0 "/>

23. Select DB01, DB02, and click Next

650) this.width=650; "height=" 272 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/30/ Wkiol1ubk2qjuidraaebg5rofio763.jpg "border=" 0 "/>

24. Specify the Copy page and click Add Copy

650) this.width=650; "height=" 504 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/30/ Wkiol1ubk3usvdgtaagvcaxpdu8394.jpg "border=" 0 "/>

25. Connect to the Server page and select Connect to sh-sql-02 server

650) this.width=650; "height=" 367 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/30/ Wkiol1ubk4cgini8aaey07exgng316.jpg "border=" 0 "/>

26. Specify the primary copy of SQL, the number of automatic failover, the number of simultaneous commits, and whether it is a readable secondary replica as required

650) this.width=650; "height=" 504 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/36/ Wkiom1ubknkgvdvfaagqk3hg1nq198.jpg "border=" 0 "/>

27. Select the initial Data sync page and select Connect only

650) this.width=650; "height=" 331 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/36/ Wkiom1ubko3adpfjaafxtxa6kme426.jpg "border=" 0 "/>

28. Verify that all validation passes through the page. The warning here is to detect if an AlwaysOn listener is configured. Click Next

650) this.width=650; "height=" 337 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/36/ Wkiom1ubkqgwkz7jaagaety_ajy913.jpg "border=" 0 "/>

29. Summary page, click Finish to confirm the error

650) this.width=650; "height=" 506 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/30/ Wkiol1ubk9bimjhbaagmwixcidi033.jpg "border=" 0 "/>

30. Wait for the configuration to complete.

650) this.width=650; "height=" 266 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/30/ Wkiol1ubk-tqhqtaaacz9kn8onw659.jpg "border=" 0 "/>

31. Expand the availability group in the Object Explorer panel, expand the Sh-sql-ag availability Group, and view the configuration information for the availability group.

650) this.width=650; "height=" 386 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/30/ Wkiol1ublatjuukhaah3dvmim4o657.jpg "border=" 0 "/>

32. Right-click the availability Listener and select Add Listener

650) this.width=650; "height=" 315 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/30/ Wkiol1ublamw-dzyaafuxc1y_gc315.jpg "border=" 0 "/>

33. New Availability Group Listener page, type the listener DNS name, port, and network

650) this.width=650; "height=" "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/30/ Wkiol1ublbgqkyfkaaearxctvcm090.jpg "border=" 0 "/>

34. Wait for availability Group listener creation to complete

650) this.width=650; "height=" 311 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/30/ Wkiol1ublbbhzwzvaad7klpo9nm375.jpg "border=" 0 "/>

35. Log in to the SH-SQL-02 server and open the SQL management console

36. Right-click DB01, select Restore Database

650) this.width=650; "height=" 476 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/30/ Wkiol1ublc2cmv71aajniunpw-q483.jpg "border=" 0 "/>

37. Restore the Database page, specify the backup file path

650) this.width=650; "height=" 348 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/36/ Wkiom1ubkx6toaezaae9ruxxurs855.jpg "border=" 0 "/>

38. You are prompted to restore the database at this time

650) this.width=650; "height=" "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/36/ Wkiom1ubk0bsewlvaag1v9fh-wo236.jpg "border=" 0 "/>

39. On the Availability Databases page, right-click database Select Connect to availability Group

650) this.width=650; "height=" 204 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/36/ Wkiom1ubk03wnxdcaac1wmwzrvy683.jpg "border=" 0 "/>

40. Create a new database engine connection

650) this.width=650; "height=" 181 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/36/ Wkiom1ubk1oqdbaaaacrtilwsqe304.jpg "border=" 0 "/>

41. Connect to the Server page, type Sh-sql-ag1

650) this.width=650; "height=" 371 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/36/ Wkiom1ubk1iqznufaaet2_cinby741.jpg "border=" 0 "/>

42. Expand the database to view the database synchronization status

650) this.width=650; "height=" 236 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/36/ Wkiom1ubk2chc3oxaadiece9h4m127.jpg "border=" 0 "/>

43. Right-click on the Sh-sql-ag and click on the display panel to view AlwaysOn status

650) this.width=650; "height=" 252 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M02/5B/36/ Wkiom1ubk3gbiv4paaeossi7fv0937.jpg "border=" 0 "/>

Here I choose to use manual failover, in the production environment is recommended to configure for automatic applause transfer

44. Right-click Sh-sql-ag, select Failover, start the SQL Failover Wizard

650) this.width=650; "height=" 323 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M00/5B/30/ Wkiol1ubljacs8dwaaflvx9qyzu840.jpg "border=" 0 "/>

650) this.width=650; "height=" 498 "title=" image "style=" margin:0px;border:0px;padding-top:0px;padding-right:0px; Padding-left:0px;background-image:none; "alt=" image "src=" http://s3.51cto.com/wyfs02/M01/5B/30/wKioL1UBlKvD_ Fmsaaffmae8yps015.jpg "border=" 0 "/>

This article is from "Xu Ting Blog-Microsoft technology Sharing" blog, please be sure to keep this source http://ericxuting.blog.51cto.com/8995534/1619897

Configuring SQL Server AlwaysOn