SQL AlwaysOn Build

Original: SQL AlwaysOn build

AlwaysOn underlying is still monitored and transferred using the Windows failover Clustering mechanism, so you need to establish Windows ClusterFirst, except that the databases in the availability group are not necessarily stored on shared storage. Can be stored on a local disk.

Now, let's look at the key features of AlwaysOn:

1. As with failover clustering, a virtual network name is required for a unified connection to the client.

2. A primary server can have up to four secondary servers, a total of five, and the secondary server supports read-only functionality.

3. The secondary server can perform backup and DBCC maintenance commands independently. By configuration, read-only requests from clients can be automatically directed to the secondary server.

4. Data between the primary and secondary servers is encrypted and compressed to improve security and network transfer efficiency.

5: Supports automatic, manual, and forced three failover modes.

6 A dashboard is used to monitor the operating status of AlwaysOn.

7. Multiple-site deployments can be implemented, where primary and secondary sites can span physical networks.

AlwaysOn can support up to five replicas , with two types of availability replicas: one primary replica and one to four secondary replicas. but only one availability replica runs on a database that is in a read-write state. This read-write database is referred to as the primary database (Primarydatabase), and this availability replica is called the primary replica (Primaryreplica). The rest of the replicas are called secondary replicas (Secondaryreplica), the databases on the secondary replicas may be inaccessible, or they can only accept read-only operations (depending on the configuration of the availability group), which are referred to as secondary databases. Once a failover occurs, any secondary replica can become the new primary replica instance. The primary replica continuously sends data changes on the primary database to the secondary replica to enable database synchronization between replicas.

Excerpt from: http://dufei.blog.51cto.com/382644/1384210/

Build

Reference http://www.cnblogs.com/lyhabc/p/4678330.html

Environment preparation

1. Server: Prepare 4 virtual machines. domainserver 10.58.8.98 DB1 10.58.8.99 DB2 10.58.8.102 DB3 10.58.8.103

2, operating system: windows2008 R2 SP2 or above version.

3, Database: SQL Server 2014.

Domainserver

DB1 DB2 DB3

Step 1: Establish a domain server:

Establish the domain service alwayson.com on the Domainserver server and set the DNS for DB1, DB2, DB3 to 10.58.8.98, and then add the domain alwayson.com.

Then next, the next installation is complete.

After the installation is complete, click the Domain Services Setup Wizard

Check whether the AD domain service and the Netlogon service are starting properly

Create a domain management account

Join this domain user to the domain computer group and the Domain Admins group

Joining DB1/DB2/DB3 to a domain server

Step 2:DB1 Install the failover cluster

DB1 Create cluster Management after installation is complete

If the OpenService "RemoteRegistry" failure error occurs

The workaround is as follows: 1. domain account login 2. Three machine times must be the same

If the cluster installation fails, or the node does not exit, you can refer to http://www.cnblogs.com/woxpp/p/5604488.html

Step 3: Configure cluster quorum

Domainserver Configuring Shared Folders

Step 4: Configure SQL Server 2014 account

Modify the DB1/DB2/DB3 database SQL Agent service and the SQL Engine service as a domain account

Log off the cluster node computer, and then log on by using a domain user, and then set the SQL Server startup account to be a domain user

Open Service Manager, first modify the SQL Agent startup account as a domain user, and then modify the SQL engine startup account for the domain user

If it doesn't start, see: Http://www.cnblogs.com/woxpp/p/5607908.html

Add a domain account to the SQL login user and give sysadmin permission

Add a SQL login user after logging in with SA and add the domain user as a logged-on user as the SQL service adds the start account step

Step 5: Configure SQL Server AlwaysOn

DB1/DB2/DB3 SQL Server Configuration Manager, enabling AlwaysOn availability groups

Close Db1/bd2/bd3 Firewall

View Validity

SELECT * from  sys.dm_hadr_cluster_members;

DB1 Creating a database TestDB

DB1 Creating AlwaysOn High Availability

Adding replicas

Set secondary replicas to be readable, automatic failover, synchronous-commit mode

"Backup Preferences" and "listeners" do not need to be set, leave the default on the line

Click "Yes"

Select Initial data synchronization

Click "Next" to verify the configuration, the corresponding listener configuration warning can be ignored, late to add listeners

If the following error occurs

DB1/DB2/DB3 Disable AlwaysOn and restart the service before you turn on AlwaysOn and restart the service

If this error occurs, restore the database to reproduce

look again at Failover Cluster Manager

Step 6: Add listeners

A failover cluster virtual network Name account is added to the Computers container in the ad

In the failover Cluster Manager, the role node, you can see the client Access name and IP address, the client through this access name to access the database

Sign in to SQL Server with listener name

At this point, the SQL AlwaysOn build is complete, modifying the data of the primary database, and two copies synchronizing the relevant data

Step 7: Manually fail over

The Monk of Http://www.cnblogs.com/woxpp/p/5587468.html: the Buddha of Buddhism

SQL AlwaysOn Build