SQL Server Cluster Network Name Resource ‘SQL Network Name’ Failed Issue

Issue
Today will discuss about the issue that I’ve encountered during performing an installation of a SQL Server failover cluster is “The cluster resource ‘SQL Server (MSSQLSERVER)’ could not be brought online due to an error bringing the dependency resource ‘SQL Network Name (MSSQL2012)’ online.” Upon checking the cluster events in the Failover Cluster Manager, you will find the below error.

Cluster network name resource 'SQL Network Name (MSSQL2012)' 
failed to create its associated computer object in domain 'ENTERPRISE.ORG' for the following reason: 
Resource online.
The associated error code is: -1073741790
Please work with your domain administrator to ensure that:
- The cluster identity 'WIN2012$' can create computer objects. 
By default, all computer objects are created in the 'Computers' container; consult the domain 
administrator if this location has been changed.
- The quota for computer objects has not been reached.
- If there is an existing computer object, 
verify the Cluster Identity ' WIN2012$' has 'Full Control' permission to that computer object 
using the Active Directory Users and Computers tool.

Lets first understand, what is Cluster Name Object (CNO)?
In a Windows Server Failover Cluster, a cluster name object (CNO) is an Active Directory (AD) account for a failover cluster.

A CNO is automatically created during cluster Setup. When the administrator creates a failover cluster and configures clustered services or applications, the "Create Cluster Wizard" creates all the Active Directory computer accounts the failover cluster requires and gives each account specific permissions. The wizard also creates a computer account for the failover cluster itself; this account is called the cluster name object.

The CNO is important because other accounts are created through it. If the CNO is deleted or permissions for the account are changed, other computer accounts required by the cluster can't be created until the CNO and correct permissions are restored.

Beginning with Windows Server 2012, both the Create Cluster Wizard and the PowerShell cmdlet New-Cluster allow administrators to decide which organizational unit (OU) should contain the CNO.

There are basically two solution of this problem:

1. One resolution is a preventative action that can be done prior to beginning the installation of the SQL Server Failover Cluster, and
2. Second resolution is after the issue experienced during installation to be able to continue.
Both resolutions require access and permissions to AD.

Resolution
1. The resolution that will prevent this issue on future installations is to “pre-stage” the VCO.

- Log in as a user with permissions to create computer objects in the domain.
- Under Active Directory Users and Computers, create a “New Computer” object within the desired AD Container for the VCO (this will be your SQL Server Network Name).
- Once the object has been created, you can then add the CNO (this will be your WSFC Name) to the security of the VCO with “Full Control” over the VCO.

2. To achieve the resolution that will be reactive for your current errors, you need to grant the proper permissions to the CNO.

- Log in as a user with administrative permissions in the domain.
- Under Active Directory Users and Computers, grant the CNO (this will be your WSFC Name) “Create Computer Objects” permissions.

After doing this, you can retry your previously failed installation, and it should be successful. To verify if this issue was corrected, you can navigate within the SQL Server (MSSQLSERVER) Cluster group and attempt to bring the Server Name resource online.
If the resource is able to be brought online successfully and thats it!!

SQL Server AlwaysOn Concept

SQL Server AlwaysOn provides a high-availability and Disaster-recovery. It makes use of existing SQL Server features, particularly Failover Clustering, and provides new capabilities such as availability groups.

This has been designed to meet the ever-increasing need for ‘High Availability’ (HA). AlwaysOn does not use entirely new technologies but makes more effective use of existing technologies that are tried and tested. It aims to provide more granular control to achieve High Availability.

Some of these can take time and resources to implement, and may therefore not be meeting your current requirements. This is where SQL Server 2012 AlwaysOn can help, because it provides the benefits of:

• Using the WSFC APIs to perform failovers. Shared storage is not required
• Utilizing database mirroring for the data transfer over TCP/IP
• providing a combination of Synchronous and Asynchronous mirroring
• providing a logical grouping of similar databases via Availability Groups
• Creating up to four readable secondary replicas
• Allowing backups to be undertaken on a secondary replica
• Performing DBCC statements against a secondary replica
• Employing Built-in Compression & Encryption

Related components of AlwaysOn:

Windows Server Failover Clustering (WSFC)

Clustering technology has been around for quite some time, starting with Microsoft Clustering Services (MCS) back in NT 4.0 days.. The technology for WSFC is part of the backbone of AlwaysOn. A WSFC cluster is a group of independent servers that work together to increase the availability of applications and services. It does this by monitoring the health of the active node and failing over to a backup node, with automatic transfer of resource ownership, when problems are detected.
Although the WSFC is able to span multiple subnets, a SQL Server which is cluster-aware has not, until now, been able to support a clustered instance of SQL Server across multiple subnets: It has therefore been quite expensive to set up clustering across multiple data centres due to the WSFC requiring shared storage in both data centres as well as the block level SAN replication. This has required a lot of work with your storage vendors to get your setup correct.

Database Mirroring

Database mirroring gives you the ability to fully-synchronise databases from one instance of SQL Server to another, whether the second instance is located on the same server, a different server in the same data centre or to a server in another data centre. It can then switch roles with the mirrored database on failover. One of the problems with database mirroring is that it cannot automatically failover a group of databases that are inter-related. If you have several databases residing in an instance of SQL Server and one of those databases is failed over to the secondary location via your database mirroring setup, this database may be dependent on one or more of the other databases in the instance as well. In this case, your application may not operate correctly. Another downside is that the mirrored database is not accessible. You can get around this by using database snapshots to give you a ‘read only’ copy.

AlwaysOn Nodes

The nodes that you will use in your SQL Server 2012 AlwaysOn solution have to be part of a WSFC. The first step we need to undertake in preparing our AlwaysOn nodes is to add the Failover Cluster Feature to each node. I’ll go into detail later on in this article.

AlwaysOn Storage

SQL Server versions prior to SQL Server 2012, being setup as clustered instance on a WSFC require the storage to be presented as shared storage. This requirement leads to the storage being more expensive and a little bit more complicated to configure and administer. With SQL Server 2012 AlwaysOn your solution does not have to utilise shared storage, but can use SAN, DAS, NAS or Local Disk depending on your budget and requirements. I suggest working with your storage providers to come up with the solution you need.

Synchronous & Asynchronous Mirroring

AlwaysOn uses SQL Server’s existing mirroring technology. Synchronous Mirroring, as its name indicates, requires the transactions to be written at both sites for the transaction to be completed. Whereas this can lead to increased latency in your system, it gives you zero data loss. AlwaysOn will support up to two secondary replicas synchronously replicated per availability group. Asynchronous Mirroring, on the other hand, is faster but increases the risk of data loss as it does not have the requirement to complete the transaction at the secondary site. One advantage that AlwaysOn has over mirroring is that it allows multiple usable secondaries of the database. Another advantage is the ability to have a combination of Synchronous & Asynchronous Mirroring in your setup so as to reach the best compromise between performance and reliability. Depending on your HA/DR requirements you possibly could have a synchronous setup to a server in your local data centre and an asynchronous setup to a server in a secondary data centre.

Availability Groups

SQL Server 2012 AlwaysOn allows for the more granular control of your environment with the introduction of AlwaysOn Availability Groups (AAG’s). AAG’s allow you to configure groups of databases that you would like to failover all together when there is a problem with the host server. When configuring your AAG’s you:

• Configure your AAG on the Primary Replica (Your AAG contains the group of DBs that you wish to group together to failover to your secondary replicas)
• You will need to configure between one and four secondary replicas, with any combination of Synchronous (Maximum of two) and Asynchronous Mirroring (Your primary replica is available for read and write connectivity, while your secondary replicas can be configured for read-only, read intent or no access)

More in-depth information on Availability Groups is covered later. 

Maintenance Tasks/ Reporting

AlwaysOnallows you to use the secondary replicas that you would have created when you setup your AAGs to undertake some regular database maintenance tasks to remove some of the performance overheads from your primary production server. Some of the tasks that you could look at undertaking on a secondary replica are:

• Database Backups
  • Full Backup With Copy_Only
  • Transaction Log Backups
• DBCC CheckDB
• Reporting
• Database Snapshots 

Licensing

SQL Server 2012 has been released with a new license model. With SQL Server 2012 AlwaysOn's ability to have multiple secondaries you need to take into account the licensing when you are going to be implementing multiple secondaries. The license model requires you to license your Active (Primary) SQL Server in your AlwaysOn Cluster. You are allowed one Passive (Secondary) server that you do not need to license. If you have more than one secondary server, you need to license that server whether it is active or passive.  For more information on licensing have a look at the SQL Server 2012 Licensing Reference Guide and talk to your licensing reseller.
What is Active and what is Passive? Your Primary is active because you are accessing it and using the database. If you setup a secondary server to perform any of the tasks listed above in Maintenance Tasks/Reporting, then this secondary is also active and needs to be licensed. Remember; if you access it, it is active. For example: If we were to have one Primary Server (Active), three Secondary Servers (one Active, two Passive) we would be required to license three of the four servers.

Security & Performance

To give you the full benefits of high availability, there will be a lot of movement of data. This brings with it security risks and higher bandwidth demands. To minimise these requirements Transparent Database Encryption (TDE) as well as Backup Compression, are both shipped with the Enterprise Edition,

Implementing AlwaysOn

Now that we have covered off the basics of what an AlwaysOn solution could possibly look like we are ready to start and plan for implementing this solution to meet your ever increasing High-Availability requirements and DR needs.
Your final solution could look something like this:

SQL Server Failover Cluster Concept

Basic understanding of the concept of SQL Server Failover Clustering

What is a windows Cluster ?
A cluster is a group of independent computer systems, referred to as nodes, working together as a unified computing resource. When we say unified computing , it does not mean that a single application can access the resources (cpu/memory) of multiple computer at any point of time. A cluster provides a single name for clients to use it services.
There are two type of cluster:
Network Load Balancing Cluster : A Network Load Balancing Cluster (NLB) distribute the load (request from the client)  to the different nodes which are part of the cluster based on predefined rules.The  client application has to communicate to a single cluster IP address (virtual server)  and client does not know which node in the cluster exactly serving its request.Network Load Balancing cluster helps to enhance the the availability and scalability of application.
 
Fail over Cluster:  Failover cluster is a collection of servers that by working together increase the availability of applications and services that run on the cluster. It can be  described as a technology that automatically allows one physical server to take over the tasks and responsibilities of another physical server that has failed.A failover cluster provides high availability for cluster aware application like SQL server ,exchange server etc.
The major difference between NLB cluster and failover cluster is, failover cluster will not help to improve the scalability of the application. It can only support high availability.

A Microsoft SQL Server Cluster is nothing more than a collection of two or more physical servers with identical access to shared storage that provides the disk resources required to store the database files. 
These servers are referred to as "nodes".  Each of the nodes talk to one another via a private network, sending a heartbeat signal between them.  Should one node not communicate its heartbeat to the other node in the cluster the secondary node will take ownership of any dependent services being run by the node that lost communication.  This process is referred to as "failover". 
A failover can occur both automatically (a server's heartbeat stops communicating) or manually.  A manual failover is beneficial in the event that patching or some other form of maintenance is required at the physical server level.

There are many misunderstanding among people about the advantage of having SQL server on a failover cluster environment. Below table will give a clear picture about the capability of SQL server on cluster environment.




To understand the failover cluster it is important to familiar with the terminologies. Let us have a look on the terminologies used in the windows clustering.

Active/Active or Active/Passive Clustering
Clusters are often referred to as either Active/Active or Active/Passive.  Just as you would expect by the name, in an Active/Active cluster there will be two or more nodes, each one owning an instance of Microsoft SQL Server.  If one node fails, the instance it owns would fail over to the other node, running along side (and contending for resources with) the other instance.  An Active/Passive architecture  requires that no matter how many nodes make up the cluster, at least one node is not the owner of an instance of SQL Server.  It is "passive" and only exists to accept a failover of a node hosting a SQL instance in the event of a failover. 
Microsoft licensing policies require only license the active nodes running Microsoft SQL Server.  The passive node need not be licensed.

Server Node: Physical node with operating system that support windows clustering. Each server node should have minimum two network card for public and private network.Minimum one local hard disk is required for OS and other application binaries.
Private Storage: Local disks are referred as private storage. There will be minimum of one private disk for OS and SQL binaries.Server node can have a additional local disk for bigger page file.

Shared Disk Array: Each server needs to be attached to the shared external storage.In non-clustered SQL server instance, databases (system/user/resource) are store on locally attached disk storage but in clustered SQL server instances databases are store data on a shared disk array.That mean all the server nodes in the cluster setup are physically connected to the disk array.This shared storage configuration allow application to failover between server in the cluster.


Quorum: Quorum is a count of voting members— a quorum is a way of taking attendance of cluster members who are present. The cluster uses a quorum to determine who should be online.

Quorum Disk: Quorum is the cluster's configuration file.This file (quorum.log) resides in the the quorum disk (one disk from shared disk array).Quorum is the main interpreter between all nodes. It stores latest cluster configuration and resource data. This helps the other nodes to take ownership when one node goes down.
In real life, it is like emergency contact number and other medical information in your wallet which help others to take action in case of emergency.
Public Network and Private Network: Each server needs at least two network cards. Typically, one is the public network and the other is a private network between the nodes. The private network is used for internal communication of cluster.This is referred as heart beat. Public network is used to connect the external world or intranet.
Heart Beat: Heart beat is health check mechanism in cluster.  A single UDP packet sent between nodes in the cluster via the private network to confirm that  nodes are still online. By default cluster service will wait five seconds(one heart beat sent in every second)  before considering a cluster node to be unreachable.
Looks Alive check:Looks alive check is a basic resource health check to verify that the service(SQL service in our context) is running properly.To perform this , cluster service queries the windows service control manager to check the status of the service.By default looks alive check will happen in every five seconds.
Is Alive check: An exhaustive check to verify that a resource is running properly. If this check fails, the resource is moved offline and the failover process is triggered. During the Is alive check  the cluster service connects to the SQL server instance and execute select @@SERVERNAME.It will check only the SQL server instance availability and does not check the availability of user databases.
Failover: Clusters service monitor the health of the nodes in the cluster setup and the resources in the cluster. In the event of a server/resource failure, the cluster service  re-starts the failed server's workload on one of the remaining servers based on possible owners settings. The process of detecting failures and restarting the application on another server in the cluster is known as failover .
Virtual Server: Virtual server consist of a network name and IP address to which the client connect.This helps the client to connect the service  which hosted in the cluster environment with out knowing the details of the server node on which the service is running.In simple word , virtual server helps the client application to connect the SQL server  instance with out changing the connection string in case of failover of SQL instance.

Possible Owners: Possible owners are the server nodes on which cluster group (in our context SQL instance) can failover

Preferred Owner: Preferred owner is the server node which best suited to running an application or group.

Cluster Setup: Below schema diagram will give a clear picture about the windows cluster setup.

This picture depicts  two  node cluster setup.At this stage Node A and Node B will have only OS and windows cluster service.One of the disk from cluster disk will be designated as the quorum disk. This setup basically done by windows administrators and storage specialists.On top of this,  DBA's install  and configure the SQL instances. We are not going talk about the SQL server installation on cluster environment on this post.
To make it more clear, let us describe our cluster environment as given below.
Example of Active\Active Cluster:



Let us look into the pictorial representation current setup.

Fig 1

From the Fig-1, we can see that INST1 and INST2 are installed on both physical node , but at any point of time one instance will be online only in one node.At this stage INST1 is running on physical node Pnode-A and INST2 is running on node Pnode-B.The cluster disk D and E will be visible in the node Pnode-A and F and G will be visible in the node Pnode-B.The request from the App1 will be routed through the virtual server A and landed in Pnode-A where the INST1 is running.In the same way the request from the App2 will be routed through the virtual Server B and landed in Pnode-B where the INST2 is running.

Let us see what will happen if there is a hardware/network failure in Pnode-A. Fig 2 depicts the failover of  scenario of INST1 to Pnode-B.

Fig 2

Now the INST1 went offline in Pnode-A and came online Pnode-B.The connection from the App1 will be routed through the same virtual Server A and landed in Pnode-B.All four cluster disk will be visible in Pnode-B and only C (Local disk) will be accessible from Pnode-A. Note that even after the failover of INST1,App1 will be connecting to the virtual server A.It helps to make the failover transparent to the application.

SQL Server Database Mirroring Vs LogShipping

Database Mirroring Vs Log-Shipping

Communication mode
Database mirroring, principal and mirror server communicate through TCP\IP ports connection and write data change directly to remote transaction log file. Database mirroring process architecture is more robust and efficient than log shipping.
Log shipping always propagate changes from primary database asynchronously by backing up transaction log file, copying it across to target server through share directory, and restoring it at the target or secondary server. All of the processes are scheduled at predefined interval by SQL Jobs.

Database State
In database mirroring, Secondary database will be in restoring mode.
In Log-Shipping, Secondary database can be used as read-only mode.

Failover Process
DB mirroring can be automatic or manual failover process.
Log-shipping is manual failover process.

Recovery Model
DB mirroring supports full recovery model only.
Log shipping supports both bulk logged recovery model and full recovery model.

Operations
In database mirroring can handle alter database operations such as adding files, file groups etc.
Log shipping cannot handle database file operations, such as adding files etc.

Replica\Secondary Servers
Database mirroring will not have multiple database destinations for mirroring the principal database. It will have one mirror database synchronizes with principal database.
Log shipping will have multiple secondary databases for the synchronization.

SQL Server LogShipping

SQL Server log shipping (Ship the T-logs) is a technique which involves two or more SQL Server instances and copying of a transaction log file from one SQL Server instance to another. The process of transferring the transaction log files and restoring is automated across the SQL Servers. As the process result there are two copies of the data on two separate locations.

A log shipping session involves the following steps:

• Backing up the transaction log file on the primary SQL Server instance
• Copying the transaction log backup file across the network to one or more secondary SQL Server instances
• Restoring the transaction log backup file on the secondary SQL Server instances

Implementation examples

One of the common log shipping scenarios is the environment with two servers (SQLServer-1 – primary and SQLServer-2 – secondary), two SQL Server instances (SQLInstance-1 and SQLInstance-2), and one SQL Server database named SQLDB-1 with log shipping running on it.



Common SQL Server log shipping scenarios - the environment with two servers

Another common configuration is the environment with three (or more) servers (SQLServer-1 – primary, SQLServer-2 – secondary, and SQLServer-3 – secondary), three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3), and one SQL Server database named SQLDB-1 with log shipping running on it
 


SQL Server Log shipping scenarios - The environment with three (or more) servers
Operating modes

There are two available modes and they are related to the state in which the secondary, log shipped, SQL Server database will be:

•     Standby mode – the database is available for querying and users can access it, but in read-only mode
• The database is not available only while the restore process is running
• Users can be forced to disconnect when the restore job commence
• The restore job can be delayed until all users disconnect themselves
•     Restore mode – the database is not accessible

Advantages and disadvantages of using SQL Server log shipping

SQL Server log shipping is primarily used as a disaster recovery solution. Using SQL Server log shipping has multiple benefits: it’s reliable and tested in details, it’s relatively easy to set up and maintain, there is a possibility for failover between SQL Servers, data can be copied on more than one location etc.

Log shipping can be combined with other disaster recovery options such as AlwaysOn Availability Groups, database mirroring, and database replication. Also, SQL Server log shipping has low cost in human and server resources

The main disadvantages in the SQL Server log shipping technique are: need to manage all the databases separately, there isn’t possibility for an automatic failover, and secondary database isn’t fully readable while the restore process is running.

Requirements

• You need three SQL Servers or at least three SQL Server Instances (three instances can be used for testing purposes only, but it is not recommended for production environments). In this sample we will use 2 Servers.
• You can use SQL Server Enterprise, Standard, Business Intelligence or Web Edition for this article.
• Create a folder and assign permissions to the SQL Server Agent Account to this folder on the principal server. In this example, the folder name will be logshipping in the c:\ drive.
• Create a folder and assign permissions to the SQL Server Agent Account to this folder on the secondary server. In this example, the folder name will be destination in the c:\ drive.

SQL Server : HADR - High Availability and Disaster Recovery Options

Database high availability (HA) and disaster recovery (DR) are critical for any organization. There is essential work to be done to ensure that online services and applications always have access to data in order to operate.

- SQL Backup and Restore :

Database and log backups are the foundation of any HADR implementation. It’s the basic blocking and tackling of any database implementation. Every SQL Server should be backed up periodically in addition to any other HADR solutions you may have implemented. Backing up is important. But it’s really the ability to restore that important. Periodically restore selected databases to another system and run integrity checks on those databases. Document the process so that in a true disaster recovery scenario, you’ll have a proven protocol.

- SQL Server Log Shipping :

Log Shipping works by automatically sending the primary database log backups to one or more secondary server instances. It applies these log backups on the secondary databases to keep them up to date with the primary database. This process is similar to the MySQL binlog or the PostgreSQL Write Ahead Log (WAL). Additionally, you can deploy a monitor server to record the backup, restore history, and raise alerts if the secondary copy is behind the primary database with a predefined threshold.

- SQL Server Replication :

SQL Server replication is used to distribute filtered data among multiple target servers. It can synchronize data between databases while maintaining data consistency and integrity.
SQL Server supports the following replication types:
-- Snapshot Replication: Snapshot replication takes a snapshot of the entire database and applies it to the subscriber database. It is helpful for initial database synchronization or cases where your source database does not change frequently.

-- Transactional Replication: Transactional replication replicates continuous changes from the primary database (publisher) to the secondary database (subscriber) with minimal latency. It is one-way replication from publisher to subscriber databases.

-- Merge Replication: Merge replication can replicate data from a publisher to a subscriber database and vice versa. It is bidirectional replication typically used in the server to the client environment for synchronizing data across database servers when they cannot be connected continuously.

-- Peer-to-Peer Replication: Peer-to-Peer replication can replicate data to multiple subscribers distributed across the globe. Data can be changed in any database server and replicated to servers participating in Peer-to-Peer replication.

- Failover Clustered Instances (FCIs) :

Failover Clustered Instances takes your HADR game up several levels. Leveraging Windows Server Failover Clusters (WSFC), a single instance of SQL Server can be installed across multiple WSFC nodes. Should one of the nodes experience a hardware or software failure, key services such as SQL Server, are automatically shifted to another node.
There are two types of failover clustering:

-- Active-Passive Cluster: Active-Passive cluster configuration uses the secondary server in case of any failover. It is a popular choice since it does not affect performance. However, your passive server remains idle until resources are failed over to it.

-- Active-Active Cluster: The Active-Active cluster runs different SQL Services (instances) on both servers. Each server has its own SQL Service and storage. If any of the servers fail, the other server takes over and runs both SQL instances.

- SQL Server Always On Availability Groups :

With Always On Availability Groups, transaction logs are synchronized from the primary replica to one or more secondary replicas. Should the primary replica become unavailable, either due to a failure or scheduled maintenance, a secondary replica transitions to the primary role. Clients, which connect to a listener, are unaware of any changes to the actual location of the primary replica.
SQL Server Always On supports two modes.

-- Synchronous Data Commit: In synchronous mode, the transaction must commit on the secondary database followed by the primary database. It ensures that you always have a synchronized database copy without any difference between the primary and secondary databases. It is suitable for the critical database where you cannot afford any data loss.

-- Asynchronous Data Commit: In asynchronous commit mode, the primary database does not wait for acknowledgment from the secondary. It is suitable for optimized performance and disaster recovery solutions.