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.