In continuation with my last post, I would like to further move on to the topic by starting with the limitations of In-Memory OLTP feature.

“Entire Data in Memory” looks such a wonderful idea to improve the performance by reducing the locking/latching etc, however, Is it true to adopt for all systems? NO. There are lots of restrictions (at least in SQL Server 2014 and hoping many would be taking off in future versions) with In-Memory OLTP feature.
You can refer those limitation in the below MSDN

Transact-SQL Constructs Not Supported by In-Memory OLTP

Here, I would like to share a mechanism to verify the compatibility level of existing environment to migrate to In-Memory Tables. By now(once you read the above link), as you all know the limitations are more, I would like to split the script in multiple snippets for simplicity.

Please refer the below script to verify the compatibility level of your environment at Server and Data base level.

SET NOCOUNT ON

PRINT '************************************************'
PRINT 'DATABASE SERVER COMPATIBILITY CHECK'
PRINT '************************************************'
IF NOT EXISTS(SELECT 1 FROM (SELECT SUBSTRING(@@VERSION,22,4)) A (VERSIONNUMBER) WHERE VERSIONNUMBER >=2014)
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT IN SQL SERVER 2014 OR LATER VERSIONS.'

IF EXISTS(SELECT 1 FROM (SELECT @@VERSION) A (BITVERSION) WHERE CHARINDEX('(64-BIT)',BITVERSION)=0)
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT IN 64 BIT VERSIONS.'

IF EXISTS(SELECT * FROM MASTER.SYS.SERVER_TRIGGERS)
	PRINT HOST_NAME() + ' IS ENABLED WITH SERVER LEVEL TRIGGER AND IN-MEMORY OLTP WILL NOT SUPPORT SERVER LEVEL TRIGGERS.'


PRINT '************************************************'
PRINT 'DATABASE COMPATIBILITY CHECK'
PRINT '************************************************'
	
IF (DB_NAME() NOT IN (SELECT DB_NAME(DATABASE_ID) FROM SYS.MASTER_FILES WHERE TYPE_DESC = 'FILESTREAM'))
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT WITH MEMORY_OPTIMIZED_DATA FILEGROUP. THE CURRENT DATABASE WILL NOT SUPPORT.'
	
IF (DB_NAME() IN (SELECT NAME FROM SYS.DATABASES WHERE IS_MERGE_PUBLISHED & IS_SUBSCRIBED & IS_DISTRIBUTOR & IS_PUBLISHED = 1))
	PRINT DB_NAME() + 'HAS BEEN REPLICATED AND IN-MEMORY OLTP WILL NOT SUPPORT REPLICATED DATABASE'

IF EXISTS(SELECT * FROM SYS.DATABASES WHERE DB_ID () = DATABASE_ID AND IS_AUTO_CLOSE_ON = 1)
	PRINT DB_NAME() + 'HAS BEEN CONFIGURED WITH AUTO CLOSE OPTION AND IN-MEMORY OLTP WILL NOT SUPPORT AUTO_CLOSE FEATURE'

We will cover more on other aspects like datatypes/constraints etc in future posts, until then, happy learning….