Subscriber / Publisher linking

Last Post 18 May 2004 06:43 PM by bryanmurtha. 0 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
bryanmurtha
New Member
New Member

--
18 May 2004 06:43 PM
Hello,

I need to define one server DB1 say, as the distributor. DB2 as the publisher. DB3 as the subscriber. I have a script that will make DB1 the distributor. What I can't figure out, is how to make the connection. Through a stored procedure or whatever between my publisher and my subscriber. This script is for the distributor this works no problem:
/****** Script to be run at Distributor ******/
/****** Installing the server DB1 as a Distributor. *****/
use master
GO

/* run sp_adddistributor to configure server as the distributor */

exec sp_adddistributor @distributor = N'DB1', @password = N''
GO

/* Run sp_msupdate_agenttype to update the agent profile defaults.
Note that agent profiles will be discussed in the next article */

sp_MSupdate_agenttype_default @profile_id = 1
GO
sp_MSupdate_agenttype_default @profile_id = 2
GO
sp_MSupdate_agenttype_default @profile_id = 4
GO
sp_MSupdate_agenttype_default @profile_id = 6
GO
sp_MSupdate_agenttype_default @profile_id = 11
GO

/* Adding the distribution database */

exec sp_adddistributiondb
@database = N'distribution',
@data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data',
@data_file = N'distribution.MDF',
@data_file_size = 2,
@log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data',
@log_file = N'distribution.LDF',
@log_file_size = 0,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@security_mode = 1
GO

/* output:
New login created.
'distributor_admin' added to role 'sysadmin'.
The dependent aliases were mapped to the new database owner.
Database owner changed.
*/
===============================================================
This script is supposed to define the publisher but I can't figure out how to tell it to look at DB1 as the publisher, so correspondingly it blows up everytime.
================================================================


/****** Script to be run at Publisher ******/

-- Adding a subscriber server
exec sp_addsubscriber
@subscriber = N'DB3',
@type = 0,
@login = N'sa',
@password = N'',
@security_mode = 0,
@frequency_type = 64,
@frequency_interval = 1,
@frequency_relative_interval = 2,
@frequency_recurrence_factor = 0,
@frequency_subday = 8,
@frequency_subday_interval = 1,
@active_start_date = 0,
@active_end_date = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235900,
@description = N''

/* set subscriber's synchronization schedule */

exec sp_changesubscriber_schedule
@subscriber = N'DB3',
@agent_type = 1,
@active_end_date = 0
GO

/* Enable the replication database */
use master
GO

exec sp_replicationdboption
@dbname = N'pubs',
@optname = N'publish',
@value = N'true'
GO


/* Add the transactional publication */

use [pubs]
GO

exec sp_addpublication
@publication = N'pubs_authors_table',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Transactional publication of pubs database from Publisher DB2.',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false',
@immediate_sync = N'false',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@retention = 336,
@allow_queued_tran = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@ftp_login = N'anonymous',
@allow_dts = N'false',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@logreader_job_name = N'DB2-pubs-1'

/* set snapshot agent's schedule */

exec sp_addpublication_snapshot
You are not authorized to post a reply.

Acceptable Use Policy