Documenting a SQL Server 2005 Solution end to end

I had heard of this tool before, and it looks interesting if you ever need to document a full SQL Server 2005 solution.  It apparently documents SSIS, SQL and SSAS as well as SSRS.  The output shows data lineage, as well as the structure of your database.  Worth a look, pretty affordable too.  Too bad they couldn't make a version that worked as an add-on to Data Dude.  Here is the link to their homepage.

Load Testing SQL Server Analysis Services 2005

Have you ever had a need to load test SSAS and then report on the results?  Your life just got easier.  There is now a project on CodePlex for that very purpose.  Apparently MCS and other large internal projects have used it for a while, but it is now publicly available as a community project.  Download it here.

Taking the pulse of SQL Server

If you have ever been involved in a big SQL Server project, you might have wanted some easy way to figure out how things are performing on the server.  Fortunately, many other people have wanted to do the same.  There are several ways available now (and more to come in SQL 08, specifically for SSAS I believe).  You can use the SQL Server Health and History Tool (SQL H2) which tells you all kinds of interesting things about the internals of SQL and run reports against it.

The other new and cool thing that has come out is an actual data mart that is built on top of the man Dynamic Management Views (DMV) inside SQL Server.  The DMV's are what drive the reports inside SSMS, and you can use them to find out all kinds of things.  Now you no longer have to run them manually.  Thanks to the SQL Server Customer Advisory Best Practices Team, now you can just install SQL DMVStats Data Warehouse on your server and your good to go.

Here are the main features as listed on CodePlex:

• DMV data collection
• DMV data warehouse repository
• Analysis and reporting.

VSTE for DB Pros (Data Dude) Power Tools released

If you are using VSTE for DB Pros, you will probably want to pick up the newly released Power Tools (didn't they used to call these sorts of things "Power Toys"?).  It adds a lot to the product in the way of convenience with the dependency viewer (you don't have to pretend to rename something to see dependencies now), additional refactoring abilities, enhanced data generation and T-SQL code analysis among other things.  Download it here.

Read more about it at Gert Drapers' blog, including detailed explanations and screen shots.

Here's the list of new features, copied from Gert's blog:

• Dependency Viewer
• Refactoring
  • Move Schema
  • Expand Wildcard
  • Fully Quality Name
  • Refactor in to strongly typed DataSet definitions
  • Refactor Command Generator
• Data Generation
  • Sequential Data Bound Generator
  • Editors for the Data Bound Generator, Sequential Data Bound Generator and RegEx String Generator to make configuration easier
  • The RegEx editor also tries to interpret your CHECK CONSTRAINTs and create a matching RegEx expression that you can use to generate data values that match the constraint definition
  • The RegEx editor can also be used for interactively defining and testing RegEx expressions and evaluate the output visually, which makes it a lot easier to create the right RegEx expression for your value domain.
• MSBuild Tasks
  • SqlSchemaCompareTask; allows you to compare schemas between two database from the command line using MSBuild.
  • SqlDataCompareTask; allows you to compare the content of tables within two databases from the command line using MSBuild.
• T-SQL Static Code Analysis
•  Miscellaneous tools
  • SQL script pre-processor command-line utility, which will expand all SQLCMD includes and variable definitions (sqlspp.exe)
• Schema Manager API

SR1 for Visual Studio 2005 Team Edition for Database Professionals (DataDude)

This news is about a month old, but the RTM version of SR1 for VSTE for DB Pros has been been released and is available for download.  One of the most important things that it adds is the ability to have "database references", which is a 3 or 4 part reference to a database other than the one that you are currently working with.  Much-needed functionality.

SSIS Pivot Transform is harder than it needs to be

Don't get me wrong - I am glad that they included the pivot transform, and I recognize that the dev team likely ran out of time to implement any sort of UI, but Wow!  That thing is way more difficult to use than it needs to be.

For instance, and I don't doubt that it is somewhat user error on my part, but in my first attempt to get it working today with 3 columns (UserID, AttributeName, AttributeValue), the transform coughed, choked and wouldn't start.  It just gave me errors.  Due to lack of time, I had to set it aside for now.  I appreciate its usefulness, but it is painful to use, and due to the limited info on the Internet about it (there are roughly 2 useful articles/blog posts), I am guessing that a lot of others just work around it as well.  A shame, since the Unpivot transform has a UI and everything.

In summary - I am not saying it is broken, I am not saying it is not useful.  Just that it not only is difficult to use, but that documentation and samples are lacking as well.  Here are the few useful posts I found:

Ashvini Sharma's blog

MSDN

Database Journal

UPDATE: My friend Matt pointed me to a blog entry that he wrote while at our old company that is a still up in which he gives some tips on the pivot transform.

Useful information about Active Directory

Anyone who is new to Active Directory will likely have a hard time finding a single comprehensive source of information on the topic.  I think the problem is almost that there is so much information, that it is difficult to find the simplest things.  Take, for example, the User Properties dialog.  What if you simply wanted to tie the fields on this screen to the LDAP attribute behind each?  There is nothing out there (that is easy to find) in the Microsoft documentation that spells this out.  Fortunately R.L. Mueller put this very document (and others) into and Excel spreadsheet and RoudyBob posted it to his blog.  Now the rest of can benefit.  Here's the link to the blog post.

Here are the direct links to the various spreadsheets:

Spreadsheet of User Properties in Active Directory Users & Computers MMC
Spreadsheet of all Active Directory attributes
Spreadsheet of User Object Property Methods
Spreadsheet of attributes exposed by the WinNT provider

Interesting AJAX enabled SSAS client

I was checking my webmail today, and one of the ad-links caught my eye.  It was for an AJAX based SSAS client.  I clicked the link and it took me to IntraSight, by a company called Active Interface.  Screenshots are available at that link.  There are a lot of interesting SSAS web based interfaces floating around out there if you look hard enough.  Add this one to the list.  SharePoint integration is listed as one of the bullet points.  Pricing is pretty affordable: $1995/server.  It might be worth a look if you need a web-based OLAP tool.

Here is their feature list:

• IE browser-based SSAS client
• High-fidelity AJAX user interaction
• Interactive table and chart views
• Annotated public and private bookmarks
• View and edit generated MDX
• Sharepoint integration
• Excel, Word, and PowerPoint generation
• Publish static HTML site from bookmarks
• Web tier caching
• Search dimensions
• Filter member sets
• Sort and filter measures
• Specialized business-centric and industry-specific views

Referencing a Custom .NET DLL from within a SQL Server 2005 SSIS script task

It is not difficult to reference a custom .NET DLL from within an SSIS script, but there are a couple of things that you will need to do:

1. Sign your DLL
2. Put it in the GAC (copy it to c:\windows\assembly)
3. Put a second copy in the C:\Windows\Microsoft.NET\Framework\v2.0.xxxx folder

Once you have done all of these things, then it will show up in the list of available libraries when you attempt to add a reference to your script task.  I found this out the hard way, so hopefully this will help at least one other person.

More details are available here.