Deploying SQL Server AlwaysOn High availability in an AD domain environment with restricted permissions

When you recently deployed a Microsoft TFS-based software lifecycle management platform to a customer, customers asked for high availability at the database tier, reducing the impact of software development on database server failures.

The customer's existing domain is a Windows 2008-level enterprise domain that is built on Windows Server 2008. In order to comply with the security requirements of the customer enterprise domain, minimum permissions are required during the deployment of the database for high availability, that is, only the full permissions of the operational account (TFSAdmin) to the organizational unit of ALM in the ad directory. On the basis of comprehensive consideration and invocation, we propose the following scheme and attach the operation instructions.

Scheme:

1. Create an organizational unit for ALM to hold computers and users in the ad domain, and assign Full Control permissions for the TFSAdmin user to this organizational unit, such as.

Figure I: organizational unit for ALM in the AD domain

Figure Two: TFSAdmin users have full control over the change of organizational unit

2. Create Computer objects in the ad domain in advance for clients and servers in the ALM organizational unit of the AD domain. As shown in figure one , all client and server computer objects in ALM are saved in the established OU.

Operations Guide: Key points in the Operations Guide:

1. Using the Action Account Domain\tfsamdin create a failover cluster (FC01), you need to set the computer account fc01$ to the "Create/Remove Computer objects" permission on the OU where the DB cluster computer is located

In the process of creating a database AlwaysOn listener, you need to create a virtual computer account in the OU, the Action Account used by the creation process is actually a computer account for failover (fc01$), and if this account does not have permissions to create computers on the OU where the cluster computer objects are located, The failure to create the listener will occur.

(You can learn that the failover computer account created the Listener computer account by looking at the security log for the event ID of 4741 on the ad controller, as in the following table)

The computer account was created. Topic:   Security id:  test\fc01$   account name:    fc01$   account domain:   test   Sign in ID:&NBSP;&NBSP;0X13B9CA New computer account:    Security id:  test\agroup03$   account name:    agroup03 $   account domain:   test Property:  sam account name:  agroup03$   Display Name:   -  User principal Name:  -  Home directory:   -  Master drive:  -  Script path:  -  configuration file path:  -  User station:  -  Last Password set: < never >   account expires: < never >   Primary Group id: 515   Allow delegation to:  -  Old UAC value:  0x0   New UAC value:  0x80   user Account Control:     ' Workstation trust account '-enabled   user parameters:  - sid History:  -  Logon Hours: < Not set value >  dns hostname:  -  Service Principal Name:  - Additional information:   Privileges   -

2. All the following operations use the Domain\tfsadmin account

3. The service account of the data engine must use a domain account (e.g. Domain\sqlservice)

Specific operation:

1. Create an organizational unit structure in the AD domain (figure i), and configure Domain\tfsadmin to have full control over the ALM node (figure II)

2. Join all nodes of the database server to the domain environment, join the Domain\tfsadmin account to the local Administrators group of the database server, and set the Administrator role for the data server

( If you need to join the client computer to the specified OU, you can use the command line netdom join%computername%/domain: "Ou=almcompuers, Ou=alm, Dc=test, dc=local" )

3. Create a failover cluster on a node-to-server and configure quorum using folder sharing

Figure three-creating a good failover cluster

4. Enable AlwaysOn on all database servers

Figure IV – Enabling AlwaysOn

5. Create a test database (TESTDB) on the primary node of the database and make a full backup of the database while sharing the backup directory

6. Create a high availability group for the database server and create a listener during the creation of a high availability group.

Figure five –sql Server AlwaysOn listeners

Figure six-Virtual machine object corresponding to listener

Figure seven-creating a DNS record that is automatically registered during a high-availability process