Column Level Encryption in SQL Server

In my last article, Transparent Data Encryption (TDE) in SQL Server I talked about enabling encryption at the entire database level and in this article I am going to further discuss and demonstrate a more granular level or each individual cell or column level encryption in detail and how it differs from Transparent Data Encryption (TDE). For more information click here.

Transparent Data Encryption (TDE) in SQL Server

Encryption brings data into a state that cannot be interpreted by anyone who does not have access to the decryption key, password, or certificates. Though encryption does not restrict the access to the data, it ensures that if data loss happens, then in that case data is useless to the person who does not have access to the decryption key/password/certificates. Though when you use encryption, there should be a maintenance strategy for passwords, keys, and certificates. 

To meet the demands of regulatory compliance and corporate data security standards, SQL Server allows you to enable encryption at column/cell level or on the database level. You can even use file level encryption provided by Windows for database files.

Though there are several ways/levels to implement encryption in SQL Server, I am going to focus on Transparent Data Encryption (TDE) in this article, which was introduced in SQL Server 2008 and available in later releases. For more information click here.

Getting Starting with Change Data Capture (CDC) in SQL Server

Prior to SQL Server 2008 there was no in-built support to identify changed data set for incrementally pulling data from a source table and hence we had to write our own custom logic (for example by adding Last Created Date or Last Modified Date columns in the source table and updating it accordingly or by some other means) so that changed data sets can be identified for incremental data pull. Starting with SQL Server 2008 we have two different in-built mechanisms (please note, you don’t need to write code for leveraging these features though you just need to enable it accordingly as per your need) to identify DML changes (INSERT, UPDATE, DELTE) happening at the source table so that only changed data sets can be considered for data pull from the source table and to load into the data warehouse called Change Data Capture (CDC) and Change Tracking (CT). Please refer this article to learn more about Change Data Capture (CDC).

Part 1

Part 2

Snapshot Isolation Level in SQL Server - What, Why and How Part 2

Introduced in SQL Server 2005, Snapshot Isolation levels improve performance, however, there are some caveats to consider when using this feature. Some people use it frequently, as it minimizes blocking and improves performance/concurrency, without knowing its impact on maintaining versions in tempdb. Others stay away from it because of this extra overhead. Some people are confused by the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one variant where the other is needed or vice versa. In my last article (Snapshot Isolation Level in SQL Server - What, Why and How – Part 1) I talked about basic of isolation levels and snapshot based isolation levels and how they differ from each other in terms of performance and cost; in this article I am going to demonstrate how to use these isolation levels with examples; for more information click here.

Snapshot Isolation Level in SQL Server - What, Why and How - Part 1

Snapshot Isolation level was introduced in SQL Server 2005 and has been available ever since. Snapshot isolation levels improve performance but there are couple of things to take into consideration when using this feature. Some people use it frequently as it minimizes blocking and improves performance/concurrency without knowing its impact on maintaining versions in tempdb, whereas some people stay away from it because of this extra overhead. Some people get confused about the two variants of snapshot isolation level (Read Committed Snapshot Isolation (RCSI) and Snapshot Isolation (SI)) and use one where the other is needed or vice versa.  In this article, I am going to discuss what snapshot isolation levels are, their variants, why and when we should use them and how we should start using this feature with examples, for more information click here.

Managing and Monitoring SQL Server Service Broker Environments

Service Broker applications run in the background. You send a message and your command returns immediately. In the background, Service Broker keeps on trying to send the message to the destination service (Queue) until it puts the message there, times out or you end the conversation. These things all happen transparently to you and your sending application. So how would you troubleshoot your Service Broker applications if they're not working as expected, and how would you identify if something goes wrong? For for information click here.

Short-cut keys for commonly used tasks in SSMS - Part 2

We often overlook different SSMS shortcut keys which provide a boost in DBA and Developer productivity. In the second tip of this series (SQL Server Management Studio keyboard shortcuts - Part 1), I am going to further explain shortcut keys for managing Intellisence, debugging, running your code and many more...click here

Short-cut keys for commonly used tasks in SSMS - Part 1

As responsibilities are growing every day, a DBA or developer needs to improve his/her productivity. One way to do this is to use as many shortcuts as possible instead of using your mouse and the menus. In this tip we take a look at common tasks you may perform when using SSMS and the associated shortcut keys, for more details click here.

Understanding SQL Server Net-Libraries

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others? SQL Server provides different Net-Libraries, known as Dynamic-Link Library (DLL), to let clients communicate with SQL Server using different network protocols. These Net-Libraries specify which networking protocols to use while exchanging data back and forth between SQL Server and the clients. To learn more about it click here.

Understanding SQL Server Net-Libraries

I know that SQL Server and its clients interact with each other using different Net-Libraries or network protocols. But what are these Net-Libraries that SQL Server uses and how do each of these differ from the others. Also, in which scenario is one preferred over the others? To learn about it click here.

Getting Started with Extended Events in SQL Server 2008

In my last article, "An Overview of Extended Events in SQL Server 2008" I gave you an overview of Extended Events (XEvents) in SQL Server 2008, then I talked about its capability in terms of the number of events to trace and its deeper level of troubleshooting and finally I talked about the different components of Extended Events.
In this article, I am going to give some hands on examples to understand how this powerful tracing feature can assist you with your performance problems, for more details click here.

An Overview of Extended Events in SQL Server 2008

As a DBA, we are faced with performance problems which we need to diagnose, trace and troubleshoot. Although there are several different tracing and troubleshooting mechanisms (DBCC, SQL Trace, Profiler, etc...) available but none of these tools provide deep levels of tracing/troubleshooting, like identifying page splits, high CPU utilization, etc... So how can we diagnose, trace and troubleshoot these kinds of performance problems in SQL Server 2008. In this tip I talk about Extended Events of SQL Server 2008 and how it is different from earlier tracing and troubleshooting methods, for more details click here.

Event Notifications in SQL Server

SQL Server 2005 and onwards provides event notifications mechanism for tracking all events or changes happening at the database or instance level. This can be done with DDL triggers or SQL traces, but event notifications have the advantage of being asynchronous in nature and running outside of the scope of the transaction and hence it can run inside (or be a part of) a database application to capture defined events without using the resources allocated to the transaction. For more details, click here.

Configuring Proactive Caching in SQL Server

The proactive caching feature ensures a cube or a measure group partition or dimension reflects current data on its own. Depending on your choice of proactive caching enabled storage mode, interception of data change notification, storage location and update frequency will vary.

You use the Storage Settings dialog box in BIDS (Business Intelligence Development Studio) to set the proactive caching feature, storage location, and notification settings for a dimension, cube, measure group, or measure group partition, for more details click here.

Introduction to SQL Server Proactive Caching

Mostly data warehousing and Business Intelligence (BI) applications work on historical data, which are normally refreshed once in a daily, weekly or monthly basis. Although there are some near real time data warehousing and BI applications, their query performance is painfully slow. To help in this scenario, Analysis Services (Microsoft SQL Server 2005 Analysis Services and later versions) provides an amazing feature called "Proactive Caching". This new feature gives a query performance, similar to what you had with historical data, with near real time data and minimal impact on the overall system. As a BI developer, you just need to configure proactive caching, and then SQL Server Analysis Services (SSAS) will ensure that you get the performance of a fully processed data warehouse on near real time data, for more details click here.

Instant File Initialization for SQL Server 2005/2008

This article discusses Database Instant File Initialization, which instructs the OS to allocate the required disk space immediately before actually zero-ing out the content from the allocated space. This means the content of the allocated disk area remains there until SQL overwrites it, improving the performance and reducing the downtime. Read on to learn more...

Using a Data Access Application Block (DAAB)

Microsoft Enterprise Library provides several reusable software components (application blocks) which help developers simplify common development tasks, minimize development efforts and help address common problems related to performance/optimization/reusability release over release. These components are provided along with source code which developers can use "as-is" or customize per their projects need. Data Access Application Block (DAAB) is one of these components which simplifies the data access mechanism. Data Access Application Block provides a consistent data access mechanism to execute both Stored Procedures and inline SQL statements across projects/enterprises along with transaction support also. For more details, click here for Part1 article and click here for Part 2 article.

SQL Server Service Broker Components - Service, Conversation, Conversation Groups, Routes, SEND and RECEIVE Commands

In my last article SQL Server Service Broker Components - Message Types, Contracts and Queue, I discussed the Initiator, Target, Message Types, Contract and Queue components of SSBS. In this article I will be talking about the Service, Conversation, Conversation group, SEND and RECEIVE commands, Route and how these components are related to each other. For more details, click here.

Writing a Data Access Layer (DAL) in ADO.NET

Almost every application being developed stores data in some or other form to make it persistent, this could be either storing it in file system, Excel sheet, XML format or in relational database. So each time, when you develop an application, you are required to write code for storing and retrieving data from these data sources.
In this article, I am going to show you how you can create a Data Access Layer (to store, retrieve and manage data in relational database) in ADO .NET. I will show how you can make it data provider independent, so that you don't have to re-write your data access layer if the data storage source changes overtime and also you can reuse it in other applications that you develop. For details, click here.

SQL Server 2008 System Functions to Research Tables, Views, Indexes, Columns, Stored Procedures, etc.

SQL Server provides several system meta data functions to get property values for different object types. Each system meta data function has a set of properties you can query using the system meta data function. In this article, I am going to outline examples on the following functions, for details click here.

• OBJECTPROPERTY and OBJECTPROPERTYEX
• INDEXPROPERTY
• COLUMNPROPERTY