Subscribe to this blog
Briefcase
Print
Hi,
Just want to share info about replication script which i generally use. this is good for sql server 2000 Transaction replication, and might also 2005/2008
>>Cust steps to configure replication(Transactional).
1. Configure the server /database for replication
sp_replicationdboption
sp_replicationdboption
2. Create and configure the snapshot replication
sp_addpublication
sp_addpublication
3. create and configure snapshot agent
sp_addpublication_snapshot
sp_addpublication_snapshot
4. Create and configure the articles
sp_add_article
sp_add_article
5. create horizontal or vertical partitions
sp_articleview/sp_articlefilter/sp_articlecolumn
sp_articleview/sp_articlefilter/sp_articlecolumn
6. create and confugre the subscription(s)
sp_addsubsription/sp-addpullsubsription/sp_addpullsubsriptionagent
sp_addsubsription/sp-addpullsubsription/sp_addpullsubsriptionagent
---------------------------------------------------------------------
e.g.
------------------->Setup the replication environment (configure distributor and publisher) here we can run below commands on publisher if publisher and distributor are on the same box.
--configure the distributor server
use master
exec sp_adddistributor @distributor = 'Distrubutorservernm'
exec sp_adddistributor @distributor = 'Distrubutorservernm'
-- distribution database
exec sp_adddistributiondb @database = N'distribution'
-- configure distribution publisher
exec sp_adddistpublisher @publisher = 'PublisherServer', @distribution_db = N'distribution', @working_directory = 'C:\'
------------------>now configure publisher.
use 'PublisherDB'
-- create table test (i int primary key,j int)
create table test1 (i int primary key,j int)
create table test2 (i int primary key,j int)
create table test1 (i int primary key,j int)
create table test2 (i int primary key,j int)
sp_replicationdboption 'publisherDB','publish','true'
GO
sp_addpublication 'publisherDB',@status='active', @sync_method = N'native'
go
sp_addpublication_snapshot @publication ='publisherDB'
GO
sp_addarticle 'publisherDB','test1','test1'
GO
sp_addarticle 'publisherDB','test2','test2'
GO
GO
sp_addpublication 'publisherDB',@status='active', @sync_method = N'native'
go
sp_addpublication_snapshot @publication ='publisherDB'
GO
sp_addarticle 'publisherDB','test1','test1'
GO
sp_addarticle 'publisherDB','test2','test2'
GO
---Adding and configuring subscriber.
exec sp_addsubscriber @subscriber = 'SubscriberserverName',,@description = 'Publisher_to_Subsriber'
go
sp_addsubscription @publication = N'publisherDB', @subscriber = @@servername,
@destination_db ='subsriberDB', @sync_type = N'automatic'
GO
sp_addsubscription @publication = N'publisherDB', @subscriber = @@servername,
@destination_db ='subsriberDB', @sync_type = N'automatic'
GO
---------------------------------------------------------------------
referrence:
20server%202000%20books&f=false
No comments.