sql2k
I need to do replication of a between 2 buildings 30 miles apart with 1 T1.
I read about the nosyn method in
http://support.microsoft.com/default...b;en-us;320499
but the hangup Im having with this is putting the DB into Single User Mode.
I see the logic here, but this sucks. Any way to do nosync without using
SUM? A way to start replication using nosync but have it queued up until I
get the box and db at the subscriber up and running?
TIA, ChrisR
the short answer is no.
In such environments I have backed up the database, restored it on the
subscriber, fixed the schema (all constraints are made not for replication,
fixed the identity keys, dropped cascading updates and deletes, made
triggers not for replication, etc), and then done my no sync. I use the
continue on data consistency errors, and patched the subscriber to be in the
same as the publisher. Its a moving target and can be a real chore to fix.
You will need to run numerous validations.
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
"ChrisR" <noemail@.bla.com> wrote in message
news:uTQO799gFHA.1996@.TK2MSFTNGP10.phx.gbl...
> sql2k
> I need to do replication of a between 2 buildings 30 miles apart with 1
T1.
> I read about the nosyn method in
> http://support.microsoft.com/default...b;en-us;320499
> but the hangup Im having with this is putting the DB into Single User
Mode.
> I see the logic here, but this sucks. Any way to do nosync without using
> SUM? A way to start replication using nosync but have it queued up until I
> get the box and db at the subscriber up and running?
>
> TIA, ChrisR
>
|||I use the
> continue on data consistency errors, and patched the subscriber to be in
> the
> same as the publisher.
> You will need to run numerous validations.
What are these? I have no idea where they are.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ud7ZdNChFHA.1444@.TK2MSFTNGP10.phx.gbl...
> the short answer is no.
> In such environments I have backed up the database, restored it on the
> subscriber, fixed the schema (all constraints are made not for
> replication,
> fixed the identity keys, dropped cascading updates and deletes, made
> triggers not for replication, etc), and then done my no sync. I use the
> continue on data consistency errors, and patched the subscriber to be in
> the
> same as the publisher. Its a moving target and can be a real chore to fix.
> You will need to run numerous validations.
>
> --
> 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
> "ChrisR" <noemail@.bla.com> wrote in message
> news:uTQO799gFHA.1996@.TK2MSFTNGP10.phx.gbl...
> T1.
> Mode.
>
|||right click on your distribution agent, select agent profiles, and select
continue on data consistency errors.
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
"ChrisR" <noemail@.bla.com> wrote in message
news:OTLkZFJhFHA.1444@.TK2MSFTNGP10.phx.gbl...
> I use the
>
> What are these? I have no idea where they are.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:ud7ZdNChFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
|||Awesome! Where do I do a validation.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OxSQsPNhFHA.3692@.TK2MSFTNGP09.phx.gbl...
> right click on your distribution agent, select agent profiles, and select
> continue on data consistency errors.
> --
> 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
> "ChrisR" <noemail@.bla.com> wrote in message
> news:OTLkZFJhFHA.1444@.TK2MSFTNGP10.phx.gbl...
>
Showing posts with label sync. Show all posts
Showing posts with label sync. Show all posts
Monday, February 20, 2012
No sync subscriptions
I have a 70 GB merge-replicated production database on which I need to
perform some maintenance. This maintenance will require the removal of my
subscription. I am trying to minimize downtime, obviously, and wonder about
the ramifications, if any, of setting up a no-sync subscription after my
maintenance is complete. The subscription exists off-site, and I want to
avoid a new subscription push across the WAN. Also, I see a snapshot option
to "Keep the existing table unchanged." Would that be better than a no-sync?
In the past, we have always dropped and recreated objects during replication
setup.
Thanks!
Stephanie Williams
Stephanie,
the no-sync option is the way to go. "Keep the existing table unchanged."
is used for a central subscriber topology. The only thing to be wary of is
making sure the data stays synchronized ie can youprevent changes to the
publisher during the subscriber downtime?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
perform some maintenance. This maintenance will require the removal of my
subscription. I am trying to minimize downtime, obviously, and wonder about
the ramifications, if any, of setting up a no-sync subscription after my
maintenance is complete. The subscription exists off-site, and I want to
avoid a new subscription push across the WAN. Also, I see a snapshot option
to "Keep the existing table unchanged." Would that be better than a no-sync?
In the past, we have always dropped and recreated objects during replication
setup.
Thanks!
Stephanie Williams
Stephanie,
the no-sync option is the way to go. "Keep the existing table unchanged."
is used for a central subscriber topology. The only thing to be wary of is
making sure the data stays synchronized ie can youprevent changes to the
publisher during the subscriber downtime?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
no sync replication instructions.
Hello.
Does anyone have instructions on how to set back up replication that has
been marked for re-initilization thru the gui (enterprise manager)? We
have a subscriber that had their wan link down for the whole weekend and
now all their publications are marked for reinitialization. Some the
tables in question are 20gb in size, and that is just too big for us to
re-do snapshots and send over a WAN link. So in reading Hilary's and
Pauls post it looks like a "no-sync" will help me not have to send over
a snap shot.
1. how do i do this through enterprise manager only?
2. is there anyway for me to tell replication to go back lets say the
last 48hrs for the tables that we replicate, and "repush" that data
over?
Thanks,
-comb
Comb,
this article should help:
http://www.replicationanswers.com/No...alizations.asp
Initially I'd use Redgate's DataCompare to sync the data for any stray
changes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <eo9dqUZrFHA.1256@.TK2MSFTNGP09.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> this article should help:
> http://www.replicationanswers.com/No...alizations.asp
> Initially I'd use Redgate's DataCompare to sync the data for any stray
> changes.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Thanks Paul. I appreciate yours and hilary's help.
looking now. I am also going to look into this Redgates.
-comb
Does anyone have instructions on how to set back up replication that has
been marked for re-initilization thru the gui (enterprise manager)? We
have a subscriber that had their wan link down for the whole weekend and
now all their publications are marked for reinitialization. Some the
tables in question are 20gb in size, and that is just too big for us to
re-do snapshots and send over a WAN link. So in reading Hilary's and
Pauls post it looks like a "no-sync" will help me not have to send over
a snap shot.
1. how do i do this through enterprise manager only?
2. is there anyway for me to tell replication to go back lets say the
last 48hrs for the tables that we replicate, and "repush" that data
over?
Thanks,
-comb
Comb,
this article should help:
http://www.replicationanswers.com/No...alizations.asp
Initially I'd use Redgate's DataCompare to sync the data for any stray
changes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <eo9dqUZrFHA.1256@.TK2MSFTNGP09.phx.gbl>,
Paul.Ibison@.Pygmalion.Com says...
> Comb,
> this article should help:
> http://www.replicationanswers.com/No...alizations.asp
> Initially I'd use Redgate's DataCompare to sync the data for any stray
> changes.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Thanks Paul. I appreciate yours and hilary's help.
looking now. I am also going to look into this Redgates.
-comb
No sync - No Initialize & scripts
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
In SQL2000, you need to call sp_scriptpublicationcustomprocs to generate a script for creating the sp_MSins|del|upd procedures at the subscriber and then call sp_MSaddsynctrigger for each article at the subscriber to create the immediate updating triggers. The following kb has more details on this:
http://support.microsoft.com/default...b;en-us;320499
In SQL2005 (publisher), we create the replication system objects for you automatically if you use 'replication support only' or 'initialize with backup' as the @.sync_method in sp_addsubscription (SQL2005 Management Studio will use "replication support only" if you choose not to initialize data\schema)
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eTe%23PyJ5FHA.3276@.TK2MSFTNGP10.phx.gbl...
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
|||It is Sql 2000, Thanx for the link to the kb! You're a great help!!!
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:uSamV6J5FHA.2524@.TK2MSFTNGP10.phx.gbl...
In SQL2000, you need to call sp_scriptpublicationcustomprocs to generate a script for creating the sp_MSins|del|upd procedures at the subscriber and then call sp_MSaddsynctrigger for each article at the subscriber to create the immediate updating triggers. The following kb has more details on this:
http://support.microsoft.com/default...b;en-us;320499
In SQL2005 (publisher), we create the replication system objects for you automatically if you use 'replication support only' or 'initialize with backup' as the @.sync_method in sp_addsubscription (SQL2005 Management Studio will use "replication support only" if you choose not to initialize data\schema)
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eTe%23PyJ5FHA.3276@.TK2MSFTNGP10.phx.gbl...
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
In SQL2000, you need to call sp_scriptpublicationcustomprocs to generate a script for creating the sp_MSins|del|upd procedures at the subscriber and then call sp_MSaddsynctrigger for each article at the subscriber to create the immediate updating triggers. The following kb has more details on this:
http://support.microsoft.com/default...b;en-us;320499
In SQL2005 (publisher), we create the replication system objects for you automatically if you use 'replication support only' or 'initialize with backup' as the @.sync_method in sp_addsubscription (SQL2005 Management Studio will use "replication support only" if you choose not to initialize data\schema)
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eTe%23PyJ5FHA.3276@.TK2MSFTNGP10.phx.gbl...
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
|||It is Sql 2000, Thanx for the link to the kb! You're a great help!!!
"Raymond Mak [MSFT]" <rmak@.online.microsoft.com> wrote in message news:uSamV6J5FHA.2524@.TK2MSFTNGP10.phx.gbl...
In SQL2000, you need to call sp_scriptpublicationcustomprocs to generate a script for creating the sp_MSins|del|upd procedures at the subscriber and then call sp_MSaddsynctrigger for each article at the subscriber to create the immediate updating triggers. The following kb has more details on this:
http://support.microsoft.com/default...b;en-us;320499
In SQL2005 (publisher), we create the replication system objects for you automatically if you use 'replication support only' or 'initialize with backup' as the @.sync_method in sp_addsubscription (SQL2005 Management Studio will use "replication support only" if you choose not to initialize data\schema)
-Raymond
"JLS" <jlshoop@.hotmail.com> wrote in message news:eTe%23PyJ5FHA.3276@.TK2MSFTNGP10.phx.gbl...
If you restore the db you plan to replicate to another server, and then setup Trans. Repl. with immediate update, without doing a Snapshot Initialization (Taking the option that says data & schema are already there)
how do the scripts get created?
The scripts that will do the insert/update/delete (sp_msdel_tbl1, sp_msdel_tbl2, etc.)?
Thanx!
JLS
Subscribe to:
Posts (Atom)