August 29, 2017 at 8:19 pm
Comments posted to this topic are about the item Database Development Made Easy?
August 30, 2017 at 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.
August 30, 2017 at 3:26 am
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.
August 30, 2017 at 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.
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.)
August 30, 2017 at 8:51 am
jasona.work - Wednesday, August 30, 2017 6:31 AMI'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.
August 30, 2017 at 9:28 am
Sean Redmond - Wednesday, August 30, 2017 2:34 AMObligatory 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>
August 30, 2017 at 9:57 am
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
August 30, 2017 at 10:10 am
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...
August 30, 2017 at 10:43 am
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. 😉
August 30, 2017 at 12:01 pm
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.
August 30, 2017 at 12:11 pm
jasona.work - Wednesday, August 30, 2017 6:31 AMI'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.
Whooo!!! let's make everything an EAV then we'll never have to touch the database again and can add fields whenever we want!!!!
August 30, 2017 at 12:33 pm
ZZartin - Wednesday, August 30, 2017 12:11 PMjasona.work - Wednesday, August 30, 2017 6:31 AMI'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.Whooo!!! let's make everything an EAV then we'll never have to touch the database again and can add fields whenever we want!!!!
I have had more than one senior developer (somebody with > 10 years development experience) say something like this to me.
August 30, 2017 at 12:36 pm
jasona.work - Wednesday, August 30, 2017 10:10 AM
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 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...
When someone signs up for a DBA position they probably think that most of their effort is going to be learning and applying those learnings to their DBs and servers.
The reality is that a large part of the role is building a reputation and nurturing relationships that predispose 5 developers to listen and give fair consideration to 1 DBA. It also involves managing upwards. The DBA has to have an honest conversation with their boss. "Look you hired me to do 'X' which I am certainly capable of doing, however I am placed in a situation where I am prevented from bringing my skills to bear. In order for me to be effective I need support with 'Y'".
Its a difficult conversation to have and it may not go the way you want. However the worse that can happen is that it accelerates a decision you'd end up making anyway.
August 30, 2017 at 12:49 pm
jasona.work - Wednesday, August 30, 2017 10:10 AMMost 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...
I agree, i think this happens at most of workplace. I too hear that, we don't have time, we'll fix later
August 30, 2017 at 6:27 pm
Anchor Modeling addresses some of these issue with a non-destructive development approach based on agile principals. It's worth a look just to wrap your head around the approach and theory:
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply