Results 1 to 6 of 6

Thread: How to do this?

  1. #1
    Join Date
    Sep 2003
    Posts
    212

    Unanswered: 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.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ummm..sounds like a full time job...

    Coordination alone...

    Who uses this "database"...

    (How is one db distributed over multiple servers btw)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    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 ?

  4. #4
    Join Date
    Sep 2003
    Posts
    212
    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.

  5. #5
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    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

  6. #6
    Join Date
    Sep 2003
    Posts
    212
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •