May 24, 2012 at 2:38 pm
I have an issue with replaying traces that I hope someone can help with. We are in the process of migrating one of our production servers from a physical box to a virtual machine. As a part of testing the new virtual server, I have captured a profiler trace file from our production server using a server-side trace, and am replaying the trace file(s) on the new virtual server. I've done this a few times before and the replay is working as expected.
My issue is this: We do nightly backups on our production server which write out the backup files to a file server (e.g. BACKUP DATABASE MyDB TO DISK = '\\MyServer\MyInstance\MyDB.bak'). So when the trace replays the backup it actually takes a backup of the db on our test server and writes it out to our backup location, overwriting the previous night's production backup.
Is there any way to prevent this? I know I can use aliases on the server to repoint to a sql server instance, but is there someway to alias a network path so that instead of writing to \\MyServer\... the backups would write to \\MyTestServer\...?
May 24, 2012 at 2:50 pm
i suppose you could on your VM server, edit the host file to trick the UNC path with DNS.
when your testing is done, remove the host file entry.
May 24, 2012 at 3:39 pm
Thanks Geoff. Yes, I've considered that, but it appears that editing the hosts file only works when resolving against IP addresses and not when resolving UNC names such as \\MyServer. I've been digging around online for a solution with no success.
May 24, 2012 at 10:21 pm
Have you considered loading the trace file into a table and then deleting the rows which contain the backup execution statements? Well, I guess you'd have to delete both the starting and ending events but doing a search on the TextData field with LIKE '%backup%' would probably identify them.
When replaying trace files, I often have to run the trace on a different machine which doesn't have the specific user names specified in the original for the login and logout events. So what I've done is import the trace to a table through profiler (I've found you have to do it through profiler) and then updated the login events to have a user which exists on the test machine.
I would think you would be able to do the same thing with the backup events. If you wanted to keep the backups for performance testing, you could rewrite the directory name of the backup destination.
May 25, 2012 at 10:32 am
Thanks Jesse! Yes, that is the approach I'm having to take now.
My original trace was a server-side trace which doesn't allow you the option of writing the trace to a table. I attempted to then load the trace files into a table using fn_trace_gettable(), but Profiler wouldn't replay the trace from the table, saying it was missing necessary events. (It replayed the trace files just fine.)
I was against running the trace on my production server using Profiler because I didn't want the load on the server. But I've found that I can run the Profiler GUI from another machine but still connect the trace to my prod server so that it keeps the overhead from Profiler GUI off my production box. So now I'm running a new trace using the GUI and loading the results to a table. Once the trace is completed I'm hoping I can just do a REPLACE() on the TextData column and replace all occurrences of \\MyServer with \\MyTestServer. What I don't at this point is what the BinaryData column contains and whether or not it will also need to be modified.
If all this fails, then my next option is to either try and filter out the backup events, or run the replay in specific time blocks to avoid the nightly backup windows.
Thanks for the input!
Tim
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply