SQL Server 2012 ETL Framework Features in the New SSIS Project Deployment Model

/0 Comments/in /by

When moving to SQL Server 2012, ETL developers would need to rethink custom ETL frameworks. If you embrace the new project deployment model, SQL Server 2012 offers a lot of plumbing features out of the box. Jen Underwood wrote a great SQL Server 2012 ETL Framework Features in the New SSIS Catalog article about what supported and what’s missing. I guess in most cases developers will find that they’ll need to implement incremental features on top of the Microsoft provided toolset that are not supported, such as restartability and execution control, while letting SSIS 2012 handle logging and performance reporting. Another great resource for getting started with the new project deployment model is the SSIS Logging in Denali blog by Jammie Thompson.

We ran into an interesting snag when validating and executing packages using the new project deployment model with Windows integrated security:

  1. Developer initiates the validation and execution in SSMS from his machine.
  2. Packages are deployed to a dedicated SQL Server and packages use Windows integrated security to connect to a SQL Server database on a different server.
  3. The source SQL Server database is on a different server.

As you’ve probably guessed it, this scenario requires configuring Kerberos delegation and registering SPNs for both the SQL Server hosting the SSIS packages and the SQL Server hosting the source database. That’s because validation and execution happen under your login and a double-hop is required to delegate your credentials to the source SQL Server. This can be avoided by using standard security (username and password) to establish connections from your packages to the source SQL Server. This wasn’t an option in our case because customer requirements dictated using Windows security to SQL Server.

I personally believe that the new Integration Services enhancements alone warrant upgrading to SQL Server 2012.

Reducing Analysis Services Index Processing Time

/0 Comments/in /by

Scenario: Processing a 100 GB SSAS 2012 cube on a dedicated DELL PowerEdge server (128 GB RAM with 32 logical cores) reveals that 50% of the time is spent on processing the cube indexes and building aggregations with only 20% average CPU utilization and not even a small dent to the memory consumption. It was clear that SSAS doesn’t utilize the server resources and additional tuning is required to increase the parallelism of this phase.

Solution: Following Greg Galloway’s advice, we’ve changed the OLAP\Process\AggregationMemoryLimitMin to 2 and OLAP\Process\AggregationMemoryLimitMin to 3 to increase the parallelism of the index processing. This led to 25% reduction of the overall processing time and increased the CPU utilization to 70-80%. As to how we derived to these numbers, James Rogers has a great write-up. For the sake of preserving the precious formulas:

AggregationMemoryLimitMax=Ceiling(100/(<number processing cores>-2))

AggregationMemoryLimitMin=Ceiling(100/((<number processing cores>-2)*1.5))

In case you’re curious about what these setting do, the amount of memory needed to build an aggregation is unknown up front. The engine has to estimate it based of certain factors (estimated rows, granularity attributes, measures, etc.) – and it uses the AggregationMemoryLimitMin value to ensure that if it’s estimate is wrong, that at least there will be a sufficient buffer for underestimation. Similarly, if the estimate is wrong in the upward direction, it will use the AggregationMemoryLimitMax value to trim the max value downward.

Presenting at SQL Saturday

/0 Comments/in /by

I have a talk “The Personal-Team-Organizational BI Conundrum” at SQL Saturday on April 14th at 9 AM.

Confused about the BI alphabetical soup? Not sure which one makes sense for your organization? Join this session to learn you can use the Microsoft BI platform to address various analytical needs. Discover how to implement the Personal-Team-Organizational continuum on a single platform. I’ll also discuss how Tabular and Multidimensional compare.

I hope you can make it. SQL Saturday is always a good show and this year we have great speakers and great BI content.