Datasheet

Chapter 1: SSIS Solution Architecture

Each Execute Package Task ties to a package either stored in the file system, or in the msdb database

package store. In many solutions, you will need to execute a set of packages at different times and with

different precedence. The master package allows this, and helps implement a rollback and checkpoint

system. Chapter 2 provides more coverage of this topic when discussing the building of a package

framework.

Server and Storage Hardware

Identifying hardware upfront is a “ Catch - 22. ” In other words, you may not know the total scope of your

project, or even what to expect from a load standpoint, but you are asked to estimate the hardware

requirements for the project.

Server Hardware

Here are some general principles to follow concerning the server hardware:

64 - bit — You can ’ t really purchase a server these days that is not an x64 processor architecture.

This is good news. But be sure that the x64 - bit version of Windows Server OS is installed, and

that the x64 version of SQL Server 2008 is installed.

Multicore processors — The biggest advantage to multicore processors (that is, dual core, quad

core, and six core, as of the writing of this book) is that for SQL Server 2008, you are only paying

per - socket. If you have a two - CPU quad core, the OS and SQL Server will see eight CPUs, but

you are only buying the license for two sockets.

Memory — Memory is relatively cheap and very valuable for SSIS. You should use at least 16GB

of memory, but preferably 32GB or 64GB, or more, depending on the intensity of your ETL and

how much you are utilizing the SSIS data flow.

You are now probably asking, “ But how many cores and how many servers should I target? ” There is no

definitive answer to that question because it is so dependent on your ETL solution complexity, volume,

and schedule.

If you must estimate, keep in mind that for a small - scale ETL solution that is dealing with less than

10GB of data (during your ETL), and that is working with a destination database that is less than

1TB, your ETL machine can probably run on a two - socket dual - core or quad - core server with 16GB

or 32GB of RAM.

For a medium ETL solution (where your ETL requires execution in a smaller window, and your recurring

volumes are larger and/or your destination database is in the multi - terabyte range), consider a quad

socket with multicore processors, and at least 32GB of RAM or possibly 64GB with the option to expand.

Larger solutions are really dependent on so many factors, and you should also consider scale - out ETL

(which is discussed in Chapter 10). Thus, recommending a general rule — especially if you are building

an SSIS server that will run all your enterprise ETL operations — is difficult.

Again, there is so much context that will really drive the hardware requirements, that you must evaluate

your situation and customize a recommendation on what you see in your solution.

❑

c01.indd 21c01.indd 21 9/24/09 11:26:38 AM9/24/09 11:26:38 AM