Post reply

updating data in a remote sqlserver dinamically

  • March 12, 2009 at 7:30 am

    #93289

    I have a windows.net application that feed a local sql2005 database and a web site that makes some of the data available online. The remote DB is also a sql2005 database with same structure as the local one. I want to be able to move and/or Update some of the data entered locally into the remote DB to make it available through the website. This should be periodically and dinamically.

    What would be the best way to get this done. Thanks in advance for any help.

  • March 12, 2009 at 9:21 am

    #958423

    Hi

    Maybe you should have a look to database replicatin and mirroring.

    Greets

    Flo

  • March 12, 2009 at 10:09 am

    #958476

    The situation is that I need to update only some of the tables at the remote DB.. I don't want to replicate the whole DB because clients will be adding data on their own for them to see it through the same web site. So this means that the remote tables are going to have also client data that will only reside in the remote server. I was considering creating a store procedure tha periodically creates an script file with the commands to insert/update the data. Once I get this file at the remote server and it's executed It would update the DB. But I think there must be an easier way to accomplish this..

    Thanks again for any suggestion

  • March 12, 2009 at 10:22 am

    #958487

    If the "some tables" are only written from your publisher database you can replicate only them.

    If not:

    First you absolutely need a Last-Update column in your tables to avoid always to transport all data.

    Either use SSIS in one step

    Use the integration services to export the data from your publishing database and direct import them into your new database. This requires that both databases are available at execution time.

    Or use SSIS in two steps

    Use the integration services on publishing server to export the data to a file (csv, xml, ...). Use SSIS on subscriber database to import the file. An availibility of both databases is never needed.

    Or use linked server

    Add the publishing server as linked server to the subscriber. Select the new/changed data from publisher (e.g. into a temp table); add the new data and update the changed. This requires that both databases are available at execution time.

    Or ...

    ...

    Take your choice and lets goon. 🙂

    Greets

    Flo

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

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