My articles published on SearchSQLServer.com from January 2014 to September 2014

The following is the list of my articles published on SearchSQLServer.com from January 2014 to September 2014. To read these articles, you must be a member of SearchSQLServer.com.

SearchSQLServer.com


Learn how to use SQL Server Import and Export Wizard, Published on September 3, 2014

SQL Server 2014 provides a variety of methods to transfer data between SQL Server databases and Microsoft Excel documents. One method is the SQL Server Import and Export Wizard, and this article provides a step-by-step guide for using this wizard to transfer data between SQL Server databases and Microsoft Excel worksheets.


The key to boosting OLTP with SQL Server 2014 In-Memory, Published on August 8, 2014

The SQL Server 2014 in-memory OLTP engine — project name Hekaton — is one of the key new performance-related architectural enhancements to the SQL Server database engine. It is designed to boost performance of OLTP and data-warehouse workloads and reduce processing times. The SQL Server 2014 in-memory OLTP engine is supported on 64-bit Enterprise, Developer and Evaluation editions of SQL Server 2014.

This article explains how to improve the performance of OLTP applications using SQL Server 2014 in-memory OLTP engine. This article also details what makes SQL Server 2014 in-memory OLTP engine different from other vendors in-memory OLTP engines and how to use that to your advantage.


Top four tips for virtualizing your SQL Server instance, Published on July 28, 2014

The increasing need for SQL Server instances is raising costs. Therefore, controlling the cost of databases and related hardware resources and improving and expanding them as necessary have become increasingly important. An ideal solution to this problem is virtualization.

This article shows you how to control the costs through effective virtualization.


The top three SQL Server third-party tools, Published on June 30, 2014

Monitoring and optimizing SQL Server performance, as well as identifying and fixing performance bottlenecks in SQL Server databases, can be a daunting task for database administrators (DBAs), developers and application support teams. SQL Server performance is influenced by factors like server hardware, operating system setup and database configuration. SQL Server provides a comprehensive set of tools for monitoring its own performance, but many SQL Server DBAs, developers and application support teams choose to use third-party performance monitoring tools.

SQL Server third-party performance monitoring tools are more flexible than the tools that ship with the product, and they provide a centralized repository for performance-related data. Moreover, they are easy to use and provide real-time visibility into all SQL Server performance. Third-party monitoring tools can help monitor SQL Server and database performance, resolve potential problems with threshold-based and trend-based alerts, and isolate the root cause of application performance issues.

This article explores three of the top SQL Server third-party tools for quickly identifying and resolving performance bottlenecks.


Tips to escape a SQL Server pitfall or performance bottleneck, Published on April 29, 2014

Over the last decade, many complex enterprise applications have been developed and deployed using Microsoft SQL Server. Today, SQL Server is a cornerstone of modern business applications and is at the center of the business processes of many leading companies. SQL Server applications range from line-of-business applications in production, to internal customer relationship management and decision support systems, to customer-facing e-commerce and Web self-service applications. Consequently, SQL Server performance and scalability are high on IT priority lists, and delivering optimal SQL Server performance and scalability is one of the key tasks of all SQL Server DBAs.

However, many SQL Server systems suffer from poor performance and scalability, often caused by poor database design, index design and a SQL Server system improperly configured for the workload. The reason for this is that the main goal of the development process of any large-scale SQL Server project is functionality, with performance and scalability frequently treated as an afterthought.

While troubleshooting SQL Server database system performance problems is a difficult task, significant performance improvements can be achieved with a relatively small time investment. This article provides five tips for SQL Server users, from avoiding a hardware performance bottleneck to dealing with database design.


Dive deep into SQL Server 2014 in-memory OLTP, Published on March 14, 2014

The SQL Server 2014 in-memory online transaction processing (OLTP) engine, previously code-named “Hekaton“, allows you to create in-memory optimized OLTP tables within a conventional relational database. SQL Server 2014 in-memory OLTP engine enables customers to build mission-critical applications and Big Data solutions using high-performance, in-memory technology across OLTP. It is one of the key new performance-related architectural enhancements to SQL Server 2014, although it is only supported on 64-bit Enterprise, Developer and Evaluation editions.

Checkout this article to learn how to set it up.


Defining half a dozen daily SQL Server DBA responsibilities, Published on February 20, 2014

SQL Server database administration can be a complex and stressful job. Database administrators’ responsibilities cover the performance, integrity and security of business data and SQL Server databases. To fulfil their duties and to make business data available to its users, database administrators have to perform routine DBA checks on their SQL Servers to monitor their status.

So, what critical aspects of SQL Server should all DBAs include in their daily checklist? This article highlights six daily DBA responsibilities that every SQL Server manager should perform.


SQL Server encryption features in SQL Server 2014, Published on February 6, 2014

Data security is a vital and growing concern for many organizations due to the increasing loss and unauthorized disclosure of confidential data. Regulatory requirements often require robust encryption solution for data such as credit card and Social Security numbers. SQL Server 2014 provides database administrators with several options to encrypt data when transmitted through the network, while creating a backup, or when stored on the server or network.

This article guides you through the different encryption options that are available in SQL Server 2014 to encrypt confidential data in the SQL Server database.


Digging through SQL Server OLAP storage models, Published on January 28, 2014

The physical storage design for your multidimensional applications affects the latency, size and performance of your project. Therefore, when designing the physical storage for it, you must determine how to store the dimensions and measures, and how to design an effective physical storage strategy for multidimensional applications.

This article shows how to distinguish among the three SQL Server OLAP storage models and decide which one is best in different use cases.


Eight key SQL Server 2014 features, Published on January 23, 2014

Microsoft SQL Server is an enterprise database server that is the cornerstone of modern business applications and is in the center of the business processes of many leading organizations. The latest release of Microsoft SQL Server, SQL Server 2014, has many new features that let you design, build, and deploy high-performance mission-critical OLTP applications and Big Data solutions.

This article reviews the eight key new SQL Server 2014 Database Engine features.


I hope you will find these articles useful. 🙂

Have a good week!

Basit

Securing Database Contents with Transparent Data Encryption (TDE)

Introduction

Microsoft SQL Server has many security features available within the database, but until release of SQL Server 2008 there has been no “out-of-the-box” method for protecting the data at the operating system level. The Transparent Data Encryption (TDE) feature introduced in SQL Server 2008 allows sensitive data to be encrypted within the data files to prevent access to it from the operating system. It solves the problems of security of data means encrypting databases on hard disk and on any backup media and is the best possible choice for bulk encryption to meet the regulatory compliance or corporate data security standards. This feature encrypts both data and logs as the records are written to SQL database files (*.mdf) in real-time, including backups, snapshots and transaction logs. TDE encrypts data before it’s written to disk and decrypts data before it is returned to the application. The encryption and decryption process is performed at the SQL layer, completely transparent to applications and users. TDE encryption uses a Database Encryption Key (DEK) (that is an asymmetric key secured by using a certificate stored in the master database), which is stored in the database boot record for availability during recovery.

In this post, I’ll show you how to encrypt database using Transparent Data Encryption (TDE) and then I will discuss the limitations of TDE.

Architecture of Transparent Data Encryption

The following illustration shows the architecture of TDE encryption:

Service Master Key is created at a time of SQL Server setup; DPAPI encrypts the Service Master Key. Service Master Key encrypts Database Master Key for the Master Database. The Database Master Key of the master Database Creates the Certificate then the certificate encrypts the database encryption key in the user database. The entire database is secured by the Database Master Key of the user Database by using TDE. TDE performs the encryption at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory.

Microsoft Reference: Transparent Data Encryption (http://msdn.microsoft.com/en-us/library/bb934049.aspx)

Demo

Note: For the purposes of this post, I’ll be encrypting SQL Server 2012 sample database AdventureWorks2012 database using TDE. It is advisable to backup the database prior to implementing TDE.

Our first step to setup TDE is to create a database master key for our master database. To do that, open the New Query window and execute the following script:

USE [master]
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1';
GO

Information about the database master key is visible in the sys.symmetric_keys catalog view. Execute the following query to verify that database master key is encrypted by the service master key:

USE [master]
GO

SELECT b.[name]
	,a.[crypt_type_desc]
FROM [sys].[key_encryptions] a
INNER JOIN [sys].[symmetric_keys] b ON a.[key_id] = b.[symmetric_key_id]
WHERE b.[name] = '##MS_DatabaseMasterKey##';
GO

Example:

Now the next step is to create a self-signed certificate that is protected by the database master key of our master database. This certificate encrypts the Database Encryption Key in the AdventureWorks2012 database.

Execute the following script to create the self-signed certificate:

USE [master]
GO

CREATE CERTIFICATE CertificateforTDE
	WITH SUBJECT = 'Certificate for TDE'
		,EXPIRY_DATE = '20220101';
GO

Navigate as follow, to view this certificate in SQL Server Management Studio:

Note: Certificate expiration is not enforced when the certificate is used for encryption.

Now backup your certificate and database master key immediately as you need them in a recovery situation. To do that, execute the following script to backup certificate:

USE [master]
GO

BACKUP CERTIFICATE CertificateforTDE TO FILE = 'D:\TDE_Demo\CertificateforTDE.cer'
WITH PRIVATE KEY (
		FILE = 'D:\TDE_Demo\CertificateforTDE.key'
		,ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'
		)
GO

Execute the following script to backup the database master key of master database:

USE [master]
GO

-- Master key password must be specified when it is opened.
OPEN MASTER KEY DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'

BACKUP MASTER KEY TO FILE = 'D:\TDE_Demo\ExportedMasterKey.key'
ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1';
GO

Once successfully executed, verify that the master key and security certificate backup files are created in the location specified in script for example for in this demo its D:\TDE_Demo\ (see below):

Note: Save your master key and security certificate backup files in a secure location as you’ll need them when restoring the database on a different SQL Server otherwise the restore process will fail.

Now that we have created the database master key and the certificate in the master database, we are now ready to create the database encryption key for our database.

Execute the following script to create the database encryption key in AdventureWorks2012 database:

USE [AdventureWorks2012]
GO

CREATE DATABASE ENCRYPTION KEY
	WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CertificateforTDE;
GO

The AES_128 option specifies Advanced Encryption Standard (AES) with a 256 bit key length, and we protect the database encryption key with the AWTDECertificate certificate that was created in the Master database.

Note: SQL Server allow you to encrypt data with several encryption algorithms such as DES, Triple DES, TRIPLE_DES_3KEY, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.

The final step in the setup process of TDE is to enable it. This is accomplished by executing the ALTER DATABASE command with the SET ENCRYPTION ON argument.

Execute the following script to enable TDE on AdventureWorks2012 database:

USE [master]
GO

ALTER DATABASE [AdventureWorks2012]
SET ENCRYPTION ON;
GO

To verify that database is encrypted using TDE, right-click the database and choose option and you will see encryption option is now ON as shown in figure below:

Execute the following query, to find out what databases are encrypted using TDE:

USE [master]
GO

SELECT db.[name]
	,db.[is_encrypted]
	,dm.[encryption_state]
	,dm.[percent_complete]
	,dm.[key_algorithm]
	,dm.[key_length]
FROM [sys].[databases] db
LEFT JOIN [sys].[dm_database_encryption_keys] dm ON db.[database_id] = dm.[database_id];
GO

Recovery

Now perform the full backup of AdventureWorks2012 database using script below:

USE [master]
GO

BACKUP DATABASE [AdventureWorks2012] TO DISK = N'D:\Backups\AdventureWorks2012.bak'
WITH NOFORMAT
	,NOINIT
	,NAME = N'AdventureWorks2012-Full Database Backup'
	,SKIP
	,NOREWIND
	,NOUNLOAD
	,STATS = 10;
GO

Now test the restore the database of AdventureWorks2012 database as follow:

As you can see from above that database restore is successful on same instance.

Now try restoring this database on different SQL Server using script below:

USE [master]
GO

RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'D:\Backups\AdventureWorks2012.bak'
WITH FILE = 1
	,MOVE N'MyFileStream' TO N'D:\Databases\MyFileStream'
	,MOVE N'AdventureWorks2012_Data' TO N'D:\Databases\AdventureWorks2012_Data.mdf'
	,MOVE N'AdventureWorks2012_Log' TO N'D:\Databases\AdventureWorks2012_log.ldf'
	,NOUNLOAD
	,STATS = 5;
GO

As you can see, the restore process will fail with the error below:

This is because the database is encrypted with TDE.

To restore the TDE encrypted database on different SQL Server instance, you first need to restore the database master key and then the self-signed certificate that is used to encrypt the database encryption key.

Execute the following script to restore the database master key from master key backup:

USE [master]
GO

RESTORE MASTER KEY
FROM FILE = 'D:\TDE_Demo_Backup\ExportedMasterKey.key'
DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'
ENCRYPTION BY PASSWORD = '$tr0ngPa$$w0rd2';
GO

Next step is to create the SQL Server certificate on the second SQL Server using the Private Key backup of Principle SQL Server. To do that execute the following script:

USE [master]
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd2'

CREATE CERTIFICATE CertificateforTDE
FROM FILE = 'D:\TDE_Demo_Backup\CertificateforTDE.cer'
WITH PRIVATE KEY (
		FILE = 'D:\TDE_Demo_Backup\CertificateforTDE.key'
		,DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd1'
		);
GO

We are now ready to restore the database on second SQL Server.

Note: You must OPEN MASTER KEY first and then perform restore otherwise restore will fail.

Execute the script below which we executed earlier to restore the AdventureWorks2012 database:

USE [master]
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = '$tr0ngPa$$w0rd2'

RESTORE DATABASE [AdventureWorks2012]
FROM DISK = N'D:\Backups\AdventureWorks2012.bak'
WITH FILE = 1
	,MOVE N'MyFileStream' TO N'D:\Databases\MyFileStream'
	,MOVE N'AdventureWorks2012_Data' TO N'D:\Databases\AdventureWorks2012_Data.mdf'
	,MOVE N'AdventureWorks2012_Log' TO N'D:\Databases\AdventureWorks2012_log.ldf'
	,NOUNLOAD
	,STATS = 10;
GO

This time restore script executed successfully and AdventureWorks2012 database has now been restored on second SQL Server (see below):

Limitation of Transparent data encryption

  • TDE does not provide encryption across communication channels.
  • When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
  • The encrypting certificate or Asymmetric should be retained even if TDE is no longer enabled on the database. Even though the database is not encrypted, the database encryption key may be retained in the database and may need to be accessed for some operations.
  • Altering the certificates to be password-protected after they are used by TDE will cause the database to become inaccessible after a restart.