Nooks in SQL Server

 

SQL server mirroring

Operating Modes of mirroring setting

High availability
High safety
High performance

Roles of mirroring

Principal role
Mirror role
Witness role

Different hash algorithms produce different hash values

DECLARE @HashValue varchar(100)
SELECT @HashValue = ‘SQL Server’
SELECT HashBytes(‘MD5’, @HashValue)
SELECT @HashValue = ‘SQL Server’
SELECT HashBytes(‘SHA1’, @HashValue)
GO

Hash values are case sensitive

DECLARE @HashValue varchar(100)
SELECT @HashValue = ‘sql’
SELECT HashBytes(‘SHA1’, @HashValue)
SELECT @HashValue = ‘SQL’
SELECT HashBytes(‘SHA1’, @HashValue)
GO

Symmetric keys

CREATE SYMMETRIC KEY MySymmetricKey WITH ALGORITHM = RC4
ENCRYPTION BY PASSWORD = ‘A1b2C3#$’
GO
SELECT * FROM sys.symmetric_keys
GO

CREATE TABLE SymmetricKeyDemo
(ID             int IDENTITY(1,1),
PlainText       varchar(30) NOT NULL,
EncryptedText   varbinary(80) NOT NULL)
GO

Symmetric key must be opened before being used

OPEN SYMMETRIC KEY MySymmetricKey DECRYPTION BY PASSWORD = ‘A1b2C3#$’
GO

INSERT INTO SymmetricKeyDemo
(PlainText, EncryptedText)
VALUES(‘SQL Server’, EncryptByKey(Key_GUID(‘MySymmetricKey’),’SQL Server’))
GO

SELECT ID, PlainText, EncryptedText, cast(DecryptByKey(EncryptedText) AS varchar(30))
FROM SymmetricKeyDemo
GO
CLOSE SYMMETRIC KEY MySymmetricKey
GO

Asymmetrics keys – public and private key tokens

Certificates

CREATE TABLE CertificateDemo
(ID             int IDENTITY(1,1),
PlainText       varchar(30) NOT NULL,
EncryptedText   varbinary(500) NOT NULL)
GO

CREATE CERTIFICATE MyCert AUTHORIZATION dbo
WITH SUBJECT = ‘Test certificate’
GO

SELECT * FROM sys.certificates
GO
INSERT INTO CertificateDemo
(PlainText, EncryptedText)
VALUES(‘SQL Server’,EncryptByCert(Cert_ID(‘MyCert’), ‘SQL Server’))
GO

SELECT ID, PlainText, EncryptedText, CAST(DecryptByCert(Cert_Id(‘MyCert’),
    EncryptedText) AS varchar(max))
FROM CertificateDemo
GO

Transparent data encryption

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<InsertStrongPassword>’;
GO
CREATE CERTIFICATE MyServerCert WITH SUBJECT = ‘TDE Certificate’
GO

USE SQL2008SBS
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO

ALTER DATABASE SQL2008SBS
SET ENCRYPTION ON
GO

——————————————————————–

To find the current identity value of any table

select IDENT_CURRENT(‘patient’)

To read the trace file and display in grid

select * from sys.fn_trace_gettable(‘\\dev-chen-pc1615\temp\test.trc’,-1)

To add custom eror message in SQL server

sp_addmessage 50002,16,’this is custom build message by Yuva’,NULL,NULL,NULL

RAISERROR (50002, 10,1) WITH LOG
RAISERROR (‘this is temp error %d and %s and %d’,16,3,12,’as’,13) WITH LOG

———————————————————————————–

To find the most time consuming stored procedure in a database

SELECT TOP 10
ProcedureName = tex.text,
ExecutionCount = sta.execution_count,
AvgExecutionTime = isnull( sta.total_elapsed_time / sta.execution_count, 0 ),
AvgWorkerTime = sta.total_worker_time / sta.execution_count,
TotalWorkerTime = sta.total_worker_time,
MaxLogicalReads = sta.max_logical_reads,
MaxLogicalWrites = sta.max_logical_writes,
CreationDateTime = sta.creation_time,
CallsPerSecond = isnull( sta.execution_count / datediff( second, sta.creation_time, getdate()), 0 )
FROM sys.dm_exec_query_stats sta
CROSS APPLY sys.dm_exec_sql_text( sta.sql_handle ) tex
— WHERE …
ORDER BY
— statotal_elapsed_time DESC
sta.execution_count desc

Will update more…

-Yuva