December 12, 2009 at 1:36 am
Hello
We have a big (and growing) database. We are talking about more than 1000 stored procedures. How do you manage this amount of code?
We are using "Speed SQL IDE" with VSS at the moment. But since this tool isn't supportet anymore, we are searching an alternative way.
I would like to use SSMS with projects. How do you get sure that the project is synchronal with the DB?
Could you give me any tipps?
Thanks
BlaiN
December 14, 2009 at 8:38 am
You have to use source control as the basis upon which to build the database. Don't edit procedures on the database, edit the scripts and then apply these to the database. I don't find SSMS projects to work well for this. We're using Visual Studio Team System to manage multiple projects, many of them large. One I'm working on now has well over 2000 stored procedures.
You could also use one of the third party compare tools that will work off of scripts like Apex Compare or Red Gate SQL Compare.
The key is simple, you have to build out of source control and no other way. Otherwise, the world gets way out of control.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2009 at 9:23 am
I'll second Grant's note. We had a db with over 1000 stored procedures, but at any given time we were editing only 10-20 of them.
We never allowed anyone to edit a proc from SSMS. They had to check out procs from VCS and edit them before deploying them onto the dev server. That's an administrative issue, but it becomes a habit quickly.
We could run reports on changed items when readying a deployment and branch those to a separate project.
I wrote a bit about this a few years back: http://www.sqlservercentral.com/articles/System+Development+Life+Cycle/vcspart2/524/
December 14, 2009 at 9:46 am
Thanks for your answers!
So, you don't using the project-functions neither?
You checkout the procedures directly with VSS, edit in SSMS and then run it on the Development-DB?
How do you get sure, that every sp from VSS is deployed in the Dev-DB? What if you rollback in VSS to a different Version?
I'm deploying always every procedure, view, function etc. and the table-structures from the Dev-DB. So the Dev-DB is something like the master-DB in my environment.
December 14, 2009 at 10:23 am
The dev db is never the master. VSS is the master. You start by pulling everything into VCS from the production db, then you deploy to the dev db from there. Then you only edit stuff from your local file system, which has been checked out.
We deployed once a week for 18 months with this system and never missed procs. We missed them in QA because of not properly sharing things in the VCS, but only when we had a proper deployment to QA did it get to prod.
December 14, 2009 at 10:50 am
You also need to be very careful about versioning and labelling your code so that you always know what state the source is in. Then, you only ever build the database from code. Occassionally, maybe even frequently, do a complete rebuild, only from source, so that you know you're developing against a known state.
And no, I don't use SSMS to do this. It just couldn't. Instead we're using Visual Studio Team System. I've also explored how you could use Red Gate's SQL Compare to do the same thing (I've written a chapter for a book on exactly that topic which will come out from Red Gate later next year). You could do the same thing with Apex's compare tool. You'll need to buy or build a method for doing this type of deployment.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2009 at 11:38 am
Ok, so the code in the VSS is the master.
Do you also store table-structure and default-data in VSS?
I like the idea of rebuilding everything from VSS. But how do you do this with tables?
Why aren't you using SSMS-projects with VSS?
Can you work with the Script-Editor from Visual Studio?
December 14, 2009 at 11:55 am
Tables, foreign keys, you name it and we've got it in source control.
The reason we don't use projects and SSMS is simply because there's no way to automate a build through that interface and if you make that interface the mechanism for accessing the code, automation becomes more difficult.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 14, 2009 at 12:01 pm
I've done the same as Grant and Steve, although with a different source control tool. You have to build processes around your change control that everyone follows. Document your process. Lay out the steps you want everyone to follow to check code out, edit code, apply changes to your development environment, when to check code back in, and how the changes will be migrated to your other environments. Compare tools come in very handy so that you can audit your environments to catch non-authorized changes in schema, code, and static data.
Another thing you can do is to use DDL triggers to audit schema changes. This can come in very handy in the event your find your databases out of sync. I used to work for a software vendor and we implmented a change log table to capture all schema changes. This can make a big difference when you are dealing with dozens or hundreds of production DBs that 'should' be in sync.
As far as tables and static data go, yes, use your source control to manage those as well. It gets a bit trickier to manage your tables though as you obviously don't want to do a drop/add for each schema change. So you may want to have a drop/add script as your first script in a version to be used to create a new, blank database and then each subsequent script woudl be the ALTER TABLE delta script to change the table as needed. Same goes for static data.
My goal is always to have an organized way to re-create a database at any point in time in time given our patching methods. So by using labels and versioning your scripts properly, you should be able to create a brand new database at any patch level you desire. This also comes in handy when you have to have a database at a certain version/patch level to troubleshoot bugs in application code.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply