Almost in every development step in the field of web-development is a data base involved. Also there is a system to control the version (I hope so). But how is it possible to work in team so that everyone is able to use the newest version of the database?
Like always, there are many solutions. I´ve chosen this way because for me it´s the best one.
Central alternative
Like i said before, there are several alternatives. For example I could create a data base on a central SQL server and everyone access on this. Advantage: you don´t need to install an SQL-Server on every machine. Disadvantages: The developer in particular will be influenced if you work on the DB. It´s also bad if you didn´t work on the same network all the time. In an Open Source Project or a hobby project it won´t be able to have a central instance anyway.
Autarkic alternative - our conditions
During our BizzBingo (Codeplex) we decided this alternative:
- Every developer has his own SQL server installed
- The data base is named equal on every developer machine
- On the SQL server Windows authentication is activated and there is no special account
- Only SQL scripts will be saved on the Source control
- No .mdf files or anything like that are on the source control
SQL Script only? - YES!
The Entity framework or NHibernate are able to create the model of a db scheme easily. The question is: what is this good for? Someday you will finish your version 1.0 of your application and it will run.
Version 1.1 - how does the update work?
Now we go on developing and get to an interesting question: How do I update my productfilebase?
The way with the highest possibility of making mistakes: Click around wildly in your SQL management studio. You will get a little plus if you saved the SQL scripts which are created by the management studio and reboot them on the productivity system. But this is stupid. If you use SQL Azure you will see, that there is no designer just scripts 
How we solved it on BizzBingo:
We thought about an initial set of data bases, tables and demo files. Therefore we used the UnitScripts:
InitSchema.sql is like the Version 1.0 of the file base without files.
InitData.sql is made for test files.
If an update of the data base is necessary we use an "UpdateScript" folder which contains SQL Scripts for every data base update. The newest SQL scripts work in the transaction.
Example - here we included different languages in our "word table":
begin transaction DataUpdate begin try INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'empower', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'communicate', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'skill set', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'think outside the box', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'gap analysis', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'validate', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'revenue', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'heavy lifting', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'value-added', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'networking', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'go the extra mile', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'big picture', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'stretch the envelope', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'client-focused', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'bottom line', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'bleeding edge', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'recognition', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'ramp up', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'business case', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'train wreck', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'bucketize', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'exponential', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'synergize', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'knowledge management', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'quality gap', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'disintermediate', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'goal', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'fast track', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'actualize', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'operationalize', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'cash-neutral', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'implementation', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'phase', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'risk management', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'backward-compatible', 0, '1033') INSERT [dbo].[Word] ([Id], [Value], [Rating], [LCID]) VALUES (NEWID(), N'adaptive', 0, '1033') update [Version] set VersionNr = '1.4' print 'VersionNr updated to 1.4' commit transaction DataUpdate print 'transaction "DataUpdate" committed' end try begin catch rollback transaction DataUpdate print 'transaction "DataUpdate" rolled back' end catch
Like you see in line 42 we saved a version number in the SQL data base - the script is written by our self and not with the management studio.
"Automatically" updates
If Ken, who is my made in this project, makes an update on the data base structure (Version 1.7) than he do so on his machine and save the update SQL script in the source control.
Because we are a little team the next step isn´t that automatically. I pick up the newest source and recognize there is a new script. Now I´m going to run the InitDatabase.bat which will call the three Powershell Scripts:
powershell ToolingScripts\CreateDatabase.ps1 -dbInstance "." -dbName "BusinessBingo" powershell ToolingScripts\InitDatabase.ps1 -dbInstance "." -dbName "BusinessBingo" -initScriptsFolder "InitScripts" powershell ToolingScripts\UpdateDatabase.ps1 -dbInstance "." -dbName "BusinessBingo" -updateScriptsFolder "UpdateScripts"
CreateDatabase.ps1 - save data base clean
The script looks up if there is another file base named "BusinessBingo". If there is one he will delete the old and creates the new one with the same name. If there wasn´t one before he is going to save this one. This script is from the blog of Dave Donaldson.
Result: a clean start point:
# Get the parameters passed to the script
Param($dbInstance, $dbName)
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$dbServer = new-object Microsoft.SqlServer.Management.Smo.Server ($dbInstance)
$db = new-object Microsoft.SqlServer.Management.Smo.Database
# Loop thru the db list to find the one we need. If found, set the local
# vars to avoid errors when trying to delete the db from within the loop.
$found = "false"
foreach ($_ in $dbServer.Databases)
{
if ($_.Name -eq $dbName)
{
$db = $_
$found = "true"
}
}
# Now that we're out of the loop we can kill the db
if ($found -eq "true")
{
"Deleting database $dbName..."
$dbServer.KillAllProcesses($db.Name)
$dbServer.KillDatabase($db.Name)
}
"Creating database $dbName..."
$db = new-object Microsoft.SqlServer.Management.Smo.Database ($dbServer, $dbName)
$db.Create()
InitDatabase.ps1 - create Version 1.0
In the next step we will open the .bat powershell script to initialize the DB. Just the InitScheme.sql and the InitData.sql will be opened.
Param($dbInstance, $dbName, $initScriptsFolder) "InitDatabase..." "Execute SchemaSql..." sqlps Invoke-Sqlcmd -ServerInstance "$dbInstance" -Database "$dbName" -InputFile "$initScriptsFolder\InitSchema.sql" "Execute DataSql..." sqlps Invoke-Sqlcmd -ServerInstance "$dbInstance" -Database "$dbName" -InputFile "$initScriptsFolder\InitData.sql"
UpdateDatabase.ps1 - create all versions
After we´ve created version 1.0 data base all SQL scripts in UpdateScripts directories will be searched and opened.
Param($dbInstance, $dbName, $updateScriptsFolder)
# find all scripts in update dir
$UpdateDir = get-childitem $updateScriptsFolder
$List = $UpdateDir | where {$_.extension -eq ".sql"}
"UpdateScripts found:"
$List | format-table name
foreach ($updateSql in $List)
{
"Execute $updateSql"
sqlps Invoke-Sqlcmd -ServerInstance "$dbInstance" -Database "$dbName" -InputFile "$updateScriptsFolder\$updateSql"
}
This happens after one click on InitDatabase.bat
The "relaxing" of this alternative: You can be sure that these scripts will be checked ever and ever.
With the update scripts it is sure that the scripts will be used on the productivity system and they will work!
What´s with the Unit Tests?
Thomas Brandt describes in his blogpost how Repository Tests could look like. We thought this is a good alternative.
Thomas idea was to use the feature of Entity framework: create a data base from a model with a code. What we want to reach: Be sure that the update scripts, which are in the source control, are always work properly.
Like Thomas we have a basic class from which all Repository Tests start (if you want to see the full code take a look on Codeplex) :
...
namespace BusinessBingo.Data.Tests
{
[Category(TestCategories.RepositoryTests)]
public abstract class ConcernOf<T> : BaseTestFixture
{
...
protected ConcernOf()
{
...
}
protected void Init()
{
...
this._databaseHelper.InitTestDatabase();
}
public abstract void Setup();
[CleanUp]
public abstract void CleanUp();
}
}
In the Init our DatabaseHelper will be called. Here you will find the same game like in the "CreateDatabase.ps1". With the Entity framework model (which we used as repository layer), we ask if the data base exists.
public void InitTestDatabase()
{
if (this._efModel.DatabaseExists() == false)
{
this.CreateTestDatabase();
}
else
{
string required = this.GetRequiredVersion();
string current = this.GetCurrentVersion();
if(required.Equals(current) == false)
{
this.DeleteDatabase();
this.CreateTestDatabase();
}
}
if (this._efModel.DatabaseExists() == false)
throw new SystemException("Die Datenbank konnte nicht erstellt werden");
this.CleanUpDatabase();
this.InsertTestData();
}
CreateTestDatabase, which will be opened anyway. Here we call the powershell order from the .NET code. Because of this the Powershell scripts took some parameters more so you can start them from the unit tests as well.
public void CreateTestDatabase()
{
Runspace runspace = RunspaceFactory.CreateRunspace();
runspace.Open();
string dbInstance = ".";
string dbName = "BusinessBingoTestDatabase";
string scriptParameters = string.Format("-dbInstance {0} -dbName {1}", dbInstance, dbName);
string toolingScriptFolder = string.Format(@"{0}\ToolingScripts", this._databaseFolder);
Pipeline pipeline = runspace.CreatePipeline();
string createDatabaseCommand = string.Format(@"{0}\CreateDatabase.ps1 {1}", toolingScriptFolder, scriptParameters);
pipeline.Commands.AddScript(createDatabaseCommand);
string initScriptsFolder = string.Format(@"{0}\InitScripts", this._databaseFolder);
string initDatabaseScriptParameters = string.Format("{0} -initScriptsFolder {1}", scriptParameters,
initScriptsFolder);
string initDatabaseCommand = string.Format(@"{0}\InitDatabase.ps1 {1}", toolingScriptFolder, initDatabaseScriptParameters);
pipeline.Commands.AddScript(initDatabaseCommand);
string updateScriptsFolder = string.Format(@"{0}\UpdateScripts", this._databaseFolder);
string updateDatabaseScriptParameters = string.Format("{0} -updateScriptsFolder {1}", scriptParameters,
updateScriptsFolder);
string updateDatabaseCommand = string.Format(@"{0}\UpdateDatabase.ps1 {1}", toolingScriptFolder, updateDatabaseScriptParameters);
pipeline.Commands.AddScript(updateDatabaseCommand);
pipeline.Commands.Add("Out-String");
Collection<PSObject> results = pipeline.Invoke();
runspace.Close();
}
In fact just the three powershell - scripts will be opened and the same thing will happen like in the InitDatabase.bat.
"Feeling save"
This looks quite complicated? I don´t think so because mostly it will be done by conventions later.
But what we reach with this is a high property: safety. We know that we are able to create a new data base with just one click and we also know that we are able to update a db with the help of the script.
Important conditions
Of course the scripts need to migrate the files and not only the shames. Actually this isn´t that important because it´s just an unknown hobby project at the moment. We´ve stopped this at one step because it wasn´t useful to write migrationsscripts for demos.
Other technics
Some of my associates told me about the opportunity to do this data base stuff with a Visual Studio project template. Yesterday I found this blogpost. But at the moment we use this one.