Best approach with several customers, multiple DBs or single DB

Question

Best approach with several customers, multiple DBs or single DB

Tommy Skaue

SSC Journeyman

Points: 81
More actions
April 18, 2007 at 3:33 am

#173085

I am sure there are several devs out there with experience building one solution for several databases.
I am in the process of porting a solution that connects to several Access Databases (same datamodel, several mdb-files), one database per customer. Each customer can have multiple users. The datalayer expects all databases to use the same datamodel, of course.
In a SQL Server (2005) world, what would be the best approach when imlementing a shared (web-)hosting environment for several customers. Would it be to make one single database and add a customer table to run filtering through, or would it be create one database pr customer and create a login procedure that finds out which database to connect to?
-Tommy
Edit: I realize now that this post might be in the wrong forum section... or?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

Depends how segregated you need the data to be, but most of those situations are using multiple instances of SQL. One per customer.

Tommy Skaue SSC Journeyman Points: 81 More actions · Answer 2

This would then again involve spreading the datamodel to several databases using perhaps the tools from Red-Gate or some other.

It would also require a seperate userdatabase where user-logins are stored and perhaps a profile-property that points to what database the user will be using... (or?)

Harley Arnett-275660 SSC Eights! Points: 859 More actions · Answer 3

We also elected to go with one database per client - we use SQL Compare to ensure that the schemas remain in sync across clients.

Using one database per client adds some admininstrative complexity but has several performance advantages - backups and other maintenance tasks can be staggered to use different off-periods for clients around the world in different time zones - and they take less time on small individual databases vs one large one, clients can be located on various drive arrays (and even different servers in distributed locations) to improve performance, minimize risk, etc.......

Good luck,

Harley

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Related topic http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=9&messageid=359206

Tommy Skaue SSC Journeyman Points: 81 More actions · Answer 5

Thanks. Its not a one correct answer here, so just like the guy in that other thread I am sort of seeking experience. The reply in that other thread wasnt all that bad