Category: Uncategorized

Backup/Restore a SQL TDE certificate

Database backups are critical to any organization. Similarly, we need to ensure the TDE certificates are backed up in case of a DR scenario. TDE cert is present on any database that has encryption at rest enabled on it.

Below script takes backup of the master key, TDE cert and Private key attached to the cert:

BACKUP MASTER KEY
TO FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_MK'
ENCRYPTION BY PASSWORD = 'StrongPassword';
GO

USE [master];
GO
BACKUP CERTIFICATE Livongo_Ascend_Cert TO FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_Cert.cert'
WITH PRIVATE KEY
(
    FILE = '\\NETWORKSHARE\CertBackup\TDE_SERVER01_Key.pk',
    ENCRYPTION BY PASSWORD = 'StrongPassword'
);
GO

Below is the script to use in a DR scenario or when you need to restore an encrypted backup to a new server:

/* This creates the service master key*/ 
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'StrongPassword'
GO

/* This creates the TDE cert from backup */
Use [master]
GO
CREATE CERTIFICATE TDE_databasename
FROM FILE='\\NETWORKSHARE\CertBackup\TDE_SERVER01_Cert.cert'
WITH PRIVATE KEY (
FILE='\\NETWORKSHARE\CertBackup\TDE_SERVER01_Key.pk',
DECRYPTION BY PASSWORD='StrongPassword')
GO

/* This creates the encryption key using the cert we created in the above step*/
Use [databasename]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_databasename
GO

/* This step enables the encrption on the database*/
ALTER DATABASE [databasename]
SET ENCRYPTION ON
GO

Use the following script to view all the encrypted DBs and their Cert name, encryption key used.

SELECT db_name(database_id) AS [Database Name],
    dek.key_length as [Key Length],
    case encryption_state when '0' then 'No database encryption key present, no encryption'
    when '1' then 'Not Encrypted'
    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'
    end as [Encryption Status] ,
    key_algorithm as [Key Algorithm],
    Name as [Cert Name],
    pvt_key_encryption_type_desc as [Pvt Key Desc],
    [subject] as [Subject],
    [expiry_date] as [Expiry Date],
    [start_date] as [Start Date]
FROM sys.certificates c
INNER JOIN sys.dm_database_encryption_keys dek ON c.thumbprint = dek.encryptor_thumbprint

How to install Mongodb on Mac

Install homebrew and run the following commands

#As of 2020, Homebrew does not allow to use shallow copy, you need to fetch from git using unshallow tag
sudo git -C "$(brew --repo homebrew/core)" fetch --unshallow

brew tap mongodb/brew

#Run this to install mongodb 4.2 or whatever is the latest version
brew install mongodb-community@4.2

#Run this to start Mongodb
brew services start mongodb-community

Run this check if mongodb service is running

ps aux | grep -v grep | grep mongod

#run this to connect to Mongodb
$mongo

dbatools – Run the sql query against all instances in the group on the CMS

I love dbatools. Lately, I have been using it regularly on my job. One thing that I do from time to time is check on various parameters like uptime, version, edition etc on various SQL servers. I would love to be able to check the uptime or latest cumulative update version on a group of servers registered under Central management server. Below is a command I found through dbatools module:

Get-DbaRegServer -SqlInstance [SERVERNAME] -Group Production | Invoke-DbaQuery -Query 'SELECT @@version'

The above one-line statement runs the query SELECT @@version across all the servers registered under group name Production on Central management server.

What is happening on the SQL server right now? sp_WhoIsActive

Open source stored proc by Adam Machanic

  1. Captures what is occurring now on the server
  2. Can be setup to capture output to table for later troubleshooting
  3. Link for documentation: http://sqlblog.com/blogs/adam_machanic/archive/2011/04/25/capt uring-the-output-a-month-of-activity-monitoring-part-25-of-30.aspx

Query Store – What can it do for us?

  1. Fix queries that have regressed recently
  2. Determine the number of times a query was executed
  3. Identify top queries
  4. Audit the history of query plans for a given query
  5. Analyze the resource usage patterns for a particular database
  6. SQL server 2017 version shows us wait stats for each query

Run one simple command to enable Query store on a database

ALTER DATABASE [DatabaseName] SET QUERY_STORE = ON;

Options
Operation Mode
Data Flush Interval (Minutes)
Statistics Collection Interval
Max Size (MB)
Query Store Capture Mode – AUTO | ALL | NONE
Size Based Cleanup Mode – AUTO | OFF
State Query Threshold (Days)