One DB machine for Two WebSites

When there is 1 DB machine for 2 web sites, is it better to have

One DataBase-instance running with 2 DataBase-Initial-Catalogs ?

or

Two DataBase-instances running, each with 1 DataBase-Initial-Catalog ?

using SQLExpress or SQLServer

please only look at those possible, simple conditions.

forget about read/write same tables, security issues..

Interms of Memory/CPU/ Performance, which setting seems better ?

Please give some thoughts on Performance / DataLoad / Consumption,,,etc...

thanks.

For pure performance and simplicity, one instance of SQL Server with two databases will work better. With multiple instances of SQL Server, your available memory is split between the instances.|||

Thanks.

But if another instance is running and loads in memory,

would it be faster for getting results in database ?

Assume I have lots of memory.

thanks.

|||One instance will be faster than two instances, since the single instance will have access to all of the machine's resources (RAM, CPU, I/O).

One database or many database?

I have a web application which collect data from MANY clients. Data
collected is in the same structure, my questions is: to use one database
store all data or create a database for each client? which way is better?
Thank you
Quinn
There is no right or wrong answer here. If you have one huge DB, then
backup and recovery can be problematic. If you have 100's of small DB's,
then it becomes an administrative challenge, though much admin can be
automated/scripted.
There are also security issues. Some clients refuse to commingle their data
with other companies.
Also, if you needed to separate a client into its own DB on another server,
it's not straightforward. Whereas, if you have each client in it's own DB,
it's just a matter of backup and restore.
If the number of clients that you have is small, then likely you can get
away with a single DB.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Quinn" <qjia@.yahoo.com> wrote in message
news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
I have a web application which collect data from MANY clients. Data
collected is in the same structure, my questions is: to use one database
store all data or create a database for each client? which way is better?
Thank you
Quinn
|||Thank you Tom.
Right now I don't know how many clients yet, but 500+- may close enough.
Quinn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23G8mcPspHHA.4212@.TK2MSFTNGP04.phx.gbl...
> There is no right or wrong answer here. If you have one huge DB, then
> backup and recovery can be problematic. If you have 100's of small DB's,
> then it becomes an administrative challenge, though much admin can be
> automated/scripted.
> There are also security issues. Some clients refuse to commingle their
> data
> with other companies.
> Also, if you needed to separate a client into its own DB on another
> server,
> it's not straightforward. Whereas, if you have each client in it's own
> DB,
> it's just a matter of backup and restore.
> If the number of clients that you have is small, then likely you can get
> away with a single DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Quinn" <qjia@.yahoo.com> wrote in message
> news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
> I have a web application which collect data from MANY clients. Data
> collected is in the same structure, my questions is: to use one database
> store all data or create a database for each client? which way is better?
>
> Thank you
> Quinn
>
|||Then, if you go the one DB per client route, you'll need to automate your
maintenance processes, e.g. backup, reindexing and the like. It's not that
big of a problem; you just need to have it in place.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Quinn" <qjia@.yahoo.com> wrote in message
news:OceHgUspHHA.4772@.TK2MSFTNGP05.phx.gbl...
Thank you Tom.
Right now I don't know how many clients yet, but 500+- may close enough.
Quinn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23G8mcPspHHA.4212@.TK2MSFTNGP04.phx.gbl...
> There is no right or wrong answer here. If you have one huge DB, then
> backup and recovery can be problematic. If you have 100's of small DB's,
> then it becomes an administrative challenge, though much admin can be
> automated/scripted.
> There are also security issues. Some clients refuse to commingle their
> data
> with other companies.
> Also, if you needed to separate a client into its own DB on another
> server,
> it's not straightforward. Whereas, if you have each client in it's own
> DB,
> it's just a matter of backup and restore.
> If the number of clients that you have is small, then likely you can get
> away with a single DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Quinn" <qjia@.yahoo.com> wrote in message
> news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
> I have a web application which collect data from MANY clients. Data
> collected is in the same structure, my questions is: to use one database
> store all data or create a database for each client? which way is better?
>
> Thank you
> Quinn
>
|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uRUj1fspHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Then, if you go the one DB per client route, you'll need to automate your
> maintenance processes, e.g. backup, reindexing and the like. It's not
> that
> big of a problem; you just need to have it in place.
I'm doing this for 400+ databases in two systems, and the maintenance stuff
is a one-time cost. The ability to move a resource-hoggy client to its own
instance or server is well worth the time invested up front to support them
all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||That's a major advantage - the ability to parse off a large client onto
another server as required. Taking it to the next level, let's say you have
those 400+ DB's on 4 servers. You can then move DB's from one box to
another to level the load. Sur, it's manual, but if you take regular
performance stats, you are then in a good position to figure out who goes
where.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvZbmStpHHA.3512@.TK2MSFTNGP06.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uRUj1fspHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Then, if you go the one DB per client route, you'll need to automate your
> maintenance processes, e.g. backup, reindexing and the like. It's not
> that
> big of a problem; you just need to have it in place.
I'm doing this for 400+ databases in two systems, and the maintenance stuff
is a one-time cost. The ability to move a resource-hoggy client to its own
instance or server is well worth the time invested up front to support them
all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

One database or many database?

I have a web application which collect data from MANY clients. Data
collected is in the same structure, my questions is: to use one database
store all data or create a database for each client? which way is better?
Thank you
QuinnThere is no right or wrong answer here. If you have one huge DB, then
backup and recovery can be problematic. If you have 100's of small DB's,
then it becomes an administrative challenge, though much admin can be
automated/scripted.
There are also security issues. Some clients refuse to commingle their data
with other companies.
Also, if you needed to separate a client into its own DB on another server,
it's not straightforward. Whereas, if you have each client in it's own DB,
it's just a matter of backup and restore.
If the number of clients that you have is small, then likely you can get
away with a single DB.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Quinn" <qjia@.yahoo.com> wrote in message
news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
I have a web application which collect data from MANY clients. Data
collected is in the same structure, my questions is: to use one database
store all data or create a database for each client? which way is better?
Thank you
Quinn|||Thank you Tom.
Right now I don't know how many clients yet, but 500+- may close enough.
Quinn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23G8mcPspHHA.4212@.TK2MSFTNGP04.phx.gbl...
> There is no right or wrong answer here. If you have one huge DB, then
> backup and recovery can be problematic. If you have 100's of small DB's,
> then it becomes an administrative challenge, though much admin can be
> automated/scripted.
> There are also security issues. Some clients refuse to commingle their
> data
> with other companies.
> Also, if you needed to separate a client into its own DB on another
> server,
> it's not straightforward. Whereas, if you have each client in it's own
> DB,
> it's just a matter of backup and restore.
> If the number of clients that you have is small, then likely you can get
> away with a single DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Quinn" <qjia@.yahoo.com> wrote in message
> news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
> I have a web application which collect data from MANY clients. Data
> collected is in the same structure, my questions is: to use one database
> store all data or create a database for each client? which way is better?
>
> Thank you
> Quinn
>|||Then, if you go the one DB per client route, you'll need to automate your
maintenance processes, e.g. backup, reindexing and the like. It's not that
big of a problem; you just need to have it in place.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Quinn" <qjia@.yahoo.com> wrote in message
news:OceHgUspHHA.4772@.TK2MSFTNGP05.phx.gbl...
Thank you Tom.
Right now I don't know how many clients yet, but 500+- may close enough.
Quinn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23G8mcPspHHA.4212@.TK2MSFTNGP04.phx.gbl...
> There is no right or wrong answer here. If you have one huge DB, then
> backup and recovery can be problematic. If you have 100's of small DB's,
> then it becomes an administrative challenge, though much admin can be
> automated/scripted.
> There are also security issues. Some clients refuse to commingle their
> data
> with other companies.
> Also, if you needed to separate a client into its own DB on another
> server,
> it's not straightforward. Whereas, if you have each client in it's own
> DB,
> it's just a matter of backup and restore.
> If the number of clients that you have is small, then likely you can get
> away with a single DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Quinn" <qjia@.yahoo.com> wrote in message
> news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
> I have a web application which collect data from MANY clients. Data
> collected is in the same structure, my questions is: to use one database
> store all data or create a database for each client? which way is better?
>
> Thank you
> Quinn
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uRUj1fspHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Then, if you go the one DB per client route, you'll need to automate your
> maintenance processes, e.g. backup, reindexing and the like. It's not
> that
> big of a problem; you just need to have it in place.
I'm doing this for 400+ databases in two systems, and the maintenance stuff
is a one-time cost. The ability to move a resource-hoggy client to its own
instance or server is well worth the time invested up front to support them
all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||That's a major advantage - the ability to parse off a large client onto
another server as required. Taking it to the next level, let's say you have
those 400+ DB's on 4 servers. You can then move DB's from one box to
another to level the load. Sur, it's manual, but if you take regular
performance stats, you are then in a good position to figure out who goes
where.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:OvZbmStpHHA.3512@.TK2MSFTNGP06.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uRUj1fspHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Then, if you go the one DB per client route, you'll need to automate your
> maintenance processes, e.g. backup, reindexing and the like. It's not
> that
> big of a problem; you just need to have it in place.
I'm doing this for 400+ databases in two systems, and the maintenance stuff
is a one-time cost. The ability to move a resource-hoggy client to its own
instance or server is well worth the time invested up front to support them
all.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

One database or many database?

I have a web application which collect data from MANY clients. Data
collected is in the same structure, my questions is: to use one database
store all data or create a database for each client? which way is better?
Thank you
QuinnThere is no right or wrong answer here. If you have one huge DB, then
backup and recovery can be problematic. If you have 100's of small DB's,
then it becomes an administrative challenge, though much admin can be
automated/scripted.
There are also security issues. Some clients refuse to commingle their data
with other companies.
Also, if you needed to separate a client into its own DB on another server,
it's not straightforward. Whereas, if you have each client in it's own DB,
it's just a matter of backup and restore.
If the number of clients that you have is small, then likely you can get
away with a single DB.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Quinn" <qjia@.yahoo.com> wrote in message
news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
I have a web application which collect data from MANY clients. Data
collected is in the same structure, my questions is: to use one database
store all data or create a database for each client? which way is better?
Thank you
Quinn|||Thank you Tom.
Right now I don't know how many clients yet, but 500+- may close enough.
Quinn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23G8mcPspHHA.4212@.TK2MSFTNGP04.phx.gbl...
> There is no right or wrong answer here. If you have one huge DB, then
> backup and recovery can be problematic. If you have 100's of small DB's,
> then it becomes an administrative challenge, though much admin can be
> automated/scripted.
> There are also security issues. Some clients refuse to commingle their
> data
> with other companies.
> Also, if you needed to separate a client into its own DB on another
> server,
> it's not straightforward. Whereas, if you have each client in it's own
> DB,
> it's just a matter of backup and restore.
> If the number of clients that you have is small, then likely you can get
> away with a single DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Quinn" <qjia@.yahoo.com> wrote in message
> news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
> I have a web application which collect data from MANY clients. Data
> collected is in the same structure, my questions is: to use one database
> store all data or create a database for each client? which way is better?
>
> Thank you
> Quinn
>|||Then, if you go the one DB per client route, you'll need to automate your
maintenance processes, e.g. backup, reindexing and the like. It's not that
big of a problem; you just need to have it in place.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Quinn" <qjia@.yahoo.com> wrote in message
news:OceHgUspHHA.4772@.TK2MSFTNGP05.phx.gbl...
Thank you Tom.
Right now I don't know how many clients yet, but 500+- may close enough.
Quinn
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23G8mcPspHHA.4212@.TK2MSFTNGP04.phx.gbl...
> There is no right or wrong answer here. If you have one huge DB, then
> backup and recovery can be problematic. If you have 100's of small DB's,
> then it becomes an administrative challenge, though much admin can be
> automated/scripted.
> There are also security issues. Some clients refuse to commingle their
> data
> with other companies.
> Also, if you needed to separate a client into its own DB on another
> server,
> it's not straightforward. Whereas, if you have each client in it's own
> DB,
> it's just a matter of backup and restore.
> If the number of clients that you have is small, then likely you can get
> away with a single DB.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Quinn" <qjia@.yahoo.com> wrote in message
> news:OLqpwLspHHA.2596@.TK2MSFTNGP06.phx.gbl...
> I have a web application which collect data from MANY clients. Data
> collected is in the same structure, my questions is: to use one database
> store all data or create a database for each client? which way is better?
>
> Thank you
> Quinn
>|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uRUj1fspHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Then, if you go the one DB per client route, you'll need to automate your
> maintenance processes, e.g. backup, reindexing and the like. It's not
> that
> big of a problem; you just need to have it in place.
I'm doing this for 400+ databases in two systems, and the maintenance stuff
is a one-time cost. The ability to move a resource-hoggy client to its own
instance or server is well worth the time invested up front to support them
all.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||That's a major advantage - the ability to parse off a large client onto
another server as required. Taking it to the next level, let's say you have
those 400+ DB's on 4 servers. You can then move DB's from one box to
another to level the load. Sur, it's manual, but if you take regular
performance stats, you are then in a good position to figure out who goes
where.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OvZbmStpHHA.3512@.TK2MSFTNGP06.phx.gbl...
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uRUj1fspHHA.2156@.TK2MSFTNGP03.phx.gbl...
> Then, if you go the one DB per client route, you'll need to automate your
> maintenance processes, e.g. backup, reindexing and the like. It's not
> that
> big of a problem; you just need to have it in place.
I'm doing this for 400+ databases in two systems, and the maintenance stuff
is a one-time cost. The ability to move a resource-hoggy client to its own
instance or server is well worth the time invested up front to support them
all.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

ON/OFF-LINE WEB DATABASE

Hi,

I have just started learning Visual Web Developer (Express) and SQL Server (Express) but have not been able to figure how to solve this problem: my organization uses a local network for the staff and intends to collect data through the internet, but we lose our connection to the internet for several hours in a day. What considerations should we bear in mind as we design the web application and database? For example, is it wise to reside the database in the web application? How do we synchronize the database whenever we come on-line?

You can configure your SQL Server 2005 Express for replication. Check out this MSDN documentation for reference

Oledb/Data Access Problems

Hi all,

I am going a bit crazy here. To give you my back ground I have done quite some work on SQL server 2000, VFP, VB6 and done quite a few web pages using frontpage. Now I am venturing into aspnet and have what seems to be some data access problems.

I got a win2k professional web server and its running/has been running my frontpage web pages quite nicely either accessing them as //localhost/myweb or www.mydomain/myweb.

Now I made some web pages in Visual Web Developer express to get my "feet wet" So I installed SQL 5.0 express on that machine, .net2.0 and VWD express and vfpoledb. I got 2 web sites designed. One to explore the login feature. Made a default web page and 2 more pages on which I display some SQL grid views from a local db I set up. Site works fine both in the test environment and also on the webserver accessed over the web. Then I added a login page with a login control and configured the site for "web Access", set up 3 test users and pw, no roles and configured the access rules to annonymous denie and all the users allow. Works great out of the VWD environment (view in browser) but when run from the web no PW is ever correct.

2nd problem on 2nd site.

Access to a VFP9.0 table via OLEDB works great again out of the design environment (View in browser) but then run from the wwroot./vfptest directory it comes up with an OLEDB error indicating it cannot find the file

any ideas?

Could you provide more details, exact error message, connection string, etc.?

Thanks

|||

I found the solution to all the problems above

Go to IIS and go to the ASPNET configuration and application tab and set the "Local impersonation" check mark and put in a user that can access the SQL or other files

OLE DB connections

I have .net 2.0 framework and SQL V 8.0 on my web server.

I am trying to create a DSN and I am not given the OLEDB provider for SQL as an option for my list of providers. Is there a download that will add this ? What do I do to get it?

Hi,

If you use Start > Control Panel > Administrative Tools > Data Sources (ODBC) Choose "File DSN" and Choose "Add" and scroll all the way to the bottom, do you not see "Sql Server" ?

|||Alternatively you could check out all these connection strings; http://www.connectionstrings.com/?carrier=sqlserver