Tuesday - Schema Compare in VSTS2008

TLA313 - End-to-End Database Development Using Microsoft Visual Studio Team System 2008 Database Edition Brian Randell talked about how to manage change with SQL Server. That was a useful session! I've been struggling with this problem for years with Oracle and SQL Server: you have to manually write DDL scripts when doing database upgrades. Those scripts make visualising changes difficult and reversing changes impossible unless you write undo scripts. DBPro (VSTS 2008 Database edition) generates those DDL scripts automatically: you edit your schema offline within your solution (all under source control) then when you need to deploy the schema, DBPro compares the target DB model to the offline model and generates the scripts automatically. So no need to store the DDL scripts in source control any more (ALTER statements to add a column for instance). What you store in source control is the declarative model (i.e. the CREATE statements for the successive versions of the table). If a DBA makes a change in production, you can have DBPro compare the schemas and highlight the differences. You can then select an individual difference and bring it back into the offline model to keep it in sync. Sounds brilliant. By the way, this is available in VSTS2008 but works against SQL Server 2005. Brian did the whole demo against 2005 to make a point that there is nothing specific to SQL Server 2008. Resources:

• Gert Drapers blog (Data Dude): http://blogs.msdn.com/gertd/
• Will take ages to download the trial version of DBPro from here
• The list of features in VSTS2008
• Power Tools for DBPro