Come visit/revisit The Beer House! (Continuous Integration)

Article
07/31/2009

Isn’t it funny how a simple word like Beer can grab your attention? In this post we will look at migrating The Beer House (TBH) sample application database to be managed by VSTS:DB. For those in the NW you may have seen this previously as I have presented this to the PNWSQL User Group. Since then I have received many requests for sharing the demo walkthrough.

This post illustrates how Visual Studio Team System Database Edition enables continuous integration for databases. The walkthrough demonstrates how to setup Team Build to automate building and deploying databases. Using VSTSDB’s incremental deployment engine in conjunction with Team Build build automation enables database continuous integration for teams working with Team System.

The best thing about this walkthrough is that anyone with a PC capable of running a VM (VPC or Hyper-V) can follow along on their own boxes because everything I will be using is FREE! Well, it’s free as in Trial Software and Sample Applications.

This post will include the following sections:

1. Why continuous integration?

2. Migrating the Beer House Sample Application to use Database Projects

3. Setting up the Database Projects for Sandbox Development

4. Adding our Solution to Source Code Control

5. Setting up our Database for Rolling Builds/Continuous Integration

6. Overriding a SQLCMD variable to Version Stamp DBs

Why continuous integration?

Continuous Integration (CI) is a core tenant of being an agile team. Teams that use agile methodologies typically work on producing software in short iterative cycles producing a functional piece of software with every check-in, at the end of every day, and at the completion of every iteration or sprint. Basically, the codebase that is checked into the shared repository should be functional at all times. CI is practice where each check-in results in a build being run which produces a software product that can be assessed for quality, completeness, and other indicators that communicate the project is moving in the right direction. This assessment typically includes build verification tests (BVTs), but may also include other tests as well. Since the CI build, or sometimes called rolling build, is automated many teams run all their automated tests. This frees up the development team from having to run long running test suites that may impact productivity if ran by multiple individuals of the team many times a day.

Some of the benefits of CI include: spotting integration issues early; early build break detection; complete and regular test runs; automated code promotion; deployment issues identified early; redundant tasks automated; stakeholder visibility into project progression increased. While CI is by no means a new concept, in fact it has been used by many teams successfully for years now, what teams continue to struggle with is how to integrate their database components into their CI process. The challenge is the same I outlined in a previous post in regards to database deployment; it is the existence of state.

With Team System Database Edition GDR and Team Foundation Server you can enable CI for Visual Studio projects. Using the Incremental Deployment Engine of VSTS:DB and the build automation platform of Team Build you can enable CI for your databases. In this post I outline the walkthrough I have presented a few times. I hope this inspires teams to automate their builds and reap the many benefits CI has to offer.

If you want to dig into CI more I recommend the following reading:

Continuous Integration – Martin Fowler

https://martinfowler.com/articles/continuousIntegration.html

See also his related whitepaper on Evolutionary Database Design.

Before we get rolling here is what you will need:

· Microsoft® Visual Studio® Team System 2008 Team Foundation Server SP1 and Team Suite SP1 VPC or Hyper-V Image (Trial)

· Microsoft® Visual Studio Team System 2008 Database Edition GDR R2

· TheBeerHouse: CMS & E-commerce Site Starter Kit (Code Plex Project)

If you have Windows Server x64 and a spare 2GB RAM free I highly recommend using the Hyper-V image. The performance of the Hyper-V image is superior to that of VPC. I will assume in this article that you can effectively use VPC or Hyper-V. If you need help, please see Hyper-V/VPC.

Migrating the Beer House Sample Application to use Database Projects

Once you have your virtualized environment setup you can start migrating the application. Do this by downloading the starter kit from the link above. Unzip the file on your VM to a directory called DasRoot. You should end up with a directory structure that looks like this:

Let’s now attach the app’s Database to the default SQL Instance. Note there are 2 SQL instances on this VM. The default instance running is 2005 SQL Enterprise along with other services like SSRS. The default instance also has the TFS databases on it. We will consider this our production server for this walkthrough. The other instance is a 2005 SQL Express instance. This is installed as a part of VSTS. We will use this instance as our development and integration server. You can also install SQL 2008 on the VM if you like. I have run 4 instances of SQL on a VM with 2GB RAM with acceptable performance for evaluation and proofing purposes.

To easily attach the TBH database to the production server copy the sql files to the default data directory of the default instance and run this T-SQL script:

Copy Command

C:\DasRoot>copy "C:\DasRoot\TheBeerHouse\TBH_Web\App_Data\ASPNETDB.MDF" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TBHDB.mdf"

1 file(s) copied.

C:\DasRoot>copy "C:\DasRoot\TheBeerHouse\TBH_Web\App_Data\ASPNETDB_log.LDF" "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TBHDB_log.ldf"

1 file(s) copied.

T-SQL to attach DB and add a login

EXEC sp_attach_db @dbname=N'TheBeerHouse',

@filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TBHDB.mdf',

@filename2=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\TBHDB_log.ldf'

USE TheBeerHouse

EXEC sp_addlogin 'TheBeerHouseApp', 'BeerNow!', 'master'

EXEC sp_grantdbaccess 'TheBeerHouseApp', 'TheBeerHouseApp'

EXEC sp_addrolemember 'db_datareader', 'TheBeerHouseApp'

EXEC sp_addrolemember 'db_datawriter', 'TheBeerHouseApp'

EXEC sp_addrolemember 'db_owner', 'TheBeerHouseApp'

You will need to also change your databases to use mix mode security since the application will be using a SQL Login for authentication. You can change the script to use a Windows login if you like, but for this walkthrough we will be using SQL Login.

This sample database includes some of the SQL diagramming tables. We won’t need these so you can clean them up by executing this T-SQL:

USE [TheBeerHouse]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_alterdiagram]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_alterdiagram]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_creatediagram]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_creatediagram]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_dropdiagram]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_dropdiagram]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_helpdiagramdefinition]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_helpdiagramdefinition]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_helpdiagrams]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_helpdiagrams]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_renamediagram]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_renamediagram]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_upgraddiagrams]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[sp_upgraddiagrams]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sysdiagrams]') AND type in (N'U'))

DROP TABLE [dbo].[sysdiagrams]

Ok, the database is there now so let’s adjust the solution to use this database. The application comes as a RANU Database. Since we will be maintaining the database as a project we don’t need the contents in App_Data anymore. We also need to change the connection string for the application. Before doing those things we also want everything in the same solution. We do this by:

1. Create a new solution [C:\DasRoot\TheBeerHouse\TBH.sln]. You do this by selecting File->New->Project->Other Project Types->Visual Studio Solutions.

2. Move both [C:\DasRoot\TheBeerHouse\TBH_Web] and [C:\DasRoot\TheBeerHouse\TBH_CustomEvents\TBHCustomEvents.csproj] directories under the TBH folder [C:\DasRoot\TheBeerHouse\TBH].

3. Add the website project to the new solution. You do this be right clicking the solution file in the Solution Explorer and selecting Add->Existing Website->Select Folder on Disk [C:\DasRoot\TheBeerHouse\TBH\TBH_Web]. Answer yes to upgrade question.

4. Add the Custom Events project to the new solution. You do this be right clicking the solution file in the Solution Explorer and selecting Add->Existing Project->Select Folder on Disk [C:\DasRoot\TheBeerHouse\TBH\TBH_CustomEvents\TBHCustomEvents.csproj]. Answer yes to upgrade question.

5. In the Web Project locate the App_Data folder and delete the ASPNETDB.MDF files.

6. In the Web Project open the web.config and update the connection string to use our new database. You do this by replacing the connection string section with this element:

</ connectionStrings >

7. Set the Web Project as the Start Up Project. (It should have already defaulted to this)

8. F5 to run the Web App against our newly attached Database. You should see something like this:

If you get errors then you probably didn’t set the SQL Security to Mixed Mode. ;)

We now have a working Web App running against our “production” database. Let’s now put the database into a database project. We will be using a 2005 Database Project and a 2005 Database Server Project. The Database Project is for the user created database objects. This is for all the database objects used by the web application. The Database Server Project is for our server level objects. We only have one, the SQL Login the web application uses to log into database. You accomplish this by:

1. Add a 2005 Database Project to the solution called TBHDB. You do this by right clicking the solution file in the Solution Explorer and selecting Add->New Project->Database Projects->SQL Server 2005.

2. Add a 2005 Database Server Project to the solution called TBHDBServer. You do this by right clicking the solution file in the Solution Explorer and selecting Add->New Project->Database Projects->SQL Server 2005.

Your solution should now look like:

We now need to populate the Database Project. We do this be reverse engineering the database we setup previously. You do this by importing the database into the database project [TBHDB]. When you import the database the database is shredded into individual SQL files. These individual files represent the schema of the Database. Since these files are simply SQL files and the files contain individual objects they are suitable to maintain and version in the project system. You import the database by right clicking on the database project node in Solution Explorer and select “Import Database Objects and Settings”. You will be presented with a dialog where you can define the connection to the database and define import settings. Let’s import the database by:

1. Defining a new connection to the TheBeerHouse database on the default instance. Click new connection. Server Name = “.”. Database Name = “TheBeerHouse”

2. Keep the “Script the column collation only when it..” option checked.

3. Keep the “Import extended properties” option checked.

4. Check the “Import permissions” and check the “Add imported permissions to the project model” also. We want to work with permissions for this walkthrough.

5. Check the “Override configuration with imported schema settings” . This will import all the database settings along with the schema. An example would be the collation of the database.

6. Leave the Max files per directory at 1000. This walkthrough doesn’t have very many files, but if you are working on a large schema you may want to drop this down to 250-500. Lots of files in one directory makes VS sluggish for some operations.

7. Click Start. When the database schema import completes click finish.

Setting up the Database Projects for Sandbox Development

You will notice that VS is now displaying status messages that it is building out the project’s model. It should complete quickly as the TBH schema is pretty simple. When it says “Ready” take a look at the VS Error List. You can do this by selecting View->Error List from the Main menu. You should see 9 Warnings and 1 Error.

When you import Database Projects the Error List is the first place you want to start. It will help you work through any Errors and Warnings as you do your house keeping to tidy up the database. Just like other VS project types, Errors block the build and Warnings are warnings that something may not be right with the project.

Let’s take care of the error first. The error is for an unresolved reference to a Login object from a database user object. The Login is the same login we previously added when setting up the database. Since Logins are a server level object it was not imported into the database project. We need to add it to our server project [TBHDBServer]. We could import the master database from the server into our server project to get the login. This would also bring down all the other user created objects in master. Since it is only one object let’s just add it to the server project ourselves. You do this by:

1. Locate the Server Project in Solution Explorer

2. Expand the Schema Objects->Server Level Objects->Security folder.

3. Right click on the Security folder and select Add->New Item.

4. Select the Login (SQL) template from the New Item Dialog.

5. Name the Login “TheBeerHouseApp” and click Ok.

6. You should now be staring at the CREATE LOGIN Script. Update password variable to “BeerNow!” and save the file.

7. Your TheBeerHouseApp.login.sql file should look like this:

8. We now need to create a database reference from the database project to the server project to resolve our error. Right click on the “References” folder under the TBHDB project. Select Add Database Reference.

9. You are now presented with a dialog for creating new Database References. It has selected the TBHDBServer project already. It has also added “master” as the literal for database variable. It does this since master is always master on the database server.

10. Click Ok. You will see the reference is added under the “References” folder of the TBHDB project. This dialog should look like:

11. Right Click on the TBHDBServer project and click Build. You do this so the database server project is validated and its model is updated.

12. Notice how the Error disappears from the error list. We now are resolving the Login to the database server project.

We should be left with 9 warnings. All the warnings are from stored procedures in the TBHDB project. One thing to note here is that unresolved references in stored procedures are usually warnings. The reason is that stored procedures have deferred name resolution. This means it is possible to create a stored procedure without the objects it uses as table sources existing. Out of the 9 warnings we will resolve 8 of them. 6 of these warnings are for references to sysobjects. Database and server projects do not include all built-ins like system tables, views, and catalogs. You need another database reference for these. Much like how we resolved the error to the login, we will resolve these warnings for sysobjects by referencing a logical model of the master database that contains all these built-ins. You may be thinking “why is this not in the server project we just created”? The fact is that database server projects only include user level created objects. You can resolve these reference warnings to sysobjects by:

1. Right click on the “References” folder under the TBHDB project again. Select Add Database Reference.

2. Select the “dbschema” option.

3. Select the master.dbschema file from disk located: C:\Program Files\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\2005\DBSchemas\master.dbschema

4. Leave database variable literal as “master”

5. The database reference dialog should look like this. Click Ok.

After a little while you should see 6 of the warnings for the missing [sysobjects] object disappear. What we have done to resolve this is added the master.dbschema model to our project that represents all the built-ins provided by master. We now have 2 references and both represent the master database. Yes, this is possible. You may have also noticed that processing the reference to master.dbschema takes much longer than it did for the server project. That is because the model for master.dbschema is much larger. Even though we only need the definition for the sysobjects table we took on the overhead of the whole master.dbschema. You may want to check this out for how to customize your master.dbschema file. Note: If you use customized dbschema files you will want to add them to your solution so everyone on the team can reference the same file. This also will become a part of the source and will be available when or where ever you do your builds.

We should be left with 3 warnings now. 2 of the warnings are for ambiguous column references in the ORDER BY clause of 2 stored procedures. The 2 table sources in each of the stored procedures both have the column that the results are ordered by. While this is fine for SQL to execute (it assumes the column in the SELECT list) it makes maintenance problematic. It’s better to be explicit in this case to prevent runtime errors should the other Added Date be included in the SELECT list. To resolve the warnings simply double click on each other warnings for tbh_Forums_GetUnapprovedPosts and tbh_Forums_GetUnapprovedPosts and replace the Added column in the ORDER BY clause with tbh_Posts.AddedDate. Save and close both files. You should see 2 warnings disappear. This leaves one warning left. The warning is for a session level temporary table. GDR R2 does not resolve references to session level temporary tables. Using session level temp tables is not a good design practice. Our guidance is to use table parameter instead as it makes the code more maintainable and design clearer. Since we don’t plan to resolve this warning we can suppress it. You can suppress the warning by:

1. Locate the aspnet_Setup_RestorePermissions.proc.sql in Solution Explorer

2. Right Click on file and select “Properties”

3. On the “Properties” dialog enter the warning number for this warning (4151).

4. The properties window will look like this:

5. Save the file.

You have no warnings now. Caution: By suppressing the warning 4151 for the aspnet_Setup_RestorePermissions.proc.sql file we just suppressed all 4151 warnings for this file. This might not be desirable as you will probably still want to know if other objects are unresolved. Unfortunately warning suppression is only supported at the file and project levels. If you want other unresolved warnings for the file you will have to live with the unresolved session level temporary warning or refactor the code to use a table variable instead. Sorry.

Now that we have dealt with our errors and warnings we have our solution in good shape to move on. Next we should deploy the database to our local sandbox. Deploying it locally and running the app against the sandbox database will give us the confidence that the database and application is fully functional. But, before we can do that we need to consider reference data. TBH application depends on some simple version metadata for the ASPNET User Profile components. We could run the Aspnet_regsql.exe that adds this data everytime we deploy a new database or we can just deploy it ourselves. After all, we are already versioning the User Profile tables we might as well version the reference data required for ASPNET to make use of them. To do this easily we just simply add this T-SQL to the post deployment script of our TBHDB project:

IF NOT EXISTS

(SELECT * FROM dbo.aspnet_SchemaVersions)

BEGIN

SET NOCOUNT ON

PRINT 'Adding [dbo].[aspnet_SchemaVersions] Data'