Using Source Code Control

So how do we actually use DBSourceTools and source code control ?
This section is just to provide a few tips and tricks that will help you synchronize database schemas between multiple developers.
Bear in mind that using source code control is a discipline, and will require each developer to follow the same process to check-out and check-in new versions.

Use the same directory structure on all dev machines.

Because DBSourceTools uses a simple dos batch file (Run_Create_Scripts.bat) to deploy databases, it is important for the directory structure of source files to be standard.
One of the simplest ways to accomplish this is to substitute a directory to a drive mapping.
  • Do this by running "subst s: c:\source".
  • I always create a batch file to do this, and put it into my startup scripts.
  • In this way, each developer will have an S: drive mapping to a local directory.
  • I normally use s:\Databases as my base directory for DBSourceTools.

Script your database to disk.

Select Table Data to script

  • Normally, databases have some measure of "configuration data", that is necessary in order for the application to function correctly.
  • Other tables hold instance data, which is not necessary to script. Examples of these are Log tables, and Audit tables.
  • Double-click on the Source Database, and select the Tables.. button to specify which table data to script.

SC_ScriptDataOptions.png

Create a Deployment Target.

  • Now this is the fun bit.
  • Create a Deployment Target that is the same database.
  • Note that you will need to use a NickName that is different to your Source Database NickName - such as local_AdventureWorks.

SC_DeploymentTarget.png

Write Targets

  • Right-click on the Deployment Targets in DBExplorer, and select Write Targets.
  • At this point, you should double-click on the RunCreateScripts.bat file to check that all db objects are correctly scripted:

SC_RunCreate.png

Check In

  • At this point, all of the necessary files are ready to check in to source control.
  • Check all of your files into source code control.
    • Ensure that you include the project file ( <project>.dbsp )
    • And the Database settings file (<project>.xml ) in the root directory.
  • Lets have a look at the files that are generated by DBSourceTools:

s:\Databases\AdventureWorks directory structure :
+---DataTypes
+---Dependencies (used by Dependency Tree)
+---DeploymentTargets
\---local_AdventureWorks
( the three required files for deployment : RunCreateScripts.bat, CreateDB.sql and DropDB.sql)
+---Functions
+---Procs
+---Roles
+---Schemas
+---Tables
+---Columns (used by both dataloader and Intellisense)
+---Data ( all table data)
+---Views
+---Columns (used by Intellisense)
\---XmlSchemas
  • Note that under each directory is a Urns directory (not shown above for brevity) which is used by the dependency tree, and must be included.

Synchronising changes with other developers

  • Each developer should then checkout the source tree to the same directory on their local machine.
  • Fire up DBSourceTools, and load the project ( s:\Databases\<project.dbsp>).
  • Double click on the deployment target to bring up the deployment target screen.
  • Deploy as per normal.
  • You have just re-created your database from Source files.

Discipline.

  • From here on out, it's just a matter of discipline.
  • Make sure that you keep the source files up to date, and do a regular checkout and deployment to ensure that nothing breaks.

Steps to remember before checking in:

Herewith the steps you need to remember before checking in:
  • Do a full "Load From Database" to ensure that your scripts on disk are up to date.
SC_LoadFromDatabase.png
  • Remember to Refresh your Tables, Views and Stored Procedures Options if you've added any new ones.
SC_Reload.png
  • Remember to Write Targets.
    • This will re-create your Run_Create_Scripts.bat file
  • Verify that the changes you have made are picked up by source control.
SC_SVNChanges.png

Enjoy

Last edited Jul 19, 2009 at 7:55 AM by rozentalsn, version 10

Comments

No comments yet.