Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    22

    Unanswered: Database Syncronisation

    This is a bit of a weird question, so bear with me, hopefully it will make sense at the end.

    I have a single SQL server that contains details of all users on a particular system.

    I also have a series of remote sites, each remote site also has an SQL server that contains details of the users within that site

    The connection between these remotes sites is via a large WAN network.

    I would like to be able to syncronise details (data, table changes, stored procs etc), from the main server into the remote sites servers. The caveat here is that the remote sites need to sync all generic information and only user information specific to the users at that site. This syncronisation would occur whenever a change is made to the main server. If a change is made to global data then all remote sites would sync, if the change is user specific then only the relevant site would sync.

    So the questions are :

    1) Are SQL Notification Services able to handle this, as it is not feasible to have sites constantly polling the central server for changes?

    2) Is DTS flexible enough to be able to do the global and per user updates?

    3) What other ways could this be handled?

    With regards to point 3, up until now I am currently processing updates via a custom service that sits on the remote site server and polls the main server for changes every hour - this isnt an acceptable solution as it has prooved to be unreliable, there is a considerable amount of bandwidth wastage and remote sites can be out of sync for upto an hour.
    Last edited by MrEyes; 01-28-05 at 09:08.

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    3) Replication. It is a vast topic and I am no expert on the subject but I do believe it is possible to keep all of your servers real time and up to date. Requires some reading and a lot of planning.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is definitely something that replication is able to handle, the question is can your WAN cope with replication. I'd really strongly suggest you find a consultant that understands replication and get them to work with, not for you on this. They can help you understand the issues and the design trade-offs, but I think it would be wasteful to have them just devise a plan and leave it for you to implement unless they also help you understand it too.

    -PatP

  4. #4
    Join Date
    Jan 2004
    Posts
    22
    Thanks for the replies.

    Unfortunately for me this is something I am going to have to do on my own as getting a DBA resource to handle this is, for various reasons, impossible.

    So the question is, where can I find a good information resource about replication?

    With regard to the WAN, without getting into specifics, it should be able to handle anything thrown at it (High Bandwidth OC connections). If replication is intensive enough to bog down this type of connection then I will need to come up with another viable alternative.

    There is also one other issue that could possibly be a show stopper: For network security reasons the central server cannot initiate its own connections out therefore each of the remote server would have to start the replication request - is this possible within replications functionality model?

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    SQL Server Books Online (located by going to Start\Program Files\Micorsoft SQL Server) and can be downloaded from the SQL Server Developer Center on the MS site.

    You might want to find a book on the subject. Lots of pitfalls you can fall into and it can get sticky. I hate dealing with it.

    Stongly suggest some professional help.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    As for your last paragragh, I seem to remember replication can work on a push or a pull basis but I am unsure if this will work for the kind of real time transactional merge replication you want.

    I helped a SR. dba with a replication project once and I have had to troubleshoot it before but like I said it is complicated.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Jan 2005
    Location
    TempDb
    Posts
    228
    Yes, you can do pull subscriptions with near real time replication (transactional replication). The key, as pointed about above, is planning: Plan, Plan, Test, Plan, Test, Ship. You can even securely replicate transactions over the internet.

    You need to use custom stored procedures to accomplish only syncing each subscription with the correct information.

    So, to start your plan, here's what you do:
    1) Make sure you can connect to your main server (or your distribution server if you choose to use one) via your remote servers. Test it.
    2) Determine how much data will be replicated initially and then the average amount to be replicated over time. Use worst-case scenarios so you can plan the space you need in your distribution database, your file location and your destination databases.
    3) One of the most important factors for replication to be successful is that your distribution database is kept as current as possible. This means that the publisher has uniterrupted access and that the subscribers can download frequently enough to avoid the database from filling.
    4) Understand the role of snapshots and plan those. Snapshots are always used regardless of the replication model you choose (unless you are starting every database from scratch).
    5) Start small with a single test table. Get it working with ALL your subscribers, including using a custom stored procedure, then start working with the real stuff.
    6) Make production implementations during off-hours.
    7) If you can't hire a consultant, hire a friend...

    Good luck.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •