One large database or a few smaller databases?

Does it make sense to break up data into a few smaller (identical)
databases to keep from having hundreds of millions of rows in a few
tables in one single database?
I ask because I've got a website that's pumping about 500k new records
into 3 or 4 tables per day. In several months I'll probably be near 400
or 500 million rows in some of them.
Users to the website query the data, and so far, it's working well
(with only a few million rows in each). My indexes look good and my
queries only join 2 or 3 tables at most, but I am mostly doing
aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
rows at a time.
My app is set up to handle the data being in separate identical
databases, but I'd like to keep it all in the same place to keep things
more simple (for backups & upgrades, etc..)
But my fear is when I start to reach half a billion rows my query times
are going to suck, even if I cram more memory into my database server
(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
Anyone have any experience with this much data?
Thanks!!
You should not split up your solution into more than one database. A
good approach would be to use the analysis services from Sql Server as
you are requireing a lot of aggregation data which could be
precalculated. Therefore these cubes will provide a much feaster
response than quering just an OLTP database.
HTH, Jens Suessmeyer.
|||Jens, thanks for the quick reply. I'm using SQL 2005 standard... Does
that I mean need to upgrade to Enterprise?
Forgive my lack of knowledge on AS and data cubes, but are they kept in
real-time? Ideally the queries would be doing in "near-time" to when
the data has been inserted.
Thanks for your help.
|||No, AS is already included in SQL Server 2k5, but has to be instaleld
if not.
You sure can design the cube that it can fallback on non aggregated
realtime data , but perhaps the Analysis Services newsgroup can help
you further Within this they should know was is possible in your case.
HTH, Jens Suessmeyer.
|||On 28 Jan 2006 11:29:29 -0800, Jeff Turner wrote:

>Does it make sense to break up data into a few smaller (identical)
>databases to keep from having hundreds of millions of rows in a few
>tables in one single database?
>I ask because I've got a website that's pumping about 500k new records
>into 3 or 4 tables per day. In several months I'll probably be near 400
>or 500 million rows in some of them.
>Users to the website query the data, and so far, it's working well
>(with only a few million rows in each). My indexes look good and my
>queries only join 2 or 3 tables at most, but I am mostly doing
>aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
>rows at a time.
>My app is set up to handle the data being in separate identical
>databases, but I'd like to keep it all in the same place to keep things
>more simple (for backups & upgrades, etc..)
>But my fear is when I start to reach half a billion rows my query times
>are going to suck, even if I cram more memory into my database server
>(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
>Anyone have any experience with this much data?
>Thanks!!
Hi Jeff,
I agree with Jens - keep it in one database.
In addition to Jens' suggestion to use analysis server, here are some
other ideas that might help you gain speed as the amount of data grows.
1. Use indexed views. If your queries are usually for aggregates, they
could prove to be an excellent way to speed up the process, since they
are precalculated. Because SQL Server has to modify each indexed view as
the base data changes, indexed views do cause a slowdown for data
modifications, though. Don't use indexed views if you need millisecond
performance on insert and update operations.
2. Consider the use of partitioned views (SQL Server 2000) or table
partitioning (SQL Server 2005).
Hugo Kornelis, SQL Server MVP
|||Hi Jeff,
Some extra thoughts:
1. The optimizer is going to do better work for you if it's in one database.
2. Backups/restores become much more complicated if you have to keep
multiple databases in sync. Consider what you might have to do to restore to
a point in time. There are methods but it just starts to complicate things.
HTH,
Greg
"Jeff Turner" <zigjst@.gmail.com> wrote in message
news:1138476569.362520.240210@.f14g2000cwb.googlegr oups.com...
> Does it make sense to break up data into a few smaller (identical)
> databases to keep from having hundreds of millions of rows in a few
> tables in one single database?
> I ask because I've got a website that's pumping about 500k new records
> into 3 or 4 tables per day. In several months I'll probably be near 400
> or 500 million rows in some of them.
> Users to the website query the data, and so far, it's working well
> (with only a few million rows in each). My indexes look good and my
> queries only join 2 or 3 tables at most, but I am mostly doing
> aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
> rows at a time.
> My app is set up to handle the data being in separate identical
> databases, but I'd like to keep it all in the same place to keep things
> more simple (for backups & upgrades, etc..)
> But my fear is when I start to reach half a billion rows my query times
> are going to suck, even if I cram more memory into my database server
> (dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
> Anyone have any experience with this much data?
> Thanks!!
>
|||Thanks to everyone for replying.
One thing I should point out is the data in these tables is broken down
by users to the website.
For example, if I did split this up by database, I could have one
database that holds all of the records for users with last names of
A-G. They would never have a need to query against the data in the
other databases.
So the practical minded side of me says, why bother running queries for
an A-G user in tables that hold everyone's data, when I could
physically have it narrowed down already.
Does that make sense? Or is SQL just smart & efficient enough with
indexing that the performance gain would be negligable compared to my
backup/maintenance costs.
Thanks again!

One large database or a few smaller databases?

Does it make sense to break up data into a few smaller (identical)
databases to keep from having hundreds of millions of rows in a few
tables in one single database?
I ask because I've got a website that's pumping about 500k new records
into 3 or 4 tables per day. In several months I'll probably be near 400
or 500 million rows in some of them.
Users to the website query the data, and so far, it's working well
(with only a few million rows in each). My indexes look good and my
queries only join 2 or 3 tables at most, but I am mostly doing
aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
rows at a time.
My app is set up to handle the data being in separate identical
databases, but I'd like to keep it all in the same place to keep things
more simple (for backups & upgrades, etc..)
But my fear is when I start to reach half a billion rows my query times
are going to suck, even if I cram more memory into my database server
(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
Anyone have any experience with this much data?
Thanks!!You should not split up your solution into more than one database. A
good approach would be to use the analysis services from Sql Server as
you are requireing a lot of aggregation data which could be
precalculated. Therefore these cubes will provide a much feaster
response than quering just an OLTP database.
HTH, Jens Suessmeyer.|||Jens, thanks for the quick reply. I'm using SQL 2005 standard... Does
that I mean need to upgrade to Enterprise?
Forgive my lack of knowledge on AS and data cubes, but are they kept in
real-time? Ideally the queries would be doing in "near-time" to when
the data has been inserted.
Thanks for your help.|||No, AS is already included in SQL Server 2k5, but has to be instaleld
if not.
You sure can design the cube that it can fallback on non aggregated
realtime data , but perhaps the Analysis Services newsgroup can help
you further Within this they should know was is possible in your case.
HTH, Jens Suessmeyer.|||On 28 Jan 2006 11:29:29 -0800, Jeff Turner wrote:

>Does it make sense to break up data into a few smaller (identical)
>databases to keep from having hundreds of millions of rows in a few
>tables in one single database?
>I ask because I've got a website that's pumping about 500k new records
>into 3 or 4 tables per day. In several months I'll probably be near 400
>or 500 million rows in some of them.
>Users to the website query the data, and so far, it's working well
>(with only a few million rows in each). My indexes look good and my
>queries only join 2 or 3 tables at most, but I am mostly doing
>aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
>rows at a time.
>My app is set up to handle the data being in separate identical
>databases, but I'd like to keep it all in the same place to keep things
>more simple (for backups & upgrades, etc..)
>But my fear is when I start to reach half a billion rows my query times
>are going to suck, even if I cram more memory into my database server
>(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
>Anyone have any experience with this much data?
>Thanks!!
Hi Jeff,
I agree with Jens - keep it in one database.
In addition to Jens' suggestion to use analysis server, here are some
other ideas that might help you gain speed as the amount of data grows.
1. Use indexed views. If your queries are usually for aggregates, they
could prove to be an excellent way to speed up the process, since they
are precalculated. Because SQL Server has to modify each indexed view as
the base data changes, indexed views do cause a slowdown for data
modifications, though. Don't use indexed views if you need millisecond
performance on insert and update operations.
2. Consider the use of partitioned views (SQL Server 2000) or table
partitioning (SQL Server 2005).
Hugo Kornelis, SQL Server MVP|||Hi Jeff,
Some extra thoughts:
1. The optimizer is going to do better work for you if it's in one database.
2. Backups/restores become much more complicated if you have to keep
multiple databases in sync. Consider what you might have to do to restore to
a point in time. There are methods but it just starts to complicate things.
HTH,
Greg
"Jeff Turner" <zigjst@.gmail.com> wrote in message
news:1138476569.362520.240210@.f14g2000cwb.googlegroups.com...
> Does it make sense to break up data into a few smaller (identical)
> databases to keep from having hundreds of millions of rows in a few
> tables in one single database?
> I ask because I've got a website that's pumping about 500k new records
> into 3 or 4 tables per day. In several months I'll probably be near 400
> or 500 million rows in some of them.
> Users to the website query the data, and so far, it's working well
> (with only a few million rows in each). My indexes look good and my
> queries only join 2 or 3 tables at most, but I am mostly doing
> aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
> rows at a time.
> My app is set up to handle the data being in separate identical
> databases, but I'd like to keep it all in the same place to keep things
> more simple (for backups & upgrades, etc..)
> But my fear is when I start to reach half a billion rows my query times
> are going to suck, even if I cram more memory into my database server
> (dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
> Anyone have any experience with this much data?
> Thanks!!
>|||Thanks to everyone for replying.
One thing I should point out is the data in these tables is broken down
by users to the website.
For example, if I did split this up by database, I could have one
database that holds all of the records for users with last names of
A-G. They would never have a need to query against the data in the
other databases.
So the practical minded side of me says, why bother running queries for
an A-G user in tables that hold everyone's data, when I could
physically have it narrowed down already.
Does that make sense? Or is SQL just smart & efficient enough with
indexing that the performance gain would be negligable compared to my
backup/maintenance costs.
Thanks again!

One large database or a few smaller databases?

Does it make sense to break up data into a few smaller (identical)
databases to keep from having hundreds of millions of rows in a few
tables in one single database?
I ask because I've got a website that's pumping about 500k new records
into 3 or 4 tables per day. In several months I'll probably be near 400
or 500 million rows in some of them.
Users to the website query the data, and so far, it's working well
(with only a few million rows in each). My indexes look good and my
queries only join 2 or 3 tables at most, but I am mostly doing
aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
rows at a time.
My app is set up to handle the data being in separate identical
databases, but I'd like to keep it all in the same place to keep things
more simple (for backups & upgrades, etc..)
But my fear is when I start to reach half a billion rows my query times
are going to suck, even if I cram more memory into my database server
(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
Anyone have any experience with this much data?
Thanks!!You should not split up your solution into more than one database. A
good approach would be to use the analysis services from Sql Server as
you are requireing a lot of aggregation data which could be
precalculated. Therefore these cubes will provide a much feaster
response than quering just an OLTP database.
HTH, Jens Suessmeyer.|||Jens, thanks for the quick reply. I'm using SQL 2005 standard... Does
that I mean need to upgrade to Enterprise?
Forgive my lack of knowledge on AS and data cubes, but are they kept in
real-time? Ideally the queries would be doing in "near-time" to when
the data has been inserted.
Thanks for your help.|||No, AS is already included in SQL Server 2k5, but has to be instaleld
if not.
You sure can design the cube that it can fallback on non aggregated
realtime data , but perhaps the Analysis Services newsgroup can help
you further Within this they should know was is possible in your case.
HTH, Jens Suessmeyer.|||On 28 Jan 2006 11:29:29 -0800, Jeff Turner wrote:
>Does it make sense to break up data into a few smaller (identical)
>databases to keep from having hundreds of millions of rows in a few
>tables in one single database?
>I ask because I've got a website that's pumping about 500k new records
>into 3 or 4 tables per day. In several months I'll probably be near 400
>or 500 million rows in some of them.
>Users to the website query the data, and so far, it's working well
>(with only a few million rows in each). My indexes look good and my
>queries only join 2 or 3 tables at most, but I am mostly doing
>aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
>rows at a time.
>My app is set up to handle the data being in separate identical
>databases, but I'd like to keep it all in the same place to keep things
>more simple (for backups & upgrades, etc..)
>But my fear is when I start to reach half a billion rows my query times
>are going to suck, even if I cram more memory into my database server
>(dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
>Anyone have any experience with this much data?
>Thanks!!
Hi Jeff,
I agree with Jens - keep it in one database.
In addition to Jens' suggestion to use analysis server, here are some
other ideas that might help you gain speed as the amount of data grows.
1. Use indexed views. If your queries are usually for aggregates, they
could prove to be an excellent way to speed up the process, since they
are precalculated. Because SQL Server has to modify each indexed view as
the base data changes, indexed views do cause a slowdown for data
modifications, though. Don't use indexed views if you need millisecond
performance on insert and update operations.
2. Consider the use of partitioned views (SQL Server 2000) or table
partitioning (SQL Server 2005).
--
Hugo Kornelis, SQL Server MVP|||Hi Jeff,
Some extra thoughts:
1. The optimizer is going to do better work for you if it's in one database.
2. Backups/restores become much more complicated if you have to keep
multiple databases in sync. Consider what you might have to do to restore to
a point in time. There are methods but it just starts to complicate things.
HTH,
Greg
"Jeff Turner" <zigjst@.gmail.com> wrote in message
news:1138476569.362520.240210@.f14g2000cwb.googlegroups.com...
> Does it make sense to break up data into a few smaller (identical)
> databases to keep from having hundreds of millions of rows in a few
> tables in one single database?
> I ask because I've got a website that's pumping about 500k new records
> into 3 or 4 tables per day. In several months I'll probably be near 400
> or 500 million rows in some of them.
> Users to the website query the data, and so far, it's working well
> (with only a few million rows in each). My indexes look good and my
> queries only join 2 or 3 tables at most, but I am mostly doing
> aggregations on lots of data (AVGs, SUMs, etc.) so I only return a few
> rows at a time.
> My app is set up to handle the data being in separate identical
> databases, but I'd like to keep it all in the same place to keep things
> more simple (for backups & upgrades, etc..)
> But my fear is when I start to reach half a billion rows my query times
> are going to suck, even if I cram more memory into my database server
> (dual xeon 3.2 with 2GB of memory, room to grow to 8GB).
> Anyone have any experience with this much data?
> Thanks!!
>|||Thanks to everyone for replying.
One thing I should point out is the data in these tables is broken down
by users to the website.
For example, if I did split this up by database, I could have one
database that holds all of the records for users with last names of
A-G. They would never have a need to query against the data in the
other databases.
So the practical minded side of me says, why bother running queries for
an A-G user in tables that hold everyone's data, when I could
physically have it narrowed down already.
Does that make sense? Or is SQL just smart & efficient enough with
indexing that the performance gain would be negligable compared to my
backup/maintenance costs.
Thanks again!

One instance or two instances

Hi,

If I have two applications which have several databases for each application, do you create one instance for two applications or you create one instance for each application? Which way is better on pricing, performance, maintanence etc?

Thanks for your input

It does not matter as far as pricing goes. You can install multiple instances on the same server once you have a single license.

If you have multiple instances on the same server, you need to think about how much memory you allocate to each instance (by setting the max memory option). In most cases you would be better off (especially from a performance and maint perspective) to only have one instance on the machine, rather than one instance per application.

Multiple instances can be more secure (from each other), and they can be at different SP levels.

|||

thanks for your help.

Where I can change the parameter for the instance on memory limitation etc?

Thanks

|||

In SQL Server 2005, you can use SQL Server Management Studio (SSMS). In Object Explorer, select the instance, right-click and select Properties. Then select Memory, and set the value for the Max server memory. You want to leave some memory for the OS.

Please mark this question as answered if this was helpful. Thanks!

One Database vs. Multiple Databases

I need to design a system which represents multiple "projects" in SQL
Server. Each project has the same data model, but is independent of all
others. My inclination is to use one database to store all projects.
Looking at the numbers involved, however, I wonder if I would get
better performance by storing each project in its own database.

Suppose I have 50 projects, each with two users and 10,000 rows; it
seems to me I'd rather have 50 x 2 users working in a table with 10,000
rows than 1 x 100 users working in a table with 500,000 rows.

On the other hand, the single database approach seems more elegant from
a design perspective. I wouldn't be creating multiple copies of an
identical data model, and I wouldn't be creating new databases as a
business procedure, every time a new project is required.

Here are my questions:
1. For the scenario described above, am I correct to assume I will get
better performance by using multiple databases, or does SQL Server have
some clever way of achieving the same performance in a single database?
2. Is the multiple database approach common? If anyone has tried it,
please tell me about how it works in practice.

-TCTC wrote:
> I need to design a system which represents multiple "projects" in SQL
> Server. Each project has the same data model, but is independent of all
> others. My inclination is to use one database to store all projects.
> Looking at the numbers involved, however, I wonder if I would get
> better performance by storing each project in its own database.
> Suppose I have 50 projects, each with two users and 10,000 rows; it
> seems to me I'd rather have 50 x 2 users working in a table with 10,000
> rows than 1 x 100 users working in a table with 500,000 rows.
> On the other hand, the single database approach seems more elegant from
> a design perspective. I wouldn't be creating multiple copies of an
> identical data model, and I wouldn't be creating new databases as a
> business procedure, every time a new project is required.
> Here are my questions:
> 1. For the scenario described above, am I correct to assume I will get
> better performance by using multiple databases, or does SQL Server have
> some clever way of achieving the same performance in a single database?
> 2. Is the multiple database approach common? If anyone has tried it,
> please tell me about how it works in practice.
>
> -TC

1. Not unless your implementation is very bad indeed. 100 users and
500,000 rows is a small database by most standards.

2. Sometimes. Partitioning a database can make sense for administrative
and support reasons or as part of a solution where data is distributed
over multiple servers. But without other changes, partitioning a
database isn't likely to achieve much if anything in terms of
performance. Given the potential complexity of supporting that kind of
solution there are certainly much easier and more effective ways to
optimise performance.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||TC wrote:
> I need to design a system which represents multiple "projects" in SQL
> Server. Each project has the same data model, but is independent of all
> others. My inclination is to use one database to store all projects.
> Looking at the numbers involved, however, I wonder if I would get
> better performance by storing each project in its own database.
> Suppose I have 50 projects, each with two users and 10,000 rows; it
> seems to me I'd rather have 50 x 2 users working in a table with 10,000
> rows than 1 x 100 users working in a table with 500,000 rows.
> On the other hand, the single database approach seems more elegant from
> a design perspective. I wouldn't be creating multiple copies of an
> identical data model, and I wouldn't be creating new databases as a
> business procedure, every time a new project is required.
> Here are my questions:
> 1. For the scenario described above, am I correct to assume I will get
> better performance by using multiple databases, or does SQL Server have
> some clever way of achieving the same performance in a single database?
> 2. Is the multiple database approach common? If anyone has tried it,
> please tell me about how it works in practice.
>
> -TC

I would go with 1 database per projet (so multiple databases):
- if your data model change, you will be able to migrate only projets
that you want, when you want.
- easier to separate projet, restart a projet, etc if you need.
- backup/restaure projet independantly
- Give acces to a particular projet to a user is easier.|||I would go the other way - one database for all. It will more scalable
and flexible. The amount of data is not too much, the speed is not an
issue as long as it is properly indexed.

I actually did a data conversion merging several hundreds of databases
(Also called project) into one.|||TC (golemdanube@.yahoo.com) writes:
> I need to design a system which represents multiple "projects" in SQL
> Server. Each project has the same data model, but is independent of all
> others. My inclination is to use one database to store all projects.
> Looking at the numbers involved, however, I wonder if I would get
> better performance by storing each project in its own database.
> Suppose I have 50 projects, each with two users and 10,000 rows; it
> seems to me I'd rather have 50 x 2 users working in a table with 10,000
> rows than 1 x 100 users working in a table with 500,000 rows.
> On the other hand, the single database approach seems more elegant from
> a design perspective. I wouldn't be creating multiple copies of an
> identical data model, and I wouldn't be creating new databases as a
> business procedure, every time a new project is required.
> Here are my questions:
> 1. For the scenario described above, am I correct to assume I will get
> better performance by using multiple databases, or does SQL Server have
> some clever way of achieving the same performance in a single database?
> 2. Is the multiple database approach common? If anyone has tried it,
> please tell me about how it works in practice.

Whether to use one or many databases has nothing to do with performance
whatsoever. If performance is the only motive for you to consider
separate databases, just forget about it given the volumes you indicated.

There may be other reasons for using separate databases. One project
says "oops, we deleted our data". With a separate database, a restore
is a quick thing. Or some projects may start to call for diverging
requirements, so that they no longer fit into the same model. There
can also be security considerations.

But all of that business requirements that are unknown to me. Since
maintaining 50 databases with the same model requires more overhead,
a single database with a good data model is a good way to start.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"TC" <golemdanube@.yahoo.com> wrote in message
news:1150833955.445569.42840@.h76g2000cwa.googlegro ups.com...
> I need to design a system which represents multiple "projects" in SQL
> Server. Each project has the same data model, but is independent of all
> others. My inclination is to use one database to store all projects.
> Looking at the numbers involved, however, I wonder if I would get
> better performance by storing each project in its own database.
> Suppose I have 50 projects, each with two users and 10,000 rows; it
> seems to me I'd rather have 50 x 2 users working in a table with 10,000
> rows than 1 x 100 users working in a table with 500,000 rows.

This is a small database by today's standards.

In general a single database will probably give you better performance since
only one copy of query plans will be cached, as opposed to 50 (assuming you
use stored procs, etc.).

disk I/O will probably be less as SQL can do a better job of reading in
batches of rows.

So performance-wise, single probably wins out.

In terms of maintenance, etc, a single one is generally better. Assume you
develop an updated version of a stored proc, or need to change a table.
Would you rather do it once or 50 times?

> On the other hand, the single database approach seems more elegant from
> a design perspective. I wouldn't be creating multiple copies of an
> identical data model, and I wouldn't be creating new databases as a
> business procedure, every time a new project is required.
> Here are my questions:
> 1. For the scenario described above, am I correct to assume I will get
> better performance by using multiple databases, or does SQL Server have
> some clever way of achieving the same performance in a single database?
> 2. Is the multiple database approach common? If anyone has tried it,
> please tell me about how it works in practice.
>
> -TC|||TC wrote:
> I need to design a system which represents multiple "projects" in SQL
> Server. Each project has the same data model, but is independent of all
> others.

Others have addressed, both pro and con using a single or multiple
databases and my response would be that the consideration is one
of maintenance and security: Two issues you have not discussed.

But what I would like to add to this discussion is based on your
first two sentences.

From what you've written I can't see how you can justify, except
for security purposes, more than one set of tables with the same
data model. I think Date and Codd said something about it so you
might want to read what they wrote. But based solely on the above
sentences ... the correct solution is to add a column to your
tables named PROJECT_ID.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||As everybody has mentioned, 500k records and 100 users is small by any
definition. But regardless, performance should not be a consideration
until it forces itself to become so.

Think about your idea from a maintenance perspective. At some point
you'll need to add a column to one of those tables, or even make a
simple stored procedure change. Imagine the pain this will cause you
down the road, trying to synchronize changes in all those databases.
Multiply every small hassle you'll ever come across in the future by
the number of "Projects" in your universe. Yikes!

Stick to one database per Application and you'll live a long and
healthy life.

Jason Kester
Expat Software Consulting Services
http://www.expatsoftware.com/

--
Get your own Travel Blog, with itinerary maps and photos!
http://www.blogabond.com/|||I recently recommended a multiple database solution, since my
assessment of the client's needs and data were that they needed the
flexibility of separate databases per data set. Also, my deadline for
completion was very short, and this product was not considered to be
used for longer than the near future.

Each external client's (about 20 clients) data set is different - even
the same client data set could vary - and the process was to massage
each set of data via stored procedures. I Initially opted for one
database per client, to allow for reuse of lookup data (holidays,
fiscal periods, accounts, etc.), but later realized that what I was
designing, an Excel workbook to analyze accounting data, would be
easier to modify if I kept everything the same and simply modified the
connection string.

Ultimately, the choice of multiple servers keeps the stored procs for
the XLW, and the XLW itself, the same, while the only modification
occurs in the stored procedures for building the data, since the data
requirements could vary wildly. The multiple database approach also
allows for multiple data sets to be massaged at the same time -
building the final data set could take several days, so some problems
in concurrency could develop - and with varying procedures for such the
independence was a benefit, albeit creating a lot of redundancy.

Given enough time to design a solution that preserved security - no
client specific information, nor information useful for hacking the
server data could be local, other than the connection string - and ran
as a single database, I could certainly design a solution for them.

James Igoe

james.igoe@.gmail.com || http://code.comparative-advantage.com

Jason Kester wrote:
> As everybody has mentioned, 500k records and 100 users is small by any
> definition. But regardless, performance should not be a consideration
> until it forces itself to become so.
> Think about your idea from a maintenance perspective. At some point
> you'll need to add a column to one of those tables, or even make a
> simple stored procedure change. Imagine the pain this will cause you
> down the road, trying to synchronize changes in all those databases.
> Multiply every small hassle you'll ever come across in the future by
> the number of "Projects" in your universe. Yikes!
> Stick to one database per Application and you'll live a long and
> healthy life.
> Jason Kester
> Expat Software Consulting Services
> http://www.expatsoftware.com/
> --
> Get your own Travel Blog, with itinerary maps and photos!
> http://www.blogabond.com/|||I want to thank everyone for their thoughtful responses. You've helped
me get perspective on this issue.

-TC

One Database or More

A bit of advice needed, as I'm having trouble figuring out whether I need to create multiple SQL Server databases or just one...or if it is a matter of choice.

Say I'm creating Website A that does one thing.

And Website B that does another thing.

But each one has common core underlying tables (customers, cargos, ports, etc) that they both use.

Am I best just creating one database or several? Then am I best creating two or three (A,B and core tables)?

Not that used to SQL server at the moment, and this will be a complete backend for the companies main core business, crm, quality, etc - all distinct apps so to speak, but the data has common underlying tables and they will want to cross-reference data.::Am I best just creating one database or several?

This absolutly and 100% depends on whether you want them or not.

In this case:

::Not that used to SQL server at the moment, and this will be a complete backend for the
::companies main core business, crm, quality, etc - all distinct apps so to speak,

No, these are NOT distinct apps. They are all parts of one suite running the company. In this case it is absolutly best to run all this:

* From one database
* with ONE SET OF BUSINESS OBJECTS.|||Yes - part of one suite. Thanks.

one configure file for all my package.

Hi,

How can i create one configure file that all my packages will inherit the connection to other databases from him?

thanks.

what is better to save my connection string into a table in my DWH or save them to XML file,

so when i want to change my SSIS to work with test enviroment it take the connection string from the db and when i want to switch to prod enviroment i'll use the connection of prod that store in the db.

i think to create a package will switch the connection string value whenever i want?

is it the right way ?

any ideas?

thanks alot!

|||Use of the database or XML is mostly a matter of personal preference. An additional step that is required with a database configuration is managing connections to the configuration database itself. You usually need a seperate XML configuration (or a variable that you set via the commandline) to handle the connection for the configuration database.

On delete cascade

Hi,
1. To my knowledge from sql 2k, there is an option "On delete cascade" and
"On update cascade". But in almost all databases which I have seen till date
,
user defined SP's have been created for deleting table content instead of
checking this "On delete cascade" option. Is there any specific reason for
this? i.e., will there be any performance or anyother reason behind this?
2. Lets assume that I have enabled "On delete cascade" in my DB. If I delete
a record in master table it should delete corresponding details in 4 to 5
tables. What would happen if it fails for some reasons while deleting the 3r
d
table content. Will it rollback?
Regards
Pradeep1. "will there be any performance or anyother reason behind this?"
-No, some guys really want to have their own hand on the deleting of
the data, due to extra issues with logging business rules checking etc.
2. "Will it rollback?"
Sure, thats what a transactional database is for.
HTH, Jens Suessmeyer.|||If can be a safety issue too.
For example, if you use Enterprise Manager to delete a parent row,
referential integrity will prevent you from deleting it if there are child
rows. With cascade delete turned on, the parent and all related children ar
e
deleted. If it was deleted by mistake, it can be quite a complex task to
restore the parent and all the children.
Joe
"Jens" wrote:

> 1. "will there be any performance or anyother reason behind this?"
> -No, some guys really want to have their own hand on the deleting of
> the data, due to extra issues with logging business rules checking etc.
> 2. "Will it rollback?"
> Sure, thats what a transactional database is for.
> HTH, Jens Suessmeyer.
>|||There are technical reasons behind this as well. Cascading referential
actions complicate deadlock minimization because there isn't any way to
determine with certainty the order in which locks will be obtained. If you
issue the DELETE or UPDATE statements in a procedure, you have control over
the order in which locks are obtained and thus can prevent most deadlocks.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1133783057.518482.200520@.g47g2000cwa.googlegroups.com...
> 1. "will there be any performance or anyother reason behind this?"
> -No, some guys really want to have their own hand on the deleting of
> the data, due to extra issues with logging business rules checking etc.
> 2. "Will it rollback?"
> Sure, thats what a transactional database is for.
> HTH, Jens Suessmeyer.
>

OMWWIZE and Copy Database Wizard

Please help.
I am trying to move one of our production databases (MSSQL7) to a larger,
more powerful box running MSSQL2000. Each time I run the copy database
wizard, I get a failure with the error message reading "Failed to create the
share OMWWIZE."
What is this share? Why do I need it? Why would this step fail? Where should
it be? How do I fix this?
I say again, please help.
--
Kerry CarrollHave a look here
http://support.microsoft.com/default.aspx?scid=kb;en-us;q274463
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Good Try" <test@.not.com> wrote in message
news:eL59ym$vDHA.1576@.TK2MSFTNGP11.phx.gbl...
> Please help.
> I am trying to move one of our production databases (MSSQL7) to a larger,
> more powerful box running MSSQL2000. Each time I run the copy database
> wizard, I get a failure with the error message reading "Failed to create
the
> share OMWWIZE."
> What is this share? Why do I need it? Why would this step fail? Where
should
> it be? How do I fix this?
> I say again, please help.
> --
> Kerry Carroll
>

OLTP and Reporting databases seperated?

we are using an object database for our OLTP but for reporting we havegot some issues about performance as the cpu becames a bottleneck.Andwe want to be able torun on low end computers...

One of our team members suggested to replicate the object database to aSQL table.But just a single one.The most denormalized thing ever.(358 coloumns)

is this the fastest way we can get in reporting?

*we don't want harddisk,ram or cpu to became a bottleneck. ( must run on cheap staff)

Replicating to SQL Server from object database is heterogeneous replication and replication is one of those things in SQL Server that is work in progress, so you may not get the results you expect. If you want to remain denormalized you can still reduce the columns by creating UNION ALL Views, if you choose to normalize the table start at the lowest level files and association because what you have now is just a flat file. Hope this helps.

|||thanks.I guess RDBMS is the fastest way to present table to UI.

Old transaction log is reported as missing during recovery

I've done this at least 50 times without issue on various databases, but this
one just doesn't want to play nice.
So I have a SQLServer2K database setup. I've got a home-grown disaster
recovery process in place, so that transactions are taken from one server and
migrated to a backup server every hour. Once it's up and running, the
process is pretty flawless. However, getting it setup can be tricky.
1) Set the recovery model to "Full".
2) Backup your transaction logs
3) Do a full backup
4) Copy the backup to the backup server
5) Restore in to a read only database
Transaction logs can now be backed up on the source database, copied over
and applied to the backup server.
This isn't working this time though. No matter what I've tried, I still get
a message during the restore of the transaction logs such as the following:
"Executed as user: dbo. The log in this backup set begins at LSN
83111000001460600001, which is too late to apply to the database. An earlier
log backup that includes LSN 83075000002839600001 can be restored. [SQLSTATE
42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
(Error 3013). The step failed."
Given the large gap between the two LSN #'s, I'm assuming that the database
thinks there is a very old transaction I'm missing. However, that is sheer
guesswork on my part.
The source box has been cycled, I've tried turning the recovery back and
forth between Full and Simple. This error is from my latest attempt, when I
swear I've done everything by the book.
Any suggestions of what might be causing this or where I should go from here?
Fred...
SQL lost a log somehow, I often had problems similar to this w/ home grown
log shipping.
Solution mostly relied upon: re-execute full backup, then restore & fire up
log shipping again. Full backup restores should be done nightly if
affordable...
Also - transactional replication ultimately proved the best solution for us,
transactions replicated within seconds, if we ever had to failover to backup,
I am much more confident minimal data would be lost as opposed to hourly log
restores...
Regards,
ChrisB
MCDBA OCP
www.MyDatabaseAdmin.com
"fkutz" wrote:

> I've done this at least 50 times without issue on various databases, but this
> one just doesn't want to play nice.
> So I have a SQLServer2K database setup. I've got a home-grown disaster
> recovery process in place, so that transactions are taken from one server and
> migrated to a backup server every hour. Once it's up and running, the
> process is pretty flawless. However, getting it setup can be tricky.
> 1) Set the recovery model to "Full".
> 2) Backup your transaction logs
> 3) Do a full backup
> 4) Copy the backup to the backup server
> 5) Restore in to a read only database
> Transaction logs can now be backed up on the source database, copied over
> and applied to the backup server.
> This isn't working this time though. No matter what I've tried, I still get
> a message during the restore of the transaction logs such as the following:
> "Executed as user: dbo. The log in this backup set begins at LSN
> 83111000001460600001, which is too late to apply to the database. An earlier
> log backup that includes LSN 83075000002839600001 can be restored. [SQLSTATE
> 42000] (Error 4305) RESTORE LOG is terminating abnormally. [SQLSTATE 42000]
> (Error 3013). The step failed."
> Given the large gap between the two LSN #'s, I'm assuming that the database
> thinks there is a very old transaction I'm missing. However, that is sheer
> guesswork on my part.
> The source box has been cycled, I've tried turning the recovery back and
> forth between Full and Simple. This error is from my latest attempt, when I
> swear I've done everything by the book.
> Any suggestions of what might be causing this or where I should go from here?
> Fred...