Mission critical application testing with Distributed Replay Joe

Mission critical application testing with Distributed Replay Joe Yong Senior Program Manager This document has been prepared for limited distribution within Microsoft. This document contains materials and information that Microsoft considers confidential, proprietary, and significant for the protection of its business. The distribution of this document is limited to those solely involved with the program described within. Confidential and Proprietary © 2011 Microsoft Last Updated:

DISTRIBUTED REPLAY • What is Distributed Replay? • Multi-client scalability to drive higher throughput • Higher workload replay fidelity • Useful for stress testing production workload or performing application compatibility testing before upgrades • Why an enhanced replay tool? • Use multiple low-cost clients (workstations) to handle large workload traces within reasonable time • Address limitations of current SQL Trace based tools (Profiler, Upgrade Assistant) • Provide enhanced features • Groundwork for future workload testing capabilities

DISTRIBUTED REPLAY BASIC CONCEPT System change Trace File Capture Replay (before) Replay (after) Comparison Reports

DISTRIBUTED REPLAY DATA & WORKFLOW Batch Request 1 Preprocess Trace File Batch Request IR file IR file Batch Request User Controller 2 Replay … SQL Server Batch Request Multiple Replay Clients

DISTRIBUTED REPLAY CONCEPTS • Connect time: time delta between trace start and login is established • Think time: time delta between two events on the same connection

PREPROCESS & REPLAY STEPS • What Happens During Preprocess: • • • Sort the events in ascending order of Event. Sequence Trim away events and columns not used for replay Generate an intermediate file Goal: preprocess the trace once, and intermediate file can be replayed with different options What Happens During Replay: • • User specified the replay options, including which clients to use Shred the intermediate file into chunks in a loadbalanced fashion Dispatch the file chunks to the clients Two replay modes, stress and synchronization o o Under stress mode, each client sends request independently Under sync mode, client needs to coordinate with controller to know when a specific event can be released (this is optimized so it is not done for every single event)

PREPROCESS OPTIONS • • Inc. System. Session: Whether system session events are filtered out Max. Idle. Time: cap max idle time between events

REPLAY OPTIONS FOR SEQUENCE CONTROL 1 Synchronization Mode Submit order Event submit ordering across SPIDs Submit timing Submit time synchronization When to use High-fidelity replay mode, such as functional runs Stress Mode Stress. Scale. Granularity = 2 SPID Stress. Scale. Granularity = 3 Connection Event submit ordering within SPIDS Event submit ordering within connections Connect. Time. Scale (0%– 100%) Think. Time. Scale (0%– 100%) Use. Connection. Pooling (Yes | No) Use 100% connect time scale and 100% think time scale for performance runs. Use SPID granularity and reduce connect time scale and think time scale for moderate-stress runs. Use Connection granularity and reduce connect time scale and think time scale for high-stress runs

SYNCHRONIZATION MODE • • Events submitted in order across SPIDS Emulate original event submit time:

STRESS MODE (SPID GRANULARITY) • • •

STRESS MODE (CONNECTION GRANULARITY) • • •

OTHER REPLAY FEATURES (1) • • Added type support for: sql_variant, money, smallmoney Healthmon for deadlock detection:

OTHER REPLAY FEATURES (2) • Automatically manages database context • Avoid DB mismatch between captured trace and current DB context – DR will issue a “USE DATABASE. . . ” command if discrepancy is detected between current DB context and DB Name column in intermediate file during replay Configurable query timeout • Result trace collection • • Execution success/failure Execution elapsed time (microsec) (On/Off) # of rows in result sets (On/Off) result set contents • Supported SQL Server versions: Input Trace File Version Target SQL Server 2005 SQL Server 2008 / 2008 R 2 / 2012 SQL Server 2008 R 2 / 2012 SQL Server 2012 SQL server 2012

PERFORMANCE & SCALABILITY

APPLICATION COMPATIBILITY TESTING • More thorough functional and performance testing • Detect potential upgrade issues not covered by Upgrade Advisor: • Test ad-hoc T-SQL embedded in application • Test actual T-SQL execution, not just syntactical checks • Test security configuration o May include OS level security settings • Detect changes in query plan, duration and results • Detects use of undocumented features/objects • Very rare but possible: identify changes that break or alter query behavior but is not documented (yet) • Not required for all applications but highly recommended for • Missions/business critical applications • Complex applications • Application where queries are mostly client-side, middle-tier or dynamic

APPLICATION COMPATIBILITY TESTING Database Upgrade Trace File Capture Replay (before) Replay (after) Comparison Reports

SQL SERVER UPGRADE ASSISTANT V 2 • Leverages Profiler for SQL Trace capture • Leverages Distributed Replay to replay captured workload in trace file • Enhanced reporting capabilities on top of SSUA V 1 • Enhanced user interface

APPLICATION COMPATIBILITY TESTING Upgrade Assistant for SQL Server V 2. 0 powered by SQL Server 2012 Distributed Replay

SUMMARY • Key to a successful upgrade is planning & testing • Tools are intended to simplify and expedite execution, not to replace proper planning • Level of pre-upgrade validation/testing is typically proportionate to business criticality • There is a wealth of resources available • Feedback through Microsoft Connect will drive improvements in product and upgrade process/tools • We are working on plans for more automation for pre-upgrade validation

RESOURCES • Distributed Replay • http: //msdn. microsoft. com/en-us/library/ff 878183(v=SQL. 110). aspx • Upgrading to SQL Server 2012 • http: //msdn. microsoft. com/en-us/library/bb 677622(v=SQL. 110). aspx • Upgrade Advisor • http: //msdn. microsoft. com/en-us/library/ms 144256(v=SQL. 110). aspx • Application compatibility testing with Upgrade Assistant • http: //social. technet. microsoft. com/wiki/contents/articles/sql-server-upgradeassistant-tool-for-denali. aspx • Product feedback • http: //connect. microsoft. com/sqlserver

FURTHER. FORWARD. FASTER. • Learn more Visit the Microsoft Virtual Academy Free technical SQL Server training courses • Get more Download SQL Server 2012 Trial Software 180 -day evaluation of SQL Server 2012 • Do more Participate in virtual launch activities and collect points for prizes The more points you earn, the bigger your prize could be

© 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.