SQL 2012 –Distributed Replay

The Microsoft SQL Server Distributed Replay feature helps you assess the impact of future SQL Server upgrades. You can also use it to help assess the impact of hardware and operating system upgrades, and SQL Server tuning. Similar to SQL Server Profiler, you can use Distributed Replay to replay a captured trace against an upgraded test environment. Unlike SQL Server Profiler, Distributed Replay is not limited to replaying the workload from a single computer .Distributed Replay offers a more scalable solution than SQL Server Profiler. With Distributed Replay, you can replay a workload from multiple computers and better simulate a mission-critical workload. The Microsoft SQL Server Distributed Replay feature can use multiple computers to replay trace data and simulate a mission-critical workload. Use Distributed Replay for application compatibility testing, performance testing, or capacity planning. In this session we will cover the following:

  • What is SQL Server Distributed Replay
  • Distributed Replay Concepts
  • Benefits of Distributed Replay
  • When to Use Distributed Relay

But before we get there we need to look at risk. Do not run it against your prod environment.
ONLY use DRU in a test environment
as it
:

Can be a security risk to sensitive information

Only uses Windows Authentication

Only supports the SQL Server Native Client ODBC data access provider

What is distributed Replay functionality in SQL Server 2012?

  • It was introduced in SQL 2012
  • Allows us to assess SQL, OS and hardware upgrades
  • Allows us to do Performance tuning or troubleshooting
  • DRU can run in 2 modes – Synchronization or Stress mode

    Distributed Concepts

  • Distributed Replay administration tool: A console application, DReplay.exe, used to communicate with the distributed replay controller. Use the administration tool to control the distributed replay.
  • Distributed Replay controller: A computer running the Windows service named SQL Server Distributed Replay controller. The Distributed Replay controller orchestrates the actions of the distributed replay clients. There can only be one controller instance in each Distributed Replay environment.
  • Distributed Replay clients: One or more computers (physical or virtual) running the Windows service named SQL Server Distributed Replay client. The Distributed Replay clients work together to simulate workloads against an instance of SQL Server. There can be one or more clients in each Distributed Replay environment.
  • Target server: An instance of SQL Server that the Distributed Replay clients can use to replay trace data. We recommend that the target server be located in a test environment.
  • The Distributed Replay administration tool, controller, and client can be installed on different computers or the same computer. There can be only one instance of the Distributed Replay controller or client service that is running on the same computer.

Distributed Replay Architecture

Benefits of DRU

  • It is not limited to replaying a Workload on one machine
  • Better simulate mission critical workloads
  • Use Distribution Replay for application compatibility testing, performance testing, or capacity planning.
  • Provides better scalability than profiler as DRU is scalable up to 16 clients
  • The Microsoft SQL Server Distributed Replay feature can use multiple computers to replay trace data and simulate a mission-critical workload. Use Distributed Replay for application compatibility testing, performance testing, or capacity planning.

When to use the tools

SQL Server Profiler

  • You want to use the conventional replay mechanism on a single computer. In particular, you need line-by-line debugging capabilities, such as the StepRun to Cursor, and Toggle Breakpoint commands.
  • You want to replay an Analysis Services trace.

Distributed Replay

  • You want to evaluate application compatibility. For example, you want to test SQL Server and operating system upgrade scenarios, hardware upgrades, or index tuning.
  • The concurrency in the captured trace is so high that a single replay client cannot sufficiently simulate it.

For the video on this please go to the  Distribution Replay  webinar I did on this for Pragmatic Works.

Advertisements

About JodyWP

I am a SQL Server MVP based in Cape Town, South Africa. I am a DBA by heart and I am a huge sports fan love WP rugby and SPURS (football/Soccer). I also serve as a PASS Regional Mentor for the MEA region. In this role I assist other like minded people in growing their user groups or starting one.. I also serve on the SQLCape leadership committee . SQLCape is the local SQL User Group in Cape Town and meets the 2nd Tuesday of each month at the local MS office. I also serve as the organizer in chief for our local SQLsaturdays
This entry was posted in DBA and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s