VS Studio 2010 Table Designer

Embarrassingly enough this one eluded me for quite some time. I was surprised to stumble onto the fact that there is indeed table designer functionality within Visual Studio. It is very much comparable to the SQL Server Management Studio table designer.

However, as far as I can tell, it is dependent on a database server connection which goes against one of Microsoft's biggest database project selling points - disconnected development.

If you're like me and haven't been able to find this functionality I included a screen shot below to help you find it. The key is the Server Explorer tab.

1. Create a connection if you don't already have one.
2. Expand your connection and right-click on Tables -> Add New Table.

Once you have defined your table you can use the Table Designer - > Generate Change Script . . . menu option to create a DDL script. If you save the definition it will actually save it to the database. 

Visual Studio 2010 Database Projects

I won't go into the details of how to create and use Visual Studio 2010 database projects here (since I am developing a thorough document on the subject) but what I will do is tell you why they are indispensable. In the end it comes down to one thing - PRODUCTIVITY.

If you are thinking about using Visual Studio 2010 database projects but aren't quite sure why you should, read below. If you find these arguments compelling enough than go and give it a try.

1) Source code structure - Visual Studio database projects force the organization of all database source code into an intuitive and somewhat customizable structure that makes all objects easy to find, modify, and version.

2) Dependency checking - The Visual Studio build engine performs dependency checks. If a column name is changed it will immediately identify any dependent database objects such as views and stored procedures that have been adversely affected, saving the time of tracking down bugs after the code has been deployed which eliminates the time necessary to fix the bug(s) and redeploy the database to the affected environments. It can also perform dependency checks between databases. If you have a core application database as well as other system databases such as an audit/logging database, the tool can perform cross-database dependency checks whether or not the other database exist as Visual Studio database project.

3) Deployment scripts - The Visual Studio database project allows for pre and post-deployment scripts that can do things like move files to certain places in preparation for a database deployment, check versions to ensure compatibility prior to deployment and stop deployment if something is not right, populate tables with reference/lookup data after the deployment.

*Keep in mind that deployment includes both the initial creation/installation of the database as well as the upgrade of an existing database that is out of date and even the rollback of a database to a past version.

I have used this tool in conjunction with InstallShield to create install/upgrade processes for a commercial software product. It worked great.

4) Parameterization of any script values - The project allows for the parameterization of just about anything within the source code such as database name, database server, database file locations, etc. so that the same set of source code can be deployed to any environment, without any code modification, by passing in environment specific parameters. Any number of configurations and corresponding sets of variables can be created (e.g. Dev1 Local Dev, Dev2 Local Dev, QA, UAT, PROD) allowing for a two-click (choose the environment and click deploy) deployment to any existing environment.

5) No need for deployment “sub-system” code - The Visual Studio deployment engine removes the need for an abundance of code that would otherwise be necessary in a manual deployment environment. Code for things such as object (table, view, stored procedure, index, function, etc.) existence checks and drops, foreign key drops and recreation, wrapping of scripts in transactions, etc. is automatically generated by the tool. All that is needed is a single “create” script for each object. This dramatically reduces the complexity of source code by eliminating the need to manually create all the deployment scripts necessary to create or upgrade a database.

6) Fast database deployment on any machine - Visual Studio comes with freely distributable executables that provide the ability for non-database developers (ETL, Report, Dashboard developers) to deploy a complete data warehouse (or any other kind of database) with a single command line, .bat, or PowerShell script without the need for a Visual Studio license.

7) Complete build and deployment solutions - Using build automation tools such as NANT or MSBuild we have the ability to package up the above command line deployment capability with tools like the freely distributable SSIS dtutil executable that automates the deployment of SSIS packages and/or RS.exe for report deployment. Taken to this level, we can deploy many if not all SQL Server related software artifacts using a single call to the automated build (NANT, MSBuild) or install (InstallShield, Wise) tool of choice.

I'm sure this doesn't cover everything but it should give you an idea as to how Visual Studio database projects can make all things database source code related much easier and more efficient. Getting started takes about a half hour if you have a pre-existing database and use the import wizard. I strongly urge you to conduct your own experiment so that you can begin to visualize just how powerful a tool this can be.

SSIS Foreach Loop Container: Continue on Error

I recently had a project that involved processing FTP log files. I chose to use SSIS for the task as it is great for this type of flat file ETL work. One of my requirements was to continue loading remaining log files even if an unhandled exception was encountered during the processes.

What I found is that the default behavior of the Foreach Loop container is such that any unhandled exceptions within the container cause it to exit the loop. Not what I wanted.

What I did want was for the container to do something on error (move the file to an error folder and send a notification email) and continue on to the next file. You can see this in the screen shot of the container below. Note the failure precedence constraint on the right hand side (red arrow).

Just like a C# application, unhandled exceptions "bubble-up" to their parent and if none of the objects handle the exception the program fails. In this case I wanted to know of the error, fail the child executable (in this case it was a Data Flow) but continue executing the parent (Foreach Loop Container).

It turns out this is pretty easy to do. The first thing you need to do is create an OnError event handler for the Data Flow (or any other child executable). Once the event handler is created show the system variables in the variables window, locate the Propagate variable and set it to false.

The Foreach Loop Container is now aware that the Data Flow has failed but the exception is handled in the child and therefore does not cause the container to fail and exit. We can now use the failure Precendence Constraint to do our additional processing (move the file to an error folder and send a notification email).

SQL Azure - Very Cool - Very Big Deal Breakers

THIS POST REQUIRES AN UPDATE WITH CHANGES MADE TO SQL AZURE

For anyone that doesn't know what SQL Azure is, it's essentially SQL Server hosted in Microsoft Data Centers and accessible via the internet. Throw the word cloud in there if you want the executives and sales guys to listen. A competing product would be Amazon's SimpleDB.

SQL Azure is cool. No doubt about it. In a matter of minutes I can provision a SQL Server database somewhere in the ether and connect to it from my machine using the same familiar tools like SQL Server Management Studio and Visual Studio. Reporting Services has a SQL Azure connection type and SSIS allows for SQL Azure sources and destinations using ADO.NET type connection managers.

The benefits of having your database be a SQL Azure database are almost in lockstep with anything "in the cloud." You don't need your own hardware, you get high availability, no software patching/updating, etc. All that stuff becomes someone elses responsibility. Additionally you get scalability and you pay for only what you need. In the traditional, on-premise model of hosting your own database(s), if you have a business where you need to support peak load that only occurs a few days or weeks out of the year you have to pay for that horsepower in both hardware and software licensing costs. Move those same databases to SQL Azure and now you have a platform that scales to your needs on-demand and you only pay for the resources consumed. There's no doubt in my mind that this model will become ubiquitous just as virtualization has become over the years.

So let's move all of our SQL Server databases to SQL Azure! Well, not quite yet. There are some major limitations that will keep 99% of "real" applications off of this platform in the near-term. My biggest "deal-breakers" are listed below.

Database Size Limitations
SQL Azure databases are limited to 5GB for Web Edition databases and 50GB for Business Edition databases. Of course there are plenty of databases that might fall within these limitations but I wouldn't want to risk my job on deploying to this platform only to hit this limitation and have a catastrophe on my hands.

No Windows Authentication
The platform currently supports only SQL Server logins. Intuitively that makes sense since the server doesn't live in your domain but this can become painful if you're an organization that has standardized on using Windows authentication. It might also mean major rework of existing applications that rely on user-based permissions.

No local restore of backups
This one kills me and I think many others as well. While you can now restore a database to another SQL Azure database you cannot bring a backup to your local environment and restore it on a local server. I imagine this could make things rather difficult. Microsoft recommends trasferring your database locally by using SSIS. No thanks.

No Replication
For a lot of organizations this might not matter but if you are an organization that relies heavily on replication you simply can't move a subscriber or publisher out to the cloud.

No SSIS, SSRS, or SSAS
These services are not available on the SQL Azure platform. I would expect that they will be at some point but if I was a betting man I wouldn't put any money on them coming anytime soon. That doesn't mean you can't use these services on local servers while consuming Azure databases but this could hurt your bottom line. I know plenty of organizations that run any combination of these services on one physical machine without issue. So now if you're paying for the hardware to support SSRS why pay a subscription fee to move your database out to the cloud? Maybe you can make some arguments around high availability or scalability for you relational engine but if you're running them on the same server to begin with then these are probably not a concern.

Lack of Profiler
You can't attach a profiler instance to a SQL Azure database. That could get rather dicey when trying to track down all sorts of issues. Seems scary to me. There might be alternative but I'm not aware of them.

Bottom line: SQL Azure is cool, it will mature to the point that it will begin to become adpoted, but it ain't quite ready from prime time.

RS.exe and rss Scripts for Automation of SSRS 2008 Administrative Tasks

Download .rss script

Most people that work with SSRS (2005, 2008, 20080 R2) are aware of two ways to deploy Reporting Services catalog items (reports, data sources, shared datasets, images, etc.). These include deploying directly from BIDS (Visual Studio) and by using Report Manager to upload files. There are also a number of web services available that enable us to do this this in a programmatic, automated fashion that allow for things such as dynamic data sources so that we can deploy reports to any environment without having to modify data source properties manually and specifying where in the Report Manager folder hierarchy we want our reports installed - again without having to modify data sources or data sets or references to either.

The following web services are available with for administrative tasks in SQL Server 2008 R2. Note that there is also a ReportExecution2005 web service that provides methods for actually executing reports.

1. ReportService2005
2. ReportService2006
3. ReportService2010

ReportService2005 was used for SSRS instances that are running in native mode.
ReportService2006 was used for SSRS instances that are running in SharePoint integrated mode.
ReportService2010 ties 2005 and 2006 together so that one web service works with both native and SharePoint integrated mode.

While you can work directly with these web services using your programming language of choice, Microsoft was kind enough to create RS.exe. This console application acts as a wrapper for these web services. It is installed with SQL Server and like any other executable it can be called by tools like NANT, MSBuild, InstallShield, etc.

My need for this tool arose when I was developing a set of reports for a commercial product that required installation through InstallShield. The development environment included ANT which we also had call RS.exe for automated deployment in an automated build environment.

Now I'll show you how to use RS.exe from the command line. I won't go into detail about how to use it from specific tools as that should be straightforward for anyone that is familiar with the tool they are using.
Below is a sample RS call with three input parameters.
-i is the file that contains your rss script. More on that later.
-s is the address of your ReportServer web service
-e is the web service end point. Remember from above that there are multiple web services available and you can target any of these endpoints using RS.

rs.exe
  -i SSRSDeploy.rss
  -s http://localhost/reportserver
  -e Mgmt2010


Now that we know how to call RS, the key to making it useful is your custom .rss (Reporting Services Script). It is in these scripts that we can write custom code to deploy reports, data sources, images and create folders as well as delete items.

I have posted a sample rss script that has the following generic methods.
CreateFolder: Creates folders in Report Manager
PublishItem: Publishes reports and shared datasets
PublishImage: Publishes images
DeleteItem: Deletes any item

These methods are essentially wrappers around the rs.CreateCatalogItem, rs.DeleteItem, rs.CreateFolder, rs.SetItemDataSources, and rs.SetItemReferences methods. They add additional exception handling and deal with the nuances of publishing reports in locations different than specified in your Visual Studio project and .rdl files.

Note the use of what are referred to as Global Variables. You can pass values into your rss scripts by using Global Variables. You don't declare these variables you simply refer to a non-declared "variable" in your rss script and RS is smart enough to know that you want to set them at the command line. The way to set them is by using the -v argument. See below for another example.

rs.exe
  -i SSRSDeploy.rss
  -s http://localhost/reportserver
  -v rootPath="/Sample"
  -v DWServerName="localhost"
  -v DWDatabaseName="MyDataWarehouse"
  -v sourceFilePath="C:\SourceCode\SSRS"
  -e Mgmt2010

With the sample script you should be able to quickly and easily create an automated process for working with your Reporting Services instances.

Download .rss script

Nested Loops Join - No Join Predicate

I was recently troubleshooting a severe query performance issue when I ran into a query plan that used a nested loops join to combine data from two tables. The join was 97% of the cost. When I looked closer I saw that there were no join predicates specified. The join in the query looked pretty straightforward to me and used a single column on each side. Why would the optimizer do what equated to a cartesian join?

It turns out the query was a little more complicated than it looked. I was actually joining two views that were part of a view-based (non-persisted) dimension model. I was joining on logical primary and foreign keys which made sense.

When I dug into the views I found that one of the key columns was based on a user-defined fuction that was converting a datetime to an integer value so that it could be used to join to a date dimension view. Once I changed the underlying view to apply the same logic as the UDF at the view level, so that a call to the UDF was unecessary, the query executed within a second as expected.

Other behavior that I noticed was that if I changed the inner join to a left join, the optimizer came up with a different much more efficient plan. This appears to be a flaw in the optimizer but I would like to speak to someone at Microsoft before making that claim.

The lesson learned here is that if you have a poorly performing query due to a nested loops join with no join predicate(s) it's not ncessarily the case that you're missing join criteria in your query (as all the posts I was able to find seemed to point to). The culprit could be a UDF on a join column.

Querying System Views for Column Names

I often find myself needing a list of columns from a table or a view for a query, documentation, troubleshooting, etc. Easy enough to drag and drop them into a query window using SSMS but that's a pain. I have better things to do with my time. All of this information is available through SQL Server system views. The following query is also ANSI compliant.

SELECT

  ORDINAL_POSITION
  ,COLUMN_NAME
  ,DATA_TYPE
  ,CHARACTER_MAXIMUM_LENGTH
  ,IS_NULLABLE
  ,COLUMN_DEFAULT
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'Customer'
ORDER BY
  ORDINAL_POSITION ASC;

You can do something like the following to give you a comma separated list that can then be used for a query.

SELECT

  COLUMN_NAME + ','
FROM
  INFORMATION_SCHEMA.COLUMNS
WHERE
  TABLE_NAME = 'Customer'
ORDER BY
  ORDINAL_POSITION ASC;

Simple. Straightforward. Time-saver.