How to do this?

Hey all!

I have a problem. On Sql server 7.0 I have a database with about 250 tables. This database is on 5 different servers. What I have to do is combine all the 5 databases on the 5 servers into one database on a sixth server. In addition, 80 of the 250 tables have to be filtered before going into the sixth server. All a unique column must be added to each table in the database before is it copied to the final database.

I have tired this with replication, but say an additionaly table is added to one of the 5 databases. The replication wont pik this table up. I ahve tried DTS packages (Export/Import) but that become unmanagable with 250 tables in the package. I'm new to this but this is very frustrating. Please help.Ummm..sounds like a full time job...

Coordination alone...

Who uses this "database"...

(How is one db distributed over multiple servers btw)|||Do you need to keep the 5 original database or the 5 servers or have you the option of just bringing all 5 servers + their tables into 1 server/database ?|||Originally posted by mtracey
Do you need to keep the 5 original database or the 5 servers or have you the option of just bringing all 5 servers + their tables into 1 server/database ?

I have to get the data from the 5 database into 1 but i cant change the data on the 5 databases (they remain untouched). This database is used for consolidated reporting purposes.|||I don't think that there are any easy solutions to this. You've identified DTS or Replication. Replication may be easier, however as you've pointed out if a new table is added to one of the databases, you will have to setup replication for it. I guess though you would have to do the same for DTS. Its whichever you're more comfortable with. Either way you'll have to have a strict change management process in place.

If I had to choose between the 2, then replication would be the way to go.

We have a reporting server, pulling data from 3 SQL Servers + 1 AS/400, using a combination of Replication, DTS + Datamirror.
Fun huh ;-)

Datamirror is very expensive btw.

Mark|||I tested this process using replication but I have to add a hostname unique column to all the tables and for a few tables i have to filter the tables so only a selected recrods are replicated. I used the snapshot pulling replication method. Are ne tips for the replication?

For DTS packages i'm using the xport/import wizard to do the transfer.

It seems like i'm going in circles with this problem