SQL SERVER – InMemory Tables on SQL Azure DB

There have been tons of enhancements in the InMemory space with SQL Server 2016. I am due to write about them in this blog. Having said that, one of my DBA friend asked me based on the Pluralsight course on SQL Server 2014 Administration – Does SQL Azure support InMemory capabilities?

This feature was not available for quite some time now, but ever since the V12 version was introduced. I saw Azure now supports InMemory. On reading some of the SQLAzureDB documentation, I saw they have extended this support. Immediately, I created a database on Azure SQLDB and connected it with SQL Server Management Studio. Our Object Explorer will look like below:

Create a Query window and let us identify if our DB (sqldude as shown above) supports our InMemory objects (tables and procedures).

SELECT SERVERPROPERTY('IsXTPSupported');
GO
CREATE TABLE tbl_InMemory
( ID INT NOT NULL,
Name VARCHAR(50) NOT NULL
CONSTRAINT Const_InMemory PRIMARY KEY NONCLUSTERED HASH (ID) WITH (BUCKET_COUNT=1000000)
) WITH (MEMORY_OPTIMIZED=ON)
GO

As you can see the above SERVERPROPERTY it returns 1 to confirm this database supports InMemory objects.

Yet another method to identify if a database support InMemory OLTP capability, we can use the below function:

SELECT DATABASEPROPERTYEX ('sqldude', 'IsXTPSupported');

This will also return 1 if it supports. This can be used for Azure DB’s and on-premise installations too. At this point in time, I wanted to bring some restrictions of using InMemory OLTP with Azure:

• It can be only used in premium DBs
• It can be used only in new DBs. You cannot use in copies of pre-existing database unless the pre-existing database was InMemory OLTP enabled from private preview.
• Downgrade: Databases with InMemory OLTP objects cannot be downgraded to basic or standard editions. But if you remove all InMemory OLTP objects, then we can downgrade.
• Performance tier downgrade within premium is blocked for databases with InMemory OLTP objects.
• A database with InMemory OLTP objects cannot be downgraded to a Premium performance tier with a smaller memory size

I am sure I might be missing something, but many a times the Azure SQL database restrictions are time bound and I am sure many be 6 months or 1 year down the lane, some of these might go away so always refer to the documentation for the latest restrictions (if any).

I am curious to understand how many of you are Azure SQL database users? What have you been using it for? What are some of the enhancements on Azure SQL DB’s that you have got excited about? Let me know via comments.

Reference: Pinal Dave (https://blog.sqlauthority.com)