SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Three Rules for Database Development


Three Rules for Database Development

Author
Message
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29039 Visits: 19002
Miles Neale (9/17/2012)
Some wonder why I install SQL Server on my local box and develop against that version first. It is obvious, never develop the initial product in a shared environment is critical. I also like to have complete administrative control of the data server for the early life of the project, that way I can do what I need when I need it, and it is controlled only on my box. SO I guess I go one step further, never develop on a shared data server and only on one that you have complete control over. If possible.

The others a fine.

M.


I can understand if it's a single use, small app. That said- with large databases with a team of 20 developing against it, having 20 copies of the DB each being modified "under full control" of 20 separate devs each with their own opinions is chaos. Without knowing early on that the SAME component needs to satisfy two separate enhancements, all you will end up with is a deployment tug of war.

Instead - we've embraced CI and solid check-out/check-in processes. Check out before you modify, check in once you're done and if need be, synchronize and/or normalizae your changes with any source control conflicts that may arise. At least you can ensure that the relevant devs are talking to each other (along with the instructions to come find an architect if some major conflict is found).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Miles Neale
Miles Neale
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4216 Visits: 1695
Matt, you are absolutely correct. With many hands on one database there is chaos. But that is not the case here. I have a luxury or two that most do not have.

Not all gray hairs are Dinosaurs!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)SSC Guru (211K reputation)

Group: General Forum Members
Points: 211895 Visits: 41977
Steve Jones - SSC Editor (9/17/2012)
Jeff Moden (9/16/2012)


I've found that such "committee decisions" can be horribly wrong especially when people who might not know as much about databases outnumber those that do (think lynch mob, gang rape, and Lemming mentality combined). Everyone working the same way has some very nasty ramifications if they're all doing the same wrong thing. ;-)

Rule 1 for database development should be, "Find someone that actually knows how to design databases correctly and then follow their rules."


This isn't about technical design decisions. It's more about process. We agree with naming conventions, we agree that there is a QA process. We agree developers will turn over unit tests or edge cases. We agree that new tables get a review by the group, or by a person.

We decide to work in a way that complements, rather than each person deciding how they like to work.


Ah... got it. Thanks for the feedback, Steve.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
mark.wojciechowicz@gmail.com
mark.wojciechowicz@gmail.com
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 611
There are a variety of reasons not to develop on a shared database. As others have already mentioned, when you are developing locally you can avoid all sorts of collisions between developers. If you are using MS database projects to do your development, you have a complete copy of your schema under source control and can check out objects and isolate your code changes. It also means that your code does not need to compile when you save an instance of it, which is helpful if you need to shelve your current work. In addition you get static code review and much better ability to handle refactoring your database.

The key factor is unit testing. To conduct efficient testing, you should be able to scrap the data, autogenerate more, rename and recreate objects. Database projects allow you to incorporate database unit tests within the same solution so that you can have a tightly integrated base of code.

Database projects allow for an easy way to version up or version down any schema. Your team can choose the best way to go about this, but I think more importantly, the implementation of schema should be a separate thing from the definition of that schema. It allows for a much more Agile development environment in which are can be constantly at the ready to implement your code changes.
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20243 Visits: 7660
I may just be old school, but I can't STAND not having an integrated development environment amongst all the developers. I guess the idea is that your database needs a 'trunk', just like everyone else's front end stuff.

How do you test for parallelism on your local box? How do you test the real effects? How can you see what your IO waits will really look like and not just make the excuse "Well it's running off my local IDE, the server will be better"? I work against half tera to terabye level systems in some cases, am I supposed to constantly restore to the 'base state' on my local system? Hell no.

I'm also not a particular fan of TFS, SourceSafe, or the rest in the database world. I'm VERY frickin' biased against this, particularly because of the idea of Continuous Integration and Databases do not meld well. Oh, sure, store yourself a version control so you can see what was and what is, no problem. It's also rather handy for lockouts so multiple developers don't step on the same object simultaneously. We used to use Excel spreadsheets for that.

However, in my opinion, TFS/SS/etc do not belong in the actual construction of the change scripts though. I don't want to 'have to inherit' off the TFS system for databases, particularly schema and view level changes where you're talking possible massive data manipulations that might be impossible to back out.

The shared dev environment contains all the currently working and used code from all developers, allowing for their modifications to directly affect yours during the workflow and development cycle. Allowing dependent changes to affect the optimization testing of items downstream.

I've never worked with a QA department that had a chance in hell of testing for that level of optimization or integration of multiple components. They're mostly concerned with end results, not how its achieved and they certainly don't have the expertise to figure this stuff out. It's our jobs as developers to do that and without that integrated dev environment we don't have a shot in heck of pulling it off.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
mark.wojciechowicz@gmail.com
mark.wojciechowicz@gmail.com
SSC Veteran
SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)SSC Veteran (201 reputation)

Group: General Forum Members
Points: 201 Visits: 611
I may just be old school, but I can't STAND not having an integrated development environment amongst all the developers.


Kraig - I completely agree. In an ideal scenario, you test and develop locally and integrate to a central server. That server can have real data for performance and real world testing.
Kristian Ask
Kristian Ask
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 251
Steve Jones - SSC Editor (9/17/2012)
Jeff Moden (9/16/2012)


I've found that such "committee decisions" can be horribly wrong especially when people who might not know as much about databases outnumber those that do (think lynch mob, gang rape, and Lemming mentality combined). Everyone working the same way has some very nasty ramifications if they're all doing the same wrong thing. ;-)

Rule 1 for database development should be, "Find someone that actually knows how to design databases correctly and then follow their rules."


This isn't about technical design decisions. It's more about process. We agree with naming conventions, we agree that there is a QA process. We agree developers will turn over unit tests or edge cases. We agree that new tables get a review by the group, or by a person.

We decide to work in a way that complements, rather than each person deciding how they like to work.


The problem I've seen emerging lately is that the database is no longer part of the equation by using Entity Framework or other similar framework. In those cases it is even harder to optimally design the database. Last issue I found was triple the number of calls where most of them should have been cached on application load.

Currently my db dev team of three have a pretty large shared db and that works just fine as long as they do what I [we] decide and as professional that I require. Nothing more, nothing less. The devs are also aware of what is expected of them...
marlon.seton
marlon.seton
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 319
Phil Factor (9/17/2012)
The idea of saying that one should never do shared database development is ridiculous, especially nowadays when you can have the best of both worlds and use database source control whilst doing either shared or single-user database-development, or both. The reason for doing your development work on your own server is merely to allow you to work on different versions of the database 'at once', which means that you can re-create a version from source control in your own private server and fix a bug or add a feature, without affecting your colleagues, or damaging other versions. I've always advocated doing both types of database development, depending on the task. The private databases are fine for prototyping, trying things out and doing sandbox development, whilst the shared model gives us extremely rapid integration, since we have schemas that prevent developers from banging heads too badly whilst sharing, source control allow us to roll back from a development mistake, virtualization to allow us to work on as many versions as we can tolerate.
I don't understand why there should be objection to the mixed-model of database development. I understand the build and integration problems that come from database application developments that chose to ignore the best-practice of having defined interfaces between application and database, but to try to anathemise or prescribe the perfectly reasonable practice of shared database development as a consequence, is like insisting that everyone should use crutches merely because you've shot yourself in the foot.


What he said.
marlon.seton
marlon.seton
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1139 Visits: 319
djackson 22568 (9/17/2012)
Testing sometimes finds bugs. Occasionally. Once in a while. Maybe only one or two...


If testing doesn't find a bug the chances are the testing hasn't been thorough enough.
Kristian Ask
Kristian Ask
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 251
Not to look down on test like unit-test but even if the tests are well-written they hardly find more than 40% of the bugs anyway. There's a fine line of when tests are just to overly complicated. You'll need extensive user testing to get above 80%.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search