Kynetix Blog

Contents

Introduction | Objectives | Objective 1: Create a new database |
Objective 2: Create a Full Upgrade | Deploying Manual Schema Changes | Objective 3: Create a partial upgrade | Using Configurations | CruiseControl Integration | NetTiers Integration

Introduction

Maintaining your database structure within Visual Studio allows you to rebuild the database from scratch and keep a record of the modification history in SourceSafe or TFS.

On one particular project, I used the standard SQL Server Project provided in Visual Studio 2005 creating my own directories to separate schema objects such as tables and stored procedures. A custom application would run the scripts in the project in a defined order to allow you to create and upgrade the database.

Recently I decided to migrate the solution to Visual Studio 2010.

When I attempted to convert the SQL Server Project I came across all kinds of issues. Such as the compilation not working because some commands such as DROP and EXEC were not supported.

I also tried to reverse engineer the database project from SQL Server into Visual Studio 2010. Being a bit of a control freak, this did not satisfy me because I didn’t know what the reverse engineering had created. I could see there were issues (e.g. permissions were not scripted how I would like), so I decided to hand-craft the project from the scripts that already existed in the old SQL Server project.

Below is a record of the objectives, along with everything that needed to be done to achieve the objectives.

Objectives

There were 3 objectives:

1. Create a new database with test data
2. Create a full upgrade: restore the previous version of the database and run schema and data upgrades
3. Create a partial upgrade: run schema and data changes on a database that has already been generated via the full upgrade (e.g. stored procedure changes) (for high load iterative development or potentially iterative deployment)

Objective 1: Create a new database

Once you have put all the scripts into the Database Project (make sure they have a Build Action of ‘Build’) it is very simple to create a database.

1. Open your Visual Studio solution. Right click on the Database Project and select Properties.
2. In the Deploy section set the connection string and the target database name.
   

   Configuring the Deployment, setting the database name and connection string.

3. Edit the Deployment Configuration File to select the ‘Always re-create the Database’ option.
   

   Setting the database to always re-create

Objective 2: Create a Full Upgrade on top of a medium volume database restored from a backup

(tables have up to 10 million rows)

In this project, as part of each upgrade, we initially restore a very stable baseline version of the database which includes static data.

In this example, we tried to use the built-in Database Project upgrade but soon discovered that when updating one of the tables with many rows the internal upgrade method was slow and high risk.

It also offered no support for complex data movements that could be done mid-schema change.

With an update we want to be in control of the majority of schema changes to prevent the database project engine from generating inefficient upgrade scripts with complex data movements
(e.g. We didn’t want to create a temporary table when modifying the schema of a table with millions of records in it).

Where should this manual schema change be done?

The Database Project builds and deploys in the following order:

1. Build files
2. Compare file schema to target database and generate upgrade.sql file
3. Run Pre-Deployment files
4. Run script generated in step 2 to update schema
5. Run Post Deployment scripts

Therefore to implement hand crafted schema changes we needed to have a custom MSBuild step that ran before the build started.

By doing this the schema changes are in place before the database project creates its SQL file in Step 2 (which otherwise would prevent this script from making schema changes).

Therefore, the project will build and deploy in the following order:

1. Run custom step to make controlled schema changes
2. Build files
3. Compare file schema to target database and generate upgrade.sql file
4. Run Pre-Deployment files
5. Run script generated in step 2 to update schema
6. Run Post Deployment scripts

A breakdown of the tasks running in each step are as follows:
(I will discuss how Tasks 1, 4 and 6 were completed below)

Task	Description	What is changing in the database
1	Run custom step to restore the database and make controlled schema changes.	Restore database. Implement table changes and related complex data movements/dependencies on these table changes on the target database
2	Build Files (MsBuild running dbProj)	Nothing
3	Compare file schema to target database and generate upgrade sql file (Database Project utility)	Nothing
4	Run Pre-Deployment files (custom built)	Nothing
5	Run script generated in step 2 (Database Project generated)	Implement all other schema changes not done in step 1
6	Run Post-Deployment scripts (custom built)	Security (see Security considerations below), Maintenance tasks (e.g. reindex, fill factors etc), Data Tasks (e.g. version specific tasks and creating test data)

Implementing the pre-build step (Task 1)

1. Create a class project that references the Microsoft.Build.Utilities dll and the Microsoft.Build.Framework dll.

2. Create a class file that implements Microsoft.Build.Utilities.Task interface.

This should override the Execute method. The Execute method will be called from MSBuild to run the task and returns true or false to indicate success or failure. From the Execute method SQL scripts can be run.

Notes

• To write messages to the Visual Studio Output window (in Build mode) use the Microsoft.Build.Utilities.TaskLoggingHelper.Log.LogError and Microsoft.Build.Utilities.TaskLoggingHelper.Log.LogMessage methods.

For messages to be written to the Output Window they should have MessageImportance set to High. You can modify Visual Studio settings to display other priority messages.

• To restore a database, a SQL script was created. It uses the RESTORE DATABASE command.It references a .bak file (10GB) on the local system. This is a backup file representing a baseline version of the database. This large backup file should just be copied onto the local system once. The path to the backup file is defined in the custom command variables file.
• A method was generated to replace variable values in the custom command variables file with those in the SQL scripts.
  E.g. the SQL script contains:

PRINT 'Drop existing database with name: $(DatabaseName)...';

The method will replace $(DatabaseName) with the value for DatabaseName in the sqlcmdvars file.

• In the Database Project sqlcmdvars file add values that can be used to determine whether the Pre-Build task should run. See screenshot below.
  

  $(DoCreateUpgradeDatabase) variable is used to determine whether to run the Pre-Build task

3. Add Public Properties to the class to receive information from the build.
The public properties added were: DatabaseName, ConnectionString and SqlCommandVariablesFile

4. In the Database Project Scripts folder add a folder called ‘Pre-Build’.

5. In the Pre-Build folder add an xml file containing tasks to run, in the order they should run.

<?xml version="1.0" encoding="utf-8" ?>
<!--List the tasks in order to run them-->
<tasks>
   <task type="Restore">Setup\RestoreDatabase.sql</task>
   <task type="SqlScript">Version\2.0.0.0\ValidateData.sql</task>
   <task type="SqlScript">Version\2.0.0.0\RemoveRedundantStaticData.sql</task>
   <task type="SqlScript">Version\2.0.0.0\UpdateSchema.sql</task>
   <task type="SqlScript">Version\2.0.0.0\Schema And Complex Data Movement.sql</task>
</tasks>

6. In the Pre-Build folder add Setup and Version folders to contain the scripts. Each script should have a Build Action of ‘Not In Build’.

7. Edit the Database Project file (*.dbproj) to add a custom step that references the Build Utilities class created in Step 1 and 2.

<UsingTask AssemblyFile="$(SharedTasksDir)\MsBuild.CustomSteps.dll" TaskName="RunPreBuildTasks" />
  <Target Name="BeforeBuild">
DatabaseName="$(TargetDatabase)" ConnectionString="$(TargetConnectionString)" SqlCommandVariablesFile="$(SqlCommandVariablesFile)">
    </RunPreBuildTasks>
  </Target>

Notes:

• The Target Name value should always equal BeforeBuild . Warning! If you just build the database it will run the restore.
• Custom variables are not passed through using this method, but you can pass through the reference to the custom variables file (sqlcmdvars) from the build and read custom variables directly from the file xml.
• The list of variable names that can be passed in e.g. $(TargetDatabase) are available in the project Macros list (found in Project Properties, Build Events, Edit Pre-Build…)

8. In the Database Project add a Shared Tasks folder. Compile the Custom Steps class and copy the dll into this folder.

While working on this upgrade we observed that the column orders need to be the same in the project schema files and the target database otherwise the Database Project will update the schema.

Run Pre-Deployment Scripts (Task 4)

Not employed in this example.

Run Post-Deployment Scripts (Task 6)

The standard file provided by the Project is the Script.PostDeployment.sql file (Build Action: PostDeploy).

Rather than having one huge script it seemed sensible to use SQLCMD to reference other smaller, more manageable, SQL scripts.

Each of these scripts have a Build Action of ‘Not In Build’.

To reference scripts do the following:
1. Right click on the Script.PostDeplopyment.sql file and select the SQLCMD mode.
2. Reference the files using the :r command (see below)

print 'Post-Deployment Script Started...misc'
--update the fill factor on all indexes
:r Maintenance/UpdateFillFactor.sql

Notes

• TSQL commands can still be used in the file when in SQLCMD mode
• Using ‘Print’ will write a message into the Output Window.
• All variables can be referenced in the .sql file (e.g. Built in ones and ones defined in sqlcmdvars file) using the $.

if ('$(User)' = 'Network Service')
    begin
        :r Security/CreateNetworkServiceUser.sql
    end

3. The types of scripts run in post deployment are Data, Maintenance, Security and Version specific.

a) Data:

Bulk insert was selected to import files (low volume testing data). The csv files needed to be available on the SQL Server running the script. The Post Deployment script referenced a Data Manager script also in SQLCMD mode. This Data Manager script then ran the SQL scripts in the relevant order.

BULK INSERT test.Files
FROM 'C:\Data\Build\TestFiles.csv'
WITH
(
    /* skip the first row containing column names */
FIRSTROW = 2,
    /* specify how fields are separated */
FIELDTERMINATOR = ',',
    /* specify how lines end */
ROWTERMINATOR = '\n',
KEEPIDENTITY
)

b) Maintenance (e.g. update statistics)

c) Security:

The Database Project provides a Database.sqlPermissions file. In the file you have to explicitly list all the permissions on the database. The way our database is set up is with a SQL Server Role which has all the permissions required. As we have over 2000 stored procedures I did not want to list all of the stored procedures and have the maintenance task afterwards.

Running the following script is far simpler:

fetch next from procedures into @name
while @@fetch_status = 0
begin
select @exString = 'GRANT EXECUTE ON ' + @name + ' TO [sqlRole]'

exec (@exString)

fetch next from procedures into @name
end

d) Version:

All data tasks to run on an upgrade e.g. update the database version.

Objective 3: Create a partial upgrade

If you recall above we set a variable called $(DoCreateUpgradeDatabase). If I set this to false then the database restore does not run.

I can then change the Project Properties | Deploy | Deployment Configuration | Always re-create Database to false. This will invoke a upgrade of any schema changes.

It is probably best to also tick the ‘Block incremental deployment if data loss might occur’. That way your project will fail if a schema change scripted by the database project may cause rows to be removed from the data tables.

Using Configurations

Local development

To make day to day development easier several configurations were created to allow the developers to easily run the deployment for Create, Full Upgrade and Partial Upgrade. Each Configuration had its own sqlcmdvars file and sqldeployment file with pre-defined connection strings, database names and the variables used in the custom steps and post deployment scripts.

CruiseControl Integration

Several different types of database builds are done on the build server:

• Fitnesse Builds (new database with test data imported)
• Running Automated Tests (new database with test data imported)
• Generating Nettiers Code (new database with test data imported)
• Building Test sites (from new database and from high volume upgraded database) (new database with test data imported OR upgraded database from .bak file)

As general rules exists between these types of builds only 3 Configurations were created to allow a CI build and deploy from new and upgrade (full upgrade path and Partial upgrade path).

The target database could be modified from the ccnet.config file settings.

  <tasks>
    <msbuild>
      <executable>C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe</executable>
      <workingDirectory>c:\BuildArea\Database\Source\SqlServer</workingDirectory>
      <projectFile>Database.dbproj</projectFile>
/noconsolelogger /t:Rebuild /p:Configuration=CI_Create
           /p:TargetDatabase=NewDatabase
           /p:SolutionDir="c:\BuildArea\Database\Source\SqlServer"
/v:diag
      </buildArgs>
      <logger>C:\Program Files (x86)\CruiseControl.NET\server\ThoughtWorks.CruiseControl.MsBuild.dll</logger>
      <timeout>1800</timeout>
    </msbuild>
    <msbuild>
      <executable>C:\WINDOWS\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe</executable>
      <workingDirectory>c:\BuildArea\Database\Source\SqlServer</workingDirectory>
      <projectFile>Database.dbproj</projectFile>
/noconsolelogger /t:Deploy /p:Configuration=CI_Create
/p:TargetDatabase=NewDatabase /v:diag
      </buildArgs>
      <logger>C:\Program Files (x86)\CruiseControl.NET\server\ThoughtWorks.CruiseControl.MsBuild.dll</logger>
      <timeout>1800</timeout>
    </msbuild>
  </tasks>

NetTiers Integration

Nettiers is used to generate the data access layer and a set of stored procedures. These stored procedures are created in a large SQL file (ours was over 85,000 lines).

When including this large file in the Database Project (with Build Actions set to Build) the Database Project could not cope. It would get stuck trying to resolve the file and check its references stating ‘Verifying your model is synchronized with your source files. Your data project will be ready after xxx operations are completed’.

Originally to solve this issue the file was split into about 1300 individual stored procedure files and included in the project. The code to generate the Nettiers stored procedures and update these stored procedures within the database project was taking a very long time as it checked the files in and out of TFS.

Therefore the original large file was reinstated but this time the Build Action was changed to ‘Not In Build’. It was run via the post deployment script (before permissions are set). The action to check out of TFS is reduced as only 1 file is checked out rather than 1300.

This fits into the requirements as to upgrade the Database it would remove all the Nettiers stored procedures as it wouldn’t find them in the project as part of the schema. (This is OK as would remove redundant ones also.)

The stored procedures would then be created at the end of deployment by running the Nettiers.sql files in the Post Deployment folder. When creating a new database the Stored Procedures would just be created at the end.

This entry was posted on Tuesday, May 31st, 2011 at 16:14 and is filed under SQL Server, Visual Studio. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.