AD RMS Log Consolidation Sample

Applies To: Windows Server 2008, Windows Server 2008 R2

Following is a copy of the logging database consolidation script used at Microsoft to aggregate data from different AD RMS clusters to a single consolidated database, for archival and reporting purposes. If you desire to implement a similar process another environment, it should be reviewed and adapted to your environment’s needs and specific characteristics.

The Steps to implement the log consolidation solution are:

1. Link the source SQL server and archive SQL server if there is no link between the servers yet.

2. Backup/restore the source logging DB

   1. Take a full backup of logging DB on the source SQL server.

   2. Move the full backup file to the archive SQL server.

   3. Create a new logging DB named DRMS_Logging_Archive on the archive SQL Server.

   4. Restore the backup file to the DRMS_Logging_Archive DB on the archive SQL Server.

3. If the source logging db contains a big amount of data and you want to keep the size small. You can run the attached script C.1. TruncateData.SQL on the source logging db. This script will truncate all the records in following tables: ServiceRequestAndCertificate, ServiceRequest, Certificate and XrmlObject

4. Run attached script C.2 CreateMappingTables.sql on DRMS_Logging_Archive database of the archive SQL Server to create the mapping tables for the archive process.

5. Create a folder on the archive SQL Server. Such as D:\ArchiveScripts

6. Copy attached script C.3. ArchiveV2LoggingDB.sql   to the folder

7. Create an SQL Agent job on the archive SQL Server to do the archival at your desired frequency, such as once per hour. The sample command for the job is :sqlcmd -i D:\ArchiveScripts\ArchiveV2LoggingDB.sql -v SourceSVR = "SourceServer" SourceDB = "DRMS_Logging_SourceServer " ArchiveDB="DRMS_Logging_Archive"

TruncateData.sql

-- //////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- Description:
--     This script can be used to Truncate all the records in following tables: ServiceRequestAndCertificate, ServiceRequest, Certificate and XrmlObject if you want to keep the source logging DB small.
--////////////////////////////////////////////////////////////////////////////////////////////////////////////////


ALTER TABLE [dbo].[ServiceRequestAndCertificate] DROP CONSTRAINT [FK_REQUESTID_ServiceRequestAndCertificate_ServiceRequest]
ALTER TABLE [dbo].[ServiceRequestAndCertificate] DROP CONSTRAINT [FK_CERTIFICATEID_ServiceRequestAndCertificate_Certificate]

ALTER TABLE [dbo].[Certificate] DROP CONSTRAINT [FK_ISSUEDPRINCIPALOBJECTID_Certificate_XrmlObject]
ALTER TABLE [dbo].[Certificate] DROP CONSTRAINT [FK_ISSUEROBJECTID_Certificate_XrmlObject]
ALTER TABLE [dbo].[Certificate] DROP CONSTRAINT [FK_WORKOBJECTID_Certificate_XrmlObject]
ALTER TABLE [dbo].[Certificate] DROP CONSTRAINT [FK_FEDERATIONPRINCIPALOBJECTID_Certificate_XrmlObject]
GO

-------------------------------
TRUNCATE TABLE ServiceRequestAndCertificate
TRUNCATE TABLE ServiceRequest
TRUNCATE TABLE Certificate
TRUNCATE TABLE XrmlObject
GO

--------------------------------
ALTER TABLE [dbo].[ServiceRequestAndCertificate] ADD CONSTRAINT [FK_REQUESTID_ServiceRequestAndCertificate_ServiceRequest] FOREIGN KEY 
(
[ServiceRequestId]
) REFERENCES [ServiceRequest] (
[ServiceRequestId]
)
ALTER TABLE [dbo].[ServiceRequestAndCertificate] ADD CONSTRAINT [FK_CERTIFICATEID_ServiceRequestAndCertificate_Certificate] FOREIGN KEY 
(
[CertificateId]
) REFERENCES [Certificate] (
[CertificateId]
)


ALTER TABLE [dbo].[Certificate] ADD CONSTRAINT [FK_ISSUEDPRINCIPALOBJECTID_Certificate_XrmlObject] FOREIGN KEY 
(
[IssuedPrincipalObjectId]
) REFERENCES [XrmlObject] (
[XrmlObjectId]
)
ALTER TABLE [dbo].[Certificate] ADD CONSTRAINT [FK_ISSUEROBJECTID_Certificate_XrmlObject] FOREIGN KEY 
(
[IssuerObjectId]
) REFERENCES [XrmlObject] (
[XrmlObjectId]
)
ALTER TABLE [dbo].[Certificate] ADD CONSTRAINT [FK_WORKOBJECTID_Certificate_XrmlObject] FOREIGN KEY 
(
[WorkObjectId]
) REFERENCES [XrmlObject] (
[XrmlObjectId]
)
ALTER TABLE [dbo].[Certificate] ADD CONSTRAINT [FK_FEDERATIONPRINCIPALOBJECTID_Certificate_XrmlObject] FOREIGN KEY 
(
[FederationPrincipalObjectId]
) REFERENCES [XrmlObject] (
[XrmlObjectId]
)
GO

The following SQL script can be used to create the necessary tables on the archive database.

CreateMappingTables.sql

-- //////////////////////////////////////////////////////////////////////////////////////////////////////////////////
-- Description:
--      This script can be used to create config table and mapping tables on archive logging database
--      Please specify the source sql server in this file.   
--////////////////////////////////////////////////////////////////////////////////////////////////////////////////

CREATE TABLE [SourceServer] (
[ServerId] [int] IDENTITY (1, 1) NOT NULL ,
[ServerName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
CONSTRAINT [PK_SourceServer] PRIMARY KEY  CLUSTERED 
(
[ServerId]
)  ON [PRIMARY] 
) ON [PRIMARY]
INSERT INTO SourceServer VALUES('SQLSERVERNAME')
GO

CREATE TABLE [ArchiveConfig] (
[ArchiveConfigId] [int] IDENTITY (1, 1) NOT NULL ,
[ServerId] [int] NOT NULL ,
[running] [bit] NOT NULL ,
[badqueuedatalog_index] [int] NOT NULL ,
[UserDomain_index] [int] NOT NULL ,
[requestuser_index] [int] NOT NULL ,
[requesttype_index] [int] NOT NULL ,
[useragent_index] [int] NOT NULL ,
[serverinformation_index] [int] NOT NULL ,
[ErrorDescription_index] [int] NOT NULL ,
[ErrorInformation_index] [int] NOT NULL ,
[ServiceRequest_index] [int] NOT NULL ,
[CertificateType_index] [int] NOT NULL ,
[XrmlObject_index] [int] NOT NULL ,
CONSTRAINT [PK__ArchiveConfig__22AA2996] PRIMARY KEY  CLUSTERED 
(
[ArchiveConfigId]
)  ON [PRIMARY] ,
CONSTRAINT [FK_ArchiveConfig_SourceServer] FOREIGN KEY 
(
[ServerId]
) REFERENCES [SourceServer] (
[ServerId]
)
) ON [PRIMARY]
GO

--Get the initial indexes
INSERT INTO ArchiveConfig VALUES (1,0,0,0,0,0,0,0,0,0,0,0,0)
update ArchiveConfig set 
badqueuedatalog_index = (isnull((select top 1 badqueuedatalogid from BadQueueDataLog order by badqueuedatalogid desc), 0)),
userdomain_index = (isnull((select top 1 userdomainid from Userdomain order by userdomainid desc), 0)),
requestuser_index = (isnull((select top 1 requestuserid from requestuser order by requestuserid desc), 0)),
requesttype_index = (isnull((select top 1 requesttypeid from requesttype order by requesttypeid desc), 0)),
useragent_index = (isnull((select top 1 useragentid from useragent order by useragentid desc), 0)),
serverinformation_index = (isnull((select top 1 serverinformationid from serverinformation order by serverinformationid desc), 0)),
ErrorInformation_index = (isnull((select top 1 ErrorInformationid from ErrorInformation order by ErrorInformationid desc), 0)),
--ServiceRequest_index = (isnull((select top 1 ServiceRequestid from ServiceRequest order by ServiceRequestid desc), 0)),
ServiceRequest_index = 0,
CertificateType_index = (isnull((select top 1 CertificateTypeid from CertificateType order by CertificateTypeid desc), 0)),
--XrmlObject_index = (isnull((select top 1 XrmlObjectid from XrmlObject order by XrmlObjectId desc), 0))
XrmlObject_index = 0
where ServerId = 1
GO

--Create UserDomain_Mapping
CREATE TABLE [UserDomain_Mapping] (
[ori_domainid] [int] NOT NULL ,
[arch_domainid] [int] NOT NULL ,
[serverid] [tinyint] NOT NULL ,
CONSTRAINT [PK_UserDomain_Mapping] PRIMARY KEY  CLUSTERED 
(
[ori_domainid],
[serverid]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO
CREATE  INDEX [IX_UserDomainMapping_ServerId] ON [dbo].[UserDomain_Mapping]([serverid]) ON [PRIMARY]
GO
CREATE  INDEX [IX_UserDomainMapping_OriDomainId] ON [dbo].[UserDomain_Mapping]([ori_domainid]) ON [PRIMARY]
GO

INSERT INTO [UserDomain_Mapping](ori_domainid, arch_domainid, serverid)
(
SELECT userdomainid, userdomainid, 1 FROM userdomain
)

--Create RequestUser_Mapping
CREATE TABLE [RequestUser_Mapping] (
[ori_requestuserid] [int] NOT NULL ,
[arch_requestuserid] [int] NOT NULL ,
[serverid] [tinyint] NOT NULL ,
CONSTRAINT [PK_RequestUser_Mapping] PRIMARY KEY  CLUSTERED 
(
[ori_requestuserid],
[serverid]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO
CREATE  INDEX [IX_RequestUserMapping_ServerId] ON [dbo].[RequestUser_Mapping]([serverid]) ON [PRIMARY]
GO
CREATE  INDEX [IX_RequestUserMapping_OriRequestUserId] ON [dbo].[RequestUser_Mapping]([ori_requestuserid]) ON [PRIMARY]
GO

INSERT INTO RequestUser_Mapping(ori_requestuserid, arch_requestuserid, serverid)
(
SELECT RequestUserid, RequestUserid, 1 FROM RequestUser
)

--Create ServerInformation_Mapping
CREATE TABLE [ServerInformation_Mapping] (
[ori_serverinformationid] [int] NOT NULL ,
[arch_serverinformationid] [int] NOT NULL ,
[ServerId] [tinyint] NOT NULL ,
CONSTRAINT [PK_ServerInformation_Mapping] PRIMARY KEY  CLUSTERED 
(
[ori_serverinformationid],
[ServerId]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO
CREATE  INDEX [IX_ServerInfoMapping_ServerId] ON [dbo].[ServerInformation_Mapping]([ServerId]) ON [PRIMARY]
GO
CREATE  INDEX [IX_ServerInfoMapping_OriServerInfoId] ON [dbo].[ServerInformation_Mapping]([ori_serverinformationid]) ON [PRIMARY]
GO

INSERT INTO ServerInformation_Mapping(ori_ServerInformationid, arch_ServerInformationid, serverid)
(
SELECT ServerInformationid, ServerInformationid, 1 FROM ServerInformation
)

--Create UserAgent_Mapping
CREATE TABLE [UserAgent_Mapping] (
[ori_useragentid] [int] NOT NULL ,
[arch_useragentid] [int] NOT NULL ,
[serverid] [tinyint] NOT NULL ,
CONSTRAINT [PK_useragent_Mapping] PRIMARY KEY  CLUSTERED 
(
[ori_useragentid],
[serverid]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO
CREATE  INDEX [IX_UserAgentMapping_ServerId] ON [dbo].[UserAgent_Mapping]([serverid]) ON [PRIMARY]
GO
CREATE  INDEX [IX_UserAgentMapping_OriUserAgentId] ON [dbo].[UserAgent_Mapping]([ori_useragentid]) ON [PRIMARY]
GO

INSERT INTO UserAgent_Mapping(ori_UserAgentid, arch_UserAgentid, serverid)
(
SELECT UserAgentid, UserAgentid, 1 FROM UserAgent
)

--Create RequestType_Mapping
CREATE TABLE [RequestType_Mapping] (
[ori_requesttypeid] [int] NOT NULL ,
[arch_requesttypeid] [int] NOT NULL ,
[ServerId] [tinyint] NOT NULL ,
CONSTRAINT [PK_requesttype_Mapping] PRIMARY KEY  CLUSTERED 
(
[ori_requesttypeid],
[serverid]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO
CREATE  INDEX [IX_RequestTypeMapping_ServerId] ON [dbo].[RequestType_Mapping]([ServerId]) ON [PRIMARY]
GO
CREATE  INDEX [IX_RequestTypeMapping_OriRequestTypeId] ON [dbo].[RequestType_Mapping]([ori_requesttypeid]) ON [PRIMARY]
GO

INSERT INTO RequestType_Mapping(ori_RequestTypeid, arch_RequestTypeid, serverid)
(
SELECT RequestTypeid, RequestTypeid, 1 FROM RequestType
)

--Create CertificateType_Mapping
CREATE TABLE [CertificateType_Mapping] (
[ori_CertificateTypeid] [int] NOT NULL ,
[arch_CertificateTypeid] [int] NOT NULL ,
[ServerId] [tinyint] NOT NULL ,
CONSTRAINT [PK_CertificateType_Mapping] PRIMARY KEY  CLUSTERED 
(
[ori_CertificateTypeid],
[serverid]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO
CREATE  INDEX [IX_CertificateTypeMapping_ServerId] ON [dbo].[CertificateType_Mapping]([ServerId]) ON [PRIMARY]
GO
CREATE  INDEX [IX_CertificateTypeMapping_OriCertificateTypeid] ON [dbo].[CertificateType_Mapping]([ori_CertificateTypeid]) ON [PRIMARY]
GO
INSERT INTO CertificateType_Mapping(ori_CertificateTypeid, arch_CertificateTypeid, serverid)
(
SELECT CertificateTypeid, CertificateTypeid, 1 FROM CertificateType
)

--Create ErrorInformation_Mapping
CREATE TABLE [ErrorInformation_Mapping] (
[ori_errorinformationid] [int] NOT NULL ,
[mapping_errorinformationid] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_ErrorInformation_trans] PRIMARY KEY  CLUSTERED 
(
[ori_errorinformationid]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO

--Create ServiceRequest_Mapping
CREATE TABLE [ServiceRequest_Mapping] (
[ori_ServiceRequestId] [int] NOT NULL ,
[mapping_ServiceRequestId] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_ServiceRequest_trans] PRIMARY KEY  CLUSTERED 
(
[ori_ServiceRequestId]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO

--Create TempCertificateId
CREATE TABLE [TempCertificateId] (
[TempCertId] [int] NOT NULL ,
CONSTRAINT [PK_TempCertificateId] PRIMARY KEY  CLUSTERED 
(
[TempCertId]
)  ON [PRIMARY] 
) ON [PRIMARY]
GO

--Create XrmlObject_Mapping
CREATE TABLE [dbo].[XrmlObject_Mapping](
[ori_xrmlobjectid] [int] NOT NULL,
[arch_xrmlobjectid] [int] NOT NULL,
[serverid] [tinyint] NOT NULL,
CONSTRAINT [PK_xrmlobject_mapping] PRIMARY KEY CLUSTERED 
(
[ori_xrmlobjectid],
[serverid]
) ON [PRIMARY] 
) ON [PRIMARY]
GO
CREATE  INDEX [IX_XrmlObjectMapping_ServerId] ON [dbo].[XrmlObject_Mapping]([ServerId]) ON [PRIMARY]
GO
CREATE  INDEX [IX_XrmlObject_OriXrmlObjectid] ON [dbo].[XrmlObject_Mapping]([ori_XrmlObjectid]) ON [PRIMARY]
GO

ArchiveV2LoggingDB.sql

-- ///////////////////////////////////////////////////////////////////////////////////////////
-- Description:
--        This script is used to archive AD RMS logging databases.  
-- ///////////////////////////////////////////////////////////////////////////////////////////
SELECT TOP 1 ServiceRequestId FROM [$(SOURCESVR)].$(SourceDB).dbo.ServiceRequest

DECLARE @serverid int

SET @serverid = ( SELECT serverid FROM $(ArchiveDB).dbo.SourceServer where servername = '$(SOURCESVR)')

-- Raise error if a previous archive task is still running
IF ((SELECT running FROM $(ArchiveDB).dbo.ArchiveConfig WHERE ServerId = @serverid) = 1)
BEGIN
RAISERROR ('A previous archive task is still running.', 16, 1)
RETURN
END
ELSE 
UPDATE $(ArchiveDB).dbo.ArchiveConfig SET running = 1 WHERE Serverid = @serverid

DECLARE @badqueuedatalog_start_index int,
@badqueuedatalog_end_index int,
@userdomain_start_index int,
@userdomain_end_index int,
@requestuser_start_index int,
@requestuser_end_index int,
@requesttype_start_index int,
@requesttype_end_index int,
@useragent_start_index int,
@useragent_end_index int,
@serverinformation_start_index int,
@serverinformation_end_index int,
@ErrorInformation_start_index int,
@ErrorInformation_end_index int,
@ServiceRequest_start_index int,
@ServiceRequest_end_index int,
@CertificateType_start_index int,
@CertificateType_end_index int,
@XrmlObject_start_index int,
@XrmlObject_end_index int,
@errorinformation_curint,
@errorinformation_mapping_curint,
@errorinformation_difint,
@servicerequest_curint,
@mapping_servicerequest_cur int,
@servicerequest_difint,
@servicerequestidint,
@certificate_start_index int

-----------
--- Get start and end Index
------------
select  @badqueuedatalog_start_index = badqueuedatalog_index,
@useragent_start_index = useragent_index,
@requesttype_start_index = requesttype_index,
@requestuser_start_index = requestuser_index,
@userdomain_start_index = userdomain_index, 
@serverinformation_start_index = serverinformation_index, 
@ErrorInformation_start_index = ErrorInformation_index, 
@CertificateType_start_index = CertificateType_index,  
@servicerequest_start_index = servicerequest_index, 
@XrmlObject_start_index = XrmlObject_index from $(ArchiveDB).dbo.ArchiveConfig 
where serverid = @serverid

set @ServiceRequest_end_index =(isnull((select top 1 ServiceRequestId from [$(SOURCESVR)].$(SourceDB).dbo.ServiceRequest 
    order by ServiceRequestId desc), 0))

set @requestuser_end_index = (isnull((select top 1 requestuserid from [$(SOURCESVR)].$(SourceDB).dbo.RequestUser 
    order by requestuserid desc), 0))

set @ErrorInformation_end_index = (isnull((select top 1 ErrorInformationId from [$(SOURCESVR)].$(SourceDB).dbo.ErrorInformation 
    order by ErrorInformationId desc), 0))

set @XrmlObject_end_index = (isnull((select top 1 XrmlObjectid from [$(SOURCESVR)].$(SourceDB).dbo.XrmlObject 
    order by XrmlObjectid desc), 0))

set @badqueuedatalog_end_index = (isnull((select top 1 badqueuedatalogid from [$(SOURCESVR)].$(SourceDB).dbo.BadQueueDataLog
    order by badqueuedatalogid desc), 0))

set @useragent_end_index = (isnull((select top 1 useragentid from [$(SOURCESVR)].$(SourceDB).dbo.useragent 
    order by useragentid desc), 0))

set @requesttype_end_index = (isnull((select top 1 requesttypeid from [$(SOURCESVR)].$(SourceDB).dbo.requesttype 
    order by requesttypeid desc), 0))

set @userdomain_end_index = (isnull((select top 1 userdomainid from [$(SOURCESVR)].$(SourceDB).dbo.Userdomain 
    order by userdomainid desc), 0))

set @serverinformation_end_index = (isnull((select top 1 serverinformationid from [$(SOURCESVR)].$(SourceDB).dbo.serverinformation 
    order by serverinformationid desc), 0))

set @CertificateType_end_index = (isnull((select top 1 CertificateTypeid from [$(SOURCESVR)].$(SourceDB).dbo.CertificateType 
    order by CertificateTypeid desc), 0))

-----------
--- Copy BadQueueDataLog 
-----------------------
if (@badqueuedatalog_end_index > @badqueuedatalog_start_index)
begin
-- Archive BadQueueDataLog table
INSERT INTO $(ArchiveDB).dbo.BadQueueDataLog (ErrorCode, ServerHostName, RequestType, MessageContents, CreatedTime)
(
SELECT ErrorCode, ServerHostName, RequestType, MessageContents, CreatedTime FROM [$(SOURCESVR)].$(SourceDB).dbo.BadQueueDataLog
WHERE (badqueuedatalogid > @badqueuedatalog_start_index) and (badqueuedatalogid <= @badqueuedatalog_end_index) 
)

-- Update BadQueueDataLog index on ArchiveConfig table
update $(ArchiveDB).dbo.ArchiveConfig set badqueuedatalog_index = @badqueuedatalog_end_index 
where serverid = @serverid
end

-----------
--- Copy UserDomain 
-----------------------
if (@userdomain_end_index > @userdomain_start_index)
begin
-- Archive UserDomain table
insert into $(ArchiveDB).dbo.Userdomain ( name )
(
select a.name from [$(SOURCESVR)].$(SourceDB).dbo.Userdomain as a
where (userdomainid > @userdomain_start_index) and (userdomainid <= @userdomain_end_index) 
and not exists 
(
   select b.name from $(ArchiveDB).dbo.userdomain as b 
   where b.name = a.name
   )
)
-- Add the mapping between original DomainId and archive DomainId to userdomain_mapping table
insert into $(ArchiveDB).dbo.userdomain_mapping (ori_domainid, arch_domainid, serverid)
(
select a.userdomainid , b.userdomainid, @serverid from [$(SOURCESVR)].$(SourceDB).dbo.Userdomain as a
inner join $(ArchiveDB).dbo.userdomain as b
on a.name = b.name
where (a.userdomainid > @userdomain_start_index) and (a.userdomainid <= @userdomain_end_index) 
)

-- Update UserDomain index on ArchiveConfig table
update $(ArchiveDB).dbo.ArchiveConfig set userdomain_index = @userdomain_end_index 
where serverid = @serverid
end

-----------
--- Copy RequestUser 
-----------------------------
if (@requestuser_end_index > @requestuser_start_index)
begin
-- Archive RequestUser table
insert into $(ArchiveDB).dbo.requestuser ( userdomainid, username )
(
select b.arch_domainid, a.username from [$(SOURCESVR)].$(SourceDB).dbo.requestuser as a
left join $(ArchiveDB).dbo.userdomain_mapping as b
on b.ori_domainid = a.userdomainid and b.serverid = @serverid
where (a.requestuserid > @requestuser_start_index) and (a.requestuserid <= @requestuser_end_index)
and not exists 
(
select c.username from $(ArchiveDB).dbo.requestuser as c 
where (isnull(c.userdomainid,0) = isnull(b.arch_domainid,0)) and (c.username = a.username)
)
)

-- Add the mapping between original RequestUserId and archive RequestUserId to requestuser_mapping table
insert into $(ArchiveDB).dbo.requestuser_mapping (ori_requestuserid, arch_requestuserid, serverid)
(
select a.requestuserid, b.requestuserid, @serverid from [$(SOURCESVR)].$(SourceDB).dbo.requestuser as a
left join $(ArchiveDB).dbo.userdomain_mapping as c
on c.ori_domainid = a.userdomainid and c.serverid = @serverid
left join $(ArchiveDB).dbo.requestuser as b
on (b.username = a.username) and (b.userdomainid = c.arch_domainid)
where (a.requestuserid > @requestuser_start_index) and (a.requestuserid <= @requestuser_end_index)
)

-- Update RequestUser index on ArchiveConfig table
update $(ArchiveDB).dbo.ArchiveConfig set requestuser_index = @requestuser_end_index 
where serverid = @serverid
end

-----------
--- Copy RequestType
-----------------------
if (@requesttype_end_index > @requesttype_start_index)
begin
-- Archive RequestType table
insert into $(ArchiveDB).dbo.requesttype ( type, path )
(
   select a.type, a.path from [$(SOURCESVR)].$(SourceDB).dbo.requesttype as a
   where (a.requesttypeid > @requesttype_start_index) and (a.requesttypeid <= @requesttype_end_index)
   and not exists 
   (
select b.type from $(ArchiveDB).dbo.requesttype as b 
where (isnull(b.type, '') = isnull(a.type,'')) and (isnull(b.path, '') = isnull(a.path, ''))
   )
)

-- Add the mapping between original RequestTypeId and archive RequestTypeId to requesttype_mapping table
insert into $(ArchiveDB).dbo.requesttype_mapping (ori_requesttypeid, arch_requesttypeid, serverid)
(
   select a.requesttypeid , b.requesttypeid, @serverid from [$(SOURCESVR)].$(SourceDB).dbo.requesttype as a
   inner join $(ArchiveDB).dbo.requesttype as b
   on (isnull(b.type, '')  = isnull(a.type, '' )) and (isnull(b.path, '') = isnull(a.path, ''))
   where (a.requesttypeid > @requesttype_start_index) and (a.requesttypeid <= @requesttype_end_index)
)

-- Update RequestType index on ArchiveConfig table
update $(ArchiveDB).dbo.ArchiveConfig set requesttype_index = @requesttype_end_index 
where serverid = @serverid
end

-----------
--- Copy UserAgent 
-----------------------
if (@useragent_end_index > @useragent_start_index)
begin
-- Archive UserAgent table
insert into $(ArchiveDB).dbo.useragent ( name )
(
   select a.name from [$(SOURCESVR)].$(SourceDB).dbo.useragent as a
   where (a.useragentid > @useragent_start_index) and (a.useragentid <= @useragent_end_index)
   and not exists 
   (
   select b.name from $(ArchiveDB).dbo.useragent as b 
   where b.name = a.name
   )
)

-- Add the mapping between original UserAgentId and archive UserAgentId to useragent_mapping table
insert into $(ArchiveDB).dbo.useragent_mapping (ori_useragentid, arch_useragentid, serverid)
(
   select a.useragentid , b.useragentid, @serverid from [$(SOURCESVR)].$(SourceDB).dbo.useragent as a
   inner join $(ArchiveDB).dbo.useragent as b
   on a.name = b.name
   where (a.useragentid > @useragent_start_index) and (a.useragentid <= @useragent_end_index)
)

-- Update UserAgent index on ArchiveConfig table
update $(ArchiveDB).dbo.ArchiveConfig set useragent_index = @useragent_end_index 
where serverid = @serverid
end

-----------
--- Copy ServerInformation 
-----------------------
if (@serverinformation_end_index > @serverinformation_start_index)
begin
-- Archive ServerInformation table
insert into $(ArchiveDB).dbo.serverinformation ( machinename )
(
   select a.machinename from [$(SOURCESVR)].$(SourceDB).dbo.serverinformation as a
   where (a.serverinformationid > @serverinformation_start_index) and (a.serverinformationid <= @serverinformation_end_index)
   and not exists 
   (
   select b.machinename from $(ArchiveDB).dbo.serverinformation as b 
   where b.machinename = a.machinename
   )
)

-- Add the mapping between original ServerInformationId and archive ServerInformationId to useragent_mapping table
insert into $(ArchiveDB).dbo.serverinformation_mapping (ori_serverinformationid, arch_serverinformationid, serverid)
(
   select a.serverinformationid , b.serverinformationid, @serverid from [$(SOURCESVR)].$(SourceDB).dbo.serverinformation as a
   inner join $(ArchiveDB).dbo.serverinformation as b
   on a.machinename = b.machinename
   where (a.serverinformationid > @serverinformation_start_index) and (a.serverinformationid <= @serverinformation_end_index)
)

-- Update ServerInformation index on ArchiveConfig table
update $(ArchiveDB).dbo.ArchiveConfig set serverinformation_index = @serverinformation_end_index 
where serverid = @serverid
end

-----------
--- Copy ErrorDescription 
-----------------------
-- Archive ErrorDescription table
insert into $(ArchiveDB).dbo.ErrorDescription ( ErrorDescriptionId, errormessage, exceptiontype, wellknowntype, errordetail )
(
select a.ErrorDescriptionId, a.errormessage, a.exceptiontype, a.wellknowntype, a.errordetail  from [$(SOURCESVR)].$(SourceDB).dbo.ErrorDescription as a
where not exists 
(
select ErrorDescriptionId from $(ArchiveDB).dbo.ErrorDescription as b 
where (b.ErrorDescriptionId = a.ErrorDescriptionId)
)
)

-----------
--- Copy ErrorInformation 
----------------------------
if (@ErrorInformation_end_index > @ErrorInformation_start_index)
begin
-- Archive ErrorInformation table
insert into $(ArchiveDB).dbo.ErrorInformation ( errordescriptionid, eventid, datafromrequest)
select errordescriptionid, eventid, datafromrequest from [$(SOURCESVR)].$(SourceDB).dbo.ErrorInformation
where (ErrorInformationId > @ErrorInformation_start_index) and (ErrorInformationId <= @ErrorInformation_end_index)
order by errorinformationid

-- Add the mapping between original ErrorInformationId and archive ErrorInformationId to useragent_mapping table
insert into $(ArchiveDB).dbo.ErrorInformation_mapping (ori_errorinformationid)
select errorinformationid from [$(SOURCESVR)].$(SourceDB).dbo.ErrorInformation
where (ErrorInformationId > @ErrorInformation_start_index) and (ErrorInformationId <= @ErrorInformation_end_index)
order by errorinformationid

set @errorinformation_cur = (select top 1 ErrorInformationId from $(ArchiveDB).dbo.ErrorInformation order by ErrorInformationId desc)
set @errorinformation_mapping_cur = (select top 1 mapping_errorinformationid from $(ArchiveDB).dbo.ErrorInformation_mapping order by mapping_errorinformationid desc)
set @errorinformation_dif = @errorinformation_cur - @errorinformation_mapping_cur

-- Update ErrorInformation index on ArchiveConfig table
update $(ArchiveDB).dbo.ArchiveConfig set ErrorInformation_index = @ErrorInformation_end_index 
where serverid = @serverid
end

-----------
--- Copy CertificateType 
-----------------------
if (@CertificateType_end_index > @CertificateType_start_index)
begin
-- Archive CertificateType table
insert into $(ArchiveDB).dbo.CertificateType ( type )
(
select a.type from [$(SOURCESVR)].$(SourceDB).dbo.CertificateType as a
where (a.CertificateTypeid > @CertificateType_start_index) and (a.CertificateTypeid <= @CertificateType_end_index)
and not exists 
(
select b.type from $(ArchiveDB).dbo.CertificateType as b 
where b.type = a.type
)
)

-- Add the mapping between original CertificateTypeId and archive CertificateTypeId to CertificateType_mapping table
insert into $(ArchiveDB).dbo.CertificateType_mapping (ori_CertificateTypeid, arch_CertificateTypeid, serverid)
(
select a.CertificateTypeid , b.CertificateTypeid, @serverid from [$(SOURCESVR)].$(SourceDB).dbo.CertificateType as a
inner join $(ArchiveDB).dbo.CertificateType as b
on a.type = b.type
where (a.CertificateTypeid > @CertificateType_start_index) and (a.CertificateTypeid <= @CertificateType_end_index)
)

-- Update CertificateType index on ArchiveConfig table
update $(ArchiveDB).dbo.ArchiveConfig set CertificateType_index = @CertificateType_end_index 
where serverid = @serverid
end

-----------
--- Copy XrmlObject 
-----------------------
if (@XrmlObject_end_index > @XrmlObject_start_index)
begin
-- Archive XrmlObject table
insert into $(ArchiveDB).dbo.XrmlObject ( id, type, name, address )
(
select a.id, a.type, a.name, a.address from [$(SOURCESVR)].$(SourceDB).dbo.XrmlObject as a
where (a.XrmlObjectid > @XrmlObject_start_index) and (a.XrmlObjectid <= @XrmlObject_end_index)
and not exists 
(
select b.id from $(ArchiveDB).dbo.XrmlObject as b 
where (isnull(b.id, '') = isnull(a.id, '')) 
and (isnull(b.type, '') = isnull(a.type, '')) 
and (isnull(b.name, '') = isnull(a.name, '')) 
and (isnull(b.address, '') = isnull(a.address, ''))
)
)

-- Add the mapping between original XrmlObjectId and archive XrmlObjectId to XrmlObject_mapping table
insert into $(ArchiveDB).dbo.XrmlObject_mapping (ori_XrmlObjectid, arch_XrmlObjectid, serverid)
(
select a.XrmlObjectid , b.XrmlObjectid, @serverid from [$(SOURCESVR)].$(SourceDB).dbo.XrmlObject as a
inner join $(ArchiveDB).dbo.XrmlObject as b
on (isnull(b.id, '') = isnull(a.id, '')) 
and (isnull(b.type, '') = isnull(a.type, '')) 
and (isnull(b.name, '') = isnull(a.name, '')) 
and (isnull(b.address, '') = isnull(a.address, ''))
where (a.XrmlObjectid > @XrmlObject_start_index) and (a.XrmlObjectid <= @XrmlObject_end_index)
)

-- Update XrmlObject index on ArchiveConfig table
update $(ArchiveDB).dbo.ArchiveConfig set XrmlObject_index = @XrmlObject_end_index 
where serverid = @serverid
end

-----------
--- Copy ServiceRequest 
-----------------------------
if (@ServiceRequest_end_index > @ServiceRequest_start_index)
begin
insert into $(ArchiveDB).dbo.servicerequest (success, requesttypeid, serverinformationid, errorinformationid, requestid, 
batchrequestid, issecureconnection, requestuserid, userhostaddress, userhostname, useragentid, contentlength, requestduration, requesttime, createdtime)
selecta.success, 
b.arch_requesttypeid, 
c.arch_serverinformationid, 
f.mapping_errorinformationid + @errorinformation_dif,
a.requestid, 
a.batchrequestid, 
a.issecureconnection, 
d.arch_requestuserid, 
a.userhostaddress, 
a.userhostname, 
e.arch_useragentid, 
a.contentlength, 
a.requestduration, 
a.requesttime, 
a.createdtime from [$(SOURCESVR)].$(SourceDB).dbo.servicerequest as a
left join $(ArchiveDB).dbo.requesttype_mapping as b
on b.ori_requesttypeid = a.requesttypeid and b.serverid = @serverid
left join $(ArchiveDB).dbo.serverinformation_mapping as c
on c.ori_serverinformationid = a.serverinformationid and c.serverid = @serverid
left join $(ArchiveDB).dbo.ErrorInformation_mapping as f
on f.ori_errorinformationid = a.errorinformationid
left join $(ArchiveDB).dbo.requestuser_mapping as d
on d.ori_requestuserid = a.requestuserid and d.serverid = @serverid
left join $(ArchiveDB).dbo.useragent_mapping as e
on e.ori_useragentid = a.useragentid and e.serverid = @serverid
where (a.ServiceRequestId > @ServiceRequest_start_index) and (a.ServiceRequestId <= @ServiceRequest_end_index)
order by a.ServiceRequestId

insert into $(ArchiveDB).dbo.servicerequest_mapping (ori_ServiceRequestId)
select ServiceRequestId from [$(SOURCESVR)].$(SourceDB).dbo.servicerequest
where (ServiceRequestId > @ServiceRequest_start_index) and (ServiceRequestId <= @ServiceRequest_end_index)
order by ServiceRequestId

set @servicerequest_cur = (select top 1 ServiceRequestId from $(ArchiveDB).dbo.servicerequest order by ServiceRequestId desc)
set @mapping_servicerequest_cur = (select top 1 mapping_ServiceRequestId from $(ArchiveDB).dbo.servicerequest_mapping order by mapping_ServiceRequestId desc)
set @servicerequest_dif =  @servicerequest_cur - @mapping_servicerequest_cur

-----------
--- Copy Certificate 
-----------------------
insert into $(ArchiveDB).dbo.tempcertificateid (TempCertId)
(
select related.certificateid from [$(SOURCESVR)].$(SourceDB).dbo.servicerequestandcertificate as related
inner join [$(SOURCESVR)].$(SourceDB).dbo.Certificate as h
on related.certificateid = h.certificateid
where ((related.servicerequestid > @ServiceRequest_start_index and related.servicerequestid <= @ServiceRequest_end_index)
and
not exists 
(
select g.certificateid from $(ArchiveDB).dbo.certificate as g
where g.hashvalue = h.hashvalue
))
group by related.certificateid
)

set @certificate_start_index = (select top 1 CertificateId from $(ArchiveDB).dbo.certificate order by CertificateId desc)

insert into $(ArchiveDB).dbo.Certificate (HashValue, ParentHashValue, GUID, CertificateTypeId, IssuedTime, ValidFrom, ValidUntil, IssuedPrincipalObjectId, IssuerObjectId, WorkObjectId, FederationPrincipalObjectId)
(
selecta.HashValue, 
a.ParentHashValue, 
a.GUID, 
b.arch_CertificateTypeId, 
a.IssuedTime, 
a.ValidFrom, 
a.ValidUntil, 
c.arch_XrmlObjectid, 
d.arch_XrmlObjectid, 
e.arch_XrmlObjectid, 
f.arch_XrmlObjectid 
from [$(SOURCESVR)].$(SourceDB).dbo.Certificate as a
left join $(ArchiveDB).dbo.CertificateType_mapping as b
on a.CertificateTypeId = b.Ori_CertificateTypeId and b.serverid = @serverid
left join $(ArchiveDB).dbo.XrmlObject_mapping as c
on a.IssuedPrincipalObjectId = c.ori_XrmlObjectid and c.serverid = @serverid
left join $(ArchiveDB).dbo.XrmlObject_mapping as d
on a.IssuerObjectId = d.ori_XrmlObjectid and d.serverid = @serverid
left join $(ArchiveDB).dbo.XrmlObject_mapping as e
on a.WorkObjectId = e.ori_XrmlObjectid and e.serverid = @serverid
left join $(ArchiveDB).dbo.XrmlObject_mapping as f
on a.FederationPrincipalObjectId = f.ori_XrmlObjectid and f.serverid = @serverid
inner join $(ArchiveDB).dbo.tempcertificateid as g
on a.certificateid =g.TempCertId
)

insert into $(ArchiveDB).dbo.servicerequestandcertificate (servicerequestid, certificateid)
(
select d.mapping_servicerequestid + @servicerequest_dif, c.certificateid from [$(SOURCESVR)].$(SourceDB).dbo.servicerequestandcertificate as a
inner join [$(SOURCESVR)].$(SourceDB).dbo.Certificate as b
on b.certificateid = a.certificateid
inner join $(ArchiveDB).dbo.certificate as c
on b.hashvalue = c.hashvalue
inner join $(ArchiveDB).dbo.ServiceRequest_mapping as d
on a.ServiceRequestId = d.ori_ServiceRequestId
where a.servicerequestid > @ServiceRequest_start_index and a.servicerequestid <= @ServiceRequest_end_index
)

update $(ArchiveDB).dbo.ArchiveConfig set ServiceRequest_index = @ServiceRequest_end_index 
where serverid = @serverid
end

-----------
--- Update Config Table
------------
update $(ArchiveDB).dbo.ArchiveConfig SET running = 0 WHERE serverid = @serverid

-----------
--- Truncate temp Tables
------------
truncate table $(ArchiveDB).dbo.ErrorInformation_mapping
truncate table $(ArchiveDB).dbo.ServiceRequest_mapping
truncate table $(ArchiveDB).dbo.TempCertificateId