Post reply

Keeping data in synch

  • January 3, 2006 at 9:16 am

    #170455

    Here's the dilema:  I am challenged with finding a way to keep several db servers worth of data in synch with each other.  Okay, you say, replication is an idea, etc. 

    However the issue is that I must also synch data accross environments (production, acceptance, dev).  Now, of course the environments should have no dependencies on each other, yet I am supposed to keep data in synch accross environments.   

    Also, to complicate things a little further, I am asked to find a way to keep data in synch accross locations (one of which is a failover location).  These locations should also be capable of operating completely independent of each other.

    So, without creating dependencies accross locations or environments by using replication.  What options am I left with here?  Any suggestions would help me a ton.  Thank you.



    A.J.
    DBA with an attitude

  • January 3, 2006 at 10:26 am

    #612437

    I guess to me the question would be why?

    Is it worth the time and money to create something to keep data in "sync".

    Is it static data the application needs? or is it user entered data?

    do you have to maintain user entered data in all the other environments? or just data entered into production?

     

  • January 3, 2006 at 10:38 am

    #612443

    Well, obviously if locationB is a failover for locationA, then the data in each location would need to be the same.  This is "all" data, user-entered, and otherwise.

    Now, to address "Why?" in regards to keeping data in synch accross environments:  The reasoning behind this is a little less concrete.  To explain it briefly, I'll just say that it's a company specific requirement, that unfortuneately comes from people who get paid a lot more than I do.



    A.J.
    DBA with an attitude

  • January 3, 2006 at 10:49 am

    #612446

    I would handle failover situations differently than keeping data in sync across dev/qa/acceptance.

    You should look at failover clustering or log shipping for this.

    But as far as getting production data over to other environments you;ll probably need to create some sort of process that basically works like merge replication.

     

  • January 3, 2006 at 10:56 am

    #612447

    We actually have log shipping in place for the failover location.  The complicated part of this is, that LocationB also serves as a "live" datacenter in parallel with LocationA.  This means that it serves dual purposes.  One purpose is to be a failover location in case of disaster.  Secondly, is for load balencing.  This complicates things.  For example:  if a record exists in LocationA and not in the same table in LocationB, then the question is... was it added in LocationA or deleted in LocationB?

     



    A.J.
    DBA with an attitude

  • January 4, 2006 at 7:03 am

    #612586

    At least for the two "live" datacenters, the easiest, most reliable solution is merge replication.  That's exactly what it was intended for.


    Student of SQL and Golf, Master of Neither

  • January 4, 2006 at 7:29 am

    #612598

    This does sound like merge replication (or possibly SQL 2005's peer-to-peer replication).  Log shipping works well for maintaining a failover system, but it is readonly.

    You could create your own system by putting triggers on all tables on each server to log any changes, and use an agent job to periodically propogate the changes to the other servers.  Or put a timestamp field in each table and create a table to track the last timestamp synced for each table, then periodically look for new records to copy.  Either way you'll just be re-inventing replication.

    You could use stored procedures for all DML actions.  Every proc includes a loop to execute the same statement on every defined linked server.  Or all procs create a SQL command that is passed to a main proc that executes it locally and against all linked servers.  I'm just tossing out ideas here, not saying this is a practical design.  An obvious disadvantage is that if any server is unavailable all the other servers will stop working.  An alternative would be to insert the commands into a distribution table and have separate jobs for each linked server to propogate them.

    I understand not wanting to put up with the constraints created by replication, but it is a robust method and I can't think of another method that doesn't involve a huge amount of custom coding and/or end up forcing the same constraints on you.

Viewing 7 posts - 1 through 6 (of 6 total)

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