|
|
|
|
|
This topic is provided for users who wish to know Microsoft SQL Server-specific details of how MOVEit Central interfaces to its database.
When SQL Server has been selected as the database engine, MOVEit Central manages its database automatically, so very few sites will need the information contained in this topic. However, unlike with MySQL, MOVEit Central's installs do not install or update the SQL Server software itself. Thus, a system administrator must see to it that periodic Microsoft updates are applied.
For the supported versions of Microsoft SQL Server, see MOVEit Central Service Requirements.
To configure MOVEit Central's connection to MS SQL Server, use the Central Config Utility.
MOVEit Central creates the database using T-SQL statements like this:
USE [master]
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'micstats') DROP DATABASE [micstats];
GO
CREATE DATABASE [micstats]
GO
USE [micstats]
GO
CREATE TABLE
[dbo].[audit]
(
ID [bigint] NOT NULL IDENTITY(1,2) NOT FOR REPLICATION,
LogTime [varchar](24) NULL,
Node [smallint] NULL,
Action [varchar](24) NULL,
TargetType [varchar](24) NULL,
TargetID [varchar](50) NULL,
TargetName [varchar](200) NULL,
CentralVersion [varchar](12) NULL,
AgentBrand [varchar](32) NULL,
AgentVersion [varchar](12) NULL,
Username [varchar](80) NULL,
IPAddress [varchar](16) NULL,
Error [int] NULL,
ErrorText text,
Message text,
Hash [varchar](40) NULL
);
GO
ALTER TABLE
[dbo].[audit]
ADD
CONSTRAINT [DF_audit_LogTime] DEFAULT NULL FOR [LogTime],
CONSTRAINT [DF_audit_Node] DEFAULT NULL FOR [Node],
CONSTRAINT [DF_audit_Action] DEFAULT NULL FOR [Action],
CONSTRAINT [DF_audit_TargetType] DEFAULT NULL FOR [TargetType],
CONSTRAINT [DF_audit_TargetID] DEFAULT NULL FOR [TargetID],
CONSTRAINT [DF_audit_TargetName] DEFAULT NULL FOR [TargetName],
CONSTRAINT [DF_audit_CentralVersion] DEFAULT NULL FOR [CentralVersion],
CONSTRAINT [DF_audit_AgentBrand] DEFAULT NULL FOR [AgentBrand],
CONSTRAINT [DF_audit_AgentVersion] DEFAULT NULL FOR [AgentVersion],
CONSTRAINT [DF_audit_Username] DEFAULT NULL FOR [Username],
CONSTRAINT [DF_audit_IPAddress] DEFAULT NULL FOR [IPAddress],
CONSTRAINT [DF_audit_Error] DEFAULT NULL FOR [Error],
CONSTRAINT [DF_audit_Hash] DEFAULT NULL FOR [Hash],
CONSTRAINT [PK_audit] PRIMARY KEY ([ID])
GO
CREATE NONCLUSTERED INDEX
[IX_audit_LogTime]
ON
[dbo].[audit]
(
[LogTime] ASC
)
GO
CREATE TABLE
[dbo].[stats]
(
ID [bigint] NOT NULL IDENTITY (1,2) NOT FOR REPLICATION,
LogStamp [varchar](24) NULL,
TaskID [int] NOT NULL,
Node [smallint] NOT NULL,
NominalStart [varchar](24) NOT NULL,
Action [varchar](12) NULL,
SourceHost [varchar](100) NULL,
SourceFilename [varchar](255) NULL,
SourceFilenameOnly [varchar](255) NULL,
SourceFileID [varchar](24) NOT NULL,
SourceStamp [varchar](24) NULL,
SourceNBytes float NULL,
SourceDuration float NULL,
SourceMsgID text,
SourceMDN text,
DestHost [varchar](100) NULL,
DestFilename [varchar](255) NULL,
DestFilenameOnly [varchar](255) NULL,
DestFileID [varchar](24) NOT NULL,
NBytes float NULL,
DestDuration float NULL,
DestMsgID text,
DestMDN text,
ErrCode [int] NULL,
Message [varchar](250) NULL,
Hash [varchar](40) NULL
);
GO
ALTER TABLE
[dbo].[stats]
ADD
CONSTRAINT [DF_stats_LogStamp] DEFAULT NULL FOR [LogStamp],
CONSTRAINT [DF_stats_TaskID] DEFAULT 0 FOR [TaskID],
CONSTRAINT [DF_stats_Node] DEFAULT 0 FOR [Node],
CONSTRAINT [DF_stats_NominalStart] DEFAULT '' FOR [NominalStart],
CONSTRAINT [DF_stats_Action] DEFAULT NULL FOR [Action],
CONSTRAINT [DF_stats_SourceHost] DEFAULT NULL FOR [SourceHost],
CONSTRAINT [DF_stats_SourceFilename] DEFAULT NULL FOR [SourceFilename],
CONSTRAINT [DF_stats_SourceFilenameOnly] DEFAULT NULL FOR [SourceFilenameOnly],
CONSTRAINT [DF_stats_SourceFileID] DEFAULT '' FOR [SourceFileID],
CONSTRAINT [DF_stats_SourceStamp] DEFAULT NULL FOR [SourceStamp],
CONSTRAINT [DF_stats_SourceNBytes] DEFAULT '-1' FOR [SourceNBytes],
CONSTRAINT [DF_stats_SourceDuration] DEFAULT '-1' FOR [SourceDuration],
CONSTRAINT [DF_stats_DestHost] DEFAULT NULL FOR [DestHost],
CONSTRAINT [DF_stats_DestFilename] DEFAULT NULL FOR [DestFilename],
CONSTRAINT [DF_stats_DestFilenameOnly] DEFAULT NULL FOR [DestFilenameOnly],
CONSTRAINT [DF_stats_DestFileID] DEFAULT '' FOR [DestFileID],
CONSTRAINT [DF_stats_NBytes] DEFAULT NULL FOR [NBytes],
CONSTRAINT [DF_stats_DestDuration] DEFAULT '-1' FOR [DestDuration],
CONSTRAINT [DF_stats_ErrCode] DEFAULT NULL FOR [ErrCode],
CONSTRAINT [DF_stats_Message] DEFAULT NULL FOR [Message],
CONSTRAINT [DF_stats_Hash] DEFAULT NULL FOR [Hash],
CONSTRAINT [PK_stats] PRIMARY KEY ([ID])
GO
CREATE NONCLUSTERED INDEX
[IX_stats_StatsUniqueRun]
ON
[dbo].[stats]
(
TaskID ASC,NominalStart ASC
)
GO
CREATE NONCLUSTERED INDEX
[IX_stats_Action]
ON
[dbo].[stats]
(
[Action] ASC
)
GO
CREATE NONCLUSTERED INDEX
[IX_stats_ErrCode]
ON
[dbo].[stats]
(
[ErrCode] ASC
)
GO
CREATE NONCLUSTERED INDEX
[IX_stats_LogStampTaskIDIndex]
ON
[dbo].[stats]
(
[LogStamp] ASC, [TaskID] ASC
)
GO
CREATE TABLE
[dbo].[taskgroups]
(
GroupName [varchar](50) NULL,
TaskID [int] NOT NULL
);
GO
ALTER TABLE
[dbo].[taskgroups]
ADD
CONSTRAINT [DF_taskgroups_GroupName] DEFAULT NULL FOR [GroupName],
CONSTRAINT [DF_taskgroups_TaskID] DEFAULT 0 FOR [TaskID]
GO
CREATE TABLE
[dbo].[taskruns]
(
ID [bigint] NOT NULL IDENTITY (1,2) NOT FOR REPLICATION,
LogStamp [varchar](24) NULL,
TaskID [int] NOT NULL,
Node [smallint] NOT NULL,
NominalStart [varchar](24) NOT NULL,
TaskName [varchar](200) NULL,
RecType [varchar](8) NULL,
TimeStarted [varchar](24) NULL,
TimeEnded [varchar](24) NULL,
StartedBy [varchar](32) NULL,
Success [varchar](12) NULL,
FilesSent [int] NULL,
TotalBytesSent float NULL,
HasBeenRead [int] NULL,
LastErrorType [int] NULL,
LastErrorText [varchar](250) NULL,
Hash [varchar](40) NULL,
);
GO
ALTER TABLE
[dbo].[taskruns]
ADD
CONSTRAINT [DF_taskruns_LogStamp] DEFAULT NULL FOR [LogStamp],
CONSTRAINT [DF_taskruns_TaskID] DEFAULT 0 FOR [TaskID],
CONSTRAINT [DF_taskruns_Node] DEFAULT 0 FOR [Node],
CONSTRAINT [DF_taskruns_NominalStart] DEFAULT '' FOR [NominalStart],
CONSTRAINT [DF_taskruns_TaskName] DEFAULT NULL FOR [TaskName],
CONSTRAINT [DF_taskruns_RecType] DEFAULT NULL FOR [RecType],
CONSTRAINT [DF_taskruns_TimeStarted] DEFAULT NULL FOR [TimeStarted],
CONSTRAINT [DF_taskruns_TimeEnded] DEFAULT NULL FOR [TimeEnded],
CONSTRAINT [DF_taskruns_StartedBy] DEFAULT NULL FOR [StartedBy],
CONSTRAINT [DF_taskruns_Success] DEFAULT NULL FOR [Success],
CONSTRAINT [DF_taskruns_FilesSent] DEFAULT NULL FOR [FilesSent],
CONSTRAINT [DF_taskruns_TotalBytesSent] DEFAULT NULL FOR [TotalBytesSent],
CONSTRAINT [DF_taskruns_HasBeenRead] DEFAULT 0 FOR [HasBeenRead],
CONSTRAINT [DF_taskruns_LastErrorType] DEFAULT NULL FOR [LastErrorType],
CONSTRAINT [DF_taskruns_LastErrorText] DEFAULT NULL FOR [LastErrorText],
CONSTRAINT [DF_taskruns_Hash] DEFAULT NULL FOR [Hash],
CONSTRAINT [PK_taskruns] PRIMARY KEY ([ID])
GO
CREATE NONCLUSTERED INDEX
[IX_taskruns_TaskRunsUniqueRun]
ON
[dbo].[taskruns]
(
[TaskID] ASC, [NominalStart] ASC
)
GO
CREATE NONCLUSTERED INDEX
[IX_taskruns_Success]
ON
[dbo].[taskruns]
(
[Success] ASC
)
GO
CREATE NONCLUSTERED INDEX
[IX_taskruns_HasBeenRead]
ON
[dbo].[taskruns]
(
[HasBeenRead] ASC
)
GO
CREATE NONCLUSTERED INDEX
[IX_taskruns_LogStampTaskIDIndex]
ON
[dbo].[taskruns]
(
[LogStamp] ASC, [TaskID] ASC
)
GO
CREATE TABLE tmplastruns
(
TaskIDOfMax [int] NULL,
IDOfMax [bigint] NULL
);
GO
ALTER TABLE
[dbo].[tmplastruns]
ADD
CONSTRAINT [DF_tmplastruns_TaskIDOfMax] DEFAULT NULL FOR [TaskIDOfMax],
CONSTRAINT [DF_tmplastruns_IDOfMax] DEFAULT NULL FOR [IDOfMax]
GO