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.
|
___________________________________________________________________________________________________________
Page 7 of 7
___________________________________________________________________________________________________________
|
|
|
|