Post reply

Uploading data from local db to remote db

  • January 8, 2007 at 10:51 pm

    #176418

    I am new to MS SQL server and I am trying to set up a script or procedure that will allow me to automate the upload of changes in a local database to a remote database so that I effectively have a duplicate of the local database.

    Any hints or tips on how to do this?

    I have some experience with .NET too is that is a useful tool for this issue.

    Thanks in advance for any advice.

    Tim

  • January 8, 2007 at 11:02 pm

    #681943

    You can use the following three methods...

    1. Mirroing...

    http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx

    http://www.dell.com/downloads/global/power/ps4q06-20070130-Muirhead.pdf

    2. Log shipping...

    http://msdn2.microsoft.com/en-us/library/ms190016.aspx

    3. Replication...

    http://msdn2.microsoft.com/en-us/library/ms151176.aspx

     

    MohammedU
    Microsoft SQL Server MVP

  • January 8, 2007 at 11:42 pm

    #681947

    yes as mentioned by the last user, use database mirroring or log shipping or replication. before first read out the full requirements of why do you want to do this. So that  you can prefer for the best method among these. If you want the secondary database to act as a stand by database?

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • January 8, 2007 at 11:46 pm

    #681949

    I gather the data locally and then I upload (once daily update) to a remote server that is widely available. I am doing it this way in order to carry out manipulation of the data and then upload only the necessary data - so I am guessing that mirroring is not necessarily the best method.

    The reason I do all of this locally is for security reasons, some of the data kepy local is sensitive and this allows me to keep that dataset more secure.

    Thanks for your help. Any further advice would be most welcome.

    Regards,

    Tim

  • January 8, 2007 at 11:55 pm

    #681951

    if thats the case the best option that i can say is that use either row or column level replication or if this is not possible then the following will surely work

    1. create a linked server to your destination server

    2. write a procedure to do your manipulation

    3. create a job in the primary server having the TSQL as

    insert into [linkedserver].dbname.obj_owner.tablename

    execute procedurename

    4. schedule the job to run daily once after production hours

     

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

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

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