January 24, 2015 at 11:31 am
Comments posted to this topic are about the item Deploy the Database First
January 24, 2015 at 3:14 pm
I agree. It's like Developers and DBAs. I don't know why people treat them separately. They should be on the same team with the same goals. Code should follow suite. Every release that requires change to the front-end and the backend should be done together so that if something does go haywire, they can both be rolled back at the same time. There are exceptions, of course, but generally not.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 26, 2015 at 1:26 am
I have successfully being deploying database changes like this for years, and in most cases it is quite simple.
For the most part we use stored procedures and we apply a version to the stored procedure names eg. GetAll____v1, GetAll____v1, Add____v1, Add____v2 etc.
This means when I change a database structure I can add stored procedures to work with the next generation of the code, and if required, make a modification to the existing version to allow the current version of code to continue working.
...and of course, all new table columns require either a default or to allow NULL. In most cases I simply allow NULL and have the developers handle this in their code - it makes deploying to the live database servers much simpler !
January 26, 2015 at 5:30 am
The application should specify the columns in the query and be immune to new columns in the database. Different applications can share the same database and each use their own set of columns.
January 26, 2015 at 6:15 am
it can work when we have control over the application's database code. but for languages that depend on ORM and a lot of abstraction this could be nightmare.
from my experience the database changes are a lot more unpredictable, taking from minutes to hours to apply - and the reasons for this are not very clear to upper management. so they can think that applying the database code 1 day earlier could save a lot of downtime, because to change the application they would only need a few minutes.
maybe this is the reason. maybe not. managers are mysterious.
January 26, 2015 at 6:23 am
There are a lot of practises that have been available for years and this is one of them. Over the past few years I've gone on a bit of a journey that has changed my mind set and my approach to a whole raft of things. Having been a DBA (past tense) I was focussed on threats to the data and databases, weaknesses of deployment approaches and in general had an overly cautious mind set.
Moving up through an architectural role I've learnt to focus more on opportunities that new processes, people and technology bring and also how these can become strengths.
The mantra if it hurts do it more often tells us not to give up when a process doesn't work. Keep digging until you understand why precisely it doesn't work and find a way to reach your end goal that can work. It is the change in mind set above all else that makes things possible.
Continuous integration, TDD/BDD and moving from a project based delivery to a product based support model have made deployments far more reliable. Adoption of tests specifically to detect potential problems in the database has also helped. In short, test disciplines and practises have allowed applications to be tested far harder and more rigorously than before.
Developers and DBAs (if such a separation is still relevant) should be working together to ensure that components are loosely coupled. When an application reads data and expects 3 fields then it shouldn't break if it gets presented with more than 3 or if those 3 are in a different order. If an application writes but doesn't yet know about a 4th field then perhaps the field can have a sensible default even if that default is especially to indicate that a values are not yet specified. My thinking on this is that switching a nullable field to a non null field is of greater impact than altering a default constraint when the application is finally set up to cater for the new field.
January 26, 2015 at 7:18 am
That would be tough to change to if the software has already been written to not account for this. But maybe over time as things get fixed or enhanced we could move towards this.
January 26, 2015 at 8:04 am
Another reason why SELECT * from <any object> should never be used in application execution code.
We have two apps created by a Developer that swears this is fine.
Of course he gets paid hourly for updates every time the vendor makes an update to the Database his app connects to.
VB6 didn't even recommend using the pattern "rs1 = SELECT * from table" and then looping through each rs.row and then each field or column in each row to execute code and inspect every value in the Record Set object. I see this code do it for as 30 column RS with two million ROWS and ask why the database is slow. :w00t:
January 26, 2015 at 8:18 am
As much as you can do it it seems like a good idea. Assuming you have CI/users of your application in order for db first to work you need to try hard to do non-breaking changes. Ie: you don't break version X in preparation for client version Y. So you are free to roll out the new front end on some servers while leaving others around to handle production load.
January 26, 2015 at 8:22 am
Or applications that sit on top of someone else's database: say health care systems, CRM etc. Often it becomes the job of the front end guys to quickly re-wire their application when the users want a new version of the main system and the schema changes all over the place. At my work my team are both the db and the webservice guys. Another team handles UI (and we support a few different products that hit our database). Becomes a bit of a pain negotiating between teams when you want to refactor things but better than becoming a javascript monkey 😉
January 26, 2015 at 8:23 am
IMHO, I think this is a wise approach. We all know that the application code and the database are different. App code is generally easy to replace while database changes are - 'sensitive - there is real data, and while restoring from a backup is possible, it should not be part of the plan if the application code needs to be rolled back. That would be a mess.
Forcing the two to be updated separately just forces this reality. If an application is up 24/7, you almost have to manage the two separately.
The more you are prepared, the less you need it.
January 26, 2015 at 9:00 am
So, deploying a new procedure, table, or column days or weeks before the associated application change is deployed? Yes, that happens all the time in my universe where the database development and application development teams are seperate. The database comes before the application, both for the initial deployment and change request deployments.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 26, 2015 at 9:10 am
We, too, have been practicing this for years, along with the strict caveat, as a previous poster mentioned, that no one ever use a SELECT * in anything, view, stored procedure, or other!
That being said, you can get bitten in the tush if you're not careful, as I red-facedly have to admit happened to me this very past weekend! An SSIS job puked because a child package had been mistakenly been deployed to a Production server that referenced a new column in a table. Shame on me!
In the long run, though, it has made sense to us to deploy structural changes first, followed by application changes later. It's usually easier to implement the former, while the latter can take much more development time, and it's useful to see whether the structural changes will cause any unforeseen problems. That said, the developer had better have thoroughly tested that in DEV first!
Larry
Optum Technology
United Health Group
January 26, 2015 at 9:25 am
mike.gallamore (1/26/2015)
Or applications that sit on top of someone else's database: say health care systems, CRM etc. Often it becomes the job of the front end guys to quickly re-wire their application when the users want a new version of the main system and the schema changes all over the place. At my work my team are both the db and the webservice guys. Another team handles UI (and we support a few different products that hit our database). Becomes a bit of a pain negotiating between teams when you want to refactor things but better than becoming a javascript monkey 😉
Anything is better than becoming a javascript monkey.
Being banished to an undocumented and uncharted area of abstracted machine code that executes in a 4 bit DLL running between the 5th and 6th levels of Hell. Forced to write code that works the same for every user on every device connected everywhere for anytime and all time.
Dante did not a clue that there would be such a Hell so extremely cruel....
yeah... I don't like javascript.
January 26, 2015 at 9:46 am
I think it's possible in many cases to upgrade a database over time by carefully planning your schema changes and accounting for those changes in your front end architecture.
If the front-end architecture exclusively consumes web services (rather than performing database I/O calls), the web services often may abstract out changes in the underlying database schema. The planning then centres on ensuring correct alignment between the database schema and related web services.
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply