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


Database Development Made Easy?


Database Development Made Easy?

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224284 Visits: 19634
Comments posted to this topic are about the item Database Development Made Easy?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Sean Redmond
Sean Redmond
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1655 Visits: 837
Obligatory rant from me:
Database development *is* easy. Developing databases well is not. You have to know what you're doing and this takes time. You have to apply yourself to learning about it. You have to care that you are doing a good job, rather than simply care about getting the job done.
I give, as an example, the last database that I received from our developers: all of the string fields were nvarchar(max). All of the numeric fields were int, even the primary key for the languages table (which will hold 4 entries). I am, at least, thankful that it had foreign keys. The database won't be big, I was told, it doesn't matter.
Dave Poole
Dave Poole
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24236 Visits: 3491
For destructive deployments we had an explicit DBA schema so the relevant data could backed up and be rolled back. The deployment/rollback usually involved some form of sp_rename for the larger tables.

You do need an agreed RTO (recovery time objective) and RPO (recovery point objective). For example, an agreement that a rollback will lose all data captured since the deployment. Alternatively for a zero data loss rollback the rollback will be accomplished in 2days, recovery will prioritize recent data in order to provide full functionality for operational concerns at the earliest possible point.

There should be space in the DB for such tables simply because you need space for reindexing. As rollbacks are rare you can put the DBA schema on its own filegroup with its own file mount. That gives you options with regards to cost of the solution. Having a separate schema for data rollback also keeps the main schemas clean and allows a clear retention policy to be defined for the DBA schema.

One thing I think would be useful would be the ability to BCP out data into Parquet files.

LinkedIn Profile
www.simple-talk.com
jasona.work
jasona.work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16207 Visits: 13106
I'd suspect some of the things software developers see as "problems" when developing a database (such as the example in the editorial about dropping a column, then wondering why the data was gone when it was re-created,) is because software developers expect the database to behave just like the application does.

You made changes to a piece of code and it doesn't behave as you expected when checked into the full application? Well change it back and everything is back the way it was. Whereas working with a database is more akin to working with paper files (I know, bad analogy incoming) of patient records. If you have someone go through with an eraser and erase the SSN from every patients file, that data is *GONE* with no way to get it back (beyond the headache and hassle of calling every patient.) Similar to that, if you drop that information from a database table that stores it, it's gone and you're not getting it back by just re-creating the column.

The software dev expects a sort of "undo" function, like what just about any editing application has, where if you do something boneheaded, you can CTRL+Z your way back out.

With a database, your only CTRL+Z is either do your work, if possible, in a transaction without a commit at the end, or take a backup before making any changes. Worse, from a developers point of view, neither of those are always an option...
How long do you leave your changes uncommitted (especially considering no one else will see those changes until you DO commit them,) how long will it take to run a backup (oh, you're dealing with a multi-terabyte database that you only take a full backup on Friday starting at 1800 and it runs all weekend until Monday at 0400?) during which time the changes are getting pushed out.
Sure, you could make a copy of the table or tables being modified, but how long will that take? With a big enough table, you might be waiting a few hours. Or there might not be enough room on the server to have a duplicate of the table, then what?

I've sort of pictured any business process that relies on a database to be an inverted pyramid. The database is at the point of the pyramid on the bottom, because any changes there have a cascading effect up to the other tiers of the pyramid (application servers, end users, etc.)
Steve Jones
Steve Jones
SSC Guru
SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)SSC Guru (224K reputation)

Group: Administrators
Points: 224284 Visits: 19634
jasona.work - Wednesday, August 30, 2017 6:31 AM
I'd suspect some of the things software developers see as "problems" when developing a database (such as the example in the editorial about dropping a column, then wondering why the data was gone when it was re-created,) is because software developers expect the database to behave just like the application does.



This is a never ending source of frustration for many developers. The fundamentally don't get this.


Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
podmate
podmate
SSC Eights!
SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)SSC Eights! (838 reputation)

Group: General Forum Members
Points: 838 Visits: 1023
Sean Redmond - Wednesday, August 30, 2017 2:34 AM
Obligatory rant from me:
Database development *is* easy. Developing databases well is not. You have to know what you're doing and this takes time. You have to apply yourself to learning about it. You have to care that you are doing a good job, rather than simply care about getting the job done.
I give, as an example, the last database that I received from our developers: all of the string fields were nvarchar(max). All of the numeric fields were int, even the primary key for the languages table (which will hold 4 entries). I am, at least, thankful that it had foreign keys. The database won't be big, I was told, it doesn't matter.
<RANT>
This is my life right now.
I am looking over the database design (including indexing, keys, stored procedures, triggers, functions, views and general table design) for some projects with 'issues' and the one constant that I see is that the developer who built this house of cards took zero time to plan the database structure and ALWAYS went for what was fastest and easiest to build.

Most of the tables have no foreign key constraints.
Indexing?
Data integrity is not even a concept to most of the dev's.
Second normal form is violated with abandon.
Stored procedures are reused without thought as to if the procedure is optimized for the use. Example: there is one generic 'search' stored procedure that allows many different items to search on (SSN, last name, application ID, date, etc), but is written in such a way that for every 'empty' search parameter, a full table scan is preformed.
Data validation. We will add that 'later', which we all know means never.
I could go on for hours.

Most of the dev's hate hearing from me because all of my suggestions require them to 'do more work' and 'take more time'.

------

I read through the linked MS blog about SSDT/VS and was appalled with the insane number of steps and complexity to do something that should be so simple.
Guess I am getting old, but IMHO the over complicated, GUI driven insanity is strangling the dev's that I work with.
Their job keeps getting harder. Not because of the language, but because of the 'frameworks', 'tools' and 'IDE's' that they are required to use.

I am glad that I read this article as it helped me understand some of the ignorance that I am seeing from dev's.
We have a recurring problem where a dev will try and promote their code to another environment and they just assume that whatever database changes (structural or data) will automagically be promoted. They look at me like I have three heads when I tell them that no, they have to specify exactly what needs to be promoted (as is clearly spelled out during their orientation and in other documentation they have access to). Many of the dev's can't understand why VS doesn't do everything for them (including thinking).
</RANT>

Eric M Russell
Eric M Russell
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43413 Visits: 12036
Any tool for auto generating DDL deployment scripts should have an option that explicitly confirms 'Allow data loss' yes / no. If no, then additional assertions should be coded that raise and error and aborts before dropping a table that contains data at runtime. There could also be a 'Require backup' yes / no option that adds runtime code to query backupset history to confirm that's been done recently.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
jasona.work
jasona.work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16207 Visits: 13106
Most of the dev's hate hearing from me because all of my suggestions require them to 'do more work' and 'take more time'.


This is part of the reason why I left my last position. Database was *horrible* internally, queries were returning SELECT * from tables with dozens to near hundreds of columns and only using 7-8 of the fields on the front end, all sorts of things.

Every time I suggested getting together with someone to take a look with an eye towards improving the database, I was told "we're too busy right now," "we don't have time," or "we'll fix it later."

So, when I got a better offer elsewhere, I jumped ship (there were other, bigger reasons to leave for me, as well.)

The worst of it is in that kind of a situation, if you stick around and they consider you the "SME" for databases, when the application runs horribly slow, or returns bad data, it's not the fault of the crappy database they put together, or bad queries they don't entirely understand how they work, no, "it's the SQL Server and why aren't you fixing it, the application is just fine the way it is and works fine with my sample data of 10 rows, so it's got to be SQL, fix it and we don't have time to make these changes to the database and the code." So now you're holding the bag-o-crap(TM) for a problem not of your making and the only tool in your toolbox that will have any impact is throwing more hardware at it...

Chris Harshman
Chris Harshman
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16235 Visits: 5172
Where I'm working at, the developers know if they want to make development easy they just have to say "database" or "SQL" or "Harshman" enough times, and I'll show up inquiring what they are working on. Wink
Robert-378556
Robert-378556
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2927 Visits: 1015
The deployment worked, but there wasn't data in the column that had been dropped, and added back. Why not, asked the developer?


IMHO, that kind of person can't be called a developer nor a programmer.
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