Post reply

Transfer data from SQL Server 2000 to SQL Server 2000 continuosly

  • July 2, 2005 at 4:38 am

    #109797

    We have data in a Source Server (SQL Server 2000), which is growing at the rate of

    50MB/min. This should be transferred to destination server (SQL Server 2000) at the same

    rate, continuously. The source server is in domain A and destination in domain B, but

    within the same network. The database schemas of source and destination are different. The

    job which does the data transfer should transform and load into the destination database

    schema.

    What is the best approach for implementing this job? We are not able to decide from amongst

    DTS, SQL Scripts with Linked servers and other concepts - to handle this load.

     

    Thanks,

    Ram

  • July 4, 2005 at 1:52 am

    #570593

    If you have to insert the data almost in paralell into 2 different servers (which I wonder why) I would modify the data entry script ot application at the client side to insert the data into the different servers. This would require the less charge of your db server, but if the client app fails there might be some inconsistency between the servers. You could use MS DTC here but you have to do some programming.

    If your server is powerfull enough I would maybe use a linked server and insert the data into the secondary (linked) server via triggers. This solution is reliable but the transactions remain open while the trigger is not finished (the data is inserted into the second server)

    You could also use tools like MQSeries which main purpose is to be a middleware between different schemas and databases.



    Bye
    Gabor

  • July 4, 2005 at 8:30 am

    #570688

    I can only echo Gabor's comment - why are you doing this? 50 Mb a minute is 3 Gb an hour, 72 Gb a day, 2 Tb a month, 24 Tb a year!! Developing an app to insert this amount of data into 2 locations simultaneously with transformations and different schemas is a big, big task, not really the sort of thing that can be answered in a free forum such as this.

  • July 8, 2005 at 2:42 pm

    #572635

    You need DTS on xp_cmdshell with a scheduled Job it can run for four hours, the main requirement SQL Server Agent with Domain Admin permissions and the Server running all will not run any database and all are in the same company network.  Hope this helps.

    Kind regards,

    Gift Peddie

    Kind regards,
    Gift Peddie

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply