project Organization

Question

Hi,
       We have a sql2005 DB with about 10 schemas each having 100 plus tables. Right now we are having soruce control issues with people stepping on one other's changes. I am planning to use the Partial projects to do the following :

1. Create a BaseDb(database project) with all DDL in it.
2. Create DerivedDB's(database project) for each schema each with thier own DML plus the BAseDB as a partial project include(makes it readonly)
3. Create a BuildDB database project with partial project includes from BaseDb as well as all DerviedDb(All DDL and DML will come together here)

I have following Questions:

1. Is this a good idea?
2. The partial project seems to be a static reference kind of thing , meaning any change in BaseDB would mean i might need to remove and recreate fresh
    partial project reference with the latest BaseDb(based on my current understanding). Do we have a limitation like this or is there a way to dynamically say
    that anything in the BaseDB project should be automatically referenced by the DerivedDb's.

PS: I am using the DBPRO GDR version.

Thanks in Advance.

Answer 1

I'm not sure that breaking up the project is the right thing to do if there are problems in source control, but... If you were to break it up, instead of using partial projects, I'd look to put together a compound project. It's much more dynamic. Changes are picked up automatically in deployments. How were you thinking about breaking the project up? By schema?

Answer 2

Thanks for the comments .
yes.
I was thinking of breaking up by Schemas so teams can work with only thier shcemas.
What's a compund project(i will try to find more). I only read about partial and composite projects

Answer 3

I believe Grant was referring to Composite Projects.  You could do this or would it make sense to change the permission of files/objects in your source code control?

Also, I'm interested in hearing what difficulty you are having with SCCI.  Are you running into problems merging changes (does our product not merge gracefully) or do you have issues with people not sync'ing up changes and completely overwriting other's work instead of trying to merge?  If we have issues in a team environment around SCCI, I'd like to hear your scenario and make sure we don't have bugs or bad UX here.

Thanks,

Jill

Answer 4

Yeah, sorry, that was meant to be composite projects, sorry for the typo.

Answer 5

We use the TFS for Source control and DBpro  for DB and i beleive its more of a training issue . However it does not help having one huge project file with all objects form all schemas in one gaint file. So the idea is to see if we can segregate the subprojects by Schema so each project only cares about stuff they need with one base project to supply the DDL(whihc we the DBA's produce) with all References included.
I tried with partial projects and ended up with this static reference limitation. I will check out how to break it up with composite projects.Is there a good post which explains the same. Any help is appreciated.

Answer 6

I have now tried to use COmposite projects to accomplish the following :

1. I created my BaseDb with all DDL
2. Created a DerivedDB database project for one of the schemas as a sample. 
       Added BaseDb as a Database reference to this project.
       Added all programmability for that schema in this project.
3. Built this project.
      output scirpt  only produces the DML from this project but excludes the DDL from the parent project
4. So i Created a third one called BuildCompositeDB as a database project. 
    Added a database reference to BaseDB
    Added a database reference to DerivedDB 
   Now built the project. Build fine.
   Deployed the project. The output script just produces the Db. No contents from  the BaseDB and DerivedDb are included.
 
I have follwoing questions:

1. Is this the expected behaviour in Composite projects.
2. Is there a way to include the contents of the baseDb and DerivedDb to be produced as part of BuildCompositeDB Build/Deploy in a single Deployment SQL file.


Thx in Advance.

   

Answer 7

I think you are not seeing the correct behavior because you don't have the database project properties set completely.  To make this work, you will need to set the deploy target database to the same database for all projects.  The SQL script produced will not include all of the projects as our deployment engine deploys them all separately, but if you set the option "Create a deployment script and deploy to the database" it will update the target database correctly.

Thanks,
Jill

Answer 8

Jill,
      This worked Beautifully.  First of all,thanks for the feedback
      This thing is now working using the Build/Deploy options setup in VS2k8 with a local DB defined as the target database.
       
      Now i have the following question.

      However since the Target DB is coming together in Deploy step, how do we configure/marry this up with our TFS build process. 
      The build process is expecting a .SQL Script that generates the entire DB and Install program takes over from there
      
      I tried changing the option to 'Create a deployment script' but that wont work.
      Please advice.


thx again
     
      

Answer 9

Am still facinf the same problem.Went through a bunch of links in MSDN but was not able to find anything.
Basicallly, if i use the COmposite project as detailed above, am able to only deploy to a single target DB auccessfully.
But am not able to get a Single deployment SQL script generated.
Its not useful for us if we deploy a composite DB to a physical instance.
The Old DBPRo was designed to generate a SQL scirpt and the whole process is oriented that way.
So any help in creating a single dpeloyment SQL script is appreciated.

Thanks in advance

Answer 10

I'm glad you are working in your sandbox environment now!  Deploying via TFS is not my area of expertise, but I have an email out to those in the know on our team and will update you as I hear back.  In the meantime, check out this msdn link for deploying our projects through TFS because it sounds like you are manually building a sql script and then passing it in, rather than incorporating our whole project into the build process.  http://msdn.microsoft.com/en-us/library/aa833289.aspx

Another question for you:  You said above "However it does not help having one huge project file with all objects form all schemas in one gaint file." How did you get your project setup to begin with?  All of your objects should not have been in one big file, we should have split them out by object, thus making source code control a lot easier.

Answer 11

Thanks for the feedback Jill.
What i meant was in current version of DBPro(prior to GDR) we are having a single Sql2005 Database Project.
In our database we have more than 10 Schemas. But since all of them are part of single project(1 .dbproj file) the frequency of updates across all schemas is huge leading to problems in source control. As i said earlier, its a training issue as well, but I thought this Composite project in GDR would be elgeant to split out the single large project into separate small projects at schema level. This way only devolopers who work on a particular shcema, will be editing those projects and not stepping on other projects.

To the other point, we are not building the deploy script manually, currently, the MSBuild task builds the project and the post build script copies the .sql file to where install program pick it up.
That's why its important for us to have a deploy script as the ouput versus a phsyical DB.

Thanks again

Answer 12

Unfortunately, there isn't a way to generate one large script that includes all the scripts for each composite project.  When you use composite project, we build and deploy each project individually.  You could create one script that references each of the composite project scripts using SQLCMD.  You could then copy each of the composite project scripts and the master script to the place where the install program can pick them up.  The install script would then just run the master script, which would in turn run each of the composite scripts.

---

Chuck Weininger, Dev Lead, Microsoft

Answer 13

Thanks for the update Chuck.
From you statement, it sounds like I can simply generate and invoke all the scripts separately in correct sequence.
But my understanding by going through some documentation was The Deploy engine Does SchemaDiff's and plugs in only required changes.
Essentially the question is
    Is running each of the separate scripts in correct sequence going to be equal to the target DB that's produced by the Deploy engine ?

Please advice.

Answer 14

Yes, running each of the separate scripts in the correct sequence is going to be equal to the target DB that's produced by the Deploy engine.  That is exactly what the deploy engine does.  It builds each project, then deploys each project in the proper order by walking the dependencies.

---

Chuck Weininger, Dev Lead, Microsoft