-- Name: sp_MSaddautonosyncsubscription
-- Description: This procedure represents a separate code path in
-- sp_addsubscription for adding a nosync subscription using the
-- automated nosync subscription setup support.
-- sp_addsubscription will invoke this procedure when it
-- recognizes that the value of the @sync_type parameter is one
-- of 'replication support only', 'initialize with backup', or
-- 'initialize from lsn'.
-- Parameters: (Subscription setup parameters)
-- @publication sysname (mandatory)
-- @article sysname (optional, default = N'all')
-- @subscriber sysname (optional, default = NULL)
-- @destination_db sysname (optional, default = NULL)
-- @sync_type nvarchar(80) (optional, default = N'replication support only')
-- @status sysname (optional, default = NULL, must be NULL or 'active')
-- @subscription_type nvarchar(4) (optional, default = N'push')
-- @update_mode nvarchar(30) (optional, default = 'read only')
-- @loopback_detection nvarchar(5) (optional, default = NULL)
-- @frequency_type int (optional, default = NULL)
-- @frequency_interval int (optional, default = NULL)
-- @frequency_relative_interval int (optional, default = NULL)
-- @frequency_recurrence_factor int (optional, default = NULL)
-- @frequency_subday int (optional, default = NULL)
-- @frequency_subday_interval int (optional, default = NULL)
-- @active_start_time_of_day int (optional, default = NULL)
-- @active_end_time_of_day int (optional, default = NULL)
-- @active_start_date int (optional, default = NULL)
-- @active_end_date int (optional, default = NULL)
-- @optional_command_line nvarchar(4000) (optional, default = NULL)
-- @enabled_for_syncmgr nvarchar(5) (optional, default = 'false')
-- @offloadserver sysname (optional, default = NULL)
-- @dts_package_name sysname (optional, default = NULL)
-- @dts_package_password sysname (optional, default = NULL)
-- @dts_package_location nvarchar(12) (optional, default = N'distributor')
-- @distribution_job_name sysname (optional, default = NULL)
-- (Backup last LSN extraction properties)
-- @backupdevicetype nvarchar(20) (optional, default = 'logical')
-- @backupdevicename nvarchar(1000) (optional, default = null)
-- @mediapassword sysname (optional, default = null)
-- @password sysname (optional, default = null)
-- @fileidhint int (optional, default = null)
-- @unload bit (optional, default = 0)
-- (Explicitly specified LSN for this subscription)
-- @subscription_lsn binary(10) (optional, default = null)
-- @subscriber_type tinyint (optional, default = 0)
-- Notes: 1) Current restrictions on using the (partially) automated
-- nosync subscription setup support:
-- 1) Partial subscription to a publication is not allowed.
-- 2) The publication must allow initialize with backup
-- if sync_type is 'initialize with backup' or 'initialize from
-- lsn' (i.e. allow_initialize_from_backup = 1).
-- 2) The various operations performed in this procedure is not
-- fully atomic and they cannot be made as atomic. It may be
-- necessary to manually clean up the script directory
-- in certain failure scenarios (failed to commit).
-- 3) SQL Server account at the publisher must have write access
-- to the distributor's working folder.
-- 4) The LSN returned by sp_replincrementlsn is not validated against
-- the publication's min. autonosync lsn because it is not
-- necessary to do so.
-- 5) No error will be raised if backup parameters are specified
-- for non-'autonosync with backup' subscriptions and vice
-- versa.
-- Security: This is an internal system procedure.
-- Requires Certificate signature for catalog access
create procedure sys.sp_MSaddautonosyncsubscription(
-- Subscription setup parameters
@publication sysname,
@article sysname = N'all',
@subscriber sysname = NULL,
@destination_db sysname = NULL,
@sync_type nvarchar(80) = N'replication support only',
@status sysname = NULL,
@subscription_type nvarchar(4) = N'push',
@update_mode nvarchar(30) = N'read only',
@loopback_detection nvarchar(5) = NULL,
@frequency_type int = NULL,
@frequency_interval int = NULL,
@frequency_relative_interval int = NULL,
@frequency_recurrence_factor int = NULL,
@frequency_subday int = NULL,
@frequency_subday_interval int = NULL,
@active_start_time_of_day int = NULL,
@active_end_time_of_day int = NULL,
@active_start_date int = NULL,
@active_end_date int = NULL,
@optional_command_line nvarchar(4000) = NULL,
@reserved nvarchar(10) = NULL,
@enabled_for_syncmgr nvarchar(5) = 'false',
@dts_package_name sysname = NULL,
@dts_package_password sysname = NULL,
@dts_package_location nvarchar(12) = N'distributor',
@distribution_job_name sysname = NULL,
-- Backup lsn extraction parameters
@backupdevicetype nvarchar(20) = 'logical',
@backupdevicename nvarchar(1000),
@mediapassword sysname = null,
@password sysname = null,
@fileidhint int = null,
@unload bit = 1,
-- Explicitly specified lsn for this nosync subscription
@subscriptionlsn binary(10) = null,
@publisher sysname = NULL,
@publisher_type sysname = N'MSSQLSERVER'
,@subscriptionstreams tinyint = NULL
,@subscriber_type tinyint = 0
)
as
begin
DECLARE @retcode int
DECLARE @transactionopened bit
DECLARE @currentdatabase sysname
DECLARE @active tinyint
DECLARE @allow_ftp bit
DECLARE @cleanupsubscription bit
DECLARE @repl_freq tinyint
DECLARE @allow_initialize_from_backup bit
DECLARE @min_autonosync_lsn binary(10)
DECLARE @lsnsource tinyint
DECLARE @pubid int
DECLARE @ispeer bit
DECLARE @OPT_ENABLED_FOR_P2P int = 0x1
SET NOCOUNT ON
select @retcode = 0,
@transactionopened = 0,
@currentdatabase = db_name(),
@active = 2,
@cleanupsubscription = 0,
@allow_initialize_from_backup = null
-- Check if the current database is published
IF (sys.fn_MSrepl_istranpublished(db_name(),1) = 0)
BEGIN
RAISERROR (14013, 16, -1)
RETURN (1)
END
-- Validate publication name
if @publication is null or rtrim(@publication) = N''
begin
raiserror (14043, 16, -1, '@publication', 'sp_MSaddautonosyncsubscription')
return (1)
end
-- Get publication id
SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)
-- Publication existence check
SELECT @allow_initialize_from_backup = allow_initialize_from_backup,
@min_autonosync_lsn = min_autonosync_lsn,
@repl_freq = repl_freq,
@ispeer = case (options & @OPT_ENABLED_FOR_P2P) when @OPT_ENABLED_FOR_P2P then 1 else 0 end
FROM syspublications
WHERE pubid = @pubid
IF (@@ERROR != 0)
BEGIN
SELECT @retcode = 1
GOTO Failure
END
-- allow_initialize_from_backup cannot be null, if it is, then the publication
-- does not exist
if @allow_initialize_from_backup is null
begin
raiserror(20026, 11, -1, @publication)
return 1
end
-- Publication cannot be a snapshot publication
if @repl_freq <> 0
begin
raiserror(18787, 16, -1)
return 1
end
-- Initial subscription status must be active or null (default)
select @status = lower(@status collate SQL_Latin1_General_CP1_CS_AS)
if @status is not null and @status <> N'active'
begin
raiserror(21408, 16, -1)
return 1
end
-- Obtain the autonosync subscription lsn depending on the
-- @sync_type value. Note that @sync_type has already
-- been validated as one of the autonosync types in sp_addsubscription
select @sync_type = lower(@sync_type collate SQL_Latin1_General_CP1_CS_AS)
select @lsnsource = case
when @sync_type = N'initialize from lsn' then 2
when @sync_type = N'initialize with backup' then 1
-- @sync_type = N'replication support only'
else 0
end
IF (@@ERROR != 0)
BEGIN
SELECT @retcode = 1
GOTO Failure
END
-- Publication must be enabled for automated nosync support for the
-- 'initialize with backup' and 'initialize from lsn' sync types.
if @lsnsource <> 0
begin
if @allow_initialize_from_backup = 0
begin
raiserror(18786, 16, -1)
return 1
end
end
-- The entire publication must be subscribed at once unless
-- this is a "replication support only" subscription
if lower(@article collate SQL_Latin1_General_CP1_CS_AS) <> N'all'
begin
if @lsnsource <> 0
begin
raiserror(21407, 16, -1)
return 1
end
end
else
begin
select @article = N'all'
end
if @lsnsource = 2
begin
-- Just make sure that the given lsn is not null
if @subscriptionlsn is null
begin
raiserror(14043, 16, -1, '@subscriptionlsn', 'sp_MSaddautonosyncsubscription')
select @retcode = 1 goto Failure
end
end
else if @lsnsource = 1
begin
-- Extract lsn from backup
exec @retcode = sys.sp_MSextractlastlsnfrombackup
@backupdevicetype = @backupdevicetype,
@backupdevicename = @backupdevicename,
@mediapassword = @mediapassword,
@password = @password,
@fileidhint = @fileidhint,
@unload = @unload,
--avoid replacing this into publishingservername for now, it's used to find the physical server name stored in file header
@backupservername = @@servername,
@backupdatabase = @currentdatabase,
@lastlsn = @subscriptionlsn output
if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
end
-- Make sure that the subscription lsn is greater than the publication's
-- min autonosync lsn, don't need to check the one obtained from
-- sp_replincrementlsn
if @subscriptionlsn < @min_autonosync_lsn and @lsnsource <> 0
begin
if @lsnsource = 2 and @ispeer = 0
--bug 122094 (PS ID 540867): in case @lsnsource = 2 and @ispeer = 1, do not raise an error, because for p2p, it is possible that @subscriptionlsn is less than @min_autonosync_lsn
begin
raiserror(21399, 16, -1)
return 1
end
else if @lsnsource = 1
begin
raiserror(21397, 16, -1)
return 1
end
end
-- Add nosync subscription in a subscribed state
exec @retcode = sys.sp_addsubscription
@publication = @publication,
@article = @article,
@subscriber = @subscriber,
@destination_db = @destination_db,
@sync_type = N'none',
@status = N'subscribed',
@subscription_type = @subscription_type,
@update_mode = @update_mode,
@loopback_detection = @loopback_detection,
@frequency_type = @frequency_type,
@frequency_interval = @frequency_interval,
@frequency_relative_interval = @frequency_relative_interval,
@frequency_recurrence_factor = @frequency_recurrence_factor,
@frequency_subday = @frequency_subday,
@frequency_subday_interval = @frequency_subday_interval,
@active_start_time_of_day = @active_start_time_of_day,
@active_end_time_of_day = @active_end_time_of_day,
@active_start_date = @active_start_date,
@active_end_date = @active_end_date,
@optional_command_line = @optional_command_line,
@reserved = N'nosync',
@enabled_for_syncmgr = @enabled_for_syncmgr,
@dts_package_name = @dts_package_name,
@dts_package_password = @dts_package_password,
@dts_package_location = @dts_package_location,
@distribution_job_name = @distribution_job_name,
@publisher = @publisher
,@subscriptionstreams = @subscriptionstreams
,@subscriber_type = @subscriber_type
if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
select @cleanupsubscription = 1
-- If the specified @destination_db is null, try to select it
-- back from syssubscriptions
IF @destination_db IS NULL
BEGIN
IF EXISTS
(
SELECT *
FROM syssubscriptions sub
WHERE sub.srvname = upper(@subscriber)
and (sub.srvname is not null and len(sub.srvname)> 0)
AND sub.dest_db = db_name()
AND artid in
(
SELECT artid
FROM dbo.sysextendedarticlesview sa,
dbo.syspublications sp
WHERE sa.pubid = sp.pubid
AND sp.pubid = @pubid
AND (@article = N'all' or @article = sa.name)
)
)
BEGIN
SELECT @destination_db = db_name()
END
ELSE
BEGIN
SELECT @destination_db = formatmessage(20586)
END
END
begin tran
save tran sp_MSaddautonosyncsubscription
IF (@@ERROR != 0) OR (@retcode != 0)
BEGIN
SELECT @retcode = 1
GOTO Failure
END
SELECT @transactionopened = 1
-- For a sync type of 'replication support only' we perform just-in-time
-- activation of article objects
IF @lsnsource = 0
BEGIN
EXEC @retcode = sys.sp_MSenableautonosync @publication = @publication,
@activate_articles_only = 1,
@publisher = @publisher,
@publisher_type = @publisher_type
IF (@@ERROR != 0) OR (@retcode != 0)
BEGIN
SELECT @retcode = 1
GOTO Failure
END
EXEC @retcode = sys.sp_replincrementlsn @xact_seqno = @subscriptionlsn OUTPUT,
@publisher = @publisher
IF (@@ERROR != 0) OR (@retcode != 0)
BEGIN
SELECT @retcode = 1
GOTO Failure
END
END
-- Update the subscription status in dbo.syssubscriptions directly
-- to active. Activation of subscription at the distributor will
-- be handled by sp_MSsetupnosyncsubscriptionwithlsn
UPDATE syssubscriptions
SET status = @active,
queued_reinit = CASE
WHEN LOWER(@update_mode COLLATE SQL_Latin1_General_CP1_CS_AS) = N'read only' then 1
ELSE 0
END,
nosync_type = @lsnsource + 1
WHERE srvname = UPPER(@subscriber)
and (srvname is not null and len(srvname)> 0)
AND dest_db = @destination_db
AND artid IN ( SELECT artid
FROM sysextendedarticlesview sa,
syspublications sp
WHERE sa.pubid = sp.pubid
AND sp.pubid = @pubid
AND (@article = N'all' OR @article = sa.name))
IF (@@ERROR != 0) OR (@retcode != 0)
BEGIN
SELECT @retcode = 1
GOTO Failure
END
EXEC @retcode = sys.sp_MSsetupnosyncsubscriptionwithlsn @publication = @publication,
@article = @article,
@subscriber = @subscriber,
@destination_db = @destination_db,
@update_mode = @update_mode,
@subscriptionlsn = @subscriptionlsn,
@lsnsource = @lsnsource,
@publisher = @publisher,
@publisher_type = @publisher_type
IF (@@ERROR != 0) OR (@retcode != 0)
BEGIN
SELECT @retcode = 1
GOTO Failure
END
commit tran sp_MSaddautonosyncsubscription
IF (@@ERROR != 0) OR (@retcode != 0)
BEGIN
SELECT @retcode = 1
GOTO Failure
END
SELECT @transactionopened = 0,
@cleanupsubscription = 0
Failure:
IF @transactionopened = 1
BEGIN
ROLLBACK TRAN sp_MSaddautonosyncsubscription
COMMIT TRAN sp_MSaddautonosyncsubscription
END
IF @cleanupsubscription = 1
BEGIN
-- Ignore errors
EXEC sp_dropsubscription @publication = @publication,
@article = N'all',
@subscriber = @subscriber,
@destination_db = @destination_db,
@publisher = @publisher
END
RETURN @retcode
END
