SQL Server 2012 was just launched and is packed a number of new features.  See the Microsoft SQL Server 2012 landing page for more information on SQL Server 2012.  SQL Server 2012 includes everything you need (almost, as I’ll show you below) to being utilizing the ALM features in Team Foundation Server 2010 without the developer edition of Visual Studio.  I’m going to walk you though the options to install, the configuration, and the additional downloads to begin using all of the features.

If you opened SQL Server Management Studio 2012 then you have probably noticed how it now powered by Visual Studio 2010.  In addition, the Business Intelligence Development Studio (BIDS) has been renamed to SQL Server Data Tools (SSDT).  Not only is SSDT powered by Visual Studio 2010, it now includes the Database Project template.  By integrating “Data Dude” (Microsoft’s original code name for the database development tools that keeps sticking) into the SQL Server installation, I think Microsoft is sending the message that ALM features for database artifacts are very important for database professionals and not just developers. 

SQL Server Installation

This first step is choose the proper features when installing SQL Server.  If you have already installed it without these features, you will need to rerun the installation and add these features.  During the Feature Selection of the SQL Server 2012 installation wizard, choose the SQL Server Data Tools and Management Tools to install the Visual Studio 2010 powered client tools as shown in Figure 1.

Figure 1 – Choosing the SQL Server Data Tools

Once this installation has completed, the two client tool programs will be available from the Microsoft SQL Server 2012 program menu.

Figure 2 – SQL Server Data Tools and Management Studio programs

Enabling Database Projects in SQL Server Data Tools

Open the SQL Server Data Tools and choose New Project. The Business Intelligence project templates are listed below for Analysis Services, Integration Services, and Reporting Services.  The SQL Server item has the option to install the additional functionality including for Database Projects.  Choose this option to download and install the additional functionality.

Figure 3 – Microsoft SQL Server Data Tools (Web Install) option

The dialog appears and provides the option to Install the features.  Click Install to take you to the link below.

Figure 4 – Installing Microsoft SQL Server Data Tools

The Get Started with Microsoft SQL Server Data Tools page includes the instructions for installing the tools for both Visual Studio 2010 Professional (or above) and SQL Server Data Tools.  The second bullet covers our scenario of the Visual Studio 2010 Integrated Shell and will download all of the required components from the link.  Click on the Download SQL Server Data Tools link to launch the Web Platform Installer 3.0 to install the SSDT application.

Figure 5 – Get Started with Microsoft SQL Server Data Tools

Click the Install Now link to run the Web Platform Installer powered installation.  Then click the Install button below to begin the installation process.

Figure 6 – Install Microsoft SQL Server Data Tools

Once the installation completes, you will have the Database Projects template available to use, however not all of the functionality that was included in previous versions of the Database Project is available.  The SSDT team is planning on using the SSDT Power Tools as a the delivery mechanism to continue to add functionality to SSDT based on the users requests and needs.  The first release of the SSDT Power Tools adds Schema View to the Database Projects.  Download and install the latest version the SSDT Power Tools to add this functionality. Continue to monitor this post and the SSDT Team blog for further improvements. 

Figure 7 – SSDT Power Tools Download Page

Finally, when you reopen the SSDT, the SQL Server Data Project template is now available.  You can now create Database Projects to manage your database artifacts with rich features such as smart rename and schema compare.  Furthermore you can leverage these projects to create automated builds and deployments to align with the application source code builds and deployments.  See my previous post, Implementing Continuous Integration (CI) and Deliveron for SQL Server Databases for some background on Database Projects especially for automated deployments.

There is one critical capability missing with this solution so far.  The Database Projects are important for following Configuration Management (CM) practices but it is not complete without the ability to use them with a Source Control Management (SCM) system.  Fortunately, by being powered by Visual Studio, SSDT can be tightly integrated with Team Foundation Server 2010 (TFS).   TFS 2010 provides a number of integrated ALM features including SCM and is even free to install if you have a MSDN license.

Figure 8 – Create SQL Server 2012 Database Projects

Connecting to Team Foundation Server 2010

The Visual Studio integrated shell that powers SSDT does not include the Team Explorer component that provides the full integration with TFS 2010.  Team Explorer is a free download (but requires a TFS CAL, also included with your MSDN license) that you can install to add this functionality.  Download and install Team Explorer 2010.  When the installation completes, you should download and re-install the Visual Studio 2010 SP1 (this was automatically installed for you).  To connect to TFS 2010, first make sure the Team Explorer view is visible.  If not go to the View menu and select Team Explorer.  In the Team Explorer toolbar, select the plus icon highlighted below to open the Connect to Team Project dialog and then select Servers to show you the Add/Remove Team Foundation Server dialog. Finally, click the Add button to create the connection to the appropriate TFS 2010 Server.  In addition to the SCM features with TFS 2010, this includes bug tracking, agile project management capabilities, build automation, test case management, and more.

Figure 9 – Connecting to Team Foundation Server 2010

I hope this provided the information you need get started using Database Projects with SQL Server 2012.  In a future post, I’ll go into detail on how to use the Database Projects.  If you are using the Visual Studio 2010 Professional or higher, the same link above will install the capabilities for this.  Finally if you are using the Visual Studio 11 beta or newer, the Database Project template for SQL Server 2012 is already included.  You will also want to download the SSDT Power Tools.

This was also cross posted on the Deliveron blog

Last Wednesday (Leap Day 2012), Microsoft made Visual Studio 11 and Team Foundation Server 11 betas available for download.  This new version includes a number of new ALM features such as Code Reviews, Agile Project Management Tools, IntelliTrace in production, Unit Test multi-framework support, and many others.   

Use download links below to begin taking advantage of the new version.  These betas have a go-live license and will have a supported upgrade path to RTM.  In addition the new version can be installed and run side by side with previous versions of Visual Studio.  The VS 11 Beta can even open existing VS 2010 SP1 solutions without upgrading the solutions allowing other developers with VS 2010 SP1 to continue to work in the same solution.  See Visual Studio 11 Compatibility for more details.  View Product Highlights for Visual Studio 11 Beta for more details on everything that is included.

Downloads are available here:

• Visual Studio 11 and Team Foundation Server Betas – This link provides downloads the Visual Studio 11, TFS 11, and Express beta downloads.
• Windows 8 Consumer Preview – Not required for any of the VS 11 projects but includes the new and innovative Metro UI.

In addition to the Beta downloads.  There was also a number of resources that also launched the same day to show you how to leverage all of the features in Visual Studio ALM 11. 

• Visual Studio ALM Rangers Readiness Beta “Wave” of Projects – The VS ALM Rangers, that I am a part of, released number of VS ALM Rangers Projects focused real world guidance that include the VS 11 and TFS 11 enhancements.
• Visual Studio 11 Application Lifecycle Management Virtual Machine and Hands-on-Labs / Demo Scripts – For those of you that want to try TFS without having to install it, this VM is a great resource to try out the features in a sandbox.  The VM includes 6 Hands on Labs for several of the new features and includes all of the data to support them.

I have already started using the VS 11 beta for several projects and the new experience and features are great (I did switch the color theme to “Dark”). 

Mike

In the weeks before the 2012 MVP Summit, I had been talking to colleagues and customers about feature requests and pain points in Team Foundation Server 2010 and Visual Studio 2010.  One the first day of the MVP Summit, I was reminded of the Visual Studio UserVoice web site.  This is a site where anyone can submit a feature idea and vote for existing ones.  Sign up for free and use your 10 votes to vote for your favorite ideas and/or create your ideas. Your votes do matter!

http://visualstudio.uservoice.com/forums/121579-visual-studio

I would still like to hear what you think.  Leave your favorites in the comments below.

Team Foundation Server 2010 provides functionality for testing applications with built in support for test plan and test case management    In our Agile/Scrum projects, we define all of the Test Cases in our planning meeting to define done for each User Story.  When starting new projects, team members often ask is how to format the Test Cases so they are clear.  One way we have found to be very useful is to use the same format as found in Behavior Driven Development(BDD).  BDD uses a format for communicating Test Cases called Gherkin.  The Gherkin format follows the pattern below:

Figure 1 – Given, When, and Then definition

Just like the User Story format (As a [user], I want to be able to do [business process], so that [business value]), we have found the Gherkin format is very useful for teams learning Agile.  In fact, these Test Cases can be written for Acceptance/Functional tests and for Unit Tests.  When we work with customers and our own projects we install our customized Deliveron Agile Process Template as part of the Deliveron Agile Delivery Process.  The Deliveron Agile Process Template is a slightly customized version of the MSF for Agile 5.0 process template.  In the Test Case work item, we have added fields for Given, When, and Then.  The “Then” should also match the expected result in the test steps.

Figure 2 – BDD additions for TFS Test Case Work Item

Figure 3 – Expected result matches the “Then”

Customizing the TFS Test Case Work Item Templates (WIT)  to add these fields is straight forward.  Follow the steps in this post I did a couple years ago.  It was for TFS 2008 but the steps are the same in TFS 2010.

In summary, use Test Cases to define done of the User Story and use BDD and Gherkin for the language of the Test Case.  Feel free to contact us if you have any questions about these changes or about the Deliveron Agile Delivery Process.

This has also be cross posted at http://www.deliveron.com/blog/post/Adding-BDD-to-the-TFS-2010-Test-Case.aspx

I recently worked with a client to create a fairly comprehensive solution for implementing Continuous Integration and Delivery for SQL Server Databases using Visual Studio 2010 Database Projects.  I had the opportunity to give a talk on the project at SQL Saturday in Omaha.  The presentation is here if you want the slides.  I think there is some context missing with the slides alone so I wanted to do this post to further explain the solution.

Before talking about the solution, let me describe three different continuous processes.  Continuous Integration (CI) is most familiar and is often used to describe all three of these processes.  I think the differences between these three processes is more clear by using these terms.

Figure 1 – Continuous Processes

Continuous Integration – Verifying code quality by compiling and running unit tests on the build server when a developer checks in changes.  Often abbreviated as CI.

Continuous Delivery – Adds the deployment of the application and database to an isolated test environment where additional integrated and UI automated tests can be run. 

Continuous Deployment – Includes automated deployment of the application through each environment through production.

In this post I will primary review our solution for CI and Continuous Delivery.  This works lays the foundation for the deployment into Staging and Production but I will discuss this in a future post.

Database Projects

Database tools in the past have been different than the tools used application code development.  These database tools have been difficult to implement change management practices and Application Lifecycle Management (ALM) practices.  Today there is an increasingly amount of application developers managing database changes.   These are some of the reasons that have led to need for a tool like Visual Studio Database Projects (DBPro for short).  This tool is part of Visual Studio 2010 (Premium and higher).  To create a Visual Studio Database Project, select SQL Server from the process template menu and then choose SQL Server 2008 Wizard or SQL Server 2008 Database Project.

Figure 2 – SQL Server Database Project Templates in Visual Studio 2010

The primary purpose of the Database Projects are to manage the the version control of database objects in SQL Server databases. The solution we established utilizes this and many of the features of DBPro including TFS Build Integration, Data Generation, Database Unit Testing, Static Code Analysis, and Database and Data Deployments.  In this post I’m not going to cover how to use all of these features but focus on how to implement the features for Continuous Integration, Delivery and Deployment processes.  For more information, please take a look at the Visual Studio ALM Rangers Visual Studio Database Guide .  This solution is complimentary to the guide and goes into more more specifics for CI.

Figure 3 – Visual Studio Database Guide

Challenges

Visual Studio Database Projects are a great tool and I highly recommend teams utilize these for managing version control for the SQL Server Databases.  However, successfully using Database Projects can be challenging.   I believe the benefits greatly out weigh the challenges but it is important for the team to be aware of these for a successful implementation.

Visual Studio – Visual Studio probably seems like an odd challenge considering this is the tool to use for the solution, however Visual Studio is a beast.  Visual Studio has become everything development.  Developers are used to Visual Studio and I have seen DBAs and other database professions get frustrated using it when they first start.  Stay with it.  It will get easier and is the future direction of Microsoft in SQL Server 2012.  From what I have seen SQL Server Management Studio 2012 is based on Visual Studio.

“Truth Center” Shift – Development teams have been used to using a shared database server and making changes directly on server since the stone age.  Managing source control of the database in DBPro essentially changes the “truth center” of the database project to DBPro.  Changes to the schema should be made in DBPro and then executed or deployed from DBPro to the shared server.  Development can also be done in local sandbox called offline schema development where the developer can make the changes locally and check them in.  Changes made directly to the shared SQL Server database risk being overwritten by the next deployment from DBPro.

Permissions – I have found DBPro does a great job managing almost all of the artifacts for databases.  The biggest challenge and frustration has been permissions.  The problem is that the database project holds the specific version of the database.  For permissions this doesn’t work in most real world examples because permissions change in each environment.  For examples, developers need different permissions in development versus what they need in production.  In addition, many enterprises use a separate domain for each environment.  As shown in Figure 4 below, Database Roles for the most part are consistent between environments and primarily the users and their role membership in those roles will vary.  The best method I found for handling these permission differences is to exclude them altogether.  Use the following steps to handle permissions when importing the schema and adding new objects to the project.  One advantage of removing the users is that that they are normally connected to a login and the login lives outside of the database in the Master database.  Including the users and logins in the project requires an additional project called SQL Server Server Project that contains the Master database.  This solution does not require a SQL Server Server Project.

Figure 4 – Managing Permissions Across Environments

Importing Databases

When using the importing the schema and objects into your project, make sure you perform the following steps to first import all of the permissions and the remove those that will change in different events.

• Enable Import permissions in the Import Wizard to import all of the permissions including Roles, Users, and Role Membership.
• After Import has completed:
  • Role permissions are to be kept in the .sqlpermissions file.
  • Schema Users (without login) are to be kept.
  • The other users must be removed
    • from sqlpermissions
    • From Security\Users
    • From RoleMemberships

Adding New Objects

When adding new objects in the Database Project

• Use Schema View
• Manually modify the Properties\Database.sqlpermissions and add the new permission
  • EX: Grant Execute to Role for Stored Procedures EXECUTE TestRole

            <
            PermissionStatement
            Action
            ="GRANT"
            >
            <
            Permission
            >EXECUTE</Permission><Grantee>TestRole</Grantee><ObjectName="spTestFromSSMS2"Schema="dbo"Type="OBJECT"/><Grantor>dbo</Grantor></PermissionStatement>

Permission Scripts

By removing the permissions from the project, there needs to be a place to account for these.  This solution accomplishes this by creating a script in the Scripts folder for environment that essentially creates the logins, users, and assigns the role membership for each user.  This allows the flexibility to store any variations between the environments and still store these in the database project and in source control.  Do not set the Build Action to PostDeploy because you can only have one for each project and it will be combined with the Deployment script.   Instead set the “Copy to Output Directory” property on the script to “Copy Always”.  This will create an Scripts folder and the permission files in the build output directory so it can be called by the deployment scripts.

Source Control

The primary benefit for using the Database Projects is that all of the database changes can be managed in source control.  There are a lot of ways to organize your source control and with branching and merging this can become complex to manage.  I like to take a pragmatic approach to source control and keep things simple but allow for complexity if needed in the future.  The Visual Studio TFS Branching Build 2010 is a great reference for adopting branching and merging strategy.  For this post I want to simply show the relationships between Production, Development, and Work Orders.  The main points is that the database projects should be branched and merged along side the application source control with some sort of release branch that has the current production version.  The Work Order branch is for production support changes that will be made into production.  Development teams should do downward merges often to always have any work order changes incorporated early.  When the application and database changes are deployed to production, the development branch should be merged up to the Production branch.  The diagrams below show how this is organized from a logic view and physical view.

Logical View

Figure 5 – Logical View of the Database Project Source Control Branches

Physical view

Figure 6 – Physical View of the Database Project Source Control Branches

Continuous Integration (CI)

To setup the most basic CI process for your Database projects, you can simply add the Solutions containing the database projects to your CI build that is building your application code.  The benefit of this is that it will build your database projects and validate that there are no schema errors and can validate any static code analysis rules.

Continuous Delivery

For Continuous Delivery, we want to expand the process to include deploying the database, insert any test data we need, and then run the database unit tests.  This adds validation that the schema in source control can correctly build the database and that stored procedures can pass any number of validations with the unit tests.  These steps would look like the following:

Figure 7 – Simple Continuous Delivery Process

Visual Studio Database projects make implementing this process very simple and only requires a couple simple settings.  The dialog below shows the out of the box settings.  To open this dialog, select Test > Test Configuration from the menu.   The sections are slightly out of order.  To start we want to set the Deployment database project to the project we want to deploy.  Next choose the configuration.  The configuration settings in the database project will specify the target connection string and other deployment properties.  Next, the Database state setting will generate the test data for the unit tests by running one of the data generation plans.

Figure 8 – Database Test Settings

Types of Continuous Delivery

The example above basically deploys the current version of the schema to the target but doesn’t is not a good practice run into production.   It basically deploys the changes from the last deployment.  My goal of the Continuous Delivery process should be a practice run into production and essentially deploy the application and database the way it will be done for the production deployment.   There are two types of delivery based on whether or not the application is already in production.  For new systems that haven’t been deployed to production, the deployment will be to deploy all of the schema.  This is referred to as Greenfield.  For existing systems, the schema will the difference of what is currently in production with what has been developed.  This is referred to as Brownfield deployments.

From a Visual Studio Database Project standpoint, Greenfield deployments are a simple using the deploy option.  This will drop the database and execute the full schema script to create the database.

Figure 9 – Greenfield Process

For Brownfield deployments in Visual Studio Database Projects, the process is accomplished in two steps using Production and Development versions of the database projects.  The first step is to use the Production version of the Database Project to create the full CREATE script.  Next, use the compare feature to compare the Production and Development versions to create the DELTA script.  Again, the key is not to compare the development against the live production database but to use the version of the Database Project that was created either from the production database or from the Release branch in source control.  Once you have these two database scripts, run the CREATE script to drop the database and create the database to the production level.  Then execute DELTA script to bring it to the current development level.  From there you can follow the similar steps to execute the data generation plan and automated tests to complete.  See below to see how this fits together

Figure 10 – Brownfield Process

Putting this all together, here are the steps in order for a good SQL Server database Continuous Delivery process.  There is some customization that has to be done for this.  The database testing options that were available for the simple process, won’t work out of the box for this solution.  This is because the Database Project doesn’t know about the production and delta scripts.  The build by default would create the database and run the data generation plan before unit tests including the database unit tests.  However, the unit tests are run immediately after the application is built and we need to specify the a step to build create the scripts and then execute them.  I customized the build definition by moving the unit test execution activities to later in the process so I could execute the SQL scripts before the Unit Tests are run.  Once this was moved, I could use the built in features to run the data generation plan.  Below are the steps for the full end to end Database Continuous Delivery process.

Figure 11 – End to End Database Continuous Delivery Process

To combine this process into the application continuous delivery process, the same tasks above can executed along with the application steps.  This process is grouped into three groups: Build/Stage, Deploy, and Execute Automated Tests.  The process is outlined below.

Figure 12 – End to End Application and Database Continuous Delivery Process

VSDBCMD

One of the great features of Visual Studio Database Projects is that the deployment and compare functionality can be executed via a command line utility called VSDBCMD.exe.  This allows us to perform the necessary steps in our Continuous Delivery process.  I utilize a InvokeProcess Activity in the build definition to call a PowerShell script to execute the VSDBCMD commands.  Below are examples of how to create the Production CREATE script and the DELTA script.  The Production Script creates the full CREATE script from the compiled Production version of the Database Project.  The DELTA command shows how to compare two Database Projects to generate the DELTA SQL Script.

Create Production Script

& "C:\program files (x86)\Microsoft
Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd.exe"

/a:deploy /dsp:sql /model:Ecommerce.dbschema /DeploymentScriptFile:c:\temp\OutputFilename2.sql 

/p:TargetDatabase="NewEcommerce"

Create Production and Development Delta Script

& "C:\program files (x86)\Microsoft
Visual Studio 10.0\VSTSDB\Deploy\vsdbcmd.exe"

/a:deploy /dsp:sql /model:Ecommerce.dbschema /DeploymentScriptFile:c:\temp\OutputFilename2.sql 

/targetmodelfile:"C:\tfs\deliveron\Production\EcommerceSolution\Ecommerce\obj\Debug\ecommerce.dbschema"

/p:TargetDatabase="NewEcommerce"

In Summary

This concludes the overview of the solution for Continuous Integration and Delivery for SQL Server Databases.  I hope it gives you a complete overview for creating your own Continuous Delivery process.  Feel free to contact me if you have any questions or comments.

Review of Key Concepts

• There are three types of Continuous processes: Integration, Delivery, and Deployment.
• Continuous Delivery should be set up to be a practice run for Production.
• Create compare scripts between development and production database projects and don’t compare against live databases.
• VSDBCMD is a command line utility that perform the deployment and compare functionality in Visual Studio Database Projects.

This was also cross posted at http://www.deliveron.com/blog/post/Implementing-Continuous-Integration-(CI)-and-Delivery-for-SQL-Server-Databases.aspx

Last week I had the opportunity to speak at Omaha’s first SQL Saturday.  My talk was on Continuous Integration with SQL Server Databases.  I had a good turnout and some great questions at my session.  Here are the slides from my talk.  I mistakenly mentioned in the talk that column changes would be treated as a Drop and an Add, thus resulting in data loss.  Visual Studio 2010 Database Projects track the changes like this and incorporates the column change into the delta script. 

In my example, I have a Product table with existing data. 

Figure 1 – Product table with data

I renamed the column from NameOfProduct to ProductName

Figure 2 – Rename feature in Database Projects

Figure 3 – Preview Changes Dialog

You can see that the delta script that was generated by the Deploy option in the Visual Studio Database Project is aware of the column name change.  The script calls the sp_rename stored procedure to rename the column name and keep the data intact.

Figure 4 – Rename Column Script

Here are the results of table after the rename. No data loss!

Figure 5 – Product table data after the rename

In my next post I’ll discuss specifics around the CI for SQL Server databases solution.

Enjoy!

This was also cross posted to http://www.deliveron.com/blog/post/Column-Changes-with-Visual-Studio-2010-Database-Projects.aspx

If you have ever tried scripting database schema and data from SQL Server, you have probably been frustrated like me that there is not a simple process for doing this.  SQL Server Management Studio includes two options highlighted below.  The Generate Scripts… option works as expected and allows you to easily create a script to recreate the database.  However, if you want to export the data to import it at a later point, using the Export Data… option doesn’t quite do what is needed to script the data.  It allows you to script to a CSV or Excel file, however I have found importing an exported file, isn’t always easy.  

Figure 1 - SQL Server Management Studio

Surprisingly there is a better option not in SQL Server Management Studio but in Visual Studio 2010 (I believe this option is available in Visual Studio 2008, but I wasn’t able to confirm this for the post.).  Visual Studio 2010 includes an view window called Server Explorer.  This is typically used for data binding and tools like LINQ and Entity Framework.

Figure 2 – Visual Studio 2010 Server Explorer

The context menu for the particular data connection includes an option called Publish to provider… 

Figure 3 – Publish to provider

This option launches the Database Publishing Wizard.  When this wizard displays it gives you the option to export both schema and/or data to a SQL file.  Especially for data, this is perfect for migrating data from one environment to the next including preserving identity keys.

The wizard opens with the option to choose a database and automatically script all objects in the selected database.  I chose the AdventureWorks database and clicked Next.

Figure 4 – Select Database in the Database Publishing Wizard

The next step in the wizard is to choose what objects types to publish.  Here I select Tables.

Figure 5 – Choose Object Types in the Database Publishing Wizard

With the the Tables option selected in the previous step, the Choose Tables step appears.  Here I selected a single table for this demo.

Figure 6 – Choose Tables in the Database Publishing Wizard

Finally choose the output location.  This can either output to a file or to a hosting provider.  Here I chose the Script to file option to save the output to a file.  To use the Publish to shared hosting provider your hosting provider or target system must support a SQL Publishing Web Service and the database already exist on the target.

Figure 7 – Select an Output Location in the Database Publishing Wizard

This screen includes the publishing options.  The options are straight forward.  The Drop existing objects in script option will toggle a dropping existing objects in the target database before the new objects are scripted.  The Schema qualify option qualifies object names with the schema.  The Script for target database drives the compatibility of the script.  Finally the Types of data to publish allows for schema or data only or both.  Here I want to script both so I chose Schema and data.

Figure 8 – Select Publishing Options in the Database Publishing Wizard

The final screen is a confirmation of the options selected.  Click Finish to run the wizard and create the script.  When the wizard completes, it will display success.

Figure 9 – Successful Database Publishing Wizard

Below is the output of the Database Publishing Wizard for the schema and data.

Figure 10 – Schema output of the Database Publishing Wizard

Figure 11 – Data output of the Database Publishing Wizard

For more information on the Database Publishing Wizard, read Deploying a Database by using the Database Publishing Wizard on MSDN.

This was also cross posted at http://www.deliveron.com/blog/post/Script-Database-Schema-and-data-using-Visual-Studio-2010-and-Database-Publishing-Wizard.aspx

Enjoy!

Mike Douglas

The Team Project portal site in TFS 2010 is the collaboration hub for many activities that typically includes document libraries, team calendar,  wiki, reporting, and more.  TFS 2010 includes a number of reports that can be displayed on the portal using SSRS (using either SharePoint 2010 Foundation or SharePoint 2010 Enterprise) and Excel Services (using SharePoint 2010 Enterprise).    In this post, I will walk through customizing the report to display the burndown for the particular Iteration..

The first question I often receive is:

How do I customize the burndown dashboard report to fit my Sprint/Iteration?

When you display the project portal page and view the burndown dashboard report, you will notice that the default parameters don’t match the current iteration.  To update this, we can override the parameters being passed into the report through the URL.  I want to set Start Date, End Date, and Iteration parameters to display the correct data.

First, navigate to the page with the report

Click on the arrow and choose “Edit Web Part” to edit the parameters for the report.

On the right of the screen is the settings for the web part and report. The link is what needs to be modified.

http://tfsserver/ReportServer/Pages/ReportViewer.aspx?%2fTfsReports%2fBP%2fTeamProject%2fDashboards%2f
Burndown&rs:Command=Render&rc:Toolbar=false&StartDateParam=07/05/2011&EndDateParam=07/26/2011

To Determine the properties to add or change, you can go to the report itself and look at the properties available. In this example, we want to update the Start Date and End Date and add the Iteration. To find out what the name of the Iteration parameter is, go to the following URL to see the properties.

http://tfsserver/Reports/Pages/Folder.aspx?ItemPath=%2fTfsReports%2fBP%2fTeamProject%2fDashboards&ViewMode=List

Choose the Manage option in the context menu of the report

In the settings screen, choose the Parameters tab and find the parameter you are looking for. This is the name we will add to the URL above. In this instance, it is IterationParam

The format of the IterationParam parameter wasn’t intuitive.  The item is a multi-select checkbox list.  So it wouldn’t take a simple text value such as “Iteration 01”.

To figure out the format of value, I used the report viewer to set the value of the Iteration and exported the report as an Atom feed.  Then I opened the Atom XML and to pull out the value of the Iteration Param that it created.  Below is what the link looks like with the IterationParam value added.

http://tfsserver/ReportServer/Pages/ReportViewer.aspx?%2fTfsReports%2fBP%2fTeamProject%2fDashboards
%2fBurndown&rs:Command=Render&rc:Toolbar=false&IterationParam=%5BWork%20Item%5D.%5B
Iteration%20Hierarchy%5D.%5BIteration1%5D.%26%5B7130920747760410946%5D%26%5B-4689172157298829814%5D&
StartDateParam=07/06/2011&EndDateParam=07/26/2011

Finally paste this URL into the link in the web part and save. This is ready to display.

Mike Douglas

This post was cross posted to http://www.deliveron.com/blog/post/Customizing-the-Burndown-Dashboard-Report-in-TFS-2010-Team-Portal.aspx

PowerShell is an awesome technology.  It has become an essential tool for doing automated deployments with Team Foundation Server 2010.  Creating deployment scripts in PowerShell are very easy to manage and maintain by the entire development without having to know about the details of the build definitions.  In future posts, I will explain how I use the build definitions and PowerShell to perform the deployments.  With this post I wanted to share a small tip on being able to catch all errors with a Try, Catch.

In PowerShell, there are two types of errors. Terminating errors and non-terminating errors.  Terminating errors will automatically be caught in a Try, Catch block but non-terminating errors will not.  Look at this example below.

try
{
    copy-item "c:\notexists\file.txt" "c:\temp"
}
catch {

write-host "bad copy"

}

This command looks like it should write to the out the error message because the folder and file do not exists. However when you run this, it will only display the PowerShell error message. 

Copy-Item : Cannot find path 'C:\notexists\file.txt' because it does not exist.
At C:\temp\copyitem.ps1:3 char:14
+     copy-item <<<<  "c:\notexists\file.txt" "c:\temp"
    + CategoryInfo          : ObjectNotFound: (C:\notexists\file.txt:String) [Copy-Item], ItemNotFoundException
    + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.CopyItemCommand

This is an example of a non-terminating error.  There are two ways to fix this.  The –ErrorAction ”Stop” flag can be added to many of cmdlets. 

copy-item "c:\notexists\file.txt" "c:\temp" -ErrorAction "Stop"

Now, when the script is run, it will display the error message, “bad copy”.

This is great but if you are like me, you don’t want to have to add this to every command.   Luckily there is a way to do this for the entire script.  At the beginning of the script, you can set $ErrorActionPreference = “Stop”.  If you want to be able to toggle it, it default setting is “Continue”.

$ErrorActionPreference = "Stop"

Here is the final script.  Enjoy!

$ErrorActionPreference = "Stop"

try
{
    copy-item "c:\notexists\file.txt" "c:\temp"
}
catch {

write-host "bad copy"

}

The Visual Studio ALM Rangers just released two projects, the Build Customization Guide and the Lab Management Guide .  Both projects provide real world, in-depth guidance and hands-on-labs (HOL) for planning and creating solutions for utilizing the Team Build 2010 and Visual Studio 2010 Lab Management.  Every development team should utilize the features covered in these guides for creating a build strategy that includes Continuous Integration (CI), packaging and versioning of applications, and automated deployments to at least the development and test environments. By utilizing Visual Studio Lab Management, these environments can be quickly provisioned and managed by those development teams.  They will be able to do things restore to a baseline before building, deploying, and running tests in those environments, clone the environment to provide multiple test environments for QA, and attaching a snapshot of the virtual environment along with other rich information to bugs for the developers.

Visit the websites for all of the details and downloads for the guidance.

Build Customization Guide

I am especially excited about the Build Customization Guide being released because this was first Visual Studio Ranger project I have had worked on.  I had the opportunity to work with many talented and dedicated individuals.

The Epics included in this guidance are:

• Practical guidance and tooling to simplify the customization of Team Foundation Build
• Practical guidance to use Team Foundation Build process templates to automate build and non-build scenarios in Microsoft environments
• Practical guidance to enable simple and flexible deployment of applications and their data stores
• Practical guidance for Activities to empower developers and build engineers
• Quality hands-on labs that complement the guidance and effectively guide the user through the features
• Visualization of the guidance using quick reference posters

http://rabcg.codeplex.com/

Lab Management Guide

I didn’t contribute to the Lab Management Guide, but I have read through the guidance.  It includes a lot of great information that include planning Lab Management, setting up the Virtual Lab environment, and creating Virtual Machines using the VM Factory.

The Epics included in this guidance are:

• Visualization of the guidance using quick reference posters
• Advanced golden image management using the VM Factory for Lab Management
• Provide guidance on setting up Test environments with respect to pre-defined personas
• Provide Guidance to enable large and small teams to setup and configure both automated and manual tests
• Provide practical guidance for managing and maintaining a Lab Management environment
• Provide practical guidance to enable teams to quickly setup and configure their lab management environment

http://ralabman.codeplex.com/

Visual Studio ALM Rangers

So who are the the Visual Studio ALM Rangers?  They are a group of internal Microsoft employees and external communities leaders/MVPs who’s mission is to accelerate the adoption of Visual Studio with out-of-band solutions for missing features and guidance.  Willy-P. Schaub has posted some great information about who we are, the past accomplishments, and future plan in the Visual Studio ALM Rangers 5 year Report .

Please contact us at tfs@deliveron.com for information on these guides or implementing these solutions in your environment.

Mike