April 13, 2012

sp_adddistributiondb (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_adddistributiondb(nvarchar @database
, nvarchar @data_folder
, nvarchar @data_file
, int @data_file_size
, nvarchar @log_folder
, nvarchar @log_file
, int @log_file_size
, int @min_distretention
, int @max_distretention
, int @history_retention
, int @security_mode
, nvarchar @login
, nvarchar @password
, int @createmode
, bit @from_scripting)

MetaData:

 --   
-- Name: sp_adddistributiondb
--
-- Descriptions:
--
-- Parameters: as defined in create statement
--
-- Returns: 0 - success
-- 1 - Otherwise
--
-- Security: Public (sysadmin check)
--
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_adddistributiondb
(
@database sysname,
@data_folder nvarchar(255) = NULL,
@data_file nvarchar(255) = NULL, -- physical file name
@data_file_size int = 5, -- Default: 5MB
@log_folder nvarchar(255) = NULL,
@log_file nvarchar(255) = NULL, -- physical file name
@log_file_size int = 0,
@min_distretention int = 0, -- min distribution retention period in hours
@max_distretention int = 72, -- max distribution retention period in hours
@history_retention int = 48, -- history retention period in hours
@security_mode int = 1, -- distributor login security 0 standard 1 integrated
@login sysname = NULL, -- standard login name
@password sysname = NULL, -- standard login password
@createmode int = 1, -- 0: use create db for attach (no longer used),
1: create db or use existing but no attach (recommended),
2: create for instdist and detach only --
@from_scripting bit = 0
)
AS
BEGIN
SET NOCOUNT ON
-- Declarations.
DECLARE @data_path nvarchar(512)
,@log_path nvarchar(512)
,@data_path_quoted_for_copy nvarchar(600)
,@log_path_quoted_for_copy nvarchar(600)
,@logical_data_file nvarchar(255)
,@logical_log_file nvarchar(255)
,@canneddbdata_file nvarchar(255)
,@canneddblog_file nvarchar(255)
,@filecopy_cmd nvarchar(4000)
,@file_exists bit
,@data_file_preexists int
,@log_file_preexists int
,@devnum int
,@retcode int
,@retcode2 int
,@agentname nvarchar(100)
,@command nvarchar (4000)
,@distbit int
,@install_path nvarchar(255)
,@mssql_data_path nvarchar(255)
,@on_clause nvarchar(1700)
,@logon_clause nvarchar(1700)
,@distproc nvarchar(512)
,@major_version int
,@db_exists bit
,@trunc_log_bit int
,@description nvarchar(100)
,@category_name sysname
,@createmode_attach int
,@createmode_noattach int
,@createmode_fordetach int
,@data_file_size_str nvarchar(20)
,@log_file_size_str nvarchar(20)
,@platform_nt binary
,@distributor_login sysname
,@model_data_size int
,@model_log_size int
,@containment tinyint

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

-- Check to ensure a login is provided if security mode is SQL Server authentication.
select @login = rtrim(ltrim(isnull(@login, '')))
if @security_mode = 0 and @login = ''
begin
-- '@login cannot be null or empty when @security_mode is set to 0 (SQL Server authentication).'
raiserror(21694, 16, -1, '@login', '@security_mode')
return 1
end

select @model_data_size = sum (size)*8/1024 from sys.master_files where database_id = db_id('model') and type_desc = 'ROWS'
select @model_log_size = sum (size)*8/1024 from sys.master_files where database_id = db_id('model') and type_desc = 'LOG'


select @password = case when (@password = N'') then NULL else @password end
,@data_folder = case when (@data_folder = N'') then NULL else @data_folder end
,@log_folder = case when (@log_folder = N'') then NULL else @log_folder end
,@platform_nt = 0x1
-- on error, delete the data and log files only if they didn't pre-exist.
-- by default, assume they pre-exist.
,@data_file_preexists = 1
,@log_file_preexists = 1
,@file_exists = 0
,@data_file_size_str = case when (isnull(@data_file_size, 4) < 5 ) and (@model_data_size < 5) then N'5 MB'
when (@model_data_size < isnull(@data_file_size, 5)) then cast(@data_file_size as nvarchar(10)) + N' MB'
else cast(@model_data_size + 5 as nvarchar(10)) + N' MB'
end
,@log_file_size_str = case when (isnull(@log_file_size, 0) < 1) and (@model_log_size < 1) then N'1 MB'
when (@model_log_size < isnull(@log_file_size, 1)) then cast(@log_file_size as nvarchar(10)) + N' MB'
else cast(@model_log_size + 1 as nvarchar(10)) + N' MB'
end
if @createmode not in (0, 1, 2)
begin
select @description = cast(@createmode as nvarchar)

-- '@description' is not a valid value for the '@createmode' parameter. The value must be 0, 1, or 2.
RAISERROR (21117, 16, -1, @description, '@createmode')
return 1
end

-- option 0 to use distmdl.mdf/ldf is no longer in use
if (@createmode = 0)
select @createmode = 1

select @createmode_attach = 0
,@createmode_noattach = 1
,@createmode_fordetach = 2
,@trunc_log_bit = 8
,@distbit = 16

if (@createmode <> @createmode_fordetach)
begin
-- Check if replication components are installed on this server
exec @retcode = sys.sp_MS_replication_installed
if (@retcode <> 1)
begin
return (1)
end
-- Check for invalid security modes
IF @security_mode < 0 OR @security_mode > 1
BEGIN
RAISERROR(14109, 16, -1)
RETURN (1)
END
IF ( ( @platform_nt != platform() & @platform_nt ) and @security_mode = 1)
BEGIN
RAISERROR(21038, 16, -1)
RETURN (1)
END
-- Check for invalid retention values
IF @min_distretention < 0 OR @max_distretention < 0 OR @history_retention < 0
BEGIN
RAISERROR(14106, 16, -1)
RETURN (1)
END
IF @min_distretention > @max_distretention
BEGIN
RAISERROR(14107, 16, -1)
RETURN (1)
END
-- Check to make sure this is a distributor
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers
WHERE UPPER(datasource collate database_default) = UPPER(@@SERVERNAME) collate database_default
AND srvstatus & 8 <> 0)
BEGIN
RAISERROR (14114, 16, -1, @@SERVERNAME)
RETURN(1)
END
-- Check if database is already configured as a distributor database
IF EXISTS (SELECT * FROM msdb..MSdistributiondbs WHERE name = @database collate database_default)
BEGIN
RAISERROR (14119, 16, -1, @database)
RETURN(1)
END
end

--
-- check folder information
--
if (@data_folder is null or @log_folder is null)
begin
--
-- Get path to version specific INSTALL directory
--
exec @retcode = sys.sp_MSget_setup_paths
@sql_path = @install_path output,
@data_path = @mssql_data_path output
IF @retcode <> 0 or @install_path is NULL or @install_path='' or @mssql_data_path = ''
BEGIN
GOTO UNDO
END
IF @data_folder IS NULL
select @data_folder = @mssql_data_path + N'\DATA'
IF @log_folder IS NULL
select @log_folder = @mssql_data_path + N'\DATA'
end

IF @data_file IS NULL
SELECT @data_file = sys.fn_replaceinvalidfilenamesymbols(@database) collate database_default + N'.MDF'
IF @log_file IS NULL
SELECT @log_file = sys.fn_replaceinvalidfilenamesymbols(@database) collate database_default + N'.LDF'

-- the file names can not be longer than 255 characters total
IF len(@data_file) > 255
begin
raiserror(21746, 16, -1, N'@data_file', 255)
return 1
end
IF len(@log_file) > 255
begin
raiserror(21746, 16, -1, N'@log_file', 255)
return 1
end

if substring(@data_folder, len(@data_folder), 1) = N'\'
select @data_folder = substring (@data_folder, 1, len(@data_folder) -1)
if substring(@log_folder, len(@log_folder), 1) = N'\'
select @log_folder = substring (@log_folder, 1, len(@log_folder) -1)

-- data and log folder must exist to continue
if @data_folder is not NULL
begin
exec sys.sp_MSget_file_existence @data_folder, @file_exists OUTPUT
if @file_exists = 0
begin
raiserror(14430, 16, -1, @data_folder)
return 1
end
end
if @log_folder is not NULL
begin
exec sys.sp_MSget_file_existence @log_folder, @file_exists OUTPUT
if @file_exists = 0
begin
raiserror(14430, 16, -1, @log_folder)
return 1
end
end

SELECT @data_path = @data_folder + N'\' + @data_file
,@log_path = @log_folder + N'\' + @log_file
,@data_path_quoted_for_copy = N'"' + sys.fn_escapecmdshellsymbolsremovequotes(@data_folder) collate database_default + N'\' + sys.fn_escapecmdshellsymbolsremovequotes(@data_file) collate database_default + N'"'
,@log_path_quoted_for_copy = N'"' + sys.fn_escapecmdshellsymbolsremovequotes(@log_folder) collate database_default + N'\' + sys.fn_escapecmdshellsymbolsremovequotes(@log_file) collate database_default + N'"'
,@logical_data_file = @database
-- Truncate the logical log file name back to 128 characters
-- long so the 'CREATE DATABASE' statement won't complain.
,@logical_log_file = case when (LEN(@database) > 124)
then SUBSTRING(@database, 1, 124) + N'_log'
else @database + N'_log' end

--
if (@createmode = @createmode_attach)
begin
select @canneddbdata_file = @mssql_data_path + N'\DATA\DISTMDL.MDF'
,@canneddblog_file = @mssql_data_path + N'\DATA\DISTMDL.LDF'

exec sys.sp_MSget_file_existence @canneddbdata_file, @file_exists OUTPUT
if (@file_exists = 0)
begin
-- Fallback to mode where instdist.sql needs to be run
select @createmode = @createmode_noattach
end
else
begin
exec sys.sp_MSget_file_existence @canneddblog_file, @file_exists OUTPUT
if (@file_exists = 0)
begin
-- Fallback to mode where instdist.sql needs to be run
select @createmode = @createmode_noattach
end
end
end
--
--
-- Create the distributor database if it does not exist
--
IF NOT EXISTS (SELECT * from master.dbo.sysdatabases WHERE name = @database collate database_default)
BEGIN
--
IF (@createmode = @createmode_attach)
BEGIN
-- DO THE CREATE DATABASE FOR ATTACH STUFF
exec sys.sp_MSget_file_existence @data_path, @data_file_preexists OUTPUT
if (@data_file_preexists = 1)
begin
raiserror(5170, 16, -1, @data_path)
return 1
end

exec sys.sp_MSget_file_existence @log_path, @log_file_preexists OUTPUT
if (@log_file_preexists = 1)
begin
raiserror(5170, 16, -1, @log_path)
return 1
end

select @filecopy_cmd = N'copy "' + sys.fn_escapecmdshellsymbolsremovequotes(@canneddbdata_file) collate database_default + N'" ' + @data_path_quoted_for_copy
EXEC @retcode = master.dbo.xp_cmdshell @filecopy_cmd, NO_OUTPUT
IF @retcode <> 0 OR @@ERROR <> 0
BEGIN
RAISERROR (25017, 16, -1, @filecopy_cmd)
return (1)
END

select @filecopy_cmd = N'copy "' + sys.fn_escapecmdshellsymbolsremovequotes(@canneddblog_file) collate database_default + N'" ' + @log_path_quoted_for_copy
EXEC @retcode = master.dbo.xp_cmdshell @filecopy_cmd, NO_OUTPUT
IF @retcode <> 0 OR @@ERROR <> 0
BEGIN
RAISERROR (25017, 16, -1, @filecopy_cmd)
return (1)
END
--
-- Create distributor database
--
SELECT @on_clause = N' ON (NAME = ''' + REPLACE(@logical_data_file, '''', '''''' ) + N''', FILENAME=''' + REPLACE( @data_path, '''', '''''' ) + N''')'
,@logon_clause = N' LOG ON (NAME = ''' + REPLACE(@logical_log_file, '''', '''''' ) + N''', FILENAME=''' + REPLACE( @log_path, '''', '''''' ) + N''')'
SELECT @command = N'USE master CREATE DATABASE ' + QUOTENAME(@database) +
+ @on_clause + @logon_clause + N' FOR ATTACH'

EXEC (@command)
IF @@ERROR <> 0
begin
RETURN (1)
end
dbcc dbreindexall(@database, 240) with no_infomsgs

SELECT @db_exists = 0
END
ELSE
--
-- BEGIN
-- DO THE CREATE DATABASE FOR NON ATTACH MODES
-- Note: Use system's default file growth.
IF @logical_data_file IS NOT NULL AND NOT EXISTS (SELECT * FROM master.dbo.sysaltfiles WHERE name = @logical_data_file collate database_default)
BEGIN
SELECT @on_clause = N' ON (NAME =''' + REPLACE( @logical_data_file, '''', '''''' ) + N''',FILENAME=''' + REPLACE( @data_path, '''', '''''' ) +
N''', SIZE=' + @data_file_size_str + N', MAXSIZE = UNLIMITED)'
END

IF @logical_log_file IS NOT NULL AND NOT EXISTS (SELECT * FROM master.dbo.sysaltfiles WHERE name = @logical_log_file collate database_default)
BEGIN
SELECT @logon_clause = N' LOG ON (NAME =''' + REPLACE( @logical_log_file, '''', '''''' ) + N''',FILENAME=''' + REPLACE( @log_path, '''', '''''' ) +
N''', SIZE=' + @log_file_size_str + N', MAXSIZE= UNLIMITED)'
END
-- Create distributor database
SELECT @command = N'USE master CREATE DATABASE ' + QUOTENAME(@database) +
+ isnull(@on_clause, N' ') + isnull(@logon_clause, N' ')
EXEC (@command)
IF @@ERROR <> 0
RETURN (1)

SELECT @db_exists = 0
-- END
END
ELSE
BEGIN
-- distribution db exists - do nothing
SELECT @db_exists = 1
END

--
-- Contained Database check (Replication is not yet supported on contained databases)
-- If the specified database exists and is a contained database, then we error out.
-- Note:
-- - If this check fires, then the attached database above won't be detached. I don't see any easy way of doing this while
-- returning a meaningful error code. The main purpose of this check is to not let customers accidentally try to use
-- replication with contained databases, and that purpose is served here. They can delete the newly created database
-- manually, if they want to. (Also, the attach option seems to be not supported according to BOL)
-- - This check needs to be after the database gets created above, since we might have attached or created a contained database above.
--
SELECT @containment=containment FROM sys.databases WHERE
name = @database collate database_default
if (@containment IS NOT NULL) AND (@containment != 0)
BEGIN
RAISERROR(12839, 16, -1, @database)
RETURN(1)
END

--
-- Must make the dist db owned by sa so that the sps in it can select from
-- security cache tables in tempdb by owership chain rule.
--
select @retcode2 = 0
,@distproc = QUOTENAME(@database) + N'.sys.sp_executesql'
,@command =
-- If the db is created by sa or from attach, sa is dbo already.
-- sp_changedbowner will fail if the new owner is an user in the db already.
-- Addendum: we don't perform the check for existing rows in
-- sysusers anymore as the server has implemented special logic
-- that handles such scenarios transparently.
-- ' if not exists (select * from sys.database_principals where sid = 0x01) ' +
N' declare @login sysname
select @login = suser_sname(0x01)
exec @retcode2 = sys.sp_changedbowner @loginame= @login'

EXEC @retcode = @distproc @command, N'@retcode2 int output', @retcode2 output
IF @retcode <> 0 or @retcode2 <> 0 or @@ERROR <> 0
BEGIN
GOTO UNDO
END

-- Consider: Lock database using EXEC %%CurrentDatabase().Lock()??

-- Set the database option truncate log on checkpoint & turn off autoclose which is default of win9x
IF EXISTS (SELECT * FROM master.dbo.sysdatabases WHERE
name = @database collate database_default AND
(status & @trunc_log_bit) = 0 ) -- if its not already marked
BEGIN
select @command = N'ALTER DATABASE ' + QUOTENAME(@database) + N' SET RECOVERY SIMPLE'
exec(@command)
IF @@ERROR <> 0
BEGIN
GOTO UNDO
END
END
select @command = N'ALTER DATABASE ' + QUOTENAME(@database) + N' SET AUTO_CLOSE OFF'
exec(@command)
IF @@ERROR <> 0
BEGIN
GOTO UNDO
END
--
-- Update sysdatabase category bit
-- This is to prevent user from dropping the database.
--
if (@createmode <> @createmode_fordetach)
EXEC %%DatabaseEx(Name = @database).SetDistributor(Value = 1)
--
-- Install instdist.sql
--
if (@createmode <> @createmode_attach) OR (@db_exists = 1)
begin
select @command = quotename(@database) + N'.sys.sp_instdist'
exec @retcode = @command
IF @retcode <> 0 or @@error <> 0
BEGIN
GOTO UNDO
END
end

if (@createmode <> @createmode_fordetach)
begin
-- Set db_existed bit in MSrepl_version
IF @db_exists = 1
BEGIN
SELECT @command = N'UPDATE ' +
QUOTENAME(@database) + N'.dbo.MSrepl_version SET db_existed = 0x1'

EXEC(@command)
IF @@ERROR <> 0
BEGIN
GOTO UNDO
END
END

DELETE msdb.dbo.MSdistributiondbs WHERE name = @database collate database_default
IF @@ERROR <> 0
BEGIN
GOTO UNDO
END

INSERT INTO msdb.dbo.MSdistributiondbs VALUES (
@database, @min_distretention, @max_distretention, @history_retention
)
IF @@ERROR <> 0
BEGIN
GOTO UNDO
END

-- This login need db_owner priviledge to call sps in distribution db
select @distributor_login = N'distributor_admin'
,@command = quotename(@database) + N'.sys.sp_MSrepl_dbrole'
exec @retcode = @command N'db_owner', @distributor_login, N'add'
IF @@error <> 0 OR @retcode <> 0
GOTO UNDO

-- The following commented out code to drop guest user was fine for merge,
-- but the tran BVT failed. It can be uncommented once tran fixes the issue
-- with no guest user in dist db.
-- The guest user needs to be dropped from the distribution db.
-- select @command = '
-- if exists (select * from ' + quotename(@database) + '.dbo.sysusers
-- where name = ''guest'' and hasdbaccess = 1)
-- begin
-- exec ' + quotename(@database) + '.sys.sp_revokedbaccess @name_in_db = ''guest''
-- end'
-- exec (@command)
-- if (@@error != 0)
-- goto UNDO

if @from_scripting = 0
begin
--
-- Create the history cleanup agent.
--
SELECT @agentname = formatmessage (20567, @database)
,@command = N'EXEC dbo.sp_MShistory_cleanup @history_retention = ' +
CONVERT(nvarchar(12), @history_retention)

IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE name = @agentname collate database_default and
UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
and master_server = 0)
BEGIN
EXEC @retcode = sys.sp_MSdrop_repl_job
@job_name = @agentname
IF @@ERROR <> 0 or @retcode <> 0
BEGIN
GOTO UNDO
END
END

set @description = formatmessage(20535)

-- Get History Cleanup category name (assumes category_id = 12)
select @category_name = name FROM msdb.dbo.syscategories where category_id = 12

EXECUTE @retcode = sys.sp_MSadd_repl_job @agentname,
@subsystem = 'TSQL',
@server = @@SERVERNAME,
@databasename = @database,
@description = @description,
@freqtype = 4,
@freqsubtype = 4,
@freqsubinterval = 10, -- Number of minutes between runs
@command = @command,
@enabled = 1,
@retryattempts = 0,
@loghistcompletionlevel = 0,
@category_name = @category_name

IF @@ERROR <> 0 or @retcode <> 0
BEGIN
GOTO UNDO
END
--
-- Create the distribution cleanup agent.
--
SELECT @agentname = formatmessage (20568, @database)
,@command = N'EXEC dbo.sp_MSdistribution_cleanup @min_distretention = ' +
CONVERT(nvarchar(12), @min_distretention) + ', @max_distretention = ' +
CONVERT(nvarchar(12), @max_distretention)

IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE name = @agentname collate database_default and
UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
and master_server = 0)
BEGIN
EXEC @retcode = sys.sp_MSdrop_repl_job
@job_name = @agentname
IF @@ERROR <> 0 or @retcode <> 0
BEGIN
GOTO UNDO
END
END

set @description = formatmessage(20541)
-- Get Distribution Cleanup category name (assumes category_id = 11)
select @category_name = name FROM msdb.dbo.syscategories where category_id = 11

EXECUTE @retcode = msdb.sys.sp_MSadd_repl_job @agentname,
@subsystem = 'TSQL',
@server = @@SERVERNAME,
@databasename = @database,
@description = @description,
@freqtype = 4,
@freqsubtype = 4,
@freqsubinterval = 10, -- Number of minutes between runs
@command = @command,
@retryattempts = 0,
@enabled = 0,
@loghistcompletionlevel = 0,
@category_name = @category_name,
-- Start and end time is 5 min off from the history cleanup, which use the default.
@activestarttimeofday = 000500,
@activeendtimeofday = 000459

IF @@ERROR <> 0 or @retcode <> 0
BEGIN
GOTO UNDO
end
--
-- Add job for replication monitoring refresh
--
select @agentname = formatmessage (20811, @database)
select @description = @agentname
,@command = N'exec dbo.sp_replmonitorrefreshjob '
if exists (select * from msdb.dbo.sysjobs_view where name = @agentname collate database_default and
upper(originating_server collate database_default) = upper(convert(sysname, SERVERPROPERTY('ServerName')))
and master_server = 0)
BEGIN
EXEC @retcode = sys.sp_MSdrop_repl_job
@job_name = @agentname
IF @@ERROR <> 0 or @retcode <> 0
BEGIN
GOTO UNDO
END
END
select @category_name = name FROM msdb.dbo.syscategories where category_id = 18

EXECUTE @retcode = msdb.sys.sp_MSadd_repl_job @agentname,
@subsystem = 'TSQL',
@server = @@SERVERNAME,
@databasename = @database,
@description = @description,
@freqtype = 64, -- continuous
@freqsubtype = 0, -- unused
@freqsubinterval = 0, -- unused
@command = @command,
@retryattempts = 0,
@enabled = 0,
@loghistcompletionlevel = 0,
@category_name = @category_name

if @@ERROR <> 0 or @retcode <> 0
GOTO UNDO
end
--
-- register default resolvers - this requires SQL installation
--
exec @retcode = sys.sp_MSrepl_register_default_resolvers @database

if @@error <> 0 or @retcode <> 0
goto UNDO
end -- (@createmode <> @createmode_fordetach) block
else
begin
-- detach
dbcc detachdb(@database)
end

RETURN(0)

-- Consider: Using transaction ??
UNDO:

IF @db_exists = 0
EXECUTE sys.sp_dropdistributiondb @database

-- Need to do it since sp_dropdistributiondb will fail in some cases
EXEC %%DatabaseEx(Name = @database).SetDistributor(Value = 0)

DELETE msdb.dbo.MSdistributiondbs where name = @database collate database_default

-- drop the database and ignore error
IF @db_exists = 0 AND
EXISTS (SELECT * from master.dbo.sysdatabases WHERE name = @database collate database_default)
BEGIN
SELECT @command = N'USE master DROP DATABASE ' + QUOTENAME(@database)
EXEC (@command)
END

--
if (@createmode = @createmode_attach)
begin
if (@data_file_preexists = 0)
begin
select @command = N'del ' + @data_path_quoted_for_copy
exec master.dbo.xp_cmdshell @command, NO_OUTPUT
-- ignore errors
end
if (@log_file_preexists = 0)
begin
select @command = N'del ' + @log_path_quoted_for_copy
exec master.dbo.xp_cmdshell @command, NO_OUTPUT
-- ignore errors
end
end
--
RETURN(1)
END

No comments:

Post a Comment

Total Pageviews