SQL Server Archives - John McCormack DBA

Distributed Replay Error: Failed to set proper database for the connection

3rd May 2019 By John McCormack Leave a Comment

Distributed Replay Error: Failed to set proper database for the connection – Troubleshooting dreplay.exe

If you see Failed to set proper database for the connection in your replayresult.trc, it is worth looking at the row below it (in profiler).

Check if the TextData for the row below (with the same ReplaySequence) would run from the database listed in the DatabaseName column.

Hint, either its a database that you haven’t restored to your test environment or you have named the databases differently to production. e.g. you have prefixed the names with perf_

https://johnmccormack.it/2016/10/error-dreplay-could-not-find-any-resources-appropriate-for-the-specified-culture-or-the-neutral-culture/

Useful links

Test read intent connections to an AG Listener

19th April 2019 By John McCormack 3 Comments

To test read intent connections to an AG Listener, I prefer to use SQLCMD but you can also test easily using SSMS.

SQLCMD

The -Kreadonly switch is your key to success here but remember to also specify the database using -d. When not set (and with an initial catalog of master for my login), I found I always got the primary instance back during my check. This simple omission cost me hours of troubleshooting work, because I was convinced my listener wasn’t working correctly. In fact, I just wasn’t testing it correctly.

Read/Write Connections
sqlcmd -S "SQL01-AG1-list" -d WideWorldImporters -E -q "SELECT @@SERVERNAME;"

Read only Connections
sqlcmd -S "SQL01-AG1-list" -d WideWorldImporters-E -q "SELECT @@SERVERNAME;" -Kreadonly

The instance that you are connected to will show in the command prompt. Type exit to leave sqlcmd.

SSMS

In object explorer, click Connect and choose Database Engine.

Then, in the bottom right hand side of the dialog box, click on Options >>

In Connection Properties, Click Connect to database and then <Browse server ..>. Choose a DB that is in your availability group.

Then click on Additional Connection Parameters and type in ApplicationIntent=ReadOnly

Click connect and run SELECT @@SERVERNAME and you should expect to see the instance name of your secondary replica, providing you have set up the read only routing correctly.

If you change connection and remove ApplicationIntent=ReadOnly from the Additional Connection Parameters, you should see the result as the name of your primary instance.

Summary

Hopefully these 2 simple techniques to test read intent connections to an AG Listener will be useful and help save you time. It’s a simple blog post but I wanted to write it because I was looking at the problem in too much depth and missing the obvious mistake of choosing my database context.

Where the extended events eventfile is located

5th April 2019 By John McCormack Leave a Comment

Do you need to find out where the extended events eventfile is located? Simply run the query below and use the WHERE clause if you know the name of the XE session.


SELECT
s.name,
CAST(st.target_data AS XML).value('(/EventFileTarget/File/@name)[1]','nvarchar(256)') AS target_data_file,
CAST(st.target_data AS XML) AS target_data_xml
FROM sys.dm_xe_session_targets st
JOIN sys.dm_xe_sessions s
ON st.event_session_address = s.[address]
WHERE s.name = 'Custom XE Session Name' -- Optional if you know the name

More info about the DMVs