AipGateway/SQL/BulkEncryptionDatabase_1.publish.sql

/*
Deployment script for bulkencryption

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "bulkencryption"
:setvar DefaultFilePrefix "bulkencryption"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF; 
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END


GO
IF EXISTS (SELECT 1
           FROM   [sys].[databases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [sys].[databases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [sys].[databases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                DATE_CORRELATION_OPTIMIZATION OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [sys].[databases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF) 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [sys].[databases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO, OPERATION_MODE = READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_PLANS_PER_QUERY = 200, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), MAX_STORAGE_SIZE_MB = 100) 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [sys].[databases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
        ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
        ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
    END


GO
IF EXISTS (SELECT 1
           FROM   [sys].[databases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET TEMPORAL_HISTORY_RETENTION ON 
            WITH ROLLBACK IMMEDIATE;
    END


GO
PRINT N'Creating [dbo].[Labels]...';


GO
CREATE TABLE [dbo].[Labels] (
    [Id]        INT            IDENTITY (1, 1) NOT NULL,
    [LabelName] NVARCHAR (255) NOT NULL,
    [LabelGuid] NVARCHAR (255) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [dbo].[Instances]...';


GO
CREATE TABLE [dbo].[Instances] (
    [Id]              INT            IDENTITY (1, 1) NOT NULL,
    [StartTime]       DATETIME       NOT NULL,
    [EndTime]         DATETIME       NULL,
    [NumberProcessed] INT            NULL,
    [NumberErrors]    INT            NULL,
    [Exception]       NVARCHAR (MAX) NULL,
    [ServerId]        INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [dbo].[Servers]...';


GO
CREATE TABLE [dbo].[Servers] (
    [Id]              INT             IDENTITY (1, 1) NOT NULL,
    [StartTime]       TIME (7)        NOT NULL,
    [EndTime]         TIME (7)        NOT NULL,
    [ServerName]      NVARCHAR (1023) NOT NULL,
    [NumberInstances] INT             NOT NULL,
    [ServerComplete]  BIT             NULL,
    [IsActive]        BIT             NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [dbo].[GlobalConfig]...';


GO
CREATE TABLE [dbo].[GlobalConfig] (
    [Id]    INT             IDENTITY (1, 1) NOT NULL,
    [Key]   NVARCHAR (1023) NOT NULL,
    [Value] NVARCHAR (MAX)  NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [dbo].[FileServers]...';


GO
CREATE TABLE [dbo].[FileServers] (
    [Id]            INT             IDENTITY (1, 1) NOT NULL,
    [FileServer]    NVARCHAR (1023) NOT NULL,
    [BJPCStartDate] DATETIME        NULL,
    [BJPCEndDate]   DATETIME        NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [dbo].[ServersFileServers]...';


GO
CREATE TABLE [dbo].[ServersFileServers] (
    [Id]           INT IDENTITY (1, 1) NOT NULL,
    [ServerId]     INT NOT NULL,
    [FileServerId] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating [dbo].[Files]...';


GO
CREATE TABLE [dbo].[Files] (
    [Id]               INT             IDENTITY (1, 1) NOT NULL,
    [FilePath]         NVARCHAR (1023) NOT NULL,
    [FileServerId]     INT             NULL,
    [Location]         NVARCHAR (1023) NULL,
    [Status]           INT             NOT NULL,
    [StartedWhen]      DATETIME        NULL,
    [CompletedWhen]    DATETIME        NULL,
    [Exception]        NVARCHAR (MAX)  NULL,
    [RetryCount]       INT             NULL,
    [InstanceId]       INT             NULL,
    [LabelId]          INT             NOT NULL,
    [NewFileName]      NVARCHAR (1023) NULL,
    [NewFileSize]      BIGINT          NULL,
    [OriginalFileSize] BIGINT          NULL,
    [PotentialBJLabel] BIT             NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
PRINT N'Creating unnamed constraint on [dbo].[Servers]...';


GO
ALTER TABLE [dbo].[Servers]
    ADD DEFAULT 1 FOR [IsActive];


GO
PRINT N'Creating unnamed constraint on [dbo].[Files]...';


GO
ALTER TABLE [dbo].[Files]
    ADD DEFAULT 1 FOR [Status];


GO
PRINT N'Creating unnamed constraint on [dbo].[Instances]...';


GO
ALTER TABLE [dbo].[Instances]
    ADD FOREIGN KEY ([ServerId]) REFERENCES [dbo].[Servers] ([Id]);


GO
PRINT N'Creating unnamed constraint on [dbo].[ServersFileServers]...';


GO
ALTER TABLE [dbo].[ServersFileServers]
    ADD FOREIGN KEY ([ServerId]) REFERENCES [dbo].[Servers] ([Id]);


GO
PRINT N'Creating unnamed constraint on [dbo].[ServersFileServers]...';


GO
ALTER TABLE [dbo].[ServersFileServers]
    ADD FOREIGN KEY ([FileServerId]) REFERENCES [dbo].[FileServers] ([Id]);


GO
PRINT N'Creating unnamed constraint on [dbo].[Files]...';


GO
ALTER TABLE [dbo].[Files]
    ADD FOREIGN KEY ([InstanceId]) REFERENCES [dbo].[Instances] ([Id]);


GO
PRINT N'Creating unnamed constraint on [dbo].[Files]...';


GO
ALTER TABLE [dbo].[Files]
    ADD FOREIGN KEY ([LabelId]) REFERENCES [dbo].[Labels] ([Id]);


GO
PRINT N'Creating unnamed constraint on [dbo].[Files]...';


GO
ALTER TABLE [dbo].[Files]
    ADD FOREIGN KEY ([FileServerId]) REFERENCES [dbo].[FileServers] ([Id]);


GO
PRINT N'Creating [dbo].[GetServerConfig]...';


GO
CREATE PROCEDURE [dbo].[GetServerConfig]
	@serverName NVARCHAR(1023)
AS

SELECT * FROM [Servers] s 
	WHERE s.[ServerName] = @serverName
GO
PRINT N'Creating [dbo].[LogServerComplete]...';


GO
CREATE PROCEDURE [dbo].[LogServerComplete]
	@serverName NVARCHAR(1023)
AS

-- Update the servers table that this server is done
UPDATE [Servers] SET [ServerComplete] = 1 WHERE [ServerName] = @serverName
GO
PRINT N'Creating [dbo].[GetActiveInstances]...';


GO
CREATE PROCEDURE [dbo].[GetActiveInstances]
	@serverId INT
AS

-- Get all the running scripts for this server
SELECT * FROM [Instances] i 
	WHERE i.[ServerId] = @serverId AND i.[EndTime] IS NULL
GO
PRINT N'Creating [dbo].[GetActiveServers]...';


GO
CREATE PROCEDURE [dbo].[GetActiveServers]

AS

-- Get all the servers which should be running
DECLARE @timeNow TIME(7) = CONVERT (time, GETUTCDATE())

SELECT * FROM [Servers] s 
	WHERE 
		s.[StartTime] < @timeNow 
		AND s.[EndTime] > @timeNow 
		AND (s.[ServerComplete] IS NULL OR s.[ServerComplete] <> 1)
		AND s.IsActive = 1
GO
PRINT N'Creating [dbo].[LogScriptEnd]...';


GO
CREATE PROCEDURE [dbo].[LogScriptEnd]
	@instanceId INT,
	@numberProcessed INT = NULL,
	@numberErrors INT = NULL,
	@exception NVARCHAR(MAX) = NULL
AS

-- Update the instances table to mark this script as 'done'... at least for now
UPDATE Instances
SET 
	[EndTime] = GETUTCDATE(),
	[NumberProcessed] = @numberProcessed,
	[NumberErrors] = @numberErrors,
	[Exception] = @exception
WHERE [Id] = @instanceId
GO
PRINT N'Creating [dbo].[LogScriptStart]...';


GO
CREATE PROCEDURE [dbo].[LogScriptStart]
	@serverName NVARCHAR(1023)
AS

DECLARE @rowId INT = NULL

BEGIN TRANSACTION

	-- Get the row from the Servers table
	DECLARE @serverId INT
	SELECT @serverId = [Id] FROM [Servers] s WHERE s.ServerName = @serverName

	-- Return null if there isn't a row in the server table for this
	IF @serverId = NULL RETURN NULL

	-- Add a row to the instances table to log this script as 'running'
	INSERT INTO Instances (StartTime, ServerId)
		VALUES (GETUTCDATE(), @serverId)	

	-- Return the new Id for the script to use
	SELECT SCOPE_IDENTITY()

COMMIT
GO
PRINT N'Creating [dbo].[SelectNextFileToProcess]...';


GO
CREATE PROCEDURE [dbo].[SelectNextFileToProcess]
	@serverId INT,
	@scriptInstanceId INT,
	@maxRetries INT,
	@mode NVARCHAR(50)
AS

BEGIN TRANSACTION

	-- Get the next row to process
	DECLARE @rowId INT = 0

	IF @mode = 'encrypt'
	BEGIN
		SELECT @rowId = Id
			FROM Files f
			WITH (TABLOCKX, HOLDLOCK) 
			WHERE 
				(f.[Status] = 1 OR f.[Status] = 4)
				AND f.[FileServerId] IN (SELECT [FileServerId] FROM [ServersFileServers] sfs WHERE sfs.ServerId = @serverId)
				AND (f.[RetryCount] IS NULL OR f.[RetryCount] < @maxRetries)
			ORDER BY f.[RetryCount] DESC, f.[Id] ASC
	END

	IF @mode = 'decrypt'
	BEGIN
		SELECT @rowId = Id 
			FROM Files f 
			WITH (TABLOCKX, HOLDLOCK) 
			WHERE 
				(f.[Status] = 3 OR f.[Status] = 5)
				AND f.[FileServerId] IN (SELECT [FileServerId] FROM [ServersFileServers] sfs WHERE sfs.ServerId = @serverId)
				AND (f.[RetryCount] IS NULL OR f.[RetryCount] < @maxRetries)
			ORDER BY f.[RetryCount] DESC, f.[Id] ASC
	END


	IF @rowId IS NOT NULL AND @rowId > 0
	BEGIN

		-- 'Lock' the row for processing by this script
		UPDATE Files
			SET [Status] = 2, [StartedWhen] = GETUTCDATE(), [InstanceId] = @scriptInstanceId
			WHERE [Id] = @rowId

		-- Get the row (with the label info + server status) to send back to the script
		SELECT f.*, l.[LabelGuid], l.[LabelName], s.IsActive, fs.FileServer, fs.BJPCStartDate, fs.BJPCEndDate
		FROM Files f 
			LEFT JOIN Labels l on f.[LabelId] = l.Id
			LEFT JOIN Instances i on f.[InstanceId] = i.Id
			LEFT JOIN [Servers] s on i.ServerId = s.Id
			LEFT JOIN [FileServers] fs on fs.Id = f.FileServerId
			WHERE f.[Id] = @rowId

	END

COMMIT
GO
PRINT N'Creating [dbo].[UpdateFileRow]...';


GO
CREATE PROCEDURE [dbo].[UpdateFileRow]
	@rowId INT,
	@exception NVARCHAR(MAX) = NULL,
	@retryCount INT = NULL,
	@status INT,
	@newfilename NVARCHAR(1024) = NULL,
	@newfilesize BIGINT = NULL,
	@originalfilesize BIGINT = NULL,
	@potentialBJLabel BIT = NULL

AS

-- Is this an error or are we good?
-- DECLARE @status INT = 3
-- IF @exception IS NOT NULL AND @exception != '' SET @status = 4

-- Update the row
UPDATE Files
	SET [Status] = @status, [CompletedWhen] = GETUTCDATE(), [Exception] = @exception, [RetryCount] = @retryCount, [NewFileName] = @newfilename, [NewFileSize] = @newfilesize, [OriginalFileSize] = @originalfilesize, [PotentialBJLabel] = @potentialBJLabel
	WHERE [Id] = @rowId

-- Get the row to send back to the script
SELECT * FROM Files f WHERE f.[Id] = @rowId
GO
/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

/* STATUS VALUES 
1 - Awaiting Processing
2 - In Process
3 - Complete
4 - Error
5 - Error rolling back (removing label)
*/

-- GLOBAL CONFIG --
SET IDENTITY_INSERT [GlobalConfig] ON

INSERT INTO [GlobalConfig] 
(Id,		[Key],							[Value])
VALUES 
(1,			'MaxRetries',					'5'),
(2,			'AADWebAppId',					'ABC-123'),
(3,			'AADWebAppKey',					'XXX'),
(4,			'AADNativeAppId',				'123'),
(5,			'AADToken',						'Token-1')

SET IDENTITY_INSERT [GlobalConfig] OFF

-- LABELS --
SET IDENTITY_INSERT [Labels] ON

INSERT INTO [Labels] 
(Id,		LabelName,					LabelGuid)
VALUES 
(1,			'Public',					'1234'),
(2,			'Restricted External',		'1234'),
(3,			'Restricted Internal',		'1234'),
(4,			'Confidential',				'1234'),
(5,			'Secret',					'd9f23ae3-a239-45ea-bf23-f515f824c57b')

SET IDENTITY_INSERT [Labels] OFF

-- SERVERS --
SET IDENTITY_INSERT [Servers] ON
INSERT INTO [Servers] 
(Id,	ServerName,			StartTime,			EndTime,		NumberInstances,		ServerComplete)
VALUES
(1,		'DAVROS',			'09:00:00',			'17:00:00',		1,						NULL),
(2,		'MININT-RDS9B7O',	'09:00:00',			'17:00:00',		2,						NULL)
SET IDENTITY_INSERT [Servers] OFF

SET IDENTITY_INSERT [FileServers] ON
INSERT INTO [FileServers] 
(Id,	FileServers,		BJPCStartDate,		BJPCEndDate)
VALUES
(1,		'X',				'2018-08-06',		'2018-08-10'),
(2,		'MININT-RDS9B7O',	'2018-09-06',		'2018-09-10')
SET IDENTITY_INSERT [FileServers] OFF


-- SERVERS - FILE SERVERS --
INSERT INTO [ServersFileServers] 
(ServerId,		FileServerId)
VALUES
(1,				1),
(2,				2)


-- STATUS CODES --
INSERT INTO [StatusCodes] (Status) VALUES 
('NotStarted'),('InProgress'),('SuccessfulEncrypt'),('EncryptError'),('FailedDecrypt'),('WillNotEncrypt'),('NotFound')

GO

GO
DECLARE @VarDecimalSupported AS BIT;

SELECT @VarDecimalSupported = 0;

IF ((ServerProperty(N'EngineEdition') = 3)
    AND (((@@microsoftversion / power(2, 24) = 9)
          AND (@@microsoftversion & 0xffff >= 3024))
         OR ((@@microsoftversion / power(2, 24) = 10)
             AND (@@microsoftversion & 0xffff >= 1600))))
    SELECT @VarDecimalSupported = 1;

IF (@VarDecimalSupported > 0)
    BEGIN
        EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
    END


GO
PRINT N'Update complete.';


GO