Tuesday, March 5, 2013

SSRS Source Control

When developing SQL Server Reporting Services reports, one method is to use SQL Server Data Tools 2012 (SSDT).  A huge benefit of using SQL Server Data Tools is that Data Tools is a derivative of Visual Studio, and therefore supports source control for version management of reports, datasets and data sources.

SQL Server Data Tools will support using TFS, SVN or any other source control system which has a provider built for Visual Studio.  In a large Microsoft shop, TFS may be a logical choice, however, in my experience, it has proven to be buggy and somewhat unreliable.  We saw issues in our environment with checkout, checkin and conflict merging.  

In light of the issues we experienced, we decided to go with Subversion (SVN).  If you don't have SVN in your environment, but would like to experiment with it, a low cost way to do this is with CollabNet SVN Edge.  SVN Edge is relatively easy to setup and can be run on Windows, Linux or Solaris.  I have personally run it on Windows and Ubuntu 12.04 LTS.  Both were easy to setup and configure by following CollabNet's instructions.  Technically, they don't support Ubuntu, but I had no issues with it.

If you decide to use SVN, you will need to download AnkhSvn, which is a free Source Control provider for Visual Studio provided by CollabNet.  Exit all versions of Visual Studio prior to installing AnkhSvn.  Once the installation is done, you will be able to interact with your Subversion repository directly from within the IDE.

In addition to using AnkhSvn, I like to have TortoiseSvn installed in tandem.  TortoiseSvn allows you to interact with your SVN repository from Windows Explorer.  TortoiseSvn comes in both x86 and x64, so when downloading make sure you know which architecture of windows you are running.

This article assumes that you already have an SVN repository created, with the appropriate trunk, branches and tags directories included in the repository.

For this demonstration, you will be using the project we created in our article on creating a new SSRS project.  To add our project to our existing repository do the following:

  • Open Data Tools
  • Open your SSRS project

  • In the File Menu under Subversion, select Add Solution to Subversion

  • Enter the information for your SVN repository
  • As per SVN best practice, you will want to make sure that your Repository has a trunk, branches and tags directory.  In later posts, we'll talk about why, but for now, please make sure that you follow the SVN best practice.

  • When you click "OK", you will be prompted to enter a comment and check your project into SVN.  This is because the "Commit files in the initial commit" checkbox is selected.  If you uncheck that option, you would have to manually checking the project to achieve your initial checkin.

On of the best ways to recover reports, datasets and data sources for SSRS is to ensure that the projects from which the reports, datasets and datasources are deployed  are checked into source control.  The alternative is to have regular backups of the SSRS Database which is of course a best practice, but if a report developer is having issues with his/her reports on an enterprise SSRS environment, it just isn't practical to restore the database.  However, if the developer is following source control best practices with branching and tagging in svn, or branching and labeling in TFS, recovery of reports, datasets and data sources will go much smoother.

Check out our next article on setting up a Shared Data Source

1 comment:

  1. Hi there,

    I have been searching for a way to also deploy reports from source control to the SSRS server.

    We do this with many of our other repositories (using git and GitLab) but SSRS is a challenge because deployed reports are actually stored in the SSRS database.

    Do you have any advice? We don't want to push changes to git and manually deploy each time, we want to have SSRS pull from git or have git push to SSRS. Essentially meaning that there's no way to deploy without first committing and pushing.