July 13, 2009 at 8:51 am
Hi All, we have a number of production pools some with over 500 databases in SQL Server 2005. We currently have a Perl application that deploys our schema changes to production when we have a new software release. With the last release it took over 25 hours to apply the schema changes to over 500 DBs in one pool.
Our development manager has asked that I come up with a better way before our next software release. I've been looking at a couple of third party tools like Redgate's SQL Compare. If anyone is doing this kind of thing on the scale of what we need to do could you let me know how you handled it or how you are handling it. We keep all our SQL scripts in CVS for version control and use a SQL Definition file for tracking new developer modifications to the current schema which are then applied with a new release.
Thanks in advance!
July 13, 2009 at 10:06 am
Most of these products will probably deploy the same way you did, they'll send a script through to SQL Server.
There isn't a shortcut here. Not that I'm pushing you away from SQL Compare, after all, they employ me, but I'd first look at what was slow?
If you've changed a column in a table that's a) being used and b) is large, it's going to take time to apply the change.
It might be that you can better structure the changes, and get them to apply faster. The other thing is that if you are applying with a script to servers sequentially, then you are going to be limited in time just from that standpoint. Instead, I'd look for ways to deploy a script in parallel. Red Gate has multi-script, which might help here.
July 13, 2009 at 11:11 am
Thanks for your reply Steve. I tend to agree with you about not re-inventing the wheel here. Our Perl app is actually quite sophisticated in how it handles deploying the schema changes. I think getting it to run in parallel will probably be our best approach. Our latest release also has a heavy number of schema changes so the performance this time around may be a one off issue. I'd still like to hear how others handle it, particularly for those with 100 + databases on their server(s).
July 13, 2009 at 11:26 am
The START command in DOS, and I forget what in *Nix will spawn processes.
One thing I'd do is break my prod pool into 3 groups.
1. One is a "pilot" small group for making sure if things go awry your entire pool isn't getting rolled back.
2. Main pool for the primary deployment.
3. Final pool for slow testers.
We used to use SSMS to deploy patches and it works in parallel to send things out. Despite testing we always had issues with some groups, so a few pools worked well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply