Welcome to EMC Consulting Blogs

I recently had to investigated a client replication topology.  I needed to understand the publications, subscribers and the articles. The problems I was facing were as follows:

• The replication topology was only defined on the production infrastructure.
• There was no documentation about the replication topology.
• I had no way of being able re-create replication topology.
• Only short amount of time.

I was able to script the replication topology from the SQL Server Management Studio.  This was partially helpful as I now had a script which could be used to review the replication topology.  However this end up being a fairly large file and would be time consuming to review all information. 

So I deicide to write a PowerShell, SQL Server Powershell for the Invoke-Sqlcmd commandlet, that would do the following steps:

• Parse the file to find the lines containing the following store procedures:
  • sp_addarticle.
  • sp_addsubscription.
  As theses contain most useful parameters to help understand the replication topology
• Pares the line to find the key parameters and their values.
• Then insert the information into tables within a database.

I chose to use PowerShell because this was a perfect fit for a scripting language: manipulating a text file and doing regular expression on strings.

This help me see that articles were being repeated across publication, some of the publication had identical articles but had different subscriber.

A copy of powershell script is available here.

In October 2009 I created 2 codeplex projects which I would to tell you about:

SQL Server Reporting Services MSBuild Tasks (ssrsmsbuildtasks)

A few years ago I created some tasks for MSBuild to help deploy reports for my project, details can be found here,  since then my tasks have been re-used in a few other projects within my company.  However lately we have been doing a few projects which have made use of Reporting Services integrated mode with SharePoint, which meant that my tasks were unusable as they only work with a native mode report server.

So I have been updating them to include support for an integrated mode report server.  Also before I added the integrated mode support I took some time to rewrite my original native mode tasks as well.  The re-writing of the tasks includes some improved understanding of MSBuild by making more use of Item Groups and metadata for setting of report server properties of the report items being deployed. 

I placed the tasks within codeplex as I needed a better way of sharing my tasks other than through the blog post.  Also using the task is helpful in getting the your reporting project integrated with any continuous build of your project.  Another way they can help is with automating unit testing of your reporting project which I have talked about how to do from a previous post.

The SQL Server Reporting Services MSBuild Tasks are available from the following URL: http://ssrsmsbuildtasks.codeplex.com/

Server Reporting Services Slide Shower (ssrsslideshower)

While working on our Scrum for Team System process templates for Team Foundation Server (http://www.scrumforteamsystem.com) I created a useful tool.  The tool helps to show reports that are important to a scrum team in a sort slide show presentation, more details can be found through this previous post.

While writing the tool most of the mechanics of the tool doesn’t depend on Team Foundation Server or our Scrum template.  Also I thought it would be a useful tool for people who wish to demo any reports that are within their reporting services infrastructure.  So I have removed the dependences of Team Foundation Server and our Scrum Template. 

I have plans to improve the tool, within my limits of coding knowledge, to allow reports parameters values or labels to be set as it renders the reports within the slide show. I also plan to get it working with a integrated report server as well.  However my ssrsmsbuildtasks project has taken most of my time but over the next few week I plan to start making my changes to this project.

I placed the project on codeplex because within our Scrum for Team System V3 template for Team Foundation Server 2010, which beta 2 is now available from this link,  we have dropped the inclusion of this tool and plan to release it within codeplex.  This is  mainly because it was a value add tool and not needed as part of the process template. Also the changes that were made to template and the reports, which now don’t have clear defaults for our report parameters which fit into the my improvements plans that I mention above.   

The SQL Server Reporting Services Slide Shower is available from the following URL: http://ssrsslideshower.codeplex.com/

I have been working with Microsoft SQL Server Reporting Services, since its first release, within many of my projects.  One of the biggest tasks, that I find with writing a report is the testing the reports and making sure the data that is displayed is correct.  My method of developing and testing reports is as follows:

• Write the queries, outside Reporting Services, for creating the report data set(s) with the parameters defined which will be configured within the report.
  • Define the parameters that report data set would create and assign a valid value.
  • Execute the queries.
  • Inspect the results to make sure that they meet the expected results.
  • Repeat with a new valid value or fix any issues.
• After one or two or even three passes that have worked then I take the queries and put them into report data sets.
• Then I define the report layout.
• Then I preview the report make sure it’s still correct
• Then I release to our testers for testing.

With this amount of manual testing there could be a potential for mistakes to creep past for several reasons: The test data often isn’t diverse enough to test all the logic of the queries; The report data has a lot of rows which aren’t all inspected; Run out of time as the testing was under estimated so amount of testing is reduced.

I did, at the beginning of last year (2008), look into away of being able to test the reports by an automated tool.  I was looking for a way to compare the information that the report was displaying and compare it to some expected results.  The need to test the report itself and not just the underlying queries were for the following reasons:

General Reasons:

• The Report could be doing more data manipulation outside the SQL queries that have been unit tested.
  • Embedded reporting calculations
  • Embedded VB.Net code
  • External class libraries code

(all of the above could have been unit tested separately but still could produce incorrect information if wired-up incorrectly)

• Test visibility setting for sections of the reports.
• Test any embedded report calculations
• The Report could be using MDX and there isn’t an easy unit testing frame work for MDX

My Project Reasons:

• All the reports used embedded SQL and MDX.
• Writing reports against a 3rd party data warehouse and processes
• There is no database project in which to use a unit test frame work for SQL.

Searching the internet I came across this article: Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server, which gives a guide on how to stress test reports.  This is helpful to ensure that the reporting solution/architecture can handle the demands that are placed upon it.  I also thought that this method of testing can be used for 2 other forms of testing:

• Simple unit testing: Ensure that report can render.  Placing this within a build process, that adds data into the reported database, then runs the web test to check if the report renders can help highlight if there have been changes to the database that breaks the build.   This can happen if the reports are based off some application database for simple reporting and the database is using an ORM tool. (Which at least one of our projects is doing).

• Build verification testing: Does the report render? Running a web test after the deployment has happened would highlight any error in the deployment of the solution or deployment of the infrastructure.

I helped to get this method of testing implemented for the above mentioned reason for an application project, where it did help to capture and highlight builds that broke the reports. However as this didn’t meet the requirements fully I was looking as this method, which uses web tests, of testing as it was only inspecting the http responses.

It wasn’t until recently that I starting looking again at unit testing reports again because of my current project as testing the reports is one of the hardest jobs for the following reason: Developing reports based upon a platform so have no control of the flow of the data.  So I looked again at the beginning of this year (2009) and again didn’t found much there that would help me.  So I went to the drawing board to design my own approach and remember that in June 2008 I used Reporting Services in interesting way; I wrote a report which was only create to generate an dynamic XML document for a web page to consume and bind the data to another control (more information can be found here: Could HTTP Soap / T-SQL endpoint be replaced by Reporting Services XML Reports? ).  So I took a further look into the XML render options, to see how the reports that uses chart would render into XML, which my current project heavily utilises, and found that all the values of the data points within the charts were exposed.  This meant that I could test the reports in the way that I intended. 

My next challenge was to design the process that would render the report into XML and then perform some sort of test.  The test was easy, if the report was going to produce an XML document then I should be able determine the how the report will render the XML based upon the following: Expected results based upon the known set of data within data store that report is querying; Passing known values for the reports and knowing the XML setting for the reports; So I decided to do a comparison on the report XML against a prepared XML document.  The next problem was how to do the comparison as there wasn’t a tool that I could find. So I asked a few of my more .NET savvy guys as well with no joy. 

Then I remembered that SQL Server Integration Services (SSIS) has an XML task that does a XML Compare that compares the element’s attributes and more importantly the values.  So I set about creating a SSIS package to do the following:

• Loop though a control file that contains the location of the reports to test; required parameters, values and location of the expect XML output.
• Download the reports into XML format from the Report Server.
• Then use the XML task to compare the results from the report server and compared against the expect results.

Based on some initial testing of this approach work for testing my reports the results were good.  However I haven’t been able to fully implement my approach for the following reasons:

• Need to work on creating the test data for project.
• Need to work on getting the test data through the 3rd party process.
• Need to work out how to deal with time as many of my reports show time differently.
• Need to work out how make it part of the build/test process.

Also recently I have found that my approach is similar to a tool that is available to buy from Innosphere. 

For the last few months I, with the help of fellow work colleague Kate Begley, have been working on the writing the reports for the Scrum for Team System V3 process template, which Beta 1 has been released and details are available from http://blogs.conchango.com/sfts/archive/2009/07/28/scrum-for-team-system-v3-beta-programme.aspx.

There are a number of changes, from a reporting aspect, that have been introduced into Team Foundation Server 2010 platform and the Scrum for Team System V3 process template like: Warehouse schema changes; only supporting Reporting Services 2008; a sophisticated multi-team support model and new QA model. As a result the reports have been completely rewritten so there is a limited number in this beta release.

However I would like to highlight some of the smaller updates that been applied to the reports to help readability / usability of the reports which are as follows:

• A reporting site folder structure has been created to make browsing the reports easier

• Reporting tool tips on the charts to display the unit, value, date that point on the chart is showing.

• A Today strip line, with tool tip showing where Today is.

• Small descriptions have been embedded into the reports so when viewing the report in Report Manager or Team Foundation Server Web access their descriptions can be seen to give a snapshot of their function.

• A report footer has been added with the same embedded description and link to a report guidance (which will be coming soon).

• The Portal reports can be resized within the SharePoint portal by changing the height and width report parameters to help with customising the SharePoint portal.

The reports that are still available from previous versions are as follows:

• Version – Diagnostic
• Bug History Chart – QA
• Product Burndown Chart By Day – Release
• Product Cumulative Flow – Release
• Sprint Burndown Chart – Sprint
• Sprint Cumulative Flow – Sprint

Alongside these additional new reports for version 3, which I will cover in another blog:

• Warehouse Status - Diagnostic
• Sprint Burndown - Tasks and Stories Chart – Sprint
• Sprint Team Aggregate – Sprint

I have just finished a project were I made a lot of use of the SQL Spatial to do some processing and loading into the database. Here are some of lesson I learnt:

• The first method that needs to call after instantiating a SQLGeographybuilder object is: SetSrid() then the BeginGeography(), BeginPoint().
• Before using sending a SQLGeometry object to the SQL Server use the IsVaild() function to ensure that Geometry object is valid.

  I create a console application which loaded line, which was using OS coordinates system, data from a flat file. Some of the line data within the file wasn’t as correct as I was led to believe. The application create the SQLGeometry object .NET but the Sql Server then rasied the error when it received and try to save it into the table.

• The STPointN() function is 1 base which is stated in the Books On-Line
• If your query needs to select some of the derive data from the functions you can help performance by using persisted compute columns on the base table.

  I had to create a view which needed the latitude and longitude of the starting and end points of the geom line also including the distance. The view would take about 20 seconds to finish executing. Once I created some persisted compute columns on the base table the view would finish executing under 3 seconds

• You can create more than one spatial index for a spatial column.
• Trying to join tables using the spatial functions isn’t great for performance.

I have been working on a project which made use of the spatial data type geography within SQL Server 2008.

An issue that I had was how to load the geography data from a KML file into SQL Server 2008. Currently there is no out of the box tools to do this. There is a 3^rd party tool, Safe FME, which offer either their own tool or components which extend integration services. This was overkill for my issues as I only had to do it once.

So I wrote a console application which parses the KML file to extract the point data convert it to a SqlGeography type and store it in the database.

To use the SqlGeography C# type you need to add reference to the following name space: Microsoft.SqlServer.Types. This can be found in the following dll: Microsoft.SqlServer.Types.dll

Then use the following code to create the c# sqlgeography type:

1:// use SqlGeographyBuilder to help create the SqlGeography type
2: SqlGeographyBuilder geographyBuilder = new SqlGeographyBuilder();
3: string[] longLat;
4: SqlGeography geography;
5:
6: // gets the co-ordinates
7: XElement coOrdinates = element.Element(ns + "Point").Element(ns + "coordinates");
8:
9: // set the Spatial Reference Identifiers that will used to create the point
10: geographyBuilder.SetSrid(4326);
11:
12: // state what type of geography object that I to create
13: geographyBuilder.BeginGeography(OpenGisGeographyType.Point);
14:
15: longLat = coOrdinates.Value.Split(new char[1] {','});
16:
17: // add the frist figure lat long point
18: geographyBuilder.BeginFigure(Convert.ToDouble(longLat[1]), Convert.ToDouble(longLat[0]));
19:
20: // close the figure and geography class
21: geographyBuilder.EndFigure();
22: geographyBuilder.EndGeography();
23:
24: // get the geography builder to return the sqlgeography type
25: geography = geographyBuilder.ConstructedGeography;
26:
27: return geography;

After creating the data type I then needed to write the code send the data to the SQL server which as follows:

1: // set the command text
2: string sqlCommandText = "insert into [dbo].[Location]([Location],[CoOrdinates]) Values(@pLocation,@pCoOrdinates)";
3:
4: // create the command object and set which command type
5: SqlCommand sqlCommand = new SqlCommand(sqlCommandText, sqlConnection);
6: sqlCommand.CommandType = CommandType.Text;
7:
8: // create and add the paramter for standard sql data type
9: sqlCommand.Parameters.Add(new SqlParameter("@pLocation", name.Value));
10:
11: // create and add the paramter for sql geography data type
12: // as I am using and system CLR type have to say what data type name the parameter is
13: sqlCommand.Parameters.Add(new SqlParameter("@pCoOrdinates", geography) {UdtTypeName = "Geography"});
14:
15: // execute the command
16: sqlCommand.ExecuteNonQuery();

Like the report viewer control the Microsoft.SqlServer.Types namespace is not installed with .Net. For the application to work on another computer, without having to install SQL server or their client tools, a Redistributable package would need to be installed.

The SQL Server System CLR Types package contains the components implementing the new geometry, geography, and hierarchyid types in SQL Server 2008. This component can be installed separately from the server to allow client applications to use these types outside of the server.

A version can be found here:

http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en

As I have mention in a previous blog entry I’ve been heavily involved in the Scrum for Team System process template since the start of version 2 and have written the majority of the reports. I have faced, and still do, some challenges while doing this. Some of them have been around how to display the data within reporting services 2005.

Now with the release of Team Foundation Server 2008 Service Pack 1, release in August, this enables Team Foundation Server 2008 to be used on a SQL Server 2008 environment. This will make some of reporting challenges I faced with reporting services 2005 disappear. This will due to major changes that been made to reporting services 2008 like Tablix control and wider Dundas chart controls.

Within this release we have made a patch which will update some of the reports for reporting services 2008. This to fix some of the reporting services 2005 Patterns and Recipes which became visible. I am currently working looking at how Scrum for Team System can make more use of the reporting services 2008 which we will release as an advance reporting pack in the future.

To find out more about the Scrum for Team System SQL Server 2008 patcher please read the following Scrum for Team System blog

New Reporting Features available in Scrum for Team System v2.2

Build Reports added to Scrum for Team System v2.2

The Scrum for Team System report slide show tool

Scrum for Team System Version 2.2 support for SQL Server 2008