March 5, 2013 at 4:38 am
Hi all, apologies if this has been asked before, I tried searching and came up with nothing.
I'm using Transactional Replication to Push a publication out to several other machines. These machines are used for reporting off, and there's a requirement to show on these reports how old the data is. So the idea is to show something like "Last updated 05-Mar-2013 11:29:00".
My question is whether it's possible to query this information from the subscriber server/database alone? I'm aware that there's various means of doing it from the publisher, but these machines are to be used for disaster recovery reports and as such we can't rely on being able to access the publisher server.
I've considered:
a) Putting triggers on the tables in the subscriber DB to update a "last updated" table on each operation.
b) Customising the Insert/Delete/Update SPs for each table to update a "last updated" table.
But I was hoping to be able to avoid either of those, simply to keep the complexity of the replication solution to a minimum. No amount of googling has come up with anything, but I could just be using the wrong search terms!
Does anyone have any clever ideas for how to get this information?
Thanks in advance
March 5, 2013 at 10:17 am
Is this a fairly "busy" OLTP situation or is there little activity throughout the day? If the publisher is a busy server I wouldn't recommend using triggers or they will fire all-the-time and you may find it severely degrades performance of your subscriber(s).
In a typical replication topology, data is being replicated as the need exists. In the event there's latency is either exists between publisher > distributor or in the distributor > subscriber. If you're not able to query the publisher, may I ask if you are able to query against the distributor? If so you can use the query below to determine the "DelivCmdsInDistDB" are greater than zero.
This will tell you that there is replicated data waiting to be applied to your subscriber(s). If you don't see your table in the list, it's current. You can also change the WHERE clause to pull against "status" - e.g. WHERE status NOT IN (2,4) << 'Succeeded' or 'Idle'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
a.publisher_db,
a.article,
ds.article_id,
ds.UndelivCmdsInDistDB,
ds.DelivCmdsInDistDB,
CASE WHEN md.[status] = 1 THEN 'Started'
WHEN md.[status] = 2 THEN 'Succeeded'
WHEN md.[status] = 3 THEN 'In Progress'
WHEN md.[status] = 4 THEN 'Idle'
WHEN md.[status] = 5 THEN 'Retrying'
WHEN md.[status] = 6 THEN 'Failed'
ELSE 'Other'
END [Status],
CASE WHEN md.warning = 0 THEN 'OK'
WHEN md.warning = 1 THEN 'Expired'
WHEN md.warning = 2 THEN 'Latency'
ELSE 'OTHER'
END [Warnings],
md.cur_latency / 60.0 / 60.0 [Latency (min.)]
FROM [Distributor].Distribution.dbo.MSdistribution_status ds
INNER JOIN [Distributor].Distribution.dbo.MSarticles a
ON a.article_id = ds.article_id
INNER JOIN [Distributor].Distribution.dbo.MSreplication_monitordata md
ON md.agent_id = ds.agent_id
WHERE
UndelivCmdsInDistDB >= 0
AND a.publisher_db = 'YourPublishedDatabase'
Hope this helps!
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply