Availability Group – SQL Server Agent Job to run only on Always On Primary Replica

-> I had a requirement to execute jobs only in primary replica.

-> To all jobs that should only be executed on Always On primary replica. I added 1 step as the first step of the job and another step as last step of that job.

-> 1st step of these jobs should execute below TSQL Query,

if (select
ars.role_desc
from sys.dm_hadr_availability_replica_states ars
inner join sys.availability_groups ag
on ars.group_id = ag.group_id
where ag.name = ''
and ars.is_local = 1) = 'PRIMARY'
begin
Print 'This is Primary Replica'
end
else
begin
RAISERROR('This job should not run in Secondary',16,1)
end

-> For 1st step,

On success action : Go to the next step
On failure action: Go to step: [n] Last_Step ( It should basically go to the last step)

-> Last step of the job should execute the below TSQL,

print 'Job_Completion'

-> For Last step,

On success action : Quit the job reporting Success
On failure action: Quit the job reporting failure

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

Advertisements

RESTORE detected an error on page (0:0) in database “Database” as read from the backup set.

-> I was restoring a database backup using SSMS.

-> I got below error,

1.Restore_Error.PNG

TITLE: Microsoft SQL Server Management Studio
——————————
Restore of database ‘JBS_DB’ failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
——————————
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: RESTORE detected an error on page (0:0) in database “JBS_DB” as read from the backup set. (Microsoft.SqlServer.SmoExtended)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18131.0+((SSMS_Rel).190606-1032)&LinkId=20476
——————————
BUTTONS:
OK
——————————

-> Executing a restore verify only command as below,

restore verifyonly from disk ='C:\Temp\JBS_DB_10July2019.bak'

-> Received below message,

 

2.Restore_Verify.PNG
Msg 3203, Level 16, State 1, Line 1
Read on “C:\Temp\JBS_DB_10July2019.bak” failed: 13(The data is invalid.)
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

-> It seems like the backup file is corrupted. Performed another backup on the database and tried a restore again and it failed with same error.

-> May be the database itself is corrupted. Executed a DBCC Checkdb on the database where backup was performed. It came out clean. So this is not a corruption issue.

-> Looking closely at the error message. The error is on page 0:0. In reality, there is no page 0:0 in a database.

-> Spoke to the application team handling the database and understood that they are using a Third party tool to encrypt the database. After discussion, I advised them to decrypt the database using the tool.

-> I performed a backup and performed a restore. This time restore worked fine.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.

 

Remove Transparent Data Encryption (TDE)

-> I had a request to remove TDE on a SQL Server Instance.

-> Execute below query and check what databases are encrypted on the SQL Server instance,

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete,encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys

0.Cert_List.PNG

-> From the above screenshot, it is clear that database JB_DB and JBS_DB are encrypted with different certificate. Database JBS_App_DB is encrypted with a different certificate. Check the “encryptor_thumbprint” for the difference.

-> Execute below query to find the name of the certificates,

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete,b.name as Certificate, encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys a INNER JOIN sys.certificates b ON a.encryptor_thumbprint=b.thumbprint

0_1.Cert_List.PNG

-> Checking the certificates from SSMS,

0_2.Cert_List_SSMS.PNG
-> Before removing TDE, I made a backup of the certificate using below TSQL,

USE master;
GO
BACKUP CERTIFICATE JBS_TDE_Certificate
TO FILE = 'C:\temp\JBS_TDE_Certificate.cer'
WITH PRIVATE KEY (file='C:\temp\JBS_TDE_Certificate.pvk',
ENCRYPTION BY PASSWORD='Pa$$w0rd');

BACKUP CERTIFICATE JBS_PROD_Certificate
TO FILE = 'C:\temp\JBS_PROD_Certificate.cer'
WITH PRIVATE KEY (file='C:\temp\JBS_PROD_Certificate.pvk',
ENCRYPTION BY PASSWORD='Pa$$w0rd');

Removing TDE the wrong way

-> I tried removing the master key using below Tsql,

use master
go
DROP MASTER KEY

-> Received below error,

1.Error_master_key

Msg 15580, Level 16, State 1, Line 3
Cannot drop master key because certificate ‘JB_TDE_Certificate’ is encrypted by it.

-> It seems like JBS_TDE_Certificate should be dropped before removing the master key. Lets try removing the certificate JBS_TDE_Certificate using below Tsql,

USE [master]
GO
DROP CERTIFICATE [JBS_TDE_Certificate]
GO

-> Received below error,

3.Drop_cert_error.PNG

Msg 3716, Level 16, State 15, Line 3
The certificate ‘JBS_TDE_Certificate’ cannot be dropped because it is bound to one or more database encryption key.

-> You will receive a similar error when you try removing certificate JBS_PROD_Certificate.

Removing TDE the Right way

-> Execute below query to disable TDE on the affected databases,

USE master;
GO
ALTER DATABASE [JB_DB] SET ENCRYPTION OFF;
GO
ALTER DATABASE [JBS_App_DB] SET ENCRYPTION OFF;
GO
ALTER DATABASE [JBS_DB] SET ENCRYPTION OFF;
GO

-> Execute below query and make sure encryption_state_desc is set to “Unencrypted” for all User databases,

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete, encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys

4.Decrypt.PNG

-> Execute below query to remove the encryption key,

USE [JB_DB];
GO
DROP DATABASE ENCRYPTION KEY;
GO

USE [JBS_App_DB];
GO
DROP DATABASE ENCRYPTION KEY;
GO

USE [JBS_DB];
GO
DROP DATABASE ENCRYPTION KEY;
GO

-> Execute below query again and see if there are any user databases encrypted,

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete, encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys

5.Only_Temdb.PNG

-> Drop certificates using below query,

USE [master]
GO
DROP CERTIFICATE [JB_TDE_Certificate]
GO
DROP CERTIFICATE [JBS_PROD_Certificate]
GO

-> Drop master key using below query,

use master
go
DROP MASTER KEY

-> Execute below query again and see if there are any databases encrypted,

select DB_NAME(database_id) AS DatabaseName, encryption_state,
encryption_state_desc =
CASE encryption_state
WHEN '0' THEN 'No database encryption key present, no encryption'
WHEN '1' THEN 'Unencrypted'
WHEN '2' THEN 'Encryption in progress'
WHEN '3' THEN 'Encrypted'
WHEN '4' THEN 'Key change in progress'
WHEN '5' THEN 'Decryption in progress'
WHEN '6' THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database is encrypting the database encryption key is being changed.)'
ELSE 'No Status'
END,
percent_complete, encryptor_thumbprint,encryptor_type
from sys.dm_database_encryption_keys

5.Only_Temdb

-> Tempdb is still there. Lets restart the SQL services and allow Tempdb to be recreated. Executing above query after restart will show no databases including Tempdb.

Thank You,
Vivek Janakiraman

Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.