Memory-optimized table support for Sql Server

[AndriySvyryd]

Provide fluent API:

modelBuilder.Entity<Product>().ForSqlServerIsMemoryOptimized();

For on-premise SQL Server a file in a memory-optimized filegroup needs to be added to the database. We will dynamically create it in the migration if it doesn't exist.

The default durability is SCHEMA_AND_DATA we will not provide an API to change it to SCHEMA_ONLY. Likewise we won't have API to set MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to false. Both can be accomplished by editing the migration.

A convention will set indexes to be nonclustered on memory-optimized tables. But the computed columns will will be up to the user to remove.

See https://msdn.microsoft.com/en-us/library/dn133165.aspx for more info on memory-optimized tables. And https://msdn.microsoft.com/en-us/library/dn246937.aspx for detailed description of limitations of memory-optimized tables.

[AndriySvyryd]

@bricelam, @ajcvickers, @anpete, @divega This is what the SQL to 'seamlessly' create the necessary database file and filegroup would look like:

IF SERVERPROPERTY('IsXTPSupported') = 1 AND SERVERPROPERTY('EngineEdition') <> 5
    BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM [sys].[filegroups] [FG] JOIN [sys].[database_files] [F] ON [FG].[data_space_id] = [F].[data_space_id] WHERE [FG].[type] = N'FX' AND [F].[type] = 2)
        BEGIN
        DECLARE @db_name NVARCHAR(MAX) = DB_NAME();
        DECLARE @fg_name NVARCHAR(MAX);
        SELECT TOP(1) @fg_name = [name] FROM [sys].[filegroups] WHERE [type] = N'FX';

        IF @fg_name IS NULL
            BEGIN
            SET @fg_name = @db_name + N'_MODFG';
            EXEC(N'ALTER DATABASE CURRENT ADD FILEGROUP [' + @fg_name + '] CONTAINS MEMORY_OPTIMIZED_DATA;');
            END

        DECLARE @path NVARCHAR(MAX);
        SELECT TOP(1) @path = [physical_name] FROM [sys].[database_files] WHERE charindex('\', [physical_name]) > 0 ORDER BY [file_id];
        IF (@path IS NULL)
            SET @path = '\' + @db_name;

        DECLARE @filename NVARCHAR(MAX) = right(@path, charindex('\', reverse(@path)) - 1);
        SET @filename = REPLACE(left(@filename, len(@filename) - charindex('.', reverse(@filename))), '''', '''''') + N'_MOD';
        DECLARE @new_path NVARCHAR(MAX) = REPLACE(CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS NVARCHAR(MAX)), '''', '''''') + @filename;

        EXEC(N'
            ALTER DATABASE CURRENT
            ADD FILE (NAME=''' + @filename + ''', filename=''' + @new_path + ''')
            TO FILEGROUP [' + @fg_name + '];');
        END
    END

IF SERVERPROPERTY('IsXTPSupported') = 1
    EXEC(N'
        ALTER DATABASE CURRENT
        SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT ON;');

[divega]

cc @jodebrui un case he can think of anything else we could do with the filegroup.

[ErikEJ]

Should it not be:

IF SERVERPROPERTY('IsXTPSupported')  = 1

[AndriySvyryd]

@ErikEJ we don't want to run this on SQL Azure, but it's a good suggestion, so I'll add it to the condition

[jodebrui]

Looks good. For reference, I created a similar script, that adds a filegroup if it's not there (in SQL Server) and configures recommended DB settings:
https://github.com/Microsoft/sql-server-samples/blob/master/samples/features/in-memory/t-sql-scripts/enable-in-memory-oltp.sql

What is your plan for recommended settings? I always recommend:

• MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON
• DB compat level at least 130 (for SQL2016 and Azure DB)

[AndriySvyryd]

@jodebrui Added MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON. At the moment the are no plans to check the compat level, unless we get user feedback

[jodebrui]

Sounds good. Thanks.
Yeah, with compat level you usually want to be careful, so may not want to change it automatically.