If you’re forced to work with MS SQL Server, here is my solution. On a single database, say IgnitionDB define three sets of twelve-month physical files for each of the three types of data
- alarm events
- agent and audit events
- tag history
The management tool can be packaged up as a UDT of which you have a single instance. The monthly job of partition management involves three TRUNCATE statements to maintain three sets of trailing months of alarm, agent+audit and tag history. Cavet is your system can store at most 12 months of data.
Note: Be sure to switch off all of Ignition’s data pruning and data partitioning (tag history) mechanisms.
-------------------------------------------------------------------------------
-- Create the IgnitionDB database
--
-- Assumptions:
-- 1) One (1) TB physical disk space for data
-- 2) 500 GB physical disk space for transaction logs
--
-- Requirements:
-- 1) Pre-allocation of physical disk space for six months data
-- 2) Log file is sized 50% of the size allocated to data files
--
-- Notes:
-- 1) The CREATE DATABASE statement may take circa 15 minutes to execute
-- because of the log file specification.
-------------------------------------------------------------------------------
USE master;
DROP DATABASE IF EXISTS IgnitionDB;
GO
-- Create subdirectory for data and transaction log
EXEC master.dbo.xp_create_subdir 'F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\';
EXEC master.dbo.xp_create_subdir 'I:\Microsoft SQL Server\MSSQL\TLogs\';
CREATE DATABASE IgnitionDB
/* The single PRIMARY file serves for all unpartitioned tables */
ON PRIMARY (NAME=IgnitionDB_Primary,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\PRIMARY.MDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
/* Twelve _DATA files serve for all monthly partitioned tables excluding Ignition alarm_events and Ignition tag history */
FILEGROUP IgnitionDB_01 (NAME=IgnitionDB_01_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\01_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_02 (NAME=IgnitionDB_02_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\02_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_03 (NAME=IgnitionDB_03_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\03_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_04 (NAME=IgnitionDB_04_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\04_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_05 (NAME=IgnitionDB_05_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\05_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_06 (NAME=IgnitionDB_06_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\06_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_07 (NAME=IgnitionDB_07_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\07_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_08 (NAME=IgnitionDB_08_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\08_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_09 (NAME=IgnitionDB_09_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\09_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_10 (NAME=IgnitionDB_10_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\10_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_11 (NAME=IgnitionDB_11_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\11_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_12 (NAME=IgnitionDB_12_DATA,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\12_DATA.NDF',SIZE=4GB,FILEGROWTH=2GB,MAXSIZE=UNLIMITED),
/* Twelve _ALRM files serve for monthly partitions of Ignition's alarm_events and alarm_event_data tables */
FILEGROUP IgnitionDB_13 (NAME=IgnitionDB_13_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\13_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_14 (NAME=IgnitionDB_14_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\14_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_15 (NAME=IgnitionDB_15_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\15_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_16 (NAME=IgnitionDB_16_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\16_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_17 (NAME=IgnitionDB_17_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\17_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_18 (NAME=IgnitionDB_18_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\18_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_19 (NAME=IgnitionDB_19_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\19_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_20 (NAME=IgnitionDB_20_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\20_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_21 (NAME=IgnitionDB_21_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\21_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_22 (NAME=IgnitionDB_22_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\22_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_23 (NAME=IgnitionDB_23_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\23_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_24 (NAME=IgnitionDB_24_ALRM,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\24_ALRM.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
/* Twelve _TAGS files serve for monthly partitions of Ignition's tag history tables */
FILEGROUP IgnitionDB_25 (NAME=IgnitionDB_25_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\25_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_26 (NAME=IgnitionDB_26_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\26_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_27 (NAME=IgnitionDB_27_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\27_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_28 (NAME=IgnitionDB_28_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\28_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_29 (NAME=IgnitionDB_29_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\29_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_30 (NAME=IgnitionDB_30_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\30_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_31 (NAME=IgnitionDB_31_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\31_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_32 (NAME=IgnitionDB_32_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\32_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_33 (NAME=IgnitionDB_33_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\33_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_34 (NAME=IgnitionDB_34_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\34_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_35 (NAME=IgnitionDB_35_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\35_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED),
FILEGROUP IgnitionDB_36 (NAME=IgnitionDB_36_TAGS,FILENAME='F:\Microsoft SQL Server\MSSQL\Data\IgnitionDB\36_TAGS.NDF',SIZE=12GB,FILEGROWTH=6GB,MAXSIZE=UNLIMITED)
LOG ON ( NAME=IgnitionDB_Log, FILENAME='I:\Microsoft SQL Server\MSSQL\TLogs\IgnitionDB_log.LDF', SIZE=200GB,FILEGROWTH=50GB,MAXSIZE=400GB);
GO
-------------------------------------------------------------------------------
-- Create partition function and schem on the IgnitionDB
-------------------------------------------------------------------------------
USE IgnitionDB;
GO
-- https://stackoverflow.com/questions/55365454/sql-server-partitioning-a-table-by-day-of-month
CREATE PARTITION FUNCTION PF_BY_MONTH (INT)
AS RANGE LEFT FOR VALUES (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12);
GO
/* https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact-sql
When you specify the primary filegroup in file_group_name [ ,...n], PRIMARY must be delimited,
as in [PRIMARY], because it is a keyword.
*/
CREATE PARTITION SCHEME PS_DATA_BY_MONTH AS PARTITION PF_BY_MONTH TO (
IgnitionDB_01, IgnitionDB_02, IgnitionDB_03, IgnitionDB_04, IgnitionDB_05, IgnitionDB_06,
IgnitionDB_07, IgnitionDB_08, IgnitionDB_09, IgnitionDB_10, IgnitionDB_11, IgnitionDB_12, [PRIMARY] );
GO
CREATE PARTITION SCHEME PS_ALARMS_BY_MONTH AS PARTITION PF_BY_MONTH TO (
IgnitionDB_13, IgnitionDB_14, IgnitionDB_15, IgnitionDB_16, IgnitionDB_17, IgnitionDB_18,
IgnitionDB_19, IgnitionDB_20, IgnitionDB_21, IgnitionDB_22, IgnitionDB_23, IgnitionDB_24, [PRIMARY] );
GO
CREATE PARTITION SCHEME PS_TAGHISTORY_BY_MONTH AS PARTITION PF_BY_MONTH TO (
IgnitionDB_25, IgnitionDB_26, IgnitionDB_27, IgnitionDB_28, IgnitionDB_29, IgnitionDB_30,
IgnitionDB_31, IgnitionDB_32, IgnitionDB_33, IgnitionDB_34, IgnitionDB_35, IgnitionDB_36, [PRIMARY] );
GO
-------------------------------------------------------------------------------
-- Create TestPartitions table and verify that the partitioned table is working
-------------------------------------------------------------------------------
use IgnitionDB;
GO
DROP TABLE IF EXISTS TestPartitions ;
GO
CREATE TABLE TestPartitions (
dt datetime NOT NULL,
str varchar(max) NOT NULL,
partition AS MONTH(dt) PERSISTED,
PRIMARY KEY CLUSTERED ([dt] ASC, [partition]),
)
ON PS_DATA_BY_MONTH (partition);
GO
with cte (dt,str) as (
select '20210101', '2021.01.01' UNION
select '20210531', '2021.05.31' UNION
select '20211231', '2021.12.31' UNION
select '20220101', '2022.01.01' UNION
select '20220201', '2022.02.01' UNION
select '20220301', '2022.03.01' UNION
select '20220401', '2022.04.01' UNION
select '20221231', '2022.12.31' UNION
select '20270101', '2027.01.01' UNION
select '20270201', '2027.02.01' UNION
select '20270301', '2027.03.01' UNION
select '20270401', '2027.04.01' UNION
select '20271231', '2027.12.31' UNION
select '20281231', '2028.12.31'
)
insert into TestPartitions -- (dt, str)
select * from cte;
Go
-- https://www.mssqltips.com/sqlservertip/4436/sql-server-2016-truncate-table-with-partitions/
SELECT partition_number, rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestPartitions'
AND rows > 0;
/* Table Rows with Partition Number
Users can find the partition number with each row of the table as well.
Users can bifurcate the row allocation to the logical partition number
with the help of a $PARTITION() function.
*/
SELECT $PARTITION.PF_BY_MONTH(partition) AS PartitionNumber, * FROM TestPartitions;
SELECT DISTINCT $PARTITION.PF_BY_MONTH(partition) AS PartitionNumber FROM TestPartitions;
/* https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql#permissions
Permissions
The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions
default to the table owner, members of the sysadmin fixed server role, and the db_owner
and db_ddladmin fixed database roles, and are not transferable. However, you can
incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure,
and grant appropriate permissions to the module using the EXECUTE AS clause.
*/
TRUNCATE TABLE TestPartitions WITH (PARTITIONS(1, 2, 3, 4 TO 12));
GO
SELECT $PARTITION.PF_BY_MONTH(partition) AS PartitionNumber, * FROM TestPartitions;
DROP TABLE IF EXISTS TestPartitions ;
GO
-------------------------------------------------------------------------------
-- CREATE IGNITION ALARM DATA MODEL (2 tables)
-- NOTE: Disable data pruning in gateway's alarm journal config's
-- https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/deterministic-and-nondeterministic-functions
-------------------------------------------------------------------------------
DROP TABLE IF EXISTS alarm_events ;
DROP TABLE IF EXISTS alarm_event_data ;
SET ANSI_NULLS ON ;
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE alarm_events (
[id] [int] IDENTITY(1,1) NOT NULL,
[eventid] [nvarchar](255) NULL,
[source] [nvarchar](255) NULL INDEX alarm_events_ix1 NONCLUSTERED,
[displaypath] [nvarchar](255) NULL INDEX alarm_events_ix2 NONCLUSTERED,
[priority] [int] NULL INDEX alarm_events_ix3 NONCLUSTERED,
[eventtype] [int] NULL INDEX alarm_events_ix4 NONCLUSTERED,
[eventflags] [int] NULL INDEX alarm_events_ix5 NONCLUSTERED,
[eventtime] [datetime] NOT NULL DEFAULT CURRENT_TIMESTAMP,
[partition] AS MONTH([eventtime]) PERSISTED,
PRIMARY KEY CLUSTERED ([id] ASC, [partition]),
INDEX alarm_events_ix6 NONCLUSTERED ([eventtime], [eventtype]),
INDEX alarm_events_ix7 NONCLUSTERED ([eventid], [eventtype])
) ON PS_ALARMS_BY_MONTH ([partition]);
CREATE TABLE alarm_event_data (
[id] [int] NOT NULL,
[propname] [nvarchar](255) NOT NULL,
[dtype] [int] NOT NULL,
[intvalue] [bigint] DEFAULT NULL,
[floatvalue] [float] DEFAULT NULL,
[strvalue] [nvarchar](max) DEFAULT NULL,
[oninsert] [datetime] DEFAULT CURRENT_TIMESTAMP, -- deterministic partitioning
[partition] AS MONTH([oninsert]) PERSISTED,
PRIMARY KEY CLUSTERED ([id] ASC, [propname], [partition])
) ON PS_ALARMS_BY_MONTH ([partition]);
GO
-------------------------------------------------------------------------------
-- CREATE IGNITION AGENT & AUDIT EVENT TABLES
-- NOTE: Disable data pruning in gateway's audit log config's
-------------------------------------------------------------------------------
DROP TABLE IF EXISTS audit_events ;
DROP TABLE IF EXISTS agent_events ;
CREATE TABLE audit_events (
[AUDIT_EVENTS_ID] [int] IDENTITY(1,1) NOT NULL,
[EVENT_TIMESTAMP] [datetime] DEFAULT CURRENT_TIMESTAMP,
[ACTOR] [nvarchar](255) NULL,
[ACTOR_HOST] [nvarchar](255) NULL,
[ACTION] [nvarchar](255) NULL,
[ACTION_TARGET] [nvarchar](max) NULL,
[ACTION_VALUE] [nvarchar](max) NULL,
[STATUS_CODE] [int] NULL,
[ORIGINATING_SYSTEM] [nvarchar](255) NULL,
[ORIGINATING_CONTEXT] [int] NULL,
[partition] AS MONTH([EVENT_TIMESTAMP]) PERSISTED,
PRIMARY KEY CLUSTERED ([AUDIT_EVENTS_ID] ASC, [partition], [EVENT_TIMESTAMP])
) ON PS_DATA_BY_MONTH ([partition]);
GO
-- https://docs.inductiveautomation.com/display/DOC/system.eam.queryAgentHistory
CREATE TABLE agent_events (
[id] [int] IDENTITY(1,1) NOT NULL,
[agent_name] [nvarchar](255) NOT NULL,
[agent_role] [nvarchar](255) NULL,
[event_time] [datetime] DEFAULT CURRENT_TIMESTAMP,
[event_category] [nvarchar](255) NULL,
[event_type] [nvarchar](255) NULL,
[event_source] [nvarchar](255) NULL,
[event_level] [nvarchar](255) NULL,
[event_level_int] [int] NULL,
[message] [nvarchar](255) NULL,
[partition] AS MONTH([event_time]) PERSISTED,
PRIMARY KEY CLUSTERED ([id] ASC, [partition], [event_time])
) ON PS_DATA_BY_MONTH ([partition]);
GO
-------------------------------------------------------------------------------
-- CREATE IGNITION TAG HISTORY DATA MODEL (6 un-partitioned relational tables)
-- https://docs.inductiveautomation.com/display/SE/Tag+History+Tables+Reference
-------------------------------------------------------------------------------
DROP TABLE IF EXISTS sqlth_annotations ;
DROP TABLE IF EXISTS sqlth_drv ;
DROP TABLE IF EXISTS sqlth_partitions ;
DROP TABLE IF EXISTS sqlth_sce ;
DROP TABLE IF EXISTS sqlth_scinfo ;
DROP TABLE IF EXISTS sqlth_te ;
CREATE TABLE sqlth_annotations ( -- stores annotations
[id] [int] IDENTITY(1,1) NOT NULL,
[tagid] [int] NULL,
[start_time] [bigint] NULL,
[end_time] [bigint] NULL,
[type] [nvarchar](255) NULL,
[datavalue] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY];
CREATE TABLE sqlth_drv ( -- stores information about the drivers
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [nvarchar](255) NULL,
[provider] [nvarchar](255) NULL,
PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY];
CREATE TABLE sqlth_partitions ( -- stores start and end times for each sqlt_data table
[pname] [nvarchar](255) NULL,
[drvid] [int] NULL,
[start_time] [bigint] NULL,
[end_time] [bigint] NULL,
[blocksize] [int] NULL,
[flags] [int] NULL
) ON [PRIMARY];
CREATE TABLE sqlth_sce ( -- stores start and end times for scan classes
[scid] [int] NULL,
[start_time] [bigint] NULL,
[end_time] [bigint] NULL,
[rate] [int] NULL
) ON [PRIMARY];
CREATE TABLE sqlth_scinfo ( -- stores scan class information
[id] [int] IDENTITY(1,1) NOT NULL,
[scname] [nvarchar](255) NULL,
[drvid] [int] NULL,
PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY];
CREATE TABLE sqlth_te ( -- stores non-data details about each tag
[id] [int] IDENTITY(1,1) NOT NULL,
[tagpath] [nvarchar](255) NULL,
[scid] [int] NULL,
[datatype] [int] NULL,
[querymode] [int] NULL,
[created] [bigint] NULL,
[retired] [bigint] NULL,
PRIMARY KEY CLUSTERED ([id] ASC)
) ON [PRIMARY];
GO
-------------------------------------------------------------------------------
-- CREATE PARTITIONED IGNITION TAG HISTORY TABLES (1 historical table per tag server)
-- NOTES:
-- 1) Disable data partitioning and data pruning in gateway's tag history config's
-- 2) Computed partition column using string conversion from '19700101' is non-deterministic
-- 3) Create tables for nine tag servers in case a gateway has some value cached
-------------------------------------------------------------------------------
DROP TABLE IF EXISTS sqlth_1_data ;
DROP TABLE IF EXISTS sqlth_2_data ;
DROP TABLE IF EXISTS sqlth_3_data ;
DROP TABLE IF EXISTS sqlth_4_data ;
DROP TABLE IF EXISTS sqlth_5_data ;
DROP TABLE IF EXISTS sqlth_6_data ;
DROP TABLE IF EXISTS sqlth_7_data ;
DROP TABLE IF EXISTS sqlth_8_data ;
DROP TABLE IF EXISTS sqlth_9_data ;
CREATE TABLE sqlth_1_data (
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL DEFAULT 0, -- string conversion from '19700101' is non-deterministic
[partition] AS MONTH(DATEADD(DAY,t_stamp/(1000*3600*24),DATEFROMPARTS(1970,01,01))) PERSISTED,
PRIMARY KEY CLUSTERED ([tagid] ASC, [partition], [t_stamp] ASC)
) ON PS_TAGHISTORY_BY_MONTH ([partition]);
CREATE TABLE sqlth_2_data (
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL DEFAULT 0, -- string conversion from '19700101' is non-deterministic
[partition] AS MONTH(DATEADD(DAY,t_stamp/(1000*3600*24),DATEFROMPARTS(1970,01,01))) PERSISTED,
PRIMARY KEY CLUSTERED ([tagid] ASC, [partition], [t_stamp] ASC)
) ON PS_TAGHISTORY_BY_MONTH ([partition]);
CREATE TABLE sqlth_3_data (
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL DEFAULT 0, -- string conversion from '19700101' is non-deterministic
[partition] AS MONTH(DATEADD(DAY,t_stamp/(1000*3600*24),DATEFROMPARTS(1970,01,01))) PERSISTED,
PRIMARY KEY CLUSTERED ([tagid] ASC, [partition], [t_stamp] ASC)
) ON PS_TAGHISTORY_BY_MONTH ([partition]);
CREATE TABLE sqlth_4_data (
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL DEFAULT 0, -- string conversion from '19700101' is non-deterministic
[partition] AS MONTH(DATEADD(DAY,t_stamp/(1000*3600*24),DATEFROMPARTS(1970,01,01))) PERSISTED,
PRIMARY KEY CLUSTERED ([tagid] ASC, [partition], [t_stamp] ASC)
) ON PS_TAGHISTORY_BY_MONTH ([partition]);
CREATE TABLE sqlth_5_data (
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL DEFAULT 0, -- string conversion from '19700101' is non-deterministic
[partition] AS MONTH(DATEADD(DAY,t_stamp/(1000*3600*24),DATEFROMPARTS(1970,01,01))) PERSISTED,
PRIMARY KEY CLUSTERED ([tagid] ASC, [partition], [t_stamp] ASC)
) ON PS_TAGHISTORY_BY_MONTH ([partition]);
CREATE TABLE sqlth_6_data (
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL DEFAULT 0, -- string conversion from '19700101' is non-deterministic
[partition] AS MONTH(DATEADD(DAY,t_stamp/(1000*3600*24),DATEFROMPARTS(1970,01,01))) PERSISTED,
PRIMARY KEY CLUSTERED ([tagid] ASC, [partition], [t_stamp] ASC)
) ON PS_TAGHISTORY_BY_MONTH ([partition]);
CREATE TABLE sqlth_7_data (
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL DEFAULT 0, -- string conversion from '19700101' is non-deterministic
[partition] AS MONTH(DATEADD(DAY,t_stamp/(1000*3600*24),DATEFROMPARTS(1970,01,01))) PERSISTED,
PRIMARY KEY CLUSTERED ([tagid] ASC, [partition], [t_stamp] ASC)
) ON PS_TAGHISTORY_BY_MONTH ([partition]);
CREATE TABLE sqlth_8_data (
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL DEFAULT 0, -- string conversion from '19700101' is non-deterministic
[partition] AS MONTH(DATEADD(DAY,t_stamp/(1000*3600*24),DATEFROMPARTS(1970,01,01))) PERSISTED,
PRIMARY KEY CLUSTERED ([tagid] ASC, [partition], [t_stamp] ASC)
) ON PS_TAGHISTORY_BY_MONTH ([partition]);
CREATE TABLE sqlth_9_data (
[tagid] [int] NOT NULL,
[intvalue] [bigint] NULL,
[floatvalue] [float] NULL,
[stringvalue] [nvarchar](255) NULL,
[datevalue] [datetime] NULL,
[dataintegrity] [int] NULL,
[t_stamp] [bigint] NOT NULL DEFAULT 0, -- string conversion from '19700101' is non-deterministic
[partition] AS MONTH(DATEADD(DAY,t_stamp/(1000*3600*24),DATEFROMPARTS(1970,01,01))) PERSISTED,
PRIMARY KEY CLUSTERED ([tagid] ASC, [partition], [t_stamp] ASC)
) ON PS_TAGHISTORY_BY_MONTH ([partition]);
-------------------------------------------------------------------------------
-- Allocate required fixed database roles to the WAM service user
-- db_datareader can read all data from all user tables and views.
-- db_datawriter can add, delete, or change data in all user tables.
-- db_ddladmin grants the ability to create/alter/drop ANY object in the database in ANY schema.
-- db_executor grants the ability to execute stored procedures.
-- see https://www.sqlmatters.com/Articles/Adding%20a%20db_executor%20role.aspx
-------------------------------------------------------------------------------
USE IgnitionDB;
EXEC sp_addrolemember 'db_datareader','WAM\svcIgnition';
EXEC sp_addrolemember 'db_datawriter','WAM\svcIgnition';
EXEC sp_addrolemember 'db_ddladmin', 'WAM\svcIgnition';
CREATE ROLE db_executor; -- Create a db_executor role
GRANT EXECUTE TO db_executor; -- Grant execute rights to the new role
EXEC sp_addrolemember 'db_executor', 'WAM\svcIgnition';
/* EXEC sp_addrolemember 'db_owner', 'WAM\svcIgnition';
We could declare the service user to be the db_owner.
This grants way more than necessary to execute stored proc's
and also goes against Microsoft's recommended best practices.
https://docs.microsoft.com/en-us/previous-versions/dotnet/framework/data/adonet/sql/managing-permissions-with-stored-procedures-in-sql-server#best-practices
*/
A few minutia:
- In order to partition the alarm_event data table, the table definition includes an extra
onInsert datetime column which serves for partitioning. Besides added indexing, this is the only genuine deviation from the auto-created plain-vanilla Ignition data models.
- All of the partitioned tables use computed columns that serve as the integer argument to the
PF_BY_MONTH() partition function.