locked
How to set up development and production database and server projects?

    Question

  • I'm just curious as to what the recommended setup is for a full production and development environment. There doesn't seem to be any type of overview.

    We were considering setting up two solutions, one which has a prod and dev project for each database, and a solution which just has one dev project for each database. The problem I'm running into is how to deal with server projects. What happens when each developer has their own server? Should dev database projects refer to production server projects?

    The tools seems so close, yet, I feel like I still have no idea what I'm doing.
    Friday, January 09, 2009 9:16 PM

Answers

  • Since you need different versions of the same thing, you might consider using a source control system, like TFS, to solve the problem. 

    What tool are you using for version control?  If you are using TFS (and most other version control systems would support this also), you could use branches to create different versions of your projects.  You could have a production and development branch containing the same project.  A way to start would be to create the development branch, check your database project into that branch, then create the production branch as a copy of the development branch.  You can then make changes in either branch without affecting the other branch and merge changes between them whenever you want.  This isn't functionality that is specific to database projects - you can do it with any type of project. 

    Here is a link to information about branching in TFS:  http://msdn.microsoft.com/en-us/library/ms181423.aspx

    I hope that helps.


    Chuck Weininger, Developer, Microsoft
    Monday, January 26, 2009 8:22 PM

All replies

  • Here is a link to our msdn library with some helpful information about how to get started:

    http://msdn.microsoft.com/en-us/library/dd193245.aspx

    There shouldn't be any reason why you need separate projects for production and development.  We recommend that you use one project for each database.  Developers make changes to the projects, deploy and test in their local development environments and when they are satisfied with their changes, they would check them into your source control server.  Then a system admin, or someone filling that role, can deploy the databases to the production environment using the same projects.

    Chuck Weininger, Developer, Microsoft
    Saturday, January 10, 2009 12:22 AM
  • The reason we wanted a separate project for production, is that sometimes someone has to make a change on a production server for support reasons, i.e. a quick fix during the day that hasn't gone through development. We want to make sure that every day we have a current "image" of the live database and have it all in source control.

    How do you deal with multiple users "developing" a database? One "developer" would be our production database, so any changes to the database in production would get merged into development branches.

    In the past, any time that I've attempted to use MS tools in a way that they were not intended to, I end up regretting it, so I'd like to have an idea as to how the database tools were meant to be used in a production environment.

    Tuesday, January 13, 2009 6:41 PM
  • I looked at this document, but it just tells you what buttons to click what to type. I'd like an overview that explains concepts. I can't read that document and then figure out what I'm supposed to do on my particular project. Why would you change file groups?
    Tuesday, January 13, 2009 7:25 PM
  • Since you need different versions of the same thing, you might consider using a source control system, like TFS, to solve the problem. 

    What tool are you using for version control?  If you are using TFS (and most other version control systems would support this also), you could use branches to create different versions of your projects.  You could have a production and development branch containing the same project.  A way to start would be to create the development branch, check your database project into that branch, then create the production branch as a copy of the development branch.  You can then make changes in either branch without affecting the other branch and merge changes between them whenever you want.  This isn't functionality that is specific to database projects - you can do it with any type of project. 

    Here is a link to information about branching in TFS:  http://msdn.microsoft.com/en-us/library/ms181423.aspx

    I hope that helps.


    Chuck Weininger, Developer, Microsoft
    Monday, January 26, 2009 8:22 PM
  • It's not that simple with database projects. Also, how do you deal with the fact that database projects now need server projects?
    We created a database solution with all of our databases and server projects.

    I'm developing a particular piece of software, so I have a solution with all of my code projects and a branch of our database project.
    The problem is that it won't compile because it needs definitions for the Logins. Now I don't want to include the Server project, because it won't be the same as my development server.

    In addition, it's not clear that a simple merge will work when attempting a rollout because of data that may need to be created for rollouts. How do you keep track of data which needs to be added when rolling out?
    Thursday, June 04, 2009 3:41 PM
  • bpeikes,


    I believe you questions regarding branching and merging we addressed on another thread. You may want to review some of the blog postings I have regarding best practices at: http://blogs.msdn.com/bahill/archive/2009/02/02/why-vsts-db-best-practices.aspx


    In regards to your data concerns the following posts should give you an idea about how to handle data and data motion as a part of a release:

    http://blogs.msdn.com/bahill/archive/2009/03/30/managing-data-motion-during-your-deployments-part-1.aspx
    http://blogs.msdn.com/bahill/archive/2009/04/01/maintaining-and-synchronizing-your-reference-data.aspx

    Thanks,
    Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question.
    Tuesday, June 16, 2009 6:22 PM
    Moderator
  • I read through some of your blog postings, but they don't take into consider how you deal with multiple developers working on different projects against a single database. I understand how VSDB works in terms of only using the DDL used to create objects (i.e. no alter statements needed), but how do you keep track of what set of changes to the database need to be released for a particular project?
    Example:
    Project1 needs x,y,z changed, but the database changes should only occur when Project1 gets rolled out, no sooner.
    Project2 needs a,b,c changed, but the database changes should only occur when Project2 gets rolled out, no sooner.

    Project1 developers have the DBADMIN make the changes to the Database project. When they are ready to rollout Project1, those changes need to be applied to production.

    Project2 developers have the DBADMIN make the changes to the Database project. When they are ready to rollout Project2, those changes need to be applied to production.

    Developers for Project2 are ready to rollout, but Project1 developers are not. How do you keep track of the changes that Project2 needs?

    Tuesday, June 16, 2009 7:55 PM
  • I believe the issue is around principles of code isolation and configuration management. 

    Given the scenario you provide I would recommend the following. I am assuming project 1 and project 2 have distinct development teams. Project 1 and project 2 are actually features, not VS Projects.

    You start with a MAIN branch of code.  You have 2 featues on the product backlog that are scheduled for release/development.  2 branches are created from MAIN.  This results in a branch structure that looks like this (simplified). 

    >DEV
    >>Feature 1 Branch
    >>Feature 2 Branch
    >MAIN

    Project team 1 works on their work items/stories and implements their feature.  When ready as passing some criteria their branch is merged into MAIN.  Project team 2 follows the same process. Once both features are merged into MAIN and passes integration testing the code is promoted to release candidate branch. This looks like:

    >DEV
    >>Feature 1 Branch
    >>Feature 2 Branch
    >MAIN
    >PROD
    >>Release 1.1

    The resulting code that needs to be deployed (including database) is in the Release 1.1 branch.  For the database portion that is probably and incremental deployment a diff needs to be performed against the database in production.  This happens as a part of deployment process either through automation or generated interactivley by the DBA/Developer using schema compare or the deployment through IDE, MSBUILD, VSDBCMD.

    Now if Project 2 has dependencies on Project 1 work items, it may make sense to branch them together to reduce the overhead of merging changes between the branches. This is usually the case for smaller shops.

    In the situation where project 1 is determined to be "No Go" or not accpeted by the customer or fails code promotion criteria, Feature Branch 1 would not be merged into Main.  It could stay in its branch until it is ready or gets deleted. If the "No Go" comes after code promotion to MAIN then the changset of the merge to MAIN for Feature 1 is rolledback/reverted.  

    You may want to look at the TFS Walkthroughs that describe some of these actions:
    http://msdn.microsoft.com/en-us/library/ms181279.aspx

    You can find Team System Concepts here:

    http://msdn.microsoft.com/en-us/library/ms181233.aspx


    Barclay Hill Program Manager VSTS: DB Team (DataDude, DBPro, Database Edition) Please mark the responses as answer if it resolves your question.
    Tuesday, June 16, 2009 9:45 PM
    Moderator