Managing multiple databases

Question

We have done a project with around 20 tables in SQL Server, and a completed an ASP.NET project.

Recently our client told us about a new parameter called Site. As he says there are multiple sites for a project data in Application have to be different for each site. We are facing some issues now since the application is completely developed we can not update every SQL Server table and every SQL query related to Application to change according to the site parameter now we are discussing to have multiple databases for each site.

Things get worse because client says there can be around 20 sites per project. So for us it'll be very hard to manage 20 databases.

Can some one please tell us a appropriate solution for this issue?

I don't know your system, so I can only offer limited advice - but couldn't you just add the Site using e.g. a SiteID to your database, in order to have a single database - instead of creating a mess with 20 databases.... — marc_s
– marc_s, Commented May 4, 2012 at 11:46
I think you haven't explain your question as you expect. It's bit confusing.. — huMpty duMpty
– huMpty duMpty, Commented May 4, 2012 at 11:47
A database per site, but all still part of the same project, would probably come back to haunt you when they start asking for analysis on the project as part of the app. — MartW
– MartW, Commented May 4, 2012 at 11:50

Bjørn van Dommelen · Accepted Answer · 2012-05-04 11:50:14Z

1

The solution you have suggested would appear to be the correct one; use a different database per site (note: a database server can host all 20 databases easily depending on the transaction volume).

Explanation: What you need is to separate the data per site; this can either be done by adding a site identifier to the tabular data or by making sure sites do not share a datastore; the latter is in this case the easiest and most cost effective solution.

Note that "maintaining a database" is not as hard as you make it sound; in my opinion the volume of the data and transactions to manage and the number of manual corrections to make are the key factors in cost of maintenance...

answered May 4, 2012 at 11:50

Bjørn van Dommelen

1,0977 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

marc_s Over a year ago

What about reports over all sites?

Bjørn van Dommelen Over a year ago

Poster didn't list this as a requirement. If it is, the tradeoff becomes "rewriting all existing code to cope with siteID in the original database" vs "rewriting report queries that need to span multiple sites"

Anthony Shaw · Accepted Answer · 2012-05-04 12:28:41Z

0

I've been down this road in the past before and what we did was created a Site table, and then the objects that a site owned, we added a foreign key relationship back to the Site table.

In our case we only had three or four objects that a site owned, so we only had to modify 4 tables. Then the relationship from there down would still work and no modifications would be needed.

I would not go the route of a database per site, it will become a maintenance nightmare if you have to make database changes in the future or if you want to pull reports across the system as a whole

answered May 4, 2012 at 12:28

Anthony Shaw

8,1744 gold badges46 silver badges62 bronze badges

Collectives™ on Stack Overflow

Managing multiple databases

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related