March 9, 2020 at 1:14 pm
Hello Gurus,
The requirement is to upgrade from SQL Server 2008 R2 to SQL Server 2019.
Can you tell me the steps to follow in Testing, UAT and Production to make sure all queries are tested and working optimally before and after the upgrade? For Ex: sproc taking 4 seconds vs sproc taking 1 second. What parameters can be put etc ?, say a legacy piece of code would NOT work on SQL Server 2019 or throw some wierd errors. I need to document and execute this as soon as I can. which tools to use ? Profiler, Extended Events, Wait Stats etc.
Thanks in Advance
March 9, 2020 at 1:33 pm
The Data Migration Adviser (I think that's what it's called) is what you need. If your application uses ad hoc SQL instead of (or in addition to) stored procedures, you'll want to capture a workload using Profiler or Extended Events so that can also be analysed.
John
March 9, 2020 at 1:34 pm
On your UAT server, restore the databases with the same compability.
You could try out https://spaghettidba.com/tag/workloadtools/ for capturing / replay the SQL on the UAT.
Saw a demo at dataminds connect. You start with a relevant restore of database of production and play the captured SQL back on the UAT server with the restored databases for a comparison
Capturing all sql's and their parametervalues is probably possible with extended events / traces.
https://www.scarydba.com/2018/09/24/extended-events-and-stored-procedure-parameter-values/
Haven't tried https://www.hammerdb.com/about.html yet to compare specific executions (query X must complete in ...)
March 9, 2020 at 4:53 pm
if you want to performance test each proc then (depending on how many procs) then you might want to use an automation tool or a trace replay.
the important thing in testing is to re-set yourself to the same position every time you run your test. you don't want the last test to corrupt your next test
MVDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply