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

Comparing Data Warehouse Design Methodologies for Microsoft SQL Server

In my last couple of articles, I talked about the importance of a Business Intelligence solution, why it is becoming priority for executives, what a typical Business Intelligence system architecture looks like, etc. In this tip, I going to talk in detail about how a data warehouse is different from operational data store and the different design methodologies for a data warehouse. For more information click here.

Microsoft SQL Server Business Intelligence System Architecture

In my last tip in this series, I talked about how Business Intelligence (BI) systems have become a priority for top executives in order to optimize resource utilization as well as to remain competitive. I also talked about BI users and how BI relates to Data Warehousing and Business Analytics. In this tip, I am going to take this discussion even further in order to understand how Business Intelligence differs from an organization's OLTP systems, what a typical BI system architecture looks as well as the different components of the Business Intelligence system architecture. For more information click here.

Microsoft SQL Server Business Intelligence - What, Why and How

Because of the recent crisis or surge in competition, it has even become more important for organizations to optimize its resources and stay competitive. This has led Business Intelligence to become one of the top priorities for many organizations, especially for top management responsible for strategic decision making. But what is Business Intelligence? How it can help an organization in optimizing their utilization of resources?  How can it help the organization remain competitive?  Check out this tip to learn more.

Importing and Processing data from XML files into SQL Server tables

In my last article, I talked about how you can use an  FTP task in SSIS to download files from an FTP server. But what if the file you have downloaded is an XML file and you need to import this data from the XML file into a SQL Server table? How do you process/parse XML data into SQL Server tables? For information click here. 

Getting Started with Columnstore Index in SQL Server 2014

Column Store Index, which improves performance of data warehouse queries several folds, was first introduced in SQL Server 2012. Though it had several limitations, now SQL Server 2014 enhances the columnstore index and overcomes several of those earlier limitations. In this article series I am going to talk about how you can get started with using this enhanced columnstore index feature in SQL Server 2014 and do some performance tests to understand the benefits.

If you are new to columnstore index, you can refer my earlier articles on the topic to understand it better.

• Understanding New Columnstore Index of SQL Server 2012
• Getting Started with the New Columnstore Index of SQL Server 2012
• New Enhanced Columnstore Index in SQL Server 2014

For more information click here for part 1 and click here for part 2.

New Enhanced Column Store Index in SQL Server 2014

Column Store Index, which improves performance of data warehouse queries several folds, was first introduced in SQL Server 2012. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request. Apart from the benefits it provides, it has several limitations in SQL Server 2012. SQL Server 2014 enhances the columnstore index and overcomes several of the earlier limitations. In this article, I am going to talk about the new enhanced columnstore index feature in SQL Server 2014. For more information click here.

Managing Slowly Changing Dimension with MERGE Statement in SQL Server

In my last article I talked about Slowly Changing Dimension (SCD) Transformation, which is available out of box in the SSIS toolkit and can be used easily and configured quickly for managing smaller slowly changing dimension. The Slowly Changing Dimension Transformation is good if you want to get started easily and quickly but it has several limitations (I talked about these limitations in my last article, Managing Slowly Changing Dimension with Slow Changing Transformation in SSIS) and does not perform well when the number of rows or columns gets larger and larger.

In this article I am going to talk about some of the alternatives that you can take for managing larger slowly changing dimensions. For more information click here.

Managing Slowly Changing Dimension with Slow Changing Transformation in SSIS

As a data warehouse expert or as an ETL developer you will often come across scenarios in which you need to maintain and manage slowly changing dimensions. There are multiple ways to implement that in SQL Server and the easiest of those is using Slowly Changing Dimension Transformation in the data flow task of SSIS packages.

In this article I am going to provide you the steps and guidance needed to manage Slowly Changing Dimension with Slowly Changing Dimension Transformation in data flow task with an example. For more information click here.

Partitioning in SQL Server: Managing Sliding Window Scenario

Earlier articles of this series discussed what partitioning in SQL Server is; the different kinds of partitioning options, why and when we should go for partitioning and the benefits a partitioned table/index provides. Then I talked about different partitioning concepts like partition function, partition scheme, guidelines on choosing a partitioning column and creating a partition on table or index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning, with an example.

This article provides you the steps and guidance needed to manage a sliding window scenario, with an example. For more information, click here.

Partitioning Using the Wizard in SQL Server

In my earlier articles of the series, I discussed what partitioning in SQL Server is; the different kinds of partitioning options that we have, why and when we should go for partitioning and what benefits partition table/index provides. Then I talked about different partitioning concepts, such as partition function, partition scheme, guideline on choosing partitioning column and creating partition on a table or index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning.

In my last article of this series, I provided a step-by-step guide on creating a partition table/index using T-SQL commands. In this article I am going to provide you a step-by-step guide on creating partition table/index using the Wizards in SQL Server Management Studio (SSMS) and talk about Partition Aligned Index Views. For more details, click here.

Partitioning in SQL Server - Part 3

In my earlier articles of the series, I talked about what partitioning is in SQL Server; the different kinds of partitioning options, why and when we should go for partitioning and the benefits partition table/index provides. Then I talked about the different partitioning concepts like partition function, partition scheme, choosing partitioning columns and creating a partition on a table or an index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning.

In this article of the series, I am going to provide a step-by-step guide on creating a partition table/index. For more information click here.

Partitioning in SQL Server - Part 2

When a table grows large or huge in size it becomes really difficult to load new data, remove old data, maintain indexes and queries involving this table, and runs extremely slow. In this situation, we can partition the table, which essentially breaks the table and its indexes into logically smaller chunks called partitions. Further, with a partitioned table, maintenance works, lock escalation settings and compression settings can be applied individually at each partition rather than on a single huge table. Not only that, Query Optimizer references only partitions needed to serve the query requests and eliminates the partitions that don’t contain data needed by the query during query optimization and execution, and also it uses parallelism to process multiple partitions in parallel. SQL Server 2008 introduced partition table parallelism for better performance and for better resource utilization (of modern multi-processors hardware). With SQL Server 2012, we are now allowed to even create up to 15K partitions on a single table.

In my last article of the series, I discussed what partitioning in SQL Server is, the different kinds of partitioning options available, why and when we should go for partitioning and all of the benefits partition table/index provide. In this article I am going to examine the different concepts of partitioning in SQL Server and provide a step-by-step guide on creating a partition table/index. For more information click here.

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.

SQL Server Integration Services SSIS FTP Task for Data Exchange

An FTP (File Transfer Protocol) server is often used for data exchanges in many data integration scenarios. SSIS includes an FTP task to download and upload data files to and from an FTP location, but how does this work and how can we configure it for data file downloads and uploads? For more information click here.

Data tapping during SSIS package execution in SQL Server 2012

During SSIS package development we can add a data viewer to the data path of the data flow task to analyze the data passing through the data path or the pipeline, but once the package is deployed (or when not using Business Intelligence Development Studio) there was no built-in support for data tapping to analyze data for troubleshooting. I have heard we can do this now with SQL Server 2012, can you explain how it works? For information click here.

Creating SSIS Package Templates for Reusability

We often need to create similar SSIS packages which contain some common components such as connection managers, data flow components, log providers, event handlers, etc... So do we really need to create a package from scratch each time and add all these commonly used components in each package again and again. Is it possible to create a SSIS package with a basic structure/workflow and common components which can be used as template to create subsequent packages? To learn about creating SSIS package template click here.

FileStream and FileTable in SQL Server 2012

Data explosion brought a need to store both structured and un-structured data together in the database so that the benefits of the database system (like transactional support, backup and restore support, integrated security support, Full-Text Search support, etc.) can be leveraged. SQL Server 2008 introduced the FileStream data type to store unstructured data, such as documents, presentations, videos, audios and images, on the file system with a pointer to the data in the database.

Storage of un-structured data in FileStream improves the performance by leveraging the NTFS APIs streaming along with additional benefits of the database system.

SQL Server 2012 enhanced this capability even further by introducing FileTable, which lets an application integrate its storage and data management components to allow non-transactional access, and provide integrated SQL Server services - including full-text search and semantic search - over unstructured data and metadata. I am going to talk about these two new features in detail, for more information click here.

Getting Started with the New Column Store Index of SQL Server 2012

Column Store Index is a new feature in SQL Server 2012 that improves performance of data warehouse queries several folds. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request along with the newly introduced batch mode processing.

In my last article I talked in detail about the new Column Store Index, how it differs from regular indexes, and why and how it improves the performance of the same query by several folds if column store index is used, where it can be used and what its limitations are.

In this article I am going to take this discussion to another level and show how you can create column store index, how you can use index query hint to include or exclude a column store index, how the performance differs when using column store index vs. row store index and more, click here for more information.

Understanding new Column Store Index of SQL Server 2012

Column Store Index is a new feature in SQL Server 2012 that improves performance of data warehouse queries several folds. Unlike regular indexes or heaps, which store data in B-Tree structure (in row-wise fashion), the column store index stores data in columnar fashion and uses compression aggressively to reduce the disk I/O needed to serve the query request. This article discusses the new Column Store Index in detail, how it differs from regular indexes, why and how it improves the performance of the same query by several folds if column store index is used, where it can be used and what its limitations are, for more information click here.