1 Replication (1) Create the Distributor

Replication Type

1. Snapshot Replication
2. Transaction Replication
3. Merge Replication
4. Heterogeneous Replication: Replicate to other DBMS.

Replication Data Conflict

1. Duplicate primary key
2. Update conflict
3. Update non exist data

Replication Agent

1. distrib.exe: use for distributor
2. snapshot.exe: use for snapshot replication
3. logread.exe: use for transaction replication
4. replmerg.exe: use for merge replication
5. qrdsvc.exe: use when configure as  queued updating option within Transaction Replication

 

SQL Server Agent is required for setting up the replication. ( It is suggested to configure for start automatically.

We can use SSMS’s wizard to generate the replication script.

Create the Distributor , Distributor database

In the sample script below, I created the distributor database name “distribution” and using the network path to store the replication data.

1. use master
2. exec sp_adddistributor @distributor = N'SG11\MSSQL2008R2', @password = N'XXXXXX'
3. GO
4. exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQL2008R2\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
5. GO
6.  
7. use [distribution]
8. if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
9.     create table UIProperties(id int)
10. -- Use the network path for both pull replication
11. if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
12.     EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\SG11\C$\REP_DATA', 'user', dbo, 'table', 'UIProperties'
13. else
14.     EXEC sp_addextendedproperty N'SnapshotFolder', N'\\SG11\C$\REP_DATA', 'user', dbo, 'table', 'UIProperties'
15. GO

Configures publisher to use the distribution database.

1. exec sp_adddistpublisher @publisher = N'SG11\MSSQL2008R2', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'\\SG11\C$\REP_DATA', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
2. GO

Other Store procedure to check Distribution DB

1. sp_helpdistpublisher
2. sp_helpdistributiondb
3. sp_helpdistributor
   

Other issues:

How to fix “SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported.”

Per Microsoft responds from here http://connect.microsoft.com/SQLServer/feedback/details/365761/sql-server-replication-requires-the-actual-server-name-to-make-a-connection-to-the-server It is by design. We have to use HOSTNAME\MSSQLNAME

However, I still got this in my lab environment. The main reason is I rename my windows server name after I install the SQL Server.  Check the MSSQL Server Name from below Query, It is still set as old server name.

1. SELECT @@SERVERNAME

Drop and re add the server name. SQL Server need to be bounce to make it effective.

1. sp_dropserver 'WIN-HNB91NNAB2G\MSSQL2008R2'
2. GO
3. sp_addserver 'SG11\MSSQL2008R2', 'local'
4. GO