Keeping the production db up to date with the dev
-
Does any one have a script or an solution for how i can keep my data definition on my production db in sync with my develop edtion?
Our programmers make changes only to the dev version db, how can i automate those changes to the production version of the database without loosing the data in the production database ?
-
Right now you'd have to write a lot of code to generate the script I think. Red-Gate has a new product out (which I'll be reviewing in the next week or two) that is supposed to do exactly what you're asking for. Right now their SQL Compare product does what you want, but requires a user to do it via their UI. New product lets you automate using COM. I'm looking forward to testing it, this could be REAL time saver. Not sure if it's up on their site yet, if not, watch our newsletter for the review and URL.
Andy
-
Thanks !!
I tested this program, and it looks kinda neat !
-
In our shop we put the onus of this task on the development team. The changes they make must have a 'conversion' script with it that will take a production database and make the change, preserving the data, etc. This works well for smaller database changes, but can get complex as the size of the database grows.
We have a test environment where we load up the production database and then transform it to the new release. This is what we call 'Development Integration Testing', and always starts with a copy of the current production environment and gets upgraded to the new target release.
This has always been the ugly side of databases. Hopefully the tool mentioned above will help out with this task, but my gut tells me there will need to be some manual tweaking of the sql.
Other tools that help are datamodeling tools such as Sybases PoerDesigner or Oracle's Datamodeler tool. Both will take two differing schemas and create scripts to update the schema, maintaining and verifying FKs, preserving data, etc.
Good luck. Let us know if you find a break through tools.
Cheers.
-
Ripple,
I used to do that, but I found most developers not providing "good"scripts that worked. If you can get mgmt to enforce this, then it is great, but in my case, mgmt said I should be verifying them and fixing issues anyway, so there was no incentive for devleopers to do a good job.
Steve Jones
-
That was a big problem at my last job. Dev team would write the update or load scripts I'd have do dang near re-write them anyway.
Wes
-
I don't let developers get that close to the database (depends on what your developers are I guess, but database structure should be considered important).
If the structure changes then the system test build should include running the database change scripts - if they don't work then the system test gets delayed and someone gets embarrassed (at least I do).
If you don't have a controlled system test then you will need to have some means of testing whether the update to the live system works and a means of backing it out.
Cursors never.
DTS - only when needed and never to control. -
Nigel - I agree. Structure is far too important and far reaching to not be in the mix of the design of these changes. I guess that can vary in the larger environments however, it would make me extremely nervous to allow changes at random by anyone. Even with the most trusted developers it makes me skittish.
Am I a control freak
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
-
-
>> Am I a control freak
Yes.
For a dba that's a good thing.
Cursors never.
DTS - only when needed and never to control. -
I have to agree with Nigel on this one: it is a good thing for a DBA to be a control freak. It's not unusual for performance issues to be put on the DBA unless he/she can prove conclusively that it is NOT the DB Server.
Michael Hotek has written an article about this very problem on his site and how to go about doing this:
http://www.mssqlserver.com/articles/practice1.asp
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
I've got a series on this very subject that should be starting soon. Hopefully it will give you some ideas about keeping your production and development in synch.
Steve Jones
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply