On 26th of February I will be speaking for the PASS DBA Virtual Chapter! I will present on the topic of Distributed Replay in SQL Server 2012 and how it can help you do performance tuning, application compatibility testing and forecasting. So if you are interested in any of those three above or you feel like they are important for you and your job, register for the webinar and I hope to see you online!
Update: Below are the recording and the questions that I did not have time to cover during the webinar. I hope that they will be helpful to you:
Q: SQL Profiler is scheduled for being removed from SQL Server 2014, so using it is not an option moving forward. [John Cook]
Q: If Profler is going away how will the trace be created?[David MacLean]
A: Absolutely true! However, no one from Microsoft has pointed us already to the solution of that. My guess(and not only mine) is that we are going to start using Extended Events for capturing our workloads(trace files).
Q: How do we go back and add users if only the current user had been added during the install?[John Cook]
A: What this step actually does is grant your current user(not all needed) DCOM permissions. So in order to add the account that you want, you have to go to Control Panel -> Administrative Tools -> Component Services -> Computers -> My computer -> DCOM Config -> DReplayController -> Security. There you should add the account for the DR Client and grant him the respective permissions based on where the Controller and the Clients are – if they are on the same machine – Local Access/Activation will do the job, otherwise you have to grant the DRClient user all Remote permissions in the Launch and Activation and on the Access Permissions section.
Q: Can you install DRClient and DRController on the same machine/instance?[Diane Sithoo]
A: Yes, you can. If you choose to do that you are also not limited to using domain accounts for the 2 services. You can use local accounts for such type of configuration.
Q: If we just want to have one dsitributed replay controller and client on the same system, do we still need to have AD account? Or a local user account would do? A: Local accounts will work in this scenario.
Q: What’s the minimum permission that we should give the DR client account on the database?[Lucas Kartawidjaja]
A: If you check MSDN they say – sysadmin. I, however, say that’s not true and you can test it for yourself. However, the minimum permissions will depend on what the workload you are running actually is. If these are just select statements – go ahead and grant only read access. If you have other DML queries though, you have to grant the respective permissions.
Q: Can you use TSQL_Replay template agaist SQL 2005 instance?[Diane Sithoo]
A: Yes, you should be able to.
Q: Let’s say that I already forgot to configure my DRreplay client to point to DRController. How do I change to my default configuration that I already left default as click next next next?[Marin Sok]
A: You can change it by going on the machine where the DR Client is installed and then in C:\Program Files(x86)\Microsoft SQL Server\110\Tools\DReplay Client there is a CONFIG file named DReplayClient. Open it and put the host name where the controller is installed between the tags. Save the file, restart the DRClient service and check if you connected successfully to the controller by opening the last log file in the DRClient Log directory. You should be able to see the message “Successfully registered with controller ”.
Q: Can you have client installed on target server?[Bharat Kenjale]
A: Yes, you can.
Q: What is the -o flag is the second replay cmd statement?[Alex Thornbury]
A: “-o” stands out for generating output files that are residing on each client and that you can use to analyse your replay.
Q: If I use sql profile to capture trace file, will it impact on my prod?[Marin Sok]
A: Yes, for sure. That’s why you have to script it and run the trace as a server side trace.
Q: What are the changes in 2014?[Joe Montilla]
A: I found no changes for now in the CTP2.
Q: Will u send a link to get the recording of this session?[Norma Jean Sebastian]
Q: Will this presentation be available online? Where?[Mike Filipiak]
A: Yes. Once the link is online, I will post it here.
Q: Is there a way to download your t-sql script for trigger the performance?[Marin Sok] Yes, you can find it attached together with the slide deck and all other materials.
Q: Can you replay traces from different instances at the same time to different clients (e.g. transactional replication, linked server workloads)? Any resources on how to do that?[Diane Sithoo]
A: Very good question! The answer is yes, you can, but you have to have more than 1 controller because each of the clients for each of the workloads that you are replaying have to be registered(connected) to separate controller. Otherwise you will see the message “The Distributed Replay controller is busy” (if you try to replay 2 traces at the same time by using 1 controller). One more thing here – you are limited to just one Controller service per machines, so you will have to install one more VM (for example) and install the second/additional controller/s there.
Q: Please repeat where to get the replay.[Mike Filipiak]
A:If you are asking for the output files from the replays (do not forget that you have to add the –o parameter in the dreplay replay command), you will have them on each client(by default) at C:\Program Files(x86)\Microsoft SQL Server\110\Tools\DReplay Client\ResultDir. What’s important to mention here is that when you are installing each of the client you can specify where those files to be created, so it is up to you to choose where you want to place them.