Friday, October 16, 2009

Using Visual Studio Database Edition (VSDB)

Database Edition is designed so that each team member works with their own isolated database. Some folks will also be making changes to the database, and others may not be.

If you are planning to make changes to the database, here are the steps I recommend:  

  1. Check with other team members to see if you might be changing some of the same objects or not. Coordinate so that you do not overwrite each other's changes.  
  2. Get latest version of at least the database project files from source control.  
  3. Update your database from the latest database project files. There are a couple ways to handle this. Let's assume you do NOT have any changes or data in your database that you need to keep. Simply do a DEPLOY of the database project to get the latest version. (If you do have changes you need to keep, then skip to Step 5 and be VERY careful about what you sync up.) If you are not sure, you can run a schema compare to see the differences.
  4. Make your changes.  
  5. Sync your changes back to the project.  

    1. Run a Schema Compare

      1. Source: your isolated DEV database
      2. Target: Project
      3. Options: see my previous email and sure the correct Object Types are being ignored
    2. Carefully review the Schema Compare output

      1. Filter to Non-Skip Objects
      2. Scroll through the list of Objects
      3. For any objects that you did not intend to change, delete, or add, change the Update Action to Skip

        1. Be sure that you are not deleting stored procs, etc., that someone else recently added – e.g. if you have done another Get Latest Version on the database project since you deployed your isolated database
    3. Write Updates to the target – this will update all non-skip items you reviewed above
  6. Check In your changes to the Project in  
  7. Let the team know that you checked in database changes and what they might affect. The team may need to update other code and get your changes before they make DB changes. 

Problems are likely to occur if folks get careless about reviewing schema compare output and what will get updated. For example, let's take the following example:

  1. Person A gets latest and deploys
  2. Person B gets latest and deploys
  3. Person A creates new proc MyProcA, syncs back to the project, and checks in
  4. Person B creates new proc MyProcB
  5. Person B does schema compare to project (maybe a get latest version happened in the project, too – for example, by adding a new object the solution file will need to be checked out)

    1. NOTE: Schema compare will want to ADD MyProcB and DELETE MyProcA (because MyProcA does not exist in Person B's isolated database)
  6. Person B ignores output and does Write Updates and then Checks In … now MyProcA has been removed from the project

So that's more or less why the steps outlined above are in general good practice and will hopefully keep you out of trouble.

Also note that Scripts are not touched by Schema Compare (e.g. post-deployment scripts which seed data).

For more reading… (this is from the DB edition documentation, available at):

Or download the whole doc at (see at the end in the Downloads section):

No comments:

Post a Comment