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 upsoftware 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...
>
>
No comments:
Post a Comment