Attunity SQL Server-CDC for SSIS 2.1

I often design and write end-to-end business intelligence (BI) solutions, so I’m always looking for ways to make them faster and more efficient. One way to accomplish this is to have BI solutions deal only with new or changed data. This saves time and processing power. However, when you don’t have audit columns or control of the source database, your options are limited. I recently tested Attunity SQL Server-CDC for SSIS, which provides a solution to this problem. It’s part of the Attunity CDC Suite for SSIS, a set of solutions that use change data capture (CDC) technology to capture and replicate changes made between heterogeneous databases, such as SQL Server, Oracle, and DB2. (Each solution is sold separately.)

Attunity SQL Server-CDC for SSIS supports both 32- and 64-bit environments running the Enterprise, Standard, Workgroup, or Developer Edition of SQL Server 2000 or later. Installation is a breeze and takes less than 5 minutes. Using a standard Windows installer, you install it on a physical drive on the machine that runs SQL Server Integration Services (SSIS). (You can’t install it on a logical drive.) You need to be a member of the Administrators group on the SSIS computer.

To test the product’s ability to capture database changes, I set up a simple CDC solution using the AdventureWorks sample database. The software is tightly integrated with SSIS, so when I opened Business Intelligence Development Studio (BIDS), the first thing I saw was a pop-up window that showed me how to get started. I closed this window and created a new Integration Services project. I then right-clicked the project name in Solution Explorer and selected Attunity SQL Server-CDC from the drop down menu, which gave me an additional menu of Attunity options. I clicked the Configure CDC Service option, as Figure 1 shows. This brings up a wizard that walks you through creating a CDC service for your source database. This Windows service is the heart of the CDC solution

Figure 1: Creating a CDC service

When creating the CDC service, you can configure it to use Windows or SQL Server authentication to connect to the target database. (Using SQL Server authentication is easier and recommended.) You need to create a CDC service for each source database.

Next, I clicked the Generate CDC Packages option, which brings up a wizard that walks you through creating CDC SSIS packages. When creating a CDC SSIS package, you can create table groupings, which allow you to split tables up among packages for separate processing. You can also map source columns to destination columns if desired. Although the product itself doesn’t perform any transformations, you can add them to a CDC SSIS package after it’s created.

Using the two wizards, I created a complete CDC solution (i.e., the CDC service and two CDC SSIS packages) in about 10 minutes. You can use a SQL Server Agent job to schedule CDC SSIS packages to run at any frequency. Each time they run, any accumulated changes are moved to the target database.

The CDC solution is lightweight (i.e., low overhead in SQL Server) and doesn’t require audit columns or any alterations to the source database, as it uses the transaction log to capture changes. If the CDC service stops for some reason and there are changes that haven’t been captured, the system prevents the transaction log from being truncated until those changes are captured, so you won’t lose any data.

The Attunity product has two management consoles: a Microsoft Management Console (MMC)-based console and a web-based console. One thing that I didn’t like is that the product provides alerts only through the web-based console and provides status information only through the MMC-based console. (A company representative told me that the two consoles’ functionalities will be merged in a future release.) I also found that I couldn’t create a CDC service while the MMC-based console was open.

Attunity SQL Server-CDC for SSIS is a great product and definitely worth a look if you need to efficiently capture data changes. At $15,000 per processor, it might be a little pricey for a small company. You have to consider, though, that an employee who writes and maintains an extraction, transformation, and loading (ETL) system can cost around $50 per hour and a consultant can cost as much as $200 per hour. So, the fact that it’s quick and easy to use and creates solutions that are lightweight and easy to maintain might make it worth the price.

Attunity SQL Server-CDC for SSIS 2.1

PROS: Uses the transaction log to move data changes to a destination database, which puts less strain on the network and server; wizard-driven interface saves a ton of development time when creating SSIS packages; CDC solutions are easy to maintain and work on several popular database platforms

CONS: Is a little pricey; the two management consoles don’t offer the same functionality; can’t create CDC services while the management console is open

RATING: 4 out of 5 diamonds

PRICE: $15,000 per processor for the first 2 processors (minimum of two processors); discounts available for additional processors

RECOMMENDATION: Attunity SQL Server-CDC for SSIS 2.1 is a great product. Although it’s a little pricey, it can easily be worth the money by saving you SSIS development and maintenance time as well as allowing you to do more with fewer servers and less network traffic. If not for the price, I would have given it 5 out of 5 stars.

CONTACT: Attunity • 866-288-8648 • www.attunity.com