What is Database Snapshot in SQL Server? Examples and Demo of Database Snapshot

What is Database Snapshot in SQL Server? Explanation 

A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A database snapshot always resides on the same server instance as its source database. As the source database is updated, the database snapshot is updated. Therefore, the longer a database snapshot exists, the more likely it is to use up its available disk space.

Multiple snapshots can exist on a given source database. Each database snapshot persists until it is explicitly dropped by the database owner.

Database snapshots are unrelated to snapshot backups, snapshot isolation of transactions, or snapshot replication.

Benefits of Database Snapshots

• Snapshots can be used for reporting purposes.
  Clients can query a database snapshot, which makes it useful for writing reports based on the data at the time of snapshot creation.
• Maintaining historical data for report generation.
  A snapshot can extend user access to data from a particular point in time. For example, you can create a database snapshot at the end of a given time period (such as a financial quarter) for later reporting. You can then run end-of-period reports on the snapshot. If disk space permits, you can also maintain end-of-period snapshots indefinitely, allowing queries against the results from these periods; for example, to investigate organizational performance.
• Using a mirror database that you are maintaining for availability purposes to offload reporting.
  Using database snapshots with database mirroring permits you to make the data on the mirror server accessible for reporting. Additionally, running queries on the mirror database can free up resources on the principal. For more information, see Database Mirroring and Database Snapshots (SQL Server).
• Safeguarding data against administrative error.
• In the event of a user error on a source database, you can revert the source database to the state it was in when a given database snapshot was created. Data loss is confined to updates to the database since the snapshot's creation.
  For example, before doing major updates, such as a bulk update or a schema change, create a database snapshot on the database protects data. If you make a mistake, you can use the snapshot to recover by reverting the database to the snapshot. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.

  Important

  Reverting does not work in an offline or corrupted database. Therefore, taking regular backups and testing your restore plan are necessary to protect a database.

  Note

  Database snapshots are dependent on the source database. Therefore, using database snapshots for reverting a database is not a substitute for your backup and restore strategy. Performing all your scheduled backups remains essential. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.
• Safeguarding data against user error.
  By creating database snapshots on a regular basis, you can mitigate the impact of a major user error, such as a dropped table. For a high level of protection, you can create a series of database snapshots spanning enough time to recognize and respond to most user errors. For instance, you might maintain 6 to 12 rolling snapshots spanning a 24-hour interval, depending on your disk resources. Then, each time a new snapshot is created, the earliest snapshot can be deleted.
  • To recover from a user error, you can revert the database to the snapshot immediately before the error. Reverting is potentially much faster for this purpose than restoring from a backup; however, you cannot roll forward afterward.
  • Alternatively, you may be able to manually reconstruct a dropped table or other lost data from the information in a snapshot. For instance, you could bulk copy the data out of the snapshot into the database and manually merge the data back into the database.

  Note

  Your reasons for using database snapshots determine how many concurrent snapshots you need on a database, how frequently to create a new snapshot, and how long to keep it.
• Managing a test database
  In a testing environment, it can be useful when repeatedly running a test protocol for the database to contain identical data at the start of each round of testing. Before running the first round, an application developer or tester can create a database snapshot on the test database. After each test run, the database can be quickly returned to its prior state by reverting the database snapshot.

Prerequisites

The source database, which can use any recovery model, must meet the following prerequisites:

• The server instance must be running on an edition of SQL Server that supports database snapshots. For more information, see Features Supported by the Editions of SQL Server 2016.
• The source database must be online, unless the database is a mirror database within a database mirroring session.
• You can create a database snapshot on any primary or secondary database in an availability group. The replica role must be either PRIMARY or SECONDARY, not in the RESOLVING state.
  We recommend that the database synchronization state be SYNCHRONIZING or SYNCHRONIZED when you create a database snapshot. However, database snapshots can be created when the database synchronization state is NOT SYNCHRONIZING.
  For more information, see Database Snapshots with Always On Availability Groups (SQL Server).
• To create a database snapshot on a mirror database, the database must be in the SYNCHRONIZED mirroring state.
• The source database cannot be configured as a scalable shared database.
• The source database must not contain a MEMORY_OPTIMIZED_DATA filegroup. For more information

Limitations on the Source Database

As long as a database snapshot exists, the following limitations exist on the snapshot's source database:

• The database cannot be dropped, detached, or restored.

  Note

  Backing up the source database works normally; it is unaffected by database snapshots.
• Performance is reduced, due to increased I/O on the source database resulting from a copy-on-write operation to the snapshot every time a page is updated.
• Files cannot be dropped from the source database or from any snapshots.

Limitations on Database Snapshots

The following limitations apply to database snapshots:

• A database snapshot must be created and remain on the same server instance as the source database.
• Database snapshots always work on an entire database.
• Database snapshots are dependent on the source database and are not redundant storage. They do not protect against disk errors or other types of corruption. Therefore, using database snapshots for reverting a database is not a substitute for your backup and restore strategy. Performing all your scheduled backups remains essential. If you must restore the source database to the point in time at which you created a database snapshot, implement a backup policy that enables you to do that.
• When a page getting updated on the source database is pushed to a snapshot, if the snapshot runs out of disk space or encounters some other error, the snapshot becomes suspect and must be deleted.
• Snapshots are read-only. Since they are read only, they cannot be upgraded. Therefore, database snapshots are not expected to be viable after an upgrade.
• Snapshots of the model, master, and tempdb databases are prohibited.
• You cannot change any of the specifications of the database snapshot files.
• You cannot drop files from a database snapshot.
• You cannot back up or restore database snapshots.
• You cannot attach or detach database snapshots.
• You cannot create database snapshots on FAT32 file system or RAW partitions. The sparse files used by database snapshots are provided by the NTFS file system.
• Full-text indexing is not supported on database snapshots. Full-text catalogs are not propagated from the source database.
• A database snapshot inherits the security constraints of its source database at the time of snapshot creation. Because snapshots are read-only, inherited permissions cannot be changed and permission changes made to the source will not be reflected in existing snapshots.
• A snapshot always reflects the state of filegroups at the time of snapshot creation: online filegroups remain online, and offline filegroups remain offline. For more information, see "Database Snapshots with Offline Filegroups" later in this topic.
• If a source database becomes RECOVERY_PENDING, its database snapshots may become inaccessible. After the issue on the source database is resolved, however, its snapshots should become available again.
• Reverting is unsupported for any NTFS read-only or NTFS compressed files in the database. Attempts to revert a database containing either of these types of filegroups will fail.
• In a log shipping configuration, database snapshots can be created only on the primary database, not on a secondary database. If you switch roles between the primary server instance and a secondary server instance, you must drop all the database snapshots before you can set the primary database up as a secondary database.
• A database snapshot cannot be configured as a scalable shared database.
• FILESTREAM filegroups are not supported by database snapshots. If FILESTREAM filegroups exist in a source database, they are marked as offline in its database snapshots, and the database snapshots cannot be used for reverting the database.

  Note

  A SELECT statement that is executed on a database snapshot must not specify a FILESTREAM column; otherwise, the following error message will be returned: Could not continue scan with NOLOCK due to data movement.
• When statistics on a read-only snapshot are missing or stale, the Database Engine creates and maintains temporary statistics in tempdb. For more information, see Statistics.

Disk Space Requirements

Database snapshots consume disk space. If a database snapshot runs out of disk space, it is marked as suspect and must be dropped. (The source database, however, is not affected; actions on it continue normally.) Compared to a full copy of a database, however, snapshots are highly space efficient. A snapshot requires only enough storage for the pages that change during its lifetime. Generally, snapshots are kept for a limited time, so their size is not a major concern.

The longer you keep a snapshot, however, the more likely it is to use up available space. The maximum size to which a sparse file can grow is the size of the corresponding source database file at the time of the snapshot creation. If a database snapshot runs out of disk space, it must be deleted (dropped).

Note

Except for file space, a database snapshot consumes roughly as many resources as a database.

Database Snapshots with Offline Filegroups

Offline filegroups in the source database affect database snapshots when you try to do any of the following:

• Create a snapshot
  When a source database has one or more offline filegroups, snapshot creation succeeds with the filegroups offline. Sparse files are not created for the offline filegroups.
• Take a filegroup offline
  You can take a file offline in the source database. However, the filegroup remains online in database snapshots if it was online when the snapshot was created. If the queried data has changed since snapshot creation, the original data page will be accessible in the snapshot. However, queries that use the snapshot to access unmodified data in the filegroup are likely to fail with input/output (I/O) errors.
• Bring a filegroup online
  You cannot bring a filegroup online in a database that has any database snapshots. If a filegroup is offline at the time of snapshot creation or is taken offline while a database snapshot exists, the filegroup remains offline. This is because bringing a file back online involves restoring it, which is not possible if a database snapshot exists on the database.
• Revert the source database to the snapshot
  Reverting a source database to a database snapshot requires that all of the filegroups are online except for filegroups that were offline when the snapshot was created.

TSQL Script to Create Database Snapshot

CREATE DATABASE database_snapshot_name

ON

(

NAME =logical_file_name,

FILENAME ='os_file_name'

) [ ,...n ]

AS SNAPSHOT OF source_database_name

Create Database Snpshot_withdbName ON

(Name=MDFlogicalname,FileName='MDFlogicalfilelocation.ss')

AS snapshot of db_name

Here you can see  we have create DB snspshot on DBsnapshotest db

we can not update Snapshot



Snapshot file info

Can not Restore Database is having Snapshot with backup

Can not Drop database is having Snapshot

Now i am generating some records insert into database table

you can see DB only having some size increased not in Snapshot

you can see table info @ db

You can see table info @ snapshot which taken before insert on db

we cant restore because we should must choose as master on db dropdown

we can restore database from Snapshot

TSQL Script to Restore database from Snapshot

Restore database dbname From Database_Snapshot ='Snapshot_name'

after restored db is having snapshot table info see below

Database snapshot is a great feature that offers virtual read only consistent database copy. When we create the database snapshot in the live operational database, it takes a database point in time static view and Rollback all uncommitted transactions in the snapshot database so we will not be having any inconsistent data that is yet to be committed. Database snapshot always exists on the Source database server.

Database snapshot works on the pages (the fundamental unit of data storage in SQL server is the page). The disk space allocated to a data file in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL server reads or writes whole data pages.

Basically, it creates a sparse file and will be pointing to the original databases, written only in case of any insert, update and delete statement is in the source database.

Suppose there is no operation going into the server when we created a database snapshot so snapshot will look like

Source Database

Snapshot Database (Sparse File)

So we have the blank copy of the database here. Now, if any Read Operation occurs ,it will be pointing to the original database only as no page modification is done since snapshot creation.

Now suppose due to a DML operation (insert\update\delete) Page 2, 5, and 8 has been modified and user operation requires page 1,2, 4 and page 8 so below picture shows how the snapshot will be and how the read operation will be performed.

We can see here whichever page gets changed due to any operation, its original page (before modification) is copied into the sparse file (snapshot), so if user operation request to read the snapshot database it works in below ways

1. If a page is modified, read operation occurs on sparse File
2. If the page is not modified it still points to the source database page

This is the concept behind the snapshot, thus, its size is small compared to the original database but it all depends on the operation if there are too many operations in the database which gets page modified so the snapshot size will be kept increasing.

So whenever we create the snapshot following operation will be done

1. 1. It creates an empty file, i.e. sparse file for each source database data file
2. Uncommitted transactions are rolled back, thus having a consistent copy of the database
3. All dirty pages will be returned to the disk
4. The user can query the snapshot database now

Now let’s say there are many database pages being updated so now the snapshot will look like

Here we can see the as there are many operations being performed all pages shown above are modified, thus the snapshot size will be increased. We should always keep an eye on the growth of the snapshot database.

The best use of Database snapshot can be during production changes\ upgrades\releases. Normally, database backup is taken and if the database size is big it is really time and space consuming since we will have to keep the backup copy apart from our regular backups. So instead of backup we can create the Database snapshot which is really quick and can perform the release, and once we have verified the changes are good we can easily drop the snapshot and also in some cases we require to see the values before changes we can view that as well.

Database snapshot and High availability

Database snapshot works well with the log shipping and database mirroring too. Normally log shipping databases are maintained in the no recovery model means no database connections can be made and it will be useful only when failover is made.

We can create a database snapshot for log shipping secondary database and use the database to run the queries.

In database mirroring, mirror database is always in no recovery mode, whether it is synchronous and asynchronous mode. By creating snapshots of the mirror database, we can easily query the mirror database so it is quite a useful feature to offload and use it for reporting purposes.

But there is some overhead if we are using synchronous mirror mode. As in synchronous mode, transactions have to be committed on both Principal and mirror servers before marking it complete so for a snapshot as it requires original page, it will add an extra task as copy database page from the mirror database to mirror Snapshot.

Benefits of Database snapshot

1. A database snapshot is convenient, reliable, Read- only, point-in-time copy of the database.
2. It is very beneficial when doing critical updates to the database as if anything goes wrong database can be restored to that point without any hassle.
3. If we want to view database suppose at 7 pm, we can create database snapshot and easily query the database, however, without snapshot we have the option to take the database backup and restore using some different name but it requires considerable system resources especially disk space.
4. Sparse file (snapshot) is small and easy to create however the size of snapshot depends upon the amount of operation and page updated.
5. Multiple database snapshots are possible for a single database.
6. There is no dependency for the recovery model, it works with full, bulk-logged and simple also.
7. Snapshot creation is really quick, it takes a few seconds only for big databases too.

Limitations and cons of Database snapshot

1. A database snapshot can be created onto the source server only. It cannot be moved to another server
2. We can’t drop an Original Source Database as long as a referring snapshot exists in that database
3. It cannot be Backed up and also detach \attach doesn’t work with database snapshots
4. Snapshots can be created for only user databases, not for the master, model and msdb
5. If we revert to a snapshot log Chain will get broken, thus we have to take a full or differential backup to bridge the log chain sequence
6. Both the source DB and the snapshot will be unavailable when the actual reversion process is in progress
7. If the source database is unavailable or corrupted we cannot use the database snapshot to revert it to the original state
8. We cannot add new users for database since it is a read-only copy
9. There is no graphical user interface for creating and reverting back the snapshot, this need to be done from query only
10. There is some overhead for every DML operation as before operation page needs to be moved out
11. If the drive in which snapshot exists is out of space which causes any DML to fail the snapshot will be in suspect mode and non-recoverable
12. The full-text index is not available with a snapshot
13. It is available with enterprise edition only
14. Database files that were online during snapshot creation should be online during the snapshot revert also

Conditions where Database snapshot is not recommended

1. It does not substitute for high availability solution such as mirroring, log shipping etc.
2. Very high user based activity as it might have extra overhead
3. Replacement of database backup as it is fully dependent on the source database

How to revert a Database Snapshot to recover a SQL Server database

Here is a step by step process to create a snapshot, delete some data and then recover the database using a database snapshot.

Step 1 - Get Information About The Database

First we will check how many data files we have in our source database. Make sure to create a separate sparse file for each data file.

EXEC SP_HELPDB KPO_MASTER

Step 2 - Create New Database Snapshot

Since we have only have one data file, we will need to create the database snapshot with one sparse file. This command creates the database snapshot for the database.

CREATE DATABASE KPO_Master_DATABASESNAPSHOT_201206029PM
ON
(NAME =KPO_MASTER_DATA,
FILENAME = 'E:\DATABASESNAPSHOT\KPO_Master_DATABASESNAPSHOT_201206029PM.dbsnapshot')
AS SNAPSHOT OF KPO_Master

 Here we can see we have 5 rows in this table.

Step 3 - Delete Data From a Table

Now I will delete all rows from a table, so that we can revert the database snapshot to recover the deleted rows.

Delete all rows from this table.

USE KPO_Master
go
DELETE tbladmin

Let's again run a SELECT statement to see the table contents.  We can see we have no data in this table.

Step 4 - Check To See If Data Still Exists In Snapshot

As you can see, all rows from tbladmin table have been deleted from the source database in the query above. Now you can run the same SELECT statement on the database snapshot. Here you can see that all of the data which we have deleted in the source database still exists. This is what we will recover using the database snapshot.

Step 5 - Drop All Other Snapshots For Database

If you have multiple database snapshots created then you must first drop all unwanted database snapshots before reverting it to the source database. To test this step, create another database snapshot using Step 2. I have created a separate database snapshot at 12 PM named "KPO_Master_DATABASESNAPSHOT_2012060212PM".

Now suppose you have multiple database snapshots from multiple source databases on your SQL Server Instance. First you have to find the correct database snapshots for your source database. As you can see the database ID is 45 for our source database which we can get in Step 1. Run the below command to get a list of all database snapshots for your source database.

SELECT name,database_id,source_database_id FROM sys.databases
WHERE source_database_id=45

Here you can see that two database snapshots are created for our source database. We must drop the unwanted database snapshot. As we have to recover our database from the first database snapshot that was created before dropping all rows from the above table. To drop a database snapshot run a normal drop database statement using the database snapshot name as shown below.

DROP DATABASE KPO_Master_DATABASESNAPSHOT_2012060212PM

Step 6 - Restore Snapshot

The final step is to restore a database snapshot to our source database to recover all data.  It is a simple restore statement using the with DATABASE_SNAPSHOT option.

RESTORE DATABASE KPO_Master 
FROM DATABASE_SNAPSHOT='KPO_Master_DATABASESNAPSHOT_201206029PM'

Now you can run the SELECT statement again to check that the data was recovered.

Script:

TSQL Script to Restore database from Snapshot

Restore database dbname From Database_Snapshot ='Snapshot_name'

TSQL Script to Create Database Snapshot

CREATE DATABASE database_snapshot_name

ON

(

NAME =logical_file_name,

FILENAME ='os_file_name'

) [ ,...n ]

AS SNAPSHOT OF source_database_name

Create Database Snpshot_withdbName ON

(Name=MDFlogicalname,FileName='MDFlogicalfilelocation.ss')

AS snapshot of db_name

Example:

Create Database db1 ON

(Name=db1_data,FileName='C:/MSSQL/DATA/db1snapshot.ss')

AS snapshot of db_name

To know databasesnspshot dbinfo

SELECT name,database_id,source_database_id FROM sys.databases
WHERE source_database_id=id

Ref:

https://www.sqlshack.com/database-snapshot-in-sql-server/

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server

https://www.mssqltips.com/sqlservertip/2721/how-to-revert-a-database-snapshot-to-recover-a-sql-
server-database/

Step by Step How to Failover in Cluster and Apply Patches in Cluster and Active Passive and Active Active Cluster Explanation

Step by Step How to Failover in Cluster and Apply Patches in Cluster and Active Passive and Active Active Cluster Explanation

Cluster information

2 node Cluster Active-Passive Method

We have installed  one SQLServer  from New SQL Server Failover Cluster  @ SQLNODE01  

After completion on Failover cluster SQL Installation on SQLNODE01 

We launched SQL installation from SQLNODE03 and use 

add node to SQL failover cluster @ SQLNODE03

so only one SQL services are running on one node and other services will be in stop status it is called Active Passive Cluster method

Windows Cluster Name: WinClusterSQLTechi

Domain:SQLTechi.com

Roles:3 Clustered Roles ( 2 Alwayson and 1 Clustered SQL roles)

Nodes: 2 Node 

Node info: SQLNode01, SQLNode03

SQL Cluster Name: SQLTechiCluster

Roles information



Node information

SQLNode01 info:

SQLNode03 info: 

SQL Cluster information

SQL Cluster Services info NODE01

SQL Cluster Services info NODE03

Failover Cluster info from NODE03

Disk information:

Network information:

Automatic Failover:

sometime when hardware/network failures Cluster will automatically failed to possible node

How to failover Manually on Cluster SQL Services:

here you can see current owner node is SQLNODE03




Now we should go to Roles and right click on SQL cluster and choose Move and select Node/Possible Node

we can choose which node to SQL Services can run as see below

Note:

Here we can defined which is preferred owner

We can also whenever preferred node is ready we can mention to fail back and defined how much failover can happen from here

Apply Patches on SQL Server Active-Passive Cluster

We should need to first apply Patches on Passive Node (Because SQL Services already on stopped state)

Here you can see Current Owner node is SQLNODE03

so we can apply patches to SQLNODE01

Verify instance edition info

Verify SQL Services are stopped state

Step 1:
we need to copy patch setup file to appropriate node (our case NODE01)

Step 2:

Right Click and run as admin in setup file of SP3 2012

Step 3 :

Complete step by step wizard

now we have successfully applied Service Pack 3 on SQL 2012 @ SQLNODE01 (passive Node)

Now we need to failover to SQLNODE01 from SQLNODE03 to apply service pack on SQLNODE03 (NEW Passive)

Now we have successfully failed over to SQLNODE01 From SQLNODE03

we can verify it SP3 upgraded to SQLNODE01

We now ready to apply service pack on new passive Node (SQLNODE03)

You can see services stopped state on SQLNODE03

Now applying SQL2012 SP3 setup file  run as admin @ SQLNODE03 (New Passive Node)

after failed over to SQLNODE03 We can SSMS to see Edition info upgraded to SP3 SQL2012 From

SQL2012 RTM

Active Active Cluster

We are going to Install another SQL Server Services and convert this Active Passive to Active Active Cluster


Current Mode (Active Passive) we can failover to any node like SQLNODE01/03

         SQLNODE01                                                              SQLNODE03

 VSQL failover Installed                                          added failover node to SQLNODE01


we are planning to install another  SQL Installation VSQL1 on SQLNODE03 and add to

SQLNODE01 (Active Active)

         SQLNODE01                                                              SQLNODE03

Will add failover node to SQLNODE03                                  VSQL1 failover will Install


Starting Installation on SQLNODE03 for SQL 2014

We have installed SQL 2014 on SQLNODE03 with failover cluster installation

now we need to add node to this cluster on SQLNODE01

DONE . We  have successfully added SQLNODE01 node to cluster

now failover cluster manager shows active active node as see below

SQL Services info @ SQLNODE03
here VSQL2014 is installed on SQLNODE03 and it is currently active owner node it will be in running status see below

SQL Services info @ SQLNODE01

here VSQL is installed on SQLNODE01 and currently it is owner node so it will be in running status see below

login via SSMS SQLNODE03

SQL EDITION information @ sqlnode03 and @sqlnode01

Trying with alwaysON 

We need to enable alwayson availability group on services of SQL before we do here

After we failed over all VSQL & Vsql2014 to SQLNODE03 it looks like below

VSQL2014 Installed SQL 2014 (sqlnode03) and SQLNODE03 Installed SQL 2012

VSQL2014 Installed SQL 2014 (sqlnode03) and VSQL Installed SQL 2012

When we are trying SQLNODE01

Checking for compatibility of the database file location on the secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)
The following folder locations do not exist on the server instance that hosts secondary replica Node1\AGTest: S:\MSSQL11.AG1\SQL_DATA; Microsoft.SqlServer.Management.HadrTasks)
– This error occurs if the drive letters or the folder path does not match between primary and secondary replica.
– To resolve this error, make sure that same drive letter and folder path exists on both the servers or perform manual synchronization of secondary server databases using backup/restore.

Failed to create, join or add replica to availability group ‘AGTest’, because node ‘Node1’ is a possible owner for both replica ‘AGTest\AGTest’ and ‘Node1\AGTest1’. If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again. (Microsoft SQL Server, Error: 19405)
– This error occurs, if SQL Server instance where you are setting up AlwaysON Availability Group is part of SQL Server failover clustering and has both nodes as possible owners and then You tried to add another availability Group for another SQL cluster instance involving these nodes.
– To resolve this error, either follow the solution suggested in the error message “If one replica is failover cluster instance, remove the overlapped node from its possible owners and try again” or instead of using cluster SQL instances, just use standalone instances.

Error while trying to connect to AlwaysON Availability group using Listener Name
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)
– This error occurs if the port number of the Listener is not 1433. To resolve this issue either use the port number along with the listener name like SQLAGLSTNR,1467 or create an alias.
– Make sure listener port is open to communicate. Test using telnet listenername portnumber
– Communication only works when using TCP network protocol.

Database Mirroring login attempt by user ‘Domain\ComputerName$.’ failed with error:
‘Connection handshake failed. The login ‘Domain\ComputerName$’ does not have CONNECT permission on the endpoint. State 84.’
– This error will be logged in SQL errorlog when trying to setup AlwaysON or Database Mirroring. This error occurs if the SQL Server services are running under local system accounts or if the SQL Service account does not have connect permission on the endpoint.
– To resolve the error, Change the SQL Server services to run under a domain account and then grant connect permission on endpoint to SQL Server service account.
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\SQLServiceAccount]
GO

Summary for the replica hosted by ServerName\INSTANCE2 Replica mode: Asynchronous commit
This replica will use asynchronous-commit availability mode and support only forced failover
(with possible data loss).
Note: This is a Failover Cluster Instance. Failover Cluster Instances do not support AlwaysOn automatic failover.
– This error occurs, if you are using SQL clustered instances to setup AlwaysON Availability Group.
– Cluster SQL Server instance will not support the automatic failover in availability groups. If you want automatic failover, install SQL server instance as standalone instance.

Cluster network name resource ‘TestAG’ failed to create its associated computer object in domain ‘testdomain.com’ during: Resource online.The text for the associated error code is: A constraint violation occurred.Please work with your domain administrator to ensure that:
The cluster identity ‘SQLClus$’ has Create Computer Objects permissions. By default all computer objects are created in the same container as the cluster identity ‘SQLClus$’.The quota for computer objects has not been reached. If there is an existing computer object, verify the Cluster Identity ‘SQLClus$’ has ‘Full Control’ permission to that computer object using the Active Directory Users and Computers tool.
– This error occurs if Cluster Name Object does not have rights to create a new Virtual Network Name Object. Refer below article for instructions to prestage the Virtual Name Object.
http://technet.microsoft.com/en-us/library/dn466519.aspx

Joining database on secondary replica resulted in an error.
(Microsoft.SqlServer.Management.HadrTasks)
Failed to join the database ‘AGTest’ to the availability group ‘AGGrpTest′ on the
availability replica ‘Replica2′. (Microsoft.SqlServer.Smo)
The connection to the primary replica is not active. The command cannot be processed.
(Microsoft SQL Server, Error: 35250 Level 16, State 7)
– Try below steps to fix the error
o Make sure that the alwaysON endpoint [Hadr_endpoint] is not blocked by firewall
o Verify and make sure that SQL Server service account of primary server is added as a login on all the secondary servers and vice-versa.
o If SQL Server service accunt is “Nt service\” or local system account then ensure system account (Domainname\systemname$) of each replica is added as a login to other replicas.
CREATE LOGIN Domain\replica2$] FROM WINDOWS
o Grant connect permission on alwaysON endpoints on each replicas for SQL Server service account of all other replicas On Secondary replica run below query by changing the domain and replica server names
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\replica1$]
On primary replica run below query by changing the domain and replica server names
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\replica2$]
o Make sure SQL Server name (select @@servername) matches with hostname of the server.
o Make sure cluster service startup account is added as SQL Server login.

Error while trying to setup AlwaysOn availability group
Operating System Error 1265(The system cannot contact a domain controller to service the authentication request. Please try again later.).
– This error can occur if the password of inter-domain trust account is not synchronized on both sides of the trust relationship.
– Refer below link to see if it helps fixing the error, else engage your domain Administrator.
http://support.microsoft.com/kb/816577/en-us

This is applicable on below versions of SQL Server

SQL Server 2012
SQL Server 2014

Possible owners Windows 2008 R2 Cluster

In the Microsoft advice for applying a SQL Server 2008 R2 service pack on a cluster, it states that it is very important to “remove nodes from the possible owner nodes list in the failover cluster instance.”   This was frustrating, because I didn’t know what that meant.  Plus, I had trouble finding this in the Cluster Manager tool in Windows Server 2008 R2.

One problem I have in understanding this was that there are many possible services where you can remove Possible Owners.  I chose to only do this on the cluster name, but maybe you would want to remove possible owners on the SQL Server service.  Some more clarity is needed in the Microsoft documentation (obviously, otherwise I wouldn’t have had to write this blog post).

Eventually I poked around enough to find the Possible Owners check boxes.  Under services and applications, navigate to Services and Applications, then SQL Server (MSSQLSERVER).

In the right-hand pane, right click on the server name, and select Properties.

Go to the Advanced Policies tab, and remove the possible owner, according to the instructions from Microsoft.     Continue to follow the directions from Microsoft to install the service pack.

Preferred Owners are a different thing, and that is done in the left pane by right-clicking on SQL Server (MSSQLSERVER), then the preferred owners check boxes will show up on the General tab.

From Windows Server 2012 to get possible owners like below

go to properties and check advanced policies and choose possible owners

Preferred Owner from Windows Server 2012:

Cluster Steps:

Disable firewall on all 4 (clusterDC,node1,2,3)

disable UAC on all 4

Add.net framework 3.5 on all 4

Add failover cluster feature on all 3 servers(node1,2,3)

update windows software

set timezone on all 4 (time zone should be same for all)

Need to change Computer name

Add application role on all 3 servers (node1,2,3)

Create Cluadmin on all 3

add Cluadmin to admin group

add IPADDRESS on all 4

Add additional adapter for Node1,2,3

we need to restart all server which changed ipaddress

add features on DC to add ACTIVE DOMAIN SERVICES

PROMOTE  add domain userpc.local

Logged in with Domain\administrator ON DC

In DC set reverse lookup zone

join all 3 nodes to DC domain

Restart all 3 nodes

add cluadmin as domain controller

use active directory users and groups

Logged in with Domain\cluadmin on all 3

all 3 each nodes use iscsi initiator to use SHARE STORAGE
Tools-->iscsi initiator
use computer management --> disk managementon
all 3 nodes each to bring db online
drive letters to be same all 3 nodes (ISCSI drives should be same on node 1,2,3)
node1
create windows cluster from failover wizard
we need to give cluseripaddress
configure quorum


Sample IP Details:

DC Domain Controller

NODE01

NODE02

You cannot add a domain controller as a node in a Windows Server 2012 failover cluster environment

Ref:

https://support.microsoft.com/en-in/help/2795523/you-cannot-add-a-domain-controller-as-a-node-in-a-windows-server-2012

When you build a Windows Server 2012 failover cluster environment, you cannot add a server that has the Active Directory Domain Services (AD DS) role as a node.

We do not support combining the AD DS role and the failover cluster feature in Windows Server 2012.

Although we do not recommend this, you can enable domain controllers as a cluster node in Windows Server versions earlier than Windows Server 2012. However, starting with Windows Server 2012, we no longer support this configuration.