performance of single database vs multiple databases

Hi

I am currently working on a portal for an online university. The portal
allows students, teachers, and administrators to access information, and
perform certain tasks based on roles. We are currently developping tools for
managing quizzes, assignments, news. What I realize if we have many courses,
and if each course has it's own database, there are tables which are being
repeated.

Here is an example of two courses databases each with the necessary tables
to run a quiz. All tables except the Courses table use foreign keys to relate
to the parent table.

Course A url -> "http://myservername/courseA"
dbo.Courses (Id, Name, Description, Language, Semester)
dbo.Quizzes(Id, Name, Description, CourseId)
dbo.Questions(Id, Name, Question, Choices, QuizId)

Course B url -> "http://myservername/courseB"
dbo.Courses (Id, Name, Description, Language, Semester)
dbo.Quizzes(Id, Name, Description, CourseId)
dbo.Questions(Id, Name, Question, Choices, QuizId)

So as you can see, Course A and Course B have both the same three tables
(Courses, Quizzes, Questions). Of course some tools will need to talk to both
course databases to generate reports, calculates grades, etc. Now is it
better to use a single huge database with only the three tables (Courses,
Quizzes, Questions), or is it better to keep them seperate? If so then why?
Is performance better with multiple databases, or with a single database?
Also I'm programming in asp.net 2.0 and would like to use typed datasets, and
from what I found typed dataset can only use a single connection string. Is
there a way around that? Also what about caching?

If would appreciate any feedback or advice.
Francis

Jerry
Thu Feb 02 12:51:32 CST 2006

"from what I found typed dataset can only use a single connection
string..." that doesn't make much sense, but then again I work main
with Windows .NET apps - however, I can't image ASP.NET would be so
different Datasets are simply XML structures that can hold data - how
it gets there, or how many different connections are used to load it
make no difference.

I'd guess that you are using Adapters to load the various tables in
your typed dataset. Within an adapter are (up to) four Command objects
(SELECT, INSERT, DELETE, UPDATE). Each command object uses a
connection object, and if you built your adapter using the Adapter
configuration wizard, then it's very likely that each Command object
uses the same connection object.

So, if you want to change which database you use, simply update the
Connection object's Connectionstring property. To make life easier,
.NET2.0 now has a SqlConnectionStringBuilder (something like that),
which breaks down the various parameters of a SQL connection string
into properties. I use this in a new app that connects to multiple
databases in a MS Great Plains system - a real time saver.

I can't honestly answer your other question: single huge DB, or
smaller, multiple DBs. If you are using MSDE or SQL EXpress, then
undoubtedly multiple DBs would benefit you, because those versions of
SQL have limits on the filesize of a single DB. Beyond that, this woud
be a better question on the SQL newsgroups.

FrancisReed
Thu Feb 02 13:31:23 CST 2006

Interesting. Thanks for the info Jerry. I'll definetly post on the sql
newsgroup. By any chance would you have the Url for that newsgroups? Also, I
tried in the past to experiment with updating the connection string property
of a Typed DataSet using the following sample code.

namespace mcQuizDSTableAdapters
{
public partial class ec_upload_submissionsTableAdapter :
upload_submissionsTableAdapter
{
public ec_upload_submissionsTableAdapter()
{
try
{
string courseName =
HttpContext.Current.Session["courseName"].ToString();
SqlConnection myConnection = new SqlConnection();
myConnection.ConnectionString =
ConfigurationManager.ConnectionStrings[courseName].ConnectionString;
this.Connection = myConnection;
}

catch (Exception e)
{
throw e;
}
}
}
}

Essentially I feed ec_upload_submissionsTableAdapter to my ObjectDataSource.
When EnableCaching is false on my ObjectDataSource, no problem, everything
works fine, but when it's true, if I feed the course name dynamically, it
always returns the first course entered. Which makes sense because it's taken
from the cache instead. Furthermore, I really do need caching or else our
site is going to feel a serious performance hit. Would you have any
suggestions?

Francis

Jerry
Thu Feb 02 14:30:22 CST 2006

Within Usenet, head to "microsoft.public.sqlserver", and there you'll
find about 25 different groups that deal with SQL stuff. Myself I
access groups via groups.google.com, which has a simple search function
available.

I'm sorry, I can't help much more beyond that...I don't do much ASP, as
I've never had the chance. However, I'll refer you to another group:
"microsoft.public.dotnet.framework.aspnet " which I'm sure could help
you out. :)

Good luck!

Marina
Thu Feb 02 15:15:44 CST 2006

Databases are design to be able to handle hundreds of thousands and even
millions of rows of data. If you have the right indexes on your tables you
should be fine - I doubt for a university you would even come close to have
amounts of data that even come close to this. If you are using SQL Server,
Oracle, etc, I wouldn't even give it a second thought. And if you are not
using something of that caliber, you should be able to talk the university
into buying it - this isn't an unreasonable expense. This kind of situation
is exactly what databases were designed for.

If you have separate databases you are asking for a maintenance nightmare.
Every time you have a schema change you have to apply it to hundreds of
databases? Every time you need to run a report, you need to join data from
hundreds of databases?

Also, I would ditch the typed data sets. You will learn much more writing
code yourself, and end up with much better code without having the black box
magic of having a ton of code generated for you.

"Francis Reed" <FrancisReed@discussions.microsoft.com> wrote in message
news:EB6ECB67-325F-4BDF-BE2E-79822AEA4E3F@microsoft.com...
> Hi
>
> I am currently working on a portal for an online university. The portal
> allows students, teachers, and administrators to access information, and
> perform certain tasks based on roles. We are currently developping tools
> for
> managing quizzes, assignments, news. What I realize if we have many
> courses,
> and if each course has it's own database, there are tables which are being
> repeated.
>
> Here is an example of two courses databases each with the necessary tables
> to run a quiz. All tables except the Courses table use foreign keys to
> relate
> to the parent table.
>
> Course A url -> "http://myservername/courseA"
> dbo.Courses (Id, Name, Description, Language, Semester)
> dbo.Quizzes(Id, Name, Description, CourseId)
> dbo.Questions(Id, Name, Question, Choices, QuizId)
>
> Course B url -> "http://myservername/courseB"
> dbo.Courses (Id, Name, Description, Language, Semester)
> dbo.Quizzes(Id, Name, Description, CourseId)
> dbo.Questions(Id, Name, Question, Choices, QuizId)
>
> So as you can see, Course A and Course B have both the same three tables
> (Courses, Quizzes, Questions). Of course some tools will need to talk to
> both
> course databases to generate reports, calculates grades, etc. Now is it
> better to use a single huge database with only the three tables (Courses,
> Quizzes, Questions), or is it better to keep them seperate? If so then
> why?
> Is performance better with multiple databases, or with a single database?
> Also I'm programming in asp.net 2.0 and would like to use typed datasets,
> and
> from what I found typed dataset can only use a single connection string.
> Is
> there a way around that? Also what about caching?
>
> If would appreciate any feedback or advice.
> Francis

KerryMoorman
Thu Feb 02 16:08:27 CST 2006

Francis,

I highly encourage you to take a database design class and practice writing
code that interacts with databases that have one-to-many and many-to-many
relationships before undertaking this project.

Kerry Moorman


"Francis Reed" wrote:

> Hi
>
> I am currently working on a portal for an online university. The portal
> allows students, teachers, and administrators to access information, and
> perform certain tasks based on roles. We are currently developping tools for
> managing quizzes, assignments, news. What I realize if we have many courses,
> and if each course has it's own database, there are tables which are being
> repeated.
>
> Here is an example of two courses databases each with the necessary tables
> to run a quiz. All tables except the Courses table use foreign keys to relate
> to the parent table.
>
> Course A url -> "http://myservername/courseA"
> dbo.Courses (Id, Name, Description, Language, Semester)
> dbo.Quizzes(Id, Name, Description, CourseId)
> dbo.Questions(Id, Name, Question, Choices, QuizId)
>
> Course B url -> "http://myservername/courseB"
> dbo.Courses (Id, Name, Description, Language, Semester)
> dbo.Quizzes(Id, Name, Description, CourseId)
> dbo.Questions(Id, Name, Question, Choices, QuizId)
>
> So as you can see, Course A and Course B have both the same three tables
> (Courses, Quizzes, Questions). Of course some tools will need to talk to both
> course databases to generate reports, calculates grades, etc. Now is it
> better to use a single huge database with only the three tables (Courses,
> Quizzes, Questions), or is it better to keep them seperate? If so then why?
> Is performance better with multiple databases, or with a single database?
> Also I'm programming in asp.net 2.0 and would like to use typed datasets, and
> from what I found typed dataset can only use a single connection string. Is
> there a way around that? Also what about caching?
>
> If would appreciate any feedback or advice.
> Francis

BenLam
Fri Feb 03 05:27:15 CST 2006

Marina,

What else would you use instead of ADO.net and datatables, datasets etc...
to access databases in .net? I agree there are performance issues but I
didn't know any other way of doing it, so i'd be very interested in finding
alternatives to ado.net

thanks in advance

"Marina Levit [MVP]" wrote:

> Databases are design to be able to handle hundreds of thousands and even
> millions of rows of data. If you have the right indexes on your tables you
> should be fine - I doubt for a university you would even come close to have
> amounts of data that even come close to this. If you are using SQL Server,
> Oracle, etc, I wouldn't even give it a second thought. And if you are not
> using something of that caliber, you should be able to talk the university
> into buying it - this isn't an unreasonable expense. This kind of situation
> is exactly what databases were designed for.
>
> If you have separate databases you are asking for a maintenance nightmare.
> Every time you have a schema change you have to apply it to hundreds of
> databases? Every time you need to run a report, you need to join data from
> hundreds of databases?
>
> Also, I would ditch the typed data sets. You will learn much more writing
> code yourself, and end up with much better code without having the black box
> magic of having a ton of code generated for you.
>
> "Francis Reed" <FrancisReed@discussions.microsoft.com> wrote in message
> news:EB6ECB67-325F-4BDF-BE2E-79822AEA4E3F@microsoft.com...
> > Hi
> >
> > I am currently working on a portal for an online university. The portal
> > allows students, teachers, and administrators to access information, and
> > perform certain tasks based on roles. We are currently developping tools
> > for
> > managing quizzes, assignments, news. What I realize if we have many
> > courses,
> > and if each course has it's own database, there are tables which are being
> > repeated.
> >
> > Here is an example of two courses databases each with the necessary tables
> > to run a quiz. All tables except the Courses table use foreign keys to
> > relate
> > to the parent table.
> >
> > Course A url -> "http://myservername/courseA"
> > dbo.Courses (Id, Name, Description, Language, Semester)
> > dbo.Quizzes(Id, Name, Description, CourseId)
> > dbo.Questions(Id, Name, Question, Choices, QuizId)
> >
> > Course B url -> "http://myservername/courseB"
> > dbo.Courses (Id, Name, Description, Language, Semester)
> > dbo.Quizzes(Id, Name, Description, CourseId)
> > dbo.Questions(Id, Name, Question, Choices, QuizId)
> >
> > So as you can see, Course A and Course B have both the same three tables
> > (Courses, Quizzes, Questions). Of course some tools will need to talk to
> > both
> > course databases to generate reports, calculates grades, etc. Now is it
> > better to use a single huge database with only the three tables (Courses,
> > Quizzes, Questions), or is it better to keep them seperate? If so then
> > why?
> > Is performance better with multiple databases, or with a single database?
> > Also I'm programming in asp.net 2.0 and would like to use typed datasets,
> > and
> > from what I found typed dataset can only use a single connection string.
> > Is
> > there a way around that? Also what about caching?
> >
> > If would appreciate any feedback or advice.
> > Francis
>
>
>

Marina
Fri Feb 03 08:23:43 CST 2006

Alternatives? Sorry, I don't think I follow. ADO.NET is great, and so are
datatables, datasets, etc.

I am personally against using *typed datasets*, which are the things you can
visual studio generate for you, along with a ton of ado.net code. Not so
much for performance issues, but for maintainence and having the code
magically written somewhere behind the scenes. I think using the wizards is
fine for starting out and learning, but I wouldn't use them in production
software.

"Ben Lam" <BenLam@discussions.microsoft.com> wrote in message
news:F1EC5C81-AC2C-40AB-AF4A-9E793F03962F@microsoft.com...
> Marina,
>
> What else would you use instead of ADO.net and datatables, datasets etc...
> to access databases in .net? I agree there are performance issues but I
> didn't know any other way of doing it, so i'd be very interested in
> finding
> alternatives to ado.net
>
> thanks in advance
>
> "Marina Levit [MVP]" wrote:
>
>> Databases are design to be able to handle hundreds of thousands and even
>> millions of rows of data. If you have the right indexes on your tables
>> you
>> should be fine - I doubt for a university you would even come close to
>> have
>> amounts of data that even come close to this. If you are using SQL
>> Server,
>> Oracle, etc, I wouldn't even give it a second thought. And if you are
>> not
>> using something of that caliber, you should be able to talk the
>> university
>> into buying it - this isn't an unreasonable expense. This kind of
>> situation
>> is exactly what databases were designed for.
>>
>> If you have separate databases you are asking for a maintenance
>> nightmare.
>> Every time you have a schema change you have to apply it to hundreds of
>> databases? Every time you need to run a report, you need to join data
>> from
>> hundreds of databases?
>>
>> Also, I would ditch the typed data sets. You will learn much more
>> writing
>> code yourself, and end up with much better code without having the black
>> box
>> magic of having a ton of code generated for you.
>>
>> "Francis Reed" <FrancisReed@discussions.microsoft.com> wrote in message
>> news:EB6ECB67-325F-4BDF-BE2E-79822AEA4E3F@microsoft.com...
>> > Hi
>> >
>> > I am currently working on a portal for an online university. The portal
>> > allows students, teachers, and administrators to access information,
>> > and
>> > perform certain tasks based on roles. We are currently developping
>> > tools
>> > for
>> > managing quizzes, assignments, news. What I realize if we have many
>> > courses,
>> > and if each course has it's own database, there are tables which are
>> > being
>> > repeated.
>> >
>> > Here is an example of two courses databases each with the necessary
>> > tables
>> > to run a quiz. All tables except the Courses table use foreign keys to
>> > relate
>> > to the parent table.
>> >
>> > Course A url -> "http://myservername/courseA"
>> > dbo.Courses (Id, Name, Description, Language, Semester)
>> > dbo.Quizzes(Id, Name, Description, CourseId)
>> > dbo.Questions(Id, Name, Question, Choices, QuizId)
>> >
>> > Course B url -> "http://myservername/courseB"
>> > dbo.Courses (Id, Name, Description, Language, Semester)
>> > dbo.Quizzes(Id, Name, Description, CourseId)
>> > dbo.Questions(Id, Name, Question, Choices, QuizId)
>> >
>> > So as you can see, Course A and Course B have both the same three
>> > tables
>> > (Courses, Quizzes, Questions). Of course some tools will need to talk
>> > to
>> > both
>> > course databases to generate reports, calculates grades, etc. Now is it
>> > better to use a single huge database with only the three tables
>> > (Courses,
>> > Quizzes, Questions), or is it better to keep them seperate? If so then
>> > why?
>> > Is performance better with multiple databases, or with a single
>> > database?
>> > Also I'm programming in asp.net 2.0 and would like to use typed
>> > datasets,
>> > and
>> > from what I found typed dataset can only use a single connection
>> > string.
>> > Is
>> > there a way around that? Also what about caching?
>> >
>> > If would appreciate any feedback or advice.
>> > Francis
>>
>>
>>

FrancisReed
Fri Feb 03 09:14:14 CST 2006

Hi Marina

Thank you for the advice Marina. It's not a question of learning database
design here, because I have taken many classes on that subject in the past. I
wanted to ask if there is a way to create a Typed DataSet myself in C#
without using a wizard. Because it's very handy to have dot syntax when you
write your datasets, also the strick clr typing is great. For example, the
Name field in my Quizzes table returns a string because it is part of a typed
dataset.

string question = Courses.Quizzes[i].Name;

As opposed to writing and casting the object to a string

string question = Courses.Tables["Quizzes"].Rows[i]["Name"].ToString();

So, is there a way to achieve this kind of result without using a wizard?

Francis

"Marina Levit [MVP]" wrote:

> Also, I would ditch the typed data sets. You will learn much more writing
> code yourself, and end up with much better code without having the black box
> magic of having a ton of code generated for you.

Marina
Fri Feb 03 09:37:04 CST 2006

I understand the benefits of using typed datasets, but I think the
disadvantages outweigh the advantages by far. It's a personal choice though,
I was just giving my opinion.

I suppose you could do all the work the wizard does manually, but I imagine
that would be quite an effort. If you are attached to the idea of using
typed datasets, you may as well take advantage of what visual studio has.

"Francis Reed" <FrancisReed@discussions.microsoft.com> wrote in message
news:3DE63730-8DC2-4B68-9472-F65A046EEFCB@microsoft.com...
> Hi Marina
>
> Thank you for the advice Marina. It's not a question of learning database
> design here, because I have taken many classes on that subject in the
> past. I
> wanted to ask if there is a way to create a Typed DataSet myself in C#
> without using a wizard. Because it's very handy to have dot syntax when
> you
> write your datasets, also the strick clr typing is great. For example, the
> Name field in my Quizzes table returns a string because it is part of a
> typed
> dataset.
>
> string question = Courses.Quizzes[i].Name;
>
> As opposed to writing and casting the object to a string
>
> string question = Courses.Tables["Quizzes"].Rows[i]["Name"].ToString();
>
> So, is there a way to achieve this kind of result without using a wizard?
>
> Francis
>
> "Marina Levit [MVP]" wrote:
>
>> Also, I would ditch the typed data sets. You will learn much more
>> writing
>> code yourself, and end up with much better code without having the black
>> box
>> magic of having a ton of code generated for you.

Ned
Mon Feb 06 15:35:30 CST 2006

"Marina Levit [MVP]" wrote:

>Databases are design to be able to handle hundreds of thousands and even
> millions of rows of data.

What about concurrent user connections? is there any benefit to be had from
splitting the data amongst many dbs to reduce stress from connections? Or are
100 connections to an SQL server instance with 1 db the same as 10
connections to an SQL server instance with 10 dbs? I am curious about this as
user load is perhaps a greater concern then the number of rows for our
application.

Thanks!

ned

William
Mon Feb 06 17:30:50 CST 2006

This is a multi-part message in MIME format.

------=_NextPart_000_000C_01C62B32.4F78BDF0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable

We've seen SQL Server handle thousands of users. Of course any =
configuration depends a lot on what SS (or any DBMS) is being asked to =
do. Other factors include (but are not limited to):
a.. Development complexity
b.. Administration expense
c.. Complexity of query
d.. Volume of rows moved to client
e.. Network performance and loading
and a dozen dozen other factors. Creating multiple databases to share =
the load tends to overlook economies of scale you get when common =
procedures or data pages are cached.=20
Sure, there are configurations where the same database is replicated =
across a dozen servers. Clustering and server farms are a good way to =
permit lots of users at the data.=20
How many users are you expecting? Is this an outward-facing web site or =
a client/server rig on a corporate LAN?

--=20
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no =
rights.
__________________________________

"Ned Schwartz" <Ned Schwartz@discussions.microsoft.com> wrote in message =
news:B30E57B6-950B-4DB1-AB31-7371D4346472@microsoft.com...
> "Marina Levit [MVP]" wrote:
>=20
>>Databases are design to be able to handle hundreds of thousands and =
even =20
>> millions of rows of data.=20
>=20
> What about concurrent user connections? is there any benefit to be had =
from=20
> splitting the data amongst many dbs to reduce stress from connections? =
Or are=20
> 100 connections to an SQL server instance with 1 db the same as 10=20
> connections to an SQL server instance with 10 dbs? I am curious about =
this as=20
> user load is perhaps a greater concern then the number of rows for our =

> application.
>=20
> Thanks!=20
>=20
> ned
>
------=_NextPart_000_000C_01C62B32.4F78BDF0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable

=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=3D"Comic Sans MS" color=3D#0000ff size=3D2>We've seen =
SQL Server=20
handle thousands of users. Of course any configuration depends a lot on =
what SS=20
(or any DBMS) is being asked to do. Other factors include (but are not =
limited=20
to):</FONT></DIV>
<UL>
<LI><FONT face=3D"Comic Sans MS" color=3D#0000ff size=3D2>Development=20
complexity</FONT></LI>
<LI><FONT face=3D"Comic Sans MS" color=3D#0000ff =
size=3D2>Administration=20
expense</FONT></LI>
<LI><FONT face=3D"Comic Sans MS" color=3D#0000ff size=3D2>Complexity =
of=20
query</FONT></LI>
<LI><FONT face=3D"Comic Sans MS" color=3D#0000ff size=3D2>Volume of =
rows moved to=20
client</FONT></LI>
<LI><FONT face=3D"Comic Sans MS" color=3D#0000ff size=3D2>Network =
performance and=20
loading</FONT></LI></UL>
<DIV><FONT face=3D"Comic Sans MS" color=3D#0000ff size=3D2>and a dozen =
dozen other=20
factors. Creating multiple databases to share the load tends to overlook =

economies of scale you get when common procedures or data pages are =
cached.=20
</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" color=3D#0000ff size=3D2>Sure, there =
are=20
configurations where the same database is replicated across a dozen =
servers.=20
Clustering and server farms are a good way to permit lots of users at =
the data.=20
</FONT></DIV>
<DIV><FONT face=3D"Comic Sans MS" color=3D#0000ff size=3D2>How many =
users are you=20
expecting? Is this an outward-facing web site or a client/server rig on =
a=20
corporate LAN?</FONT></DIV>
<DIV><BR><FONT face=3D"Comic Sans MS" color=3D#0000ff size=3D2>--=20
<BR>____________________________________<BR>William (Bill) =
Vaughn<BR>Author,=20
Mentor, Consultant<BR>Microsoft MVP<BR>INETA Speaker<BR></FONT><A=20
href=3D"http://www.betav.com/blog/billva"><FONT face=3D"Comic Sans MS" =
color=3D#0000ff=20
size=3D2>www.betav.com/blog/billva</FONT></A><BR><A=20
href=3D"http://www.betav.com"><FONT face=3D"Comic Sans MS" =
color=3D#0000ff=20
size=3D2>www.betav.com</FONT></A><BR><FONT face=3D"Comic Sans MS" =
color=3D#0000ff=20
size=3D2>Please reply only to the newsgroup so that others can =
benefit.<BR>This=20
posting is provided "AS IS" with no warranties, and confers no=20
rights.<BR>__________________________________<BR></DIV></FONT>
<DIV><FONT face=3D"Comic Sans MS" color=3D#0000ff size=3D2>"Ned =
Schwartz" &lt;Ned=20
</FONT><A href=3D"mailto:Schwartz@discussions.microsoft.com"><FONT=20
face=3D"Comic Sans MS" =
size=3D2>Schwartz@discussions.microsoft.com</FONT></A><FONT=20
face=3D"Comic Sans MS" color=3D#0000ff size=3D2>&gt; wrote in message =
</FONT><A=20
href=3D"news:B30E57B6-950B-4DB1-AB31-7371D4346472@microsoft.com"><FONT=20
face=3D"Comic Sans MS"=20
size=3D2>news:B30E57B6-950B-4DB1-AB31-7371D4346472@microsoft.com</FONT></=
A><FONT=20
face=3D"Comic Sans MS" color=3D#0000ff size=3D2>...</FONT></DIV><FONT=20
face=3D"Comic Sans MS" color=3D#0000ff size=3D2>&gt; "Marina Levit =
[MVP]"=20
wrote:<BR>&gt; <BR>&gt;&gt;Databases are design to be able to handle =
hundreds of=20
thousands and even&nbsp; <BR>&gt;&gt; millions of rows of data. <BR>&gt; =

<BR>&gt; What about concurrent user connections? is there any benefit to =
be had=20
from <BR>&gt; splitting the data amongst many dbs to reduce stress from=20
connections? Or are <BR>&gt; 100 connections to an SQL server instance =
with 1 db=20
the same as 10 <BR>&gt; connections to an SQL server instance with 10 =
dbs? I am=20
curious about this as <BR>&gt; user load is perhaps a greater concern =
then the=20
number of rows for our <BR>&gt; application.<BR>&gt; <BR>&gt; Thanks! =
<BR>&gt;=20
<BR>&gt; ned<BR>&gt;</FONT></BODY></HTML>

------=_NextPart_000_000C_01C62B32.4F78BDF0--

NedSchwartz
Tue Feb 07 13:13:28 CST 2006

Hi, thanks for the answer - definitely gives me something to chew on.

We are an "outward facing" web site with about 4000 daily users currently
and a reasonable projection of about 2000 new users a year. We have an
existing system that has built up awkwardly in an ad hoc fashion over the
last few years and are now in the early planning stages of a re-design to
move to a more robust, scalable and modular design.

Honestly, we are still modeling the data and so are only starting to think
about how and if the data should be structured in different databases.

Thanks again for the input!

ned


"William (Bill) Vaughn" wrote:

> We've seen SQL Server handle thousands of users. Of course any configuration depends a lot on what SS (or any DBMS) is being asked to do. Other factors include (but are not limited to):
> a.. Development complexity
> b.. Administration expense
> c.. Complexity of query
> d.. Volume of rows moved to client
> e.. Network performance and loading
> and a dozen dozen other factors. Creating multiple databases to share the load tends to overlook economies of scale you get when common procedures or data pages are cached.
> Sure, there are configurations where the same database is replicated across a dozen servers. Clustering and server farms are a good way to permit lots of users at the data.
> How many users are you expecting? Is this an outward-facing web site or a client/server rig on a corporate LAN?
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> INETA Speaker
> www.betav.com/blog/billva
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> __________________________________
>
> "Ned Schwartz" <Ned Schwartz@discussions.microsoft.com> wrote in message news:B30E57B6-950B-4DB1-AB31-7371D4346472@microsoft.com...
> > "Marina Levit [MVP]" wrote:
> >
> >>Databases are design to be able to handle hundreds of thousands and even
> >> millions of rows of data.
> >
> > What about concurrent user connections? is there any benefit to be had from
> > splitting the data amongst many dbs to reduce stress from connections? Or are
> > 100 connections to an SQL server instance with 1 db the same as 10
> > connections to an SQL server instance with 10 dbs? I am curious about this as
> > user load is perhaps a greater concern then the number of rows for our
> > application.
> >
> > Thanks!
> >
> > ned
> >