Replication Type
- Snapshot Replication
- Transaction Replication
- Merge Replication
- Heterogeneous Replication: Replicate to other DBMS.
Replication Data Conflict
- Duplicate primary key
- Update conflict
- Update non exist data
Replication Agent
- distrib.exe: use for distributor
- snapshot.exe: use for snapshot replication
- logread.exe: use for transaction replication
- replmerg.exe: use for merge replication
- 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.
- use master
- exec sp_adddistributor @distributor = N'SG11\MSSQL2008R2', @password = N'XXXXXX'
- GO
- 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
- GO
- use [distribution]
- if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
- create table UIProperties(id int)
- -- Use the network path for both pull replication
- if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
- EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\SG11\C$\REP_DATA', 'user', dbo, 'table', 'UIProperties'
- else
- EXEC sp_addextendedproperty N'SnapshotFolder', N'\\SG11\C$\REP_DATA', 'user', dbo, 'table', 'UIProperties'
- GO
Configures publisher to use the distribution database.
- 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'
- GO
Other Store procedure to check Distribution DB
- sp_helpdistpublisher
- sp_helpdistributiondb
- 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.
- SELECT @@SERVERNAME
Drop and re add the server name. SQL Server need to be bounce to make it effective.
- sp_dropserver 'WIN-HNB91NNAB2G\MSSQL2008R2'
- GO
- sp_addserver 'SG11\MSSQL2008R2', 'local'
- GO
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