Multiple DataSources on a DataSourceView

I have added multiple tables from different data sources to a dsv. When I try to make a model from that DSV, it throws an error, saying that it can't find any of my tables that are not the primary tables. Is this a bug or am I missing something that I should be doing?

TITLE: Microsoft Visual Studio

An error occurred while executing a command.
Message: Invalid object name 'dbo.CategoryID'.
Command:
SELECT COUNT(*) FROM [dbo].[CategoryID] t

BUTTONS:

OK

Thanks!

Nathan

I have done a bit of research on the issue and I am now thinking it may be a problem w/ my databases, but I am not sure. What I do need is to confirm that someone has used DSV's that contain multiple datasources with success. Thanks!

Nathan

Multiple DataSources on a DataSourceView

I have added multiple tables from different data sources to a dsv. When I try to make a model from that DSV, it throws an error, saying that it can't find any of my tables that are not the primary tables. Is this a bug or am I missing something that I should be doing?

TITLE: Microsoft Visual Studio

An error occurred while executing a command.
Message: Invalid object name 'dbo.CategoryID'.
Command:
SELECT COUNT(*) FROM [dbo].[CategoryID] t

BUTTONS:

OK

Thanks!

Nathan

I have done a bit of research on the issue and I am now thinking it may be a problem w/ my databases, but I am not sure. What I do need is to confirm that someone has used DSV's that contain multiple datasources with success. Thanks!

Nathan

multiple databases vs single db ASP model

I'm looking for more opinions on a single vs many database option for our ne
w
architecture.
Background:
We are an ASP. We host website portals for organizations around the world.
We don't own the data we store and security is a large concern. Currently w
e
maintain separate databases for each client. Most of the schemas are
different, however we customize some tables based on individual client needs
.
All of our websites are currently maintained as separate webisites, with
distinct html/asp code, but a shared set of isapi dlls.
We are in the beginning stages of designing a new architecture to reduce our
adminstrative nightmare of maintenance specifically with the multiple
html/asp code bases, but would like to simplify everywhere possible.
There are 3 scenarios we are considering.
1. Combine all databases in to one.
2. Combining all trivial/similar data into one db, but keeping the most
confidential data in separate databases.
3. Continuing the same as we are now with each client in separate databases
.
#1 would be ideal, but i have the following concerns:
Security.
Problems are all or nothing, if all clients users info is in one table, all
sites would be unavailable
Restoring a table information for one specific client becomes a nightmare.
Possible locking/blocking. We do large scale updates of 100,000s rows at a
time to insert/update one client's data. I would think this would cause
performance issues for all sites during that period.
#2 seems like a good approach, because it allows specific customizations and
constraints to client tables and lessens the administrative issues with
applying changes to structures and sp's across multiple databases.
I'm guessing we can overcome some of the issues with #1 but it will require
more work in the long run. I'm looking for any thoughts on the subject.
Thanks,
SteveJust a few random thoughts... remember they're worth exactly what you paid
for them
Most of the ASPs I've dealt with run one or more SQL Servers, but maintain a
separate database for each client. I've even written back-end applications
that automate the process of creating and securing databases for new
customers. I personally think that would probably be your best bet, since
your clients tend to have different schemas anyway. This gives your clients
access to the SQL Server, but only in their limited database. They don't
even have to know your other customers' databases exist.
Also, from a legal standpoint, this may be a requirement in some industries
that your clients may be in. I know that in some financial services,
insurance and other industries, laws for maintaining data integrity are
pretty strict. In some industries I've even seen where different
subsidiaries of the same corporation (really big in the insurance industry)
can't legally store data in the same databases, due to restrictions imposed
by law.
Obviously you can create a master database with information pertaining to
each client, such as billing info, space used, statistics, etc. But other
than that, intermingling customer data can cause legal and other problems.|||> Also, from a legal standpoint, this may be a requirement in some
industries
> that your clients may be in. I know that in some financial services,
> insurance and other industries, laws for maintaining data integrity are
> pretty strict. In some industries I've even seen where different
> subsidiaries of the same corporation (really big in the insurance
industry)
> can't legally store data in the same databases, due to restrictions
imposed
> by law.
In fact, it is often the case in this extreme, that the customer's data must
live on a different *server* than your other customers' data, not just in a
different database.
A|||Thanks for the information. Would you happen to have links with references
to such laws? We aren't in a finacial or health care industry, but do deal
with goverment funded institutions. And none of our RFC's have referenced
this, but I'd like to see some instances where this comes into play.
Thanks again,
Steve
"Aaron [SQL Server MVP]" wrote:

> industries
> industry)
> imposed
> In fact, it is often the case in this extreme, that the customer's data mu
st
> live on a different *server* than your other customers' data, not just in
a
> different database.
> A
>
>|||> Thanks for the information. Would you happen to have links with
references
> to such laws?
Personally, I don't know that there are laws per se, but I know that if a
client wants that (e.g. Fidelity is a prime example), they're going to
demand it, and you will lose the business if you don't provide it, because
it is part of their requirement for every vendor.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||I know it's big in the Insurance Industry, although I don't know what the
specific laws are. It might be part of Sarbanes-Oxley. I would use that as
a starting point to find out more. Specifically, as far as I know it tends
to affect the Financial Services sector (including banks, credit unions,
brokerages, etc.) and it mostly gets into anti-trust law, although I
wouldn't doubt that other industries like Health Care, etc., might have
similar laws and regulations.
"Steve Zohn" <SteveZohn@.discussions.microsoft.com> wrote in message
news:B68ED433-BB5C-4F2B-9758-524FBDEC1095@.microsoft.com...[vbcol=seagreen]
> Thanks for the information. Would you happen to have links with
> references
> to such laws? We aren't in a finacial or health care industry, but do
> deal
> with goverment funded institutions. And none of our RFC's have referenced
> this, but I'd like to see some instances where this comes into play.
> Thanks again,
> Steve
> "Aaron [SQL Server MVP]" wrote:
>|||Actually there are laws in place I had a heckuva time setting up
software for a client because their data had to be completely separate from
their parent company, due to laws/regulations in financial services. Even
though one was the parent company of the other, they were considered to be
"in competition" with one another, and therefore couldn't share data.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23cbaz2OKFHA.1392@.TK2MSFTNGP10.phx.gbl...
> references
> Personally, I don't know that there are laws per se, but I know that if a
> client wants that (e.g. Fidelity is a prime example), they're going to
> demand it, and you will lose the business if you don't provide it, because
> it is part of their requirement for every vendor.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>|||Thanks. I'm very pro on the multiple databases, I'm trying to come up with
solid reasons to argue against an outside consultant that is insisting that
a
single database solution is the only right solution.
"Michael C#" wrote:

> I know it's big in the Insurance Industry, although I don't know what the
> specific laws are. It might be part of Sarbanes-Oxley. I would use that
as
> a starting point to find out more. Specifically, as far as I know it tend
s
> to affect the Financial Services sector (including banks, credit unions,
> brokerages, etc.) and it mostly gets into anti-trust law, although I
> wouldn't doubt that other industries like Health Care, etc., might have
> similar laws and regulations.
> "Steve Zohn" <SteveZohn@.discussions.microsoft.com> wrote in message
> news:B68ED433-BB5C-4F2B-9758-524FBDEC1095@.microsoft.com...
>
>

multiple databases vs single db ASP model

I'm looking for more opinions on a single vs many database option for our new
architecture.
Background:
We are an ASP. We host website portals for organizations around the world.
We don't own the data we store and security is a large concern. Currently we
maintain separate databases for each client. Most of the schemas are
different, however we customize some tables based on individual client needs.
All of our websites are currently maintained as separate webisites, with
distinct html/asp code, but a shared set of isapi dlls.
We are in the beginning stages of designing a new architecture to reduce our
adminstrative nightmare of maintenance specifically with the multiple
html/asp code bases, but would like to simplify everywhere possible.
There are 3 scenarios we are considering.
1. Combine all databases in to one.
2. Combining all trivial/similar data into one db, but keeping the most
confidential data in separate databases.
3. Continuing the same as we are now with each client in separate databases.
#1 would be ideal, but i have the following concerns:
Security.
Problems are all or nothing, if all clients users info is in one table, all
sites would be unavailable
Restoring a table information for one specific client becomes a nightmare.
Possible locking/blocking. We do large scale updates of 100,000s rows at a
time to insert/update one client's data. I would think this would cause
performance issues for all sites during that period.
#2 seems like a good approach, because it allows specific customizations and
constraints to client tables and lessens the administrative issues with
applying changes to structures and sp's across multiple databases.
I'm guessing we can overcome some of the issues with #1 but it will require
more work in the long run. I'm looking for any thoughts on the subject.
Thanks,
Steve
Just a few random thoughts... remember they're worth exactly what you paid
for them
Most of the ASPs I've dealt with run one or more SQL Servers, but maintain a
separate database for each client. I've even written back-end applications
that automate the process of creating and securing databases for new
customers. I personally think that would probably be your best bet, since
your clients tend to have different schemas anyway. This gives your clients
access to the SQL Server, but only in their limited database. They don't
even have to know your other customers' databases exist.
Also, from a legal standpoint, this may be a requirement in some industries
that your clients may be in. I know that in some financial services,
insurance and other industries, laws for maintaining data integrity are
pretty strict. In some industries I've even seen where different
subsidiaries of the same corporation (really big in the insurance industry)
can't legally store data in the same databases, due to restrictions imposed
by law.
Obviously you can create a master database with information pertaining to
each client, such as billing info, space used, statistics, etc. But other
than that, intermingling customer data can cause legal and other problems.
|||> Also, from a legal standpoint, this may be a requirement in some
industries
> that your clients may be in. I know that in some financial services,
> insurance and other industries, laws for maintaining data integrity are
> pretty strict. In some industries I've even seen where different
> subsidiaries of the same corporation (really big in the insurance
industry)
> can't legally store data in the same databases, due to restrictions
imposed
> by law.
In fact, it is often the case in this extreme, that the customer's data must
live on a different *server* than your other customers' data, not just in a
different database.
A
|||Thanks for the information. Would you happen to have links with references
to such laws? We aren't in a finacial or health care industry, but do deal
with goverment funded institutions. And none of our RFC's have referenced
this, but I'd like to see some instances where this comes into play.
Thanks again,
Steve
"Aaron [SQL Server MVP]" wrote:

> industries
> industry)
> imposed
> In fact, it is often the case in this extreme, that the customer's data must
> live on a different *server* than your other customers' data, not just in a
> different database.
> A
>
>
|||> Thanks for the information. Would you happen to have links with
references
> to such laws?
Personally, I don't know that there are laws per se, but I know that if a
client wants that (e.g. Fidelity is a prime example), they're going to
demand it, and you will lose the business if you don't provide it, because
it is part of their requirement for every vendor.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
|||I know it's big in the Insurance Industry, although I don't know what the
specific laws are. It might be part of Sarbanes-Oxley. I would use that as
a starting point to find out more. Specifically, as far as I know it tends
to affect the Financial Services sector (including banks, credit unions,
brokerages, etc.) and it mostly gets into anti-trust law, although I
wouldn't doubt that other industries like Health Care, etc., might have
similar laws and regulations.
"Steve Zohn" <SteveZohn@.discussions.microsoft.com> wrote in message
news:B68ED433-BB5C-4F2B-9758-524FBDEC1095@.microsoft.com...[vbcol=seagreen]
> Thanks for the information. Would you happen to have links with
> references
> to such laws? We aren't in a finacial or health care industry, but do
> deal
> with goverment funded institutions. And none of our RFC's have referenced
> this, but I'd like to see some instances where this comes into play.
> Thanks again,
> Steve
> "Aaron [SQL Server MVP]" wrote:
|||Actually there are laws in place I had a heckuva time setting up
software for a client because their data had to be completely separate from
their parent company, due to laws/regulations in financial services. Even
though one was the parent company of the other, they were considered to be
"in competition" with one another, and therefore couldn't share data.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23cbaz2OKFHA.1392@.TK2MSFTNGP10.phx.gbl...
> references
> Personally, I don't know that there are laws per se, but I know that if a
> client wants that (e.g. Fidelity is a prime example), they're going to
> demand it, and you will lose the business if you don't provide it, because
> it is part of their requirement for every vendor.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
|||Thanks. I'm very pro on the multiple databases, I'm trying to come up with
solid reasons to argue against an outside consultant that is insisting that a
single database solution is the only right solution.
"Michael C#" wrote:

> I know it's big in the Insurance Industry, although I don't know what the
> specific laws are. It might be part of Sarbanes-Oxley. I would use that as
> a starting point to find out more. Specifically, as far as I know it tends
> to affect the Financial Services sector (including banks, credit unions,
> brokerages, etc.) and it mostly gets into anti-trust law, although I
> wouldn't doubt that other industries like Health Care, etc., might have
> similar laws and regulations.
> "Steve Zohn" <SteveZohn@.discussions.microsoft.com> wrote in message
> news:B68ED433-BB5C-4F2B-9758-524FBDEC1095@.microsoft.com...
>
>

multiple databases vs single db ASP model

I'm looking for more opinions on a single vs many database option for our new
architecture.
Background:
We are an ASP. We host website portals for organizations around the world.
We don't own the data we store and security is a large concern. Currently we
maintain separate databases for each client. Most of the schemas are
different, however we customize some tables based on individual client needs.
All of our websites are currently maintained as separate webisites, with
distinct html/asp code, but a shared set of isapi dlls.
We are in the beginning stages of designing a new architecture to reduce our
adminstrative nightmare of maintenance specifically with the multiple
html/asp code bases, but would like to simplify everywhere possible.
There are 3 scenarios we are considering.
1. Combine all databases in to one.
2. Combining all trivial/similar data into one db, but keeping the most
confidential data in separate databases.
3. Continuing the same as we are now with each client in separate databases.
#1 would be ideal, but i have the following concerns:
Security.
Problems are all or nothing, if all clients users info is in one table, all
sites would be unavailable
Restoring a table information for one specific client becomes a nightmare.
Possible locking/blocking. We do large scale updates of 100,000s rows at a
time to insert/update one client's data. I would think this would cause
performance issues for all sites during that period.
#2 seems like a good approach, because it allows specific customizations and
constraints to client tables and lessens the administrative issues with
applying changes to structures and sp's across multiple databases.
I'm guessing we can overcome some of the issues with #1 but it will require
more work in the long run. I'm looking for any thoughts on the subject.
Thanks,
SteveJust a few random thoughts... remember they're worth exactly what you paid
for them :)
Most of the ASPs I've dealt with run one or more SQL Servers, but maintain a
separate database for each client. I've even written back-end applications
that automate the process of creating and securing databases for new
customers. I personally think that would probably be your best bet, since
your clients tend to have different schemas anyway. This gives your clients
access to the SQL Server, but only in their limited database. They don't
even have to know your other customers' databases exist.
Also, from a legal standpoint, this may be a requirement in some industries
that your clients may be in. I know that in some financial services,
insurance and other industries, laws for maintaining data integrity are
pretty strict. In some industries I've even seen where different
subsidiaries of the same corporation (really big in the insurance industry)
can't legally store data in the same databases, due to restrictions imposed
by law.
Obviously you can create a master database with information pertaining to
each client, such as billing info, space used, statistics, etc. But other
than that, intermingling customer data can cause legal and other problems.|||> Also, from a legal standpoint, this may be a requirement in some
industries
> that your clients may be in. I know that in some financial services,
> insurance and other industries, laws for maintaining data integrity are
> pretty strict. In some industries I've even seen where different
> subsidiaries of the same corporation (really big in the insurance
industry)
> can't legally store data in the same databases, due to restrictions
imposed
> by law.
In fact, it is often the case in this extreme, that the customer's data must
live on a different *server* than your other customers' data, not just in a
different database.
A|||Thanks for the information. Would you happen to have links with references
to such laws? We aren't in a finacial or health care industry, but do deal
with goverment funded institutions. And none of our RFC's have referenced
this, but I'd like to see some instances where this comes into play.
Thanks again,
Steve
"Aaron [SQL Server MVP]" wrote:
> > Also, from a legal standpoint, this may be a requirement in some
> industries
> > that your clients may be in. I know that in some financial services,
> > insurance and other industries, laws for maintaining data integrity are
> > pretty strict. In some industries I've even seen where different
> > subsidiaries of the same corporation (really big in the insurance
> industry)
> > can't legally store data in the same databases, due to restrictions
> imposed
> > by law.
> In fact, it is often the case in this extreme, that the customer's data must
> live on a different *server* than your other customers' data, not just in a
> different database.
> A
>
>|||> Thanks for the information. Would you happen to have links with
references
> to such laws?
Personally, I don't know that there are laws per se, but I know that if a
client wants that (e.g. Fidelity is a prime example), they're going to
demand it, and you will lose the business if you don't provide it, because
it is part of their requirement for every vendor.
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||I know it's big in the Insurance Industry, although I don't know what the
specific laws are. It might be part of Sarbanes-Oxley. I would use that as
a starting point to find out more. Specifically, as far as I know it tends
to affect the Financial Services sector (including banks, credit unions,
brokerages, etc.) and it mostly gets into anti-trust law, although I
wouldn't doubt that other industries like Health Care, etc., might have
similar laws and regulations.
"Steve Zohn" <SteveZohn@.discussions.microsoft.com> wrote in message
news:B68ED433-BB5C-4F2B-9758-524FBDEC1095@.microsoft.com...
> Thanks for the information. Would you happen to have links with
> references
> to such laws? We aren't in a finacial or health care industry, but do
> deal
> with goverment funded institutions. And none of our RFC's have referenced
> this, but I'd like to see some instances where this comes into play.
> Thanks again,
> Steve
> "Aaron [SQL Server MVP]" wrote:
>> > Also, from a legal standpoint, this may be a requirement in some
>> industries
>> > that your clients may be in. I know that in some financial services,
>> > insurance and other industries, laws for maintaining data integrity are
>> > pretty strict. In some industries I've even seen where different
>> > subsidiaries of the same corporation (really big in the insurance
>> industry)
>> > can't legally store data in the same databases, due to restrictions
>> imposed
>> > by law.
>> In fact, it is often the case in this extreme, that the customer's data
>> must
>> live on a different *server* than your other customers' data, not just in
>> a
>> different database.
>> A
>>|||Actually there are laws in place :) I had a heckuva time setting up
software for a client because their data had to be completely separate from
their parent company, due to laws/regulations in financial services. Even
though one was the parent company of the other, they were considered to be
"in competition" with one another, and therefore couldn't share data.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23cbaz2OKFHA.1392@.TK2MSFTNGP10.phx.gbl...
>> Thanks for the information. Would you happen to have links with
> references
>> to such laws?
> Personally, I don't know that there are laws per se, but I know that if a
> client wants that (e.g. Fidelity is a prime example), they're going to
> demand it, and you will lose the business if you don't provide it, because
> it is part of their requirement for every vendor.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>|||Thanks. I'm very pro on the multiple databases, I'm trying to come up with
solid reasons to argue against an outside consultant that is insisting that a
single database solution is the only right solution.
"Michael C#" wrote:
> I know it's big in the Insurance Industry, although I don't know what the
> specific laws are. It might be part of Sarbanes-Oxley. I would use that as
> a starting point to find out more. Specifically, as far as I know it tends
> to affect the Financial Services sector (including banks, credit unions,
> brokerages, etc.) and it mostly gets into anti-trust law, although I
> wouldn't doubt that other industries like Health Care, etc., might have
> similar laws and regulations.
> "Steve Zohn" <SteveZohn@.discussions.microsoft.com> wrote in message
> news:B68ED433-BB5C-4F2B-9758-524FBDEC1095@.microsoft.com...
> > Thanks for the information. Would you happen to have links with
> > references
> > to such laws? We aren't in a finacial or health care industry, but do
> > deal
> > with goverment funded institutions. And none of our RFC's have referenced
> > this, but I'd like to see some instances where this comes into play.
> >
> > Thanks again,
> > Steve
> >
> > "Aaron [SQL Server MVP]" wrote:
> >
> >> > Also, from a legal standpoint, this may be a requirement in some
> >> industries
> >> > that your clients may be in. I know that in some financial services,
> >> > insurance and other industries, laws for maintaining data integrity are
> >> > pretty strict. In some industries I've even seen where different
> >> > subsidiaries of the same corporation (really big in the insurance
> >> industry)
> >> > can't legally store data in the same databases, due to restrictions
> >> imposed
> >> > by law.
> >>
> >> In fact, it is often the case in this extreme, that the customer's data
> >> must
> >> live on a different *server* than your other customers' data, not just in
> >> a
> >> different database.
> >>
> >> A
> >>
> >>
> >>
>
>

Multiple Databases vs a Few

I started work a new company recently and they provide a web based tool to
companies. The database model is in rough shape -- when a new client comes
on they simply create a new database for each client. Right now they have
roughly 400+ databases split over two database servers.
I am suggesting they bring that # down to a couple. I think managing 400
connections to a variety of databases might be a performance concern. I
would also think that in terms of Stored Procs it must be using more
resources -- instead of optimizing 1 it has to optimize the same 1, 400
times.
Im guessing on those above -- does anyone know the postive and negatives in
doing this, or know of any article discussing this topic.
Thanks
Brian wrote:
> I started work a new company recently and they provide a web based
> tool to companies. The database model is in rough shape -- when a
> new client comes on they simply create a new database for each
> client. Right now they have roughly 400+ databases split over two
> database servers.
> I am suggesting they bring that # down to a couple. I think managing
> 400 connections to a variety of databases might be a performance
> concern. I would also think that in terms of Stored Procs it must
> be using more resources -- instead of optimizing 1 it has to optimize
> the same 1, 400 times.
> Im guessing on those above -- does anyone know the postive and
> negatives in doing this, or know of any article discussing this topic.
> Thanks
I don't necessarily think using separate databases is an architectural
problem. It does, however, lead to more involved backups and SP
management. OTOH, the databases are smaller, can be backed up more
quickly, and different databases offers a level of security that might
be more difficult to manage should all clients be combined into the same
tables in the same database. What happens when a client leaves? How
involved is the removal of data if they share tables? What happens if
one client's data is accidentally shown as being "owned" by another
client? Does that lead to potential legal concerns... You get the idea.
The architecture, it seems, may need to involve legal as well as IT.
Your comments about SP duplication, however, is a concern. Certainly, if
you could find a way to share all common procedures, it would help
maintenance. You could consider using a shared database for all (most)
stored procedure access. That should allow you to use a single set of
procs for all user databases. Instead of executing the procedure in the
current database, just prefix the shared db (shareddb.dbo.procname).
David Gugick
Imceda Software
www.imceda.com
|||Brian
There are no reasons to create a database for each client. I think you are
going to build a new model of the database , only you do know a business
requrements.
Visit at www.sql-server_performance.com and you will find a really good
stuff there.
"Brian" <brian@.nospam.com> wrote in message
news:%23k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client
comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>
|||It is an administrator's nightmare. However, if the DBs for each of your
clients has data related to that specific client and should not be mixed
with other clients (which is correct) then I guess, you will have to live
with it. If the DBs contain code and metadata for your web based client
application, then consolidating is the right approach.
"Brian" <brian@.nospam.com> wrote in message
news:#k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client
comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>
|||Have you considered using partitioned views to unify all this data in a
common DB? Using that method you may only need a single copy of much of your
server-side code.
I would have expected that the main argument for unifying the databases into
one would be based on the potential savings on development, reporting and
admin costs over the costs of those things for 100s of identical databases.
David Portas
SQL Server MVP
|||There is little to no difference between the databases. A couple clients
have a few custom pieces but nothing that could not be solved in the data
model.
Aside from the admin nightmare (it exists now), is there any performance
hit. I guess this may be more is an IIS webserver question. Having to
manage 400 different connection strings.
And again the duplicate Stored Procs, and who knows what else.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:uwhvCspuEHA.2144@.tk2msftngp13.phx.gbl...
> It is an administrator's nightmare. However, if the DBs for each of your
> clients has data related to that specific client and should not be mixed
> with other clients (which is correct) then I guess, you will have to live
> with it. If the DBs contain code and metadata for your web based client
> application, then consolidating is the right approach.
>
>
> "Brian" <brian@.nospam.com> wrote in message
> news:#k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> comes
> in
>
|||Hi Brian,
I am a dba for multiple companies in a datacenter and one of our companies
has around 400 lawfirms and everything is in one database. The main thing
that you have to think about is security. In our case each lawfirm has a role
and many tables contain a field that specifies to which law firm the row
belongs (row level security). This is basically the idea but it gets more
complex when you have multiple groups with each law firm. This is based on
the requirements and something that you have to analyze). If it was me, I
would create one database with 2 clients and test the crap out of it... The
process might be painful but you have a lot to win as a DBA (amount of work
for you and the developers, $$$, more simplified disaster recovery
process...).
Sasan Saidi, MSc. in CS
"Brian" wrote:

> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives in
> doing this, or know of any article discussing this topic.
> Thanks
>
>
|||Hi Brian,
I have noticed Peter and Sasan Saidi had given their suggestions on this
issue. I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Multiple Databases vs a Few

I started work a new company recently and they provide a web based tool to
companies. The database model is in rough shape -- when a new client comes
on they simply create a new database for each client. Right now they have
roughly 400+ databases split over two database servers.
I am suggesting they bring that # down to a couple. I think managing 400
connections to a variety of databases might be a performance concern. I
would also think that in terms of Stored Procs it must be using more
resources -- instead of optimizing 1 it has to optimize the same 1, 400
times.
Im guessing on those above -- does anyone know the postive and negatives in
doing this, or know of any article discussing this topic.
ThanksBrian wrote:
> I started work a new company recently and they provide a web based
> tool to companies. The database model is in rough shape -- when a
> new client comes on they simply create a new database for each
> client. Right now they have roughly 400+ databases split over two
> database servers.
> I am suggesting they bring that # down to a couple. I think managing
> 400 connections to a variety of databases might be a performance
> concern. I would also think that in terms of Stored Procs it must
> be using more resources -- instead of optimizing 1 it has to optimize
> the same 1, 400 times.
> Im guessing on those above -- does anyone know the postive and
> negatives in doing this, or know of any article discussing this topic.
> Thanks
I don't necessarily think using separate databases is an architectural
problem. It does, however, lead to more involved backups and SP
management. OTOH, the databases are smaller, can be backed up more
quickly, and different databases offers a level of security that might
be more difficult to manage should all clients be combined into the same
tables in the same database. What happens when a client leaves? How
involved is the removal of data if they share tables? What happens if
one client's data is accidentally shown as being "owned" by another
client? Does that lead to potential legal concerns... You get the idea.
The architecture, it seems, may need to involve legal as well as IT.
Your comments about SP duplication, however, is a concern. Certainly, if
you could find a way to share all common procedures, it would help
maintenance. You could consider using a shared database for all (most)
stored procedure access. That should allow you to use a single set of
procs for all user databases. Instead of executing the procedure in the
current database, just prefix the shared db (shareddb.dbo.procname).
David Gugick
Imceda Software
www.imceda.com|||Brian
There are no reasons to create a database for each client. I think you are
going to build a new model of the database , only you do know a business
requrements.
Visit at www.sql-server_performance.com and you will find a really good
stuff there.
"Brian" <brian@.nospam.com> wrote in message
news:%23k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client
comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>|||It is an administrator's nightmare. However, if the DBs for each of your
clients has data related to that specific client and should not be mixed
with other clients (which is correct) then I guess, you will have to live
with it. If the DBs contain code and metadata for your web based client
application, then consolidating is the right approach.
"Brian" <brian@.nospam.com> wrote in message
news:#k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client
comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>|||Have you considered using partitioned views to unify all this data in a
common DB? Using that method you may only need a single copy of much of your
server-side code.
I would have expected that the main argument for unifying the databases into
one would be based on the potential savings on development, reporting and
admin costs over the costs of those things for 100s of identical databases.
--
David Portas
SQL Server MVP
--|||There is little to no difference between the databases. A couple clients
have a few custom pieces but nothing that could not be solved in the data
model.
Aside from the admin nightmare (it exists now), is there any performance
hit. I guess this may be more is an IIS webserver question. Having to
manage 400 different connection strings.
And again the duplicate Stored Procs, and who knows what else.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:uwhvCspuEHA.2144@.tk2msftngp13.phx.gbl...
> It is an administrator's nightmare. However, if the DBs for each of your
> clients has data related to that specific client and should not be mixed
> with other clients (which is correct) then I guess, you will have to live
> with it. If the DBs contain code and metadata for your web based client
> application, then consolidating is the right approach.
>
>
> "Brian" <brian@.nospam.com> wrote in message
> news:#k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
>> I started work a new company recently and they provide a web based tool
>> to
>> companies. The database model is in rough shape -- when a new client
> comes
>> on they simply create a new database for each client. Right now they
>> have
>> roughly 400+ databases split over two database servers.
>> I am suggesting they bring that # down to a couple. I think managing 400
>> connections to a variety of databases might be a performance concern. I
>> would also think that in terms of Stored Procs it must be using more
>> resources -- instead of optimizing 1 it has to optimize the same 1, 400
>> times.
>> Im guessing on those above -- does anyone know the postive and negatives
> in
>> doing this, or know of any article discussing this topic.
>> Thanks
>>
>|||Hi Brian,
I am a dba for multiple companies in a datacenter and one of our companies
has around 400 lawfirms and everything is in one database. The main thing
that you have to think about is security. In our case each lawfirm has a role
and many tables contain a field that specifies to which law firm the row
belongs (row level security). This is basically the idea but it gets more
complex when you have multiple groups with each law firm. This is based on
the requirements and something that you have to analyze). If it was me, I
would create one database with 2 clients and test the crap out of it... The
process might be painful but you have a lot to win as a DBA (amount of work
for you and the developers, $$$, more simplified disaster recovery
process...).
--
Sasan Saidi, MSc. in CS
"Brian" wrote:
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives in
> doing this, or know of any article discussing this topic.
> Thanks
>
>|||Personally I would go for the smaller the number of
databases the better.
Here are two reasons.
If you need a to make a change to the schema (i.e. insert
a new table), you will need to insert the new table into
every database table, and that goes for every schema
change and standard / generic database changes.
The second is general DB work, you will need to write a
lot of generic procedures that effect all the databases.
There however a really big downside to this which is
security. Assuming each customer db has there own users /
roles ect putting them all into one big database can be a
bit of a problem, as SQL Server as yet does not have
automatic row selections for specific users.
Here is an example.
You have three databases, each of them has
a 'PersonDetails' table. You mold them into one big table,
how do you stop the people in database 1 accessing info in
database 2.
It is possible to implement this, but requires a change to
your schema.
However my personal opinon still stands.
Peter
"Real knowledge is to know the extent of one's ignorance."
Confucius
>--Original Message--
>I started work a new company recently and they provide a
web based tool to
>companies. The database model is in rough shape -- when
a new client comes
>on they simply create a new database for each client.
Right now they have
>roughly 400+ databases split over two database servers.
>I am suggesting they bring that # down to a couple. I
think managing 400
>connections to a variety of databases might be a
performance concern. I
>would also think that in terms of Stored Procs it must be
using more
>resources -- instead of optimizing 1 it has to optimize
the same 1, 400
>times.
>Im guessing on those above -- does anyone know the
postive and negatives in
>doing this, or know of any article discussing this topic.
>Thanks
>
>.
>|||Hi Brian,
I have noticed Peter and Sasan Saidi had given their suggestions on this
issue. I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Multiple Databases vs a Few

I started work a new company recently and they provide a web based tool to
companies. The database model is in rough shape -- when a new client comes
on they simply create a new database for each client. Right now they have
roughly 400+ databases split over two database servers.
I am suggesting they bring that # down to a couple. I think managing 400
connections to a variety of databases might be a performance concern. I
would also think that in terms of Stored Procs it must be using more
resources -- instead of optimizing 1 it has to optimize the same 1, 400
times.
Im guessing on those above -- does anyone know the postive and negatives in
doing this, or know of any article discussing this topic.
ThanksBrian wrote:
> I started work a new company recently and they provide a web based
> tool to companies. The database model is in rough shape -- when a
> new client comes on they simply create a new database for each
> client. Right now they have roughly 400+ databases split over two
> database servers.
> I am suggesting they bring that # down to a couple. I think managing
> 400 connections to a variety of databases might be a performance
> concern. I would also think that in terms of Stored Procs it must
> be using more resources -- instead of optimizing 1 it has to optimize
> the same 1, 400 times.
> Im guessing on those above -- does anyone know the postive and
> negatives in doing this, or know of any article discussing this topic.
> Thanks
I don't necessarily think using separate databases is an architectural
problem. It does, however, lead to more involved backups and SP
management. OTOH, the databases are smaller, can be backed up more
quickly, and different databases offers a level of security that might
be more difficult to manage should all clients be combined into the same
tables in the same database. What happens when a client leaves? How
involved is the removal of data if they share tables? What happens if
one client's data is accidentally shown as being "owned" by another
client? Does that lead to potential legal concerns... You get the idea.
The architecture, it seems, may need to involve legal as well as IT.
Your comments about SP duplication, however, is a concern. Certainly, if
you could find a way to share all common procedures, it would help
maintenance. You could consider using a shared database for all (most)
stored procedure access. That should allow you to use a single set of
procs for all user databases. Instead of executing the procedure in the
current database, just prefix the shared db (shareddb.dbo.procname).
David Gugick
Imceda Software
www.imceda.com|||Brian
There are no reasons to create a database for each client. I think you are
going to build a new model of the database , only you do know a business
requrements.
Visit at www.sql-server_performance.com and you will find a really good
stuff there.
"Brian" <brian@.nospam.com> wrote in message
news:%23k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client
comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>|||It is an administrator's nightmare. However, if the DBs for each of your
clients has data related to that specific client and should not be mixed
with other clients (which is correct) then I guess, you will have to live
with it. If the DBs contain code and metadata for your web based client
application, then consolidating is the right approach.
"Brian" <brian@.nospam.com> wrote in message
news:#k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client
comes
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>|||Have you considered using partitioned views to unify all this data in a
common DB? Using that method you may only need a single copy of much of your
server-side code.
I would have expected that the main argument for unifying the databases into
one would be based on the potential savings on development, reporting and
admin costs over the costs of those things for 100s of identical databases.
David Portas
SQL Server MVP
--|||There is little to no difference between the databases. A couple clients
have a few custom pieces but nothing that could not be solved in the data
model.
Aside from the admin nightmare (it exists now), is there any performance
hit. I guess this may be more is an IIS webserver question. Having to
manage 400 different connection strings.
And again the duplicate Stored Procs, and who knows what else.
"Bhanu" <SQLDBA1999@.yahoo.com> wrote in message
news:uwhvCspuEHA.2144@.tk2msftngp13.phx.gbl...
> It is an administrator's nightmare. However, if the DBs for each of your
> clients has data related to that specific client and should not be mixed
> with other clients (which is correct) then I guess, you will have to live
> with it. If the DBs contain code and metadata for your web based client
> application, then consolidating is the right approach.
>
>
> "Brian" <brian@.nospam.com> wrote in message
> news:#k$J8cpuEHA.3808@.TK2MSFTNGP10.phx.gbl...
> comes
> in
>|||Hi Brian,
I am a dba for multiple companies in a datacenter and one of our companies
has around 400 lawfirms and everything is in one database. The main thing
that you have to think about is security. In our case each lawfirm has a rol
e
and many tables contain a field that specifies to which law firm the row
belongs (row level security). This is basically the idea but it gets more
complex when you have multiple groups with each law firm. This is based on
the requirements and something that you have to analyze). If it was me, I
would create one database with 2 clients and test the crap out of it... The
process might be painful but you have a lot to win as a DBA (amount of work
for you and the developers, $$$, more simplified disaster recovery
process...).
Sasan Saidi, MSc. in CS
"Brian" wrote:

> I started work a new company recently and they provide a web based tool to
> companies. The database model is in rough shape -- when a new client come
s
> on they simply create a new database for each client. Right now they have
> roughly 400+ databases split over two database servers.
> I am suggesting they bring that # down to a couple. I think managing 400
> connections to a variety of databases might be a performance concern. I
> would also think that in terms of Stored Procs it must be using more
> resources -- instead of optimizing 1 it has to optimize the same 1, 400
> times.
> Im guessing on those above -- does anyone know the postive and negatives
in
> doing this, or know of any article discussing this topic.
> Thanks
>
>|||Hi Brian,
I have noticed Peter and Sasan Saidi had given their suggestions on this
issue. I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Multiple databases for each client of one big master DB?

We are designing a new data model, and have many clients. Currently they each have their own database in SQL.

We are contemplating the pro's and con's of migrating to a Single Master Database.

I would like to hear from others on what your thoughts are on the pros and cons. Also, if you have experience doing something similar, please speak up.

Thanks in advance,
ScAndal2 of the biggest cons I see are restore database ability and the ability to customize things on a per client basis.|||I was at this "fork in the road" at the beginning of this month. I have a system that is runs within an Application Sevice Model over the web. There are approx. 26 tables and 275 stored procedures for the system. database Our initial design was to have a differenct database for each client. There was one main controlling database which would administer the clients and their respective databases. The model was working fine and I even had the administration of creating new clients automatic. But the harsh reality of a maintenance nightmere was coming true. We have the potential of having 60,000 clients, which is equivilent to 60,000 databases and then aother 60,000 databases on another server on another coast that is used for replication. That is a total of 120,000 SQL Databases!

Though the reality of having that many clients will most likely never happen, it did bring up a very good point: what happens if you have to make one filed change a two stored procedure changes? What a nightmere!

We made the decision to modify the model to oeprate from one database which uses administration tables to administer the clients through the database. This model is more cost efficient an safe from corruption. It will allow easy replication and backup of logs. Though the data is now squashed into one Data Structure it makes sense to let the database do what it was desinged for: manage data. A good portion of your decision should also be based upon your possible client base. Determine the minimum number and maximum number of clients that you could potentialy have and go from there.

HTH|||Wes you bring up some great points. Currently our client base is < 20. I forsee us never taking on more than 100.

ScAndal|||One of the pros of the multi database approach is data security and integrity between client databases. This is why I initially designed the model using multi-databases. Each client login would utilize a different SQL Server login account that was specific to their database. Therefore if one of my clients network was penetrated by a hacker who then gained login access to the system and decided to distroy and corrupt the database, I would only have to explain to 1 client what had happend, and at worst lose 1 client--even though the hacker came through their network!. Whereas in the single database model I would potentially have to explain to 200 other clients, and possibly lose 200 clients, why their data is corrupt and the system is offline for thirty minutes while I restore from backups on the other coast (given the hacker was not able to jump to those servers). That is my strongest argument for the multe-database model.

Another aspect to look at is Administration of the Databases after development and deployment. Is your time limited and the databases will need to run without much intervention or will you be fully dedicated to these databases where you might get bored with them?

I would love to hear about experiences from others on this same issue.

Multiple data sources in a report model

I've built several data view sources and report models and think its great.
But this week I'v ran into problems. I'm trying to implementent a report
model that uses data from three databases. I've managed to build the data
source view containing tables from all three different data sources, but when
I try to build the report model it fails. It seems that it can't find tables
in other data sources that the primary.In SQL Server 2005, a report model can have only one data source file, one
data source view file, and one smdl file. What you are attempting to do is
not a supported scenario in this release.
"Erik Hedlund" wrote:
> I've built several data view sources and report models and think its great.
> But this week I'v ran into problems. I'm trying to implementent a report
> model that uses data from three databases. I've managed to build the data
> source view containing tables from all three different data sources, but when
> I try to build the report model it fails. It seems that it can't find tables
> in other data sources that the primary.|||Hmm, this is very limiting, because the data source view can contain tables
from several datasources. It's rather odd that the report model should be
concerned about lies bellow the data source view abstraction layer.
"Katmac [MS]" wrote:
> In SQL Server 2005, a report model can have only one data source file, one
> data source view file, and one smdl file. What you are attempting to do is
> not a supported scenario in this release.
> "Erik Hedlund" wrote:
> > I've built several data view sources and report models and think its great.
> > But this week I'v ran into problems. I'm trying to implementent a report
> > model that uses data from three databases. I've managed to build the data
> > source view containing tables from all three different data sources, but when
> > I try to build the report model it fails. It seems that it can't find tables
> > in other data sources that the primary.|||Multiple data source support is on the wish list for a future release.
"Erik Hedlund" wrote:
> Hmm, this is very limiting, because the data source view can contain tables
> from several datasources. It's rather odd that the report model should be
> concerned about lies bellow the data source view abstraction layer.
> "Katmac [MS]" wrote:
> > In SQL Server 2005, a report model can have only one data source file, one
> > data source view file, and one smdl file. What you are attempting to do is
> > not a supported scenario in this release.
> >
> > "Erik Hedlund" wrote:
> >
> > > I've built several data view sources and report models and think its great.
> > > But this week I'v ran into problems. I'm trying to implementent a report
> > > model that uses data from three databases. I've managed to build the data
> > > source view containing tables from all three different data sources, but when
> > > I try to build the report model it fails. It seems that it can't find tables
> > > in other data sources that the primary.|||If the product doesn't allow a DSV to have multiple data sources, then you
shouldn't be able to save a DSV that references more than 1 DS. I just
wasted three hours trying to track down this "bug." Certainly would have
saved me time had VS just told me it didn't support more than 1 data source
in a dsv. instead, I got a cryptic message at model generation time that it
coudln't find a table.
"Katmac [MS]" wrote:
> Multiple data source support is on the wish list for a future release.
> "Erik Hedlund" wrote:
> > Hmm, this is very limiting, because the data source view can contain tables
> > from several datasources. It's rather odd that the report model should be
> > concerned about lies bellow the data source view abstraction layer.
> >
> > "Katmac [MS]" wrote:
> >
> > > In SQL Server 2005, a report model can have only one data source file, one
> > > data source view file, and one smdl file. What you are attempting to do is
> > > not a supported scenario in this release.
> > >
> > > "Erik Hedlund" wrote:
> > >
> > > > I've built several data view sources and report models and think its great.
> > > > But this week I'v ran into problems. I'm trying to implementent a report
> > > > model that uses data from three databases. I've managed to build the data
> > > > source view containing tables from all three different data sources, but when
> > > > I try to build the report model it fails. It seems that it can't find tables
> > > > in other data sources that the primary.

Multiple Client Database Model

Does anyone have a sample of what a database would look like that would support multiple clients in a single ASP.net application?

Thank you,

Why would this be any different from any other database?
A database is meant to be shared amongst multiple clients. That'sone of the main reasons for using databases instead of storing data inflat files or other application-based structures. Databasessupport transactions and locking that keep clients from destroying eachothers' work. Perhaps I'm misunderstanding your question,though? Are you looking for an example of a specific type ofdatabase?

|||

Really what I was hoping was an example of a database that would be used on a web host that could maintain more than one client using it. For example two or more companies using the same hosted database. What is done to the data tables to allow for the separation of the companies and their clients, etc...

I hope this explains it better.

Thank you,

|||Okay, that makes a lot more sense
This can be achieved using what's known as "row-based security". See this article:
http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm
Note that there are some serious caveats in a row-based securityscheme, and if you really need heavy security it's probably better todo a seperate database per client. Steve Kass (SQL Server MVP)has posted some interesting ways he's found to hack row-based securityschemes -- unfortunately, the SQL Server engine doesn't realize you'redoing row-based security so it doesn't know not to show certain data inerrors/etc if you give it the right inputs.

|||Thank you, I will check it out.