views:

299

answers:

1

After creating a SQL Server 2008 database, I made a Linq-to-SQL schema in Visual Studio. Next, in the .dbml visual editor (in Visual Studio 2010), I added PK-to-FK and PK-to-PK associations to the schema.

How do I copy those associations that I created in Visual Studio over to the database? In other words, how do I sync with the DB?

+1  A: 

Linq-to-SQL is a great tool, but never ever rely on it to update the schema. Always apply schema changes through upgrade SQL scripts that use DDL CREATE/ALTER/DROP statements to change the deployed schema from the on-disk version to the current version. This way you keep all you schema changes under version control as source text files, you can upgrade from any past version to the current application version and you can easily test all your upgrade paths, including upgrading deployments with very large tables.

The far worse alternative is to use SQL compare tools that can diff your desired schema from your deployed schema, like vsdbcmd. This may work on trivial deployments, but copying and renaming tables of millions of records will quickly show it's ugly downside.

What you absolutely cannot do is rely on the .dbml file as the 'master' copy of your schema. This is a little detail usually omitted by the Linq-to-SQL advocates, one that you discover usually when you attempt to deliver v2 of your application and realize you have no upgrade tool set.

Remus Rusanu
So what should I do from here? I don't have any records in the tables yet, as I'm just starting development on this application.
Maxim Zaslavsky
I'll tell you how I did it: I created a SQL text file that contained the script to deploy the v1 of my application. This file was embedded as a resource in my app, and when I start up I would run this script (transform it into a series of GO separated batches, then run each batch). Then, as my application evolved, I would add a script that upgrades the database from v1 to v1.2, then a new script for v1.3. When a I release a new version of the app, I just add a new upgrade script. When the new app is deployed, it checks the current schema version and runs all the intermediate upgrades.
Remus Rusanu
This is all described in the link to http://rusanu.com/2009/05/15/version-control-and-your-database. I never change anything in the database schema except by doing an upgrade script and increasing the schema version.
Remus Rusanu