Grant Fritchey (2/7/2008)
Nope. The one issue that we've had, and believe me the developers have howled, is that they can not simply add procedures (or drop them either). They have to let the dba team know that new procedures exist. We can pick them up from source control and add them. It actually works out, for us, because it provides us with a pretty easy mechanism to identify the procedures that need a review.
So are you saying that developers still need to go into the Database and create new procedures and then let you know they're there? Or are you saying they add them to the Project and then you "Get Latest Version" and the scripts are there?
Everything Jamie said is correct. I'll just add this.
Any changes made in source control to an object, which is how we work, that is already a part of the project can be taken into the project with a simple "Get Latest Version" or get by label or get by version number, whatever. When the developers are creating a new procedure, we have them create the proc and check it into source control, so that it is protected. They then have to ask someone with a VSTS DB license to add that file to the project. We simply do a get and add the existing file to the project. We're still working on getting them to format the code correctly (too many just take the SSMS generated proc and check it in), but it's a pretty painless process.
Currently, most of our development is on new systems, so we're recreating the database with each deployment. More and more existing systems are coming on line in DBPro and we're still working out how best to deal with incremental builds. We're trying to stay away from using the compare utility for deployments. Once we get it quantified, I will document it for an article here, but we're still in the early days at this point. So, we're trying a different approach than the one Jamie is advocating. His isn't right and ours isn't wrong, or vice versa, they're just different. Who knows, we may find that's the only way to go. But right now we've still got hope for the incremental process.
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore RooseveltThe Scary DBA
Author of: SQL Server Query Performance Tuning
and SQL Server Execution Plans
Product Evangelist for Red Gate Software