|
|
| |
/*****************************************************************************************
Created: 23/4/2005
By: Paul Ibison
Purpose: Script to be run at a subscriber. It will
(a) create a linked server
(b) add the new subscriber
(c) add the new (continuous) pull subscription to a snapshot and merge publication
Modifications:
******************************************************************************************/
USE master
go
-- Add a stored procedure to update the remote server to a linked server if it already exists
-- If not, no worries, as the update will not do anything in this case
sp_configure 'allow updates', 1
go
RECONFIGURE WITH OVERRIDE
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[changestatus]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[changestatus]
GO
CREATE PROCEDURE changestatus @servername sysname AS
DECLARE @srvstat smallint
SELECT @srvstat = srvstatus
FROM master.dbo.sysservers
WHERE srvname = @servername
-- Configure the remote server for 'data access'
SELECT @srvstat = @srvstat | 32 | 128
UPDATE master.dbo.sysservers
SET srvstatus = @srvstat
WHERE srvname = @servername
go
EXEC changestatus 'SQLDev'
go
sp_configure 'allow updates', 0
go
RECONFIGURE WITH OVERRIDE
go
-- If there never was a linked or remote server, the code below will create it
if not exists (select * from sysservers where srvname = 'SQLDev')
EXEC sp_addlinkedserver 'SQLDev','SQL Server'
GO
-- If there never was a Dev_User login, the code below will create it
if not exists (select name from sysxlogins where name = 'Dev_User')
EXEC sp_addlinkedsrvlogin 'SQLDev', 'false', NULL, 'Dev_User', 'Dev_Password'
-- Initially we use Dev_User to add the subscriber, but this is to be replaced by using "RUN AS"
-- If the subscriber doesn't already exist, the code below will create it
use testsubscription1
go
declare @servername varchar(255)
set @servername = @@servername
--////////////////// SNAPSHOT ///////////////////////////////////////
if not exists (select subscriber from SQLDev.distribution.dbo.MSsubscriber_info where subscriber = @servername)
begin
exec SQLDev.mgmtsystem.dbo.sp_addsubscriber
@subscriber = @servername
, @login = 'Dev_User'
, @password = 'cms_password'
, @security_mode = 0
end
declare @hassnapshotsubscription bit
set @hassnapshotsubscription = 0
if exists(select * from sysobjects where name = 'MSreplication_subscriptions')
if exists(select publication from MSreplication_subscriptions where publication = 'BravoPub1')
set @hassnapshotsubscription = 1
if @hassnapshotsubscription = 0
begin
exec sp_addpullsubscription
@publisher = N'SQLDev',
@publisher_db = N'mgmtsystem',
@publication = N'BravoPub1',
@independent_agent = N'false',
@subscription_type = N'pull',
@description = N'Snapshot publication of mgmtsystem database financials from Publisher SQLDev.',
@update_mode = N'read only',
@immediate_sync = 0
-- quick check to see if the subscription already exists - only try to add if it doesn't
if not exists (SELECT pub.name, srvname
FROM SQLDev.mgmtsystem.dbo.syssubscriptions subs,
SQLDev.master.dbo.sysservers ss,
SQLDev.mgmtsystem.dbo.syspublications pub,
SQLDev.mgmtsystem.dbo.sysextendedarticlesview art
where subs.srvid = ss.srvid
AND art.pubid = pub.pubid
AND subs.artid = art.artid
AND pub.name = 'BravoPub1' AND srvname = @servername)
BEGIN
exec SQLDev.mgmtsystem.dbo.sp_addsubscription @publication = 'BravoPub1'
, @subscriber = @servername
, @destination_db = 'testsubscription1'
, @subscription_type = 'pull'
END
exec sp_addpullsubscription_agent @publisher = 'SQLDev'
, @publisher_db = 'mgmtsystem'
, @publication = 'BravoPub1'
, @subscriber = @servername
, @subscriber_db = 'testsubscription1'
, @subscriber_security_mode = 0
, @subscriber_login = 'sa'
, @subscriber_password = 'security'
, @distributor = 'SQLDev'
, @distribution_db = 'distribution'
, @distributor_security_mode = 0
, @distributor_login = 'Dev_User'
, @distributor_password = 'cms_password'
, @frequency_type = 64
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 500
, @active_end_time_of_day = 459
, @active_start_date = 20050422
, @active_end_date = 99991231
, @publication_type= 1
end
--////////////////// MERGE ///////////////////////////////////////
declare @hasmergesubscription bit
set @hasmergesubscription = 0
if exists(select * from sysobjects where name = 'sysmergesubscriptions')
if exists(select publication from sysmergesubscriptions where publication = 'mgmtsystem_DataTables2')
set @hasmergesubscription = 1
if @hasmergesubscription = 0
begin
exec sp_addmergepullsubscription
@publisher = 'SQLDev',
@publisher_db = 'mgmtsystem',
@publication = 'mgmtsystem_DataTables2',
@description = 'Merge publication'
-- quick check to see if the subscription already exists - only try to add if it doesn't
if not exists (SELECT pub.name, srvname
FROM SQLDev.mgmtsystem.dbo.syssubscriptions subs,
SQLDev.master.dbo.sysservers ss,
SQLDev.mgmtsystem.dbo.syspublications pub,
SQLDev.mgmtsystem.dbo.sysextendedarticlesview art
where subs.srvid = ss.srvid
AND art.pubid = pub.pubid
AND subs.artid = art.artid
AND pub.name = 'mgmtsystem_DataTables2' AND srvname = @@servername)
BEGIN
exec SQLDev.mgmtsystem.dbo.sp_addmergesubscription
@publication = 'mgmtsystem_DataTables2'
, @subscriber = @@servername
, @subscription_type = 'pull'
, @subscriber_db = 'testsubscription1'
END
exec sp_addmergepullsubscription_agent @publisher = 'SQLDev'
, @publisher_db = 'mgmtsystem'
, @publication = 'mgmtsystem_DataTables2'
, @subscriber = @@servername
, @subscriber_db = 'testsubscription1'
, @subscriber_security_mode = 0
, @subscriber_login = 'sa'
, @subscriber_password = 'security'
, @distributor = 'SQLDev'
, @distributor_security_mode = 0
, @distributor_login = 'Dev_User'
, @distributor_password = 'cms_password'
, @publisher_security_mode = 0
, @publisher_login = 'Dev_User'
, @publisher_password = 'cms_password'
, @frequency_type = 64
, @frequency_interval = 0
, @frequency_relative_interval = 0
, @frequency_recurrence_factor = 0
, @frequency_subday = 0
, @frequency_subday_interval = 0
, @active_start_time_of_day = 500
, @active_end_time_of_day = 459
, @active_start_date = 20050422
, @active_end_date = 99991231
end
|
|
|
|
|
April 2005
|
