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

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