Re: How do manage running queries across multiple DB servers?

Thanks for the book suggestion Ivan, I'll be sure to read this on Safari.
While I'm thinking of the challenges, I was picturing this like a
configuration management issue, but really there's much more depth to the
problem. Another issue is how to roll back, as you mentioned there's no way
to really make this easy to undo a mistake.

All of the comments and suggestions are well regarded and I'll be sure to
offer up these ideas to our team. Hoping that in the end, we move to
something more stable to work with.

Robert



On Mon, Nov 5, 2018 at 12:51 AM Ivan Sergio Borgonovo <[email protected]>
wrote:

>
>
> On 11/5/18 5:29 AM, Robert Freiberger wrote:
>
> > ## Ivan Sergio Borgonovo
>
> > e.g. if your DB is made of "components", eg. on some instances you've
> > some tables and in some other you don't you could version every
> > component so that it would be easier to run everything you need in the
> > same transaction. Or you could write "fast path" script to move you from
> > version 3 to version 5 when following all the steps would be too slow
> etc...
> >
> >
> >  >>> That's a great idea to keep versions of the database in the tables.
> > Which is something we're not currently doing and there has been some
> > confusion if the queries were executed on the database or not. I'm going
> > to ask if there is already a current way of checking database versions,
> > or something we could easily add.
> >
> > The other issue is we have sharding, but only some of the environments
> > are fully sharded while others are sharded for certain databases, but
> > others are tied together. So it's slightly confusing to think of these
> > copies of each other since they are actaully each slightly unique (aside
> > from the few that are master/secondary configuration).
> > 0
> > Thank you again for the replies, I'm reading up on database management.
>
> This morning as soon as I got the notice of your reply I remembered you
> are using MySQL. I'm not a MySQL frequent flyer but I remember MySQL DDL
> (the part of SQL that is used to change the schema and objects in the
> DB) may not be transactionally safe.
>
> That means you won't be able to rollback schema changes in case of
> transaction failure so you'd have to write your schema "rollback" script
> manually.
>
> It seems that each DB is pretty unique so I'm not really sure you could
> automate anything unless upstream development get changed a bit upstream.
>
> Still not only you could save DB version but also some other
> meta-information.
>
> As said you could have a table storing "component versions" but also
> enabled features.
>
> Of course meta-informations are somehow redundant. They are a summary of
> the state of the DB and may end up to be hard to manage especially if
> you don't have a componentized development upstream.
> eg. if you've feature X you could write it in a table but eg. you could
> also check if some DB objects are there and while reading a DB column
> may be easier than checking if the DB schema is in a certain status by
> checking its component one by one, if your "development" is not clearly
> componentized you may end up not only maintaining a list of checks, but
> be sure to keep them in sync with the version you write in the column
> etc...
>
> I felt that while "Refactoring Databases" has never been an epiphany for
> me, I was saved to think how to implement good object oriented
> programming techniques at the DB level.
>
> Most of the book is simply about being aware of an access interface to
> the DB, separating the "interface" from the data at the DB level, make
> change in the DB maintaining the same interface, provide a new
> interface, switch the code to the new one and providing the techniques
> to do so.
>
> But there are chapters dedicated to deployment, versioning (a la git,
> but you can't use git to version DB schema), configuration management
> that could give you a hint about the techniques you've at your disposal
> to try to manage database changes.
>
> Not only the DBA should get a grasp on these techniques but also the
> developer because not only it will make the overall structure of the
> application cleaner but eg. not always restoring the previous state of
> the VM you were working on is a feasible/best way to go when you're
> testing your code, and being able to manage "change set" to your DB
> comes handy.
>
> BTW "Refactoring Databases" is a pretty old book, probably there are
> better around, probably you could just get a glance at the chapter to
> get some ideas and then google for more and probably in 10 years not
> only they have developed better parctices but also better tools (eg. DB
> versioning systems "a la git").
>
> Eg. "database versioning control" returns much more interesting results
> than 10 years ago... I guess you could find interesting info also
> searching for "database deployment".
>
> When I was doing DB development DevOps where not on the radar yet, now
> there are entire sub-branches of deployment art.
>
> --
> Ivan Sergio Borgonovo
> https://www.webthatworks.it https://www.borgonovo.net
>
>
> _______________________________________________
> svlug mailing list
> [email protected]
> http://lists.svlug.org/lists/listinfo/svlug
>
-- 
Robert Freiberger
510-936-1210

_______________________________________________
svlug mailing list
[email protected]
http://lists.svlug.org/lists/listinfo/svlug