Installing FMS repository on an Azure SQL Managed Instance (MI) (4226446)

Can Foglight use an Azure SQL Managed Instance as a Foglight Management Server (FMS) repository?

WORKAROUND

Installing Foglight with the FMS repository on an Azure SQL Managed Instance (MI) is not officially supported as it requires manual intervention during the installation as described below.

An Azure SQL Managed Instance can be used as a FMS repository for Foglight 5.9.8+.

To install the FMS 5.9.8+ repository on an Azure SQL Managed Instance

1. Run the Foglight installer

2. Step "Foglight Repository Database Type"

     a). Select "SQL Server(External database)"
     b). Click next

3. Step "Foglight Repository(SQL Server)"

     a). Fill "Server name", e.g "sql-mi.xxxxxx.database.windows.net"
     b). Set username/password/database name for foglight. Note: the credential is new db account used by fogight server, not sql mi's admin credential
     c). Select "Manually create the repository database"
     d). Click "Generate"
     e). Modify script
          i). Open /scripts/sql/sqlsvr_create_db.sql
          ii). Change the following SQL  

-- Get the SQL Server data path
DECLARE @data_path nvarchar(256)
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1)
DECLARE @model_data_size_kb int
SET @model_data_size_kb = (SELECT SUM(size) * 8
                  FROM master.sys.master_files
                  WHERE database_id = 3 and [type] = 0)
IF @model_data_size_kb < (250 * 1024)
    SET @model_data_size_kb = 250 * 1024
DECLARE @model_log_size_kb int
SET @model_log_size_kb = (SELECT SUM(size) * 8
                  FROM master.sys.master_files
                  WHERE database_id = 3 and [type] = 1)
IF @model_log_size_kb < (50 * 1024)
    SET @model_log_size_kb = 50 * 1024
DECLARE @schema_data_size_str nvarchar(12)
DECLARE @schema_log_size_str nvarchar(12)
SET @schema_data_size_str = CAST(@model_data_size_kb AS nvarchar(12))
SET @schema_log_size_str = CAST(@model_log_size_kb AS nvarchar(12))
-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE test1
ON
( NAME = test1_dat1,
    FILENAME = '''+ @data_path + 'test1.mdf'',
    SIZE = ' + @schema_data_size_str + 'KB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 25MB )
LOG ON
( NAME = test1_log1,
    FILENAME = '''+ @data_path + 'test1.ldf'',
    SIZE = ' + @schema_log_size_str + 'KB,
    MAXSIZE = 10GB,
    FILEGROWTH = 5MB )
COLLATE SQL_Latin1_General_CP1_CS_AS'
);
GO

to

-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE test1 COLLATE SQL_Latin1_General_CP1_CS_AS');
GO

     f). Create DB manually using DB tools(e.g. Microsoft SQL Server Management Studio)
          i). Run modified  /scripts/sql/sqlsvr_create_db.sql
          ii). Switch database to the database we created, then run script /scripts/sql/sqlsvr_create_schema.sql
          iii). Switch database to the database we created, then run script /scripts/sql/sqlsvr_populate_db.sql
     g). Click "Next"

4. Continue FMS installer steps and finish the installation.


STATUS

Enhancement request FOG-1499 has been submitted to development for consideration in a future release of the Foglight Management Server.