Build SQL Server R2 Failover Cluster

The first half of the task, there is a SQL Server Advanced topic, advance topics inside another important is about the cluster. Then there is the study.

SQL Server cluster, the English full name is called Failover clustering, belongs to the category of high availability. Simply put, the role is to ensure that 7 X 24 hours of continuous service delivery, in a timely manner there are individual server downtime, the other servers will be seamlessly on the top, continue to ensure the availability of the application, and the user in the process of downtime is not too obvious, in addition to some of the current operation has some pauses. In the process of switching between an already down and an available server, the code can be switched in the exception section.

There are two aspects involved, one is how the code is written, this Part I have never been involved, that is, no such programming experience, have to make up; the second is how to build such a Failover Cluster, and how to automatically switch or manually switch, how to monitor the running state of these servers. Once in Windows Server 2003 did a SQL Server 2000 cluster build, the construction process is rough, in full accordance with the network log or blog, step by step experiment. There is no in-depth study of the concepts involved, such as what is called domain, domain control, virtual subnet, heartbeat, fault cluster (is there a load balance cluster, which provides network load for multiple servers?). The performance and service objects of 1 servers are limited, but multiple servers can solve concurrency problems. Is there a concept of distributed services here? ), as well as the quorum disk, ISIC, SAN, Hyper-V, etc. See, only ask yourself a few questions, technically there is still a room for promotion.

So, for this SQL Server failover clustering ask yourself a few more questions:

1 How many components, including hardware and software, are needed to build such a cluster:
What is now imagined is:
Windows Server 2008 (and above) Failover clustering,
SQL Server 2005 (and above) Enterprise Edition; SQL Server 2012 has the AlwaysOn concept, not just the high availability.
Steps:
1.1 Prepare 3 virtual servers, 1 are used for domain control, shared storage (but later found to be a separate one to do the shared storage, because Windows Server 2008 does not support the sharing storage service on the domain control or any one cluster node), 2 to do Failover Clus ter nodes.
Install domain control, Active Directory domain Service, DNS.
Issues that occur during installation:
1.1.1 Active Directory Domain Services (referred to as ADDS) is not feature but Role, so it is new in Add Roles and cannot be installed with other role. You must first install Active Directory Domain Services.
1.1.2 ADDS will restart the server during installation, run Dcpromo.exe to configure the Domain Controller after installation is complete.
1.1.3 A Delegation for this DNS server cannot is created because the authoritative parent zone cannot be found or it does Not run Windows DNS server. When you install DNS Server, you encounter a similar hint, and I guess the idea is that you don't have to do anything if you're just building it inside the intranet. Because I did the experiment in the virtual machine, so whatever it was.
1.1.4 There is a step to configure the database, log files, and SYSVOL corresponding folders for Active Directory Domain Controller, we will give you the default:
C:\windows\ntds; C:\windows\sysvol
1.1.5 Directory Services Restore Mode Administrator Password: Unlike Domain Administrator account, re-specify a password: Smart123456.
1.1.6 ADDS Installation Complete: This Active Directory domain controller is assigned to the site "Default-first-site-name". You can manage sites with the Active Directory sites and Services administrative tool.
1.1.7 Add the other 2 servers to this domain, I thought as long as the domain name modification, it is designated as an already set up the ADDS server is OK. But the error is: an Active Directory Domain Controller (AD DC) for the domain "smartoffice.com" could is contacted.
Updated: 2016/3/26
The workaround is to designate the DNS of the two computers that will be joined to the domain as the IP of the newly created DNS server. And then join the domain.
1.1.8 Create iSCSI: Start Microsoft iSCSI Service. Before starting the iSCSI Service, you must download the iSCSI Target, which can be downloaded on the official Microsoft website.
1.1.8.1 creates an ISCSI Target named: Smartofficetarget, IQN (identifier, the resource name that can be accessed) is smartofficetarget.smartoffice.com.
1.1.8.2 2 virtual files as storage for the target created above, specify file size
1.1.8.3 then specify iSCSI identifier, it is important to be careful here, the iSCSI identifier and Target are two-way bound, now when Target is created, it is necessary to specify the iSCSI identifier that can be accessed (that is, you can access This target corresponds to the stored server). And, as far as possible not to establish target on the Domain Controller, I stuck here for a long time until a change of a non-domain-controlled server. Finally, we need to format the "hard disk" (here is the virtual). Here's a question to stay here, what does this ISCSI-specific hard drive use? The answer is to send two uses: A piece for arbitration, a piece to put the data. Basic Configuration 2 fast enough, but if it is production, then the more the more.
Updated: 2016/3/27
1.2 Installing Windows Server failover Cluster
1.2.1 needs to be pointed out here because it takes a lot of time here:
First, I created a VM for Windows Server 2008r2 and then copied 2 copies as cluster node. Originally, it is convenient to configure failover cluster with domain Administrator account, and the result indicates that the load topology cannot be used. Is the legendary ox80070005, see the instructions "Access denied" should be inaccessible, and then opened a bunch of services, usually with the network, remote related service is turned on to turn off the firewall. The result is no, this attempt has failed.
Then started to establish a domain user, tried to add domain users to the local Administrators group, also tried to use Group Policy method of adding groups to the domain user authorization, the result is not yet. Quite funny is, every time I forget the local Administrators group inside add domain Users, the next time to open, found that only administrator in the inside, other new added domain users are gone. When re-added, it will be reported that the user is already in the list. Strange mistake, I thought it was Microsoft's bug. In fact, the answer is below.
Finally bing.com hint, also can try to use Sysprep to fix, specific what ghost, try again. Bing.wikipedia's explanation, Sysprep is to let you distribute your hands of Windows, OK, that Sysprep is equivalent to the system prepare. After some tossing and installing a series of services, sure enough, in the local Administrators group to see the domain users I want to add. Internet era of things that you want to know can be found on the Internet, you are not a person!
Updated: 2016/3/28
1.2.2 Access Point for a Cluster:
Cluster name:smartofficedb
NETWORKS:192.168.2.0/24 address:192.168.2.110
This step is also a bit delayed because duplicate physical address of Windows Failover cluster virtual adapter. The reason is that I used three VMs that were copied from the same VM to do the nodes, and I did Sysprep in the case of Failover Cluster feature installation. So redo a VM and look under Sysprep to see if it solves the problem.
Updated: 2016/3/29
Another thing to be aware of here is the problem of shared disks: How many disks are needed, and how each node mounts the disk. Create an iSCSI storage server, configure iSCSI target, and virtual disks so that each Node can be initialize to these virtual disks. The more depressing is that the server that has iSCSI target installed is the target that cannot be created with initializer.
Updated: 2016/3/30
Here comes a heartbeat problem: as the heartbeat of the two network card (as long as the addition of two virtual network card in VMware), only allowed to do one thing, is communication. Other can not do, the network card properties in addition to the other IP4 removed, set two adjacent IP address, such as 10.10.10.1/10.10.10.2, and set to not automatically add suffixes in the domain, disable NetBIOS. Otherwise, even if all the validation are over, cluster will not be able to create a success and may report a time-out problem. Bring the iSCSI disks to online before creating cluster will increase the speed.
Updated: 2016/3/31
1.3 Installing AlwaysOn Failover Cluster Instance
Relatively simple, all the way next.

2 How to monitor the running state of this cluster?
2.1 How many concurrent volumes can be run by a cluster
2.2 Logging and status of running cluster outages
2.3 How to manually switch the active node of a cluster

3 Simulating the outage process
3.1 Programming to implement access to this cluster
3.2 Deliberately let one of the machines down
3.3 Observe concurrency, find maximum concurrency and system indicator parameters. This should be a balance point.

Answer each:
1 first to understand the concept of the domain
1.1 Why there is domain, the role of domain and creation
Domain is a divide and conquer thought, all the computer resources in the network have their own IP, access to these computing resources, relying on IP to locate, it is not so easy to be recognized by humans, easy to forget. Therefore, the domain server is used to record the IP and resource name of the corresponding relationship.
In Windows Server, DNS is the bottom-level of implementation, and management and configuration of these DNS should be implemented by Active Directory Domain Service. First AD DS uses Dynamic DNS to publish its own IP, making other DNS aware of its own services, as well as pulling other servers to provide services and corresponding IPs. AD DS enables the sharing of records between several DNS and constantly synchronizing updates. Before the Dynamic DNS was invented, only manually synchronizing the mapping information between DNS by manual.
Therefore, the creation of DNS Server can also be created by adding AD DS Feature.
1.2 How to join a domain
1.3 Domain management: Adding and deleting changes
1.4 Domain-controlled high availability? A cluster or a dual machine?
1.5 The difference between DNS and Active Directory DomainService

2 arbitration Quorum (translated here as the number of arbitrators or the number of arbitrations can be better understood)
2.1 The role of arbitration: Determines whether the Cluster is working, can not run up;
2.2 Creation of arbitration:
Starting with Windows Server 2008, it is no longer a single quorum disk. Instead, it provides multiple modes of arbitration, such as files, disks, nodes, and so on. This avoids a single disk quorum flaw. In Windows 2003 quorum only uses the disk, that once the disk loses the union, the entire cluster will be the machine.
The quorum is configured by the cluster software. Here are two concepts to figure out, one is Failover Cluster Manager Snapin, two is Cluster software.
2.3 Administration of arbitration
2.4 Arbitration mechanism: Here is a concept called "majority", English name is called Majority. He has two characteristics, one is automatically determined, the other is dynamic.
Automatically determined is the designation of the number of cluster nodes at the outset, which is more than half the number of nodes. If there are 5 servers, when there are 3 machines, the Cluster can not continue to run. This Cluster can still run when only two servers are down. So the number of servers is the best base, so better judgment.
Dynamic, based on the first feature, as the number of servers increased, the number of arbitrations increased correspondingly.

32 Ways of Clustering
3.1 Failover Clustering: The main supply is high-availability higher availability.
Hardware requirements: Must be certified by Microsoft.
Software Requirements: Server (version, patch, machine hardware configuration, etc.), network (unified subnet, same domain, Heartbeat, IP), storage (shared storage). AD DS (Active direcory Domain Services) role (roles). Storage, in the production environment, is sure to be used with the SAN, to fiber-level transmission. But to develop a test environment, we can use ISCSI to create a shared storage. Windows Server 2008 comes with.
Installation: From Windows Server 2008, the system components are installed from Add-feature, and the system components to be mounted here are Failover Cluster. After the Failover cluster is loaded, there will be Failover Cluster Manager snapin to manage Failover. The underlying services are Cluster service. Hardware and software validation is also done by Failover Cluster Manager Snapin. Visible configuration hardware and software in front, set Failover Cluster after, and finally start management Cluster Node.
As you can see from the installation approach, the way Windows Server is managed is also changing, and keeping up with the latest features is a necessary thing. Always pay attention to new trends, write down the analysis of new features, hands-on thousand experiments, record the experience. To have this consciousness!
Installation steps:
3.1.1 Verification Prerequisites: Hardware and software configuration
In addition to hardware and software, there are other configurations that need to be created in advance.
For example, clustered storage can be created when creating a cluster, or it can be created after the cluster is created; All servers must be joined under the same domain; Create domain users and give them administrator-level permissions so that they create clusters on the server.
3.1.2 on each server, add Failover Cluster feature.
3.1.3 Through Validation of failover Cluster Manager snapin to verify various configurations
3.1.4 Failover Cluster Manager snapin To configure and start cluster
Here's a concept: Access point for administrating the cluster. This AP needs to configure him with a NetBIOS Name and a dedicated IP.
3.1.5 Create AD DS Clustered Roles.
It is necessary to automate part of the repetitive operation via PowerShell!
3.2 Network Load Balance: Mainly provides high scalability Scalability, load balancing. Think carefully, so many cluster is not for high availability, as for horizontal expansion, bottleneck or write, more than a few machines what is the use? or a variety of locks. Read through the replication stripping out, engage so 7, 8 from the library, concurrent and duly completed. But the writing is sad, ACID let you follow the rules, the queue or line.
3.3 Individual building methods
4. AlwaysOn Availability Group
A single point of failure is most likely to occur at the storage level. Because the storage does not have more redundancy. SAN can only support local high availability, the extreme example is the local computer room earthquake, the rest of the dish.
In response to this problem, AlwaysOn presented the availability group. To achieve database-level redundancy, offsite data synchronization, high availability is improved some.
Of course availability Group and Failover cluster can be used together.

Build SQL Server R2 Failover Cluster