Thursday, November 3, 2011

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

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

1 comments:

  1. Cartierbuy replica watches is the renowned French jeweller and watchmaker with replica cartier a history of pioneering design and a drive for excellence. The first references to Cartier wristwatches date back to 1888. Late in the 19th century it was pocket and chatelaine watches that were most popular, but Louis Cartier saw a future in timepieces that could be worn on the wrist.

    ReplyDelete

 

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