In order to reduce Tempdb contention one of  the best practices is to maintain multiple sized Tempdb data files, matching the number of processors and up to a maximum of 8. In this post I will show you T-SQL script to identify current Tempdb configuration and number of logical processors along with adding additional Tempdb data files as required.

Script 1: Find current tempdb configuration

select DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name from sys.master_files mf
where database_id =2 and type_desc= 'rows'

Script 2: Find number of logical processors

SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info

Script 3: Add tempdb data files as per processor count from the above query

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',
FILENAME = N'D:\DBA\Data\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 5MB) --<<--Update the data file location/Size/AutoGrowth

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3',
FILENAME = N'D:\DBA\Data\tempdev3.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
---ETC, add files as per processors count

Reboot/Restart of SQL services is not required for making the tempdb changes. Here is a great post that explains how to best remove extra Tempdb files.

For more information:


Often times it is required to find out the SQL name from the host names in a clustered environment so you can use it to connect through SSMS. There are many ways you can achieve this, however I am showing 2 methods here. Method 1 is the most common way using Failover Cluster Manager (ideal when there is one or just a few servers) and second method focuses on getting the same information using PowerShell (Ideal when you are dealing with a large number of servers)

Method 1:

The easiest way (if not the fastest) is to load up the Failover Cluster Manager and go to each Failover Clustered instance to look for the virtual SQL names. You can also easily see which FCIs are running on a particular node by selecting a node and viewing what resources it currently owns as shown in the below snippets.


This is the same for Windows 2003/ 2008+ and SQL 2005/ 2008.

Method 2:

While the above method is pretty easy, its not practical to log into each server to load up FCM when you have tens of hundreds of servers. For this you can use this simple PowerShell script that can be run remotely and can be fed with as many servers as you want to. This will output the SQL virtual names including those with named instances. Please note that the PowerShell cmdlets used here require admin rights on all of the nodes in the cluster or a security exception will be thrown.


1.  Create a notepad file “TestServers” on your desktop that contains the list of servers (Host names) that you want to run the code against


2. Open Powershell with Admin Privileges

3. Use the below script to load the servers list  into a Variable (Copy & paste the below code in PowerShell and click Enter – Make sure to change the location of your text file)

$machines = get-content C:\Users\Desktop\testservers.txt 

4. Use the below script to call the servers from the above variable to get the output

import-module failoverclusters
foreach ($machine in $machines) {get-clusterresource -Cluster 
$machine -ErrorAction SilentlyContinue| where-object 
{$_.ResourceType -like ?SQL Server?}| 
get-clusterparameter VirtualServerName,InstanceName | 
group-object ClusterObject | select-object @{Name = ?SQLInstance?;
Expression = {[string]::join(?\?,($_.Group | 
select-object -expandproperty Value))}}} 

Here is the session I gave for the techies at Microsoft about in-built tools available in SQL Server to analyze a query performance. This is a bit lengthy, but covers ALL out of the box tools in SQL Server to get query metrics.

Though there are several ways for importing SSIS packages into SQL Server either by creating a stored procedure or using a SSIS package itself, I find this simple code of line much easier and handy to work with.  It utilizes the “dtutil.exe” application which is installed by default with SQL Server installation. It is capable of importing and exporting packages and is found in the Binn folder of SQL Server (For eg: C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtutil.exe)

Below is a post from Suresh, which describes how dtutil works for importing and exporting one package at a time.

How to Copy or Export an SSIS Package Using Command Prompt Utility – DTUTIL

In this article we will see deploying multiple SSIS packages.

Demo for deploying multiple packages.

1. Open command prompt where the packages (.dtsx) are stored

Tip: Hold ‘Shift’ and right click to open command prompt window at the desired location.


2. Execute the dtutil script from cmd.

for %I in (*.dtsx) do dtutil /FILE "%I" /COPY SQL;"/Packages/%~nI" /DESTSERVER localhost


Note to make the below changed before you run the above script.

  • Change the package folder location where the SSIS packages will be deployed
  • Change the server name at the end of the code to reflect the destination server. We can choose ‘localhost’ if running this within the server as shown in the below figure.


As seen in the above figure all our packages are imported into the server.

For more dtutil Utility options please see the MSDN article HERE.

Before we get started on this topic, here is a quick fact..in SQL Server 2000, there used to be hard limit on the data that can be stored in a single row, which is 8,060 bytes. So, if the data exceeds this limit, the update or insert operation would fail!

Fortunately, in later SQL Server versions, rows are dynamically managed to exceed this limit and the combined width of the row can now exceed the 8,060 byte limit. I wanted to refresh this in our memory as this will help us to better understand the allocation units concept.

What are Allocation Units in SQL Server:

Every partition in a SQL Server table can contain 3 types of data, each stored on its own set of pages. And each of these types of pages is called an Allocation Unit. Below are the 3 types of Allocation Units.


So, an Allocation Unit is basically just a set of particular type of pages. Now, let us try to understand each of these allocation units using a demo.


When the row size stays within the 8,060-byte limit, SQL Server stores all of the data in the IN_ROW_DATA allocation unit and usually this unit holds the majority of data in most of the applications.

To better explain the concept, I came up with this simple Demo:

--Create a sample db AllocationUnitsDemo
USE master
CREATE DATABASE AllocationUnitsDemo

--Cretae a sample table ProductDetails in the AllocationUnitsDemo db
--Total length of the row in this table is 1000 + 4000 = 5000 (< 8000)
Use AllocationUnitsDemo
CREATE TABLE ProductDetails
ProductName varchar(1000),
ProductDesc varchar (4000), 

--Check the allocation unit type
Use AllocationUnitsDemo
SELECT type_desc, total_pages, used_pages,data_pages 
FROM sys.allocation_units
WHERE container_id = (SELECT partition_id FROM sys.partitions 
WHERE OBJECT_ID = OBJECT_ID('ProductDetails'))


Remember the introduction? so, when the row exceeds the 8,060-byte limit, SQL Server then moves one or more of the variable-length columns to pages in the ROW_OVERFLOW_DATA allocation unit.

We still have a limitation here for the row size. Though the combined width of the row can exceed the 8,060 byte limit, the individual width of the  columns must be within the limit of 8,000 bytes. This means we can have a table with two columns defined as nvarchar(5000), nvarchar(5000), but we are not allowed nvarchar(10000)

Demo Continued..

--Add an extra column to the above table ProductDetails
--Make the total length of the row to become 5000 + 4000 = 9000 (>8000)
Use AllocationUnitsDemo
ALTER TABLE ProductDetails ADD ProductSummary nvarchar(4000) 

--Now, Check the allocation unit type
Use AllocationUnitsDemo
SELECT type_desc, total_pages, used_pages,data_pages 
FROM sys.allocation_units
WHERE container_id = (SELECT partition_id FROM sys.partitions 
WHERE OBJECT_ID = OBJECT_ID('ProductDetails'))


If a column with LOB data type is defined, then SQL Server uses the LOB_DATA allocation unit. To know what data types are considered LOB and to get the list of LOB columns from a database, please refer my previous post: “SQL Server – Find all the LOB Data Type Columns in a Database Using T-SQL Script

Demo Continued..

--Add LOB data type column to the table ProductDetails
Use AllocationUnitsDemo
ALTER TABLE ProductDetails ADD ProductImage Image

--Again, Check the allocation unit type
Use AllocationUnitsDemo
SELECT type_desc, total_pages, used_pages,data_pages 
FROM sys.allocation_units
WHERE container_id = (SELECT partition_id FROM sys.partitions 
WHERE OBJECT_ID = OBJECT_ID('ProductDetails'))

Use master
DROP DATABASE AllocationUnitsDemo

How many Allocation Units can a Table have?

It actually depends on the number of partitions and indexes on the table.

To simplify the concept, as shown in the below picture, assume there is one table having no indexes (HEAP) and no partitions. Having no partitions mean, all of the table’s contents are stored in a single partition, meaning every table has at-least 1 partition.


Based on the above, we can have upto 3 allocation units for a table with no partitions and no indexes. And how about if we have partitions and Indexes? Below is the formula I came up with to get the maximum possible number of allocation units per table.

  • No of Allocation Units = No of Partitions × No of Indexes × 3


So, as we see from the figures above, a table can have up to 45 million allocation units in SQL Server 2012!

In this article I will show you how to restore a database that is encrypted with Transparent Data Encryption (TDE) on to a different server. Let’s use the same database ‘TDE_Test’ that we have created in the previous post HERE

Restoring a database to a different SQL Instance is usually a straightforward task. However, this attempt will return an error as shown below for an encrypted database when restoring into a different instance.

USE [master]
DISK = N'C:\Backup\TDE_Test_withtde.bak' 


Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint..
Msg 3013, Level 16, State 3, Line 2
RESTORE DATABASE is terminating abnormally

To restore successfully, we will need to physically copy the certificate (.cer) and private key (.pvk) to the destination server. As a best practice, we should immediately back up the certificate and the private key when we enable TDE. However, we can still take backup the certificate and private key now in the source server as shown below if not done earlier.

USE master; 
TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
(FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',

Create a Master Key in destination server.

The password provided here is different from the one we used in the source server since we are creating a new master key for this server.

USE master

After a master key has been created, create a certificate by importing the certificate we created earlier. Here the ‘Decryption By Password’ parameter is same as that provided to export the certificate to a file.

FROM FILE = 'E:\cert_Backups\ certificate_TDE_Test_Certificate.cer'     
WITH PRIVATE KEY (FILE = 'E:\cert_Backups\certificate_TDE_Test_Key.pvk', 

Restore Database in destination server

We will now be able to restore the encrypted database backup successfully.

USE [master]
RESTORE DATABASE [TDE_Test] FROM  DISK = N'F:\Backup\TDE_Test_withtde.bak' 

Listed are some of the advantages and disadvantages of using TDE.


  • The performance impact of TDE is minor. Estimated to be around 3-5%
  • Performs real-time I/O encryption and decryption of the data and log files
  • Encrypts the Entire Database in rest
  • No architectural changes needed
  • No application code changes are required and the user experience is the same
  • Easy to implement
  • DBAs can still see the data


  • No protection for data in memory
  • Not granular – Cannot just encrypt specific tables/columns
  • Not good for high CPU bottleneck servers
  • Not protected through communication/networks

Being a part of the database engineering team, I was given a task to set up disaster recovery program for some of our SQL Servers that host critical applications. After a few discussions, I planned to make use of log shipping to sync data between the two data centers like most DR sites work. This requires us to copy the backup files to the Disaster Recovery (DR) site for the initial setup. But in a little while, I realized moving 30 TB of data over the network is a Herculean task considering our extra large databases at an approximate 12-15 mbps over the network. This might not be a feasible option with 45 days of wait time along with something that chews up all our bandwidth. So we rather decided to ship the hard drives in a truck to the DR site which is just a few hundred miles away.

Everything was good until one of the IT Directors who was passing by got curious and questioned if it isn’t a security risk in case we lose our disks during the transit?

That made a whole lot sense, as anyone who can get hold of the disks can break-in into the stored data which is a huge threat. How do we solve this? Yes, encryption.

SQL Server has something called Transparent Data Encryption.

Lets see what Transparent Data Encryption is?

Transparent Data Encryption is a new feature in SQL Server 2008 which allows the encryption of the entire database while providing real time encryption of data files (.mdf) and log files (.ldf). Apart from this, TDE also encrypts any backups taken while the encryption is enabled. Data is encrypted before it is written to disk and data is decrypted when it is read from disk as it is read into the memory. This will help in preventing unauthorized access to the data and backup files.


I will show you an example of how anyone can read the backup files even without restoring the backup into another database.

I have created a database called TDE_Test that contains address details as shown below.(This database basically contains a few tables imported from the AdventureWorks2012 sample database. For downloading and installing AdventureWorks2012, refer HERE)


Figure 1: Result set showing Address data

Let’s backup the ‘TDE_Test’ database

TO DISK = 'E:\Backup\TDE_Test__Backup.bak' 

Open the backup file using Notepad and search for address ‘‘5 4 4   M a g d a   W a y’, as shown in Figure 2 (note the spaces between the letters and three spaces between each word as this is Unicode text)

We can easily see that the data in backup file is readable. This means anyone with the backup file can actually read your data with little skill, even without restoring it. 


Figure 2: Backup file opened in Notepad – Un-encrypted format

Implementing Transparent Data Encryption

TDE setup includes the below four basic steps.

  1. Creating a Master Key
  2. Creating a Certificate Protected by the Master Key
  3. Creating a Database Encryption Key
  4. Turning on the TDE

1. Creating a master key 

It is a good practice to backup the database before proceeding with the encryption. So in case, we want to reverse the implementation of TDE we have a latest copy of the database to bring back.

The master key must be created within the master database


2. Creating a Certificate Protected by the Master Key

The certificate is used to protect the database encryption key that we will create next. In addition, this certificate is protected by the master key created in the previous step.


3. Creating a Database Encryption Key

Create Database Encryption Key inside the database to be encrypted, and protect it with the certificate. You can choose from one of the following encryption algorithms: AES_128, AES_192, AES_256, or TRIPLE_DES_3KEY. Details on the encryption algorithms are beyond the scope of this article. Check out this Microsoft Technet article for guidance on choosing the encryption algorithm

USE TDE_Test  


I have also added an additional script below to backup the private key and certificate to a disk drive. Since TDE also encrypts the backup files, it is always required to maintain a backup of private key and certificate along with the database backup to use at the time of restoring.

USE master; 
TO FILE = 'E:\Backup\certificate_TDE_Test_Certificate.cer'
(FILE = 'E:\Backup\certificate_TDE_Test_Key.pvk',

4. Turning TDE ON

Since encryption is a resource-intensive process it is always a best practice to turn on TDE during off-business hours or when the server is least busy.


We can check the progress of Encryption using the below script from utilizing ‘sys.dm_database_encryption_keys’ DMV. A value of ‘2’ for ‘encryption_state’ indicates that encryption has begun and ‘3’ for completed

FROM sys.databases db JOIN sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id WHERE db.name = 'TDE_TEST'

Let’s take a backup of our encrypted database now and search for the same address that we looked earlier

TO DISK = 'E:\Backup\TDE_Test_withtde.bak' 

Figure 3: Backup file opened in Notepad – Encrypted format

That’s it! Our data is now encrypted and secured 🙂

This way, we can make sure the data is secured as well protected from any unauthorized access.

As a continuation of this topic,  I have explained how to restore a TDE Encrypted database backup HERE

The command prompt utility dtutil can be very handy when we want to quickly export an SSIS package from either file system to msdb or vice-versa.

For a quick demo, I created a package called “ProductPrice” uder the file system C:\packages, as shown in the screenshot below


Also, I created another package called “UpdatePrice” in SQL Server, which gets stored in msdb database, below is the screnshot


Now, let us see how we can quickly import or export these packages using cmd. For this we will be using the COPY option in the dtutil utility

  • To copy package from file system to msdb

Run the below syntax from cmd:

dtutil /FILE C:\Packages\ProductPrice.dtsx /COPY SQL;ProductPrice

This copies/exports “ProductPrice” package from file system to msdb database as shown in the below screenshot


  • To copy a package from msdb to file system

Run the below syntax from cmd:

dtutil /SQL UpdatePrice /COPY FILE;C:\Packages\UpdatePrice.dtsx

This copies/exports “UpdatePrice” package from msdb database to file system. Below is the screenshot


I have used “Windows Authentication” in this demo. To use mixed mode authentication or to export packages to a different server, we need to provide proper dtutil options, which can be found running the syntax dtutil /? from cmd utility.

Sometimes you might want to add more than one column as primary key. For example, if you have three columns named Last Name, First Name and Address and  there can be duplicate Last Names  or duplicate First Names but can never have duplicates in Last Name, First Name  and Address combined together

Adding columns to a primary key constraint using T-SQL script:

USE AdventureWorks2012;
ALTER TABLE Production.TransactionHistoryArchive 
ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID 
PRIMARY KEY CLUSTERED (TransactionID,[ProductID]);

In order to add columns to already existing primary key, you need to drop the primary key constraint first to add columns later.
Drop primary key script is provided below.

USE AdventureWorks2012;
ALTER TABLE Production.TransactionHistoryArchive 
DROP constraint [PK_TransactionHistoryArchive_TransactionID] 

Adding columns to a primary key constraint using SSMS GUI:

Right click on the Table Name and click on ‘Design’. Hold Ctrl key and select the column names that you want to add as Primary key. Then click on the ‘Set Primary Key’ as shown below.


The below screen shot shows three columns as primary key.


As a quick fact, the maximum number of columns that you can add to the primary key is limited to 16.

Registered servers is a great way to manage, categorize and access the SQL servers through SSMS and also one of the overlooked aspects. Registered servers are handy when you want to have all your servers at one place, sorted and categorized in various ways. With registered servers all your instances are just a click away eliminating the need to type the instance name every time you connect. Apart from the aforementioned uses of registered server I have put down other helpful scenarios where registered servers come in handy.

  • Easy to share among the team members and which is especially helpful for a newly hired DBA with import and export feature.
  • Does great job when you want to run a query on multiple servers in one query window. Either you want to check backups for all your production   instances or disable a built-in admin login from all your 2005 servers.
  • Helps to immediately identify servers’ availability after an outage.

Here are the steps to register individual servers using GUI

From the SSMS, open view and click on registered servers or Ctrl+Alt+G if you are feeling lucky 🙂

Reg ser 1

You will have an option of creating either a new group or a new server. Here I have chosen ‘New Server Registration’

Reg ser 2

As you can see here, server name is the actual name/ip address and Registered server name is what you like it to display.

Reg ser 3

I usually prefer the servers categorized based on the environment as shown below.

Reg ser 4

You can right click on any of the groups and open a new query window. That means, a query window is open to execute a query that gets the result from all the servers. The below screenshot shows the number of servers connected. This comes handy when you are checking the servers for connection issues.

Reg ser 5

Sure, there are hundreds of servers in an organization and adding a server one at a time is probably not a best way to go. This hints us to a way to add multiple entries at a time. Instead of reinventing the wheel again I found a great way to do this HERE

Technical Reviewer(s): Venkata Suresh Raavi; Jaipal Vajrala