It is important to create index on the columns that are used for filtering and joins so as to perform faster retrieval of data. Whenever merge agent runs, it searches for the data, if database engine finds an index on the columns that are used for filter and join it can make the retrieval faster, otherwise it would have to search through the table.
Note
Reference article on index:
I would strongly recommend reading the article on index(s) available at http://technet.microsoft.com/en-us/library/cc966523.aspx. This is the best ever article I have read on index(s); although for SQL Server 2000 it is equally true and applicable for any other version of SQL Server. The following are some more articles that could be of assistance:
Microsoft recommends that we should separate the columns that have LOB as data type to another table using a one-to-one relationship. This would help us reduce the overhead on performance.
There has been a lot said about using identity as primary key in replicated database. The primary issue behind this is the manageability — every time identity value reaches its threshold value, we will have to reseed the identity value and adjust identity range at Publisher end; this happens when we restore backup at Subscriber end. We will have to find the last inserted identity value and adjust the identity range at Publisher using the following steps:
/* Step 1: Execute below T-SQL for every subscriber
*/
selectident_current('tblName')
go
/* Step 2 :Note down the highest value
found from subscribers
*/
/*
Step 3 : Execute below T-SQL at publisher
*/
dbcc checkident('tblName',reseed,value+1)
-- Note: value = heighest value found in step 1
/*
Step 4: At publisher execute below T-SQL
*/
executesp_adjustpublisherIdentityrange PublicationName, tblName
Note
Here is an article on my blog for reference, suggesting why not to use identity as PK: http://www.sql-server-citation.com/2009/12/common-mistakes-in-sql-server-part-2.html.
Question: I get the following message: Violation of Primary Key. And now I am unable to add a new value. So, how do I resolve it?
Answer: As we are all aware, the primary key violation may occur only if there is a conflict of key value, and we should be careful while troubleshooting this error. Typically, we should check the following:
If the identity column is used, whether it is set to the "not for replication" option
Whether the identity management is auto or manual and what is the range specified
Is there any schema difference in any of the Subscriber and/or with Publisher
Check whether the duplicate record exists, and if yes, act accordingly
There are so many reasons depending on the situation, but I would certainly recommend referring to an article by Hilary Cotter, available at http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/.
Question: Sometimes I notice the following error: The Schema script 'some file name' could not propagated to the subscriber (Error number: -2142010001) or OS Error 2 or System cannot find the file specified. Why is it so?
Answer: Ensure that the agent account responsible to run the merge account has the required (read, list and write) share and NTFS permission to the snapshot folder.
Question: Why does Merge Agent or Distribution Agent keep on getting timeout error?
Answer: To overcome the problem, go to Agent Properties and change the value of
QueryTimeOutparameter as shown in the following screenshot: