Today we are going to discuss one of the powerful new functions of SQL Server 2012. The name of the feature is Distributed Replay which can be used to assess the impact of changes and upgrades or SQL Server tunings by replaying a trace captured from production SQL Server environment to a test environment.
What are the benefits?
Unlike SQL Server profiler, the Distributed Replay can run the recorded trace against as much as 16 Distributed Replay Client, all these clients are controlled by one Distributed Replay Controller. Distributed Replay components are installed separately from the regular SQL Server install. The Distributed Replay Controller can act as a replacement for Ostress, except for the ability to replay SQL and RML files.
This powerful feature will help customers/DBAs in understanding the impact of any application/configuration changes on performance for multiple servers. You can customize the trace replay by choosing appropriate mode for your environment
Environment:
We will use a Distributed Replay controller and a Distributed Client on server SQLNODE1, a second Distributed Replay Client on server SQLNODE2 and a target instance with SQL Server 2008 R2 instance.
For installation we need to create two Active Directory accounts .First CORP\DR_Controller for Distributed Replay Controller and CORP\DR_Client for Distributed Replay Client. Below are the screenshots for reference.
Distributed Replay installation
We will start the installation now — Make sure that you install Management Tools too as it provides Distributed Replay administration tool. For this installation I will install Distributed Replay Controller and Distributed Replay Client, as I already have Management tools installed on my SQL Server instances.
We will start installation on SQLNODE1 as it will act as a controller as well as client.
Step #1: Select the installation type. Here we will add features to existing standalone installation, selecting the same.
Step #2: Setup will install setup support files.
Step #3: You have option to include SQL Server updates.
Step #4: Setup will install setup files.
Step #5: Setup should pass all setup support rules. You may ignore warnings and go ahead on a case-by-case basis.
Step #6: Since I already have the DB Engine installed, I will choose to Add features to existing instance of SQL Server 2012.
Step #7: Since this Server will act as a Controller and Client thus selecting both here.
Step #8: This page will review the disk space requirement.
Step #9: On this page, Provide both the accounts created for Distributed Replay Controller and Distributed Replay Client in Active directory.
Step #10: On Distributed Replay Controller page, add the Active Directory account that we have created.
Step #11: On Distributed Replay Client page, provide the controller name.
Step #12: On Error Reporting Page you can opt for sending information of Error reporting to Microsoft.
Step #13: On this page setup will check if the installation will be blocked by running rules. All green here.
Step #14: We are ready to install. You might like to .cross verify the selected configurations before proceeding.
Step #15: Installation is in progress.
Step #16: Finally the setup is successful. You can close the window now.
Windows Firewall configurations
We will configure the Windows firewall on the Distributed Replay Controller to allow inbound connection for DReplayController.exe application .
Go to Windows firewall, Inbound Rules, and add a new rule for a program:
Browse to the DReplayController.exe location:
Allow the Connection.
Specify a name to the rule.
Similarly create a rule on each Distributed Replay Client to allow Distributed Replay Clients to connect and register to the Distributed Replay controller,
Verification
Start Services and check client registrations.. First start the Controller service on the Distributed Replay Controller SQLNODE1, open a command prompt screen and enter:
NET STOP “SQL Server Distributed Replay Controller”
NET START “SQL Server Distributed Replay Controller”
You should see the result of this command in the log folder of Distributed Replay Controller under C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayController\log.It should be like this
2014-01-06 20:30:42:482 OPERATIONAL [Controller Service] Microsoft SQL Server Distributed Replay Controller – 11.0.2100.60.
2014-01-06 20:30:42:482 OPERATIONAL [Controller Service] © Microsoft Corporation.
2014-01-06 20:30:42:482 OPERATIONAL [Controller Service] All rights reserved.
2014-01-06 20:30:42:482 OPERATIONAL [Controller Service] Current edition is: [Enterprise Edition].
2014-01-06 20:30:42:482 OPERATIONAL [Controller Service] The number of maximum supported client is 16.
2014-01-06 20:30:42:482 OPERATIONAL [Controller Service] Windows service “Microsoft SQL Server Distributed Replay Controller” has started under service account “CORP\DR_Controller”. Process ID is 2888.
2014-01-06 20:30:42:498 OPERATIONAL [Controller Service] Time Zone: India Standard Time.
2014-01-06 20:30:42:498 OPERATIONAL [Common] Initializing dump support.
2014-01-06 20:30:42:498 OPERATIONAL [Common] Dump support is ready.
Now start Distributed Replay Client service on your Distributed Replay Clients SQLNODE1 and SQLNODE2. Verify that they are correctly synchronized with your Distributed Replay Controller.
On both servers, open a command prompt screen and run:
NET STOP “SQL Server Distributed Replay Client”
NET START “SQL Server Distributed Replay Client”
Open the latest log file in the location C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\log and check for text Registered with controller
2014-01-06 20:30:51:000 OPERATIONAL [Client Service] Microsoft SQL Server Distributed Replay Client – 11.0.2100.60.
2014-01-06 20:30:51:000 OPERATIONAL [Client Service] © Microsoft Corporation.
2014-01-06 20:30:51:000 OPERATIONAL [Client Service] All rights reserved.
2014-01-06 20:30:51:000 OPERATIONAL [Client Service] Current edition is: [Enterprise Edition].
2014-01-06 20:30:51:015 OPERATIONAL [Common] Initializing dump support.
2014-01-06 20:30:51:015 OPERATIONAL [Common] Dump support is ready.
2014-01-06 20:30:51:015 OPERATIONAL [Client Service] Windows service “Microsoft SQL Server Distributed Replay Client” has started under service account “CORP\DR_Client”. Process ID is 2872.
2014-01-06 20:30:51:015 OPERATIONAL [Client Service] Time Zone: India Standard Time.
2014-01-06 20:30:51:015 OPERATIONAL [Client Service] Controller name is “SQLNODE1”.
2014-01-06 20:30:51:015 OPERATIONAL [Client Service] Working directory is “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\WorkingDir”.
2014-01-06 20:30:51:015 OPERATIONAL [Client Service] Result directory is “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir”.
2014-01-06 20:30:51:015 OPERATIONAL [Client Service] Heartbeat Frequency(ms): 3000
2014-01-06 20:30:51:015 OPERATIONAL [Client Service] Heartbeats Before Timeout: 3
2014-01-06 20:30:51:078 OPERATIONAL [Client Service] Registered with controller “SQLNODE1”.
You might get below error message in the Client Log if in case you forget to add the Client Service account in the Controller Service access permission page during setup or you just change the Distributed Replay client service account .I re-produced the scenario and below is the log file.
2014-01-06 18:04:54:603 OPERATIONAL [Client Service] Microsoft SQL Server Distributed Replay Client – 11.0.2100.60.
2014-01-06 18:04:54:619 OPERATIONAL [Client Service] © Microsoft Corporation.
2014-01-06 18:04:54:619 OPERATIONAL [Client Service] All rights reserved.
2014-01-06 18:04:54:806 OPERATIONAL [Client Service] Current edition is: [Enterprise Edition].
2014-01-06 18:04:54:806 OPERATIONAL [Common] Initializing dump support.
2014-01-06 18:04:54:853 OPERATIONAL [Common] Dump support is ready.
2014-01-06 18:04:54:884 OPERATIONAL [Client Service] Windows service “Microsoft SQL Server Distributed Replay Client” has started under service account “CORP\DR_Client”. Process ID is 2800.
2014-01-06 18:04:55:196 OPERATIONAL [Client Service] Time Zone: India Standard Time.
2014-01-06 18:04:55:243 OPERATIONAL [Client Service] Controller name is “SQLNODE1”.
2014-01-06 18:04:55:243 OPERATIONAL [Client Service] Working directory is “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\WorkingDir”.
2014-01-06 18:04:55:258 OPERATIONAL [Client Service] Result directory is “C:\Program Files (x86)\Microsoft SQL Server\110\Tools\DReplayClient\ResultDir”.
2014-01-06 18:04:55:258 OPERATIONAL [Client Service] Heartbeat Frequency(ms): 3000
2014-01-06 18:04:55:258 OPERATIONAL [Client Service] Heartbeats Before Timeout: 3
2014-01-06 18:04:55:648 CRITICAL [Client Service] [0xC8100005 (6)] Failed to connect controller with error code 0x80070005.
The error code 0x80070005 is also described as “ACCESS DENIED.” Below are the steps to fix it.
- On the server where controller is installed. Goto – Start -> Run and type dcomcnfg and open Component Services.
- Navigate to Console Root –> Component Services –> Computers –> My Computer –> DCOM Config -> DReplayController
- Open the properties of DReplayController and select Security tab
- Edit “Launch and Activation Permissions” and grant “Distributed Replay client service account” permission for “Local Activation” and “Remote Activation”.
- Edit “Access Permissions” and grant “Distributed Replay client service account” permission for “Local Access” and “Remote Access”.
- Add “Distributed Replay client service account” domain user account within “Distributed COM Users” group.
- Restart controller and client services like below
NET STOP “SQL Server Distributed Replay Controller”
NET STOP “SQL Server Distributed Replay Client”
NET START “SQL Server Distributed Replay Controller”
NET START “SQL Server Distributed Replay Client”
- Check the Distributed Replay Client log file and see the message “Registered with controller SQLNODE1”
Conclusion
At this point, our Distributed Replay lab is ready to use, clients and controller are registered together. We will perform a demonstration in upcoming blog post and will understand this powerful feature.
No comments