Tag: sql server

Working with CDC in SQL server

How do you read all the changes captured by CDC in a table?

USE AdventureWorks2022; 
GO 
DECLARE @from_lsn binary(10), @to_lsn binary(10); 
SET @from_lsn = sys.fn_cdc_get_min_lsn('HR_Department'); 
SET @to_lsn = sys.fn_cdc_get_max_lsn(); 
SELECT * FROM cdc.fn_cdc_get_all_changes_HR_Department (@from_lsn, @to_lsn, N'all'); 
GO

How do you read the net changes captured by CDC in a table?

USE AdventureWorks2022;  
GO  
DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);  
-- Obtain the beginning of the time interval.  
SET @begin_time = DATEADD(day, -1, GETDATE()) ;  
-- DML statements to produce changes in the HumanResources.Department table.  
INSERT INTO HumanResources.Department (Name, GroupName)  
VALUES (N'MyDept', N'MyNewGroup');  
  
UPDATE HumanResources.Department  
SET GroupName = N'Resource Control'  
WHERE GroupName = N'Inventory Management';  
  
DELETE FROM HumanResources.Department  
WHERE Name = N'MyDept';  
  
-- Obtain the end of the time interval.  
SET @end_time = GETDATE();  
-- Map the time interval to a change data capture query range.  
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);  
SET @from_lsn = ISNULL(sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time), [sys].[fn_cdc_get_min_lsn]('HR_Department') );
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);  
  
-- Return the net changes occurring within the query window.  
SELECT * FROM cdc.fn_cdc_get_net_changes_HR_Department(@from_lsn, @to_lsn, 'all');

source: cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL) – SQL Server | Microsoft Learn

SQL Server IO is not fixed size

A peak underneath the hood

OperationIO Block Size
Transaction log write512 bytes – 60 KB
Checkpoint/Lazywriter8KB – 1MB
Read-Ahead Scans128KB – 512KB
Bulk Loads256KB
Backup/Restore1MB
ColumnStore Read-Ahead8MB
File Initialization8MB
In-Memory OLTP Checkpoint1MB

How to configure Transparent Data Encryption (TDE) in SQL Server

Introduction

Transparent Data Encryption (TD) was introduced in SQL Server 2008. Its main purpose was to protect data by encrypting the physical files, both the data (mdf) and log (ldf) files (as opposed to the actual data stored within the database). TDE encrypts SQL Server, Azure SQL Databases, and Azure SQL Data Warehouse data files.

Instructions

step-by-step guide:

  1. Create a master key
  2. Create or obtain a certificate protected by the master key
  3. Create a database encryption key and protect it by using the certificate.
  4. Set the database to use encryption.

The following example shows the encryption and decryption of the StackOverflow database using a certificate named MyServerCert that’s installed on the server.

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE StackOverflow;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE StackOverflow
SET ENCRYPTION ON;
GO

The encryption and decryption operations are scheduled on background threads by SQL server. You can use the DMVs to query the cert and key information.

What happens when you enable TDE?

To enable TDE on a database, SQL server must do an encryption scan. The scan reads each page from the data files into the buffer pool and then writes the encrypted pages back out to disk.

Is Tempdb encrypted too when you enable TDE on any database in a SQL instance?

Yes, the tempdb system database is encrypted if any other database on the SQL server instance is encrypted by using TDE. This encryption might have a performance effect for unencrypted databases on the same SQL instance.

Are you able to add an encrypted database to an AlwaysOn availability group?

Yes, you can. To encrypt databases that are part of an availability group, create the master key and certificates on all secondary replicas before creating the database encryption key on the primary replica.

If a certificate is used to protect the database encryption key, backup the certificate created on the primary replicate, and then create the certificate from a file on all secondary replicas before creating the database encryption key on the primary replica.

Here is the script to view all the encrypted DBs and their certificate names on a SQL server instance:

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