|
|
|
|
|
This topic describes Microsoft SQL Server-specific details of how MOVEit Automation interfaces to its database.
When SQL Server has been selected as the database engine, MOVEit Automation manages its database automatically, so very few sites will need the information contained in this topic. However, unlike with MySQL, a MOVEit Automation installation does not install or update the SQL Server software itself. Therefore, a system administrator must make sure that periodic Microsoft updates are applied.
For the supported versions of Microsoft SQL Server, see MOVEit Automation Service Requirements.
To configure the MOVEit Automation connection to MS SQL Server, use the MOVEit Automation Config Utility.
MOVEit Automation 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,
ClientIP [varchar](16) 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