Replication Configuration and Basics

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
2. Create and configure the snapshot replication
sp_addpublication
3. create and configure snapshot agent
sp_addpublication_snapshot
4. Create and configure the articles
sp_add_article
5. create horizontal or vertical partitions
sp_articleview/sp_articlefilter/sp_articlecolumn
6. create and confugre the subscription(s)
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’
– 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)
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
—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
 
added 10272009: we should also know  “Multiple Versions of SQL Server in a Replication Topology”
added: 12122009 :Replication over different domain and Intenet:
 
 
 
———————————————————————
referrence:
20server%202000%20books&f=false
 
 
 
About these ads
This entry was posted in Replication and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s