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

Extending SSIS Capabilities by Creating Custom SSIS Components for SharePoint

SQL Server Integration Services (SSIS) - a component of SQL Server - is an extensible platform for building high performance data integration (ETL - Extraction, Transformation and Loading) and workflow solutions. The extensibility part of SSIS comes from the fact that, the developers have ability to create custom components if the in-built components of it do not suffice the specific need. The platform makes it very simple to embed your own code into control flow or data flow pipeline.

This article demonstrates how to create custom SSIS components (connection manager, source component and destination component) that integrate data to/from SharePoint lists and work like any other native components of SSIS utilizing the SSIS pipeline for better performance. For more information click here.

Let's Explore Excel add-in of Master Data Services of SQL Server 2012 - Part 2

SQL Server 2008 R2 introduced Master Data Services (MDS) as Master Data Management (MDM) platform for managing enterprise master data centrally in a consistent, clean and up-to-date manner. There were three ways to manage master data in MDS.  First, was the Master Data Manager which is a web based User Interface. Second, was loading data through the staging process. Third, was to use the MDS WCF services to programmatically manage master data. These were fine options, but there were many users who wanted to manage data in Microsoft Excel in bulk than managing each single record/member at a time in Master Data Manager UI. The SQL Server team heard this requirement and introduced a brand new Excel add-in for SQL Server 2012 for MDS to manage master data. In my last tip I talked about how to get started with MS Excel MDS add-in to manage master data stored in Master Data Services database in Excel. In this tip, I am going to talk more about editing, creating, combining master data, creating entities and how it can be used in conjunction with Data Quality Services for data matching. For more information click here. 

Excel add-in for SQL Server 2012 Master Data Services - Part 1

SQL Server 2008 R2 introduced Master Data Services (MDS) as Master Data Management (MDM) platform for managing enterprise master data centrally in a consistent, clean and up-to-date manner. There were three ways to manage master data in MDS.  First is the Master Data Manager which is a web based user interface.  Second is loading data through a staging process.  Third  is using the MDS WCF services to programmatically manage the master data. These are fine, but there are many users who want to manage data in MS-Excel in bulk rather than managing each record one at a time in the Master Data Manager UI. The SQL Server team heard this requirement and introduced a brand new Excel add-in for MDS server in SQL Server 2012 to manage master data. With this new option, now the questions are, how to get started with this new add-in, how to manage data in bulk with this add-in and how publish it to MDS server?  Check out this tip to learn more.

Getting Started with Master Data Services (MDS) In SQL Server 2012

Master Data Services is a new feature introduced in SQL Server 2008 R2 and further enhanced in SQL Server 2012. Master Data Services is an extensible Master Data Management (MDM) platform, which is built on top of the SQL Server database engine and Windows Communication Foundation (WCF).

MDS allows you to create a centralized hub for creating and managing enterprise master data and provides many functionalities, to learn more click here.

Creating a Custom Report Template in SSRS

When we start creating reports at the enterprise level, we need to ensure the consistency in the look and feel of reports throughout the organization. For example, the header of all reports should have the company logo and address whereas the footer should have other company information or page layout information across all reports in a consistent manner. Now the challenge is, at the enterprise level there might be several departments/developers creating reports, so how do you ensure report layout consistency?
Fortunately, SQL Server Reporting Services (SSRS) allows us to create standard/custom report templates with the desired report layout and use the same custom template every time when creating a new report. With this, you can ensure consistent report layout across departments of the organization, for more information click here.

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.

Creating Indicator and Gauge Report in SSRS - SQL Server 2008 R2 - Part 2

SQL Server Reporting Services allows you to embed indicators and Gauges in your report to analyze the data and its state. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent state value of Key Performance Indicator (KPI).

In my last article (Creating Indicator and Gauge in SSRS - SQL Server 2008 R2 - Part 1), we discussed the details of Indicator and Gauge, and created a step-by-step tablix report with two levels of grouping, which  I will be using further to add different types of indicators and gauges to it, for more click here.

Creating Indicator and Gauge Report in SSRS - SQL Server 2008 R2 - Part 1

SQL Server Reporting Services provides several ways to analyze the data; a few of them are creating reports with indicators and Gauges. Indicators are minimal gauges that convey the state of a single data value at a glance and are mostly used to represent the state value of Key Performance Indicator (KPI, a measurable value which has business significance with a specific target or goal that indicates whether things are going good or bad). Indicators can be used in either dashboards or free-form reports but more commonly are used in tabular or matrix reports to visualize data in rows or columns. There are several types of indicators, to learn more about these indicators and how to use them click here.

Creating Chart Reports in SSRS SQL Server 2008 R2

SQL Server Reporting Services provides several ways to analyze data; one of them is creating Chart reports. With Chart reports you can analyze the aggregated information (either on small or large volumes of data) at a glance. But before you start creating Chart reports it’s essential to understand and prepare the data to be analyzed so that you can choose the appropriate chart type and create the report quickly and efficiently.

In this article, I am going to demonstrate how you can go about creating, modifying and beautifying the chart report easily and quickly, for more information click here.

Creating Map Drill Down Reports in SSRS - SQL Server 2008 R2

In my last article, I talked about creating map report based on ESRI shapefile. In this article I will be talking about creating a map report with drill down functionality, so basically I will be creating two reports as follows:

• Main report based on SQL Server Spatial data for showing countries wide sales
• Drill down map report based on ESRI shapfile for showing statewise sales for the selected country

The idea behind this article is to demonstrate how you can use a combination of sources of spatial data and create a map drill down report. Also I will explain how you can import ESRI shapefile to SQL Server as a spatial data type and use it in a map report; for more information click here.

Creating a Map Report in SSRS - SQL Server 2008 R2

SQL Server 2008 R2 brought several new features into the SSRS (SQL Server Reporting Services) arena. In the data visualization category, we now have three additional ways to display and visualize/analyze data in the reports:

• Sparkline and data bars – Sparkline and data bars are normally used inside tables and matrices to analyze the trend and series and compare them with each other.
• Indicators – If you are aware of KPIs, the concept of indicators is not new to you. As the name implies, indicators have icons to represent trends (up, down or flat), progress state, conditions.
• Maps – It allows you to create maps or maps layers to let you visualize data against a geographic background.

In this article, I am going to demonstrate how you can create map reports to analyze your data against a geographical background and then in the next article I will be talking about creating a map report with drill down functionality, click here for more information.

Data Driven Report Subscription in SSRS 2008 R2

In my last article I talked about standard subscription in which we need to specify whatever parameters the report expects at the time of subscription creation and which cannot be changed at runtime; in this article we are going to discuss and use data-driven subscription.
In data-driven subscription the data or parameter values required during execution of the report can come from a query from a database including recipient list, delivery method and parameter values needed for each recipient's report execution, which makes it a dynamic subscription that gets required data during run time from the external data source, click here fore more information.

Standard Report Subscription in SSRS 2008 R2

You can set the subscription for the report in which case report server processes the subscription, generates the reports and delivers it to its intended audience via email delivery or file share delivery on the defined schedule, to learn more about it click here.

Report Snapshots in SSRS 2008 R2

You can set SSRS to create report snapshots, which show data at a point of time or you can subscribe to your published reports.

An SSRS report snapshot represents a report that contains data retrieved at a specific point of time along with layout information in the form of intermediate rendering format. SSRS allows you to create report snapshots on defined schedule or on demand whenever you need to; a report snapshot is stored in the ReportServer database, for more information click here.

Report Caching in SQL Server Reporting Services 2008 R2

SQL Server Reporting Services (SSRS) is a server based scalable and extensible platform for authoring, deploying, executing and managing reports based on a variety of data sources. SSRS allows us to create interactive, tabular, graphical (using data visualization controls) or free form reports from relational, multidimensional (using MDX or DMX) or XML data sources. Furthermore it allows you to view/export your reports in a variety of formats. You can specify to create report snapshots, which show data at a point of time or you can even subscribe to your published reports.

To improve the performance of report processing, SSRS lets you enable caching for the report so that if the same report request comes again, the stored copy can be rendered in the desired format and served instead of processing it from scratch, for more information click here.

Creating and using Report Parts in Report Builder 3.0

Report Builder 3.0, which comes with SQL Server 2008 R2, is a new enhanced report development and report authoring tool intended to be used by business users for ad-hoc reporting. Report Builder 3.0 has numerous new features and one of them is Report Part Gallery, which allows you to create and publish report parts and reuse them in other reports. In other words, you can publish different report parts on the report server and reuse them in different reports when required, without recreating them from scratch, for more information click here.