create procedure sys.sp_vupgrade_replmsdb
as
begin
set nocount on
declare @retcode int
,@profile_id int
,@table_name sysname
,@profile_name nvarchar(100)
,@profile_desc nvarchar(100)
,@snapshot_type int
,@logreader_type int
,@merge_type int
,@qreader_type int
select @snapshot_type = 1
,@logreader_type = 2
,@merge_type = 4
,@qreader_type = 9
-- these tables must exist to continue
IF OBJECT_ID('msdb..MSagent_profiles', 'U') IS NULL
OR OBJECT_ID('msdb..MSagent_parameters', 'U') IS NULL
BEGIN
RETURN 0
END
--MSagentparameterlist is new in SQL2005
--if it does not exist, we are upgrading from pre-2005
IF OBJECT_ID('msdb..MSagentparameterlist', 'U') IS NULL
BEGIN
EXEC @retcode = sys.sp_createagentparameterlist
IF @@ERROR != 0 OR @retcode != 0
RETURN 1
END
else -- some parameters were added after 2000 but before 2005 RTM, check and add them in case we came from 2005 beta
BEGIN
if not exists(SELECT *
FROM msdb..MSagentparameterlist WHERE agent_type=2
-- make sure the parameter_name column is case-insensitive
AND parameter_name collate SQL_Latin1_General_CP1_CI_AS='LogScanThreshold')
BEGIN
exec @retcode = sys.sp_createagentparameter 2, N'LogScanThreshold', 500000, 1, NULL
if (@retcode = 1 or @@ERROR <> 0)
RETURN 1
END
if not exists (SELECT * FROM msdb..MSagentparameterlist WHERE agent_type=3
-- make sure the parameter_name column is case-insensitive
AND parameter_name collate SQL_Latin1_General_CP1_CI_AS='UseOledbStreaming')
BEGIN
exec @retcode = sys.sp_createagentparameter 3, N'UseOledbStreaming', NULL, NULL, NULL
if (@retcode = 1 or @@ERROR <> 0)
RETURN 1
END
if not exists (SELECT * FROM msdb..MSagentparameterlist WHERE agent_type=3
-- make sure the parameter_name column is case-insensitive
AND parameter_name collate SQL_Latin1_General_CP1_CI_AS='OledbStreamThreshold')
BEGIN
exec @retcode = sys.sp_createagentparameter 3, N'OledbStreamThreshold', 32768, 400, 1048576
if (@retcode = 1 or @@ERROR <> 0)
RETURN 1
END
if not exists(SELECT *
FROM msdb..MSagentparameterlist WHERE agent_type=4
-- make sure the parameter_name column is case-insensitive
AND parameter_name collate SQL_Latin1_General_CP1_CI_AS='MakeGenerationInterval')
BEGIN
exec @retcode = sys.sp_createagentparameter 4, N'MakeGenerationInterval', 1, 0, NULL
if (@retcode = 1 or @@ERROR <> 0)
RETURN 1
END
--PublisherFailoverPartner was the last parameter added before 2005 RTM, if they exists, we can assume this
--is upgrading from RTM to post 2005, no need to do anything in msdb in this case since nothing new has happend since RTM
IF not exists (SELECT * FROM msdb..MSagentparameterlist WHERE agent_type in
(@snapshot_type, @logreader_type, @merge_type, @qreader_type)
-- make sure the parameter_name column is case-insensitive
AND parameter_name collate SQL_Latin1_General_CP1_CI_AS='PublisherFailoverPartner')
BEGIN
exec @retcode = sys.sp_createagentparameter @snapshot_type, N'PublisherFailoverPartner', null, null, null
if (@retcode = 1 or @@ERROR <> 0)
RETURN 1
exec @retcode = sys.sp_createagentparameter @logreader_type, N'PublisherFailoverPartner', null, null, null
if (@retcode = 1 or @@ERROR <> 0)
RETURN 1
exec @retcode = sys.sp_createagentparameter @merge_type, N'PublisherFailoverPartner', null, null, null
if (@retcode = 1 or @@ERROR <> 0)
RETURN 1
exec @retcode = sys.sp_createagentparameter @qreader_type, N'PublisherFailoverPartner', null, null, null
if (@retcode = 1 or @@ERROR <> 0)
RETURN 1
END
else -- if MSagentparameterlist already contains PublisherFailoverPartner entry, we must have come from RTM, in which case, no need to do anything
return (0)
END
-- refresh Monitoring metrics
exec @retcode = sys.sp_replmonitorinitializemetrics
if (@retcode = 1 or @@ERROR <> 0)
RETURN 1
-- raiserror('sp_vupgrade_replmsdb', 0,1)
-- Drop and regenerate agent parameters and associated values from system profiles.
-- in 70 RTM there were 10 profiles. Hence since we should have created the first 10
-- it should be fine to drop and recreate them.
select @profile_id = 1
while (@profile_id <= 10)
begin
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
exec @retcode = sys.sp_generate_agent_parameter @profile_id
if (@retcode = 1)
return (1)
select @profile_id = @profile_id + 1
end
declare hUserProfile CURSOR LOCAL FAST_FORWARD FOR
select distinct profile_id from msdb..MSagent_profiles where profile_id > 10 for read only
open hUserProfile
fetch hUserProfile into @profile_id
while @@fetch_status <> -1
begin
exec @retcode = sys.sp_drop_agent_parameter @profile_id, @parameter_name = '-ReadBatchThreshold'
if (@retcode = 1)
begin
close hUserProfile
deallocate hUserProfile
return (1)
end
fetch hUserProfile into @profile_id
end
close hUserProfile
deallocate hUserProfile
-- Add 8.0 agent profiles
/*
** Distribution agent : Synchronization Manager Profile
*/
select @profile_id = NULL
,@profile_name = N'Windows Synchronization Manager profile' -- Dist SyncMgr Profile
,@profile_desc = formatmessage(20551)
select @profile_id = profile_id from msdb..MSagent_profiles
where agent_type = 3 and profile_name = @profile_name
if (@profile_id is null)
begin
-- Add profile
select @profile_id = NULL
exec @retcode = sys.sp_add_agent_profile
@profile_id = @profile_id OUT,
@profile_name = @profile_name,
@agent_type = 3, -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
@profile_type = 0, -- 0-System, 1-Custom
@description = @profile_desc,
@default = 0
if (@retcode = 1 or @@ERROR <> 0)
return (1)
end
else
begin
-- Profile exists - drop the parameters for regeneration
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
end
exec @retcode = sys.sp_generate_agent_parameter
@profile_id = 10,
@real_profile_id = @profile_id
if (@retcode = 1 or @@ERROR <> 0)
return (1)
-- Queue default profile
select @profile_id = NULL
select @profile_id = profile_id from msdb..MSagent_profiles
where agent_type = 9 and def_profile = 1
if (@profile_id is null)
begin
-- Add profile
select @profile_id = NULL
,@profile_name = formatmessage(20545) -- Default QueueReader Profile
,@profile_desc = formatmessage(20589)
exec @retcode = sys.sp_add_agent_profile
@profile_id = @profile_id OUT,
@profile_name = @profile_name,
@agent_type = 9,
@profile_type = 0,
@description = @profile_desc,
@default = 1
if (@retcode = 1 or @@ERROR <> 0)
return (1)
end
else
begin
-- Profile exists - drop the parameters for regeneration
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
end
exec @retcode = sys.sp_generate_agent_parameter
@profile_id = 11,
@real_profile_id = @profile_id
if (@retcode = 1 or @@ERROR <> 0)
return (1)
-- Add 'skip data consistency error' profile
select @profile_id = NULL
,@profile_name = formatmessage(20599) -- Default Distribution Profile
,@profile_desc = formatmessage(20600)
select @profile_id = profile_id from msdb..MSagent_profiles
where agent_type = 3 and profile_name = @profile_name
if (@profile_id is null)
begin
-- Add profile
select @profile_id = NULL
exec @retcode = sys.sp_add_agent_profile
@profile_id = @profile_id OUT,
@profile_name = @profile_name,
@agent_type = 3, -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
@profile_type = 0, -- 0-System, 1-Custom
@description = @profile_desc,
@default = 0
if (@retcode = 1 or @@ERROR <> 0)
return (1)
end
else
begin
-- Profile exists - drop the parameters for regeneration
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
end
exec @retcode = sys.sp_generate_agent_parameter
@profile_id = 14,
@real_profile_id = @profile_id
if (@retcode = 1 or @@ERROR <> 0)
return (1)
/*
** Merge agent : Non default profile for disconnected scenarios ( unreliable link )
*/
set @profile_id = NULL
set @profile_name = formatmessage(20548) -- Non-Default Merge Profile
set @profile_desc = formatmessage(20549)
select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
if (@profile_id is null)
begin
exec @retcode = sys.sp_add_agent_profile
@profile_id = @profile_id OUT,
@profile_name = @profile_name,
@agent_type = 4, -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
@profile_type = 0, -- 0-System, 1-Custom
@description = @profile_desc,
@default = 0
end
else
begin
-- Profile exists - drop the parameters for regeneration
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
end
exec @retcode = sys.sp_generate_agent_parameter 7, @profile_id
if (@retcode = 1 or @@ERROR <> 0)
return (1)
/*
** Merge agent : Non default profile for verbose histroy
*/
set @profile_id = NULL
set @profile_name = formatmessage(20546) -- Verbose Merge Profile
set @profile_desc = formatmessage(20547)
select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
if (@profile_id is null)
begin
exec @retcode = sys.sp_add_agent_profile
@profile_id = @profile_id OUT,
@profile_name = @profile_name,
@agent_type = 4, -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
@profile_type = 0, -- 0-System, 1-Custom
@description = @profile_desc,
@default = 0
end
else
begin
-- Profile exists - drop the parameters for regeneration
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
end
exec @retcode = sys.sp_generate_agent_parameter 8, @profile_id
if (@retcode = 1 or @@ERROR <> 0)
return (1)
/*
** Merge agent : Synchronization Manager Profile
*/
set @profile_id = NULL
set @profile_name = N'Windows Synchronization Manager profile' -- SyncMgr Profile
set @profile_desc = formatmessage(20551)
select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
if (@profile_id is null)
begin
exec @retcode = sys.sp_add_agent_profile
@profile_id = @profile_id OUT,
@profile_name = @profile_name,
@agent_type = 4, -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
@profile_type = 0, -- 0-System, 1-Custom
@description = @profile_desc,
@default = 0
end
else
begin
-- Profile exists - drop the parameters for regeneration
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
end
exec @retcode = sys.sp_generate_agent_parameter 9, @profile_id
if (@retcode = 1 or @@ERROR <> 0)
return (1)
/*
** Merge agent : Rowcount Validation profile
*/
set @profile_id = NULL
set @profile_name = formatmessage(21308) -- Rowcount Validation Profile
set @profile_desc = formatmessage(21309) -- Rowcount Validation Profile Description
select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
if (@profile_id is null)
begin
exec @retcode = sys.sp_add_agent_profile
@profile_id = @profile_id OUT,
@profile_name = @profile_name,
@agent_type = 4, -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
@profile_type = 0, -- 0-System, 1-Custom
@description = @profile_desc,
@default = 0
end
else
begin
-- Profile exists - drop the parameters for regeneration
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
end
exec @retcode = sys.sp_generate_agent_parameter 12, @profile_id
if (@retcode = 1 or @@ERROR <> 0)
return (1)
/*
** Merge agent : Rowcount & Checksum Validation profile
*/
set @profile_id = NULL
set @profile_name = formatmessage(21310) -- Rowcount & Checksum Validation Profile
set @profile_desc = formatmessage(21311) -- Rowcount & Checksum Validation Profile Description
select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
if (@profile_id is null)
begin
exec @retcode = sys.sp_add_agent_profile
@profile_id = @profile_id OUT,
@profile_name = @profile_name,
@agent_type = 4, -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
@profile_type = 0, -- 0-System, 1-Custom
@description = @profile_desc,
@default = 0
end
else
begin
-- Profile exists - drop the parameters for regeneration
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
end
exec @retcode = sys.sp_generate_agent_parameter 13, @profile_id
if (@retcode = 1 or @@ERROR <> 0)
return (1)
/*
** Merge agent : High volume server-to-server profile
*/
set @profile_id = NULL
set @profile_name = formatmessage(20616) -- High volume server-to-server profile
set @profile_desc = formatmessage(20617) -- High volume server-to-server profile Description
select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 4 and profile_name = @profile_name
if (@profile_id is null)
begin
exec @retcode = sys.sp_add_agent_profile
@profile_id = @profile_id OUT,
@profile_name = @profile_name,
@agent_type = 4, -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
@profile_type = 0, -- 0-System, 1-Custom
@description = @profile_desc,
@default = 0
end
else
begin
-- Profile exists - drop the parameters for regeneration
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
end
exec @retcode = sys.sp_generate_agent_parameter 15, @profile_id
if (@retcode = 1 or @@ERROR <> 0)
return (1)
-- Distribution agent : OLEDB streaming usage Profile
select @profile_id = NULL
,@profile_name = isnull(formatmessage(20814),N'Message 20814')
,@profile_desc = isnull(formatmessage(20815),N'Message 20815')
-- if profile exists - drop the parameters for regeneration
-- else create the profile
select @profile_id = profile_id from msdb..MSagent_profiles where agent_type = 3 and profile_name = @profile_name
if (@profile_id is null)
begin
exec @retcode = sys.sp_add_agent_profile
@profile_id = @profile_id OUT,
@profile_name = @profile_name,
@agent_type = 3, -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
@profile_type = 0, -- 0-System, 1-Custom
@description = @profile_desc,
@default = 0
end
else
begin
exec @retcode = sys.sp_drop_agent_parameter @profile_id
if (@retcode = 1)
return (1)
end
-- regenerate the parameter for this profile
exec @retcode = sys.sp_generate_agent_parameter 16, @profile_id
if (@retcode = 1 or @@ERROR <> 0)
return (1)
-- Mark all replication tables in msdb as system objects as part of
-- upgrade
if exists ( select * from msdb.sys.objects where name = 'MSdistpublishers'
and type = 'U')
begin
exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.MSdistpublishers'
if (@retcode <> 0) or (@@error <> 0)
return (1)
end
if exists ( select * from msdb.sys.objects where name = 'MSdistributiondbs'
and type = 'U')
begin
exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.MSdistributiondbs'
if (@retcode <> 0) or (@@error <> 0)
return (1)
end
if exists ( select * from msdb.sys.objects where name = 'MSdistributor'
and type = 'U')
begin
exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.MSdistributor'
if (@retcode <> 0) or (@@error <> 0)
return (1)
end
if exists ( select * from msdb.sys.objects where name = 'sysreplicationalerts'
and type = 'U')
begin
exec @retcode = msdb.dbo.sp_MS_marksystemobject 'sysreplicationalerts'
if (@retcode <> 0) or (@@error <> 0)
return (1)
end
if exists ( select * from msdb.sys.objects where name = 'MSagent_profiles'
and type = 'U')
begin
exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.MSagent_profiles'
if (@retcode <> 0) or (@@error <> 0)
return (1)
end
if exists ( select * from msdb.sys.objects where name = 'MSagent_parameters'
and type = 'U')
begin
exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.MSagent_parameters'
if (@retcode <> 0) or (@@error <> 0)
return (1)
end
-- Upgrade data type mappings
-- Create new datatype mapping schema if necessary
EXEC msdb.sys.sp_MSrepl_createdatatypemappings
/*
* New indexes added starting with 7.0 sp1
*/
-- MSdistpublishers
select @table_name = N'MSdistpublishers'
IF EXISTS ( SELECT * FROM msdb.sys.objects WHERE name = 'MSdistpublishers' )
BEGIN
-- All the entries should have name in uppercase
update msdb.dbo.MSdistpublishers
set name = upper(name)
-- create indices
IF EXISTS ( SELECT name
FROM msdb.dbo.MSdistpublishers
GROUP BY name
HAVING COUNT(*) > 1 )
RAISERROR (21203, 10, 19, @table_name)
ELSE
BEGIN
IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysindexes WHERE name = 'uc1MSdistpublishers' AND
id = OBJECT_ID('msdb.dbo.MSdistpublishers') )
CREATE UNIQUE CLUSTERED INDEX uc1MSdistpublishers ON msdb.dbo.MSdistpublishers(name)
END
IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysindexes WHERE name = 'nc2MSdistpublishers' AND
id = OBJECT_ID('msdb.dbo.MSdistpublishers') )
CREATE NONCLUSTERED INDEX nc2MSdistpublishers ON msdb.dbo.MSdistpublishers(distribution_db)
END
-- MSdistributiondbs
SELECT @table_name = N'MSdistributiondbs'
IF EXISTS ( SELECT * FROM msdb.sys.objects WHERE name = 'MSdistributiondbs' )
BEGIN
IF EXISTS ( SELECT name
FROM msdb.dbo.MSdistributiondbs
GROUP BY name
HAVING COUNT(*) > 1 )
RAISERROR (21203, 10, 20, @table_name)
ELSE
IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysindexes WHERE name = 'uc1MSdistributiondbs' AND
id = OBJECT_ID('msdb.dbo.MSdistributiondbs') )
CREATE UNIQUE CLUSTERED INDEX uc1MSdistributiondbs ON msdb.dbo.MSdistributiondbs(name)
END
-- MSdistributor
SELECT @table_name = N'MSdistributor'
IF EXISTS ( SELECT * FROM msdb.sys.objects WHERE name = 'MSdistributor' )
BEGIN
IF EXISTS ( SELECT property
FROM msdb.dbo.MSdistributor
GROUP BY property
HAVING COUNT(*) > 1 )
RAISERROR (21203, 10, 21, @table_name)
ELSE
IF NOT EXISTS ( SELECT * FROM msdb.dbo.sysindexes WHERE name = 'uc1MSdistributor' AND
id = OBJECT_ID('msdb.dbo.MSdistributor') )
CREATE UNIQUE CLUSTERED INDEX uc1MSdistributor ON msdb.dbo.MSdistributor(property)
END
/*
* Upgrade replication passwords in msdb database.
*/
begin transaction
/*
* MSdistpublishers
*/
if exists (select name from msdb.sys.objects where name='MSdistpublishers')
begin
if not exists (select * from msdb.sys.columns where object_id = object_id('msdb.dbo.MSdistpublishers') and name = 'password' and max_length = 1048)
begin
/*
* alter password column from sysname to nvarchar(524)
*/
alter table msdb.dbo.MSdistpublishers alter column password nvarchar(524)
end
/* Add publisher_type column */
if not exists (select * from msdb.sys.columns where object_id = object_id('msdb.dbo.MSdistpublishers') and name = 'publisher_type')
begin
alter table msdb.dbo.MSdistpublishers
add publisher_type sysname null default N'MSSQLSERVER'
/* Update any existing dist publishers */
exec('update msdb.dbo.MSdistpublishers
set publisher_type = N''MSSQLSERVER''
where publisher_type is null')
end
/*
* convert all the passwords to new encryption
*/
exec('declare @password nvarchar(524)
declare cur_MSdistpublishers CURSOR LOCAL FORWARD_ONLY for
select password
from msdb.dbo.MSdistpublishers
for update of password
open cur_MSdistpublishers
fetch next from cur_MSdistpublishers into @password
while ( @@fetch_status <> -1 )
begin
EXEC sys.xp_repl_convert_encrypt_sysadmin_wrapper @password OUTPUT
update msdb.dbo.MSdistpublishers
set password=@password
where current of cur_MSdistpublishers
fetch next from cur_MSdistpublishers into @password
end')
if @@error <> 0
begin
rollback transaction
return 1
end
end
commit transaction
return (0)
end
