Windows Server 2012 build SQL server always on full record


Windows Server 2012 build SQL server always on full record

Environmental information:

Windows Server 2012 R2
Sql Server 2012

Windows Server 2012 build SQL server always on full record

The whole process is divided into three steps

  • 1. Configure the pre control and add the machine to the domain.
  • 2. Configure windows fail over cluster
  • 3. Configure SQL server always on high availability group (Ag)

Full record of stepping on the pit

  • Pre control configuration

    It’s better to configure. It’s important to note that the non precontrol machine needs to point DNS to precontrol before joining the domain. A pit encountered here will be prompted when adding a domain to node2 node

    Windows Server 2012 build SQL server always on full record
    The virtual machine uses Huawei private cloud application. The SID of the application server is consistent with node1 node, so it is enough to regenerate the SID
    Check the general item and start * *.

  • Configure the windows failover cluster.

    When node2 node is added to node1, it will prompt “unable to access the remote registry, please make sure that the remote server registry service is running and remote management has been started”.


From the literal point of view, put this diagnostic registry service on the windows policy, and found that the service and policy configuration are all right. Finally, when trying to add a local node node1 to node1, you will be prompted that "no local failover tool is installed". After querying in the windows service, you only checked the option of the failover management tool. In fact, you did not install the failover cluster (there are two options in the windows service, and checking the cluster management tool will not cascade the cluster service). The problem is solved. The diagnosis took a long time due to the misleading hints.
  • Configure SQL Server AG

  1. Failed to install SQL Server

    Because. Net Framework 3.5 is not installed. Windows Server 2012 R2 can’t install. Net Framework 3.5. It’s a common problem. After checking on the Internet, you can manually make a SXS local file path and configure the installation. It’s hard to find SXS download resource package on the Internet. CSDN needs points, and you need to send me a private message.

Windows Server 2012 build SQL server always on full record

  1. When configuring SQL server available group, creating Ag prompts that the specified file directory on node2 does not exist

Windows Server 2012 build SQL server always on full record

This problem is because the SQL servers on node1 and node2 both use named instances. There is a default instance on node1, and the name of the named instance is different. As a result, node2 is misled to go to the folder of the default instance to find it. In fact, the default instance is not installed. Some solutions are to manually map to a new path by writing SQL statements, which can be solved.
I directly installed a new default instance on node2, the problem was solved directly, simple and crude.

  1. Configure listener listeners

    The process of configuring a listener is relatively simple. You can specify a static IP address and a default port number of 1433. However, after configuring the listener, there is a peculiar phenomenon. You can only Ping the listener name or IP on the primary node. You can’t Ping on any other replica machine, and you can’t connect using SQL Management Studio (you can connect on the primary node).

    There is no problem with database synchronization, but because the application needs to use the listener address to connect when connecting to the cluster, the external application cannot connect to the cluster and the cluster does not work. From the pre control and DNS records, everything is normal

    After checking many posts on stackoverflow and TechNet, many people encountered the same problem, but the solution was basically not work. Finally, we suspected that it was a network problem, so we contacted the network administrator for help. Finally, we discussed it together. It was because the listener IP and name were not bound and exposed on Huawei’s private cloud, so we verified that other servers could not Ping, and only the primary node could connect. After the network administrator has finished processing, the problem is solved.

Concluding remarks

The whole building process takes about a day and a half, during which there are more environmental problems and repeated work on multiple nodes, so the whole building process is smooth. After that, in Windows Server 2016 and SQL Server 2016, we successfully built a domain free SQL Ag environment, but we still stepped into a lot of pits. If you encounter problems, please send me a private message.
If you also encounter the pit I listed, I hope this article can help you. The most complete configuration document is concerned with official account and private author.

Like to remember to scan below the QR code attention Oh~

Windows Server 2012 build SQL server always on full record

This article is published by openwrite, an operation tool platform such as blog Group Publishing and multiple publishing