Closed AndriySvyryd closed 8 years ago
@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;');
cc @jodebrui un case he can think of anything else we could do with the filegroup.
Should it not be:
IF SERVERPROPERTY('IsXTPSupported') = 1
@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
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:
@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
Sounds good. Thanks. Yeah, with compat level you usually want to be careful, so may not want to change it automatically.
Provide fluent API:
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 toSCHEMA_ONLY
. Likewise we won't have API to setMEMORY_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.