0

I know there are a few of these posts, but not in relation to my specific quandary.

I've always gone the 'per user' SQL Server DB rather than 'one DB for all' because:

a) I thought speed wise it's better for a lite user to only work within their small DB rather than all users being possibly affected by a few big users (surely searching a few hundred rows each time is better than searching through thousands of rows all the time).

b) If a server has too many DBs (very wishful thinking) then I can put the next lot of users on a 2nd, 3rd, 4th, etc server.

c) If a user no longer wishes to use my app or needs it restoring to a backup then I just bin/or restore their specific DB and don't affect anybody else.

My problem is that my next pet project involves pupils potentially competing against pupils in other schools and also showing a league table of schools so I just keep thinking a per school DB is going to be really messy compared to everybody in one DB. Would you agree?

My fear of one DB is that later down the line there might be potentially 500 schools (again wishful thinking) with on average 300 pupils in each school which is a lot of pupil logins and results to manage in one DB - without it being lumpy.

Anybody had similar problems and overcome them?

Thanks

Improve this question
16
  • 3
    If all of the DB's are competing with the same resources (CPU, Memory, etc) or are on the same instance, thus competing for other resources like tempdb, then creating additional databases (especially in the quantities you speak of) doesn't provide any benefit that i can see. It would be, however, a nightmare to manage (backups, restores, index maintenance, dbcc operations, etc). VLDB, or very large databases, aren't uncommon so your fear may simply be fearing what you don't know, or perhaps assuming problems before they arise. Don't over optimize based on fears.
    – S3S
    Commented Dec 17, 2018 at 19:32
  • 3
    To add to scsimon's answer, "surely searching a few hundred rows each time is better than searching through thousands of rows all the time" --> Proper indexing on the table + optimizing your queries should negate this. Also, optimizing your queries and adding indexes should be more manageable on one database.
    – Randi Vertongen
    Commented Dec 17, 2018 at 19:38
  • Hi scsimon/randi thank you for the prompt and informed replies. When it comes to scale and concurrent user demand with a big db is it basically increasing the power (CPU, memory) of the server??
    – Macsicarr
    Commented Dec 17, 2018 at 19:48
  • It is very much the fear of the unknown!! :)
    – Macsicarr
    Commented Dec 17, 2018 at 19:48
  • I mean if the data is the same, the connections are the same, the batches are the same, then it should not matter if it is in one database or multiple. The only thing that changes is blocking, depending on if you are separating the schools via tables in the same database, or creating a db for each school. Updates, inserts and deletes will be a big factor here
    – Randi Vertongen
    Commented Dec 17, 2018 at 20:26

1 Answer 1

Reset to default
1

How much data are you dealing with? 500 schools with 300 students in each is not a lot of data for SQL Server to deal with if you design the tables well. As other users have mentioned in the comments, a few indexes on the right columns and you should be fine. SQL Server can happily run terabytes of data without working too hard.

The management of multiple databases is also much harder than it looks. A few examples:

  1. Deploying schema changes to multiple databases when you upgrade your application
  2. Backups for multiple databases - location, times, restore time, etc.
  3. Running other maintenance tasks (checkdb) for multiple databases

The one point you mentioned that is harder is if restoring or deleting the data for a particular school is an important requirement. It is much harder to restore/delete data for 1 school if they are all in the same database, and that reason might be enough to mean you should split the data.

My general rule is if it's the same app then you should start designing it in 1 database until you have a reason to split it.

Improve this answer
2
  • Hi Greg, thanks for tips. Yes, it's one web app to be used by multiple users/schools. I've just never done all in one db before and I think this is where the trepidation is coming from. In my previous projects, I've had a fallback that if there seemed to be too many dbs/users on the server then I just point user #400 to server #2 and then it continues - but like you say iterating a db update to all dbs is fun :)
    – Macsicarr
    Commented Dec 17, 2018 at 21:32
  • It does take away the option of scaling out, but I would be very surprised if you couldn't scale up to meet demand.
    – Greg
    Commented Dec 17, 2018 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.