Sidebar

SQL Server script to create High Availability database for QIE

+1 vote
1.7K views
asked Aug 7, 2019 by rich-c-2789 (17,610 points)
edited Aug 13, 2019 by rich-c-2789
Applies only to QIE version .47 or later with High Availability running against Microsoft SQL Server 2016 or greater.

3 Answers

+1 vote
 
Best answer

Note: Azure requires Premium (DTU) or Business Critical (vCore) & in-memory OLTP.  Then run

USE qieHA

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;

vs. the below script.

This script applies to QIE version .47 or later licensed for High Availability running against Microsoft SQL Server 2016 or greater.

If you do not have a QIE version .47 or later licensed for High Availability and Microsoft SQL Server 2016 or greater, use this script instead: Is there a script to create the qie database and user on MS SQL Server

-- Create a new database
CREATE DATABASE qieHA
GO

USE qieHA
-- Create the qie user and map it to the qie database
CREATE LOGIN qie
WITH PASSWORD='qie', DEFAULT_DATABASE=qieHA, DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

-- Create a user in a database for a given login
CREATE USER qie FOR LOGIN qie WITH DEFAULT_SCHEMA=[dbo]
GO

-- Make the qie user a member of following groups
ALTER ROLE [db_backupoperator] ADD MEMBER [qie]
ALTER ROLE [db_datareader] ADD MEMBER [qie]
ALTER ROLE [db_datawriter] ADD MEMBER [qie]
ALTER ROLE [db_ddladmin] ADD MEMBER [qie]
ALTER ROLE [db_owner] ADD MEMBER [qie]
GO

-- Set the recovery model to simple
ALTER DATABASE CURRENT SET RECOVERY SIMPLE;
GO

 

-- High Availability support
-- The following Database modification are specific to High Availability requirements
-- Change this setting to avoid this error:
-- The operation 'AUTO_CLOSE' is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.
-- when adding a MEMORY_OPTIMIZED_DATA filegroup below (ref 1 and 2)
ALTER DATABASE CURRENT SET AUTO_CLOSE OFF
GO

-- Validate support for then add the MEMORY_OPTIMIZED_DATA filegroup and alter setting to support it.
-- 1. validate that In-Memory OLTP is supported
IF SERVERPROPERTY(N'IsXTPSupported') = 0
BEGIN
PRINT N'Error: In-Memory OLTP is not supported for this server edition or database pricing tier.';
END

IF DB_ID() < 5
BEGIN
PRINT N'Error: In-Memory OLTP is not supported in system databases. Connect to a user database.';
END
ELSE
BEGIN
BEGIN TRY;
-- 2. add MEMORY_OPTIMIZED_DATA filegroup when not using Azure SQL DB
IF SERVERPROPERTY('EngineEdition') != 5
BEGIN
DECLARE @SQLDataFolder nvarchar(max) = cast(SERVERPROPERTY('InstanceDefaultDataPath') as nvarchar(max))
DECLARE @MODName nvarchar(max) = DB_NAME() + N'_mod';
DECLARE @MemoryOptimizedFilegroupFolder nvarchar(max) = @SQLDataFolder + @MODName;

DECLARE @SQL nvarchar(max) = N'';

-- Add a MEMORY_OPTIMIZED_DATA filegroup. Only one filegroup per database can be created to contain MEMORY_OPTIMIZED_DATA. (ref 3)
IF NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE type = N'FX')
BEGIN
SET @SQL = N'
ALTER DATABASE CURRENT
ADD FILEGROUP ' + QUOTENAME(@MODName) + N' CONTAINS MEMORY_OPTIMIZED_DATA;';
EXECUTE (@SQL);
END;

-- Add container in the filegroup in the default data folder. Note: for best performance SQL Server recomends this should point to a folder on a separate drive. (ref 3)
IF NOT EXISTS (SELECT * FROM sys.database_files WHERE data_space_id IN (SELECT data_space_id FROM sys.filegroups WHERE type = N'FX'))
BEGIN
SET @SQL = N'
ALTER DATABASE CURRENT
ADD FILE (name = N''' + @MODName + ''', filename = '''
+ @MemoryOptimizedFilegroupFolder + N''')
TO FILEGROUP ' + QUOTENAME(@MODName);
EXECUTE (@SQL);
END
END

-- 3. Set compat level to 130 if it is lower. (needed for parallel queries and auto-update of statistics) (ref 4).
IF (SELECT compatibility_level FROM sys.databases WHERE database_id=DB_ID()) < 130
ALTER DATABASE CURRENT SET COMPATIBILITY_LEVEL = 130

-- 4. Enable MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT for the database. To avoid the
-- need to use the WITH (SNAPSHOT) hint for ad hoc queries accessing memory-optimized
-- tables (ref 5).

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = ON;
END TRY
BEGIN CATCH
PRINT N'Error enabling In-Memory OLTP';
IF XACT_STATE() != 0
ROLLBACK;
THROW;
END CATCH;
END;
GO

Some notes regarding the above script:

High Availability: This script builds on the prior QIE database script. Everything after the following comment "-- High Availability support" is used to prepare the database for an High Availability deployment of QIE.

Single-disk systems: This script was written with with single-disk systems in mind. It uses the default file location of the data path to create the container in the filegroup. However, for better performance Microsoft recommends the following (ref 6):

"By default, the data and transaction logs are put on the same drive and path. This is done to handle single-disk systems. However, this may not be optimal for production environments. We recommend that you put data and log files on separate disks."

Filegroups:In this script we create a filegroup solely to store non durable (SCHEMA_ONLY) MEMORY_OPTIMIZED_DATA. Microsoft states (ref 7):

"Even though data rows from SCHEMA_ONLY tables are not persisted and the metadata for memory-optimized tables and natively compiled stored procedures is stored in the traditional catalogs, the In-Memory OLTP engine still requires a memory-optimized filegroup for SCHEMA_ONLY memory-optimized tables to provide a uniform experience for databases with memory-optimized tables."

With this in mind, disk space and IO are not taxed. However, this may change in later versions of QIE. Regardless it might be a good time to review microsofts recommendation regarding files and filegroup management:

o Most databases will work well with a single data file and a single transaction log file.

o If you use multiple data files, create a second filegroup for the additional file and make that filegroup the default filegroup. In this way, the primary file will contain only system tables and objects.

o To maximize performance, create files or filegroups on different available disks as possible. Put objects that compete heavily for space in different filegroups.

o Use filegroups to enable placement of objects on specific physical disks.

o Put different tables used in the same join queries in different filegroups. This will improve performance, because of parallel disk I/O searching for joined data.

o Put heavily accessed tables and the nonclustered indexes that belong to those tables on different filegroups. This will improve performance, because of parallel I/O if the files are located on different physical disks.

o Do not put the transaction log file(s) on the same physical disk that has the other files and filegroups.

 

answered Aug 7, 2019 by rich-c-2789 (17,610 points)
edited Nov 15 by rich-c-2789
commented Mar 11 by rich-c-2789 (17,610 points)
To use this script for high availability which requires the use of in-memory or memory optimized tables on an Azure Sql Database vs Sql Server, see this link:

https://learn.microsoft.com/en-us/azure/azure-sql/database/in-memory-oltp-overview?view=azuresql

Which states:

“Note
In-memory technologies are available in the Premium and Business Critical tiers of Azure SQL Database.”
0 votes

Verify In-Memory OLTP Support on Azure: Confirm that your database supports In-Memory OLTP by executing the following Transact-SQL query:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsXTPSupported');

Select an Appropriate Service Tier: Ensure your database is in the Premium (DTU) or Business Critical (vCore) service tiers, as these support In-Memory OLTP. The Hyperscale service tier supports a subset of In-Memory OLTP objects but does not include memory-optimized tables.

answered Nov 15 by rich-c-2789 (17,610 points)
...