Checklist to find potential solutions.
1. Check the hardware requirements.
For SQL Server 7.0, you should have:
* Alpha AXP, Intel or compatible platform
* Pentium 166 MHz or higher
* 64MB RAM or more (recommended)
* 180MB hard disk space
For SQL Server 2000, you should have:
* Intel or compatible platform
* Pentium 166 MHz or higher
* 64MB RAM or more (recommended)
* 250MB hard disk space
2. Check the software requirements.
To set SQL Server as publisher for merge or snapshot replication, you can use any edition of Windows XP, Windows 9x, Windows NT 4.0 or Windows 2000.
To set SQL Server as publisher for transactional replication, you should use one of the following operation systems:
* Windows NT Server version 4.0 (with Service Pack 4 or later for SQL Server 7.0 and Service Pack 5 or later for SQL Server 2000)
* Windows NT Server Enterprise Edition version 4.0 (with Service Pack 4 or later for SQL Server 7.0 and Service Pack 5 or later for SQL Server 2000)
* Windows 2000 Server
* Windows 2000 Advanced Server
* Windows 2000 DataCenter
SQL Server editions have the following restrictions:
* SQL Server 7.0 Desktop Edition cannot be used as publisher for transactional replication
* SQL Server 2000 Personal Edition cannot be used as publisher for transactional replication
* SQL Server 2000 Desktop Engine cannot be used as publisher for transactional replication
* SQL Server 2000 Windows CE Edition does not support snapshot or transactional replication, and supports only Anonymous Subscriber to merge replication.
3. Check that you've installed the latest SQL Server service pack.
To check what SQL service pack are you running, see this link:
How can I check what SQL service pack I'm running?
4. Ensure that the account the MSSQLServer and SQLServerAgent services run under belongs to the Administrators local group and is a member of the Domain Users group.
The LocalSystem account does not have network access rights, so this account should not be used if you want to use replication. The account the MSSQLServer and SQLServerAgent service runs under should be a member of the Administrators local group and a member of the Domain Users group.
5. Ensure that you have sysadmin permissions on the SQL Server.
Only members of the sysadmin server role can configure replication, so if you do not have these permissions, you will not be able to set up or configure replication.
6. Ensure that the 'trunc. log on chkpt' option is turned off if you want to set up Transactional replication.
Transactional replication uses the transaction log to capture changes that were made to data and then sends the INSERT, UPDATE, and DELETE statements to Subscribers in the same order they were made in the Publication database.
7. Check the Agent history to determine which task failed and the reason for failure.
To view the Agent history, you can do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group, then expand a server.
3. Expand Replication Monitor and choose the Agent to view history.
4. Right-click appropriate publication and select Agent History...
8. Choose the appropriate Agent profile for your replication model.
For example, if the replication will work through the slow link, choose Slow link agent profile.
To choose the Agent profile, you can do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group, then expand a server.
3. Expand Replication Monitor and choose the Agent.
4. Right-click appropriate publication and select Agent Profiles...
Note. You can also create your own Agent profile (click the New Profile button under the Agent profile window).
9. Set the rowcount or rowcount and checksum validation to avoid problems with data consistency.
You can use the sp_table_validation system stored procedure to test for row count or checksum differences.
You can also find a step-by-step guide illustrating the data validation process for Transactional replication at:
Data Validation for Transactional Replication
And you can find a step-by-step guide illustrating the data validation process for Merge replication at:
Data Validation for Merge Replication
10. If the Merge Agent or Distribution Agent fails on timeout, increase the QueryTimeout value in the Merge Agent or Distribution Agent profile.
The QueryTimeout value in the Merge Agent or Distribution Agent profile indicates the number of seconds before the queries issued by the agent time out.
To increase the QueryTimeout value in the Merge Agent or Distribution Agent profile, you can do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group, then expand a server.
3. Expand Replication Monitor and choose the Agent.
4. Right-click appropriate publication and select Agent Profiles...
5. Click the New Profile button to create the new profile with the appropriate QueryTimeout value.
6. Choose the newly created profile.
11. If you receive an "Access Denied" error when starting the Snapshot Agent, make sure the account the SQL Server Agent runs under has default access and launch DCOM permissions.
To check/set it, you can do the following:
1. Run Dcomcnfg.exe.
2. Click the Default Security tab.
3. Check that the account that SQL Server Agent runs under has default access and launch DCOM permissions.
12. Make sure that the snapshot folder is shared correctly.
Otherwise, replication agents cannot access the snapshot folder, and you will get replication error. For example, on a distributor server running Windows 9x, the snapshot folder defaults to using the local path without a share. So, you should change the local path to a network path by sharing the folder manually.
13. If you get a "Couldn't deliver schema information" error when synchronizing the Internet publications, set up FTP server at the Distributor and set the Merge Agent command line to include an FTP address.
Because a UNC path, which is used by default, works only in the Local Area Network (LAN) for Wide Area Networks (WAN), you should use a FTP address instead.
14. If conflict occurs when merging newly inserted rows that contain identity columns, you must assign each Subscriber that will insert new rows containing an identity a unique range of identity values.
Try to avoid using identity columns in the tables that will be replicated.
15. You cannot specify the uniqueidentifier column with the ROWGUIDCOL property as the primary key of the published table when you use merge publishing from SQL Server to Jet 4.0.
To work around this, you can use a composite primary key with two columns (uniqueidentifier column and integer column, for example).
16. Use a ALTER TABLE statement instead of using the Design Table in SQL Server 7.0 Enterprise Manager tool to modify a table that has the NOT FOR REPLICATION property.
Otherwise, the NOT FOR REPLICATION property on the IDENTITY column will be lost.
17. Use the Replication Conflict Viewer to get more information about conflict details.
Replication Conflict Viewer is a Wzcnflct.exe file that can be executed from the command prompt. You can run Replication Conflict Viewer from the SQL Server Enterprise Manager. To run Replication Conflict Viewer from the SQL Server Enterprise Manager, do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group, then expand a server.
3. Expand Replication Monitor and choose the article.
4. Right-click article and select View Conflicts...
1. Check the hardware requirements.
For SQL Server 7.0, you should have:
* Alpha AXP, Intel or compatible platform
* Pentium 166 MHz or higher
* 64MB RAM or more (recommended)
* 180MB hard disk space
For SQL Server 2000, you should have:
* Intel or compatible platform
* Pentium 166 MHz or higher
* 64MB RAM or more (recommended)
* 250MB hard disk space
2. Check the software requirements.
To set SQL Server as publisher for merge or snapshot replication, you can use any edition of Windows XP, Windows 9x, Windows NT 4.0 or Windows 2000.
To set SQL Server as publisher for transactional replication, you should use one of the following operation systems:
* Windows NT Server version 4.0 (with Service Pack 4 or later for SQL Server 7.0 and Service Pack 5 or later for SQL Server 2000)
* Windows NT Server Enterprise Edition version 4.0 (with Service Pack 4 or later for SQL Server 7.0 and Service Pack 5 or later for SQL Server 2000)
* Windows 2000 Server
* Windows 2000 Advanced Server
* Windows 2000 DataCenter
SQL Server editions have the following restrictions:
* SQL Server 7.0 Desktop Edition cannot be used as publisher for transactional replication
* SQL Server 2000 Personal Edition cannot be used as publisher for transactional replication
* SQL Server 2000 Desktop Engine cannot be used as publisher for transactional replication
* SQL Server 2000 Windows CE Edition does not support snapshot or transactional replication, and supports only Anonymous Subscriber to merge replication.
3. Check that you've installed the latest SQL Server service pack.
To check what SQL service pack are you running, see this link:
How can I check what SQL service pack I'm running?
4. Ensure that the account the MSSQLServer and SQLServerAgent services run under belongs to the Administrators local group and is a member of the Domain Users group.
The LocalSystem account does not have network access rights, so this account should not be used if you want to use replication. The account the MSSQLServer and SQLServerAgent service runs under should be a member of the Administrators local group and a member of the Domain Users group.
5. Ensure that you have sysadmin permissions on the SQL Server.
Only members of the sysadmin server role can configure replication, so if you do not have these permissions, you will not be able to set up or configure replication.
6. Ensure that the 'trunc. log on chkpt' option is turned off if you want to set up Transactional replication.
Transactional replication uses the transaction log to capture changes that were made to data and then sends the INSERT, UPDATE, and DELETE statements to Subscribers in the same order they were made in the Publication database.
7. Check the Agent history to determine which task failed and the reason for failure.
To view the Agent history, you can do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group, then expand a server.
3. Expand Replication Monitor and choose the Agent to view history.
4. Right-click appropriate publication and select Agent History...
8. Choose the appropriate Agent profile for your replication model.
For example, if the replication will work through the slow link, choose Slow link agent profile.
To choose the Agent profile, you can do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group, then expand a server.
3. Expand Replication Monitor and choose the Agent.
4. Right-click appropriate publication and select Agent Profiles...
Note. You can also create your own Agent profile (click the New Profile button under the Agent profile window).
9. Set the rowcount or rowcount and checksum validation to avoid problems with data consistency.
You can use the sp_table_validation system stored procedure to test for row count or checksum differences.
You can also find a step-by-step guide illustrating the data validation process for Transactional replication at:
Data Validation for Transactional Replication
And you can find a step-by-step guide illustrating the data validation process for Merge replication at:
Data Validation for Merge Replication
10. If the Merge Agent or Distribution Agent fails on timeout, increase the QueryTimeout value in the Merge Agent or Distribution Agent profile.
The QueryTimeout value in the Merge Agent or Distribution Agent profile indicates the number of seconds before the queries issued by the agent time out.
To increase the QueryTimeout value in the Merge Agent or Distribution Agent profile, you can do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group, then expand a server.
3. Expand Replication Monitor and choose the Agent.
4. Right-click appropriate publication and select Agent Profiles...
5. Click the New Profile button to create the new profile with the appropriate QueryTimeout value.
6. Choose the newly created profile.
11. If you receive an "Access Denied" error when starting the Snapshot Agent, make sure the account the SQL Server Agent runs under has default access and launch DCOM permissions.
To check/set it, you can do the following:
1. Run Dcomcnfg.exe.
2. Click the Default Security tab.
3. Check that the account that SQL Server Agent runs under has default access and launch DCOM permissions.
12. Make sure that the snapshot folder is shared correctly.
Otherwise, replication agents cannot access the snapshot folder, and you will get replication error. For example, on a distributor server running Windows 9x, the snapshot folder defaults to using the local path without a share. So, you should change the local path to a network path by sharing the folder manually.
13. If you get a "Couldn't deliver schema information" error when synchronizing the Internet publications, set up FTP server at the Distributor and set the Merge Agent command line to include an FTP address.
Because a UNC path, which is used by default, works only in the Local Area Network (LAN) for Wide Area Networks (WAN), you should use a FTP address instead.
14. If conflict occurs when merging newly inserted rows that contain identity columns, you must assign each Subscriber that will insert new rows containing an identity a unique range of identity values.
Try to avoid using identity columns in the tables that will be replicated.
15. You cannot specify the uniqueidentifier column with the ROWGUIDCOL property as the primary key of the published table when you use merge publishing from SQL Server to Jet 4.0.
To work around this, you can use a composite primary key with two columns (uniqueidentifier column and integer column, for example).
16. Use a ALTER TABLE statement instead of using the Design Table in SQL Server 7.0 Enterprise Manager tool to modify a table that has the NOT FOR REPLICATION property.
Otherwise, the NOT FOR REPLICATION property on the IDENTITY column will be lost.
17. Use the Replication Conflict Viewer to get more information about conflict details.
Replication Conflict Viewer is a Wzcnflct.exe file that can be executed from the command prompt. You can run Replication Conflict Viewer from the SQL Server Enterprise Manager. To run Replication Conflict Viewer from the SQL Server Enterprise Manager, do the following:
1. Run SQL Server Enterprise Manager.
2. Expand a server group, then expand a server.
3. Expand Replication Monitor and choose the article.
4. Right-click article and select View Conflicts...
No comments:
Post a Comment