data-konzept

'“How do we know replication is actually delivering transactions? How long were things taking to be delivered to the distributor? Was there a lot of latency between the distributor and the subscriber?” These were the questions our business partners were asking.

It was amazing how much I couldn’t answer. Or more accurately, how much I could quantify. It was time to get personal and just take a peek at what was going on. Traditionally I would just write some queries against the distribution database to see how many transactions were pending but it doesn’t offer much in terms of historical performance. And replication monitor just answers every question with “POTATO.”

So if I wanted monitoring solution, I’d have to make like a dad at a Rush concert and roll my own. It was time to actually start monitoring replication performance, and I was going to use… extended events. Extended events are wonderful diagnostic tools to have in your tool belt for any SQL Server occasion really. If you’ve never used them before and want to learn more about them, I’d highly suggest checking out Erin Stellato’s and Jess Borland’s blogs (or talks) on them. I learned this all from them, and they’re my go-to if I ever have questions.

I like to watch

Extended events are great; they have all the goodness of profiler except you don’t use profiler. Win/win! More to the point, extended events let you quickly and easily view, sort, and aggregate events that occur on your instances. They also have powerful filters (really, a “where” clause) to limit noise. You have way more control over what you monitor, how you store the data, and how you view and use it. This makes them perfect use to track replicated transactions, since we want to measure at both an individual level and the aggregate.

I fired up management studio and went to “New Session” looking for some replication event goodness and I found…'...

http://port1433.com/2017/03/08/monitoring-sql-server-replication-with-extended-events/?utm_source=SSC&utm_medium=pubemail