I’ve worked in a bunch of environments where we rolled schema changes to production throughout the week, and worked hard to limit downtime. Our biggest challenges were:
The code released wouldn’t have the intended effect, and multiple hotfixes would sometimes need to be written quickly if the issue was urgent.
Problems would happen with replication in production – either replication would be delayed because a lot of modifications were occurring, or a change in an “upstream” system would cause a huge amount of data processing to happen in a “downstream” database
I’m in a ZDT environment. There’s a pretty good amount of overhead work that goes into getting there; there are times in which a 20 minute window would have saved hours of development time, so it’s important to keep that trade-off in mind.
SSDT helps us to refactor code by automating the actions of:
- Expanding wildcards
- Fully qualifying object names
- Moving objects to a different schema
- Renaming objects
Aside from this list SSDT also, of course, helps us to refactor code manually with its general editing facilities.
If you aren’t familiar with what SSDT can do, check out this article.
Monica Rathbun needed to rename a table in a lot of scripts. Here’s how she did it:
The quick answer that I came up with is to script out all of the stored procedures into a single query window. This can be done easily through the GUI. Once that is complete, I can easily do a “Find & Replace” on the table name and we’re done!
Ideally, those stored procedures are in source control already and you do your find-and-replace there, but sometimes you can’t control that.