SQL Server Performance

What's the most appropriate method for my situation?

Discussion in 'SQL Server 2008 Replication' started by PaulMolloy, Aug 21, 2009.

  1. PaulMolloy New Member

    I've got small databases used to write log data to on multiple servers in different locations around the world. None of the servers can be 100% relied upon due to variable network conditions in each location, but they are self-contained systems logging only their own data. They generate approximately 50,000 records per day between them at the moment, but I'm expecting that to increase as more servers are added over the next year.
    What I need to do is to collect the log records from the originating servers into a central database used for statistical analysis.
    The current approach is a nightly job that calls a web service hosted on each logging server. The service returns the days records and deletes them from the log database to keep the size down and to prevent them from being collected more than once.
    I've been looking at the various database replication methods with a view to setting the main server up as a subscriber and each of the log servers as publishers, but I'm getting confused and I don't think that's going to work. Wouldnt that result in deleting them from the stats server when they get deleted from the logging servers? Everything I've seen seems designed to exactly duplicate a table in two places rather than transfer the data from one to another which is what I need.
    Any suggestions would be appreciated.
  2. MohammedU New Member

    If you are looking for replication option then you can do one way merge/transactional replication instead of bi-directional.
  3. satya Moderator

    Welcome to the forums.
    What is the SLA for availability, is it a 24/7 or any downtime on application?
    What is the recovery model on the database?
    On the larger scale we need to consider the latency between the sites, do you have dedicated network between central and other client servers? If not it is hard to achieve continuous availability or you could design the snapshot of replication on periodic basis sequentially.
    Just a note on transactional replication (from Technet):
    Transactional replication provides the tracer token feature, which provides a convenient way to measure latency in transactional replication topologies and to validate the connections between the Publisher, Distributor and Subscribers. A token (a small amount of data) is written to the transaction log of the publication database, marked as though it were a typical replicated transaction, and sent through the system, allowing a calculation of:
    • How much time elapses between a transaction being committed at the Publisher and the corresponding command being inserted in the distribution database at the Distributor.
      • How much time elapses between a command being inserted in the distribution database and the corresponding transaction being committed at a Subscriber.

Share This Page