Mar 282008

Some of you may have visited the link to Microsoft’s whitepaper on the BI Metadata Toolkit only to find the referenced samples download link is not working. Many of us have always had a copy of the whitepaper, but not the samples download. We’ve received that missing download and are sharing it here. The folks at Microsoft are aware of the bad samples download link and are working to get that resolved.

The whitepaper focuses on sharing metadata between Microsoft’s BI tools and is a very good read. It touches on what is “metadata,” how can you use it, where to use it, and most importantly *why* you’d want to use it. Sarbanes-Oxley is mentioned and is important to consider when deploying SSIS solutions. Sarbanes-Oxley (a.k.a. the knee-jerk reaction to the Enron debacle), while broad in nature, basically boils down to enforcing accountability and separation of duties. The BI Metadata Toolkit can help in ensuring SOX compliance.

As part of the samples download, Microsoft includes the following tools:

  • Dependency Analyzer: loads into a database lineage data between SSIS, SSAS, and SQL Server
  • Dependecy Viewer: graphically see dependencies from the above tool
  • Data Source View: A DSV for use in Reporting Services (SSRS) to view dependency repository
  • Lineage Repository: A database called SSIS_META that can be used to house metadata from nearly any system
  • Reports
  • Report Model: For use with Report Builder to allow creating ad-hoc reports
  • Integration Services Samples: Sample packages to start auditing and viewing lineage on

BI Metadata Toolkit Whitepaper (.doc – 994 kb)
BI Metadata Toolkit Samples (.msi – 311 kb)

SQL Server 2008 – New table/column limits

 SQL Server  Comments Off on SQL Server 2008 – New table/column limits
Mar 112008

Here are some new*, upgraded limits as they apply to tables in SQL Server 2008:

  • Column limit per base table: 30,000 columns, which is up from 1,024 columns
  • Index limit: 1,000 indexes, which is up from 249
  • Statistics limit: 30,000 statistics, which is up from 2,000

These limits are still bound to the row size limit which is 8,060 bytes; however if a table has sparse columns in it, then that limit is reduced to 8,018 bytes.

*Note: These are not yet available in the CTPs but should be in the CTP6 refresh.

SQL Server 2008 Upcoming Features/Additions

 SQL Server  Comments Off on SQL Server 2008 Upcoming Features/Additions
Mar 102008

Here are some highlights of things that will be offered with SQL Server 2008:

  • Activity Monitor for the DBA: Finally a way to see, in an easy to use UI, information related to active sessions, wait states, file I/O, long running queries, etc… Oh, and you can launch Profiler from here as well.
  • Object Search: Users can now enter full or partial search strings to match on in the current database and results will be displayed in the new Object Explorer Details pane.
  • Intellisense: We all know what this is… It’s coming to Management Studio to aid in writing queries with the red squiggles and all!
  • Customizable Tabs: The query editor’s results can now have their tabs set with customized names
  • Launch Profiler: The option is now available to launch Profiler from within a query editor results window and it will automatically tie Profiler to the SPID of the query.
  • Permissions editing: The screens have been reworked to reduce the amount of clicks to set permissions
  • Performance Studio: A new offering that tracks historical performance metrics from SQL Server and the operating system by storing results in a repository that offers drill-through reporting.
  • Register servers once in a central location
  • Management Studio adds a new GUI to manage/create table partitions.
  • And finally (as far as my list and this article is concerned)….. A T-SQL Debugger in Management Studio.