|
| 1 | +-- The below scipt enables the use of In-Memory OLTP in the current database, |
| 2 | +-- provided it is supported in the edition / pricing tier of the database. |
| 3 | +-- It does the following: |
| 4 | +-- 1. Validate that In-Memory OLTP is supported. |
| 5 | +-- 2. In SQL Server, it will add a MEMORY_OPTIMIZED_DATA filegroup to the database |
| 6 | +-- and create a container within the filegroup in the default data folder. |
| 7 | +-- 3. Change the database compatibility level to 130 (needed for parallel queries |
| 8 | +-- and auto-update of statistics). |
| 9 | +-- 4. Enables the database option MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT to avoid the |
| 10 | +-- need to use the WITH (SNAPSHOT) hint for ad hoc queries accessing memory-optimized |
| 11 | +-- tables. |
| 12 | +-- |
| 13 | +-- Applies To: SQL Server 2014 (or higher); Azure SQL Database |
| 14 | +-- Author: Jos de Bruijn (Microsoft) |
| 15 | +-- Last Updated: 2016-05-02 |
| 16 | + |
| 17 | +SET NOCOUNT ON; |
| 18 | +SET XACT_ABORT ON; |
| 19 | + |
| 20 | +-- 1. validate that In-Memory OLTP is supported |
| 21 | +IF SERVERPROPERTY(N'IsXTPSupported') = 0 |
| 22 | +BEGIN |
| 23 | + PRINT N'Error: In-Memory OLTP is not supported for this server edition or database pricing tier.'; |
| 24 | +END |
| 25 | +IF DB_ID() < 5 |
| 26 | +BEGIN |
| 27 | + PRINT N'Error: In-Memory OLTP is not supported in system databases. Connect to a user database.'; |
| 28 | +END |
| 29 | +ELSE |
| 30 | +BEGIN |
| 31 | + BEGIN TRY; |
| 32 | +-- 2. add MEMORY_OPTIMIZED_DATA filegroup when not using Azure SQL DB |
| 33 | + IF SERVERPROPERTY('EngineEdition') != 5 |
| 34 | + BEGIN |
| 35 | + DECLARE @SQLDataFolder nvarchar(max) = cast(SERVERPROPERTY('InstanceDefaultDataPath') as nvarchar(max)) |
| 36 | + DECLARE @MODName nvarchar(max) = DB_NAME() + N'_mod'; |
| 37 | + DECLARE @MemoryOptimizedFilegroupFolder nvarchar(max) = @SQLDataFolder + @MODName; |
| 38 | + |
| 39 | + DECLARE @SQL nvarchar(max) = N''; |
| 40 | + |
| 41 | + -- add filegroup |
| 42 | + IF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE type = N'FX') |
| 43 | + BEGIN |
| 44 | + SET @SQL = N' |
| 45 | +ALTER DATABASE CURRENT |
| 46 | +ADD FILEGROUP ' + QUOTENAME(@MODName) + N' CONTAINS MEMORY_OPTIMIZED_DATA;'; |
| 47 | + EXECUTE (@SQL); |
| 48 | + |
| 49 | + -- add container in the filegroup |
| 50 | + IF NOT EXISTS (SELECT * FROM sys.database_files WHERE data_space_id IN (SELECT data_space_id FROM sys.filegroups WHERE type = N'FX')) |
| 51 | + BEGIN |
| 52 | + SET @SQL = N' |
| 53 | +ALTER DATABASE CURRENT |
| 54 | +ADD FILE (name = N''' + @MODName + ''', filename = ''' |
| 55 | + + @MemoryOptimizedFilegroupFolder + N''') |
| 56 | +TO FILEGROUP ' + QUOTENAME(@MODName); |
| 57 | + EXECUTE (@SQL); |
| 58 | + END |
| 59 | + END; |
| 60 | + END |
| 61 | + |
| 62 | + -- 3. set compat level to 130 if it is lower |
| 63 | + IF (SELECT compatibility_level FROM sys.databases WHERE database_id=DB_ID()) < 130 |
| 64 | + ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130 |
| 65 | + |
| 66 | + -- 4. enable MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT for the database |
| 67 | + ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON; |
| 68 | + |
| 69 | + |
| 70 | + END TRY |
| 71 | + BEGIN CATCH |
| 72 | + PRINT N'Error enabling In-Memory OLTP'; |
| 73 | + IF XACT_STATE() != 0 |
| 74 | + ROLLBACK; |
| 75 | + THROW; |
| 76 | + END CATCH; |
| 77 | +END; |
0 commit comments