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?