How do you develop your Databases
-
There's the old way, and the new way. In both cases, databases exist on a per-machine basis. For our current version, we have a large number of SQL scripts run by a special tool. It also inserts sample data. Maintaining it can be... fun, although we've hit no real problems yet. For the new version, the app itself is capable of generating or synchronising the entire schema from interface definitions in an assembly, and sample data can be added automagically too.
I do it through a set of scripts executed by a script which is configured through config files. Schema changes made according to the change log, code is redeployed at every update completely. All the changes/code are introduced in form of script registered in one of the config files. All the code is under source control - subversion on my machine and/or TFS for the projects. Most of the time I don't even know were my databases are deployed - developers and testers are able to install and update their local and shared copies by themselves. I described it in an article here: http://www.simple-talk.com/sql/database-administration/deploying-database-developments/[^] Please mind that while the article decribes approach that I'm using allover the place for a few years, the script attached to the article was just rewritten and was pretty buggy :). I have an updated version of it, should you be interested in it - email me. But the question is may be too broad - I use Erwin for the model, generate schema of it, put it to the scripts. For the changes usually use red-gate sql compare and modify it as needed. For the procs I use Management studio + sql toolbelt + CLCL utility (for the whole wack of templates for any occasions). Security and other config files are xml files, so I edit it with something. HTH
-
Statutory "sorry if you think this is a programming question - its not meant to be" disclaimer. I'm interested in how other people handle development in a multi-developer team environment. In our situation we have four (or so) developers working on a .Net system against an SQL server back end. Each of us has a copy of the DB on our dev. machines. To say that the DB schema changes frequently would be an understatement - so they have added the SQL script to recreate all of the tables in the DB to the .Net solution in Subversion. So if I want to make a DB change, I get the latest version and lock it, run the SQL, make my changes, run a utility to recreate the script, then check it back in to subversion. This seems to me to be cumbersome, and somewhat prone to stuffing up (more than a few changes have been lost when someone runs the script which then undoes their changes) So how are other people coping with this out there (assuming a similar sort of environment)? (in a previous life I worked on a system where there was no Dev DB - so the live DB was updated by developers - so at least I know our system isn't the worst out there!)
___________________________________________ .\\axxx (That's an 'M')
Most solutions I see or hear about are based around ignoring change control, giving developers the power to rapidly change the database, or build the db on build, or generate the DAL on build, yadda, yadda, yadda. The best solution is to have one person, or process to control changes to the DB. Each developer may have his or her own database but to get the changes to the actual dev database it has to go through the documented change control process, get approval, etc. This allows the maximum number of persons to work on a system, effectively. (For staff of less than 2 I wouldn't bother)
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego. -
Statutory "sorry if you think this is a programming question - its not meant to be" disclaimer. I'm interested in how other people handle development in a multi-developer team environment. In our situation we have four (or so) developers working on a .Net system against an SQL server back end. Each of us has a copy of the DB on our dev. machines. To say that the DB schema changes frequently would be an understatement - so they have added the SQL script to recreate all of the tables in the DB to the .Net solution in Subversion. So if I want to make a DB change, I get the latest version and lock it, run the SQL, make my changes, run a utility to recreate the script, then check it back in to subversion. This seems to me to be cumbersome, and somewhat prone to stuffing up (more than a few changes have been lost when someone runs the script which then undoes their changes) So how are other people coping with this out there (assuming a similar sort of environment)? (in a previous life I worked on a system where there was no Dev DB - so the live DB was updated by developers - so at least I know our system isn't the worst out there!)
___________________________________________ .\\axxx (That's an 'M')
RedGate SQLCompare. It shows you the differences between two databases (eg. master, local).
-
Statutory "sorry if you think this is a programming question - its not meant to be" disclaimer. I'm interested in how other people handle development in a multi-developer team environment. In our situation we have four (or so) developers working on a .Net system against an SQL server back end. Each of us has a copy of the DB on our dev. machines. To say that the DB schema changes frequently would be an understatement - so they have added the SQL script to recreate all of the tables in the DB to the .Net solution in Subversion. So if I want to make a DB change, I get the latest version and lock it, run the SQL, make my changes, run a utility to recreate the script, then check it back in to subversion. This seems to me to be cumbersome, and somewhat prone to stuffing up (more than a few changes have been lost when someone runs the script which then undoes their changes) So how are other people coping with this out there (assuming a similar sort of environment)? (in a previous life I worked on a system where there was no Dev DB - so the live DB was updated by developers - so at least I know our system isn't the worst out there!)
___________________________________________ .\\axxx (That's an 'M')
We use mirgatordotnet by (find it at http://code.google.com/p/migratordotnet/[^]) . Basically it allows you to version your database schema by writing c# code to alter the database schema. You can also insert (demo) data trough the same mechanism. You make a separate project in your solution that generates an assembly. You then feed the migrator tool (or make a nAnt task) this DLL and it updates the schema. You can also rollback the schema to previous versions. Each time you update your source, the miration is run on your local SQL DB. You this allways have the latest schema. When our released software runs the first time after installation, it asks for a DB server and its credentials and automatically generates the DB! Upon upgrading, the schema is upgraded too! What do you guys think of our scenario?
Dennis Decoene ML Solutions -The Micro-ISV bringing you next generation business software - http://www.mlsolutions.be
-
Statutory "sorry if you think this is a programming question - its not meant to be" disclaimer. I'm interested in how other people handle development in a multi-developer team environment. In our situation we have four (or so) developers working on a .Net system against an SQL server back end. Each of us has a copy of the DB on our dev. machines. To say that the DB schema changes frequently would be an understatement - so they have added the SQL script to recreate all of the tables in the DB to the .Net solution in Subversion. So if I want to make a DB change, I get the latest version and lock it, run the SQL, make my changes, run a utility to recreate the script, then check it back in to subversion. This seems to me to be cumbersome, and somewhat prone to stuffing up (more than a few changes have been lost when someone runs the script which then undoes their changes) So how are other people coping with this out there (assuming a similar sort of environment)? (in a previous life I worked on a system where there was no Dev DB - so the live DB was updated by developers - so at least I know our system isn't the worst out there!)
___________________________________________ .\\axxx (That's an 'M')
Not to "rock the boat" and I know changes do happen. But, the DB schema should be mapped out well in advance of even a single line of code being written to interface it. Also, a "single" instance that is accessed by all is always the best way to go. Also, the application should have a tier design, so only one person needs to update the DAL. Whoever updates this layer after being notified of the DB changes, would then document the changes to the DAL so the other developers know what they need to change on their end. There is rarely a need for all developers to even see the DB itself, they should only be concerned with the data coming from the DAL to their business logic layer. e.g. If someone separates 1 large table into 3 smaller tables for efficiency, I could care less. I only care about what's being returned, not how it's aggregated before hand. My 2 Cents
-
Statutory "sorry if you think this is a programming question - its not meant to be" disclaimer. I'm interested in how other people handle development in a multi-developer team environment. In our situation we have four (or so) developers working on a .Net system against an SQL server back end. Each of us has a copy of the DB on our dev. machines. To say that the DB schema changes frequently would be an understatement - so they have added the SQL script to recreate all of the tables in the DB to the .Net solution in Subversion. So if I want to make a DB change, I get the latest version and lock it, run the SQL, make my changes, run a utility to recreate the script, then check it back in to subversion. This seems to me to be cumbersome, and somewhat prone to stuffing up (more than a few changes have been lost when someone runs the script which then undoes their changes) So how are other people coping with this out there (assuming a similar sort of environment)? (in a previous life I worked on a system where there was no Dev DB - so the live DB was updated by developers - so at least I know our system isn't the worst out there!)
___________________________________________ .\\axxx (That's an 'M')
-
Simon Capewell wrote:
he core structure has already been developed, so most of the changes being made are subtle tweak
I think that's the problem with ours - it didn't occur to me until I read your post that the reason it is so bad where I am now is that because there has been no design (and I mean NONE) the changes aren't minor - they can involve massive wholescale changes, splitting a single table into many, or merging many onto one, etc.
___________________________________________ .\\axxx (That's an 'M')
We're in a similar situation - major structural changes to try and impose some sanity on a non-designed db (but with thousands of bespoke reports running against it, it still has look as bad as it ever did :sigh: ) What we've done is create a tool to read in numbered scripts, which are kept under source control, run the statements in them, then bin them. When they're run against a production db the script number is recorded in a table, so a check can be made to stop the same script being run more than once (and a check can be made in a client app for what schema its db is really at). The contents of each script are limited to a single transaction. This system's managed to get a complete nightmare under control. The only thing the devs have to do occasionally is say "I'm taking scripts 345 to 349 - any objections?". I guess we could formalise that bit, but it's not worth it in a small team.
-
We're in a similar situation - major structural changes to try and impose some sanity on a non-designed db (but with thousands of bespoke reports running against it, it still has look as bad as it ever did :sigh: ) What we've done is create a tool to read in numbered scripts, which are kept under source control, run the statements in them, then bin them. When they're run against a production db the script number is recorded in a table, so a check can be made to stop the same script being run more than once (and a check can be made in a client app for what schema its db is really at). The contents of each script are limited to a single transaction. This system's managed to get a complete nightmare under control. The only thing the devs have to do occasionally is say "I'm taking scripts 345 to 349 - any objections?". I guess we could formalise that bit, but it's not worth it in a small team.
DaxaDan wrote:
We're in a similar situation - major structural changes to try and impose some sanity on a non-designed db (but with thousands of bespoke reports running against it, it still has look as bad as it ever did Sigh )
Refactor everything inside and then create a view that looks like the WTFbase for legacy apps while requiring all new development to use the good schema?
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
-
Statutory "sorry if you think this is a programming question - its not meant to be" disclaimer. I'm interested in how other people handle development in a multi-developer team environment. In our situation we have four (or so) developers working on a .Net system against an SQL server back end. Each of us has a copy of the DB on our dev. machines. To say that the DB schema changes frequently would be an understatement - so they have added the SQL script to recreate all of the tables in the DB to the .Net solution in Subversion. So if I want to make a DB change, I get the latest version and lock it, run the SQL, make my changes, run a utility to recreate the script, then check it back in to subversion. This seems to me to be cumbersome, and somewhat prone to stuffing up (more than a few changes have been lost when someone runs the script which then undoes their changes) So how are other people coping with this out there (assuming a similar sort of environment)? (in a previous life I worked on a system where there was no Dev DB - so the live DB was updated by developers - so at least I know our system isn't the worst out there!)
___________________________________________ .\\axxx (That's an 'M')
Dev's worked locally on dev dbs, and submitted finished requirements to the DBA which folds it into the master db. Does your database schema change that much after release? You aren't growing columns on all of your tables are you?
This statement is false
-
DaxaDan wrote:
We're in a similar situation - major structural changes to try and impose some sanity on a non-designed db (but with thousands of bespoke reports running against it, it still has look as bad as it ever did Sigh )
Refactor everything inside and then create a view that looks like the WTFbase for legacy apps while requiring all new development to use the good schema?
Today's lesson is brought to you by the word "niggardly". Remember kids, don't attribute to racism what can be explained by Scandinavian language roots. -- Robert Royall
-
This happened to me once on a production database. Your heart feels really weird sitting in your stomach as you watch every row in production get updated.
I actually won praise for deleting the master table from a production database once...for having instituted the backup strategy the week before :laugh: might've been different if it hadn't been minutes after the backup had finished :wtf:
-
Most solutions I see or hear about are based around ignoring change control, giving developers the power to rapidly change the database, or build the db on build, or generate the DAL on build, yadda, yadda, yadda. The best solution is to have one person, or process to control changes to the DB. Each developer may have his or her own database but to get the changes to the actual dev database it has to go through the documented change control process, get approval, etc. This allows the maximum number of persons to work on a system, effectively. (For staff of less than 2 I wouldn't bother)
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.Also, the one consideration I don't see in the responses (but which your comment allows as a consideration) is this: how do you migrate the data? Not every database change means the production of new fields with NULL or specified defaults. I've been in multiple situations where new columns must be added with specific values based on existing content. Only a well-managed change control process can handle this reliably.
-
Dev's worked locally on dev dbs, and submitted finished requirements to the DBA which folds it into the master db. Does your database schema change that much after release? You aren't growing columns on all of your tables are you?
This statement is false
Synaptrik wrote:
Does your database schema change that much after release?
Oh, yes! Because none of it was designed (the developers just creeated tables based upon some preliminary Gui design) there are constant changes - adding, removing and changing tables and columns. And because we're not using stored procs, but building SQL in code, the nightmare continues into the application as a whole!
Synaptrik wrote:
You aren't growing columns on all of your tables are you?
Yes we are! Normalisation is a swear word - after all, why not store the patient's name on several tables? (THIS IS NOT MY OPINION, BUT WHAT HAPPENS WHErE I WORK) We also have had situations where there have been , say, five columns called 'Amount1, amount2..' becasue the gui showed five in a list - but (obviously) this whould be a separate table containing amounts. But when the deveooper is told they need more than five - he wants to just add more columns. madness and hell
___________________________________________ .\\axxx (That's an 'M')
-
Statutory "sorry if you think this is a programming question - its not meant to be" disclaimer. I'm interested in how other people handle development in a multi-developer team environment. In our situation we have four (or so) developers working on a .Net system against an SQL server back end. Each of us has a copy of the DB on our dev. machines. To say that the DB schema changes frequently would be an understatement - so they have added the SQL script to recreate all of the tables in the DB to the .Net solution in Subversion. So if I want to make a DB change, I get the latest version and lock it, run the SQL, make my changes, run a utility to recreate the script, then check it back in to subversion. This seems to me to be cumbersome, and somewhat prone to stuffing up (more than a few changes have been lost when someone runs the script which then undoes their changes) So how are other people coping with this out there (assuming a similar sort of environment)? (in a previous life I worked on a system where there was no Dev DB - so the live DB was updated by developers - so at least I know our system isn't the worst out there!)
___________________________________________ .\\axxx (That's an 'M')
We have development, QA and production environments (at a minimum). Developers are banned from using their own local version of the databases - they either use the central development database, or nothing. Updates are made directly to development database as we work and scripted out with a date and release stamp. That way each developer is working on the most current version. When development gets uploaded to QA, we run the collective database scripts for everything that's changed since we last updated QA. I do find though, that this is tedious and you have to keep a hawk eye on fellow developers. I've been planning to write my own DB comparison tool which will script out the differences between to databases... Recently discovered Red Gate's SQL toolbelt though (awesome product) which does an excellent job of scripting the differences (and a host of other nifty features), but it' price is a bit prohibitive so we won't be using it past the trial... :^) :^)