I knew Brent and Erik wouldn’t touch replication, so I figured I’d give it a whirl.
My good, old friend replication
I have a love-hate relationship with replication. Mostly hate due to latency and errors, but it does serve its purpose. Before Availability Groups came out, I used Transactional Replication to copy data from the production OLTP database to another server so that we could offload reports.
Will it work with a Managed Instance?
I immediately crashed and burned as I didn’t read the limitations of Managed Instances. My bad.
A Managed Instance can be a subscriber but not a distributor or a publisher. FINE.
I setup the distributor and a publisher on an Azure VM and then added the Managed Instance as the subscriber. I initially had some errors in Replication Monitor, but that was because I hadn’t touched replication in over 2 years and had forgotten some things that I once knew, such as replication needs a UNC path for the snapshot. Once the data was flowing between the two servers, I started pumping data into the publisher and watching Replication Monitor for latency and errors.
I inserted a tracer token to verify how fast data was making it to the subscriber: 4 seconds, not bad.
Those who’ve used replication know that rebuilding a large index can cause excessive replication latency. I didn’t have a large index for this test, but I still wanted to see how this setup would do.
Latency was always under 20 seconds, and it recovered back down to 4 seconds fairly quickly.
Test, test, test
If you plan on using a Managed Instance as a subscriber in your replication topology, be sure you test for latency. Excessive latency is typically caused by a large transaction. The largest transaction on a particular database is often rebuilding the largest index. Start there for your latency testing.