Morning

Morning

Morning

Morning

Morning

Morning, data scientists

@Philᵀᴹ Where?

@Philᵀᴹ That's so 2016. Data Engineers are all the rage now.

@hot2use Don't say that I was feeling good - I've never been called a scientist before

@George.Palacios :-)

Although I think data magician sounds much better

@MaxVernon When are you coming to Blighty?

or Data Wizard

I prefer data shoveller.

Master of Data

@AndriyM thanks, is there any advantage by using <pre> tag instead of the usual 4-spaced text?

@hot2use Sounds too much like a scrum master.

People might think you're a knob.

@ConcernedOfTunbridgeWells And if I switch it around?

Data Master

Sounds even more like scrum master.

Lord Data - Master of the DB-verse

@hot2use At least nobody will confuse you with an Agile evangelist.

Supreme lord data dommander

commander

dommander is a COMPLETELY different job

On an unrelated note, I think Portrait is the most egregious abuse of a relational DBMS platform I've ever seen.

@ConcernedOfTunbridgeWells What is it?

@George.Palacios Workflow system

@ConcernedOfTunbridgeWells What's wrong with it?

Not merely content to embed most of the data in opaque and highly configurable XML blobs, they encode relationships to other DB entities within the XML documents. Then, some of the relationships come out as encrypted strings if you don't configure the system to save them as identities.

The contents of the relationships are in configurable attributes and, of course, no two classes are alike.

At least Duck Creek and Guidewire have the decency to include everything in the blob.

Oh wow

@McNets I use the <pre> tag when I want monospaced text that isn't code (like ascii-art tables), but that's just a general habit

Sounds like it would have been better suited to a document DB

Dynamic joins can be nasty though, nevermind dynamic joins based on contents of XML

@ConcernedOfTunbridgeWells Just burn it down

@George.Palacios We are. Currently profiling the data to do a migration off the platform.

@ConcernedOfTunbridgeWells That sounds incredibly painful in itself, but like the only sane option

@George.Palacios Don't give the devs ideas. I've never seen such a database-phobic development culture as they have here.

@ConcernedOfTunbridgeWells idk... i've found myself recommending document storage more and more lately. but i've got a lot of devs who have a bad habit of insisting they need another column with a dedicated index added to super bloated "oltp" tables

column name [FreeText55]? 😭

@PeterVandivier They're planing to use Azure Blob storage for the physical document store.

@McNets Sorry, I actually meant to explain my frivolous edit but was distracted by work-related issues. First of all, the issue with the code snippet where I replaced the <pre> tag with 4-space indentation was that conditions like P2 <> 0 didn't show correctly. The <> was parsed as some kind of tag and was rendered invisible, so it ended up showing P2 0 instead.

In such cases if I need to keep it inside the <pre> tag, I replace all < with <, but in this case the block already was indented with 4 spaces, so I just removed the pre tag

And then I couldn't stop myself doing the opposite transformation to all non-code snippets, i.e. removing the 4-space indentation and wrapping them in pre blocks

The reason for that was the same as @PeterVandivier explained, since those were not actually code, they didn't need highlighting. If you keep 4-space indentation, you can disable highlighting by adding a special comment before the block, but I figured using pre was easier in that case.

@McNets Of course you are perfectly free to disregard all my reasoning and style your code blocks the way you like. I didn't mean to impose. As I said, I wanted to explain myself straight away but was distracted. Please let me know if I really crossed the line with my edit there.

i wonder if you could do a CDATA block inside the pre tag 🤔 - but at some point you probably should have a lie down and think about your priorities 😜

@AndriyM no, no worries. It's perfect, I didn't realize about <> issue. In this case I used notepad++ to edit my answer, fiddle added hours as 00:00:00 to the dates and I shorted all of them and I removed too the quotes > that it adds to the code bloks. Thanks.

I'm not sure I like material design

@Taryn I'm just wondering from a professional perspective: How many databases are you responsible for? And how many peers have the same job as you do? Is this a one-person show at SE?

@hot2use Databases? It depends on the server/cluster. I'm the only DBA here at Stack, although Nick Craver pretty much knows all since I took over all the servers from him

We have servers with 5 databases and servers with 370, so it varies greatly

@Taryn size-wise, how do you balance data to ram?

like, for the servers with 370, i assume the databases are smaller

with a lot of care

@Lamak WHAT DO YOU KNOW ABOUT CARE

NOTHING AT ALL!

you dubious rapscallion

when are you gonna invade venezuela?

@sp_BlitzErik carefully :P

For example the 5 DB server has the SO database plus a couple of others, the 370 DB server has all the SE sites and some larger databases, like Careers

do they all have the same amount of memory?

Nope, the SO servers have 1.5TB of RAM and the SE servers have ~770GB

@Taryn "Nick Craver knows all" - I think the Ouija board said that at my last seance

@sp_BlitzErik it's not on my plans yet

@Taryn And it's SQL Server only? Or do you take care of MySQL, PostgreSQL and other products?

@Taryn ...and thanks for the answers so far.

@hot2use SQL Server only around here

@Taryn Thanks a lot. Much appreciated.

A question for the regulars in here. What are your opinions on this answer:

@hot2use I mean... it's not a great answer. I think your comment is pretty constructive. Whether or not the user reads & follows up is kind of up to them

Hello.

I'm building an ecommerce database model. My company already has a current design which lacks in flexibility and features so we're somewhat rethinking it from scratch.

demo.akeneo.com

user admin password admin

this open source project "akeneo" is already kind of doing that so I'm looking at it

I thought it would be more obvious, looking at the diagram really doesn't tell me much by itself..

wow….the queries will be quite something

@A_V How can that lack flexibility? Every column is an EAV construction in itself

This is not my design, this is an online open source project

At first sight it looks as if everything ends up in an attribute table

which, TBH is not unseen in PIM

I'm looking at it trying to see what they're doing. It indeed is an EAV at each and every level. The relationship integration is most of the design it seems

@TomV exactly

@Taryn So that would be 500 databases tops? Or more?

Are there particular good reads you could recomend on PIM database design

it seems you always end up needing an EAV table somewhere

@hot2use about that ~520 total databases - not including the databases on replicas

The reason I'm asking. Our three person team is responsible for 1354 database on SQL Server (880 DB on 88 instances), PostgreSQL (108 on 20 instances), MySQL (74 on 12 instances ) and Oracle (279 on 30 instances) with others trailing(13). And we are finding it difficult to coordinate updates, because of the complexity. I'm trying to collect some numbers to present to management so that we could apply for slightly more person-power.

too on the nose? brentozar.com/archive/2015/12/…

and I'm really envious at how fast you adopt to new SQL Server versions

@PeterVandivier read that three years ago before I left the previous company. In the end it wasn't the main reason to leave, but it helped me to make my decision.

@Taryn Thanks.

@hot2use in my very limited experience, a big part of management willingness to upgrade quickly is tolerance for breakage. If you're an early-adopter company, it shows across the entire stack and it's not limited to just the DBs. also - if you're pretty buttoned up in terms of logging and ability to respond to aberrant behavior, then the kinds of breakage that come with migration aren't seen as world-ending

on the other hand, if there's a low tolerance for that, then you may be in the kind of situation where you have to fight tooth and nail just to keep the patching schedule you've got... cough... cough... but that's definitely not the situation i'm in now... cough...

@PeterVandivier If you're developing your own programs, then that isn't much of an issue. If you are running third-party solutions, then you have to rely on your suppliers to update their products to support current DBMS versions.

@hot2use I just throw new versions at it and hope things work. :)

...and we don't develop ourselves. We implement a software that requires a database, and those products are then kind of .... well here to stay.

@Taryn I'd love to.

When I started here not one SQL Server had the correct SP. That was one of my first big tasks.

Took me three quarters of a year to fix.

I'm not complaining. Could be worse.

off to re-boot that SQL Server 2005

@hot2use welcome to the thunderdome. i once got a cease-and-desist from the State of California b/c a SQL 2000 box running on a Windows 98 VM had an IP conflict with the State DOC. The application that needed that server could never be stopped because compliance and the IP it used to find the DB server could not be updated. IIRC we did some fun VMWare swapping to get it into a network segment where we could keep the two little nightmares isolated from the rest of world.

I'm pretty sure things "needing to stay online forever" is one of the circles of hell

@PeterVandivier agree

@hot2use we still have a SQL Server 2005 active

And the last we've installed this year a 2014, due 3rd party software hasn't certified if for 2016/7

@McNets Welcome to the circle.

@hot2use yep

And I was planning to use Postgres for a data collector, connected to our PLC's, just to (force me to) learn more.

@PeterVandivier So, so true

Oldest we have here is an Oracle 8i

and SQL 2005

The 8i is so fragile. I treat it like a frail old man at this point

What's that 8i? Your network dropped out for a microsecond and now all of my secondaries are out of sync. It's okay you just watch TV and I'll fix things.

@George.Palacios In June 2018, for a client, I had to fix a business critical Oracle 8i instance running on Windows XP. The pinnacle of my career.

@BalazsPapp :o

Windows XP?

> running enterprise database software on a desktop OS

The emoji version was not appropriately large.

@BalazsPapp Hoooo boy

@A_V not really. I've only seen a couple of pim systems myself but on the surface they all looked like that

@ConcernedOfTunbridgeWells I arrive on Dec 19th and return on January 9th, so pretty much Christmas.

will be in Bournemouth or Norfolk

@MaxVernon Bit early for drinkies, unfortunately. Drop us a line if you're anywhere near Tunbridge Wells.

@ConcernedOfTunbridgeWells yah, that's was I was thinking. Maybe next time!

^^^Do you think it should be closed?

@McNets too localized, in my opinion.

@MaxVernon yes

@BalazsPapp I see you also want to be the butt of some Hungarian notation jokes from @ConcernedOfTunbridgeWells