Combining databases

Databases
1

I’m at a loss here.

I’m in the process of trying to migrate all my Access databases to SQL Server. But I’m running into a problem where the database people keep insisting that all the current databases be combined into one gigantic master database, with prefixed table names. I can’t for the life of me understand how this can be anything but a Bad Idea.

The way I look at it:

  • Combining databases will make all the apps using those databases take a performance hit if one app is getting hammered.
  • If one app gets exploited, then the data from all the apps will be vulnerable.
  • If one database needs to be taken offline, you have to dig through the master db to find and remove the tables.
  • If one database needs insert and/or update privileges, they all end up with those privileges.

It just seems like the only advantage to combining them is that it makes it easier for the people maintaining them. Note that these databases/apps have nothing whatsoever to do with each other, and will never share any data.

Is this as bad an idea as I’m thinking, or am I missing something?

2

Here are another few disadvantages for monolithic databases:

  • Backups get ugly fast – you are typically better off dealing with multiple small backups than a single huge backup.
  • Development data pulls get tricky – oftentimes you want to pull the database for a single app out to develop against. Now you get to pull the databases for all the apps.
  • You have just opened the door to lots of undocumented crosstalk between apps – oh, hey, we just need a list of things from that app. Rather than bother with defining an interface we’ll just grab it from the SQL without telling the responsible dev team. Now things get exciting when the other dev team changes schemas.

I would not raise the performance issue – from the database server’s point of view it doesn’t matter; you can’t saturate a particular database but rather saturate disk or RAM i/o which will take out the whole server.

What they probably mean by table prefixes is to use MSSQL schemas which can handle alot of the security angles without too much overhead. I still would prefer physically separate databases.

3

they sound like a right bunch of numpties

god, i’m so glad i’m out of office politics

4

THAT is a really good point, especially with us being a gov’t agency. I might not be authorized to see the data in someone else’s database.

What they mean is that if I have an application for say, an office directory, rather than this:

table: staff
table: offices
table: states

We’d have this:

table: officedir_staff
table: officedir_offices
table: officedir_states

That way if two apps use a states table, one won’t overwrite the other. This explanation was right out the the dba’s mouth.

5

you sure it wasn’t some other orifice?

6

hah! Maybe.

Oh and btw, you made my wife’s day with the numpties comment. :slight_smile:

7

:smiley:

8

Wow. There are reasons I don’t do government work.

PS: those magic names are not schemas.

PPS: I’d just say “sorry, our table names are hardcoded into things. Could we get databases for our apps, thanks.”

9

In the end I got my way and I have my own standalone database.

But there are 14 others that I’ll need to convert, so I have a feeling I’ll be having a replay of this. I’m going to look for small, simple apps that can be combined, and ones (like our CMS) that can’t. I think choosing my battles is the best strategy with this.