Configure your TFS and SQL Server environment to work at home

Would you like to take some work of your company to do it at home, maybe, because project time pressure or feel sick and you are very responsible or just you love it? If answer is YES and your project is managed with TFS and uses SQL Server as database, I will explain how to configure your environment in a stand-alone way to be properly built in another computer with minimal development requirements. If you achieve it and your bosses will thank even more your efforts :)


TFS Configuration

Let's say, you have your environment working in your workstation and you want to copy in an USB and, then, run it in your homestation. The easiest part is the TFS one. In your homestation surely you won't have connection with the TFS server, so you will have to work offline and, obviously, you still want to track the changes you do within the project.

TFS helps you in two ease steps:

1. Prepare a new workstation in TFS. TFS is able to work with several workspaces in other locations, just you need to add a new workstation named USB on the top of the Source control explorer (see below figure), switch to this new workstation and map your solution to a new location into the USB device. And that's it.

2. Go offline feature. When you have the USB workspace as current, you just have to go to the solution and set the source control the "Go to offline". Simple. When you connect again this workspace with the tfs server, just to set the "Go to online" and you will be able to check-in all your changes in a proper way without lose anything.

Database configuration

If your project does not rely on any database, you don't need to follow reading. This configuration is much complex the previous one because we need to retrieve all data and schema of a SQL Server instance database.

What we will do is to have a database file and to include it in the solution. Then, we will add another connection string in the web configuration (or application configuration) and we will comment the one that we don't use in our current workspace. Simple but it is not.

The first thing that we have to think is the type of our application, if it is a Silverlight one, you need to use a SQL Compact instance. So, we will assume that we always have to generate a sdf database file (SQL Compact one). After much research, you can do it following these steps:

1. Create the SDF database file from our database SQL Server instance. In order to perform this, there is a plugin for VS 2010 to do it but for me, it was quite easier through a bat file. We need to download the ExportSqlCE and sqlcecmd tools and put them into a same folder, then run the following bat file that has (do not forget to set your connection string):


ExportSqlCE40 "Data Source={ENVIRONMENT};Initial Catalog={DATABASE};Integrated Security=True"  databasefile .sqlce
sqlcecmd40 -d "Data Source=C:\databasefile.sdf" -e create
sqlcecmd40 -d "Data Source=C:\ databasefile .sdf" -i databasefile.sqlce > log.txt
del log.txt
for %%f in (databasefile*.sqlce) do sqlcecmd40 -d "Data Source= C:\databasefile.sdf " -i %%f >> log.txtt
pause


Links to tools:
http://exportsqlce.codeplex.com/
http://sqlcecmd.codeplex.com/

2. Copy the result sdf into the Data folder on the project into your solution and set the connectiong string:

<add name="DbSqlConnection" connectionString="Data Source=|DataDirectory|databasefile.sdf" providerName="System.Data.SqlServerCe.3.5" />

3. Reference Sql Provider CE into the project.

And that's it. There should work.

BUT if you are using ASP.NET MVC and you have installed the default version of SQL Compact 3.5, this won't work... Sql Compact is not compatible with it... Don't worry, there is a solution for this, you need to update your SQL Compact version to the 4.0:

3.a: So, install the following packages (and ensuring that you are linking the latest assembly of System.Data.SqlServerCE in your project (it should be the 4.0 version):

- Sql Server Compact 4.0 from http://www.microsoft.com/download/en/details.aspx?id=17876.
- Visual Studio tools update for Sql Server Compact 4.0: http://go.microsoft.com/fwlink/?LinkId=212219 
- Latest version of Web Platform: http://www.microsoft.com/web/gallery/install.aspx

Of course, update the version of sql server compact in the connectionstring to:

<add name="DbSqlConnection" connectionString="Data Source=|DataDirectory|databasefile.sdf" providerName="System.Data.SqlServerCe.4.0" />

And it should work using Entity Framework in your ASP.NET solution.

BUT if you are using ASP.NET Membership with your database, this won't work... And again there is a solution, you need to download the System.Web.Providers via NuGet (or searching via web) and changing the membership provider.

3.b: Install System.Web.Providers via NuGet and changing the sql providers in the config file following the next equivalences for universal providers:

System.Web.Security.SqlMembershipProvider -> System.Web.Providers.DefaultMembershipProvider
System.Web.Profile.SqlProfileProvider -> System.Web.Providers.DefaultProfileProvider
System.Web.Security.SqlRoleProvider -> System.Web.Providers.DefaultRoleProvider
(Built in provider) -> System.Web.Providers.DefaultSessionStateProvider

And, now YES, this should work!

Hope, this helps to many people :)