March 3, 2015 at 8:31 am
My involvement with deployments is writing particular scripts for the focused modification I'm doing. My particular deployments always have to do with data modification. Each programmer does the same thing. Though they call them run-once scripts, I always try to make mine such that it checks the current state of the data and makes the appropriate changes as needed. In that way it can be run over and over and update only when it needs to.
March 3, 2015 at 8:43 am
It sounds to me like some folks are just making in-place modifications to tables and stored procedures in the development environment and then using a schema compare against production too to generate a delta script.
That's really sloppy and dangerous when there are multiple developers working on unrelated projects, features, or fixes which are not necessarily intended to for completion or deployment in tandem. You're going to do stuff like pickup someone elses half baked stored procedure modification in your deployment script or do something like push a "DROP INDEX" statement to production simply because someone was experimenting in development and didn't cleanup after themselves. Not to mention deploying logins and permissions that might be appropriate in development but were never intended for production.
Then of course there are the occasions where someone is doing a compare against production and then accidentally clicks the Execute button confuses production with QA. That's why the DBA needs to make damn sure developers don't have SYSADMIN or DBO permission in production.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 3, 2015 at 8:49 am
Eric M Russell (3/3/2015)
It sounds to me like some folks are just making in-place modifications to tables and stored procedures in the development environment and then using a schema compare against production too to generate a delta script.That's really sloppy and dangerous when there are multiple developers working on unrelated projects, features, or fixes which are not necessarily intended to for completion or deployment in tandem......
Eric - I agree it is dangerous. However, that is the situation we shall find ourselves in. The company only has budget for a dev/test platform and the current production platform. So what do you recommend as a better, more robust way of getting newly tested features (which may, as you rightly state, be only some of the 'new' code on dev/test) safely onto production?
March 3, 2015 at 9:00 am
Bob JH Cullen (3/3/2015)
Eric M Russell (3/3/2015)
It sounds to me like some folks are just making in-place modifications to tables and stored procedures in the development environment and then using a schema compare against production too to generate a delta script.That's really sloppy and dangerous when there are multiple developers working on unrelated projects, features, or fixes which are not necessarily intended to for completion or deployment in tandem......
Eric - I agree it is dangerous. However, that is the situation we shall find ourselves in. The company only has budget for a dev/test platform and the current production platform. So what do you recommend as a better, more robust way of getting newly tested features (which may, as you rightly state, be only some of the 'new' code on dev/test) safely onto production?
I'd look into SSDT - it's free to use, integrates well with source control so you can branch, merge, etc., and would allow you to generate a change script off of your mainline/master branch when releasing to production. I'd also look into the idea of doing local development using SQL Server Developer edition. It's cheap or included in the price of your MSDN subscription and would let developers work locally before pushing changes to your shared Dev environment.
I'd definitely be wary of doing a full compare of Dev to Prod and hope to only include the appropriate changes if you don't keep a good log of those changes. I'd also be careful because a rename operation could easily cause a LOT of issues. Compare tools don't know what to do with those and you could easily lose data when trying to synchronize the schema.
March 3, 2015 at 9:05 am
Bob JH Cullen (3/3/2015)
Eric M Russell (3/3/2015)
It sounds to me like some folks are just making in-place modifications to tables and stored procedures in the development environment and then using a schema compare against production too to generate a delta script.That's really sloppy and dangerous when there are multiple developers working on unrelated projects, features, or fixes which are not necessarily intended to for completion or deployment in tandem......
Eric - I agree it is dangerous. However, that is the situation we shall find ourselves in. The company only has budget for a dev/test platform and the current production platform. So what do you recommend as a better, more robust way of getting newly tested features (which may, as you rightly state, be only some of the 'new' code on dev/test) safely onto production?
For each database, a have a folder in source control. In my case this is Team Foundation Server, but any source control tool could just as well apply. For each object there is a separate script. The process of development, testing, and deployment is very basically as follows:
1. check-out the object script(s) from source control
2. modify
3. deploy to development
4. unit test in development
5. check-in to source control
6. deploy to QA
7. test in QA
8. Attach script(s) to change order ticket
9. Production control DBA deployes script(s) to production
There is a lot of crap in development that will never see the light of day in production. That's why at each step in the process, I keep track of what scripts are related to each project or task, and I have a reference to each script within the work order ticket.
So, this is a bare minimum fuctional overview of the process, but notice that it doesn't involve schema comparisons and delta scripts. Even in the smallest shops where the DBA assumes the role of database developer, QA, and production control, the above process can be easily followed.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 3, 2015 at 9:14 am
At least for me, schema comparison is more of a diagnostic tool used in scenarios where I'm troubleshooting an issue in production and need to know what total differences there are between production and development, or perhaps discover a modification that was accidentally left out of the deployment. But even in these scenarios, the solution is to get the required script from source control, and then attach that to a new production change order for deployment. I'm still not pushing delta scripts.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 4, 2015 at 10:45 am
I've had a lot of success with a state based/compare approach, it worked out to be a lot easier and more accurate than asking devs to coordinate/submit scripts. I'm a fan of smaller changes more often too, which makes the state approach easier. I've done a lot of work where we did table swaps, partition swaps, even database swaps so that we could reduce the outage/have a rollback plan.
March 4, 2015 at 11:04 am
For those of you who work in shops where developers make in-place changes to database objects and deployments are based on comprehensive delta scripts, how do you implement versioned source control?
You're just archiving delta scripts?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 4, 2015 at 11:08 am
Some places I have worked at required differing version upgrade scripts when they had to support multiple schema versions i.e. upgrade to v3 from v1 script AND from v2 script. This was for products as opposed to projects.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 4, 2015 at 11:17 am
Eric M Russell (3/4/2015)
For those of you who work in shops where developers make in-place changes to database objects and deployments are based on comprehensive delta scripts, how do you implement versioned source control?You're just archiving delta scripts?
That's what we did for a while - put them in folders appropriate to the current sprint, ordered as much as possible. It worked okay until we needed to only push _some_ changes that happened during a sprint. Backing things out that way was painful. It would have been a bit better if we had been following more of a "release often" approach, but the release had built up over months. I became a big fan of the whole DB/SQL Project approach after that release. I like being able to say "make the database look like this" and let the tool do it's work. Keep in mind that we don't tend to do huge refactor operations. That would likely change my opinions a bit, but not too much.
I also implemented DDL triggers to catch things the devs might have forgotten to mention, along with a daily report on those changes e-mailed to me.
I think this is the sort of mentality behind DBDeploy, but it's not a tool I've used. I looked at it briefly and realized that the approach wasn't one I really wanted to repeat.
March 4, 2015 at 11:30 am
Peter Schott (3/4/2015)
Eric M Russell (3/4/2015)
For those of you who work in shops where developers make in-place changes to database objects and deployments are based on comprehensive delta scripts, how do you implement versioned source control?You're just archiving delta scripts?
That's what we did for a while - put them in folders appropriate to the current sprint, ordered as much as possible. It worked okay until we needed to only push _some_ changes that happened during a sprint. Backing things out that way was painful. It would have been a bit better if we had been following more of a "release often" approach, but the release had built up over months. I became a big fan of the whole DB/SQL Project approach after that release. I like being able to say "make the database look like this" and let the tool do it's work. Keep in mind that we don't tend to do huge refactor operations. That would likely change my opinions a bit, but not too much.
I also implemented DDL triggers to catch things the devs might have forgotten to mention, along with a daily report on those changes e-mailed to me.
I think this is the sort of mentality behind DBDeploy, but it's not a tool I've used. I looked at it briefly and realized that the approach wasn't one I really wanted to repeat.
Where I am, there are multiple database developers working on different projects and sprints, but never multiple developers changing the same object. So, I can't deploy a database level delta script in either development or production without overwriting someone elses changes or inadvertently picking up changes not intended for my release.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 4, 2015 at 11:40 am
You should be able to handle this with appropriate branching of the scripts, especially if nobody is changing the same files/objects. It may not work as well for your shared dev environment, but should work fine for production. Separate the work by story/feature branch (if a bunch of stories), group the work done that way. You'll have some work to do to pick out all of the stories to release to production, but if they're separate you should be able to handle that. Maybe consider something like:
Dev
-Sprint1
--Story1
--Story2
When you want to release, move/copy the folders:
Release
-Sprint1
--Story2
After release, move (again, maybe with a date)
Released
-Sprint1
--Story2
It's not pretty, but you could see what you're about to release and it could be semi-ordered. If you put your stories in dependency order, that would be ideal. If not, you'll have some more tweaking to do when building the scripts. It's not a pretty process, but we worked with it for a while. It does take some discipline, especially not to just tweak an earlier script with a change because that's easier.
For DB projects, we were able to isolate changes into their own branches, then merge those branches into a mainline/master build before releasing to production. That kept the changes that weren't going to production separate while maintaining the other changes. As long as you keep any dependencies in mind, you should be fine.
March 4, 2015 at 1:28 pm
These issues are no different from those for non-database code.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
March 4, 2015 at 1:43 pm
Gary Varga (3/4/2015)
These issues are no different from those for non-database code.
I think I'd disagree with that to an extent. If I'm modifying table structures or doing refactoring, there are a lot of other things going on behind the scenes. I know I've been hit hard before with what should have been a "rename" operation, but looked like a "drop/add" situation. There's also no data to maintain with most non-database code. You drop a new file, you're good to go. If you change something in the database, your new stored proc may not work or may not work as expected because of data changes.
I'd also say that the order matters a bit more w/ database ops because I might need to refactor in a certain order where each step preceding is needed for the end result to work. That's a bit different from being able to just drop the end files where they should go. I may have done some data manipulation along the way from step 1 to step 10 so can't just drop the changed proc/table in step 10.
The issues may be similar, but I've found that DB changes tend to be a bit different because the state of the database matters a lot more than the version of the executable and isn't quite as easy to change overall.
March 4, 2015 at 1:56 pm
There are a lot of scenarios where SSMS table designer or a schema compare tool will want to rename / create / select / insert, when a simple ALTER TABLE statement would suffice. But even in those cases were it is needed, it's still just a single object script that can be checked and versioned into source control.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 15 posts - 16 through 30 (of 36 total)
You must be logged in to reply to this topic. Login to reply