Archive for the ‘SQL Server’ Category

Book Review–Pro SQL Server 2008 Reporting Services

Friday, October 2nd, 2009

I’m not sure whether to be excited or disappointed about this book.  The authors (Landrum, McGehee, and Voytek) try to explain SQL Server Reporting Services in a conversational tone, which I find much lighter and easier to read than most technical books. 

As an occasional user of other reporting software and tools, I am starting to learn SQL Server Reporting Services to have another option that a lot of people have already bought with their purchase of SQL Server.  This book helps, but also hurts….  🙂

 The book succeeds in describing SQL Server 2008 Reporting Services concepts and theory in a format that someone like myself new to Reporting Services can understand.  Unfortunately, a lot of the code examples have issues, and don’t work exactly as described (or they are assuming a level of knowledge I don’t have that means there are other things I should be doing not in the step by step instructions….)

 Overall, not a bad book, but not really a good one either.  Not sure I would spend the 40 bucks on it again.

 James

SQL Server TRY-CATCH error handling

Wednesday, July 22nd, 2009

Thanks to four very talented and inquisitive developers who were taking my Implementing a Microsoft SQL Server 2005 Database class last week, I was encouraged to delve a little deeper into SQL Server’s TRY-CATCH error handling structure.  (You know who you are, and thank you….)

First implemented in SQL Server 2005, the idea was to introduce structured exception and error handling into SQL Server.  The general idea goes something like this:

BEGIN TRY
      <SQL Statement(s) to attempt data modification go here>
END TRY
BEGIN CATCH
      <SQL Statements for Custom Error Handling Go Here>
END CATCH

If the code contained between the BEGIN TRY and END TRY statements runs successfully, then execution jumps to the next statement after the END CATCH statement.  If the code contained between the BEGIN TRY and END TRY statements produces an error with a severity level greater than 10 that are not fatal to the connection.

If you are familiar with the TRY-CATCH block in other Microsoft languages such as Visual Basic, you are used to seeing execution jump to the code contained in the CATCH block immediately after the error (exception) is thrown.  Unfortunately for consistency’s sake, this is not the case for SQL Server.

In SQL Server 2005 and SQL Server 2008, all code between the BEGIN TRY and END TRY executes before the error states are checked.  This means you can get yourself into trouble if you “assume” that it behaves like the TRY-CATCH block in other languages.  For example:

BEGIN TRY
     BEGIN TRAN
          <SQL Statement(s) to attempt data modification go here>
     COMMIT TRAN
     BEGIN TRAN
          <SQL Statements(s) to attempt a second data modification go here>
     COMMIT TRAN
END TRY
BEGIN CATCH
      <SQL Statements for Custom Error Handling Go Here>
END CATCH

If SQL Server’s TRY-CATCH behaved like Microsoft’s complied languages, then if the first transaction failed, execution would immediately jump to the CATCH block.  However, in SQL Server, both transactions would run, and while a failure of either would cause the CATCH block to execute, you could end up with one transaction committed you did not expect to be committed.

While SQL Server’s version of the TRY-CATCH is restricted to a single batch, it is not restricted to a single transaction.  If you are going to use the TRY-CATCH in SQL Server, you will want to make sure you only have one transaction or logical unit of work in the TRY block.

 Hope you find this useful;

 James

SQL Server Integration Packages–The Pain of Storage

Monday, June 1st, 2009

One of the largest pain points for me when Microsoft moved their ETL (Extract, Transform, and Load) technologies for SQL Server from DTS (Data Transformation Services) to SSIS (SQL Server Integration Services) has been their reduced editing functionality for packages saved in the MSDB database.

 As a DBA and not a developer, I have always been a fan of taking advantage of SQL Server’s built-in functionality to protect packages and their “secrets” (passwords for connecting to other systems, etc.) and to ensure that they get backed up along with the SQL Server system databases.  With the advent of SSIS, Microsoft has made this much more difficult to work with since there is no direct editing of packages stored in MSDB.  You end up having to export the package, edit it, and then re-import it into MSDB.  That is a pain in sensitive body parts for more than a couple of packages.

Developers advise us to use the file system to store our SSIS packages like they would store their VB or C# program code, projects, and solutions to work around these issues.  In my opinion, this has some significant disadvantages:

1.  To save secrets in the packages, you have to either encrypt the package with a password or with a user’s key.  If you use a password, that is one more thing to lose or forget, meaning no one has access to the secrets in the package or package source, or everyone does.  If you use a user’s key, then access to the secrets or package source is only available to that particular user.

2.  You cannot take advantage of SQL Server’s built-in roles in the MSDB database like db_ssisadmin to secure who has access to SSIS.  You are forced to rely on a combination of the security listed in Step 1 (user keys and package passwords) and NTFS file system security.  The introduction of having to worry about NTFS-level security adds yet another variable (and in large organizations, another team of administrators) to worry about.

Thankfully, someone has finally taken the time to write and make available an automated way to import packages to and export packages from MSDB.  The June 2009 issue of SQL Server Magazine has a reader solution that allows us to easily import and export SSIS packages from MSDB.  You can find the online version and code here:  http://www.sqlmag.com/Articles/ArticleID/101918/101918.html

Thank you Shaunt Khaldtiance and thank you SQL Server Magazine. 

Hope this helps someone else;

 James

Book Review–Delivering Business Intelligence with Microsoft SQL Server 2005

Monday, May 18th, 2009

 The first technical book review I’d like to post is for Delivering Business Intelligence with Microsoft SQL Server 2005.

If you are new to Business Intelligence (hereafter referred to as BI) theory, this is not the book for you.  The first chapters do a cursory review of the BI process and theory, but it is no where in depth enough for newcomers to the topic.

However, if you already have a grounding in basic BI concepts like OLAP, Data Marts, Measures, Dimensions, Facts, Star Schemas, Snowflake Schemas, etc. this book shines in walking you step by step in the setup, configuration, and management of basic pieces of Analysis Services, Integration Services, and Reporting Services. 

The strength of this book for me was the step-by-step Learn by Doing exercises.  These allowed me to get comfortable with the Business Intelligence Design Studio (BIDS) and its complex user interface.

If you are looking for a hands-on walkthrough of the BI components of SQL Server 2005, this is an excellent book to use.

 Hope this helps someone;

James