December 22, 2014 at 6:23 am
We have a database which is (a subset of tables are) replicated to another via transactional replication. Whilst most changes made at the published database reach the subscriber within a matter of seconds, we have a SQL Agent job which performs a calculation in the published database and then immediately exports data from the subscriber using log shipping. The result is that the calculated changes do not make it through to the exported transaction logs in time.
Is there a way to manually "refresh" the subscriber databases using T-SQL?
December 22, 2014 at 6:43 am
You can invalidate the subscription and re-snapshot, but I don't think that's what you want.
Easiest thing here would probably to put a delay in the export process. Transactional replication inherently has latency, you cannot assume that any changes will propagate instantly, they won't.
Change the calculation process so that it writes some form of status into a replicated table, then have the export process wait until that expected status is there.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2014 at 7:34 am
Hi Gail,
Thank you for your response and suggestions, we may well end up doing something like this. Another option would be for us to break the SQL Agent into two separate jobs (one scheduled after the other), although this just means more maintenance going forwards and is not particularly attractive as a solution.
Is there definitely no T-SQL way of pushing all outstanding changes across, or manually calling the replication process (even though it runs continuously)? It would be great if we could run a job step to ensure that known changes are replicated at the subscriber before a subsequent SQL Agent job step is executed.
December 22, 2014 at 8:33 am
zoggling (12/22/2014)
Is there definitely no T-SQL way of pushing all outstanding changes across,
As I said, you can drop the subscriber and re-snapshot. That'll do what you want....
or manually calling the replication process (even though it runs continuously)?
How would calling something that runs *continuously* help? It already runs continuously. Replication latency is a factor of the network and the disks mostly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply