Checking the status of replicated transaction

I have an ETL process setup in my OLAP stagging area for a particular
reporting application. The stagging area consists of articles from several
publishers. I have a two step external process that runs, the first step
updates the status of detail items in each publication, the second step
synchronizes my transformation area from the changes in the replicated
transactional tables. My problem is how do I know that the distributor has
for each publication has posted all of the transaction to their respective
subscribers so when I run my synchronize transformation process I get ALL of
the updated data. This sounds like pretty common functionality, I'm just not
sure how to implement... Please help...?
Dan B
Dan,
you could have a 'master' job. This job has several steps - a step to run
each distribution/merge agent and a final step to run the transformation
process.
HTH,
Paul Ibison
|||Hi Paul,
Thanks for your reply... I'm implementing the concept of a master job
already, I don't know how to run the distribution agent remotely in T-SQL, I
was thinking that this agent is already running...? Maybe I'm getting
confused... I thought that you could setup the distribution agent to
immediately update subscribers or queue the updates, I think that ours is
configured to immediately update so I guess I just need to know if there are
any pending transactions on the publisher... Does that make any sense...?
Dan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23wdeDX$YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Dan,
> you could have a 'master' job. This job has several steps - a step to run
> each distribution/merge agent and a final step to run the transformation
> process.
> HTH,
> Paul Ibison
>
|||Dan,
to run the transformation task after all synchronizations have finished
you'll need to schedule the synchronizations on specific times rather than
continuously. To run the distribution agents, which is essentially a series
of jobs, you can run sp_start_job.
HTH,
Paul Ibison

Checking for replication when applying schema changes

All
Is there some simple way to check if a database is being replicated that I
can use in a script? I have 2 copies of a production database, one under
replication and one not, and I would like to have any schema change scripts
check for replication to see which action to take. E.g.
<script>
If <database is replicating>
sp_addreplcolumn ...
Else
Alter Table...
</script>
TIA
Ron Lounsbury
sp_dboption 'pubs','published'
GO
sp_dboption 'pubs','merge publish'
GO
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
> All
> Is there some simple way to check if a database is being replicated that I
> can use in a script? I have 2 copies of a production database, one under
> replication and one not, and I would like to have any schema change
> scripts check for replication to see which action to take. E.g.
> <script>
> If <database is replicating>
> sp_addreplcolumn ...
> Else
> Alter Table...
> </script>
> TIA
> Ron Lounsbury
>
|||Hilary
Thanks for the reply. Unfortunately, when I run that against a database
that is participating in replication and one that is not, both give me a
result for the "CurrentSetting" of OFF. This occurs for both "Merge
Publish" and "Publish". Is there something I am missing? I am running SQL
Server 2000, SP 3a (with some hotfixes).
Thanks,
Ron Lounsbury
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
> sp_dboption 'pubs','published'
> GO
> sp_dboption 'pubs','merge publish'
> GO
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:uY5tItmUGHA.4764@.TK2MSFTNGP11.phx.gbl...
>
|||If it returns off it means these databases are not enabled for replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
> Hilary
> Thanks for the reply. Unfortunately, when I run that against a database
> that is participating in replication and one that is not, both give me a
> result for the "CurrentSetting" of OFF. This occurs for both "Merge
> Publish" and "Publish". Is there something I am missing? I am running
> SQL Server 2000, SP 3a (with some hotfixes).
> Thanks,
> Ron Lounsbury
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:edTAbLnUGHA.4740@.TK2MSFTNGP14.phx.gbl...
>
|||Hilary
When I run this script in Query Analyzer:
USE NITSS2kDev
exec sp_dboption 'pubs','merge publish'
GO
use NITSS2kdeployed
exec sp_dboption 'pubs','merge publish'
GO
I get these results:
OptionName CurrentSetting
-- --
merge publish off
OptionName CurrentSetting
-- --
merge publish off
NITSS2kDev is actively replicating with 2 other databases, and one of the
replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
The script was run on the Publishing server.
Thanks,
Ron L
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
> If it returns off it means these databases are not enabled for
> replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Ron L" <ronl@.bogus.Address.com> wrote in message
> news:ODmoO2pUGHA.4452@.TK2MSFTNGP12.phx.gbl...
>
|||Oh Man, My Stupid. I don't know where my brain was yesterday. When I put
the right database name in the call it works fine.
Thanks,
Ron L
"Ron L" <ronl@.bogus.Address.com> wrote in message
news:OtXBmGqUGHA.5148@.TK2MSFTNGP12.phx.gbl...
> Hilary
> When I run this script in Query Analyzer:
> USE NITSS2kDev
> exec sp_dboption 'pubs','merge publish'
> GO
> use NITSS2kdeployed
> exec sp_dboption 'pubs','merge publish'
> GO
> I get these results:
> OptionName CurrentSetting
> -- --
> merge publish off
> OptionName CurrentSetting
> -- --
> merge publish off
> NITSS2kDev is actively replicating with 2 other databases, and one of the
> replication jobs ran 3 minutes ago. NITSS2kDeployed is not replicating.
> The script was run on the Publishing server.
> Thanks,
> Ron L
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eKM%23DAqUGHA.1444@.TK2MSFTNGP11.phx.gbl...
>

checkign replication percentage

Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs
If you are talking about the initial snapshot, then you can keep refreshing
the history of the distribution agent, as it shows the number of batches it
applied to the subscriber, along with the number of rows/batch. If know how
many rows there are on the publishing table, then the distribution agent
history gives you a pretty good idea of how many more rows to go.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:C637A6F1-5060-4247-8F63-FABA4FC5B33D@.microsoft.com...
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs

checkign replication percentage

Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgsIf you are talking about the initial snapshot, then you can keep refreshing
the history of the distribution agent, as it shows the number of batches it
applied to the subscriber, along with the number of rows/batch. If know how
many rows there are on the publishing table, then the distribution agent
history gives you a pretty good idea of how many more rows to go.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:C637A6F1-5060-4247-8F63-FABA4FC5B33D@.microsoft.com...
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs

checkign replication percentage

Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgsIf you are talking about the initial snapshot, then you can keep refreshing
the history of the distribution agent, as it shows the number of batches it
applied to the subscriber, along with the number of rows/batch. If know how
many rows there are on the publishing table, then the distribution agent
history gives you a pretty good idea of how many more rows to go.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:C637A6F1-5060-4247-8F63-FABA4FC5B33D@.microsoft.com...
Hi,
let's say if i need to replicate a huge database to another server ,
where/how could i check how much % of data has been replicated over
tks & rdgs