April 13, 2012

sp_adddistributor (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_adddistributor(nvarchar @distributor
, int @heartbeat_interval
, nvarchar @password
, bit @from_scripting)

MetaData:

 create procedure sys.sp_adddistributor (  
@distributor sysname, -- distributor server name --
@heartbeat_interval int = 10, -- minutes
@password sysname = NULL,
@from_scripting bit = 0
)
AS
BEGIN

SET NOCOUNT ON

--
-- Declarations.
--
DECLARE @retcode int
,@agentname nvarchar(100)
,@command nvarchar(255)
,@distribution_db sysname
,@distproc nvarchar(255)
,@dist_rpcname sysname
,@dist_datasource sysname
,@server_added bit
,@login sysname

--
-- Security Check: require sysadmin
--
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,16,-1)
RETURN (1)
END

select @server_added = 0
select @login = 'distributor_admin'

-- Verify that this SKU is allowed to be a distributor
exec @retcode= sys.sp_MSsku_allows_replication
if @@error<>0 return 1
if @retcode <> 0
begin
raiserror(21105, 16, -1)
return (1)
end

--
-- Check if replication components are installed on this server
--
exec @retcode = sys.sp_MS_replication_installed
if (@retcode <> 1)
begin
return (1)
end

-- Must be at master db.
IF db_name() <> 'master'
BEGIN
RAISERROR(5001, 16,-1)
return (1)
END
--
-- Parameter Check: @distributor.
-- Check to make sure that the distributor is not NULL and that it
-- conforms to the rules for identifiers.
--
IF @distributor IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@distributor', 'sp_adddistributor')
RETURN (1)
END

EXECUTE @retcode = sys.sp_validname @distributor

IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)

-- Verify password
IF (@password IS NULL) OR (@password = N'')
BEGIN
IF (UPPER(CONVERT(sysname, ServerProperty('ServerName'))) = UPPER(@distributor))
BEGIN
-- LOCAL DISTRIBUTOR: Seed password with random value
SELECT @password = convert(sysname, newid())
END
ELSE
BEGIN
-- REMOTE DISTRIBUTOR: Require non-null password for security
RAISERROR(21768, 16, -1)
RETURN (1)
END
END

if isnumeric(@heartbeat_interval) = 0 or convert(int, isnull(@heartbeat_interval, 0)) < 1
begin
raiserror (21115, 16, -1, @heartbeat_interval, '@heartbeat_interval')
return 1
end

--
-- Check to make sure that the distributor doesn't already exist.
--
SELECT @dist_rpcname = NULL
SELECT @dist_rpcname = srvname
,@dist_datasource = datasource
FROM master.dbo.sysservers
WHERE srvstatus & 8 <> 0
IF @dist_rpcname IS NOT NULL
BEGIN
RAISERROR (14099, 16, -1, @dist_datasource)
RETURN(1)
END

-- drop repl_distributor if it exists.
if exists (select * from master.dbo.sysservers where lower(srvname collate SQL_Latin1_General_CP1_CS_AS) =
N'repl_distributor' collate SQL_Latin1_General_CP1_CS_AS)
begin
exec @retcode = sys.sp_dropserver 'repl_distributor', 'droplogins'
IF @@error <> 0 OR @retcode <> 0
BEGIN
RETURN(1)
END
end

-- Add the linked server entry for the distributor
-- Note we do this even for local server for consistancy
EXECUTE @retcode = sys.sp_addserver 'repl_distributor'
IF @@error <> 0 OR @retcode <> 0
BEGIN
RETURN (1)
END

select @server_added = 1

-- Mark system link
EXECUTE @retcode = sys.sp_serveroption 'repl_distributor', 'system','true'
IF @@error <> 0 OR @retcode <> 0
BEGIN
goto UNDO
END

-- We will always use UPPERCASE name
select @distributor = upper(@distributor)
EXECUTE @retcode = sys.sp_setnetname 'repl_distributor', @distributor
IF @@error <> 0 OR @retcode <> 0
BEGIN
goto UNDO
END

exec @retcode = sys.sp_addlinkedsrvlogin
@rmtsrvname= 'repl_distributor',
@useself = 'false',
@locallogin = NULL,
@rmtuser = @login,
@rmtpassword = @password
IF @@error <> 0 OR @retcode <> 0
BEGIN
goto UNDO
END

--
-- If this is not the local server, remote distributor must be set up first
--
IF UPPER(@distributor) <> UPPER(@@SERVERNAME)
BEGIN
-- make sure distributor version is >= publisher version
declare @dist_ver bigint
select @dist_ver = 0
EXEC @retcode = repl_distributor.master.sys.sp_executesql N'select @dist_ver = @@microsoftversion',
N'@dist_ver bigint output', @dist_ver output
IF (@retcode <> 0 or @@ERROR <> 0)
begin
GOTO UNDO
end
-- compare major versions
if (@dist_ver & 0xFF000000 < @@microsoftversion & 0xFF000000 )
begin
RAISERROR (21320,16,-1)
GOTO UNDO
end
--
-- Test to see if the local server is defined as publisher/subscriber
-- at the remove distributor.
-- Note: cannot call sp_helpdistributor locally since the server is not
-- marked for distribution.
-- We can not move the serveroption call before this RPC because RPC failure
-- may cause the SP to terminate. Thus, we can not UNDO the server option.
--
SELECT @distproc = 'repl_distributor.master.sys.sp_helpdistributor'

DECLARE @loc_directory nvarchar(255)
DECLARE @loc_account nvarchar(255)
DECLARE @loc_mindistretention int
DECLARE @loc_maxdistretention int
DECLARE @loc_historyretention int
DECLARE @loc_historycleanupagent nvarchar(100)
DECLARE @loc_distribcleanupagent nvarchar(100)
DECLARE @alert_name nvarchar(100)
DECLARE @alert_id int

--
-- from publisher
--
EXECUTE @retcode = @distproc
@distributor = @distributor OUTPUT,
@distribdb = @distribution_db OUTPUT,
@directory = @loc_directory OUTPUT,
@account = @loc_account OUTPUT,
@min_distretention = @loc_mindistretention OUTPUT,
@max_distretention = @loc_maxdistretention OUTPUT,
@history_retention = @loc_historyretention OUTPUT,
@history_cleanupagent = @loc_historycleanupagent OUTPUT,
@distrib_cleanupagent = @loc_distribcleanupagent OUTPUT,
@publisher = @@SERVERNAME,
@local = 'local'
IF @@error <> 0 OR @retcode <> 0 OR @distribution_db is NULL
BEGIN
RAISERROR (21007,16,1)
GOTO UNDO
END

-- cleanup potential leftover here rather than in sys.sp_changedistpublisher as it used to be
-- because sp_changedistpublisher is public proc which can be called by user in ad-hoc fashion
if @distribution_db is not null
begin
SELECT @distproc = 'repl_distributor.' + quotename(@distribution_db) + '.sys.sp_MSdistpublisher_cleanup'
EXECUTE @retcode = @distproc @@SERVERNAME
IF @@error <> 0 OR @retcode <> 0
BEGIN
GOTO UNDO
END
end

-- Activate the dist publisher at the remote distributor --
SELECT @distproc = 'repl_distributor.master.sys.sp_changedistpublisher'
EXECUTE @retcode = @distproc @@SERVERNAME, 'active','true'
IF @@error <> 0 OR @retcode <> 0
BEGIN
GOTO UNDO
END
END
ELSE
-- set the registry --
BEGIN
EXEC @retcode = sys.sp_MScreate_distributor_tables
if @@error <> 0 or @retcode <> 0
goto UNDO

declare @distributor_login sysname
select @distributor_login = 'distributor_admin'

-- Add publisher/subscriber rpc login
if not exists (select * from master.dbo.syslogins where loginname = @distributor_login collate database_default)
begin
EXEC @retcode = sys.sp_addlogin @loginame = @distributor_login,
@passwd = @password
if @@error <> 0 or @retcode <> 0
goto UNDO
end
else
begin
-- Change the password if the distributor is local
EXEC @retcode = sys.sp_password NULL, @password, 'distributor_admin'
if @@error <> 0 or @retcode <> 0
goto UNDO
end

-- Add the login to sysadmin
-- Refer to sp_MSpublication_access in distribution db
if is_srvrolemember('sysadmin', @distributor_login) <> 1
begin
exec @retcode = sys.sp_addsrvrolemember @distributor_login, 'sysadmin'
IF @@error <> 0 OR @retcode <> 0
GOTO UNDO
end

if @from_scripting <> 1
begin
-- Add Replication Agent Checkup Agent
exec @retcode = sys.sp_MScreate_replication_checkup_agent @heartbeat_interval = @heartbeat_interval
if @@error <> 0 or @retcode <> 0
goto UNDO
end

delete msdb..MSdistributor where property = 'heartbeat_interval'
if @@error <> 0
goto UNDO
insert into msdb..MSdistributor (property, value) values ('heartbeat_interval',
convert(nvarchar(10), @heartbeat_interval))
if @@error <> 0
goto UNDO

-- Add Replication Alerts and Response Jobs
exec @retcode = sys.sp_MSadd_distributor_alerts_and_responses
@from_scripting = @from_scripting
if @@error <> 0 or @retcode <> 0
goto UNDO
END

--
-- Set the server option to indicate that there is a distributor.
--
EXECUTE @retcode = sys.sp_serveroption 'repl_distributor', 'dist', true
IF @@error <> 0 OR @retcode <> 0
BEGIN
GOTO UNDO
END

-- Set sp_MSrepl_startup to be a startup stored procedure
-- Note: This needs to be after the marking the distribution server
exec @retcode = sys.sp_procoption 'sp_MSrepl_startup', 'startup', 'true'
if @@error <> 0 or @retcode <> 0
goto UNDO1

exec @retcode = sys.sp_MSrepl_startup_internal
if @@error <> 0 or @retcode <> 0
goto UNDO1

EXEC @retcode = sys.sp_MSrepl_add_expired_sub_cleanup_job
IF @@ERROR <> 0 or @retcode <> 0
BEGIN
GOTO UNDO1
END

RETURN (0)

UNDO1:
exec sys.sp_serveroption 'repl_distributor', 'dist', false
EXEC sys.sp_MSrepl_drop_expired_sub_cleanup_job

UNDO:
IF @server_added = 1
begin
-- Drop the remote logins, otherwise, sp_dropserver will fail.
EXECUTE sys.sp_dropserver 'repl_distributor', 'droplogins'
end

RETURN (1)
END

No comments:

Post a Comment

Total Pageviews