Build SQL Server AlwaysOn Third (configure AlwaysOn) starting from 0

This is the third in SQL Server AlwaysOn from 0, and this article really started to build AlwaysOn, the first two is to build AlwaysOn to prepare

Steps

This article still uses step by step to introduce how to build AlwaysOn

Please log in to both cluster nodes with local user administrator first and do the following, do not log in with domain user Dcadmin first

1, two cluster nodes are required to install the. NET Framework 3.5 (Install using the Add feature in Windows Server R2) first.

2, each cluster node local to prepare the relevant software, on each node to install SQL Server 2012 independently (not using cluster installation), to ensure the use of the same installation directory structure and collation of each node!

Select a new SQL Server standalone installation, and do not select a newer SQL Server failover cluster installation

As for the installation process, the default next step is no different from the standalone installation of SQL Server, where the installation process is ignored

Note: Because my installation package has been brought SP1 patch pack , in order to avoid stepping on the pit, if not installed SP1 or above the patch pack, please install first

Note: If you start using domain user dcadmin to log on to the cluster node machine and you encounter a pit when installing SQL Server, SQL Server Setup will connect to the failover cluster, but in fact the standalone installation SQL Server does not need to connect to the failover cluster at all

I have been troubleshooting for a long time to find the reason why SQL Server Setup will connect to the failover cluster, the following is the SQL Server installation log

If you start with a cluster node local user Administrator login to install SQL Server, you won't step on this pit .

3. Log off the cluster node computer, and then log on with the domain user dcadmin, and then set the SQL Server startup account to be a domain user dcadmin

Open Service Manager, first modify the SQL Agent startup account for the domain user dcadmin, and then modify the SQL engine's startup account to be a domain user dcadmin

Click "Location", modify the Find location, select ABC.com

Click on "Advanced"

Click "Find Now" and select Dcadmin

Click "OK"

Enter the password for the domain user dcadmin

Restart the SQL Agent service

After restarting you can see the login user as [email protected]

Similarly, the SQL Engine service requires the same settings

In this way, both the SQL Engine service and the SQL Agent service are started with the domain user dcadmin

SQL Server for another cluster node also needs to do the same

Note : After the cluster node is out of domain, both the SQL Engine service and the SQL Agent service are started with the local service account and can no longer be started with domain users

4. Add the dcadmin domain user to the SQL Server login user of two cluster nodes, select the sysadmin server role

Log on to SQL Server with SA first

Add the logged-on user to add the dcadmin domain user as a logged-in user as the SQL service adds the start account step

Grant sysadmin permission

Two cluster nodes can log in to SQL Server with Dcadmin domain Users

5. Back to SQL Server Configuration Manager, enable AlwaysOn availability groups

Note : The cluster node must be able to communicate, if the cluster node is disconnected from the communication, then enable AlwaysOn when the error

Restart SQL Server

If AlwaysOn is enabled successfully, you can see that enable Hadr is true in the server properties

6. In one of the cluster nodes in SQL Server, verify the number of votes for each node, and execute it on the SQL Server of one of the cluster nodes
Use the following SQL statement

SELECT * from  sys.dm_hadr_cluster_members;

SELECT * from SYS. [Dm_hadr_cluster]

7, again ensure that the node has shut down the firewall, if the firewall is not closed, then the creation of the availability group will be the following error pop-up

Unable to join database "test" to availability Group "XX" for availability replica "XXX"

The connection to the primary replica is not active. Unable to process the command (Error: 35250)

8, we use the way to initialize the database, so this step needs to create a new test library and test table and insert some test data (these actions are not demonstrated), and then make a full backup and log backup of the database

Then move the full backup file and log backup file to the WIN-5PMSDHUI0KQ machine, restore it in turn, full backup-"restore full backup-" Log backup-"RESTORE log backup
Script

--Execute CREATE DATABASE on WIN-7107JJJ2BCC [Test]use [Test]create TABLE [Test1] ([id] int,[name] VARCHAR () INSERT into [ Test1] SELECT 1, ' Test '

--Executes declare @CurrentTime varchar on WIN-7107JJJ2BCC, @FileName varchar SET @CurrentTime = replace (replace ( REPLACE (CONVERT (VARCHAR, GETDATE (), +), '-', ' _ '), ' ', ' _ '), ': ', ')        --(test Database full backup) SET @FileName = ' C:\DBBackup\ Test_fullbackup_ ' + @CurrentTime + '. Bak ' BACKUP DATABASE [test]to [email protected] with FORMAT, COMPRESSION  -(test Database log backup) SET @FileName = ' c:\DBBackup\test_logBackup_ ' + @CurrentTime + '. Bak ' backup log [test]to [email protected] with for MAT, COMPRESSION

--Execute use on WIN-5PMSDHUI0KQ [Master]restore DATABASE [test] from  DISK = N ' C:\DBBackup\test_FullBackup_2015_07_30_ 093949.bak '  with FILE = 1, MOVE n ' Test ' to n ' C:\Program Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\data\test.mdf ', MOVE n ' test_log ' to n ' C:\Program Files\Microsoft SQL Server\mssql11. Mssqlserver\mssql\data\test_log.ldf ', Nounload,norecovery,  REPLACE,  STATS = 5go--Note Be sure to use NORECOVERY to restore the backup using [Master] RESTORE DATABASE [test] from  DISK = N ' C:\DBBackup\test_logBackup_2015_07_30_105949.bak '  with FILE = 1, nounload , NORECOVERY,  REPLACE,  STATS = 5GO

The advantage of self-initializing a database is that if our secondary replica is a cross-room, we can control the initialization time, that is, the time of backup and restore, do not need SQL Server to help us back up and restore the database

What it looks like after a log backup is restored on WIN-5PMSDHUI0KQ

9. Right-Select the New Availability Group Wizard on the AlwaysOn High Availability node

Note: databases that are joined to AlwaysOn availability groups must meet the following requirements

(1) The recovery model of the database must be a "full" recovery model

(2) A full backup of the database has been made

(3) need to be user library, System library cannot join availability Group

(4) The database can be read and written, read-only libraries cannot be added to the availability group

(5) database in multi-user mode

(6) The database does not use Auto_Close

(7) does not belong to any other availability group

(8) database is not configured for database mirroring

One availability group supports up to 100 databases

10. Click "Next" to enter a high availability group name that has never been used Testag

11. Click Next to select the database you want to add

12. Click "Next", use Add Copy to add additional nodes to the availability group, and select the automatic failover node and the synchronous commit node, because we only have two nodes

We set the secondary replica to be readable, able to fail over automatically, synchronous commit mode

13. Click on the "Endpoints" tab page to set the endpoint

Note: The endpoint URL uses IP, do not use the FQDN long name, because the server will usually have two network cards, a public network card, a private network card, the endpoint is recommended to use the private network card address

In this way, the data transmitted directly at the endpoint is routed through the private network card, and using tcp://test.abc.com:5022 , the long name of the FQDN does not guarantee that the endpoint data is routed through the private network card.

The SQL Server service account uses domain user dcadmin to authenticate to avoid the way certificates are used

14, "Backup preferences" and "listener" do not need to set, keep the default on the line, the availability of listeners we add later, you can directly click "Next"

Click "Yes"

15, select the initial data synchronization, here Select "Join only" mode

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

Because you are using the "join only" Database initialization method, verify that the check is skipped like free disk space

17, click "Next" to check and confirm the previous configuration information, if correct, click "Done". In addition, you can save an availability group script here to step through the troubleshooting.

18. All summaries are completed successfully, showing Green checkmark. If a yellow warning is present, further judgment is required for success. If a red error occurs, the AG creation is unsuccessful

Note: If you use an odd-number cluster node and the quorum configuration uses the majority of the nodes, the WSFC quorum voting configuration displays a warning when the availability group is created

Click on the warning link will pop up the following dialog box, this warning can actually ignore

19. View server and database changes

Primary replica

The database has become synchronized

Secondary replicas

The secondary replica is readable at this time, and changes made to the Test1 table on the primary replica can be synchronized to the Test1 table on the secondary replica

Look again at Failover Cluster Manager

Availability group becomes a clustered role

Click on the display panel to display the availability panel

20, after creating the availability group, right-click on the "Availability Group listener" to add a listener to create the listener, select the network mode of the static IP (try not to select the DHCP network mode),

Enter a name that has never been used (this name will be used to create a network Name resource) and access port

Click OK

Created successfully

An A record is registered on the domain-controlled DNS Manager

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

Of course, you can also use listener IP to connect to SQL Server

Add the logged-on user to the secondary replica so that the logged-on user on the primary replica can also read the secondary replica data
Step 1: View the SID of the account on the main library [test] for the library name
SELECT * FROM [test]: sysusers
For example: Dalogin 0x99ad266afd26f841b3e49ef9633b0d4b

Step 2: Create the corresponding account on the replica database, where the SID corresponds to the value of the SID found on the main library
CREATE LOGIN [Dalogin] with password=n ' xxxxxxx ',
SID =0x99ad266afd26f841b3e49ef9633b0d4b, Default_database=[test],
Check_expiration=off, Check_policy=off

Report Account Settings
In the primary replica, execute:
Use library name
Go
User used by grant exec to report account connection or grant exec on stored procedure to report account connection

Basic management of availability groups

Note: try to use the SSMs UI or TSQL statement to manipulate the availability group, and do not use failover Cluster Manager

Fail over

First use the listener name to log on to SQL Server and see which machine is currently in the primary replica

Manual Fail-Over

Select Testag This availability group, right-"failover

Click "Next"

Click "Next"

Connect to WIN-5PMSDHUIOKQ this auxiliary copy machine

Click "Finish"

Start a failover

Execute query once again

Has failed over to WIN-5PMSDHUIOKQ this machine

View the properties of the Testag availability Group

Execute the following SQL statement to set the WIN-7107JJJ2BCC machine as a readable copy

Use [master]goalter availability GROUP [testag]modify REPLICA-N ' win-7107jjj2bcc ' with (Secondary_role (allow_ CONNECTIONS = All)) GO

Execute the following SQL statement to test

In the current primary replica execution

Insert into Test1 Select 3, ' Test3 '

You can see that the data is synchronized to the WIN-7107JJJ2BCC machine, and the database is readable.

A readable secondary copy is read-only and an error occurs if data changes are to be made to it

print ' servername: ' [email protected] @SERVERNAME INSERT into [test]. [dbo]. [Test1] Select 4, ' Test4 '

AlwaysOn Related views

--Use these two views to query AlwaysOn delay select B.replica_server_name, A.*from sys.dm_hadr_database_replica_states a Inne R JOIN Sys.availability_replicas b on a.replica_id = b.replica_id--The availability group is located in the Windows failover cluster select * from Sys.dm_hadr_cluster; SELECT * from Sys.dm_hadr_cluster_members; SELECT * from Sys.dm_hadr_cluster_networks; SELECT * from Sys.dm_hadr_instance_node_map; SELECT * FROM sys.dm_hadr_name_id_map--availability Group SELECT * from Sys.availability_groups; SELECT * from Sys.availability_groups_cluster; SELECT * from Sys.dm_hadr_availability_group_states; SELECT * FROM Sys.dm_hadr_automatic_seedingselect * from sys.dm_hadr_physical_seeding_stats--availability Replica SELECT * FROM Sys.availability_replicas; SELECT * FROM Sys. [Availability_read_only_routing_lists] SELECT * from Sys.dm_hadr_availability_replica_cluster_nodes; SELECT * FROM Sys. [Dm_hadr_availability_replica_cluster_states] SELECT * FROM Sys. [dm_hadr_availability_replica_states]--Availability Database select * from Sys.availability_databases_cluster; SELECT * FROM Sys. dm_hadr_database_replica_cluster_states; SELECT * FROM Sys. [Dm_hadr_auto_page_repair] SELECT * FROM Sys. [dm_hadr_database_replica_states]--Availability Group Listenerselect * from Sys.availability_group_listener_ip_addresses; SELECT * from Sys.availability_group_listeners; SELECT * FROM sys.dm_tcp_listener_states;--Add read-only routing list alter availability GROUP [agtest2]modify REPLICA on N ' Win-5pmsdhui0kq ' With (Secondary_role (allow_connections= read_only)); ALTER availability GROUP [agtest2]modify REPLICA on N ' Win-5pmsdhui0kq ' with (Secondary_role (Read_only_routing_url=n ') tcp://192.168.66.157:1433 ')) ALTER availability GROUP [agtest2]modify REPLICA on N ' Win-4ae61rva6uv ' with (secondary_ ROLE (allow_connections= read_only)); ALTER availability GROUP [agtest2]modify REPLICA on N ' Win-4ae61rva6uv ' with (Secondary_role (Read_only_routing_url=n ') tcp://192.168.66.158:1433 '))

Summarize

AlwaysOn build steps are introduced here, I hope you follow me to experience the power of AlwaysOn

Similarly, add a secondary replica: domain, join failover cluster, change quorum configuration, add secondary replica

Follow the article to believe that it is not difficult to add a secondary copy

And there's a problem. Read more about SQL Server errorlog and WSFC event logs

For more information, refer to the "SQLSERVER2012 implementation and Management Guide" book

Address: http://product.dangdang.com/1419881008.html

Related articles

Http://www.cnblogs.com/stswordman/p/3821538.html

Http://www.cnblogs.com/stswordman/p/3850570.html

Http://www.cnblogs.com/stswordman/p/3936584.html

http://www.cnblogs.com/stswordman/p/3252549.html#3229105

Http://blogs.msdn.com/b/psssql/archive/2012/09/07/how-it-works-sql-server-alwayson-lease-timeout.aspx

http://blogs.msdn.com/b/alwaysonpro/archive/2014/11/26/ Diagnose-unexpected-failover-or-availability-group-in-resolving-state.aspx

https://msdn.microsoft.com/zh-cn/library/ff878308%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396

Http://www.bubuko.com/infodetail-930336.html creates a weak event, but it is located on the wrong object to download patch resolution (https://www.microsoft.com/en-us/download/ details.aspx?id=36468)

http://blogs.msdn.com/b/alwaysonpro/archive/2013/10/30/ Errors-while-trying-to-create-an-availability-group-listener.aspx 19471 Error

Create Listener Error 19471,19476 problem
Http://blogs.msdn.com/b/alwaysonpro/archive/2013/10/30/errors-while-trying-to-create-an-availability-group-listener.aspx
http://blogs.msdn.com/b/alwaysonpro/archive/2014/03/25/ Create-listener-fails-with-message-the-wsfc-cluster-could-not-bring-the-network-name-resource-online.aspx

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o

2015-11-13 Supplement

Two-node AlwaysOn, using shared folders as a witness disk

When automatic failover occurs, sometimes the secondary replica is parsing, previously thought to be quorum lost (the WSFC lost contact with the file share witness), causing WSFC to hang, and later found to be caused by the problem of the maximum number of failures in the role attributes in the WSFC Control Panel.

It is recommended to set the maximum number of failures during a specified period to 10

View Code

2015-12-9 Supplement

Uninstalling a Cluster

1. Evict nodes first

2, destroy the cluster, the last node of the cluster can not be evicted can only destroy the cluster, there are two ways, 1 with the command 2 right-click on the interface-"Destroy the cluster

Method One: Cluster node (any cluster node) executes the following command, cannot be executed on the DC, because the DC does not have failover cluster installed, SQLTESTDEMO1 is the cluster name, method one is completely removed

Get-cluster Sqltestdemo1 | Remove-cluster-force-cleanupad

Method Two: Cluster-"more operations-" destroy the cluster

3. After destroying the cluster, run the following command in PowerShell on each cluster to clear some garbage

Clear-clusternode

4, in the domain-controlled AD user and computer interface to remove the cluster virtual name

5. Re-enable and disable AlwaysOn, operate on all AlwaysOn nodes

Delete an availability group

1. Delete Secondary Replicas

Use [master]goalter availability GROUP [hagroup01]remove REPLICA on N ' SQLSVR2 '; GO

2. Delete the availability database

Use [master]goalter availability GROUP [Hagroup01]remove DATABASE [SQLDB02]; GO

3. Delete Listeners

Use [master]goalter availability GROUP [hagroup01]remove LISTENER N ' SQLAG01 '; GO

4. Delete an availability group

Use [Master]godrop availability GROUP [HAGROUP01]; GO

5. Remove the listener virtual name from the domain-controlled AD user and computer interface

AlwaysOn Attention Issues

Http://www.cnblogs.com/lyhabc/articles/5310781.html

About AlwaysOn Backups

The backup types supported by the primary replica are not mentioned on MSDN and in the SQL Server2012 implementation and Management Guide

Tested, the primary replica is the same as SQL Server for single instance, full backup, differential backup, log backup all support

Fault-Switching criteria

The first step
Lookalive 5 sec/Time Telnet 1433 port timeout 30 seconds
Step Two
IsAlive 60 sec/Time Sp_server_diagnoist timeout 60 seconds timeout after 3 times to prevent miscarriage

Start using sp_server_diagnoist from SQL server2012 more accurate than select @ @servername
Avoid miscalculation caused by SQL Server's high pressure
"SQL Server2012 Implementation and Management Guide" P42 P50 P53

10 seconds/times between replicas ping each other, if not, such as the secondary replica does not pass, will be displayed as disconnect

AlwaysOn Attention Issue: http://www.cnblogs.com/lyhabc/articles/5310781.html

About arbitration: http://www.tech-coffee.net/understand-failover-cluster-quorum/

Build SQL Server AlwaysOn Third (configure AlwaysOn) starting from 0