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

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 theweb.They will all use thesame 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

I would choose #1 possibly if it would be important for backup purposes to keep the data "separate". Cross reporting would be difficult, but not totally impossible -- if you can work from a "snapshot". It would be easiest to run a process to pull the data you need from all the agencies into a singular table structure just for the report(s), that has the added OwnerID column. You could do this either as some nightly process, or place a trigger on each of the tables that need to be reported on to replicate the changes to the common table. It would be a completely redundant dataset, so the backup of it isn't all that important, other than the table structure, and any special views/stored procedures/extra tables you may have in there.

This approach also makes it a lot easier to insure that a mistake isn't made whereby another agencies data isn't presented to a different agency. If you have a DBA (Which I'm guessing you do not, or you are the DBA), then they should be able to provide you with views and stored procedures that the application programmers are allowed to use to access the data and hence shouldn't be able to get themselves into trouble.

This type of setup is a bit more complicated to set up, but, it does effectively partition the tables for cross-agency reports.

4. Make one database instance, put a database ownerID(int) field in one (or more) of the tables. ID's are global (across agencies), so that a ownerID is redundant in tables that use an ID (or GUID) as it's primary key that is created in a different table.

|||hmm I am sorry but I did not understand how your 4 th approach differs from the 2 nd approach?|||

First 20,000 insurance agencies is not many, if you take them to be users, it means you have 20,000 users. In the US insurance is divided into Property and Casuality for home and cars policies and Life and Health. So just put them into the type of policy sold or by location and it becomes just 20,000 users. I have told you before you need a DDL(data definition language) expert.

If you don't have it installed check the Pubs sample database for ideas if you decide to use location to separate them because car insurance is sold by state drivers license. Hope this helps.

|||

The 4th approach differs from the 2nd in that if you have say these two tables:

Users->UserID,Name, etc.
Policies->PolicyID(IDENTITY),OwnerID(a UserID),SalesmanID(a UserID),PolicyTypeID, etc.
PolicyRider->PolicyID,RiderTypeID,etc.

In approach 2, the tables would look like this:

Users->AgencyID,UserID,Name, etc.
Policies->AgencyID,PolicyID,OwnerID,SalesmanID,PolicyTypeID,etc
PolicyRider->AgencyID,PolicyID,RiderTypeID,etc.

Where in approach 4, the tables would look like this if you allow any user to belong to multiple Agencies:

Users->UserID,Name,etc
UserAgencies->UserID,AgencyID
Policies->AgencyID,PolicyID, ...
PolicyRider->PolicyID,RiderTypeID,etc (NOTE No AgencyID, because the PolicyRider record has a reference to the Policies table entry which already contains a AgencyID)

Or like this, if no Users can be in multiple agencies:

Users->AgencyID,UserID, ...
Policies->PolicyID, ... (NOTE No AgencyID because each policy is owned by a user, and each user can only be in a single agency, this policy must belong to the agency the user is in)
PolicyRider->PolicyRider->PolicyID,RiderTypeID,etc (NOTE No AgencyID, because the PolicyRider record has a reference to the Policies table entry which has a reference to a Users table entry which contains a AgencyID)

You may want to attach the AgencyID to the Policy instead of the User, in these cases, but the idea is that so long as the "parent" of any relationship already has an AgencyID, the children do not, since you can imply their AgencyID by referencing their (singular) parent's AgencyID.

|||

if no Users can be in multiple agencies:

Users->AgencyID,UserID, ...
Policies->PolicyID, ... (NOTE No AgencyID because each policy is owned by a user, and each user can only be in a single agency, this policy must belong to the agency the user is in)

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I have read for 4 times.But did not understand how will I know that a record is owned by that AGENCY or by that USER.

maybe it is too late and I am really tired...is there anyuserID on the Policies table?

PS:I really do wanna implement a simple accountability pattern for the user system but performant.So the users can be in agencies but I will define that by simple attributeTypes in database not using the relations...

|||Sorry, yes. I was assuming that the Policies table would also have a OwnerID field (that's a UserID) or an AgentID (also a UserID), or some other field that you would use that to tie the record back to a particular user which is then tied to an AgencyID.

Multiple Server Reporting

Let me preface this
I need to design a report that displays data for 3 branches of same
companies,
Branch A ,Branch B and Branch C. The problem is that each Branch has their
own
distinct server and database. The schema is identical between them, just
different data in each.
The report is identical for each Branch, the only different is that the
report needs to pull data from all the three Databases A,B,C to display the
report data for that Company as an aggregated view and Also data for each
branch
individually.
How can I design the report or setup the data source.
I really don't want to create and maintain 3 identical reports, the only
difference being the data source.
One possible option I guess would be to create a Linked Server and
Distributed view that combines data from identical tables in 3 databases, and
use that view as the datasource in the report.
Other option is moving data to the centralized server and report from that,
which would cause data redundancy both at local branch and at centralized
server and huge vloume of data movement and keeping track of data.
Just looking for what others have done in similar circumstances so I don't
spend multiple days architecting the wrong approach.
Thanks!
SHi There,
I was under impression that there are expertise here who has implemented a
valid and reliable solution to my similar issue. Now it makes me to beleive
that none has gone through such a senario.
Any help or suggestion are welcome
/
S
"ERS Developer" wrote:
> Let me preface this
> I need to design a report that displays data for 3 branches of same
> companies,
> Branch A ,Branch B and Branch C. The problem is that each Branch has their
> own
> distinct server and database. The schema is identical between them, just
> different data in each.
> The report is identical for each Branch, the only different is that the
> report needs to pull data from all the three Databases A,B,C to display the
> report data for that Company as an aggregated view and Also data for each
> branch
> individually.
> How can I design the report or setup the data source.
> I really don't want to create and maintain 3 identical reports, the only
> difference being the data source.
> One possible option I guess would be to create a Linked Server and
> Distributed view that combines data from identical tables in 3 databases, and
> use that view as the datasource in the report.
> Other option is moving data to the centralized server and report from that,
> which would cause data redundancy both at local branch and at centralized
> server and huge vloume of data movement and keeping track of data.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!
> S|||ERS,
The simplest solution is linked servers (if you have high
availability), have one report and use IF statements based on a
parameter to decide the query to use.
The parameter would be the branch, so you could have;
If @.Branch = 'A'
Select * From BranchAServer.MyDB.dbo.MyTable
If @.Branch = 'B'
Select * From BranchBServer.MyDB.dbo.MyTable
If @.Branch = 'C'
Select * From BranchCServer.MyDB.dbo.MyTable
So the user could select their branch. This is very basic and could be
made more efficient placing the query in a stored procedure. Also there
is aperformance issue with distributed queries like this pulling all
data to the calling server before processing. Ideally you'd use an
OPENQUERY, check BOL for info on that.
Letting the user select a branch is obviously a security issue, but you
could deny access to the main report but create 'linked' reports for
each branch and set the branch parameter in properties of the linked
report and set it not to prompt the user. 'Linked' reports are merely
shortcuts, so when you change the main report, it's reflected in the
linked reports as well, but the parameter settings remain independant.
There is another option which we use here, which is to create a simple
form of data warehouse. This is more useful if you have disparate
datasources. Basically, create jobs that copies data from the branches
into a central database periodically, say every 20 minutes. To reduce
redundancy, just take the fields you need. You could use replication to
do this also. Not only will this make your report quicker, but it
should reduce the overhead on your OLTP servers.
Chris
ERS Developer wrote:
> Hi There,
> I was under impression that there are expertise here who has
> implemented a valid and reliable solution to my similar issue. Now it
> makes me to beleive that none has gone through such a senario.
> Any help or suggestion are welcome
> /
> S
> "ERS Developer" wrote:
> > Let me preface this
> > I need to design a report that displays data for 3 branches of same
> > companies,
> > Branch A ,Branch B and Branch C. The problem is that each Branch
> > has their own
> > distinct server and database. The schema is identical between them,
> > just different data in each.
> >
> > The report is identical for each Branch, the only different is that
> > the report needs to pull data from all the three Databases A,B,C to
> > display the report data for that Company as an aggregated view and
> > Also data for each branch
> > individually.
> >
> > How can I design the report or setup the data source.
> >
> > I really don't want to create and maintain 3 identical reports, the
> > only difference being the data source.
> >
> > One possible option I guess would be to create a Linked Server and
> > Distributed view that combines data from identical tables in 3
> > databases, and use that view as the datasource in the report.
> >
> > Other option is moving data to the centralized server and report
> > from that, which would cause data redundancy both at local branch
> > and at centralized server and huge vloume of data movement and
> > keeping track of data.
> >
> > Just looking for what others have done in similar circumstances so
> > I don't spend multiple days architecting the wrong approach.
> >
> > Thanks!
> > S|||Hi S/All,
just to let viewers here know the generic answer to the generic
question...
Lots of people, tens of thousands of them, have faced the issue of
reporting from multiple different oprational systems and the Corporate
Information Factory is one rather comprehensive solution to the
pervasive issue of consolitated analysis/reporting...
Best Regards
Peter Nolan
www.peternolan.com
http://groups-beta.google.com/group/microsoft.public.sqlserver.datawarehouse/browse_frm/thread/473404688a753292/64024bd1636f02e9?hl=en#64024bd1636f02e9