Continous Integration and the Database

Spend some few days setting up a continous integration for a database project. Found some stuff on the internet but they got it all wrong :)

I am using Team Foundation Server 2008.

The only thing you need to do is modify the TFSBuild.proj

Modify the following sections. This enabled the automatic generation of a sql script that will be copied in the Drop Folder.
Find the SoltionToBuild tag and add the properties as shown below:

<SolutionToBuild Include="$(BuildProjectFolderPath)/../../CIDB/CIDB.sln">
<Targets></Targets>
<Properties>DefaultDataPath=C:\BuildDrop\;TargetDataBase=TestDb</Properties>
</SolutionToBuild>

Add the default configuration to the ConfigurationToBuild ItemGroup as shown below (make sure to match it to your settings in the Configuration Manager)
<ItemGroup>
<!-- CONFIGURATIONS
The list of configurations to build. To add/delete configurations, edit this value. For example,
to add a new configuration, add the following lines:

<ConfigurationToBuild Include="Debugx86">
<FlavorToBuild>Debug</FlavorToBuild>
<PlatformToBuild>x86</PlatformToBuild>
</ConfigurationToBuild>

The Include attribute value should be unique for each ConfigurationToBuild node.
-->
<ConfigurationToBuild Include="ReleaseAny CPU">
<FlavorToBuild>Release</FlavorToBuild>
<PlatformToBuild>Any CPU</PlatformToBuild>
</ConfigurationToBuild>

<!--Include the configuration to build this generates a .sql file in the drop folder-->
<ConfigurationToBuild Include="DefaultAnyCPU">
<FlavorToBuild>Default</FlavorToBuild>
<PlatformToBuild>Any CPU</PlatformToBuild>
</ConfigurationToBuild>
</ItemGroup>

Next add the following section to the end of the TFSBuild.proj file. the %3B is the escape character for ';' in MSBuild

<PropertyGroup>

<TargetConnection>Data Source=.%3BIntegrated Security=True%3BPooling=False</TargetConnection>
<DefaultDataPath>C:\BuildDrop\</DefaultDataPath>
<TargetDataBase>DevDb</TargetDataBase>
</PropertyGroup>


<Target Name="AfterDropBuild">
<MSBuild Projects="$(SolutionRoot)\CIDB\CIDB\CIDB.dbproj"
Properties="Configuration=Default;
OutDir=$(SolutionRoot)\..\binaries\Default\;
AlwaysCreateNewDatabase=false;
TargetConnectionString=$(TargetConnection);
DefaultDataPath=$(DefaultDataPath);
TargetDataBase=$(TargetDataBase)"
Targets="ReBuild;Deploy" /> <!—Need to specify the Rebuild target otherwise fails -->
</Target>

Update:

If you modify your source folder setting in the workspace - defaults to $(SourceDir)- make sure that you override your SolutionRoot property

by adding the following

...

</ProjectExtensions>
<PropertyGroup>

<SolutionRoot>C:\Workspace_Build02 </SolutionRoot>

....

Thats all there is to it :)



Coming up automatically generate an upgrade script to deploy to production systems... I installed the Database Team Edition on the build agent but I am not quite sure if its necessary I will investigate this... I dont know if its default part of ms build

Guid vs Int

Rather interesting article on when tho use a guid or an int as a Primary Key:

http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

Summary:

IDENTITY
PROS
small storage footprint; optimal join / index performance (e.g. for time range queries, most rows recently inserted will be on a limited number of pages); highly useful for data warehousing; native data type of the OS, and easy to work with in all languages; easy to debug (where userid = 457); generated automatically (retrieved through SCOPE_IDENTITY() rather than assigned); not updateable (though some consider this a disadvantage, strangely enough).
CONS
cannot be reliably "predicted" by applications — can only be retrieved after the INSERT; need a complex scheme in multi-server environments, since IDENTITY is not allowed in some forms of replication; can be duplicated, if not explicitly set to PRIMARY KEY: CREATE TABLE splunge( splungeID INT IDENTITY(1,1), foo CHAR(1) ) GO SET NOCOUNT ON INSERT splunge(foo) VALUES('a') INSERT splunge(foo) VALUES('b') INSERT splunge(foo) VALUES('c') GO DBCC CHECKIDENT('splunge', RESEED, 2) GO INSERT splunge(foo) VALUES('d') GO SELECT * FROM splunge GO DROP TABLE splunge GO if part of the clustered index on the table, this can create an insert hot-spot; proprietary and not directly portable; only unique within a single table; gaps can occur (e.g. with a rolled back transaction), and this can cause chicken little-style alarms.
GUID()
PROS
since they are {more or less} guaranteed to be unique, multiple tables/databases/instances/servers/networks/data centers can generate them independently, then merged without clashes; required for some forms of replication; can be generated outside the database (e.g. by an application); distributed values prevent hot-spot (as long as you don't cluster this column, which can lead to abnormally high fragmentation).
CONS
the wider datatype leads to a drop in index performance (if clustered, each insert almost guaranteed to 'dirty' a different page), and an increase in storage requirements; cumbersome to debug (where userid = {BAE7DF4-DDF-3RG-5TY3E3RF456AS10}); updateable (need to propogate changes, or prevent the activity altogether); sensitive to time rollbacks in certain environments (e.g. daylight savings time rollbacks); GROUP BY and other set operations often require CAST/CONVERT; not all languages and environments directly support GUIDs; there is no statement like SCOPE_GUID() to determine the value that was generated, e.g. by NEWID(); there are display issues to consider, such as response.write issues (see Article #2358), and also consider cases where you display a large amount of rows as checkboxes or other form elements with IDs - your page just got a whole lot bigger, and you have to handle the IDs in a custom way because JavaScript can't deal with objects that have dashes in their name/id.