MultiUsers Question

We had a debate on designing a database .The use case is;

an application that we will rent to other companies from our own datacenter on the web.They will all use the same database schema.We found 3 alternatives for our solution.But we are not sure which one is the best.

We are expecting 20.000 insurance agencies to use the database composed of 33 tables.And they will all have their unique data but sometimes for reporting we might union their data.

ALTERNATIVES

1.Make 20.000 database instances.Every agency will have their own database instance.

2.Make one database instance , put a database ownerID(int) field to every table.And use appropiate indexes.*or table partioning

3.Make one database instance , and create 33 tables for every database owner.So total number of tables in the datase equals to numberofUsers X 33

Did anyone tested this situation?

*an article I read on the web said partining is allowed to 1000 per table and it is slow in SQL 2005.(I am confused)

http://www.sqljunkies.com/Article/F4920050-6C63-4109-93FF-C2B7EB0A5835.scuk

In common, I am a fan of putting all the data together in one database. based on the information that you provided it is hard to decide wheter you need separated database / instances or not.

There are several things which you should consider:

-Organisational structure (You could also split the database up in regions / geographical or country based)
-Maintainance (Due to different timezones / work loads on the databases you might have different maintainance windows to do something)
-Load balancing
-Scaling
-Availability
-Estimated workload

etc.

I think that we both agree that you wouldn′t put 20000 dbs on on server, or create thousends of instances (the limit is 25 for one computer)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

sql