Seeking for transactional replication setup and troubleshooting

Dear All,

I am new to mssql and I am about to setup a replication. I had setup one transactional replication successful. However, the developer said a table data missing from primary after I setup the transactional replication. I need some advice if the subscriber could make changes to publisher?

The primary/publisher database version is 

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)  Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

The standby/subscriber database version is 

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)  Copyright (c) 1988-2008 Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

May I know if anyway i could stop anyone make changes to subscriber (if subscriber could make changes and revert the changes and apply into primary/publisher)

I setup the replication based on the URL below:
http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/

I need help here.

1. Did subscriber could make changes and affect the primary? (example delete tables and data)

2. How to prevent other access to make changes other than publisher system user?

3. Any license required for my standby/subscriber?

4. any patching required for my databases as both seems very outdated version?

5. anything i missing or did not raise up in above questions

September 3rd, 2015 8:56am

Find the answers to your questions below.

1. Did subscriber could make changes and affect the primary? (example delete tables and data)

As per your post and the article you shared, you have created a vanilla transactional replication. In such case no data changes that happens in the subscriber will be replicated to publisher. So publisher will not know of any changes in subscriber. Its a one-way replication from publisher to subscriber.

2. How to prevent other access to make changes other than publisher system user?

Revoke access of all other users in the subscriber database.

3. Any license required for my standby/subscriber?

Yes. It will be licensed as the subscriber is always available for this and other databases.

4. any patching required for my databases as both seems very outdated version?

As per this article your service pack support is already expired.

https://support.microsoft.com/en-gb/lifecycle?p1=13165

Please update SP4.

5. anything i missing or did not raise up in above questions

If you did create transactional replication in the correct way there is no chance the table data will be missing. Do you have any kind of auditing to see if there were many deletes in the publisher? You might have to see who all accessed the database during that time. Take a look at the default traces in sql server to see if there is data during that time frame.

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server---the-power-of-performance-and-security-auditing/

Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2015 9:41am

Dear Ashwin,

Thank you so much to spend your time to answer my questions. 

I am following that URL instruction to setup replication. So, it is a 1 way replication and subscriber will not impact to publisher.

I am not able to enable the audit due to Standard Edition not support the system audit.

My question raised because after I had enabled the replication as mentioned earlier, the developer claims one table has missing data. 

However, I don't think replication could cause this problem and without audit function not possible to trail the DML action.

September 4th, 2015 8:02am

Did you check if that data is available in the subscriber?

Or else if you have backups taken prior to setting up replication, restore them in a test server and see if the data was there. In that way you can prove if setting up replication caused this.

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2015 10:38am

Thanks, Ashwin. 

I will run the test again. The actual issue is more complicated than I wrote here. If you are interested to further discuss, we may bring it into private chat or email.

September 9th, 2015 1:21am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics