The Curious Case of… how to find FILESTREAM info from an MDF

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

A couple of weeks ago a question came in to the MCM email distribution list where someone wanted to know how to find FILESTREAM info from an MDF. They were working with a client who needed to attach a database and all they had was the MDF – no backup, and no access to the database attached anywhere else. The attach was failing because the database had a FILESTREAM filegroup defined, and so the CREATE DATABASE … FOR ATTACH statement needed to specify the FILESTREAM filegroup name and a new location for it.

Given that there was no SQL Server access to the database or a backup, the only way to find the information was through examining the MDF directly.

Here are the steps I recommended:

  • Download and install the freeware hex editor HxD from here.
  • Open the MDF file in HxD.
  • Hit Search, change the data type to hex values and search for the string ‘6C 00 64 00 66 00’, which is the Unicode byte-reversed hex for ‘ldf’.
  • Hit F3 until it can’t find any more. Now you’re positioned in the list of file for the database.
  • Just below that will be pathnames of other files, so scroll down until you come to one that doesn’t have ldf, mdf, or ndf at the end of the pathname.
  • This is the pathname for the FILESTREAM data container, and above that is the filegroup name.

As an example, the image below shows a portion of the MDF for a test database I created:

C:\SQLskills\Documents is the pathname and FSWaitsDocuments is the logical name.

Bottom line: you never know when a little hex-editor spelunking will come in handy!

The Curious Case of… the CLR assembly failure after an AG failover

(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)

Jonathan was working with a client recently who experienced a CLR assembly failure after an AG failover and needed to figure out why. They’d been testing their AG disaster recovery strategy and ran into an unexpected problem with their application which relies heavily on SQLCLR and an UNSAFE assembly that calls a web service from inside SQL Server.  When they failed over their AG to their DR server, the CLR assembly failed with the following error:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65546. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: System.IO.FileLoadException: Could not load file or assembly ‘sqlclr_assemblyname, Version=1.0.0.0, Culture=neutral, PublicKeyToken=fa39443c11b12591’ or one of its dependencies. Exception from HRESULT: 0x80FC80F1

To try and bypass this error, they executed the command ALTER DATABASE <DBNAME> SET TRUSTWORTHY ON to enable the trustworthy bit on the DR server.  They then tried the steps in KB Article 918040 and changed the database owner for the database on the DR server and then their CLR assembly began to work.

Well, at least it worked until they tried to failover to their original primary replica, and they again began to have problems with their CLR assembly.

Why would that be the case, especially since it originally worked on the primary replica before the DR failover?

It has to do with login SIDs in SQL Server and server-scoped permissions.  The database owner is mapped inside the database by the SID of the login on the server.  If the SID of the owner internally in the database doesn’t match a SID of a server principal on the server then the owner can’t be established.  The dbo SID internally in the database is replicated as a part of the AG, but the server login is not.  Also server scoped objects, like the asymmetric key used to sign the CLR assembly, are maintained in master, as is the login associated with that key and the EXTERNAL_ACCESS or UNSAFE ASSEMBLY permission associated with it.  So to fix this issue and get rid of the TRUSTWORTHY ON bit setting for the database they had to do the following steps:

  1. Create the asymmetric key from the assembly DLL on the DR server.
  2. Change the database owner to match the SID on both servers in sys.server_principals (script the dbo login using sp_help_revlogin to transfer with SID intact to both servers)
  3. Create login from asymmetric key on DR server and grant UNSAFE ASSEMBLY to match primary replica
  4. ALTER DATABASE <DBNAME> SET TRUSTWORTHY OFF
  5. Fail over to test between both sites

Bottom line: it’s *always* a good idea to regularly test your failover strategy as you never know what’s going to fail when you do! Kudos to this client for doing that.

SQLintersection Spring 2019

As we head towards our SQLintersection Spring 2019 in June (our 13th show), I’m excited to say that it’s once again our most diverse, complete, and information-packed show yet!

One of the pieces of feedback we hear over and over is that attendees love SQLintersection because it’s a smaller, laid-back show, where you get to actually spend time talking with the presenters 1-1. I have to say that’s one of the reasons why we love the show so much; *we* get to spend time talking to attendees, rather than being mobbed by hundreds of people after a session ends. And we only pick presenters who we know personally, and who we know to be humble, approachable, and eager to help someone out.

We have 2 pre-con days at the show and with our post-con day, there are 8 full-day workshops from which to choose. We have 32 technology-focused (NOT marketing) sessions from which to choose, plus two SQL Server keynotes, multiple industry-wide keynotes by Microsoft executives, and the ever-lively closing Q&A that we record as a RunAs Radio podcast (you can listen to the recording from our Fall 2018 show here.)

You’ll learn proven problem-solving techniques and technologies you can implement immediately. Our focus is around performance monitoring, troubleshooting, designing for scale and performance, cloud, as well as new features in Azure and SQL Server 2016, 2017, and 2019. It’s time to determine your 2008 / 2008 R2 migration strategy – should you upgrade to 2016/2017/2019 directly? Should you move to Azure? This is the place to figure that out!

If you’re interested in how we got here – check out some of Kimberly’s past posts:

  1. SQLintersection: a new year, a new conference
  2. SQLintersection’s Fall Conference – It’s all about ROI!
  3. Fall SQLintersection is coming up soon and we can’t wait!
  4. SQLintersection Conference and SQLafterDark Evening Event – what a fantastic week in Vegas

And Kimberly recorded a Microsoft Channel 9 video where she discusses the 2017 Spring show – see here.

SQLafterDark

With minimal to no marketing filler, we’ve largely kept our conference focus on ROI and technical content (performance / troubleshooting / tales-from-the-trenches with best practices on how to fix them ) but we’ve also added even more social events so that you really get time to intersect with the conference attendees and speakers. The addition of the SQL-specific, pub-quiz-style evening event SQLafterDark was wildly popular from our past shows and that’s returning for Spring!

 

SQLintersection: Great Speakers!

Once again, I think a great show starts with great speakers and current / useful content. All of these speakers are industry-experts that have worked in data / SQL for years (some can even boast decades) but all are still focused on consulting and working in the trenches. And, they’re good presenters! Not only will you hear useful content but you’ll do so in a way that’s digestible and applicable. Every speaker is either an MCM (Master), a past/present SQL Server MVP, or a past/present Microsoft employee (or a combination of all three!) But, regardless of their official credentials, ALL are focused on providing the most ROI that’s possible in their session(s) and/or their workshops, and ALL have spoken for SQLintersection multiple times.

Check out this phenomenal list of speakers:

  • Aaron Bertrand – MVP, SentryOne
  • Ben Miller – MCM, MVP, past Microsoft, Consultant
  • Bob Ward – SQL Server team at Microsoft
  • Brent Ozar – MCM, past-MVP, Consultant
  • Buck Woody – SQL Server team at Microsoft
  • David Pless – MCM, Microsoft
  • Gareth Swanepoel – SQL Server team at Microsoft
  • Glenn Berry – past-MVP, SQLskills
  • Grant Fritchey – MVP, RedGate
  • Jonathan Kehayias – MVP, MCM, MCM Instructor, SQLskills
  • Kevin Farlee – SQL Server team at Microsoft
  • Kimberly L. Tripp – MCM Instructor, MVP, past SQL Server team at Microsoft, SQLskills
  • Pam Lahoud – MCM, SQL Server team at Microsoft
  • Paul S. Randal – MCM Instructor, MVP, past SQL Server team at Microsoft, SQLskills
  • Pedro Lopes – MCM, SQL Server team at Microsoft
  • Tim Chapman – MCM, Microsoft
  • Tim Radney – MVP, SQLskills

You can read everyone’s full bio on our speaker page here.

SQLintersection: When is it all happening?

The conference officially runs from Tuesday, June 11 through Thursday, June 13 with pre-conference and post-conference workshops that extend the show over a total of up to 6 full days. For the full conference, you’ll want to be there from Sunday, June 9 through Friday, June 14.

  • Sunday, June 9 – pre-con day. There are three workshops running:
    • Migrating to SQL Server 2019 with Glenn Berry
    • Developer’s SQL Server Recipe Book with Brent Ozar
    • Leveling up with PowerShell for the DBA with Ben Miller
  • Monday, June 10 – pre-con day. There are three workshops running:
    • Performance Tuning and Optimization for Modern Workloads with Tim Radney and David Pless
    • Performance Troubleshooting using Waits and Latches with Paul S. Randal
    • SQL Server Indexes: What, Why, and How! with Kimberly L. Tripp
  • Tuesday, June 11 through Thursday, June 13 is the main conference. Conference sessions will run all day in multiple tracks:
    • Check out our sessions online here
    • Be sure to check out our cross-conference events and sessions
    • Get your pop-culture trivia and techie-SQL-trivia hat on and join us for SQLafterDark on Wednesday evening, June 12
  • Friday, June 14 is our final day with three post-conference workshops running:
    • SQL Server Reporting Services and Power BI – Reporting Solutions with David Pless and Tim Radney
    • Zero to Hero: Faster SQL Query Performance with Jonathan Kehayias

SQLintersection: Why is it for you?

If you want practical information delivered by speakers that not-only know the technologies but are competent and consistently highly-rated presenters – this is the show for you. You will understand the RIGHT features to troubleshoot and solve your performance and availability problems now!

Check us out: www.SQLintersection.com.

We hope to see you there!

PS – Use the discount code ‘SQLskills’ when you register and receive $50 off registration!