Notes on Encryption

Let us create a database:

create database encrypt_test

Change to this new database:

use encrypt_test

Now add a database master key:
create master key encryption by password = 'pwd123'

Some subtle points here:

1. Database master key cannot be just created with service master key, it needs a password for creation.

2. 'create master key' automatically encrypts master key by service master key. Therefore, if you wish the master key to be opened by password only, you must drop encryption by service master key at this stage. However, you must consider the situation of losing the master key password before dropping encryption by service master key since it can be your only saviour when you lose the master key password.


Now let us create a table:
create table crypto(
    actual int, 
    encrypted varbinary(256)
)

Let us add some data to play with:
insert into crypto(actual)
select number from master..spt_values where type = 'p'
go 100

Add a certificate and symmetric key:


create certificate crypto_cert with subject = 'crypto'
create symmetric key crypto_key with algorithm = triple_des encryption by certificate crypto_cert

 Open the symmetric key to encrypt data:
open symmetric key crypto_key decryption by certificate crypto_cert

Check if it is open alright:
select * from sys.openkeys


Go for a test drive of encryption:
select top 20 actual, encryptbykey(key_guid('crypto_key'),convert(varchar(10),actual)) from crypto

Enough, now encrypt the data:
update crypto
set encrypted = encryptbykey(key_guid('crypto_key'),convert(varchar(10),actual))

How about checking the encrypted value:
select top 20 actual, convert(varchar(10),decryptbykey(encrypted)) from crypto

All is well till you lose the master key password . If you have dropped encryption by service master key, you cannot retrieve the encrypted data. Therefore, it is a good practice to add encryption by service master key to safeguard against such situation.

Regenerate the master key:

alter master key regenerate with encryption by password = '123pwd'

Regenerate uses the service master key to open the master key followed by decrypting and encrypting the certificate/symmetric keys (it does not re-encrypt the data). If the old master key cannot be opened, regenerate will fail.

After regerate succeeds, open the master key and add encryption by service master key:

open master key decryption by password = '123pwd'

alter master key add encryption by service master key

Note that database master key regeneration is different from service master key regeneration.

CPU usage tracker

--1. Detect whenever the CPU usage gets over a configured value e.g. 90%
-- 1.1. Determine the CPU usage
--2. Take snapshots of query running for next 5mins



--select
-- timestamp,
-- convert(xml, record) as record
--from sys.dm_os_ring_buffers
--where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' and record like '%%'
--order by timestamp desc
/*
select * from test.dbo.sql_process_snapshot
drop table sql_process_snapshot
create table sql_process_snapshot (
logdatetime datetime,
log_comment nvarchar(128),
session_id smallint,
status nvarchar(24),
command nvarchar(24),
cpu_time int,
total_elapsed_time int,
start_time datetime,
database_name nvarchar(256),
query_text nvarchar(1024)
)

*/

declare
@ts_now bigint,
@sql_cpu_usage int,
@server_cpu_usage int,
@threshold_cpu_value int,
@snapshot_delay varchar(8),
@snapshot_iterations tinyint

select
@threshold_cpu_value = 90,
@snapshot_delay = '00:00:15', --hh:mm:ss
@snapshot_iterations = 16 --cpu usage is recorded every 4mins

select
@ts_now = cpu_ticks/(cpu_ticks/ms_ticks)
from sys.dm_os_sys_info;

;with cpu_usage
as
(
select top 1
record_id,
dateadd(ms, -1 * (@ts_now - [timestamp]), GetDate()) as event_time,
sql_process_utilization,
system_idle,
100 - (system_idle + sql_process_utilization) as server_process_utilization
from
(
select
record.value('(./Record/@id)[1]', 'int') as record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') as system_idle,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') as sql_process_utilization,
timestamp
from
(
select
[timestamp],
convert(xml, record) as record
from sys.dm_os_ring_buffers
where ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
and record like '%%'
) as x
) as y
order by event_time desc
)
select
@sql_cpu_usage = sql_process_utilization,
@server_cpu_usage = server_process_utilization
from cpu_usage

select @sql_cpu_usage as 'SQL CPU Usage', @server_cpu_usage as 'Server CPU Usage'

if @sql_cpu_usage >= @threshold_cpu_value
begin
declare @i int
select @i = 1
while @i <= @snapshot_iterations begin insert into test.dbo.sql_process_snapshot(logdatetime, log_comment, session_id, [status], command, cpu_time, total_elapsed_time, start_time, database_name, query_text) select getdate(), N'SQL exceeded CPU threshold: SQL CPU Usage - ' + convert(nvarchar(10),@sql_cpu_usage) + N'; Server CPU Usage - ' + convert(nvarchar(10),@server_cpu_usage), req.session_id, req.[status], req.command, req.cpu_time, req.total_elapsed_time, req.start_time, db_name(req.database_id) as db_name, stmt.text from sys.dm_exec_requests req cross apply sys.dm_exec_sql_text(sql_handle) as stmt where session_id <> @@spid --exclude current process

waitfor delay @snapshot_delay
select @i = @i + 1
end

end

if @server_cpu_usage >= @threshold_cpu_value
begin
insert into test.dbo.sql_process_snapshot(logdatetime, log_comment)
select
getdate(),
N'Server exceeded CPU threshold: SQL CPU Usage - ' + convert(nvarchar(10),@sql_cpu_usage) + N'; Server CPU Usage - ' + convert(nvarchar(10),@server_cpu_usage)
end




---
create trigger tI_sql_process_snapshot
on sql_process_snapshot
for insert
as
begin
declare @log_event nvarchar(128)

select top 1 @log_event = log_comment from inserted

--exec sp_send_mail

end
go

--

Minimally logged/Non-logged operation and truncate table

Nonlogged operations offer much better performance than logged operations.

The following DML operations are either nonlogged or minimally logged:

TRUNCATE TABLE.

SELECT INTO.


The TRUNCATE TABLE statement cannot be used on tables that are referenced by a FOREIGN KEY constraint, unless the FOREIGN KEY constraint is self-referencing. The TRUNCATE TABLE statement cannot be used on a table that is a part of an indexed view. You cannot use the TRUNCATE TABLE statement on tables that are published by transactional or merge replication. The TRUNCATE TABLE statement will not activate triggers, as triggers rely on transaction logs

The TRUNCATE TABLE statement uses much less transaction log space. The DELETE statement logs information about every row that is affected in the transaction log. When deleting from a table with millions of rows, this is both time- and disk-space-consuming.

The DELETE statement holds a lock on each individual row it is deleting. The TRUNCATE TABLE statement locks only the table and each data page. This offers better performance, as locking is one of the most time-consuming SQL Server activities.

The DELETE statement will leave empty data pages for the table and its indexes. If you wish to shrink the database by deleting data in a large table, the DELETE statement may prove counterproductive. The TRUNCATE TABLE statement, on the other hand, is guaranteed to leave zero data pages behind.

Void sales

--check/delete void sales
;with s1
as
(
--reverse transactions
select *,
row_number() over(partition by recordtype, TxnDateTime, TransactionIdentifier, ExternalStoreIdentifier, TillNumber, ProductItemIdentifier, ItemQuantity, SaleAmount order by recordtype, TxnDateTime, TransactionIdentifier, ExternalStoreIdentifier, TillNumber, ProductItemIdentifier, ItemQuantity, SaleAmount) as rn
from @sales
where (recordtype = 'S' and SaleAmount < 0 and ItemQuantity < 0) or (recordtype = 'R' and SaleAmount > 0 and ItemQuantity > 0)
),
s2 as
(
--regular transactions
select *,
row_number() over(partition by recordtype, TxnDateTime, TransactionIdentifier, ExternalStoreIdentifier, TillNumber, ProductItemIdentifier, ItemQuantity, SaleAmount order by recordtype, TxnDateTime, TransactionIdentifier, ExternalStoreIdentifier, TillNumber, ProductItemIdentifier, ItemQuantity, SaleAmount) as rn
from @sales
where (recordtype = 'S' and SaleAmount > 0 and ItemQuantity > 0) or (recordtype = 'R' and SaleAmount < 0 and ItemQuantity < 0)
),
s3 as
(
select s1.srn from s1 inner join s2 on s1.recordtype = s2.recordtype and s1.TxnDateTime = s2.TxnDateTime and s1.TransactionIdentifier = s2.TransactionIdentifier and s1.ExternalStoreIdentifier = s2.ExternalStoreIdentifier and s1.TillNumber = s2.TillNumber and s1.ProductItemIdentifier = s2.ProductItemIdentifier
where s1.rn = s2.rn and s1.SaleAmount = -s2.SaleAmount and s1.ItemQuantity = -s2.ItemQuantity
union
select s2.srn from s1 inner join s2 on s1.recordtype = s2.recordtype and s1.TxnDateTime = s2.TxnDateTime and s1.TransactionIdentifier = s2.TransactionIdentifier and s1.ExternalStoreIdentifier = s2.ExternalStoreIdentifier and s1.TillNumber = s2.TillNumber and s1.ProductItemIdentifier = s2.ProductItemIdentifier
where s1.rn = s2.rn and s1.SaleAmount = -s2.SaleAmount and s1.ItemQuantity = -s2.ItemQuantity
)
delete s from @sales s inner join s3 on s.srn = s3.srn