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.
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.
- 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:
- 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 :
+---Dependencies (used by Dependency Tree)
( the three required files for deployment : RunCreate
Scripts.bat, CreateDB.sql and DropDB.sql)
+---Columns (used by both dataloader and Intellisense)
+---Data ( all table data)
+---Columns (used by Intellisense)
- 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.
- 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.
- Remember to Refresh your Tables, Views and Stored Procedures Options if you've added any new ones.
- 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.