Introduction to SQL Server cluster knowledge

Cluster cluster types Introduction iSCSI-based SQL Server 2012 cluster Test (i)--sql cluster installation SQL Server cluster How to detect MS DTC distributed Transaction Coordinator online in a clusterfirst, SQL Server cluster Basic schema

Whether a cluster or a non-clustered SQL Server server, you need the following basic components to provide data services:

1. An instance of SQL Server or a SQL Server binary executable that makes up the various services that the database management system runs, manages database data and client requirements, executes operations, and so on. Whether clustered or non-clustered, these instances are installed on a local disk to provide services, so installing a SQL Server cluster not only installs the primary SQL Server cluster on the active node, but also adds the cluster service to the different nodes.
2. The system and user database, including the actual data, as well as individual database settings and so on; non-clustered, the data is stored locally, accessed by the local instance, the database is placed on the shared storage in the cluster, each node has the ability to access it (but only the active node is allowed access at any time); SQL The server instance completes database management by mounting the database.
3. Accessing the database also requires a server network name, or an IP address. Locally with local IP or alias, the cluster accesses the virtual name or virtual IP.

Figure 1.1 SQL Server cluster Basic schema

By changing the cluster server above, the SQL Server service fails over to another node before the SQL Server service stops the failed node first, the shared storage is mounted to the standby node, the virtual IP is re-bound to the standby node's public NIC interface, and the SQL Server service for the standby node is started. The services of the standby node read the shared storage data to restore the business. The client accesses the database resources simply by accessing the SQL Server service through the virtual name or virtual IP.

There are many ways of Windows clustering, on the basis of a variety of general, SQL Server cluster instance installation there are many ways, different businesses can choose different ways to install, on the basis of security and stability to maximize the use of server resources.

II. SQL Server cluster resources and dependencies

Enter cluster res in the DOS interface to see what cluster cluster resources are, such as Windows clustered resources, and which resources are SQL Server cluster services?

2.1 Shared storage

Storage System and user database, database error log. Tempdb is not stored on shared storage, and tempdb is reborn as a result of restarting the service. However, different nodes are required to have a storage path for tempdb. To prevent the restart from occurring, the path does not exist with an exception.

Storage has always been a bottleneck in the computer world, and this bottleneck is particularly noticeable for databases with high IO read and write requirements, so it is recommended to use RAID10 to improve disk performance in frequently accessed databases.

2.2 Cluster Groups

The cluster group has a virtual network name and IP for Windows Clustering for managing Windows clusters. Under normal circumstances, whether it is heartbeat detection or service detection, is the communication between node networks, rarely involving access to the domain, but in the event of a failover, the need to verify the individual cluster nodes, involving the domain server.

2.3 SQL Server cluster virtual IP and virtual network name

SQL Server IP address and network name, providing links to clients. The network name name points to the virtual IP address, and SQL Server accesses the network name.

SQL Server network name for cross-subnet cluster also exists whether all IPs are registered on-line issue, through testing to determine whether it is not necessary to register online. You need to set Registerallprovidersip to 0:cluster res "SQL Server cluster Name"/PRIV registerallprovidersip=0

2.4 SQL Server services and other services

SQL Server services and other services provide database services.

2.5 cluster resource dependencies

These resources work together to provide services through the Cluster Service Manager, which manages relationships with each other. The dependencies for these resources are as follows:

1. Network name depends on virtual IP address

For cross-subnet clusters, when adding cluster nodes, there is a case of cross-subnet access, the number of times the virtual network will register two IP addresses in the domain, the two IP dependencies are or, that is, only one online.

2. SQL Server service relies on shared storage and network name

The SQL Server service can start only if the shared storage and network name are both online.

3. The SQL Agent service relies on the SQL Server service

iii. How SQL Server clusters are detected onlineiv. SQL Server Cluster serviceDatabase Engine service

The core components of a SQL Server database can be set in clusters. Install the SQL Server database engine in the cluster, together with SQL Server replication distribution, full-text indexing, and data quality analysis.

Analytics Services (Analysis Service)

Support Analysis Services cluster starting from SQL Server 2005;

If you deploy the Analysis Services cluster separately, it is very simple and does not require much consideration, and if you are deploying to the same cluster as the Database engine service, it is recommended that you install the Database engine services and Analysis Services separately into the respective resource groups (this requires each resource group to own its own shared disk, name, IP Address Resource)

If the analysis service and the Database Engine service are installed in the same resource group, the Analysis Service system database is installed together with the database system database and the analysis service is affected once the disk that placed the system database becomes problematic. At the same time, when the Database Engine service or analysis service needs to be upgraded, two services must be upgraded at the same time.

Full-text indexing and SQL Server Broker

Starting with SQL Server 2008, full-text indexing and SQL Server Broker services have been integrated into the Database engine service, so they can work correctly after the Database engine service failover occurs.

reports and SSIS Services

The cluster API is not supported in reports and SSIS service programs, and cannot be clustered in the same way as the database engine. However, you can also create a generic cluster application to cluster reports and SSIS. However, because the code does not contain the cluster API, some features may not be normal after switching.

SQL Writer Service

The service is installed on each node of the cluster and is disabled by default.

SQL Server Browser service

Each node in the cluster also installs the service, which cannot be clustered.

v. Distributed Transaction Coordinator for MS DTC in clusters

Introduction to SQL Server cluster knowledge