Build SQL Server 0 AlwaysOn third (install data, configure AlwaysOn)

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

Operation Steps:

1. Install SQL server2012 ( Note: Be sure to install. NET fromwork 3.5 and log in and install with each node's local administrator account, do not use the Domain Administrator account )

2. Select a new installation of SQL Server standalone installation or add features to an existing installation

2. Determine the installation program support rules

3. Setting up roles, SQL Server feature Installation

4. Select the installed feature, select All or partial (the primary management tool needs to be installed)

5. Add the domain administrator account to the SQL Server Administrator account

The above completes the SQL Server 2012 installation and then the same actions for each node (long002,long005)

6. Log off the cluster node computer and log in using the domain user Dcadmin

7. Open the Service Manager, first modify the SQL Agent startup account for the domain user dcadmin, and then modify the SQL engine startup account for the domain user dcadmin

8. Add a domain user and enter the domain user password

9. Start the SQL Server Agent service

SQL Engine service and SQL Agent service are started with domain user dcadmin

SQL Server for another cluster node (long002,long005) 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

10. Next open SQL Server Configuration Manager, enable AlwaysOn availability groups (if no domain users are joined to the SQL Server login user of three cluster nodes and the server role chooses sysadmin, need to be set)

11. In one of the cluster nodes, in SQL Server, verify the number of votes for each node, executing on one of the cluster nodes on SQL Server
Use the following SQL statement

12. We use our own database data, then make a full backup and log backup of the database and then move the full backup file and log backup files to the other node machines, restore them in turn, full backup-"restore full backup-" Log backup-"RESTORE log backup

13. 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

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

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

16. Click "Next" to add additional nodes to the availability group using the Add copy and select the automatic failover node and the synchronous commit node

17. We set the secondary replica to be readable, capable of automatic failover, synchronous commit mode

18. Click the "Endpoints" tab page to set the endpoint (note: use IP, do not use FQDN long name method )

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://long.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

19. "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"

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

21. 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

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

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

Take a look again . Failover Cluster Manager

Availability group becomes a clustered role

24. Click on the display panel to display the availability panel

25. After creating the availability group, right-add the listener on the availability group 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

26. an a record is registered on the domain-controlled DNS manager

27. a failover cluster virtual network Name account is added to the Computers container in the AD

28. Log on with the Listener name SQL Server

The above has been completed to build SQL Server AlwaysOn

Note: Let's take a look at the middle pit.

1. Data requires a full recovery model

Workaround: Modify into full recovery mode

2. Add a secondary node there is no way to add, unable to connect

Workaround: Check if fire resistance is turned off (discovery Domain firewall is not off), then turn off firewall

3. This is I manually back up one, then copy to different node manually restore, then error

WORKAROUND: Export the backup using the script data, then copy to the other node to restore by script, and then successfully

4. Other nodes are unable to connect, the first day after the shutdown, the next day the boot is like this

Solution: The result query down is that the service does not start automatically, and then started on it.

Build SQL Server 0 AlwaysOn third (install data, configure AlwaysOn)