How to find the protocol used by the connetion

As you know SQL Server supports three types of protocols, or we can called them as SNI Transport Providers, for communicating with a SQL Server instance. How do we know what SNI Transport Provider is being used for our connection?

Details of the connection established can be obtained from sys.dm_exec_connections dynamic management view. Here is the result of it with for a connection established to local instance.

view plainprint?

1. SELECT CONVERT(nvarchar(128), SERVERPROPERTY('SERVERNAME')) ServerConnected, *    
2. FROM sys.dm_exec_connections   
3. WHERE session_id = @@SPID;  

 SELECT CONVERT(nvarchar(128), SERVERPROPERTY('SERVERNAME')) ServerConnected, *  
 FROM sys.dm_exec_connections 
 WHERE session_id = @@SPID;

Since all protocols are enabled and connection made to a local instance, as you see, Connection uses Shared Memory as the protocol. Here is the result when only Named Pipes is enabled.

With TCP/IP enabled;

Log is getting automatically truncated with FULL Recovery Model

General understanding regarding the log file (ldf) is, it is getting automatically truncated when the Recovery Model is set to SIMPLE and it needs transaction log backup to be taken for truncating when the Recovery Model is set to FULL. Once the transaction log is truncated only, the unused space can be released back to the operating system.

Can there be a situation where the log is getting automatically truncated even with FULL Recovery Model? Yes, it is possible. Here is the code that shows it;

This code snippet creates two databases called Database_SIMPLERecovery and Database_FULLRecovery. Recovery model of first is set as SIMPLE and second set to FULL. Note that initial log file size is 1MB.

view plainprint?

1. -- 1.  
2. -- Creating a database with SIMPLE recovery model  
3. USE master;  
4. GO  
5.   
6. IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Database_SimpleRecovery')  
7.  DROP DATABASE Database_SimpleRecovery  
8. GO  
9. CREATE DATABASE [Database_SimpleRecovery]  
10.  CONTAINMENT = NONE  
11.  ON  PRIMARY   
12. ( NAME = N'Database_SimpleRecovery',   
13.  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_SimpleRecovery.mdf'   
14.  , SIZE = 3072KB , FILEGROWTH = 1024KB )  
15.  LOG ON   
16. ( NAME = N'Database_SimpleRecovery_log',   
17.  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_SimpleRecovery_log.ldf'   
18.  , SIZE = 1024KB , FILEGROWTH = 10%)  
19. GO  
20. ALTER DATABASE [Database_SimpleRecovery] SET RECOVERY SIMPLE   
21. GO  
22.   
23.   
24. -- 2.  
25. -- Creating a database with FULL recovery model  
26. USE master;  
27. GO  
28.   
29. IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Database_FullRecovery')  
30.  DROP DATABASE Database_FullRecovery  
31. GO  
32. CREATE DATABASE [Database_FullRecovery]  
33.  CONTAINMENT = NONE  
34.  ON  PRIMARY   
35. ( NAME = N'Database_FullRecovery',   
36.  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_FullRecovery.mdf'   
37.  , SIZE = 3072KB , FILEGROWTH = 1024KB )  
38.  LOG ON   
39. ( NAME = N'Database_FullRecovery_log',   
40.  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_FullRecovery_log.ldf'   
41.  , SIZE = 1024KB , FILEGROWTH = 10%)  
42. GO  
43. ALTER DATABASE Database_FullRecovery SET RECOVERY FULL   
44. GO  

-- 1.
-- Creating a database with SIMPLE recovery model
USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Database_SimpleRecovery')
 DROP DATABASE Database_SimpleRecovery
GO
CREATE DATABASE [Database_SimpleRecovery]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Database_SimpleRecovery', 
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_SimpleRecovery.mdf' 
 , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Database_SimpleRecovery_log', 
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_SimpleRecovery_log.ldf' 
 , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [Database_SimpleRecovery] SET RECOVERY SIMPLE 
GO


-- 2.
-- Creating a database with FULL recovery model
USE master;
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Database_FullRecovery')
 DROP DATABASE Database_FullRecovery
GO
CREATE DATABASE [Database_FullRecovery]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Database_FullRecovery', 
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_FullRecovery.mdf' 
 , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Database_FullRecovery_log', 
 FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\Database_FullRecovery_log.ldf' 
 , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE Database_FullRecovery SET RECOVERY FULL 
GO

Let's add some records to these two databases and check the space used by log files.

view plainprint?

1. -- Adding some transactions to Database_SimpleRecovery   
2. USE Database_SimpleRecovery;  
3. GO  
4.   
5. SELECT *  
6. INTO dbo.TestTable  
7. FROM AdventureWorksDW2014.dbo.FactInternetSales2;  
8.   
9. -- Checking for freespace in the log  
10. SELECT DB_NAME() AS DatabaseName,   
11.  name AS FileName,   
12.  size/128.0 AS CurrentSizeMB,    
13.  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB   
14. FROM sys.database_files;   
15.   
16.   
17. -- Adding some transactions to Database_FullRecovery   
18. USE Database_FullRecovery;  
19. GO  
20.   
21. SELECT *  
22. INTO dbo.TestTable  
23. FROM AdventureWorksDW2014.dbo.FactInternetSales2;  
24.   
25. -- Checking for freespace in the log  
26. SELECT DB_NAME() AS DatabaseName,   
27.  name AS FileName,   
28.  size/128.0 AS CurrentSizeMB,    
29.  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB   
30. FROM sys.database_files;   

-- Adding some transactions to Database_SimpleRecovery 
USE Database_SimpleRecovery;
GO

SELECT *
INTO dbo.TestTable
FROM AdventureWorksDW2014.dbo.FactInternetSales2;

-- Checking for freespace in the log
SELECT DB_NAME() AS DatabaseName, 
 name AS FileName, 
 size/128.0 AS CurrentSizeMB,  
 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 


-- Adding some transactions to Database_FullRecovery 
USE Database_FullRecovery;
GO

SELECT *
INTO dbo.TestTable
FROM AdventureWorksDW2014.dbo.FactInternetSales2;

-- Checking for freespace in the log
SELECT DB_NAME() AS DatabaseName, 
 name AS FileName, 
 size/128.0 AS CurrentSizeMB,  
 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

Note the free-space in both log files. Although the files sizes have gone up, no active transactions, they have been truncated, more free-space, hence free-space can be released (You might not see the same free-space immediately if CHECKPOINT process has not been run. If so, wait for few seconds and query for space again). However, when the database is in FULL recovery model, transactions should not be truncated until a transaction log backup is performed. What is the issue with second database?

There can be two more reasons for getting transaction log automatically truncated;

1. Full database backup has never been taken.
2. Full or differential backup has never been taken after switching from SIMPLE to FULL recovery model.

Since we have not taken a backup, our second database log is getting automatically truncated. Let's take a full backup and then do the same and see.

view plainprint?

1. -- Connecting with Database_FullRecovery  
2. USE Database_FullRecovery;  
3. GO  
4.   
5. -- Dropping table  
6. DROP TABLE dbo.TestTable;  
7. GO  
8.   
9. -- Taking full backup  
10. BACKUP DATABASE Database_FullRecovery  
11. TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\Database_FullRecovery.bak';  
12. GO  
13.   
14. -- Loading data again  
15. SELECT *  
16. INTO dbo.TestTable  
17. FROM AdventureWorksDW2014.dbo.FactInternetSales2;  
18.   
19. -- Checking for freespace in the log  
20. SELECT DB_NAME() AS DatabaseName,   
21.  name AS FileName,   
22.  size/128.0 AS CurrentSizeMB,    
23.  size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB   
24. FROM sys.database_files;   

-- Connecting with Database_FullRecovery
USE Database_FullRecovery;
GO

-- Dropping table
DROP TABLE dbo.TestTable;
GO

-- Taking full backup
BACKUP DATABASE Database_FullRecovery
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\Database_FullRecovery.bak';
GO

-- Loading data again
SELECT *
INTO dbo.TestTable
FROM AdventureWorksDW2014.dbo.FactInternetSales2;

-- Checking for freespace in the log
SELECT DB_NAME() AS DatabaseName, 
 name AS FileName, 
 size/128.0 AS CurrentSizeMB,  
 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS FreeSpaceMB 
FROM sys.database_files; 

As you see, not much free-space in the file now, means log has not been truncated. All you have remember is, in a scenario like this, log gets automatically truncated, which is not acceptable with FULL recovery model and should be avoided. If you need to see whether your database log file is getting automatically truncated with FULL recovery model, use sys.database_recovery_status to see last_log_backup_lsn column. If the column is NULL, then it indicates that the log is getting automatically truncated.

view plainprint?

1. SELECT * FROM sys.database_recovery_status  
2. WHERE database_id = DB_ID();  

SELECT * FROM sys.database_recovery_status
WHERE database_id = DB_ID();

Non Reversible Encryption with SQL Server: HASHBYTES function

Not all cipher texts are required to be converted back to plain texts. Good example is "passwords". All we need with non reversible encryption is, store them in encrypted format and perform comparison when required. What is the best way of implementing this with SQL Server?

SQL Server provides number of functions that can be used for encrypting plain texts using different mechanisms. Most of the functions allow you to encrypt and then decrypt them back to plain text. Examples for these functions are "ENCRYPTBYKEY" and "ENCRYPTBYCERT". If decryption is not required or the requirment is non reversible encryption, then the best function to be used is "HASHBYTES".

HASHBYTES returns the hash of given clear text based on the algorithm used. Algorithms supported are: MD2, MD4, and MD5 (128 bits (16 bytes)); SHA and SHA1 (160 bits (20 bytes)); SHA2_256 (256 bits (32 bytes)), and SHA2_512 (512 bits (64 bytes)). SHA2_256 and SHA2_512 available only with SQL Server 2012 and above.

Though we have been given many algorithms for this, most of them are susceptible for several attacks and no longer considered as secured cryptography algorithm. Some of them are known to "collisions" that generate same output for different inputs. If you are using a version before 2012, best is SHA1 even though it has been marked for "collisions". If the version of SQL Server is 2012 or above, best is either SHA2_256 or SHA2_512.

Here is a sample code that shows the usage of HASHBYTES;

This code creates a table and inserts two records.

view plainprint?

1. -- Creating table  
2. IF OBJECT_ID('dbo.UserCredential', 'U') IS NOT NULL  
3.  DROP TABLE dbo.UserCredential  
4. GO  
5. CREATE TABLE dbo.UserCredential  
6. (  
7.  UserId int identity(1,1) PRIMARY KEY  
8.  , UserName varchar(20) NOT NULL  
9.  , Password binary(64) NOT NULL  
10. )  
11. GO  
12.   
13. -- Inserting records  
14. INSERT INTO dbo.UserCredential  
15.  (UserName, Password)  
16. VALUES  
17.  ('Dinesh', HASHBYTES('SHA2_512', 'Pa$$w0rd'))  
18.  , ('Yeshan', HASHBYTES('SHA2_512', 'P@$$w0rD'))  
19.   
20. -- Checking records inserted  
21. SELECT * FROM dbo.UserCredential;  

-- Creating table
IF OBJECT_ID('dbo.UserCredential', 'U') IS NOT NULL
 DROP TABLE dbo.UserCredential
GO
CREATE TABLE dbo.UserCredential
(
 UserId int identity(1,1) PRIMARY KEY
 , UserName varchar(20) NOT NULL
 , Password binary(64) NOT NULL
)
GO

-- Inserting records
INSERT INTO dbo.UserCredential
 (UserName, Password)
VALUES
 ('Dinesh', HASHBYTES('SHA2_512', 'Pa$$w0rd'))
 , ('Yeshan', HASHBYTES('SHA2_512', 'P@$$w0rD'))

-- Checking records inserted
SELECT * FROM dbo.UserCredential;

Since the cipher text cannot be reverted back with HASHBYTES, here is the way of doing the comparison.

view plainprint?

1. -- Validating user  
2. IF EXISTS (SELECT * FROM dbo.UserCredential  
3.    WHERE UserName = 'Yeshan'  
4.     AND Password = HASHBYTES('SHA2_512', 'P@$$w0rD'))  
5.  Print 'User authenticated'  
6. ELSE  
7.  Print 'Invalid user!'  

-- Validating user
IF EXISTS (SELECT * FROM dbo.UserCredential
   WHERE UserName = 'Yeshan'
    AND Password = HASHBYTES('SHA2_512', 'P@$$w0rD'))
 Print 'User authenticated'
ELSE
 Print 'Invalid user!'

CONVERT returns '*' - [Happy New Year]

Let's start with a simple thing in 2015. Why we get '*' when converting one type to another? If you have already experienced it, then you know the reason, if not here is the reason;

view plainprint?

1. DECLARE @Integer int = 123;  
2.   
3. SELECT 'My Integer is ' + CONVERT(char(2), @Integer);  
4. GO  

DECLARE @Integer int = 123;

SELECT 'My Integer is ' + CONVERT(char(2), @Integer);
GO

Analyze the code above, as you see, CONVERT function returns '*' instead of '12'. This could happen with conversion because when converting from one type to another, data may be truncated, might be appeared as cut-off, or an error could be thrown because the new type is not fit enough to display the result. The conversion in the above code tries to convert 123 into char(2) which is too small to hold the value, hence displays '*'.

However, this behavior depends on the types involved. If you are converting varchar(3) to char(2), do not expect '*' but a cut-off value.

And all SQL lovers and my followers;