Keeping Production DB Up To Date
-
I am working on an app where I now need to keep the production DB up to date with the changes we're making in development. In addition, there is another team that needs to work with this DB. Can anyone recommend tools for keeping Production and Development DB's in sync. Requirements include 1) Structure changes. 2) Data cannot be lost. Thanks
If it's not broken, fix it until it is
-
I am working on an app where I now need to keep the production DB up to date with the changes we're making in development. In addition, there is another team that needs to work with this DB. Can anyone recommend tools for keeping Production and Development DB's in sync. Requirements include 1) Structure changes. 2) Data cannot be lost. Thanks
If it's not broken, fix it until it is
Kevin - might also help if you mention a) 'which' db eg SQL Server, Oracle, {other} b) how fast/how many updates occur in a unit of time c) what sort of distance is between prod and dev db's - eg, are they separated into 2 different data centres, 1000 km apart for example d) how fast do you expect the data to be avail on the dev db - is it 'real time transactional' for example, or 'batch with a 24 hour window' (because the answers to those questions could really shape someones response) Let me give an example if you said a) = Oracle, b) = < 1000 transactions an hour, c) = 1000km, and you had a reliable pipe between Data Centres, then I might respond 'oracle log shipping', which is fairly low-tech, cheap, but meets those requirements [edit] included a 'd' [/edit] 'g'
-
Kevin - might also help if you mention a) 'which' db eg SQL Server, Oracle, {other} b) how fast/how many updates occur in a unit of time c) what sort of distance is between prod and dev db's - eg, are they separated into 2 different data centres, 1000 km apart for example d) how fast do you expect the data to be avail on the dev db - is it 'real time transactional' for example, or 'batch with a 24 hour window' (because the answers to those questions could really shape someones response) Let me give an example if you said a) = Oracle, b) = < 1000 transactions an hour, c) = 1000km, and you had a reliable pipe between Data Centres, then I might respond 'oracle log shipping', which is fairly low-tech, cheap, but meets those requirements [edit] included a 'd' [/edit] 'g'
Fair enough.... a) SQL 2008 R2. b) Updates probably will be weekly, but could be more often. c) Production DB is in Winnipeg Canada. I am in development in Southern California. d) Data will be in use immediatley in Canada.
If it's not broken, fix it until it is
-
I am working on an app where I now need to keep the production DB up to date with the changes we're making in development. In addition, there is another team that needs to work with this DB. Can anyone recommend tools for keeping Production and Development DB's in sync. Requirements include 1) Structure changes. 2) Data cannot be lost. Thanks
If it's not broken, fix it until it is
We use Red-Gate SQL compare for deployment of scripts (schema changes). If we have to move any data into production it is a 3 week process with multiple sign offs and blood on the table. You could set up and automated deployment process that would: take a backup of the production script the changes (Red-Gate) copy the scripts to the production server run the scripts on production take a backup of the production FTP it to the dev server restore the production backup to the dev server. However I would shoot the person who suggested you automate this, I want someone hand holding the processes at every step, oversight and someones balls at risk if production goes down.
Never underestimate the power of human stupidity RAH
-
We use Red-Gate SQL compare for deployment of scripts (schema changes). If we have to move any data into production it is a 3 week process with multiple sign offs and blood on the table. You could set up and automated deployment process that would: take a backup of the production script the changes (Red-Gate) copy the scripts to the production server run the scripts on production take a backup of the production FTP it to the dev server restore the production backup to the dev server. However I would shoot the person who suggested you automate this, I want someone hand holding the processes at every step, oversight and someones balls at risk if production goes down.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
However I would shoot the person who suggested you automate this, I want someone hand holding the processes at every step
really ? thats a bit drastic isnt it .. if you cant afford a decent replication scheme, do you want to be a slave to manual methods ? different strokes for different folks I guess We use disk level mirroring between prod and d/r - an initial mirror is replicated to our d/r site over dark fibre - that has its issues as well - if the prod mirror is corrupted, then, so is the d/r mirror (thats a basic level explanation, its a bit more complicated) .. my point is, not everyone has the $$ for big buck solutions, or the need, but I would automate a 'simpler' process as much as possible (caveat) with the appropriate checks and balances and control-points, and, everything has different 'angles'/pitfalls ..so know the weak points of a solution 'g'
-
Mycroft Holmes wrote:
However I would shoot the person who suggested you automate this, I want someone hand holding the processes at every step
really ? thats a bit drastic isnt it .. if you cant afford a decent replication scheme, do you want to be a slave to manual methods ? different strokes for different folks I guess We use disk level mirroring between prod and d/r - an initial mirror is replicated to our d/r site over dark fibre - that has its issues as well - if the prod mirror is corrupted, then, so is the d/r mirror (thats a basic level explanation, its a bit more complicated) .. my point is, not everyone has the $$ for big buck solutions, or the need, but I would automate a 'simpler' process as much as possible (caveat) with the appropriate checks and balances and control-points, and, everything has different 'angles'/pitfalls ..so know the weak points of a solution 'g'
Actually I was talking about the automated deployment TO production, that is something I would not want to do, the process yes but not the initiation of that process. Replication and backup distribution of the production database is something I would highly approve of if I could get it, policy, the fucking god POLICY will not allow production data in dev. Ah well you work in finance you have to abide by their rules :sigh: .
Never underestimate the power of human stupidity RAH
-
Actually I was talking about the automated deployment TO production, that is something I would not want to do, the process yes but not the initiation of that process. Replication and backup distribution of the production database is something I would highly approve of if I could get it, policy, the fucking god POLICY will not allow production data in dev. Ah well you work in finance you have to abide by their rules :sigh: .
Never underestimate the power of human stupidity RAH
sorry - my fault production data in dev - yeah, a tricky one - we have a process that 'washes the data'/ anonymizes it 'g'
-
sorry - my fault production data in dev - yeah, a tricky one - we have a process that 'washes the data'/ anonymizes it 'g'
Garth J Lancaster wrote:
we have a process that 'washes the data'/ anonymizes it
And I still need a dispensation from the god Policy to get the data!
Never underestimate the power of human stupidity RAH
-
I am working on an app where I now need to keep the production DB up to date with the changes we're making in development. In addition, there is another team that needs to work with this DB. Can anyone recommend tools for keeping Production and Development DB's in sync. Requirements include 1) Structure changes. 2) Data cannot be lost. Thanks
If it's not broken, fix it until it is
You are going to be doing structure and data changes to a production database on a weekly basis? And I can only suppose that there is other software that uses it which will also need to be updated. Certainly not something I would want to see. Better make sure that you have complete automated unit and system testing. And have a lot of time to dedicate to implementing the automated deployment and providing for a rollback for ALL systems. I doubt you will find a tool that does it all. And every database change will need to be evaluated for rollback impact.