My good friend Lori Brown just had an article in this month’s SQL Server Magazine (November 2011), so go check it out at the link and read up on this great technique of capturing actions on a SQL Server via SQL Profiler, and then replaying those actions on another machine. It’s an OLTP thing, mostly, but can be used in the data warehouse as well. The concept is, on a high level, that you back up your database, take a trace of an operation/set of operations, restore your database, and run them again via the SQL Trace facility. It’s a cool way to check your environment when major changes have taken place: a new version of SQL Server install, a new server, new operating system…this may even be a great way to load test a virtual machine to see how it performs to the old box. I actually just thought of this as I’m typing, and it’s a great idea since people are so paranoid about how “performant” these Hyper-V boxes are – this would be a great way of testing a move to VMs!
By the way, the graph that Lori gave to SQL Server Mag (below)…this picture, right…it’s mine and I drew it. Where’s my credit, Lori? Just kidding.
This sort of test can also be done – maybe even a bit easier than the native way described – by using the Scalability Experts SQL Server Upgrade Assistant. Not to be confused with the SQL Server Upgrade Advisor, this tool is a download for doing basically the same thing, and the best part is that it automates a goodly portion of the tasks in the above picture. My picture!!!
Check out Lori’s great article, and try this technique sometime. It works very well. It’s not easy and it takes some time, but it’s worth it, especially with SQL 2012 coming out next March.
Thanks for reading,
Lee
------------------------
http://www.sqlmag.com/article/performancetuning/sql-server-trace-replay-tool-139800
http://www.scalabilityexperts.com/tools/downloads.html
http://www.windowsitpro.com/content/content/139800/Brown-SQL2144-Fig1-lg.jpg
89bca54a-6b5a-4cfb-bc12-c5c74358753d|0|.0