Some of you may know that I am kind of a huge fan of Distributed Replay – a technology that not that many people know about, but at the same time a feature that can help you a ton especially when you are planning an upgrade, migration or you simply have a performance problem. DReplay has a problem though – you need to capture your application workload and that needs to happen with a Profiler trace and as you probably have heard already – Profiler is deprecated. Recently, however, Robert Dorr from Microsoft just mentioned as part of some other discussion that the latest version of the RML Utilities – 9.04.0004 – can convert an .xel file to a .trc. I immediately asked myself – so does that mean that now I can capture my workload with xEvent session -> convert the workload to a trace file and then replay it with Distributed Replay? Let’s find out…

By the way, this post was not going to be possible(as you will find out why below) without the help of Robert, so thank him and not me!

The first thing I did was to open a Profiler Replay template(as it includes exactly all the events that you need to capture in order to make DReplay work) and take a look at every event that was captured by it and select those same events for my xEvent session.
Once I have done that, I started the session and ran my workload by retaining the events into a file on my local machine. Once the workload was captured, I downloaded the latest version of the RML Utilities and I remember that Robert mentioned that in order to convert the .xel to .trc we need to use the -M parameter. I did not find any documentation on this, so I started guessing how the command should look like. At one point I ended up having this:

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -M"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC.trc"

However, when I tried to convert the file it was throwing errors for events that were not captured. That was kinda strange because remember – I mimicked the same events from the Replay template of Profiler. I read the output log which I found in some nasty directory – C:\Users\YourUser\AppData\Local\Temp. Once I reconfigured the session with all the new events, I again captured the workload and tried to convert to a trace file.

…………………………………………………………………………………………………………………………………………………………………

A lot hours later I was going back and forth, capturing, trying to convert and receiving those same errors for missing events. At that point I decided to write to Robert and ask him if there is some guidance or article, or something that I can use in order to make that work. He replied to me that there is a .sql script that will create the xEvent session with everything needed in order to make that work as part of the samples in the download. Ooooooh… okay! Let’s go there:

After entering the path where the .xel file should be, I again captured my workload and tried to convert. This time something different happened when I ran my readtrace command:

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -M"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC.trc"

The Reporter.exe opened up with analysis of the workload, but more importantly – there was no DReplayTRC.trc in the destination folder I selected?
Went back and forth, back and forth again with no success(again – there is no documentation on this – nothing anywhere). I again wrote to Robert and he said that the -M parameter does not accept output(and he was surprised that it was not throwing me an error), so I needed to do -M -o”the path to the file”. This means that if I run the below, it should work:

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -M -o"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC.trc"

And it did. Partially… The problem this time was that the -o parameter in this case was accepting DreplayTRC.trc as a folder and placing all the generated files there + there was a .trc file for each session from the workload and I did not want that. Again to the command line, this time using readtrace /?

Seeing the -MS parameter I was now optimist that I will finally be able to convert to a single trace file. Let’s see (+changed the directory output folder):

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -MS -o"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC"

Finally! We have managed to convert a .xel file containing our application workload to a SQL Profiler trace .trc file. Now that .trc file can be given to the Distributed Replay Controller to prepare it for replay…

I don’t know how you feel about this process, but I kinda feel that there should be a better way for doing all of these things in the future. If Distributed Replay is “the technology that we are going to be using for replaying mission critical workload in the future” as Microsoft said in 2012, then let’s hope that first DReplay will get a GUI(because you remember how many people were using Extended Events in SQL Server 2008R2, don’t you?) and second why not DReplay “accepting” .xel files in first place?

Thanks for hosting this one, Jes! Hoping you will be able to count to more than 100 at the end of this Tuesday! 🙂

Oh, by the way, if you want to stop the Reporter.exe to show up at the end of each export, just put -T18 at the end of the cmd command above. It should look like this.

readtrace -I"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\XECapture_0_130781709497210000.xel" -MS -o"C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\DReplayTRC" -T18

Don’t ask. 🙂