Creating different instances for each customer

Hello,
We want to manage customers over a VPN, such that a customer logs into the
software, the accounting software (smart client), and checks his/her info.
We at the server want to manage the accounts with the software.
Is creating different instances for each customer a good way, or we should
create one instance and add their databases in the same instance? What are
the pros and cons?
I appreciate your comments in advance.
--
MikeCreating one instance per customer is almost always a bad idea. First of all,
a server can only support that many instances (the maximum number of SQL2000
instances supported on a computer is 16). Secondly, there is significant
overhead in maintaining an instance that would be a total waste repeated for
each customer. You would be much better off with one database per customer
within an instance. In most cases, you would be even better off with a single
database for multiple customers, and that's what a DBMS is designed for.
Thirdly, forget about the scalability and feasibility for now. The cost
factor would be prohibitive if you have many customers since you would have
to keep adding more servers.
All that said, one instance per customer can be easily justified if we are
talking about each customer being sufficient large in its data volume,
resource consumption, importance in isolation, and so on. Say if you only
have a few cusotmer accounts and each is a Fortune 500 company and each is
generating a lot of revenue for you. Heck, you may even need to dedicate a
cluster for each customer.
So we need more detail to be specific.
Linchi
"Mike9900" wrote:
> Hello,
> We want to manage customers over a VPN, such that a customer logs into the
> software, the accounting software (smart client), and checks his/her info.
> We at the server want to manage the accounts with the software.
> Is creating different instances for each customer a good way, or we should
> create one instance and add their databases in the same instance? What are
> the pros and cons?
> I appreciate your comments in advance.
> --
> Mike|||Hi Linchi,
Thanks for help.
We use SQL Server 2005. It is not possible to keep all customers in one db.
But I think each instance for a customer is a bad idea because the instance
would allocate its own memory.
But we are concerned about customer security. If all customers use the same
instance, we must keep the sa password secret. Also, our software does not
use SQL server user id to connect, instead it has a master user id in SQL
server, and the user connects to the app, then the app uses the master
password to check the user id with the db. So, if we create different user
id for each customer the app connects to SQL server with the same master user
id. So the connection string have the same userid/password, but will have
different computer name. I am wondering if this may decrease the performance.
Our customer are small and do not have huge transactions.
Mike
"Linchi Shea" wrote:
> Creating one instance per customer is almost always a bad idea. First of all,
> a server can only support that many instances (the maximum number of SQL2000
> instances supported on a computer is 16). Secondly, there is significant
> overhead in maintaining an instance that would be a total waste repeated for
> each customer. You would be much better off with one database per customer
> within an instance. In most cases, you would be even better off with a single
> database for multiple customers, and that's what a DBMS is designed for.
> Thirdly, forget about the scalability and feasibility for now. The cost
> factor would be prohibitive if you have many customers since you would have
> to keep adding more servers.
> All that said, one instance per customer can be easily justified if we are
> talking about each customer being sufficient large in its data volume,
> resource consumption, importance in isolation, and so on. Say if you only
> have a few cusotmer accounts and each is a Fortune 500 company and each is
> generating a lot of revenue for you. Heck, you may even need to dedicate a
> cluster for each customer.
> So we need more detail to be specific.
> Linchi
> "Mike9900" wrote:
> > Hello,
> >
> > We want to manage customers over a VPN, such that a customer logs into the
> > software, the accounting software (smart client), and checks his/her info.
> > We at the server want to manage the accounts with the software.
> >
> > Is creating different instances for each customer a good way, or we should
> > create one instance and add their databases in the same instance? What are
> > the pros and cons?
> >
> > I appreciate your comments in advance.
> >
> > --
> > Mike|||I assume you guys are the author of this software. If so, I don't see why it
is NOT possible to use multiple databases instead of multiple instances.
Fixing the overall architecture later on will be much more difficult than
fixing how the logins are handled now.
Linchi
"Mike9900" wrote:
> Hi Linchi,
> Thanks for help.
> We use SQL Server 2005. It is not possible to keep all customers in one db.
> But I think each instance for a customer is a bad idea because the instance
> would allocate its own memory.
> But we are concerned about customer security. If all customers use the same
> instance, we must keep the sa password secret. Also, our software does not
> use SQL server user id to connect, instead it has a master user id in SQL
> server, and the user connects to the app, then the app uses the master
> password to check the user id with the db. So, if we create different user
> id for each customer the app connects to SQL server with the same master user
> id. So the connection string have the same userid/password, but will have
> different computer name. I am wondering if this may decrease the performance.
> Our customer are small and do not have huge transactions.
>
> --
> Mike
>
> "Linchi Shea" wrote:
> > Creating one instance per customer is almost always a bad idea. First of all,
> > a server can only support that many instances (the maximum number of SQL2000
> > instances supported on a computer is 16). Secondly, there is significant
> > overhead in maintaining an instance that would be a total waste repeated for
> > each customer. You would be much better off with one database per customer
> > within an instance. In most cases, you would be even better off with a single
> > database for multiple customers, and that's what a DBMS is designed for.
> > Thirdly, forget about the scalability and feasibility for now. The cost
> > factor would be prohibitive if you have many customers since you would have
> > to keep adding more servers.
> >
> > All that said, one instance per customer can be easily justified if we are
> > talking about each customer being sufficient large in its data volume,
> > resource consumption, importance in isolation, and so on. Say if you only
> > have a few cusotmer accounts and each is a Fortune 500 company and each is
> > generating a lot of revenue for you. Heck, you may even need to dedicate a
> > cluster for each customer.
> >
> > So we need more detail to be specific.
> >
> > Linchi
> >
> > "Mike9900" wrote:
> >
> > > Hello,
> > >
> > > We want to manage customers over a VPN, such that a customer logs into the
> > > software, the accounting software (smart client), and checks his/her info.
> > > We at the server want to manage the accounts with the software.
> > >
> > > Is creating different instances for each customer a good way, or we should
> > > create one instance and add their databases in the same instance? What are
> > > the pros and cons?
> > >
> > > I appreciate your comments in advance.
> > >
> > > --
> > > Mike|||Thanks.
So my question is if the connection string differs by the computer name, in
Workstation ID of conn string, would the sql server think of it as a
different connection string in the pool.
--
Mike
"Linchi Shea" wrote:
> I assume you guys are the author of this software. If so, I don't see why it
> is NOT possible to use multiple databases instead of multiple instances.
> Fixing the overall architecture later on will be much more difficult than
> fixing how the logins are handled now.
> Linchi
> "Mike9900" wrote:
> > Hi Linchi,
> >
> > Thanks for help.
> >
> > We use SQL Server 2005. It is not possible to keep all customers in one db.
> > But I think each instance for a customer is a bad idea because the instance
> > would allocate its own memory.
> >
> > But we are concerned about customer security. If all customers use the same
> > instance, we must keep the sa password secret. Also, our software does not
> > use SQL server user id to connect, instead it has a master user id in SQL
> > server, and the user connects to the app, then the app uses the master
> > password to check the user id with the db. So, if we create different user
> > id for each customer the app connects to SQL server with the same master user
> > id. So the connection string have the same userid/password, but will have
> > different computer name. I am wondering if this may decrease the performance.
> >
> > Our customer are small and do not have huge transactions.
> >
> >
> >
> > --
> > Mike
> >
> >
> > "Linchi Shea" wrote:
> >
> > > Creating one instance per customer is almost always a bad idea. First of all,
> > > a server can only support that many instances (the maximum number of SQL2000
> > > instances supported on a computer is 16). Secondly, there is significant
> > > overhead in maintaining an instance that would be a total waste repeated for
> > > each customer. You would be much better off with one database per customer
> > > within an instance. In most cases, you would be even better off with a single
> > > database for multiple customers, and that's what a DBMS is designed for.
> > > Thirdly, forget about the scalability and feasibility for now. The cost
> > > factor would be prohibitive if you have many customers since you would have
> > > to keep adding more servers.
> > >
> > > All that said, one instance per customer can be easily justified if we are
> > > talking about each customer being sufficient large in its data volume,
> > > resource consumption, importance in isolation, and so on. Say if you only
> > > have a few cusotmer accounts and each is a Fortune 500 company and each is
> > > generating a lot of revenue for you. Heck, you may even need to dedicate a
> > > cluster for each customer.
> > >
> > > So we need more detail to be specific.
> > >
> > > Linchi
> > >
> > > "Mike9900" wrote:
> > >
> > > > Hello,
> > > >
> > > > We want to manage customers over a VPN, such that a customer logs into the
> > > > software, the accounting software (smart client), and checks his/her info.
> > > > We at the server want to manage the accounts with the software.
> > > >
> > > > Is creating different instances for each customer a good way, or we should
> > > > create one instance and add their databases in the same instance? What are
> > > > the pros and cons?
> > > >
> > > > I appreciate your comments in advance.
> > > >
> > > > --
> > > > Mike|||It is never a good idea to give out the sa password regardless of how you
implement. Hopefully your app is not running under sa rights.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
news:17B8417C-A9BD-44DD-8F10-68BCE2735FFC@.microsoft.com...
> Hi Linchi,
> Thanks for help.
> We use SQL Server 2005. It is not possible to keep all customers in one
> db.
> But I think each instance for a customer is a bad idea because the
> instance
> would allocate its own memory.
> But we are concerned about customer security. If all customers use the
> same
> instance, we must keep the sa password secret. Also, our software does
> not
> use SQL server user id to connect, instead it has a master user id in SQL
> server, and the user connects to the app, then the app uses the master
> password to check the user id with the db. So, if we create different
> user
> id for each customer the app connects to SQL server with the same master
> user
> id. So the connection string have the same userid/password, but will have
> different computer name. I am wondering if this may decrease the
> performance.
> Our customer are small and do not have huge transactions.
>
> --
> Mike
>
> "Linchi Shea" wrote:
>> Creating one instance per customer is almost always a bad idea. First of
>> all,
>> a server can only support that many instances (the maximum number of
>> SQL2000
>> instances supported on a computer is 16). Secondly, there is significant
>> overhead in maintaining an instance that would be a total waste repeated
>> for
>> each customer. You would be much better off with one database per
>> customer
>> within an instance. In most cases, you would be even better off with a
>> single
>> database for multiple customers, and that's what a DBMS is designed for.
>> Thirdly, forget about the scalability and feasibility for now. The cost
>> factor would be prohibitive if you have many customers since you would
>> have
>> to keep adding more servers.
>> All that said, one instance per customer can be easily justified if we
>> are
>> talking about each customer being sufficient large in its data volume,
>> resource consumption, importance in isolation, and so on. Say if you only
>> have a few cusotmer accounts and each is a Fortune 500 company and each
>> is
>> generating a lot of revenue for you. Heck, you may even need to dedicate
>> a
>> cluster for each customer.
>> So we need more detail to be specific.
>> Linchi
>> "Mike9900" wrote:
>> > Hello,
>> >
>> > We want to manage customers over a VPN, such that a customer logs into
>> > the
>> > software, the accounting software (smart client), and checks his/her
>> > info.
>> > We at the server want to manage the accounts with the software.
>> >
>> > Is creating different instances for each customer a good way, or we
>> > should
>> > create one instance and add their databases in the same instance? What
>> > are
>> > the pros and cons?
>> >
>> > I appreciate your comments in advance.
>> >
>> > --
>> > Mike|||No, our app create a different user id which has data reader/Writer rights
only and is not a db admin.
But the security for the app is defined and enforced by the application, and
not the sql server. The reason is that the app does not use stored procs,
and uses many things for the security not just security on table, such as the
right of the user to sell below certain price.
--
Mike
"Andrew J. Kelly" wrote:
> It is never a good idea to give out the sa password regardless of how you
> implement. Hopefully your app is not running under sa rights.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Mike9900" <Mike9900@.discussions.microsoft.com> wrote in message
> news:17B8417C-A9BD-44DD-8F10-68BCE2735FFC@.microsoft.com...
> > Hi Linchi,
> >
> > Thanks for help.
> >
> > We use SQL Server 2005. It is not possible to keep all customers in one
> > db.
> > But I think each instance for a customer is a bad idea because the
> > instance
> > would allocate its own memory.
> >
> > But we are concerned about customer security. If all customers use the
> > same
> > instance, we must keep the sa password secret. Also, our software does
> > not
> > use SQL server user id to connect, instead it has a master user id in SQL
> > server, and the user connects to the app, then the app uses the master
> > password to check the user id with the db. So, if we create different
> > user
> > id for each customer the app connects to SQL server with the same master
> > user
> > id. So the connection string have the same userid/password, but will have
> > different computer name. I am wondering if this may decrease the
> > performance.
> >
> > Our customer are small and do not have huge transactions.
> >
> >
> >
> > --
> > Mike
> >
> >
> > "Linchi Shea" wrote:
> >
> >> Creating one instance per customer is almost always a bad idea. First of
> >> all,
> >> a server can only support that many instances (the maximum number of
> >> SQL2000
> >> instances supported on a computer is 16). Secondly, there is significant
> >> overhead in maintaining an instance that would be a total waste repeated
> >> for
> >> each customer. You would be much better off with one database per
> >> customer
> >> within an instance. In most cases, you would be even better off with a
> >> single
> >> database for multiple customers, and that's what a DBMS is designed for.
> >> Thirdly, forget about the scalability and feasibility for now. The cost
> >> factor would be prohibitive if you have many customers since you would
> >> have
> >> to keep adding more servers.
> >>
> >> All that said, one instance per customer can be easily justified if we
> >> are
> >> talking about each customer being sufficient large in its data volume,
> >> resource consumption, importance in isolation, and so on. Say if you only
> >> have a few cusotmer accounts and each is a Fortune 500 company and each
> >> is
> >> generating a lot of revenue for you. Heck, you may even need to dedicate
> >> a
> >> cluster for each customer.
> >>
> >> So we need more detail to be specific.
> >>
> >> Linchi
> >>
> >> "Mike9900" wrote:
> >>
> >> > Hello,
> >> >
> >> > We want to manage customers over a VPN, such that a customer logs into
> >> > the
> >> > software, the accounting software (smart client), and checks his/her
> >> > info.
> >> > We at the server want to manage the accounts with the software.
> >> >
> >> > Is creating different instances for each customer a good way, or we
> >> > should
> >> > create one instance and add their databases in the same instance? What
> >> > are
> >> > the pros and cons?
> >> >
> >> > I appreciate your comments in advance.
> >> >
> >> > --
> >> > Mike
>|||To quote MS documentation: "Each connection pool is associated with a
distinct connection string. When a new connection is opened, if the
connection string is not an exact match to an existing pool, a new pool is
created."
Linchi
"Mike9900" wrote:
> Thanks.
> So my question is if the connection string differs by the computer name, in
> Workstation ID of conn string, would the sql server think of it as a
> different connection string in the pool.
> --
> Mike
>
> "Linchi Shea" wrote:
> > I assume you guys are the author of this software. If so, I don't see why it
> > is NOT possible to use multiple databases instead of multiple instances.
> > Fixing the overall architecture later on will be much more difficult than
> > fixing how the logins are handled now.
> >
> > Linchi
> >
> > "Mike9900" wrote:
> >
> > > Hi Linchi,
> > >
> > > Thanks for help.
> > >
> > > We use SQL Server 2005. It is not possible to keep all customers in one db.
> > > But I think each instance for a customer is a bad idea because the instance
> > > would allocate its own memory.
> > >
> > > But we are concerned about customer security. If all customers use the same
> > > instance, we must keep the sa password secret. Also, our software does not
> > > use SQL server user id to connect, instead it has a master user id in SQL
> > > server, and the user connects to the app, then the app uses the master
> > > password to check the user id with the db. So, if we create different user
> > > id for each customer the app connects to SQL server with the same master user
> > > id. So the connection string have the same userid/password, but will have
> > > different computer name. I am wondering if this may decrease the performance.
> > >
> > > Our customer are small and do not have huge transactions.
> > >
> > >
> > >
> > > --
> > > Mike
> > >
> > >
> > > "Linchi Shea" wrote:
> > >
> > > > Creating one instance per customer is almost always a bad idea. First of all,
> > > > a server can only support that many instances (the maximum number of SQL2000
> > > > instances supported on a computer is 16). Secondly, there is significant
> > > > overhead in maintaining an instance that would be a total waste repeated for
> > > > each customer. You would be much better off with one database per customer
> > > > within an instance. In most cases, you would be even better off with a single
> > > > database for multiple customers, and that's what a DBMS is designed for.
> > > > Thirdly, forget about the scalability and feasibility for now. The cost
> > > > factor would be prohibitive if you have many customers since you would have
> > > > to keep adding more servers.
> > > >
> > > > All that said, one instance per customer can be easily justified if we are
> > > > talking about each customer being sufficient large in its data volume,
> > > > resource consumption, importance in isolation, and so on. Say if you only
> > > > have a few cusotmer accounts and each is a Fortune 500 company and each is
> > > > generating a lot of revenue for you. Heck, you may even need to dedicate a
> > > > cluster for each customer.
> > > >
> > > > So we need more detail to be specific.
> > > >
> > > > Linchi
> > > >
> > > > "Mike9900" wrote:
> > > >
> > > > > Hello,
> > > > >
> > > > > We want to manage customers over a VPN, such that a customer logs into the
> > > > > software, the accounting software (smart client), and checks his/her info.
> > > > > We at the server want to manage the accounts with the software.
> > > > >
> > > > > Is creating different instances for each customer a good way, or we should
> > > > > create one instance and add their databases in the same instance? What are
> > > > > the pros and cons?
> > > > >
> > > > > I appreciate your comments in advance.
> > > > >
> > > > > --
> > > > > Mike|||Mike9900 wrote on Sat, 13 Oct 2007 11:12:01 -0700:
> No, our app create a different user id which has data reader/Writer
> rights only and is not a db admin.
> But the security for the app is defined and enforced by the
> application, and not the sql server. The reason is that the app does
> not use stored procs, and uses many things for the security not just
> security on table, such as the right of the user to sell below certain
> price.
> -- Mike
Why not use a combination of SQL security (so clients can only access their
own database), and application level settings.
Dan|||Server app and client app can access their own dbs, but it does not limit the
client app to access certain tables within the db; which means the server app
does not provide any access.
But in the futures we may limit the client app by a server app.
--
Mike
"Daniel Crichton" wrote:
> Mike9900 wrote on Sat, 13 Oct 2007 11:12:01 -0700:
> > No, our app create a different user id which has data reader/Writer
> > rights only and is not a db admin.
> > But the security for the app is defined and enforced by the
> > application, and not the sql server. The reason is that the app does
> > not use stored procs, and uses many things for the security not just
> > security on table, such as the right of the user to sell below certain
> > price.
> > -- Mike
> Why not use a combination of SQL security (so clients can only access their
> own database), and application level settings.
> Dan
>
>