Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Three Rules for Database Development Expand / Collapse
Author
Message
Posted Monday, September 17, 2012 10:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 7,120, Visits: 15,016
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?
Post #1360315
Posted Monday, September 17, 2012 10:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:18 PM
Points: 2,266, Visits: 1,320
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!
Post #1360336
Posted Monday, September 17, 2012 1:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:56 PM
Points: 36,775, Visits: 31,230
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1360432
Posted Friday, September 21, 2012 4:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:34 PM
Points: 17, Visits: 572
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.
Post #1363027
Posted Friday, September 21, 2012 4:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:51 PM
Points: 6,158, Visits: 7,222
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
Post #1363033
Posted Friday, September 21, 2012 5:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:34 PM
Points: 17, Visits: 572
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.
Post #1363038
Posted Wednesday, September 26, 2012 2:06 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 12:21 PM
Points: 53, 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...
Post #1364494
Posted Thursday, September 27, 2012 2:19 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825, 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.
Post #1365088
Posted Thursday, September 27, 2012 2:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 8:03 AM
Points: 825, 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.
Post #1365089
Posted Thursday, September 27, 2012 7:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 12:21 PM
Points: 53, 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%.
Post #1365228
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse