Error creating memory optimized filegroup in SSDT

Question

I am attempting to add a memory optimized filegroup to a SQL server database project in SSDT. This is for SQL Server 2017, using Visual Studio 2017.

However compiling the project (pressing F5 to build) is resulting in an error. This error does not occur when deploying (via Deploy).

The filegroup is created as normal (with the SQLCMD variable as scripted by SSDT):

ALTER DATABASE [$(DatabaseName)]
ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA

However this results in an error:

The operation 'AUTO_CLOSE' is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.

However, database settings show that auto close is indeed disabled:

The deployment script generated by SSDT for some reason is creating the database, creating the filegroup, and then only setting AUTO_CLOSE off afterward. It is possible this is resulting in the error:

CREATE DATABASE [$(DatabaseName)]
ON 
PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
PRINT N'Creating [MemoryOptimizedFilegroup]...';

GO
ALTER DATABASE [$(DatabaseName)]
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

GO
ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [MemoryOptimizedFilegroup_69323650], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_MemoryOptimizedFilegroup_69323650.mdf') TO FILEGROUP [MemoryOptimizedFilegroup];

GO
USE [$(DatabaseName)];

GO
IF EXISTS (SELECT 1
    FROM   [master].[dbo].[sysdatabases]
    WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ANSI_NULLS ON,
            ANSI_PADDING ON,
            ANSI_WARNINGS ON,
            ARITHABORT ON,
            CONCAT_NULL_YIELDS_NULL ON,
            NUMERIC_ROUNDABORT OFF,
            QUOTED_IDENTIFIER ON,
            ANSI_NULL_DEFAULT ON,
            CURSOR_DEFAULT LOCAL,
            CURSOR_CLOSE_ON_COMMIT OFF,
            AUTO_CREATE_STATISTICS ON,
            AUTO_SHRINK OFF,
            AUTO_UPDATE_STATISTICS ON,
            RECURSIVE_TRIGGERS OFF 
        WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [$(DatabaseName)]
        SET AUTO_CLOSE OFF 
        WITH ROLLBACK IMMEDIATE;
END

I am not certain whether AUTO_CLOSE is enabled or disabled by default after CREATE DATABASE. If ON by default, it seems that SSDT is generating the deployment script in the wrong order. If OFF by default, then I don't understand why the error exists.

Has anyone had success creating a memory optimized file group in an SSDT project?

Answer 1

I am not certain whether AUTO_CLOSE is enabled or disabled by default after CREATE DATABASE. If ON by default, it seems that SSDT is generating the deployment script in the wrong order. If OFF by default, then I don't understand why the error exists.

It seems LocalDB has hard coded use of AUTO_CLOSE, ignoring model

LocalDB / SQLEXPRESS

True, when connecting to localdb and running a standard create database command

CREATE DATABASE TEST

GO

SELECT name,database_id
FROM sys.databases 
WHERE is_auto_close_on = 1;

name    database_id
TEST    5

It appears that the is_auto_close_on is set to 1 even when model has auto_close disabled.

Normally the default settings are based on the model system database.

---

Resolving this issue in theory

You could create a trigger that automatically changes the auto close to 0 for newly created databases. This is a bit of a hacky approach but works.

Try / implement at your own risk.

CREATE TRIGGER Change_Auto_Close 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    DECLARE 
        @DatabaseName NVARCHAR(128)
        ,@SQL NVARCHAR(4000);
    SELECT 
        @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')

    SET @SQL = 'USE [master] ALTER DATABASE ['+@DatabaseName+'] SET AUTO_CLOSE OFF WITH NO_WAIT;';

    IF @@TRANCOUNT = 1
    COMMIT TRAN; -- commit previous tran, otherwise we cannot alter the database
    -- PRINT @SQL;
    EXEC (@SQL);
    BEGIN TRAN; --begin new tran as not get error that the transaction ended in the trigger

GO

Ideas from here and here

---

Other editions

It could be that your model database has is_auto_close_on = 1 as databases are created as a copy of model. Below are some tests with auto_close enabled and disabled on the model database.

---

Set auto close on for model

USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE ON WITH NO_WAIT;
GO

Create a default database

CREATE DATABASE AutoClosedDB;

Validate the auto close state of the newly created database

SELECT name, is_auto_close_on 
FROM sys.databases 
WHERE name = 'AutoClosedDB';

Result

name            is_auto_close_on
AutoClosedDB    1

---

Set auto close off for model

USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE OFF WITH NO_WAIT;
GO

Create a database with default settings

CREATE DATABASE NoAutoClosedDB;

Validate the auto close state of the newly created database

SELECT name, is_auto_close_on 
FROM sys.databases 
WHERE name = 'NoAutoClosedDB';

Result

name            is_auto_close_on
NoAutoClosedDB  0

---

Try creating a memory optimized filegroup on both databases

ALTER DATABASE NoAutoClosedDB
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

Succeeds

ALTER DATABASE AutoClosedDB
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

Fails with error message:

Msg 10794, Level 16, State 125, Line 33 The operation 'AUTO_CLOSE' is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.

---

To validate the auto close state on the model database, run below script

SELECT name, is_auto_close_on 
FROM sys.databases 
WHERE name = 'model';

Disable auto close with

USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE OFF WITH NO_WAIT;
GO

Answer 2

Further to the answer by Randi Vertongen above, another solution is to install a SQL Server instance, and edit the SSDT Project in the Debug settings to connect to that instead of LocalDB.

For example, I have now installed a named instance of SQL Server only for SSDT builds (when pressing F5 for debug build test), and the default instance I use for actual deployments (when using Publish).

This overcomes the limitations of LocalDB, of which default AUTO_CLOSE is only one.