Database Mirroring will be suspended and will get error Cannot find server certificate with thumbprint '0xDEABEBD2A72BB96EDA2521AB16011FD4E1F661DF'.

Issue:-

Database Mirroring will be suspended and will get error Cannot find server certificate with thumbprint '0xDEABEBD2A72BB96EDA2521AB16011FD4E1F661DF'.

Merge Replication Fail with error : The insert failed. It conflicted with an identity range check constraint in database 'DataBase Name', replicated table 'TableName', column 'ColumnName'. If the identity column is automatically managed by replication, update the range as follows: for the Publisher, execute sp_adjustpublisheridentityrange; for the Subscriber, run the Distribution Agent or the Merge Agent.

Issue:-

sp_adjustpublisheridentityrange fail to increase the Range identity

---------------------------------------------------------------------------------------------------------

SQL Server Service will not restart and will get Error Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25.

Issue:-

015-06-12 21:37:54.32 spid5s      Script level upgrade for database 'master' failed because upgrade step 'msdb110_upgrade.sql' encountered error 200, state 7, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

2015-06-12 21:37:54.32 spid5s      Error: 3417, Severity: 21, State: 3.

2015-06-12 21:37:54.32 spid5s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.

2015-06-12 21:37:54.33 spid5s      SQL Server shutdown has been initiated

2015-06-12 21:37:54.33 spid5s      SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

Cannot close event log because there are still event handle users active.

The issue in previous Errorlog file. 

2015-06-12 18:34:49.17 spid7s      Uploading data collector package from disk: D:\MSSQL11.MSSQLSERVER\MSSQL\Install\SqlTraceCollect.dtsx

2015-06-12 18:34:49.17 spid7s      Uploading data collector package from disk: D:\MSSQL11.MSSQLSERVER\MSSQL\Install\SqlTraceCollect.dtsx

2015-06-12 18:34:49.17 spid7s      Error: 4860, Severity: 16, State: 1.

2015-06-12 18:34:49.17 spid7s      Cannot bulk load. The file "D:\MSSQL11.MSSQLSERVER\MSSQL\Install\SqlTraceCollect.dtsx" does not exist.

2015-06-12 18:34:49.17 spid7s      Error: 912, Severity: 21, State: 2.



The Problem is SQL Server generate more genereic error, and we have tendency to focus on generic error only, and we ignore actual error.




Here in this case the actual error was

2015-06-12 18:34:49.17 spid7s Uploading data collector package from disk: D:\MSSQL11.MSSQLSERVER\MSSQL\Install\SqlTraceCollect.dtsx

where as DBA concentrate more one error highlighted in green.

Resolution:-

Actually, The error highlighted in green is a generic & Misguided error.

The Actual error is highlighted in Red*

Actually, "X:\MSSQL11.MSSQLSERVER\MSSQL\Install \" should contain a list of dtsx files besides *.sql and *.cer

PerfCountersCollect.dtsx -- Missing file from the upgrade logs

PerfCountersUpload.dtsx -- Missing file from the upgrade logs

QueryActivityCollect.dtsx -- Missing file from the upgrade logs

QueryActivityUpload.dtsx -- Missing file from the upgrade logs

SqlTraceCollect.dtsx        -- Missing file from the upgrade logs

SqlTraceUpload.dtsx     --- Missing file from the upgrade logs

TSQLQueryCollect.dtsx -- Missing file from the upgrade logs

TSQLQueryUpload.dtsx -- Missing file from the upgrade logs

Resolution:-

In order to fix this issue, you have to  copy  all these files from same version of another SQL Server and then paste in the location X:\MSSQL11.MSSQLSERVER\MSSQL\Install.

then  restarted the services, the SQl Server service will  came up.

Thanks for Reading and hope this will help someone who have facec this issue.

Do not forget to comment, irrespective of Issue gets resolve or not.  

Enable TDE for a database and how to restore TDE Enabled Database on other database server

Step by Step Enable TDE for a database and how to restore TDE Enabled Database on other database servers

---------------------------------------------------------------------------------------------------

For Theory refer this link 

-------------------------------

In Server 1:

USE Master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';

GO

CREATE CERTIFICATE TestSQLServerCert

WITH SUBJECT = 'Certificate to protect TDE key',
EXPIRY_DATE = '2030-04-23';

GO

-- Create a database to be protected by TDE.

CREATE DATABASE CustRecords;

GO

-- Switch to the new database.

-- Create a database encryption key, that is protected by the server certificate in the master database.

-- Alter the new database to encrypt the database using TDE.

USE CustRecords;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;

GO

you will receive this warning..

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database

Hence, we will take backup of that certificate in below steps

-- Create a backup of the server certificate in the master database.

-- The following code stores the backup of the certificate and the private key file in the default data location for this instance of SQL Server

-- (C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA).

Use master
go

BACKUP CERTIFICATE TestSQLServerCert

TO FILE = 'C:\SQLCert\SQLServerCertificate'

WITH PRIVATE KEY

(

    FILE = 'C:\SQLCert\SQLServerPrivateKeyFile',

    ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1'

);

GO
Then execute above command again to remove those warning..USE CustRecords;

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE TestSQLServerCert;

GO

use CustRecords
go

ALTER DATABASE CustRecords SET ENCRYPTION ON;

GO

/* Now Verifying TDE is enabled for which database and what is status.. */
At any point, you can use the sys.dm_database_encryption_keys DMV to determine which databases are currently encrypted (or in progress of being encrypted) on your server.
This DMV also gives you the status of the encryption process of newly database.

USE master;

GO

SELECT

    db.name,

    db.is_encrypted,

    dm.encryption_state,

    dm.percent_complete,

    dm.key_algorithm,

    dm.key_length

FROM

    sys.databases db

    LEFT OUTER JOIN sys.dm_database_encryption_keys dm

        ON db.database_id = dm.database_id;

GO

or


SELECT DB_NAME(database_id) as database_name,
database_id,
CASE encryption_state
  when 0 Then N'No database encryption key present, no encryption'
  When 1 Then N'Unencrypted'
  When 2 Then N'Encryption in progress'
  When 3 Then 'Encrypted'
  When 4 Then 'Key change in progress'
  When 5 Then 'Decryption in Progress'
END as encryption_state,
key_algorithm,
key_length,
Percent_complete
from sys.dm_database_encryption_keys;

/*
select *
from  sys.certificates
inner join sys.key_encryptions
on sys.key_encryptions.thumbprint = sys.certificates.thumbprint
where (sys.certificates.[name] = '<Certificate Name>')
*/

use master;
go

select * from sys.certificates
go

select @@SERVERNAME,database_name = d.name,
cert_name = c.name    --dek.encryptor_type,
from sys.dm_database_encryption_keys dek
left join sys.certificates c
on dek.encryptor_thumbprint = c.thumbprint
inner join sys.databases d
on dek.database_id = d.database_id;
      
go                          

SELECT db_name(database_id),encryption_state,
case encryption_state
when 3 then '3=Encrypted'
end 'encryption_state',
encryptor_thumbprint, encryptor_type, percent_complete
FROM sys.dm_database_encryption_keys 


--Output will show -- > encryption_state is 3=Encrypted for <Database>


After successful execution, you will find 2 files in this location (C:\SQLCert)

 Detach the TDE protected database from the source server. (optional) if you want then.. 

USE Master ;

GO
EXEC master.dbo.sp_detach_db @dbname = N'CustRecords';

GO

In Server-2
----------------
when you will try to restore TDE enabled database in 2nd server,will get below error..

Msg 33111, Level 16, State 3, Line 4
Cannot find server certificate with thumbprint '0x9A16334765A95CD7E1E3A3A7D7CE61BD73385F3A'.
Msg 3013, Level 16, State 1, Line 4

RESTORE DATABASE is terminating abnormally.

Hence

Create a database master key on the destination instance of SQL Server. For more information, see Using Transact-SQL below.

Recreate the server certificate by using the original server certificate backup file. For more information, see Using Transact-SQL below.

-- Move or copy the database files from the source server to the same location on the destination server.

-- Move or copy the backup of the server certificate and the private key file from the source server to the same location on the destination server.

-- Create a database master key on the destination instance of SQL Server.

 USE master;

   GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '*rt@40(FL&dasl1';

GO

--- After creating a Master key when i tried to restore the database again, got below error..

Msg 33111, Level 16, State 3, Line 4
Cannot find server certificate with thumbprint '0x9A16334765A95CD7E1E3A3A7D7CE61BD73385F3A'.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
Msg 33111, Level 16, State 3, Line 8
Cannot find server certificate with thumbprint '0x9A16334765A95CD7E1E3A3A7D7CE61BD73385F3A'.
Msg 3013, Level 16, State 1, Line 8
RESTORE FILELIST is terminating abnormally.

-- Recreate the server certificate by using the original server certificate backup file.

-- The password must be the same as the password that was used when the backup was created.

CREATE CERTIFICATE TestSQLServerCert

FROM FILE = 'C:\SQLCert\SQLServerCertificate'

WITH PRIVATE KEY

(

    FILE = 'C:\SQLCert\SQLServerPrivateKeyFile',

    DECRYPTION BY PASSWORD = '*rt@40(FL&dasl1'

);

GO

-- Attach the database that is being moved.

-- The path of the database files must be the location where you have stored the database files.

CREATE DATABASE [CustRecords] ON

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustRecords.mdf' ),

( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustRecords_log.LDF' )

FOR ATTACH ;

GO

Thanks for Reading..

other Interesting link on this topic:-

https://mattsql.wordpress.com/2012/11/13/migrating-sql-server-databases-that-use-database-master-keys/

Link for Understanding Encryption Hierarchy is written in this blog link..

http://kushagrarakesh.blogspot.com/2015/09/there-is-already-master-key-in-database.html

Disk resource 'Cluster Disk X' cannot be used as a failover cluster disk.disk resource 'Cluster Disk X' is already in use by resource 'New Distributed Transaction Coordinator'

The specified disk resource 'Cluster Disk 10' cannot be used as a failover cluster disk.  Reason: The disk resource 'Cluster Disk 10' is already in use by resource 'New Distributed Transaction Coordinator (1)'. To use a disk in a new SQL Server failover cluster instance, the disk must not have any dependencies that reference it.
----------------------------------------------------------------------------------
The SQL Server failover cluster instance name 'XXXXXXSQLXXXX' already exists as a clustered resource.  Specify a different failover cluster instance name.
--------------------------------------------------------------------------------------------------------------------------

Error: - when you un-install SQL Server  MSDTC or any SQL server resource, sometimes do not clean neatly, even after remove node wizard executes successfully, and at a time of SQL Server Installation, will notice below error.. 

The specified disk resource 'Cluster Disk 1' cannot be used as a failover cluster disk.  Reason: The disk resource 'Cluster Disk X' is already in use by resource 'New Distributed Transaction Coordinator (1)'. To use a disk in a new SQL Server failover cluster instance, the disk must not have any dependencies that reference it.

Error in details.txt

(01) 2015-08-21 12:11:47 Slp: Inner exceptions are being indented
(01) 2015-08-21 12:11:47 Slp: 
(01) 2015-08-21 12:11:47 Slp: Exception type: Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException
(01) 2015-08-21 12:11:47 Slp:     Message: 
(01) 2015-08-21 12:11:47 Slp:         The specified disk resource 'Cluster Disk 1' cannot be used as a failover cluster disk.  Reason: The disk resource 'Cluster Disk 10' is already in use by resource 'New Distributed Transaction Coordinator (1)'. To use a disk in a new SQL Server failover cluster instance, the disk must not have any dependencies that reference it.
(01) 2015-08-21 12:11:47 Slp:     HResult : 0x84b40002
(01) 2015-08-21 12:11:47 Slp:         FacilityCode : 1204 (4b4)
(01) 2015-08-21 12:11:47 Slp:         ErrorCode : 2 (0002)
(01) 2015-08-21 12:11:47 Slp:     Data: 
(01) 2015-08-21 12:11:47 Slp:       SQL.Setup.FailureCategory = InputSettingValidationFailure
(01) 2015-08-21 12:11:47 Slp:       DisableWatson = true
(01) 2015-08-21 12:11:47 Slp:     Stack: 

And

(01) 2015-08-21 13:03:31 Slp: Inner exceptions are being indented
(01) 2015-08-21 13:03:31 Slp: 
(01) 2015-08-21 13:03:31 Slp: Exception type: Microsoft.SqlServer.Chainer.Infrastructure.InputSettingValidationException
(01) 2015-08-21 13:03:31 Slp:     Message: 
(01) 2015-08-21 13:03:31 Slp:         The SQL Server failover cluster instance name 'XXXXXXSQLXXXX' already exists as a clustered resource.  Specify a different failover cluster instance name.
(01) 2015-08-21 13:03:31 Slp:     HResult : 0x84b40002
(01) 2015-08-21 13:03:31 Slp:         FacilityCode : 1204 (4b4)
(01) 2015-08-21 13:03:31 Slp:         ErrorCode : 2 (0002)
(01) 2015-08-21 13:03:31 Slp:     Data: 
(01) 2015-08-21 13:03:31 Slp:       SQL.Setup.FailureCategory = InputSettingValidationFailure
(01) 2015-08-21 13:03:31 Slp:       DisableWatson = true
(01) 2015-08-21 13:03:31 Slp:     Stack: 
(01) 2015-08-21 13:03:31 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.InputSettingService.LogAllValidationErrorsAndThrowFirstOne(ValidationState vs)
(01) 2015-08-21 13:03:31 Slp:         at Microsoft.SqlServer.Configuration.SetupExtension.ValidateFeatureSettingsAction.ExecuteAction(String actionId)
(01) 2015-08-21 13:03:31 Slp:         at Microsoft.SqlServer.Chainer.Infrastructure.Action.Execute(String actionId, TextWriter errorStream)
(01) 2015-08-21 13:03:31 Slp:         at Microsoft.SqlServer.Setup.Chainer.Workflow.ActionInvocation.ExecuteActionHelper(TextWriter statusStream, ISequencedAction actionToRun, ServiceContainer context)
(01) 2015-08-21 13:03:31 Slp: Collecting Cluster Logs:

Issue:-

While Installing SQl Server in Failover Cluster Environment, gets below error:-

The specified disk resource 'Cluster Disk X' cannot be used as a failover cluster disk.  Reason: The disk resource 'Cluster Disk X' is already in use by resource 'New Distributed Transaction Coordinator (1)'. To use a disk in a new SQL Server failover cluster instance, the disk must not have any dependencies that reference it.


Problem:-

Installation of SQL Server will gets fail. you will not find  any resource  with name "'New Distributed Transaction Coordinator (1)"  using  graphical mode of cluadmin.msc

so what you will do? How we will resolve this Error?

if you will search this in registry, with string "New Distributed Transaction Coordinator (1)", chances are  you will find this in "HKEY_LOCAL_MACHINE\Cluster\Resources"

Do not delete this key from here.

Resolution:-

 go to command prompt

run 
C:\> Cluster resource

  

You will get a resource with name "New Distributed Transaction Coorinator (1) as a resource

then
Run this command..

C:\>Cluster resource "New Distributed Transaction Coordinator (1)" /delete

After execution of this command, This offline resource will get deleted.

Now you can install SQL Server, SQL Server in cluster will get install successfully.


Thanks for reading..