Retired products

DLM Dashboard server objects

When you set up a server for monitoring, you run a setup script to add the following objects to your server:

Object

Type

What it does

RG_SQLLighthouse_DDLTriggerRedGate

DDL trigger

Detects all schema changes.

For more information on DDL triggers, see Understanding DDL Triggers.

RedGate

Database

Stores details of schema changes detected by the DDL trigger.

RG_SQLLighthouse_WriteEvents

RG_SQLLighthouse_ReadEvent

RG_SQLLighthouse_WriteError

Stored procedures

Writes details of schema changes to the RedGate database.

Extracts details of schema changes from the RedGate database.

Logs any errors encountered when executing the DDL trigger.

Note that the RedGate database may exist already if you have other Redgate products installed. The trigger is created to EXECUTE AS 'sa'.

Location of stored procedures

The default location for the stored procedures was changed in DLM Dashboard 1.7.0 from the master database to the Redgate database. For more information, see Moving stored procedures from master.

How DLM Dashboard works

When the schema of a monitored database changes:

The DDL trigger detects the change.
RG_SQLLighthouse_WriteEvents adds details of the changes to the RedGate database, which is regularly polled by the DLM Dashboard Monitoring Service.
The monitoring service detects these changes and compares schema snapshots within the DLM Dashboard Storage Service, and records the differences.
Differences are shown on the Review page in DLM Dashboard.

Running the setup script

The setup script below applies to DLM Dashboard 1.7.0 and later.

If you're using an earlier version of DLM Dashboard, or if you're changing the configuration of a server set up for monitoring in an earlier version, use the link available in DLM Dashboard when you add or edit the server. This will give you the appropriate setup script, login script and permissions information for your server configuration. See Add servers and database

To create the DLM Dashboard server objects, run this SQL script (download here) on each server you want to monitor:

Installation script

/*
 *    Red Gate
 *
 *
 *  _____   _       __  __       _____               _      _                             _ 
 * |  __ \ | |     |  \/  |     |  __ \             | |    | |                           | |
 * | |  | || |     | \  / |     | |  | |  __ _  ___ | |__  | |__    ___    __ _  _ __  __| |
 * | |  | || |     | |\/| |     | |  | | / _` |/ __|| '_ \ | '_ \  / _ \  / _` || '__|/ _` |
 * | |__| || |____ | |  | |     | |__| || (_| |\__ \| | | || |_) || (_) || (_| || |  | (_| |
 * |_____/ |______||_|  |_|     |_____/  \__,_||___/|_| |_||_.__/  \___/  \__,_||_|   \__,_|
 *                                                                                          
 *                                                                                          
 *
 *
 *    For information about these objects, see: http://www.red-gate.com/dlm-dashboard/ddl-trigger
 *
 *    If you have any problems, or need to change these objects, we really want to know,
 *    please email us at: dlmdashboardsupport@red-gate.com
 *
 *    Copyright © Red Gate Software Ltd. 2016 | Version 1.12 | 2016-02-29
 */
USE master;
GO
/* Make sure RedGate database exists, and writes to it don't block transactions in Snapshot isolation level */
IF DB_ID(N'RedGate') IS NULL
	CREATE DATABASE RedGate
ALTER DATABASE RedGate SET RECOVERY SIMPLE
ALTER DATABASE RedGate SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE RedGate SET READ_COMMITTED_SNAPSHOT ON
GO
/* Create/update objects necessary for the DLM Dashboard change detection trigger in a transaction */
BEGIN TRANSACTION RG_SQLLighthouse_CreateTrigger
IF EXISTS (SELECT 1 FROM sys.server_triggers WHERE name = 'RG_SQLLighthouse_DDLTrigger')
	DROP TRIGGER RG_SQLLighthouse_DDLTrigger ON ALL SERVER
GO
USE RedGate;
GO
IF OBJECT_ID(N'dbo.RG_SQLLighthouse_WriteEvent', N'P') IS NOT NULL
	DROP PROCEDURE dbo.RG_SQLLighthouse_WriteEvent;
GO
IF OBJECT_ID(N'dbo.RG_SQLLighthouse_WriteError', N'P') IS NOT NULL
	DROP PROCEDURE dbo.RG_SQLLighthouse_WriteError;
GO
IF OBJECT_ID(N'dbo.RG_SQLLighthouse_ReadEvents', N'P') IS NOT NULL
	DROP PROCEDURE dbo.RG_SQLLighthouse_ReadEvents;
GO
/* Ensure SQLLighthouse schema exists */
IF OBJECT_ID(N'SQLLighthouse.DDL_Events', N'U') IS NOT NULL
	DROP TABLE SQLLighthouse.DDL_Events
GO
IF OBJECT_ID(N'SQLLighthouse.Trigger_Errors', N'U') IS NOT NULL
	DROP TABLE SQLLighthouse.Trigger_Errors
GO
IF SCHEMA_ID(N'SQLLighthouse') IS NOT NULL
	DROP SCHEMA SQLLighthouse
GO
CREATE SCHEMA SQLLighthouse AUTHORIZATION dbo
GO
CREATE TABLE SQLLighthouse.DDL_Events (
	id bigint NOT NULL IDENTITY (1,1) PRIMARY KEY,
	PostTime datetimeoffset,
	transaction_id bigint, spid smallint, options integer, nestlevel int, langid smallint, client_net_address varchar(48), appname nvarchar(128),
	eventdata XML
  )
GO
CREATE TABLE SQLLighthouse.Trigger_Errors (
	id BIGINT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
	error_time DATETIMEOFFSET,
	error_number INT,
	error_severity INT,
	error_state INT,
	error_procedure sysname,
	error_line INT,
	error_message NVARCHAR(4000),			
)
GO
/*
 *    Red Gate
 *
 *
 *  _____   _       __  __       _____               _      _                             _ 
 * |  __ \ | |     |  \/  |     |  __ \             | |    | |                           | |
 * | |  | || |     | \  / |     | |  | |  __ _  ___ | |__  | |__    ___    __ _  _ __  __| |
 * | |  | || |     | |\/| |     | |  | | / _` |/ __|| '_ \ | '_ \  / _ \  / _` || '__|/ _` |
 * | |__| || |____ | |  | |     | |__| || (_| |\__ \| | | || |_) || (_) || (_| || |  | (_| |
 * |_____/ |______||_|  |_|     |_____/  \__,_||___/|_| |_||_.__/  \___/  \__,_||_|   \__,_|
 *                                                                                          
 *                                                                                          
 *
 *
 *    For information about these objects, see: http://www.red-gate.com/dlm-dashboard/ddl-trigger
 *
 *    If you have any problems, or need to change these objects, we really want to know,
 *    please email us at: dlmdashboardsupport@red-gate.com
 *
 *    Copyright © Red Gate Software Ltd. 2016 | Version 1.12 | 2016-02-29
 */
/************************************************************************************************************
 * Stored procedure: RedGate.dbo.RG_SQLLighthouse_WriteEvent
 * Used by trigger RG_SQLLighthouse_DDLTrigger to write change events to RedGate.SQLLighthouse.DDL_Events
 ************************************************************************************************************/
CREATE PROCEDURE dbo.RG_SQLLighthouse_WriteEvent
	@eventdata XML
AS
	INSERT INTO RedGate.SQLLighthouse.DDL_Events
	VALUES  (
		TODATETIMEOFFSET(CONVERT(varchar(23), @eventdata.query('data(/EVENT_INSTANCE/PostTime)')), DATEPART(tz, SYSDATETIMEOFFSET())),
		(SELECT transaction_id from sys.dm_tran_current_transaction), @@SPID, @@OPTIONS, @@NESTLEVEL, @@LANGID, CONVERT(varchar(48), CONNECTIONPROPERTY('client_net_address')), APP_NAME(),
		@eventdata
	  )
	IF (SELECT COUNT(*) FROM RedGate.SQLLighthouse.DDL_Events) >= 512
	BEGIN
		DELETE FROM RedGate.SQLLighthouse.DDL_Events WHERE id <= SCOPE_IDENTITY() - 420
	END
GO
/*
 *    Red Gate
 *
 *
 *  _____   _       __  __       _____               _      _                             _ 
 * |  __ \ | |     |  \/  |     |  __ \             | |    | |                           | |
 * | |  | || |     | \  / |     | |  | |  __ _  ___ | |__  | |__    ___    __ _  _ __  __| |
 * | |  | || |     | |\/| |     | |  | | / _` |/ __|| '_ \ | '_ \  / _ \  / _` || '__|/ _` |
 * | |__| || |____ | |  | |     | |__| || (_| |\__ \| | | || |_) || (_) || (_| || |  | (_| |
 * |_____/ |______||_|  |_|     |_____/  \__,_||___/|_| |_||_.__/  \___/  \__,_||_|   \__,_|
 *                                                                                          
 *                                                                                          
 *
 *
 *    For information about these objects, see: http://www.red-gate.com/dlm-dashboard/ddl-trigger
 *
 *    If you have any problems, or need to change these objects, we really want to know,
 *    please email us at: dlmdashboardsupport@red-gate.com
 *
 *    Copyright © Red Gate Software Ltd. 2016 | Version 1.12 | 2016-02-29
 */
/*****************************************************************************************************************
 * Stored procedure: RedGate.dbo.RG_SQLLighthouse_ReadEvents
 * Used by the DLM Dashboard change detection service to read change events from RedGate.SQLLighthouse.DDL_Events
 *****************************************************************************************************************/
CREATE PROCEDURE dbo.RG_SQLLighthouse_ReadEvents
AS
	SELECT * FROM RedGate.SQLLighthouse.DDL_Events
GO
/*
 *    Red Gate
 *
 *
 *  _____   _       __  __       _____               _      _                             _ 
 * |  __ \ | |     |  \/  |     |  __ \             | |    | |                           | |
 * | |  | || |     | \  / |     | |  | |  __ _  ___ | |__  | |__    ___    __ _  _ __  __| |
 * | |  | || |     | |\/| |     | |  | | / _` |/ __|| '_ \ | '_ \  / _ \  / _` || '__|/ _` |
 * | |__| || |____ | |  | |     | |__| || (_| |\__ \| | | || |_) || (_) || (_| || |  | (_| |
 * |_____/ |______||_|  |_|     |_____/  \__,_||___/|_| |_||_.__/  \___/  \__,_||_|   \__,_|
 *                                                                                          
 *                                                                                          
 *
 *
 *    For information about these objects, see: http://www.red-gate.com/dlm-dashboard/ddl-trigger
 *
 *    If you have any problems, or need to change these objects, we really want to know,
 *    please email us at: dlmdashboardsupport@red-gate.com
 *
 *    Copyright © Red Gate Software Ltd. 2016 | Version 1.12 | 2016-02-29
 */
/************************************************************************************************************
 * Stored procedure: RedGate.dbo.RG_SQLLighthouse_WriteError
 * Used by trigger RG_SQLLighthouse_DDLTrigger to write error logs to RedGate.SQLLighthouse.Trigger_Errors
 ************************************************************************************************************/
CREATE PROCEDURE dbo.RG_SQLLighthouse_WriteError
	@error_number INT,
	@error_severity INT,
	@error_state INT,
	@error_procedure sysname,
	@error_line INT,
	@error_message NVARCHAR(4000)
AS
	INSERT  INTO RedGate.SQLLighthouse.Trigger_Errors
	VALUES  (
		SYSDATETIMEOFFSET(), @error_number, @error_severity, @error_state, @error_procedure, @error_line, @error_message
		)
	IF (SELECT COUNT(*) FROM RedGate.SQLLighthouse.Trigger_Errors) >= 512
	BEGIN
		DELETE FROM RedGate.SQLLighthouse.Trigger_Errors WHERE id <= SCOPE_IDENTITY() - 420
	END
GO
USE master;
GO
/*
 *    Red Gate
 *
 *
 *  _____   _       __  __       _____               _      _                             _ 
 * |  __ \ | |     |  \/  |     |  __ \             | |    | |                           | |
 * | |  | || |     | \  / |     | |  | |  __ _  ___ | |__  | |__    ___    __ _  _ __  __| |
 * | |  | || |     | |\/| |     | |  | | / _` |/ __|| '_ \ | '_ \  / _ \  / _` || '__|/ _` |
 * | |__| || |____ | |  | |     | |__| || (_| |\__ \| | | || |_) || (_) || (_| || |  | (_| |
 * |_____/ |______||_|  |_|     |_____/  \__,_||___/|_| |_||_.__/  \___/  \__,_||_|   \__,_|
 *                                                                                          
 *                                                                                          
 *
 *
 *    For information about these objects, see: http://www.red-gate.com/dlm-dashboard/ddl-trigger
 *
 *    If you have any problems, or need to change these objects, we really want to know,
 *    please email us at: dlmdashboardsupport@red-gate.com
 *
 *    Copyright © Red Gate Software Ltd. 2016 | Version 1.12 | 2016-02-29
 */
/*****************************************************************************************************************
 * Server level trigger: RG_SQLLighthouse_DDLTrigger
 * Detects DDL events and writes them to the table (via RedGate.dbo.RG_SQLLighthouse_WriteEvent)
 *****************************************************************************************************************/
CREATE TRIGGER RG_SQLLighthouse_DDLTrigger
    ON ALL SERVER
    WITH EXECUTE AS 'sa'
    AFTER DDL_EVENTS
AS
	IF OBJECT_ID(N'RedGate.dbo.RG_SQLLighthouse_WriteEvent', N'P') IS NOT NULL
	BEGIN
		-- see http://msdn.microsoft.com/en-us/library/ms190763.aspx
		DECLARE @restoreXACT_ABORT bit = 16384 & (SELECT @@OPTIONS);
		SET XACT_ABORT OFF
		DECLARE @restoreANSI_PADDING bit = 16 & (SELECT @@OPTIONS);
		SET ANSI_PADDING ON
		DECLARE @restoreANSI_WARNINGS bit = 8 & (SELECT @@OPTIONS);
		SET ANSI_WARNINGS ON
		DECLARE @restoreCONCAT_NULL_YIELDS_NULL bit = 4096 & (SELECT @@OPTIONS);
		SET CONCAT_NULL_YIELDS_NULL ON
		DECLARE @restoreNUMERIC_ROUNDABORT bit = 8192 & (SELECT @@OPTIONS);
		SET NUMERIC_ROUNDABORT OFF
		DECLARE @restoreNOCOUNT bit = 512 & (SELECT @@OPTIONS);
		SET NOCOUNT ON
		BEGIN TRY
			DECLARE @eventdata XML = EVENTDATA();
			EXECUTE RedGate.dbo.RG_SQLLighthouse_WriteEvent @eventdata
		END TRY
		BEGIN CATCH
			IF OBJECT_ID(N'RedGate.dbo.RG_SQLLighthouse_WriteError', N'P') IS NOT NULL
			BEGIN
				BEGIN TRY
					DECLARE @error_number INT = ERROR_NUMBER(),
						@error_severity INT = ERROR_SEVERITY(),
						@error_state INT = ERROR_STATE(),
						@error_procedure SYSNAME = ERROR_PROCEDURE(),
						@error_line INT = ERROR_LINE(),
						@error_message NVARCHAR(4000) = ERROR_MESSAGE();
					EXECUTE RedGate.dbo.RG_SQLLighthouse_WriteError @error_number,
						@error_severity, @error_state, @error_procedure,
						@error_line, @error_message;
				END TRY
				BEGIN CATCH
					-- Left intentionally blank :(
				END CATCH
			END
		END CATCH
		IF @restoreXACT_ABORT = 1
		BEGIN
			SET XACT_ABORT ON
		END
		IF @restoreANSI_PADDING = 0
		BEGIN
			SET ANSI_PADDING OFF
		END
		IF @restoreANSI_WARNINGS = 0
		BEGIN
			SET ANSI_WARNINGS OFF
		END
		IF @restoreCONCAT_NULL_YIELDS_NULL = 0
		BEGIN
			SET CONCAT_NULL_YIELDS_NULL OFF
		END
		IF @restoreNUMERIC_ROUNDABORT = 1
		BEGIN
			SET NUMERIC_ROUNDABORT ON
		END
		IF @restoreNOCOUNT = 0
		BEGIN
			SET NOCOUNT OFF
		END
	END
GO
/***************************************************************************
 * Cleanup obsolete objects (from previous versions of DLM Dashboard) 
 ***************************************************************************/
IF OBJECT_ID(N'tempdb.dbo.RG_SQLLighthouse_v1', N'U') IS NOT NULL
DROP TABLE tempdb.dbo.RG_SQLLighthouse_v1
IF OBJECT_ID(N'tempdb.dbo.RG_SQLLighthouse_Errors_v1', N'U') IS NOT NULL
DROP TABLE tempdb.dbo.RG_SQLLighthouse_Errors_v1
IF OBJECT_ID(N'master.dbo.RG_SQLLighthouse_Sproc', N'P') IS NOT NULL
DROP PROCEDURE dbo.RG_SQLLighthouse_Sproc
IF OBJECT_ID(N'master.dbo.RG_SQLLighthouse_WriteEvent') IS NOT NULL
DROP PROCEDURE dbo.RG_SQLLighthouse_WriteEvent
IF OBJECT_ID(N'master.dbo.RG_SQLLighthouse_ReadEvents') IS NOT NULL
DROP PROCEDURE dbo.RG_SQLLighthouse_ReadEvents
IF OBJECT_ID(N'master.dbo.RG_SQLLighthouse_WriteError') IS NOT NULL
DROP PROCEDURE dbo.RG_SQLLighthouse_WriteError
COMMIT TRANSACTION RG_SQLLighthouse_CreateTrigger

Removing the DLM Dashboard objects from SQL Server

To uninstall the DLM Dashboard objects from your SQL Server, run this uninstall script. The uninstall script does not delete the RedGate database - you'll need to delete it manually.

Retired products

About the DLM Dashboard server objects

DLM Dashboard server objects

How DLM Dashboard works

Running the setup script

Removing the DLM Dashboard objects from SQL Server