We have an OLTP, DSS, and separate Distribution machine. The OLTP serves as
a "HUB" for our 18 server bi-directional replication scenario, and the
replication machine regulates the flow of that "HUB" data. In the event that
our OLTP server becomes non-operational, is anyone familiar with how we could
point multiple remote publishers to the DSS machine without breaking down all
remote publications and subscriptions...we're afraid of the data loss
associated with dropping publications and subscriptions.
Thanks.
Wendy Sporleder
With un-directional replication you can have multiple publishers publishing
to the same subscriber.
If the main publisher went off line a standby publisher could go online and
start replicating to these same subscribers. The number of transactions lost
would be whatever had not made it to the standby server.
With bi-directional replication you would know that all the data is in the
subscriber, and then you would be faced with the same question as to what
data was still in the publisher which has yet to make it to the subscriber.
You would probably be best to do no-sync subscriptions and then validations
to ensure each node is consistent.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"wendy.sporleder@.carlson.com"
<wendy.sporleder@.carlson.com@.discussions.microsoft .com> wrote in message
news:FFE2AE1D-7436-402C-9698-140A26A5EFBA@.microsoft.com...
> We have an OLTP, DSS, and separate Distribution machine. The OLTP serves
as
> a "HUB" for our 18 server bi-directional replication scenario, and the
> replication machine regulates the flow of that "HUB" data. In the event
that
> our OLTP server becomes non-operational, is anyone familiar with how we
could
> point multiple remote publishers to the DSS machine without breaking down
all
> remote publications and subscriptions...we're afraid of the data loss
> associated with dropping publications and subscriptions.
> Thanks.
> Wendy Sporleder
>
2012年3月7日星期三
Disadvantage "trunc. log on chkpt."?
Hi NG
In a published db (transactional), anytime
the option "trunc. log on chkpt."( for fast BCP) is active.
I have set
<PublicationDatabaseName>', 'sync with backup', 'true'
<DisributionDatabaseName>', 'sync with backup', 'true'
for full recovery in desaster case.
Is it a problem?
BOL says, if sync with backup = true
then trunc. log on chkpt must be false!
How can I bypass this circumstance?
Stop Logreader and Distritask?
Regards,
Thomas
the same question
for option: 'select into/bulkcopy'
On Mon, 16 Aug 2004 09:16:47 GMT, tohas@.freenet.de (Thomas Hase)
wrote:
>Hi NG
>In a published db (transactional), anytime
>the option "trunc. log on chkpt."( for fast BCP) is active.
>I have set
><PublicationDatabaseName>', 'sync with backup', 'true'
><DisributionDatabaseName>', 'sync with backup', 'true'
>for full recovery in desaster case.
>Is it a problem?
>BOL says, if sync with backup = true
>then trunc. log on chkpt must be false!
>How can I bypass this circumstance?
>Stop Logreader and Distritask?
>Regards,
>Thomas
|||If you have to switch recovery models you are breaking the log chain. You
will have to change your recovery model back to full, backup your databases
(distribution and publication) and restore them on the subscriber with the
keep_replication switch.
Then start shipping the logs again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:41227e0d.67962859@.news.t-online.de...
> the same question
> for option: 'select into/bulkcopy'
>
> On Mon, 16 Aug 2004 09:16:47 GMT, tohas@.freenet.de (Thomas Hase)
> wrote:
>
In a published db (transactional), anytime
the option "trunc. log on chkpt."( for fast BCP) is active.
I have set
<PublicationDatabaseName>', 'sync with backup', 'true'
<DisributionDatabaseName>', 'sync with backup', 'true'
for full recovery in desaster case.
Is it a problem?
BOL says, if sync with backup = true
then trunc. log on chkpt must be false!
How can I bypass this circumstance?
Stop Logreader and Distritask?
Regards,
Thomas
the same question
for option: 'select into/bulkcopy'
On Mon, 16 Aug 2004 09:16:47 GMT, tohas@.freenet.de (Thomas Hase)
wrote:
>Hi NG
>In a published db (transactional), anytime
>the option "trunc. log on chkpt."( for fast BCP) is active.
>I have set
><PublicationDatabaseName>', 'sync with backup', 'true'
><DisributionDatabaseName>', 'sync with backup', 'true'
>for full recovery in desaster case.
>Is it a problem?
>BOL says, if sync with backup = true
>then trunc. log on chkpt must be false!
>How can I bypass this circumstance?
>Stop Logreader and Distritask?
>Regards,
>Thomas
|||If you have to switch recovery models you are breaking the log chain. You
will have to change your recovery model back to full, backup your databases
(distribution and publication) and restore them on the subscriber with the
keep_replication switch.
Then start shipping the logs again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:41227e0d.67962859@.news.t-online.de...
> the same question
> for option: 'select into/bulkcopy'
>
> On Mon, 16 Aug 2004 09:16:47 GMT, tohas@.freenet.de (Thomas Hase)
> wrote:
>
2012年2月24日星期五
Disable updates via triggers - how?
Hi,
We use push transactional replication successfully and are about to bring a
few remote on-sites into the fold. The connection speed will be low - in the
64k/128k range. As I understand it "Not for replication" means that a trigger
on the subcriber will not fire if the update is generated via a replication
process, i.e. all updates are pushed from the publisher. If this is correct
is there any way to reverse the logic, i.e. don't replicate updates on the
publisher that are caused by triggers and assume the subscriber will have the
appropriate triggers in place to do the update.
We are interested in doing this to reduce the amount of network bandwidth
consummed be replication (this will be critical when the remote sites come
on-line).
Can this be done?
Cheers, Peter
The not for replication switch means that replication related activity
will not cause the trigger, constraint, identity property to be enforced
if the triggering activity occurs by a replication process. This could
happen on the publisher or subscriber.
There is a way to bypass the replication process. What you do is add
filters to your tables which look like this
select * from published tables where 1=1.
A filter proc will be created which the log reader agent uses when
figuring out what commands are to be written to the distribution agent.
Change this filter to always return 0 when you want the commands to be
ignored. Change it back to 1 when you want the commands to be processed.
The problem is you can't alter procs via a trigger.
The way I handle things like this is to build the logic into the
replication proc that applies these transactions on the subscriber(s).
Hilary Cotter
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
|||Hi Hillary,
Thanks for the response.
Where do I go to add the filter: "select * from published tables where 1=1."?
When and where would I change the filter?
I do have a copy of your fine book - what sections are relevent to this
particular issue.
I presume the following isn't relevent to my circumstance as what I'm trying
to do reduce network traffice. Or I have I misunderstood?
> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
Cheers, Peter
"Hilary Cotter" wrote:
> The not for replication switch means that replication related activity
> will not cause the trigger, constraint, identity property to be enforced
> if the triggering activity occurs by a replication process. This could
> happen on the publisher or subscriber.
> There is a way to bypass the replication process. What you do is add
> filters to your tables which look like this
>
> A filter proc will be created which the log reader agent uses when
> figuring out what commands are to be written to the distribution agent.
> Change this filter to always return 0 when you want the commands to be
> ignored. Change it back to 1 when you want the commands to be processed.
> The problem is you can't alter procs via a trigger.
> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
> --
> Hilary Cotter
> 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
>
|||You are correct, making these change to the replication procs used to
synchronize your data will not minimize network traffic. Replicating the
execution of stored procedures will, if the majority of your transactions
occurring on the publisher affect more than one row.
Have a look at this post for an example of how to make the log reader agent
bypass transactions.
http://groups-beta.google.com/group/...c?dmode=source
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:87E55BFF-BCB2-4C20-B5F5-9C48B3B8064D@.microsoft.com...[vbcol=seagreen]
> Hi Hillary,
> Thanks for the response.
> Where do I go to add the filter: "select * from published tables where
> 1=1."?
> When and where would I change the filter?
> I do have a copy of your fine book - what sections are relevent to this
> particular issue.
> I presume the following isn't relevent to my circumstance as what I'm
> trying
> to do reduce network traffice. Or I have I misunderstood?
>
> Cheers, Peter
>
> "Hilary Cotter" wrote:
We use push transactional replication successfully and are about to bring a
few remote on-sites into the fold. The connection speed will be low - in the
64k/128k range. As I understand it "Not for replication" means that a trigger
on the subcriber will not fire if the update is generated via a replication
process, i.e. all updates are pushed from the publisher. If this is correct
is there any way to reverse the logic, i.e. don't replicate updates on the
publisher that are caused by triggers and assume the subscriber will have the
appropriate triggers in place to do the update.
We are interested in doing this to reduce the amount of network bandwidth
consummed be replication (this will be critical when the remote sites come
on-line).
Can this be done?
Cheers, Peter
The not for replication switch means that replication related activity
will not cause the trigger, constraint, identity property to be enforced
if the triggering activity occurs by a replication process. This could
happen on the publisher or subscriber.
There is a way to bypass the replication process. What you do is add
filters to your tables which look like this
select * from published tables where 1=1.
A filter proc will be created which the log reader agent uses when
figuring out what commands are to be written to the distribution agent.
Change this filter to always return 0 when you want the commands to be
ignored. Change it back to 1 when you want the commands to be processed.
The problem is you can't alter procs via a trigger.
The way I handle things like this is to build the logic into the
replication proc that applies these transactions on the subscriber(s).
Hilary Cotter
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
|||Hi Hillary,
Thanks for the response.
Where do I go to add the filter: "select * from published tables where 1=1."?
When and where would I change the filter?
I do have a copy of your fine book - what sections are relevent to this
particular issue.
I presume the following isn't relevent to my circumstance as what I'm trying
to do reduce network traffice. Or I have I misunderstood?
> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
Cheers, Peter
"Hilary Cotter" wrote:
> The not for replication switch means that replication related activity
> will not cause the trigger, constraint, identity property to be enforced
> if the triggering activity occurs by a replication process. This could
> happen on the publisher or subscriber.
> There is a way to bypass the replication process. What you do is add
> filters to your tables which look like this
>
> A filter proc will be created which the log reader agent uses when
> figuring out what commands are to be written to the distribution agent.
> Change this filter to always return 0 when you want the commands to be
> ignored. Change it back to 1 when you want the commands to be processed.
> The problem is you can't alter procs via a trigger.
> The way I handle things like this is to build the logic into the
> replication proc that applies these transactions on the subscriber(s).
> --
> Hilary Cotter
> 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
>
|||You are correct, making these change to the replication procs used to
synchronize your data will not minimize network traffic. Replicating the
execution of stored procedures will, if the majority of your transactions
occurring on the publisher affect more than one row.
Have a look at this post for an example of how to make the log reader agent
bypass transactions.
http://groups-beta.google.com/group/...c?dmode=source
"Peter Jones" <PeterJones@.discussions.microsoft.com> wrote in message
news:87E55BFF-BCB2-4C20-B5F5-9C48B3B8064D@.microsoft.com...[vbcol=seagreen]
> Hi Hillary,
> Thanks for the response.
> Where do I go to add the filter: "select * from published tables where
> 1=1."?
> When and where would I change the filter?
> I do have a copy of your fine book - what sections are relevent to this
> particular issue.
> I presume the following isn't relevent to my circumstance as what I'm
> trying
> to do reduce network traffice. Or I have I misunderstood?
>
> Cheers, Peter
>
> "Hilary Cotter" wrote:
订阅：
博文 (Atom)