SQL Server AlwaysOn Deployment

A About AlwaysOn

AlwaysOn availability groups are new features that start in SQL Server 2012, ensuring the availability of application data and achieving zero data loss. AlwaysOn Availability Group technology incorporates the benefits of database clustering and database mirroring, and one of the great benefits of this technology is the provision of unshared storage, which avoids the failure of the entire availability scheme due to a single point of failure in storage.

AlwaysOn availability groups are based on the database (group) level, where a set of user databases (which can be one or more) is zoned into a group. Each set of availability databases is hosted by an availability replica. An availability replica consists of one primary replica and one to four secondary replicas. The primary replica is used to host the primary database, and the secondary replica hosts a set of secondary databases and acts as a potential failover target for the availability group. The primary replica enables the primary database to be used for client-side read-write connections and to implement changes to the database. Synchronize at the same time at the database level. The primary replica sends transaction log records for each primary database to each secondary database. Each secondary replica caches the transaction log records, and then restores them to the appropriate secondary database. The primary database synchronizes data independently with each connected secondary database. Therefore, one secondary database can hang or fail without affecting other secondary databases, and one primary database can hang or fail without affecting other primary databases.

Deploying an AlwaysOn Availability group requires a Windows Server failover cluster (WSFC) cluster. Each availability replica for a given availability group must be on a different node in the same WSFC cluster. When you deploy an AlwaysOn availability group, a WSFC resource group is created for each availability group. The WSFC cluster monitors this resource group to determine the state between nodes in order to evaluate the health of the primary replica. When a failure occurs, the quorum for AlwaysOn availability groups is based on all nodes in the WSFC cluster, regardless of whether a given cluster node hosts any availability replicas.

A user can provide a client connection to the primary replica of a given availability group by creating an availability Group listener. The Availability group listener uses DNS names to connect resources for a given availability group so that client connections are directed to the appropriate availability replicas.

AlwaysOn availability groups allow a set of databases to be synchronized to a maximum of 4 read replicas, which is a new feature introduced by SQL Server 2012. SQL Server 2014 increases the number of read replicas to 8

650) this.width=650; "alt=" Wkiom1oj3-_wylypaabep6xbuws496.gif "src=" http://s3.51cto.com/wyfs02/M01/8B/6E/ Wkiol1howedbxw9iaac0bvjulr0506.png "height=" 221 "/>

Two Deployment Environment Readiness 1. Deployment environment

Computer name	IP Address	Operating system	Role	Note
DC01.contoso.com	10.0.0.2	Win2012r2	DC, DNS server	Provide the Quorum folder
SQL01.contoso.com	10.0.0.3,20.0.0.2	Win2012r2	SQL Server 2014,WSFC cluster nodes	AlwaysOn Availability Group Name SG01
SQL02.contoso.com	10.0.0.4,20.0.0.3	Win2012r2	SQL Server 2014,WSFC cluster nodes
SQLCluster	10.0.0.5			Failover Cluster Name
Sql	10.0.0.10			Listener Name

2. Creating a Failover Cluster

Configuring LAN and Heartbeat networks for SQL01 and SQL02

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/8B/72/wKiom1hOWeDgsx-UAACU7Ad4pG8161.jpg "height=" 522 " alt= "Wkiom1howedgsx-uaacu7ad4pg8161.jpg"/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/8B/72/wKiom1hOWeHSTQCEAAByCM3niqs599.png "height=" 579 " alt= "Wkiom1howehstqceaabycm3niqs599.png"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M00/8B/6F/ Wkiol1howeggn_gyaabznqgq05s192.png "height=" 577 "alt=" Wkiol1howeggn_gyaabznqgq05s192.png "/>650" this.width=650 ; "Src=" Http://s3.51cto.com/wyfs02/M01/8B/72/wKiom1hOWeGwX-AaAABwoLplSkk279.png "height=" 585 "alt=" Wkiom1howegwx-aaaabwolplskk279.png "/>

Add roles and functionality wizards in SQL01 and SQL02, select failover clustering

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/8B/6F/wKioL1hOWeKySDCVAABkjOm3B5M526.jpg "height=" 393 " alt= "Wkiol1howekysdcvaabkjom3b5m526.jpg"/>

Create a failover cluster name computer SQLCluster

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/8B/72/wKiom1hOWeLAXmECAAB7NNyIEvM713.png "height=" 489 " alt= "Wkiom1howelaxmecaab7nnyievm713.png"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M02/8B/6F/ Wkiol1howeodmf8caablb7te9am737.png "height=" 282 "alt=" Wkiol1howeodmf8caablb7te9am737.png "/>

Add cluster nodes SQL01 and SQL0 to security properties and give full access

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/8B/6F/wKioL1hOWeTzjoETAACd6OH8Hgc967.png "height=" 556 " alt= "Wkiol1howetzjoetaacd6oh8hgc967.png"/>

After creation, disable account

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/8B/72/wKiom1hOWeSC57SVAABH4bOxhsA115.jpg "height=" 222 " alt= "Wkiom1howesc57svaabh4boxhsa115.jpg"/>

Open Failover Cluster Manager, create a cluster

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/8B/72/wKiom1hOWeeTgJ-1AABbFsQ-AzM527.jpg "height=" 306 " alt= "Wkiom1howeetgj-1aabbfsq-azm527.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M00/8B/6F/ Wkiol1howejd-7l_aacayufmwog620.jpg "height=" 373 "alt=" wkiol1howejd-7l_aacayufmwog620.jpg "/>

To add a member server node

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/8B/72/wKiom1hOWeiium2GAABO-SWBTn4410.jpg "height=" 373 " alt= "Wkiom1howeiium2gaabo-swbtn4410.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M01/8B/6F/ Wkiol1howejc1uygaabpptikaoa934.jpg "height=" 378 "alt=" wkiol1howejc1uygaabpptikaoa934.jpg "/>650" this.width=650 ; "Src=" http://s3.51cto.com/wyfs02/M02/8B/72/wKiom1hOWenhiJ2gAABZsFc6axQ080.jpg "height=" 373 "alt=" Wkiom1howenhij2gaabzsfc6axq080.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M02/8B/6F/ Wkiol1howenavzwsaabaoee9a58367.jpg "height=" 370 "alt=" Wkiol1howenavzwsaabaoee9a58367.jpg "/>

Create a cluster computer account before entering SQLCluster

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/8B/72/wKiom1hOWemgonnfAABawCJvO3o520.jpg "height=" 376 " alt= "Wkiom1howemgonnfaabawcjvo3o520.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M00/8B/6F/ Wkiol1howeqb2el8aabol5yatfc418.jpg "height=" 368 "alt=" wkiol1howeqb2el8aabol5yatfc418.jpg "/>650" this.width=650 ; "Src=" http://s3.51cto.com/wyfs02/M01/8B/72/wKiom1hOWerBqZ2KAAA6DDN-FnI444.jpg "height=" 373 "alt=" Wkiom1howerbqz2kaaa6ddn-fni444.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M01/8B/6F/ Wkiol1howeqi7ewpaabpzmverx4226.jpg "height=" 373 "alt=" wkiol1howeqi7ewpaabpzmverx4226.jpg "/>650" this.width=650 ; "Src=" http://s3.51cto.com/wyfs02/M02/8B/72/wKiom1hOWeujUmXCAAA_rmJ72ug077.jpg "height=" 310 "alt=" Wkiom1howeujumxcaaa_rmj72ug077.jpg "/>

When the cluster is successfully created, the computer account and the DNS record are automatically enabled

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/8B/6F/wKioL1hOWevSZRgRAACjOtTgvg4005.png "height=" 207 " alt= "Wkiol1howevszrgraacjottgvg4005.png"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M02/8B/6F/ Wkiol1howeucqgrfaaal2-3enxm539.png "height=" "alt=" Wkiol1howeucqgrfaaal2-3enxm539.png "/>

Create a quorum witness folder in domain control

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/8B/72/wKiom1hOWeyAFzEDAABWX0YFjno984.png "height=" 435 " alt= "Wkiom1howeyafzedaabwx0yfjno984.png"/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/8B/72/wKiom1hOWezCgweBAABwen-MrzQ339.png "height=" 584 " alt= "Wkiom1howezcgwebaabwen-mrzq339.png"/>

Configure cluster quorum

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/8B/6F/wKioL1hOWe2RnEtCAACsBA0DtXI587.png "height=" 453 " alt= "Wkiol1howe2rnetcaacsba0dtxi587.png"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M00/8B/6F/ Wkiol1howe3djef-aabslzztxua822.jpg "height=" 377 "alt=" wkiol1howe3djef-aabslzztxua822.jpg "/>650" this.width=650 ; "Src=" http://s3.51cto.com/wyfs02/M01/8B/72/wKiom1hOWe6CV22kAABU2IrQkws287.jpg "height=" 372 "alt=" Wkiom1howe6cv22kaabu2irqkws287.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M01/8B/6F/ Wkiol1howe6qnas7aabtrdwwftg778.jpg "height=" 374 "alt=" wkiol1howe6qnas7aabtrdwwftg778.jpg "/>650" this.width=650 ; "Src=" http://s3.51cto.com/wyfs02/M02/8B/72/wKiom1hOWe7S3UgPAABToq0GTaQ515.jpg "height=" 372 "alt=" Wkiom1howe7s3ugpaabtoq0gtaq515.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M02/8B/72/ Wkiom1howe_i-iccaabpknqit34524.jpg "height=" 380 "alt=" wkiom1howe_i-iccaabpknqit34524.jpg "/>650" this.width=650 ; "Src=" http://s3.51cto.com/wyfs02/m02/8b/6f/wkiol1howe_r-nr4aabv4kwcsii682.jpg "height=" 378 "alt=" Wkiol1howe_r-nr4aabv4kwcsii682.jpg "/> 650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/8B/72/wKiom1hOWe-Bb2ljAABSHg9v-eA507.jpg "height=" 371 " alt= "Wkiom1howe-bb2ljaabshg9v-ea507.jpg"/>

Three Installing SQL Server

Installing local standalone SQL on SQL01 and SQL02

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/8B/6F/wKioL1hOWfDA14DwAABTe4QVUDo840.jpg "height=" 258 " alt= "Wkiol1howfda14dwaabte4qvudo840.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M01/8B/72/ Wkiom1howflrjwdiaacsi058oda133.jpg "height=" 415 "alt=" wkiom1howflrjwdiaacsi058oda133.jpg "/>650" this.width=650 ; "Src=" Http://s3.51cto.com/wyfs02/M01/8B/6F/wKioL1hOWfKQoXQRAABZIO3YX2A803.jpg "height=" 394 "alt=" Wkiol1howfkqoxqraabzio3yx2a803.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M02/8B/6F/ Wkiol1howflzs8zlaabtcpnak5q824.jpg "height=" 355 "alt=" wkiol1howflzs8zlaabtcpnak5q824.jpg "/>650" this.width=650 ; "Src=" http://s3.51cto.com/wyfs02/M00/8B/72/wKiom1hOWfOiHuABAAB39VJe93o695.jpg "height=" 388 "alt=" Wkiom1howfoihuabaab39vje93o695.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M02/8B/72/ Wkiom1howfprfrn-aabqaxhuaw0474.jpg "height=" 394 "alt=" wkiom1howfprfrn-aabqaxhuaw0474.jpg "/>650" this.width=650 ; "Src=" http://s3.51cto.com/wyfs02/m00/8b/6f/wkiol1howfszscpaaab1v1y6dw4676.jpg "height=" 393 "alt=" Wkiol1howfszscpaaab1v1y6dw4676.jpg "/>

Four Enable AlwaysOn

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/8B/6F/wKioL1hOWfSQbajeAAAvk_xpVgo772.jpg "height=" 127 " alt= "Wkiol1howfsqbajeaaavk_xpvgo772.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M01/8B/72/ Wkiom1howftybqsjaabaa0fcjga739.png "height=" 514 "alt=" Wkiom1howftybqsjaabaa0fcjga739.png "/>

New Database

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/8B/72/wKiom1hOWfWDi2gIAABPJ8J7pnc010.jpg "height=" 295 " alt= "Wkiom1howfwdi2giaabpj8j7pnc010.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M00/8B/6F/ Wkiol1howfxy32ngaablbr3dyvg775.jpg "height=" alt= "Wkiol1howfxy32ngaablbr3dyvg775.jpg"/>

Recovery mode must be full mode

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/8B/72/wKiom1hOWfaTOVouAACG0-O67so834.jpg "height=" 413 " alt= "Wkiom1howfatovouaacg0-o67so834.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M01/8B/6F/ Wkiol1howfaj_wc1aabqr1bzybe349.png "height=" 294 "alt=" Wkiol1howfaj_wc1aabqr1bzybe349.png "/>

Back up this database

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/8B/6F/wKioL1hOWfbTeJxkAAB4RIvcBDE996.jpg "height=" 426 " alt= "Wkiol1howfbtejxkaab4rivcbde996.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M02/8B/72/ Wkiom1howfeibw3laabosrrjb-u811.jpg "height=" 323 "alt=" wkiom1howfeibw3laabosrrjb-u811.jpg "/>

Enable folder sharing for backup

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/8B/72/wKiom1hOWffijmHaAABfacSPdVY600.jpg "height=" 354 " alt= "Wkiom1howffijmhaaabfacspdvy600.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M02/8B/6F/ Wkiol1howfegg8faaab8x6tkvgs593.png "height=" 448 "alt=" Wkiol1howfegg8faaab8x6tkvgs593.png "/>

To open the New Availability Group wizard

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/8B/72/wKiom1hOWfvScMUJAACRCQI5lzo781.png "height=" 428 " alt= "Wkiom1howfvscmujaacrcqi5lzo781.png"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M00/8B/72/ Wkiom1howfuc-dviaabgvkethfa618.jpg "height=" alt= "Wkiom1howfuc-dviaabgvkethfa618.jpg"/>

Enter the name of the availability group

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/8B/6F/wKioL1hOWfvDUbWbAAA7xsjVLSY970.jpg "height=" 441 " alt= "Wkiol1howfvdubwbaaa7xsjvlsy970.jpg"/>

Select the database you want to add to the availability group

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/8B/72/wKiom1hOWfzxMGIFAABDGvoCnws784.jpg "height=" 406 " alt= "Wkiom1howfzxmgifaabdgvocnws784.jpg"/>

Add replica, select node Two SQL02

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/8B/6F/wKioL1hOWfyRIr_xAABeK8JyG8U489.jpg "height=" 341 " alt= "Wkiol1howfyrir_xaabek8jyg8u489.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M01/8B/6F/ Wkiol1howf2rz2wdaabpmavo154751.jpg "height=" 365 "alt=" wkiol1howf2rz2wdaabpmavo154751.jpg "/>650" this.width=650 ; "Src=" http://s3.51cto.com/wyfs02/M02/8B/72/wKiom1hOWf3Ce09oAAByI_2jxoo489.jpg "height=" 402 "alt=" Wkiom1howf3ce09oaabyi_2jxoo489.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M02/8B/6F/ Wkiol1howf2cdzhsaabzzh0v1vs148.jpg "height=" 337 "alt=" wkiol1howf2cdzhsaabzzh0v1vs148.jpg "/>650" this.width=650 ; "Src=" Http://s3.51cto.com/wyfs02/M00/8B/72/wKiom1hOWgHQKhssAAB0URzt-dg885.jpg "height=" 429 "alt=" Wkiom1howghqkhssaab0urzt-dg885.jpg "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M00/8B/6F/ Wkiol1howggashqraaaxmuvswjm090.jpg "height=" 364 "alt=" wkiol1howggashqraaaxmuvswjm090.jpg "/>650" this.width=650 ; "Src=" http://s3.51cto.com/wyfs02/m01/8b/72/wkiom1howglbo9g1aacdlf1siam764.jpg "height=" 445 "alt=" wkiom1howglbo9g1aacdlf1siam764.jpg "/> 650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/8B/6F/wKioL1hOWgOSiRmaAAB2D7b9a6g667.png "height=" 432 " alt= "Wkiol1howgosirmaaab2d7b9a6g667.png"/>

Five Creating listeners

Enter the name and IP of the listener, and subsequent access to SQL will be managed through this FQDN and IP address

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/8B/72/wKiom1hOWgTTMuXFAABSgfu7EAs687.jpg "height=" 326 " alt= "Wkiom1howgttmuxfaabsgfu7eas687.jpg"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M02/8B/72/ Wkiom1howgtbtre3aabn_0-ryc0515.jpg "height=" 464 "alt=" wkiom1howgtbtre3aabn_0-ryc0515.jpg "/>650" this.width=650 ; "Src=" Http://s3.51cto.com/wyfs02/M02/8B/6F/wKioL1hOWgThOu9MAAA0P7b9K-w241.png "height=" 201 "alt=" Wkiol1howgthou9maaa0p7b9k-w241.png "/>650) this.width=650;" Src= "http://s3.51cto.com/wyfs02/M00/8B/72/ Wkiom1howgxruxkfaaarwzx63iw874.jpg "height=" 113 "alt=" wkiom1howgxruxkfaaarwzx63iw874.jpg "/>

Six Test the cluster

The current SQL Run node is SQL01

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/8B/6F/wKioL1hOWgWwheaEAABH__Tenfc363.jpg "height=" 371 " alt= "Wkiol1howgwwheaeaabh__tenfc363.jpg"/>

Turn off SQL01 LAN Nic for testing

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/8B/6F/wKioL1hOWgWQpYzeAABW7hzcvZQ617.png "height=" 213 " alt= "Wkiol1howgwqpyzeaabw7hzcvzq617.png"/>

When the NIC is disabled, SQL automatically switches to the SQL02 and becomes the active node

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/8B/72/wKiom1hOWgazcnmWAABGn2yLBwQ325.jpg "height=" 371 " alt= "Wkiom1howgazcnmwaabgn2ylbwq325.jpg"/>

At this point we are accessing SQL, we will find the original SQL02 secondary node, and now it is the primary node

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/8B/72/wKiom1hOWgai_c-qAABtvAvWwW0590.png "height=" 370 " alt= "Wkiom1howgai_c-qaabtvavwww0590.png"/>650) this.width=650; src= http://s3.51cto.com/wyfs02/M01/8B/6F/ Wkiol1howgbx6q27aadq4qhxsek681.png "height=" 488 "alt=" Wkiol1howgbx6q27aadq4qhxsek681.png "/>

This article is from the "hanson-Microsoft Technology Blog" blog, please be sure to keep this source http://cloud365.blog.51cto.com/6246780/1881981

SQL Server AlwaysOn Deployment