We are planning to enable Multi AZ on our production database which uses Amazon RDS as a managed service. Our Database is using SQL Server 2012 standard version.
Before enabling that, I wanted to simulate our production workload on a dummy database to check how the read write latency are getting affected after making the dummy database as Multi AZ.
Whats the best way to do the above ?
I like Brent’s idea, nice and simple and will give you an indication of what you need. Here are also options to replicate the actual real workload:
record it in production and replay it using SQL distributed replay
manually capture select statements using profiler (may be tricky)
involve more advanced performances testing tools such as Hammer DB, SQLStress
write your own queries and in SSMS run them in different tabs with GO x, where x is the number of iterations (yes SSMS can do this) to simulate concurrent workload (although that’s more or less what SQLStress would do)
Hope this helps
The fastest, easiest way is to rebuild an index on a large table.
That pushes a lot of write activity through, and it’s consistent, easily repeatable, and easily timed.
It’s not your workload (or anything near it) but helps you get a fast idea of the overhead right away.