I have a question regarding database design and size .
I am migrating from several DB2 databases to SQL server. I was going to
create different databases based on application dept or business units
(the way it has been in db2). But my application folks says, they
cannot connect to multiple database or join tables accross databases,
so have all the tables in one database.
If I do that( i hate to do it), the database size will easily be 200 -
250 GB.

1. Is having all the tables in 1 database a good idea, what are the
pros and cons ?

2. If I create this huge database, how can i do maintainance on it ? Is
there a way, I can backup quickly. My guestimate for backing up a 250
GB database is around 1-2 hrs, which is not feasible.

Any input is greatly appreciated.

Thanks

Roger

1st of all, your application folks dont know what they'r talking about.

You CAN do multi-db joins, and they should be able to connect to multiple
DBs. (are they writing in VB, C++, C#, VB.NET, etc or what ?)

If they dont know how to do that, then they might want to go take a class or
something as it's pretty "101" stuff.



Multiple databases on the same server is not a bad option at all.

further, you should look at this site and learn about large Databases and
maintenance, etc:




Cheers

Greg Jackson
PDX, Oregon

man I was so aggravated, I forgot to paste my link

http://www.microsoft.com/sql/techinfo/administration/2000/scalability.asp



GAJ

Thanks Greg, Those guys are coding in COBOL, using ES-MTO (a
microfocus engine)- this is a mainframe conversion project. I showed
them that you can add the database name in front of the table name to
do multi database queries (am i right) . But they keep saying they
cannot do it. ES-MTO uses ODBC , ADO.NET to connect to sql server.

Thanks for link Greg...i appreciate it

AMEN My Brother....!

Furthermore if they use ADO.NET and SQL Providers, they can do all the joins
they need.

but I'm not going to even gonna go down that road.

I like Aarons solution much better anyway.


GAJ

[quoted text, click to view]

Ideally, their external code would call stored procedures. Then they don't
have to know how you implement the database side. It could be one database
or 200, and you could have a job switch it back and forth between the two
architectures every other Thursday.

Let the developers write the code. This is why you have database people on
staff. :-)