Disaster Recovery for Bi-Directional Transactional Replication

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
>

Disabling replication problems

Hello,
When I try to remove/disable replication on a particular
server nothing happens. The message box 'Removing
publishing and distribution components ...' comes up and
just hangs from there on. Does anyone know why this is
happening?
Any help would be greatly appreciated!
Thanks in advance
watch what is going on while you try to disable replication using Profiler.
By chance do you have a remote distributor/publisher?
Do you get any error messages?
Try running sp_who2 while you are disabling replication to see if there is
any locking occurring.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Mike S." <anonymous@.discussions.microsoft.com> wrote in message
news:26c3101c46371$95c70940$a601280a@.phx.gbl...
> Hello,
> When I try to remove/disable replication on a particular
> server nothing happens. The message box 'Removing
> publishing and distribution components ...' comes up and
> just hangs from there on. Does anyone know why this is
> happening?
> Any help would be greatly appreciated!
> Thanks in advance
|||Hilary,
Can't wait to see this book hit the shelf. You got one buyer here.
"Hilary Cotter" wrote:

> watch what is going on while you try to disable replication using Profiler.
> By chance do you have a remote distributor/publisher?
> Do you get any error messages?
> Try running sp_who2 while you are disabling replication to see if there is
> any locking occurring.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Mike S." <anonymous@.discussions.microsoft.com> wrote in message
> news:26c3101c46371$95c70940$a601280a@.phx.gbl...
>
>

Disabling merge distribution change my table definitions at publisher

I have merge replication between sql 2005 SP1, and when I disable distribution from my publisher, my tables with identity columns change the property "Not for replication" to "NO", does somebody knows if this is a known problem ?

Other information : the compatibilty level for database is 2000.

Do you see a problem with that?

Typically when replication is involved, Not For Replication is needed because when the identity value is tried to insert on the other node, this constraint needs to be skipped. Now with no replication on the machine, it would not matter if this property is ON or OFF.

disable replication problem - sql server 2000

Hello,
I need to relocate the Distribution DB, and I was advised that I need to
stop/disable replication to do this. I looked up this article on disabling
replication:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog03.mspx
which says (near the bottom of the article) that I need to right click on
Replication and then to select Disable publishing and follow the wizard. I
could not find that selection. I right-clicked on Replication Monitor, right
clicked on Publisher, ... I did find in properties on dropping the
publication to stop replication.
How can I stop/disable replication temporarily so I can move the
distribution DB? Is it possisble to stop/disable replication without having
to drop anything? the publication...? How to accomplish this?
Thanks,
Rich
Hmmm, I am stressing too much about this. I saw the replication icon and the
disable selection. My stress is that I have to drop everything and it sounds
like I then have to recreate it all.
Is there a way to save the publication to a script so that I can run the
script to recreate the publication? Or do I have to recreate it from scratch?
"Rich" wrote:

> Hello,
> I need to relocate the Distribution DB, and I was advised that I need to
> stop/disable replication to do this. I looked up this article on disabling
> replication:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog03.mspx
> which says (near the bottom of the article) that I need to right click on
> Replication and then to select Disable publishing and follow the wizard. I
> could not find that selection. I right-clicked on Replication Monitor, right
> clicked on Publisher, ... I did find in properties on dropping the
> publication to stop replication.
> How can I stop/disable replication temporarily so I can move the
> distribution DB? Is it possisble to stop/disable replication without having
> to drop anything? the publication...? How to accomplish this?
> Thanks,
> Rich
|||Rich - just right-click the publication and get Enterprise Manager to create
the script. Also you can use SQLDMO to generate the script for a backup:
http://www.replicationanswers.com/Script7.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Thank you. I feel much better now .
"Paul Ibison" wrote:

> Rich - just right-click the publication and get Enterprise Manager to create
> the script. Also you can use SQLDMO to generate the script for a backup:
> http://www.replicationanswers.com/Script7.asp
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>

disable publishing and distribution error

Ok, which database are you creating sysmergepublications and
sysmergesubscriptions in ?
I've created sysmergesubscriptions in distribution , master , msdb ...
I still receive an error when I run
use master
exec sp_dropdistributor @.no_checks = 1
go
I receive :
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%233PUslE$GHA.896@.TK2MSFTNGP03.phx.gbl...
> create table sysmergepublications
> (
> publisher sysname,
> publisher_db sysname,
> name sysname,
> description nvarchar(510),
> retention int,
> publication_type tinyint,
> pubid uniqueidentifier,
> designmasterid uniqueidentifier,
> parentid uniqueidentifier,
> sync_mode tinyint,
> allow_push int,
> allow_pull int,
> allow_anonymous int,
> centralized_conflicts int,
> status tinyint,
> snapshot_ready tinyint,
> enabled_for_internet bit,
> dynamic_filters bit,
> snapshot_in_defaultfolder bit,
> alt_snapshot_folder nvarchar(510),
> pre_snapshot_script nvarchar(510),
> post_snapshot_script nvarchar(510),
> compress_snapshot bit,
> ftp_address sysname,
> ftp_port int,
> ftp_subdirectory nvarchar(510),
> ftp_login sysname,
> ftp_password nvarchar(1048),
> conflict_retention int,
> keep_before_values int,
> allow_subscription_copy bit,
> allow_synctoalternate bit,
> validate_subscriber_info nvarchar(1000),
> ad_guidname sysname,
> backward_comp_level int,
> max_concurrent_merge int,
> max_concurrent_dynamic_snapshots int,
> use_partition_groups smallint,
> dynamic_filters_function_list nvarchar(1000),
> partition_id_eval_proc sysname,
> publication_number smallint,
> replicate_ddl int,
> allow_subscriber_initiated_snapshot bit,
> distributor sysname,
> snapshot_jobid binary(16),
> allow_web_synchronization bit,
> web_synchronization_url nvarchar(1000),
> allow_partition_realignment bit,
> retention_period_unit tinyint,
> decentralized_conflicts int,
> generation_leveling_threshold int,
> automatic_reinitialization_policy bit
> )
> --
> 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
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:%23exvteE$GHA.4196@.TK2MSFTNGP03.phx.gbl...
>
Publication database. Check which databases are published for merge
replication and put it there.
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
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uyEq9AQBHHA.4672@.TK2MSFTNGP02.phx.gbl...
> Ok, which database are you creating sysmergepublications and
> sysmergesubscriptions in ?
> I've created sysmergesubscriptions in distribution , master , msdb ...
> I still receive an error when I run
> use master
> exec sp_dropdistributor @.no_checks = 1
> go
> I receive :
> Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
> Invalid object name 'dbo.sysmergesubscriptions'.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%233PUslE$GHA.896@.TK2MSFTNGP03.phx.gbl...
>
|||My database is no longer being published for replication because I used
exec sp_replicationdboption 'databasename','merge publish',false
Nonetheless, I added a dbo.sysmergesubscriptions table to this database and
ran
use master
exec sp_dropdistributor @.no_checks = 1
go
but I still receive the error :
Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
Invalid object name 'dbo.sysmergesubscriptions'.
Could it be because I need to add dbo.sysmergesubscriptions as a system
object ? How do I do this ?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eZifFJQBHHA.2304@.TK2MSFTNGP02.phx.gbl...
> Publication database. Check which databases are published for merge
> replication and put it there.
> --
> 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
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:uyEq9AQBHHA.4672@.TK2MSFTNGP02.phx.gbl...
>
|||these are the lines around 103
if not exists (select * from dbo.sysmergesubscriptions
where UPPER(subscriber_server) =
UPPER(publishingservername()) and db_name = db_name() and subid <> pubid)
begin
select @.ignore_merge_metadata = 1
end
It is complaining about the database you are running the command in. Is the
table there? Does it have the owner dbo?
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
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:ePkGbNQBHHA.1220@.TK2MSFTNGP04.phx.gbl...
> My database is no longer being published for replication because I used
> exec sp_replicationdboption 'databasename','merge publish',false
> Nonetheless, I added a dbo.sysmergesubscriptions table to this database
> and ran
> use master
> exec sp_dropdistributor @.no_checks = 1
> go
> but I still receive the error :
> Msg 208, Level 16, State 1, Procedure sp_MSmergepublishdb, Line 103
> Invalid object name 'dbo.sysmergesubscriptions'.
> Could it be because I need to add dbo.sysmergesubscriptions as a system
> object ? How do I do this ?
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eZifFJQBHHA.2304@.TK2MSFTNGP02.phx.gbl...
>

Disable publishing

We are using SQL 2K with sp4 and use push subcription. The publisher
database and the distribution database are on the same SQL server.
I need to disable publishing and distribution so I can recreate the
publication from scratch. I use SQL EM's Disable Publishing and Distribution
wizard and after I 'finish' the wizard, it proceed to do its thing and gives
me an error that 'distribution database' is in use. I click ok and the
wizard finished its stuff. I checked the publication and it is droped along
with subscrtiptions. Here are my questions.
1. To avoid the 'in use' error, am I supposed to do something like stop
some jobs or agents before I do that?
2, If the wizard didn't encounter any error, what is supposed to happen to
the distribution database?
Wingman
The distribution database will get removed when it works ok. When I have
seen this error, the publications are removed, and disabling publication for
the second time will remove the distribution database correctly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the info.
I ran the second time and it removed the distribution database as you
described. It clean out a lot of stuff.
I check the replication folder in EM and find a subscription in the
Subscriptions folder. Is it ok to leave it there before I redo the
publishing from scratch? Why do they want to leave a subscription there?
Wingman
"Paul Ibison" wrote:

> The distribution database will get removed when it works ok. When I have
> seen this error, the publications are removed, and disabling publication for
> the second time will remove the distribution database correctly.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Wingman,
You can have a look at sp_MSenumsubscriptions to see what is happening when
you click on the subscriptions folder. The key tables in each subscribing
databases are sysmergesubscriptions and MSreplication_subscriptions. If this
database is no longer a subscriber, then run sp_removedbreplication, which
removes the redundant system tables. If you want these tables to remain,
then simply locate the redundant record in the above system table and delete
it.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Disable parallelism for replication agents

Is there a way to disable parallelism for all the distribution agents
participating in trans replication using SQL 2KWhy do you want to do that? Is there a sepcific issue, if so, I'd be
interested in details.
Anyway, distribution agents by default apply commands to subscribers, using
stored procedures. You could edit these stored procedure and specify the
degree of parallelism. Search BOL for MAXDOP.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OeEoeuHkDHA.2488@.TK2MSFTNGP12.phx.gbl...
Is there a way to disable parallelism for all the distribution agents
participating in trans replication using SQL 2K|||If by parallelism you mean multiple distribution agents for different
publications on the same publishing server and database going to the same
subscriber, subscribing database - there is the independent_agent option
which by default is false.'
Does your distribution agent say multiple publications? If so you do not
have an independent agent.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OeEoeuHkDHA.2488@.TK2MSFTNGP12.phx.gbl...
> Is there a way to disable parallelism for all the distribution agents
> participating in trans replication using SQL 2K
>|||Well we do not use Stored Procedures to apply commands to subscribers...Our
sp_addarticle values for @.ins_cmd ,@.del_cmd and @.upd_cmd = SQL
Not too sure why it was implemented this way but it is. And in this case,
cant seem to use the MaxDop option
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:%23htMaGMkDHA.2964@.tk2msftngp13.phx.gbl...
> Why do you want to do that? Is there a sepcific issue, if so, I'd be
> interested in details.
> Anyway, distribution agents by default apply commands to subscribers,
using
> stored procedures. You could edit these stored procedure and specify the
> degree of parallelism. Search BOL for MAXDOP.
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> What hardware is your SQL Server running on?
> http://vyaskn.tripod.com/poll.htm
>
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OeEoeuHkDHA.2488@.TK2MSFTNGP12.phx.gbl...
> Is there a way to disable parallelism for all the distribution agents
> participating in trans replication using SQL 2K
>
>|||By parallelism, I mean parallel queries. I think my previous post "Spid
being blocked by itself + parallelism" gives you an example
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23ZKaDEOkDHA.3024@.tk2msftngp13.phx.gbl...
> If by parallelism you mean multiple distribution agents for different
> publications on the same publishing server and database going to the same
> subscriber, subscribing database - there is the independent_agent option
> which by default is false.'
> Does your distribution agent say multiple publications? If so you do not
> have an independent agent.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OeEoeuHkDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > Is there a way to disable parallelism for all the distribution agents
> > participating in trans replication using SQL 2K
> >
> >
>|||As Vyas was asking, I too would be interested in why you wanted to disable
parallelism in this case.
--
Linchi Shea
linchi_shea@.NOSPAMml.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23SDJjXOkDHA.964@.TK2MSFTNGP10.phx.gbl...
> Well we do not use Stored Procedures to apply commands to
subscribers...Our
> sp_addarticle values for @.ins_cmd ,@.del_cmd and @.upd_cmd = SQL
> Not too sure why it was implemented this way but it is. And in this case,
> cant seem to use the MaxDop option
>
> "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> news:%23htMaGMkDHA.2964@.tk2msftngp13.phx.gbl...
> > Why do you want to do that? Is there a sepcific issue, if so, I'd be
> > interested in details.
> >
> > Anyway, distribution agents by default apply commands to subscribers,
> using
> > stored procedures. You could edit these stored procedure and specify the
> > degree of parallelism. Search BOL for MAXDOP.
> > --
> > HTH,
> > Vyas, MVP (SQL Server)
> > http://vyaskn.tripod.com/
> > What hardware is your SQL Server running on?
> > http://vyaskn.tripod.com/poll.htm
> >
> >
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OeEoeuHkDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > Is there a way to disable parallelism for all the distribution agents
> > participating in trans replication using SQL 2K
> >
> >
> >
> >
>|||I think my previous post "Spid being blocked by itself + parallelism" gives
you an example
"Linchi Shea" <linchi_shea@.NOSPAMml.com> wrote in message
news:ev0F3ATkDHA.744@.tk2msftngp13.phx.gbl...
> As Vyas was asking, I too would be interested in why you wanted to disable
> parallelism in this case.
> --
> Linchi Shea
> linchi_shea@.NOSPAMml.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23SDJjXOkDHA.964@.TK2MSFTNGP10.phx.gbl...
> > Well we do not use Stored Procedures to apply commands to
> subscribers...Our
> > sp_addarticle values for @.ins_cmd ,@.del_cmd and @.upd_cmd = SQL
> > Not too sure why it was implemented this way but it is. And in this
case,
> > cant seem to use the MaxDop option
> >
> >
> > "Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
> > news:%23htMaGMkDHA.2964@.tk2msftngp13.phx.gbl...
> > > Why do you want to do that? Is there a sepcific issue, if so, I'd be
> > > interested in details.
> > >
> > > Anyway, distribution agents by default apply commands to subscribers,
> > using
> > > stored procedures. You could edit these stored procedure and specify
the
> > > degree of parallelism. Search BOL for MAXDOP.
> > > --
> > > HTH,
> > > Vyas, MVP (SQL Server)
> > > http://vyaskn.tripod.com/
> > > What hardware is your SQL Server running on?
> > > http://vyaskn.tripod.com/poll.htm
> > >
> > >
> > >
> > >
> > > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > > news:OeEoeuHkDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > > Is there a way to disable parallelism for all the distribution agents
> > > participating in trans replication using SQL 2K
> > >
> > >
> > >
> > >
> >
> >
>