Post reply

Solution need for multiple remote Site database update

  • July 1, 2014 at 4:49 am

    #292871

    Hi all, hope there is someone that can guide me on the right path using the best features SQL has to offer:

    Here is the setup I'm dealing with:

    6 individual sql databases

    All 6 servers are running SQL 2008 STD

    The tables that I would need to update between these databases are exactly structured the same(each table has exact same column headings and table name, only the values in the Rows would be different between databases)

    The 6 servers will link between each other via the internet

    The objective:

    1) When I edit a specific row in a column on a table for example -

    Table: dbo.ProdPrice

    Column: AveCost

    Row: 15

    (updating product price)

    It needs to replicate that change down to the 5 other remote databases, updating that exact Table, Column, Row (does not have to happen live could update on schedule)

    (I was thinking of using an after update trigger for this)

    2)5 of these databases would need to regularly send updated values from a specific Table, but only a selected number of column to a output file so that the data can be uploaded to a website. For example -

    Table: dbo.stk_master

    Column: stkdescriptionid ; stkonhnd

    (Do not have an idea to get this working)

    Hopefully someone out there can give me some type of direction to get this started.

    Thanks

  • July 1, 2014 at 5:09 am

    #1726111

    That's actually a tough problem. If this were a SQL Server 2012 instance I would ask if it was possible that those clients be Read Only. In this case you could set up Availability Groups, but, you can't have 5 read only until 2014, so it's possibly a bad choice. You might want to look into BizTalk to manage the messages so that you can get all the databases to receive the various commands and deal with the distributed nature of the transactions that you're looking at. You could look at merge replication, but that's extremely difficult to set up and manage. You might be well served by looking into the Service Broker and those events.

    I'm not crazy about triggers on tables for something like this because that means that locks are held while you wait on transactions across the internet. That could cause all sorts of really bad performance problems, timeouts, etc.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

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

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