November 20, 2014 at 4:01 pm
Hi,
We have transactional replication set up in production environment. Same database has CDC enabled and some of the replicated tables have CDC enabled on them. It's working fine.
In development environment there was a need to set up similar solution for a different database which was failing and returning error "Replicated transactions are waiting for next Log backup or for mirroring partner to catch up".
There is no mirroring. Running backup didn't help.
I tried to to recreate (script out and move to Dev) database and replication set up from production, however, it was throwing the same error. Compared all database properties, article properties and agent properties to make sure that they are the same in both Dev and Prod.
To start fresh, I created One database in Dev with two tables: One table for replication, the other for CDC. CDC by itself is working fine till replication is set up. As soon as replication is set up it starts throwing error above. Similar, If I first create transactional replication it is working fine, but as soon as CDC is enabled on the database it stops working.
This was redone several times. "sync with backup" is FALSE for both user database and distribution database. 'Sa' account is owner of all the jobs, and both databases (distribution and user database).
It looks like that issue starts when CDC capture job is dropped to be substituted with Log Reader job.
If I remove replication (and capture job gets recreated automatically) CDC starts working again.
Running sp_repldone , sp_replflush and restarting log reader job clears the erorr, but only till the next attempt to update any record in any of the tables.
SQL Server Service and SQL Agent are running under windows domain account. (which even local admin and explicit member of sysadmin role)
Distributor and publisher are running on the same server.
Dev Server version: SQL 2008 R2 (SP3) Enterprise. Issue was there with SP2 CU13 as well.
Prod Server version: (that is working fine) SQL 2008 R2 (SP2), CU9. Enterprise
I would appreciate if someone could help with ideas for further troublshooting.
Please don't tell me that set up is not really good (distributor should be separate, why do we need CDC and transactional replication etc. as I can't change that).
Many thanks in advance
Vera
November 23, 2014 at 7:58 pm
Update:
At the end i decided to start from the scratch and created solution on SQL Server 2008 R2 with CU9 and it is working. I followed the same procedure for creating replication and enabling CDC.
Based on everything I checked:
*sys.configurations
*sys.databases for both distributor and user ID
*Database properties
*Article properties
*Agent profiles
*Security
*Database and job owners
*backups
it seems that the only difference between these two servers is build.
Wondering if there is a possibility that bug was introduced after CU9?
November 27, 2014 at 4:34 pm
Just in case someone finds this when troubleshooting the same issue:
Decided to upgrade my"fresh" serve and install CU13 => no issues
It seems that problem is related to the server itself, however I couldn't find where the problem is and covered many things.
Another wierdwieldg is, that on the server where replication was trowithrowingr, when I tried to disable CDC I got error:
“Msg 22831, Level 16, State 1, Procedure sp_cdc_disable_db_internal, Line 262
Could not update the metadata that indicates database XXX is not enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_drop_objects]'. The error returned was 229: 'The EXECUTE permission was denied on the object 'sp_MScdc_ddl_event', database 'mssqlsystemresource', schema 'sys'.'. Use the action and error to determine the cause of the failure and resubmit the request.
“
Profiled disable command (broke it down to statements) and the only login/user context used is 'sa', service account and myself.
All logins are members of sysadmin role. Makes no sense.
With new set up everything is behaving :-), but still hope to find out what went wrong there...
November 27, 2014 at 5:54 pm
Last one from me!
Managed to fix my issue (just couldn't give up without understanding what was causing it).
Came across this article: http://stackoverflow.com/questions/1473315/the-select-permission-was-denied-on-the-object-sysobjects-database-mssqlsyst
which gave me the idea to check permissions for public role in details and comapre to permissions on the server where it was working (and where I know it's set up correctly).
So - found the differences, scripted out missing permissions and applied and replication is fixed 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply