How to automatically check out a database file in a source controlled web application ?

Question

Hello,

I am working on an ASP.NET web application, we are a small team (4 students) and we do not have access to a dedicated server to host the database instance. So for this web application we decided just to put the database file in the App_Data folder.

The problem is that our project is source controled on TFS, so every time you open the solution and try to launch the web application, we get an expcetion saying that database is read-only. That is logical because the databse file is not automatically checked-out.

Is there a workaround to avoid a manual check-out of the database file everytime we open the solution ?

Thanks.

Answer 1

Never check in MDF files. You should develop your project with a deployment script that creates the tables and other database objects you need. Never modify the MDF directly from VS Server Explorer. Use a version based approach to database schema.

I know you'll say 'but is hard', but trust me, the alternative which VS wizards guide you is far worse. Not only from a team development point of view, but from deployment point of view too. After you deploy v1 of you site, you'll make changes and want to deploy v1.1 or v2. Each time, you'll face the same dilemma: you have 4 MDF files, one on each team member enlistment, and 1 on the deployed site, and there is simply no way to align the schemas. You'll end up asking for SQL diff tools like SQL Compare, but they cost a tonne of moneys and they're far from perfect when it comes to maintaining a SQL schema.

Besides, having scripts as your database source will also align well with all the other source control goodies, like tracking down who changed what and when.

Answer 2

If you are able to install "Visual Studio Team System Database Edition" add-on for Visual Studio (previously called "Visual Studio Team Edition for Database Professionals", previously called "DBPro") you can import your current database into a Visual Studio Database project, which can be part of your overall VS Solution.

This then allows you to manage schema objects (tables, stored procs, permissions etc.) in Visual Studio and integrates your database schema nicely with TFS.

It also allows you to deploy the schema in your VS Project to the database server, automatically handling modifications without causing data loss (or failing if data would be lost).

The add-on for VS 2008 is located at: http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en