How can I minimize data in SQL replication

Question

I want to replicate data from a boat on the shelf to a ground object. The connection is sometimes made via satellite and can be slow and have a high delay.

The delay in our application is important, people on shore should have data as soon as possible.

There is one table replication consisting of an identifier, date and time, and some binary data that can vary in length, usually <50 bytes.

The offshore application constantly moves data (hardware measurements) to a table and we want this data to be in place as quickly as possible.

Are there any tricks in MS SQL Server 2008 that can help reduce bandwidth usage and reduce latency? Initial testing uses a bandwidth of 100 kB / s.

Our alternative is to minimize our own data transfer, and the initial prototyping here uses a bandwidth of 10 kB / s (while transferring the same data at the same time interval). This is without any checks of reliability and integrity, so this number is artificially low.

Answer 1

You can try different replication profiles or create your own. Different profiles are optimized for different network / bandwidth scenarios.

MSDN talks about replication profiles here .

Answer 2

Have you considered getting a WAN accelerator? I'm too new here to post a link, but there are a few available.

In fact, the device on the sending side compresses the outgoing data, and the receiving side unpacks it, everything is "on the fly" and completely invisible. This allows you to increase the visibility of the traffic speed and does not require changing the server configuration. It should be completely transparent.

Answer 3

I would suggest on-the-fly compression / decompression outside of SQL Server. That is, SQL replicates data normally, but something in the network stack is compressed, so it is much less and efficient in bandwidth.

I don’t know anything, but I’m sure that they exist.

Do not contact SQL files directly. This is crazy, if not impossible.

Answer 4

Do you expect that there will always be only one table that is replicated? Are there many updates or just inserts? Replication is implemented by calling insert / update sproc for the destination for each changed row. One cheap optimization is to make the sproc name be small. By default, it is made up of the table name, but IIRC you can force a different sproc name for the article. Given that about 58 bytes are inserted for a string, saving 5 or 10 characters in the sproc name is significant.

I would suggest that if you update the binary field, is it usually a whole replacement? If this is not the case, and you can change a small part, you can flip your own error correction mechanism. Perhaps the second table containing the time series of the byte is changed to the originals. Sounds like pain, but can have huge bandwidth savings depending on your workload.

Are inserts generally performed in logical lots? If so, you can save the batch of inserts as one customized blob in the replicated table and have a secondary process that decompresses them into the final table that you want to work with. This will reduce the overhead of these small rows flowing through replication.