What's the best approach to my situation? RRS feed

  • Question

  • Is this even a job for replication?

    I've got small SQL Server 2008 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 stats server can't be 100% relied on either, so I need an approach that will carry on where it left off if the server is shut down for a while.

    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 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 not sure where to start and I'm not even sure that's going to work. Wouldn't 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. I really need a shove in the right direction.

    Friday, August 21, 2009 10:48 AM


  • I think this is the correct approach.

    While replication technologies are good for distributing or aggregating data they are not good where you want to purge one or more of the sources. One approach that might work is transactional replication in all of the originating servers and replace the del command with a NONE statement. However this would require the web or standard edition in each of the originating servers and I am not sure if this would fit your budget as you mention "small SQL Server 2008" database which I suspect are Express editions. SQL Server Express does nto support transactional replication.


    looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Friday, August 21, 2009 10:58 AM