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.
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 ;-)
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