Thursday, November 3, 2011

0 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

image

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

image

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

image

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

image

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

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates