Snapshot replication trick

Regarding how to select a proper replication type on SQL Server, many article gave many opinions, also tons of “best practice” may help as well. The official rules may be referenced to Microsoft MSDN: Selecting the appropriate type of replication

Recently we found an issue on our website, the web pages will be broken sometimes, it occur rarely but it did happen now and then which is annoying. By further investigation, I found the issue is due to replication, whenever the replication starting, some web pages which reference to the tables which involved in the replication will be broken. Our replication is snapshot replication, which I thought it should change to transactional replication anyway, just because some application needs to trigger the replication by clicking button.

However, when I troubleshooting the configuration of snapshot, just found under articles–>”Destination object” –> “Action if name is in use”, the default setting is “drop existing object and create a new one”, that’s the root reason of the problem. No surprise the web pages will broke as whenever the replication in progress, those tables are dropped, the query cannot refer to the tables and throw some wired errors. Also as this is not a heavy duty replication, so clients rarely meet this issue unless they just click the page at the replication time(5-10 seconds).

The solution is easy, just change the default setting to “Truncate all data in the existing object”

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s