SQL SERVER builds AlwaysOn high availability groups

The project needs to ensure high availability of data, so the option is nothing more than Oracle clustering, traditional master-slave + heartbeat access points, and SQL Server AlwaysOn-like scenarios. (//experience is not much, understand and practice the program on this kind of, pat)

Oracle is too big, the core developers at the beginning of the project will not use (the project that was taken over from the university, the pre-graduate students do), so it is limited in the SQL Server technology stack, it is good to understand that SQL Server 2012 starts with AlwaysOn components, Fully meet the project requirements, so this program is so determined.

"Introduction to SQL AlwaysOn Technology"

The Always on Availability Group feature is a high availability and disaster recovery solution that provides an enterprise-class solution that replaces database mirroring. The Always on Availability Group 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 eight groups. Optionally, the secondary database can have read-only access and/or some backup operations.

The availability group fails over at the availability replica level. Failover is not caused by problems such as database corruption due to loss of data files, deletion of databases, or corrupt transaction logs.

Core content, it looks like a master-slave deployment, but for the master and slave, only the ability to achieve a single frequency of data backup, and can not seamlessly switch. While AlwaysOn automates the transfer of Windows failover Clustering technology, that is, if you have a B two node, A is the master, then B will stay in sync and automatically connect after a hangs up. For external access, the entire cluster exposes a virtual IP, which is the access point for this cluster.

This way, when the cluster is built, the whole cluster is black-out.

According to the official statement, the availability group supports a primary node and 8 secondary nodes (replicas)

In this way, the general business is fully wealthy, regardless of how it is thought.

"Hardware, network environment"

In my case, the hardware environment used 4 computers altogether. Their roles were:

1. Dell R730 * *, cluster node, database node.

2. Shared storage required for the Dell 3,010 cluster quorum witness.

3. Windows Server R2 VM, cluster domain control.

My network environment is a single network, in my office LAN alone to the four PCs set up a domain abc.com.

In fact, production environment should definitely not do so, good practice should be the cluster in the network, the outside network business alone to go to an external LAN, with data middleware to the database interaction.

Just set up this double network environment to build a cluster, you may step on the pit.

Note that because it is in a domain environment, domain control must always be on, otherwise you will not be able to log in using a domain account.

So it's not advisable for me to use a virtual machine to do domain control. (I am convenient for Project site deployment)

Let's take a brief look at the steps before you start AlwaysOn, because there is a bit more, so don't write anymore ...

To see step by step steps, please visit:

Build SQL Server AlwaysOn First "Configure domain Control" starting from 0

Build SQL Server AlwaysOn Second "Configuring Failover Clusters" starting from 0

Build SQL Server AlwaysOn Third "Configure AlwaysOn" starting from 0

These three articles are very detailed, especially thanks to the bloggers.

"Pre-order Steps"

0. System Environment Preparation

The system environment recommends using the Windows Server R2 system and must be installed as DataCenter, otherwise failover is not supported.

On the software side, the new system is installed to use the Role Manager to add. Net Framework3.5, or SQL Server SP1 cannot be installed.

Modify the network environment, according to my experience after stepping on the pit, it is recommended that if you want to use a dual network environment, first in the Intranet Environment (cluster network) to complete the building, and then join the external network environment.

SQL Server can not be installed at this time, in case the cluster setup failed to reload the computer ...

　　

It is recommended to use a tool like ghost to back up the system. Because if the cluster establishes a problem, it is possible that the node cannot clear the configuration of the cluster so that you cannot join the new cluster, and it is likely that you will not be able to exit the old cluster and use the Clear-clusternode tool.

Step out of the cluster and later have time to write the blog again.

1. Build a domain environment

The domain environment is created in order to create a failover cluster, in fact, you will understand that the domain environment is to ensure that the Windows account can be directly in the login authentication between the nodes.

So the domain user you can add more, but eventually to two nodes with a domain user login to configure.

After the node is added to the domain, it is important to remember to add the domain account to the current PC's Administrators group, otherwise the build group is prone to permissions problems.

　　

The cluster quorum witness is this way, you can understand that there is always a shared location between nodes for file Exchange, just like a hot mirror scheme for a dual machine, a shared storage is required to synchronize the data.

So the PC that puts this witness folder must be running with the cluster and can be accessed by all nodes in the network. Referring to the blog The second article mentions that even nodes can be a shared folder.

To reduce the permissions problem, I added this PC to the domain environment. (This can be done without doing)

　　

After the domain environment is set up, enter the configuration of the failover.

2. Configuring a failover cluster for a node

First you will use the Administrator account to log in, not the domain account .

After using Server Manager, in the Add roles and features, go to the feature options to locate the failover cluster, configure the server as failover mode, and install the Failover Cluster Manager.

The failover Cluster Manager looks like this:

Because I was afterwards. jpg (escape) There is already a list of my clusters.

After this step is OK, make sure that the firewalls on the nodes are shut down, and then continue configuring the failover group. (It is important to note that after establishing a domain environment, the network will have one more domain network, so if there is no check, it is likely that its firewall is turned on.) This causes the cluster to not find the corresponding node. ）

Shutting down the firewall ensures smooth communication between nodes because a firewall device is typically used exclusively for external use. In addition, if the multi-network, the Theory of Internal network (private network) and the open firewall outside the network does not have much impact.

In the configuration of the failover cluster, the warnings in the authentication configuration can be ignored, but there must be no errors . Some warnings are best viewed.

In the configuration after verification, do not tick the "Join all eligible shared disks to cluster" option (this is mentioned in the above blog). ）

　　

After the cluster is established, you can ping the external IP of the cluster according to your configuration,

　　

As I understand it, this cluster is equivalent to being a virtual machine on the outside.

To this, the preliminary preparation work basic OK.

"SQL Server AlwaysON Configuration"

0. SQL Server version requirements

Be sure to use SQL Server Sp1 or later, or you will step into a pit. (referring to the third article has mentioned this pit)

1. Installing SQL Server

Use the Administrator account to login and install, otherwise you will also step on a pit. (The reference to the third article also mentions.) ）

To choose a new installation, configure on-demand, but it is a good idea to ensure that the data and log directories are the same on each node.

In addition to set up the account in the installation of the step, referring to the blog mentioned to the SQL Server data Engine and SQL Server Agent account from the default NT account to the domain account, this I think the blogger was wrong, because the installation under Administrator, The domain account is not available here.

So this step is recommended to fill in the administrator account. (modifications will be made after installation.)

Be sure to use SA account hybrid authentication!

Other blogs do as well.

After installation, configure the SQL Server service in the service, the SQL Agent service default login account is a domain account, then be sure to check on the two nodes on the other node's computer name + domain account login.

(The first use of a dual-network environment to install, here is extremely prone to problems.) Although SQL Server supports multi-NIC routing, but I encountered the situation is not able to log in this combination, only with the intranet ip+ SA, so that the AlwaysOn configuration of the route is the intranet, the final step is not successful. ）

　　

2. When the above work is confirmed, start the configuration.

First, SQL Configuration Manager opens, enabling AlwaysOn availability groups. Each node is manipulated, and finally the attribute is validated in an instance of SQL.

Verify AlwaysOn Enabled:

After validating two nodes, you can see in the object Manager of SQL Management Studio that AlwaysOn high availability groups can right-click to add availability groups.

This is the result of the final establishment.

3. Prepare the Database

Because the availability group is for the database, this step prepares the database first. When migrating from a stand-alone server to a database, you need to perform a full backup to recover the database on one of the nodes.

This database is then backed up again on this node, and it is important to note that the backup must be with No recovery. "Be sure to check it out, otherwise you won't end up linking to this copy for synchronization"

Here, the newly backed up database is copied to Node B for recovery.

If the above operation is resumed, the icon for the database on the B node will have a green arrow, and the state in parentheses is (restoring ...). ）

By the end of the above steps, you can formally enter the establishment of the availability group.

4. Availability Group Creation

Right-click on the availability group mentioned above and select the New Availability Group wizard:

　　

Then select the database:

　　

It is important to note that only the user database is listed here, which is the database we restored earlier. In particular, note the information given in the Status column, which is not allowed to enter the next step if it is not "meet prerequisites".

The prerequisite condition is mainly 1. User database, 2. Full backup, meet these two conditions here will not error.

However, if the B node does not have a restore with no recovery, B cannot be added to the availability replica.

This step requires attention to check the configuration. (Figure Self-citation blog)

In addition, the configuration of the Endpoint tab, the default URL is the computer name: port, it is recommended to manually fill in the IP: port, so you can forcibly specify the intra-AlwaysOn group of routes. Otherwise, if you leave the default, the routes accessed under multiple networks are random.

Then follow the wizard, and the final step, if you succeed, you should

Figure Self-referencing blog

It is important to note that if the last step fails, the reason is that the firewall, the B node is not the recovery model, the A-b node cannot use Windows account authentication with each other (check the domain environment, consider whether there are multiple network conflicts)

　　

In fact, you will find that the status on the AB node becomes synchronized, and you can now log into the DB instance of the High Availability group directly using the external IP + domain account of the failover cluster mentioned earlier.

The availability group also has an availability group object that you configure.

The listener is then established for this availability group, and I understand that using a listener can be independent of the cluster's IP. In fact, the listener can arbitrarily configure another access point IP.

Here, the entire AlwaysOn build is complete.

PostScript

It took a lot of time to toss this out, and I hereby record it. Hope that you can help to see the blog.

　　

　　

SQL SERVER builds AlwaysOn high availability groups