CREATE PROCEDURE sys.sp_MScreate_peer_tables
AS
BEGIN
DECLARE @retcode bit
begin transaction tran_sp_MScreate_peer_tables
save transaction tran_sp_MScreate_peer_tables
IF OBJECT_ID(N'MSpeer_lsns', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSpeer_lsns
(
id int identity(1,1) primary key nonclustered,
last_updated datetime default getdate(),
originator sysname,
originator_db sysname,
originator_publication sysname,
originator_publication_id int,
originator_db_version int,
originator_lsn varbinary(16),
originator_version int NULL, --server build version of the peer
originator_id int NULL -- 4 bytes are used
)
IF @@ERROR <> 0
GOTO UNDO
EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_lsns'
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
CREATE UNIQUE CLUSTERED INDEX uci_MSpeer_lsns
ON dbo.MSpeer_lsns
(
originator,
originator_db,
originator_publication_id,
originator_db_version,
originator_lsn
)
END
IF EXISTS (select * from sys.objects where name = 'syspublications')
BEGIN
IF OBJECT_ID(N'MSpeer_request', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSpeer_request
(
id int identity(1,1),
publication sysname,
sent_date datetime default getdate(),
description nvarchar(4000)
)
IF @@ERROR <> 0
GOTO UNDO
EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_request'
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
IF OBJECT_ID(N'MSpeer_response', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSpeer_response
(
request_id int,
peer sysname,
peer_db sysname,
received_date datetime NULL
)
IF @@ERROR <> 0
GOTO UNDO
EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_response'
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
IF OBJECT_ID(N'MSpeer_topologyrequest', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSpeer_topologyrequest
(
id int identity(1,1),
publication sysname,
sent_date datetime default getdate()
)
IF @@ERROR <> 0
GOTO UNDO
EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_topologyrequest'
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
IF OBJECT_ID(N'MSpeer_topologyresponse', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSpeer_topologyresponse
(
request_id int,
peer sysname,
peer_version int NULL, --server build version of the peer
peer_db sysname,
originator_id int NULL, -- 4 bytes are used
peer_conflict_retention int NULL,
received_date datetime NULL,
connection_info XML
)
IF @@ERROR <> 0
GOTO UNDO
EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_topologyresponse'
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
IF OBJECT_ID(N'MSpeer_originatorid_history', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSpeer_originatorid_history
(
originator_publication sysname,
originator_id int not null, -- 4 bytes are used
originator_node sysname,
originator_db sysname,
originator_db_version int not null,
originator_version int not null, --server build version of the peer
inserted_date datetime not null default getdate()
)
IF @@ERROR <> 0
GOTO UNDO
EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_originatorid_history'
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
CREATE UNIQUE CLUSTERED INDEX uci_MSpeer_originatorid_history
ON dbo.MSpeer_originatorid_history
(
originator_publication,
originator_id,
originator_node,
originator_db,
originator_db_version
)
END
IF OBJECT_ID(N'MSpeer_conflictdetectionconfigrequest', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSpeer_conflictdetectionconfigrequest
(
id int identity(1,1) not null primary key,
publication sysname,
sent_date datetime not null default getdate(),
timeout int not null, --seconds
modified_date datetime not null default getdate(),
progress_phase nvarchar(32) not null,
phase_timed_out bit not null
)
IF @@ERROR <> 0
GOTO UNDO
EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_conflictdetectionconfigrequest'
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
END
IF OBJECT_ID(N'MSpeer_conflictdetectionconfigresponse', 'U') is NULL
BEGIN
CREATE TABLE dbo.MSpeer_conflictdetectionconfigresponse
(
request_id int not null,
peer_node sysname,
peer_db sysname,
peer_version int NULL, --server build version of the peer
peer_db_version int NULL,
is_peer bit NULL, --once set, later rounds would expect response from it
conflictdetection_enabled bit NULL,
originator_id int NULL,
peer_conflict_retention int NULL,
peer_continue_onconflict bit NULL,
peer_subscriptions xml NULL, --list of (sub_node, sub_db)
progress_phase nvarchar(32) not null,
modified_date datetime default getdate()
)
IF @@ERROR <> 0
GOTO UNDO
EXEC @retcode = dbo.sp_MS_marksystemobject N'MSpeer_conflictdetectionconfigresponse'
IF @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
CREATE UNIQUE CLUSTERED INDEX uci_MSpeer_conflictdetectionconfigresponse
ON dbo.MSpeer_conflictdetectionconfigresponse
(
request_id,
peer_node,
peer_db
)
END
END
--add conflict detection alert on subscriber
declare @alert_name nvarchar(max) = formatmessage(22827)
,@alert_id int = 22815
,@category_name sysname
SELECT @category_name = name
FROM msdb.dbo.syscategories
WHERE category_id = 20
IF NOT EXISTS
(
SELECT *
FROM msdb.dbo.sysalerts
WHERE message_id = @alert_id
)
BEGIN
EXEC @retcode = msdb.dbo.sp_add_alert @enabled = 0,
@name = @alert_name,
@category_name = @category_name,
@message_id = @alert_id
IF (@@ERROR != 0 OR @retcode != 0)
BEGIN
goto UNDO
END
END
commit transaction tran_sp_MScreate_peer_tables
return 0
UNDO:
rollback transaction tran_sp_MScreate_peer_tables
commit transaction tran_sp_MScreate_peer_tables
return 1
END