Overview

SQL Server 2005 Replication Testing Replication

Testing
To identify the records from Publisher and Subscribers database, we have to define a row in the Counters Table with different CurCount values.

For Publisher Machine
TableName : SiteID
CurCount : 0

For Subscriber Machine
TableName : SiteID
CurCount : 1

Note: If that database is already replicated previously, then run the below query on the subscriber machine and give the new SiteID (1...9 or a...z or A..Z)

Find the Existing SiteID

SELECT DISTINCT(SiteID) AS SiteID
FROM (
SELECT DISTINCT(SUBSTRING(ConsIntID,3,1)) AS SiteID
FROM dbo.Consultants
UNION ALL
SELECT DISTINCT(SUBSTRING(CompanyIntID,3,1)) AS SiteID
FROM dbo.Companies
UNION ALL
SELECT DISTINCT(SUBSTRING(ContactIntID,3,1)) AS SiteID
FROM dbo.Contacts
UNION ALL
SELECT DISTINCT(SUBSTRING(RemIntID,3,1)) AS SiteID
FROM dbo.Reminders
UNION ALL
SELECT DISTINCT(SUBSTRING(ReqIntID,3,1)) AS SiteID
FROM dbo.Requirements
UNION ALL
SELECT DISTINCT(SUBSTRING(FROMID,3,1)) AS SiteID
FROM dbo.links
UNION ALL
SELECT DISTINCT(SUBSTRING(TOID,3,1)) AS SiteID
FROM dbo.links
) TEMP

Increment the CurCount values as new Subscriber is added. Following illustrates the screen shorts

• Launch the Replication monitor and check the status of replication.

• Right click on the Subscription Wizard List and select Start Synchronizing.

• To check the replication is working properly, update a record at the Subscriber Machine and See the reflection at Publisher Machine and vice versa.
  
  E.g. Open the Companies Table and update a column and see the reflection at other replicated database.

 

Deprecated Features in SQL Server 2005 Replication

Feature	Description
Schema changes using sp_repladdcolumn and sp_repldropcolumn	The stored procedures sp_repladdcolumn (Transact-SQL) and sp_repldropcolumn (Transact-SQL) have been deprecated. Use schema change replication instead. For more information, see Making Schema Changes on Publication Databases . The stored procedures cannot be used for adding or dropping columns with data types introduced in SQL Server 2005: XML, varchar(max), nvarchar(max), varbinary(max), or user defined types (UDT).
Multicolumn UPDATE option	When merge replication performs an update, it updates all changed columns in one UPDATE statement and resets unchanged columns to their original values. Alternatively, it can issue multiple UPDATE statements, with one UPDATE statement for each column that has changed. The multicolumn UPDATE statement is typically more efficient. In previous versions of SQL Server, it was recommended to specify a value of false for the fast_multicol_updateproc article option to address cases in which a multicolumn update (one UPDATE statement) might be less efficient: Most updates involve a small number of columns. Index maintenance on unchanged columns is high because those columns are reset when updates occur. Due to performance improvements in SQL Server, this option is no longer required for these cases.

Troubleshooting:
Question:
I had created a distribution in sqlserver 2005. I don't want it. When i am
trying to delete it from the replication node, it says that it could not be
dropped. How to solve the problem.
I expect a quick reply .

If you have any active publications drop them.
Then issue the following commands.

exec sp_dropdistpublisher @publisher = @@ServerName
GO
use master
exec sp_dropdistributiondb @database =N'cBizOneIndia'
GO
exec sp_dropdistributor @no_checks = 1, @ignore_distributor = 1
GO

Question:

Cannot drop the database because it is being used for replication. (Microsoft SQL Server, Error: 3724)

Fix/Workaround/Solution:

The solution is very simple. Create the empty database with the same name on another server/instance first. Take full back of the same and forced restore over this database (With our replication settings).

Question:
When we have proble with bulk copy, execute following:

update sysmergepublications
set generation_leveling_threshold=0
where generation_leveling_threshold=1000

Problems If You Use an IP Address to Configure Replication

If you try to register the server (Publisher or the Subscriber) by using an IP address instead of a client alias, or if the client alias is different from the actual SQL Server NetBIOS name, the Merge Agent may fail, and you receive the following error messages:

• Error 20084:
  The process could not connect to subscriber 'IP address'.
• Error 18456:
  Login failed for user 'administrator'
• The subscription to publication 'test' is invalid.
• Error 14010:
  The remote server is not defined as a subscription server.
  For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
  217395 (http://support.microsoft.com/kb/217395/EN-US/ ) PRB: Error 18482: "Unable to Connect to Site . . ."
• Could not configure [SQL Server Name] as the distributor for [SQL Server Name].
• Error 18483:
  Could not connect to server [SQL Server Name] because 'distributor_admin' is not defined as a remote login at the server.

Solution: Click Hear

Adding the columns in 2005 replication database

sp_repladdcolumn '<Table_Name>', '<Column_Name>', '<Typetext>', 'all', null, 0, 0

sp_repladdcolumn 'Consultants', 'UserField11', 'nvarchar(50) null', 'all', null, 0, 0

Note: DDL commands can only be executed at the root Publisher, not at any of the republishing Subscribers.

This procedure should not be used on columns with data types that were introduced in SQL Server 2005 or SQL Server 2008.