Best Practice Question

I have around 10 databases currently residing on different platforms which make-up for roughly a terrabyte of information. I would like to migrate all of these DBs over so that they are all managed under one instance of SQL server 2K. In my view this streamlines things a lot and reduces costs of licensing/hardware.

However, is managing all of these databases on one clustered instance of SQL 2k the best approach from a performace stand point? Would it be better to seperate each database onto its own machine? I am under the impression that given enough hardware (processors, RAM) using just one instance of SQL 2k enterprise should be enough to perform the mangement of this data. Is this correct? Is there an optimal model?

Money is always a concern but in this case, performance is the main objective. The size of the data managed will be growing significantly so the system should be scalable.

My background is as a developer so I may not have provided enough to give a good answer. Please ask questions if you need more detail. I am looking for suggestions on the best way to handle this.

Specifically I would like to know the preferred architecture as well as any suggested hardware.

Thanks in advanceThis link may help - http://www.sql-server-performance.com/vldb.asp
I would definitely consider getting some "expert" advice.|||Thanks, Bruce|||Management and administration don't get any better if all databases reside on the same server vs. separate servers. In fact I'd say you may introduce a "one point of failure" situation, especially if you have some ill-tuned or heavily abused databses.

Best practice

Hi,
I'm writing an application using a sql server database. In this database
there roughly 3 kinds of tables:
1. tables containing data which is generated during the run of an
application
2. tables containing data entered by our clients
3. tables containing data entered by our compagny.
Twice a year our clients need to receive new data in table group 3. Thereby
the data stored in table group 1 may be removed.
Only the data in group 2, entered by our clients should stay in the
database.
What's the best practice to achieve this goal?
I was thinking in splitting it up in 2 databases, 1 with group 1 tables, and
1 with group 2 and 3 tables. Twice a year we create a backup of this second
database en our clients restore this database. After this the clients need
to run a program to check the integrity.
Is this the way to go?
Thank's,
PerryOn Tue, 11 Apr 2006 15:36:42 +0200, Perry van Kuppeveld wrote:

>Hi,
>I'm writing an application using a sql server database. In this database
>there roughly 3 kinds of tables:
>1. tables containing data which is generated during the run of an
>application
>2. tables containing data entered by our clients
>3. tables containing data entered by our compagny.
>Twice a year our clients need to receive new data in table group 3. Thereby
>the data stored in table group 1 may be removed.
>Only the data in group 2, entered by our clients should stay in the
>database.
>What's the best practice to achieve this goal?
>I was thinking in splitting it up in 2 databases, 1 with group 1 tables, an
d
>1 with group 2 and 3 tables. Twice a year we create a backup of this second
>database en our clients restore this database. After this the clients need
>to run a program to check the integrity.
>Is this the way to go?
Hi Perry,
I'd prefer to keep all data in just one database. That makes it much
easier to maintain integrity (FOREIGN KEY constraints don't work
coorss-database), plus it will probably yield better performance.
For your half-yearly update of the company-supplied tables, I'd
recommend that you distribute a script to your customers. The script
would either be a .SQL script with INSERT, UPDATE and DELETE statements,
or a .CMD file with a series of SQLCMD and BCP statements, plus the
files to be used in the bcp operations.
Hugo Kornelis, SQL Server MVP|||like Hugo said, one database for sure.
staging tables to import/export data. good names for all tables.
stored procedures to load/run the data in and out.
you need to make a backup before and after all the data movements.