Recently, while I was on vacation with my family in Australia, I received an email about InMemory OLTP functionality in SQL Server. It got me thinking about the importance of compatibility and how it applies not only to toys but also to databases.
My Recent Vacation
As a workaholic, I always make sure to dedicate quality time to my family. During my daughter’s vacation, I took her to Australia, and it turned out to be an amazing experience for all of us. We explored the city leisurely, enjoying the sights and sounds.
During our trip, I had a special time with my daughter where we strolled through the local market. She was fascinated by a toy of different shapes and insisted on getting it because her old blocks had broken. Being her special time, I happily obliged and bought her the toy. However, when we got home, she realized that the new blocks were not compatible with the old ones. It was a valuable lesson for her – not all toys are compatible with each other.
Let’s Get Back to SQL
After returning from my vacation, I had a long list of emails waiting for me. Among them, I found an interesting email stating that some of my InMemory OLTP scripts were not working in a particular environment. This sparked my curiosity, and I decided to investigate the issue with the developer.
During my analysis, I discovered two fundamental observations that I shared with the developer:
- A new database cannot support InMemory OLTP if it is restored from a database that was created before the InMemory OLTP features became active.
- To check if a database supports InMemory OLTP, you can run the following T-SQL SELECT statement:
SELECT DatabasePropertyEx(DB_NAME(), 'IsXTPSupported');
GO
If the result of the above query is 1, it means that the database supports InMemory OLTP.
The developer ran the query and discovered that there was an issue with his environment, which was causing it to not support InMemory OLTP objects. However, when he tried the same script on a different database, everything worked perfectly. This small script helped us identify the problem and find a solution.
I’m curious to know how many of you have encountered the need to use InMemory OLTP functionality in your environments. Have you faced any compatibility issues? Let me know in the comments.
Remember, just like toys, not all databases are compatible with each other. Understanding the compatibility requirements of SQL Server features can save you a lot of time and effort in troubleshooting.