Bad database design? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Bad database design?

Author

Topic

fubar
Starting Member

6 Posts

Posted - 2012-05-24 : 08:26:59

I have recently started a new job. The place I am at were using MySQL and have recently (few days) switched to SQL Server 2008 R2. The problem I have is that they have a bespoke web based system that allows users to signup and use a web based system, SQL is the back end for this.

I am looking at the admin, backup and DR for this. However, the first thing I noticed is that everytime a new user signs up, an entirly new database is created for that user. It is assumed there will be thousands of users signing up, some leaving, some going dormant etc.

How am I supposed to keep track of all these databases and ensure they are ALL backed up as they will always be growing.

Now, I am no developer, by a very long way, but surely this is a inherently a bad design. Would it not be better to put users details, logins, preferences and setting into a set of tables with a unique identifier (user name) to hold them together?

Feel free to shoot me down as I am not a devoloper, its just seems odd to keep creating database after database. There could well end being thousands of these. Is there a number of databases that can be within an instance?

Any help is much appreciated.

Fubar

BTW, great forum. Have found many interesting bits of info already.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-24 : 09:10:16

I cannot even begin to tell you how suiting your forum nick is to your situation. Creating a new database for each new user is completely fubar! Have them change it as soon as possible. Preferably yesterday.

- Lumbago
My blog-> http://thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-24 : 09:12:53

Backing them all up however is not really a problem. Here is a script that will do it for you:

--> Full backup all databases
SET NOCOUNT ON

DECLARE
  @tmpDate datetime,
  @TimeStamp varchar(50),
  @totCount int,
  @Current int,
  @dbName varchar(50),
  @BackupFolder varchar(500),
  @BackupFilename varchar(500)

SET @BackupFolder = 'f:\Backup\'

DECLARE @Databases TABLE (
  ID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
  dbName varchar(50)
)

SET @tmpDate = GETDATE()
SET @TimeStamp = CONVERT(varchar(50), @tmpDate, 112) + '_' +
  RIGHT('00' + CAST(DATEPART(hh, @tmpDate) AS varchar(4)), 2) +
  RIGHT('00' + CAST(DATEPART(mi, @tmpDate) AS varchar(4)), 2)

INSERT INTO @Databases (dbName)
SELECT name
FROM sys.databases
WHERE name NOT IN ('model', 'tempdb')

SET @totCount = (SELECT @@ROWCOUNT)
SET @Current = 1

WHILE (@Current <= @totCount)
BEGIN
  SELECT @dbName = dbName FROM @Databases WHERE ID = @Current
  SET @BackupFilename = @BackupFolder + @TimeStamp + '_' + @dbName + '_full.bak'

  BACKUP DATABASE @dbName TO DISK = @BackupFilename WITH COMPRESSION, CHECKSUM
  SET @Current = @Current + 1
END

- Lumbago
My blog-> http://thefirstsql.com

fubar
Starting Member

6 Posts

Posted - 2012-05-24 : 10:12:30

Lumbago,

Thank you for the response and the script. Its not so much the backing up, although that was provong to be a bit of a headache it more the fact that there will be thousands upon thousands of databases created. The beta system had 5000 users. When the system goes live they are aiming for tens of thousands of users. I am sure that this model of design is wrong. I just need to find away of explaining it to my new employer, Jeeze I am only five days into the job!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-24 : 10:29:40

First off they'll hit a limit of 32767 databases per SQL Server instance, so if they expect that many users they'll need another SQL Server. Second is that each of those databases will use minimum 2 MB with absolutely no data in them, that's 64 GB of disk overhead. Yeah doesn't sound like much except it will also use that much RAM too, or try to, and again that's with no data actually stored, just overhead.

This also applies to each database backup you make, multiplied by however many backups you have to retain (which should be at least 3 days worth).

How many tables are in each database? What kind of indexes are there and how many? By the way, does anyone expect to do any kind of reporting out of this mess? Ask them how to find which user has the most/least of whatever it is they're trying to measure.

If it were me (this is just me talking) my response would be "Who is the idiot who designed this?" and I would wait for an answer. I'm at a stage in my career where I can say that, so this isn't advice really.

My next step is to move on to another job, again I'm at a stage where that's feasible.

fubar
Starting Member

6 Posts

Posted - 2012-05-24 : 11:26:45

Thanks robvolk, certainly food for thought. My situation is a bit strange. I kind of chucked in the rat race after 20 years as an IT consultant, mostly doing board level IT management stuff. I have moved 2000 miles to a new country and simplified my life so that I can live a meagre existance. I have taken a low level job, so I am getting my hands dirty for the first time in a long while. I have no interest in being the boss again. I have arrived on site, and as far as I can tell the design is just plain wrong. I can't say too much more, not on a public forum at least.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-24 : 12:43:00

The only explanation they could have for supporting one user per database is absolute security per user, meaning there's no chance one user could retrieve another's data. There are other ways to guarantee that using a single database, especially in SQL Server (stored procedures). My guess is that since they started on MySQL they don't know about or use stored procedures.

There's some good database design reading on Louis Davidson's blog:

http://sqlblog.com/blogs/louis_davidson/archive/tags/Database+Design/default.aspx

He also has a few editions of Database Design in SQL Server from Apress Publishing, if you prefer dead trees or eBooks. I don't remember if he addresses the multiple database paradigm but I know he goes into single table vs. multiple table pros and cons. If your team can't justify a single table per user, then they absolutely can't justify one database per user.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-24 : 16:04:09

Have you asked them who made this design decision and why? It is just terrible in every possible way. It could be justifiable if it was a white-label website or something and the users/clients were in the low hundreds (at most!) but thousands is just outrageous.

- Lumbago
My blog-> http://thefirstsql.com

fubar
Starting Member

6 Posts

Posted - 2012-05-25 : 09:31:52

Well its seems not only are they happy with the database design but I pointed out that having everything on the one 2 Tb C drive (Mirrored) was likely to cause perfomance issues etc. However, it seems they are happy with that as well. I am beginning to wonder do I stop sticking my nose in and just get on with my job. Being new and in a new country, perhaps I am just not used to how they do things. It all seems very odd to me though.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-25 : 14:45:28

Hm, how bad do you need this job, and how hard would it be to find another? They are clearly a bunch of morons and instead of taking on the fight and become the company "besserwisser" in less than a week, you could just take your hat and leave. It will be a living nightmare maintaining such a thing...

- Lumbago
My blog-> http://thefirstsql.com

fubar
Starting Member

6 Posts

Posted - 2012-05-28 : 03:39:33

The answer to "how bad do you need this job, and how hard would it be to find another" is a lot and I don't know. I worried abou this all weekend and cannot get my head around why they would do it this way. I suppose, from a security point of view, having each new user in its own database is more secure but I still believe from a scalable and management point of view its a nightmare. Got an email over the weekend asking me to look at growth plans and up-scaling of the system.

X002548
Not Just a Number

15586 Posts

Posted - 2012-05-29 : 13:02:09

What Country and what Company?

fubar
Starting Member

6 Posts

Posted - 2012-05-30 : 03:22:50

I'd rather not say, in case I get recognised. Suffice to say, I cannot afford not to have the job.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-30 : 05:39:32

This isn't a well thought out suggestion BTW....

In the interests of pragmatism -- i.e getting something that *works* and is palatable for the existing developers:

1) You have a lot of good reasons not to do this. REAL Reasons.
2) there is some perceived benefit to having all those dbs (security / permissions or whatever)

How about arguing for instead of 1 database per user, 1 schema per user.

You can still have fairly strong permissions at the schema level.... and it might not be that big a change for whatever application layer is on top of this mess..

The better solution is to stop and redesign, but that might get you going in the meantime.

As I said -- haven't really though through the implications but it might be workable as a short term thing.,

Transact Charlie
Msg 3903, Level 16, State 1, Line 1736:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
http://nosqlsolution.blogspot.co.uk/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-30 : 05:41:21

you can have 2,147,483,647 schemas.....

I don't think they introduce overhead like all those separate db's would.

Transact Charlie
Msg 3903, Level 16, State 1, Line 1736:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
http://nosqlsolution.blogspot.co.uk/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-05-30 : 05:51:41

quote:
Originally posted by robvolk

The only explanation they could have for supporting one user per database is absolute security per user, meaning there's no chance one user could retrieve another's data. There are other ways to guarantee that using a single database, especially in SQL Server (stored procedures). My guess is that since they started on MySQL they don't know about or use stored procedures.

Absolute security isn't guaranteed. The company would still have to implement basic security procedures such as not using the same account for all the db's etc.

All those db's are on the same server instance so a sql injection or some other attack has the possibility of accessing other db's if they use a common account.

ED: Rob - obviously not trying to teach you how to suck eggs -- just pointing it out for OP as more ammunition. If the developers thought this was a justifiable idea then I wonder what else they have done
Transact Charlie
Msg 3903, Level 16, State 1, Line 1736:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
http://nosqlsolution.blogspot.co.uk/

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-30 : 06:52:50

quote:
ED: Rob - obviously not trying to teach you how to suck eggs

I don't mind at all. My Grandma, on the other hand...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources