I have a device that continuously collects data and stores it on a local database. I would like to also replicate that data to a second computer over the network every 10 seconds to ensure data reliability. Should I use transaction replication to achieve this? What if the network connection is temporarily unavailable, will the replication service automatically transmit all the backlogged data once the connection is restored? Can the computer that collects the data be SQL Server Compact or does it have to be at least Workgroup edition to be a Publisher. The second PC will run SQL Server Express and would be a subscriber. Thanks.
+1
A:
I think you will need at least work group edition to be your publisher. Other than that yes transaction replication should do the trick. It can run continuously and the "backlogged" data will be replicated once connectivity is restored.
Marc Tidd
2010-05-28 21:45:04
+2
A:
- I'd prefer to use mirroring or log shipping for something like this, but those options are off the table if your second PC is running Express.
- The distribution agent will retry, however if the disconnect continues for an extended amount of time, your subscription may be deactivated or expired/dropped.
- Your publisher must be at least Workgroup edition (which limits the number of subscribers).
See these links for the specifics on which editions support which features.
Joe Stefanelli
2010-05-28 21:48:25
2. Yes, you will need to monitor your subscriptions and reinitialize them if they are expired. I use merge and snapshot replication and I check subscribers status daily.
Marc Tidd
2010-05-28 22:05:09
We could probably upgrade from the Express Edition if there was any significant benefit. To clarify, the first PC collects the data and stores it in local database as well as replicates it to main server that processes the data. The only purpose of the local database is to maintain a backup in case of data loss on the server. The database is updated approximately every 10 seconds so the replication needs to be fast and incremental as the updates come in. However, if for example the database on the server is erased, all the missing data needs to be synchronized. What is the best option?
Serge
2010-05-28 22:47:42
I also assume that the compact edition is out of the question as it does not support any of these services.
Serge
2010-05-28 22:58:05
If upgrading from Express is an option, you should definitely read up on mirroring and see if it meets your specific needs.http://technet.microsoft.com/en-us/library/cc917680.aspx
Joe Stefanelli
2010-05-29 03:51:11