Rejoining two broken replicating servers

We had a two-site system go badly wrong as a result of something. The net
result of this was that the publisher had to be completely reformatted and
reinstalled. The server has, as a result of this, been given a different
name (went from being a named instance to not being one).
Currently the publisher doesn't know it's a replicating system.
Is there any way to join these two together so that they just start
from where they left off, or will I have to re-do all the replication
from scratch by various SELECT INTOs to get the data across, then
pushing out fresh subscriptions?
Thanks,
Jim
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.
Jim,
I'm hoping you have you got a copy of your replication scripts? If so, I'd
alter the server name in the scripts and then set things up from scratch by
initializing the subscribers. There's no need to do select intos - the
odbcbcp will take care of the initial data flow.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||In article <OZP8o$qvFHA.1648@.TK2MSFTNGP10.phx.gbl>, Paul Ibison wrote:
> Jim,
> I'm hoping you have you got a copy of your replication scripts? If so, I'd
> alter the server name in the scripts and then set things up from scratch by
> initializing the subscribers. There's no need to do select intos - the
> odbcbcp will take care of the initial data flow.
> Cheers,
Forgive me, but which scripts are these?
I'm a bit new at this sort of low-level tinkering :-)
Jim
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.
|||Jim,
I make regular backups of my replication setup - (see
http://www.replicationanswers.com/Script7.asp). If you don't have these,
you'll might still try running my script to see if you can generate them on
your renamed database - bit of a longshot but worth a try. Next thing to try
is to restore the database to it's original name and run hte script.
Finally, restore this database, msdb and the distribution database then run
the script. I can't test this right now, but please let me know how it goes.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Rejoining replicating servers

We had a two-site system go badly wrong as a result of something. The net
result of this was that the publisher had to be completely reformatted and
reinstalled. The server has, as a result of this, been given a different
name (went from being a named instance to not being one).

Currently the publisher doesn't know it's a replicating system.

Is there any way to join these two together so that they just start
from where they left off, or will I have to re-do all the replication
from scratch by various SELECT INTOs to get the data across, then
pushing out fresh subscriptions?

Thanks,

Jim
--
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.You might get a better response in
microsoft.public.sqlserver.replication.

Simon|||In article <1127313805.460090.79830@.g14g2000cwa.googlegroups.c om>, Simon Hayes wrote:
> You might get a better response in
> microsoft.public.sqlserver.replication.

Thanks.

Jim
--
Find me at http://www.ursaMinorBeta.co.uk
JediGeeks http://www.jedigeeks.com
"There's no 'I' in team, but there is a 'me' if you jumble
the letters up a bit." - Dr. House.

reinitializing subscriptions

We had some data become corrupt on our publisher and have to do a database
restore. Am I correct in thinking that after the restore I can just click
'reinitialize all subscriptions" and the snapshot and transactional
replication will clear out the old data and fill with the new data again at
the subscriber?
Partially. The reinitialze all subscriptions will mark the snapshots as
obsolete and the next time your snapshot agent runs the snapshot will be
generated and distributed to the subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"AW" <AW@.discussions.microsoft.com> wrote in message
news:6AA6434B-D32D-46DD-BCA2-8D22E0E7C0EA@.microsoft.com...
> We had some data become corrupt on our publisher and have to do a database
> restore. Am I correct in thinking that after the restore I can just click
> 'reinitialize all subscriptions" and the snapshot and transactional
> replication will clear out the old data and fill with the new data again
> at
> the subscriber?
|||On 2004-12-03, Hilary Cotter <hilary.cotter@.gmail.com> wrote:
> Partially. The reinitialze all subscriptions will mark the snapshots as
> obsolete and the next time your snapshot agent runs the snapshot will be
> generated and distributed to the subscribers.
Does that mean, for merge replication, that I'll have all the data on the
publisher resent to the subscriber? DROP table on the subscriber, recreate
it, and then apply the initial snapshot? Or that happens only when first
setting up the replication?
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr

ReIndexing while using replication Important

Does anyone know of a good set of guidelines to follow, after running DBCC
Showcontig on a publisher of replication and DBCC reindexing is called for?
What are the pitfalls and things to watch out for? I can't afford to make a
mistake since there are three servers involved and it's in production.
I can do the reindexing during night time hours. I've read a lot of material
but haven't found "replication considerations" and have not found anything
other than generalities when explaining how do analyze the Index fragmenting
statistics.
Matthew Mark
For merge replication it helps to reindex or defragment the msmerge tables.
You need to stop the merge agents which you are doing this. Ideally you
would stop all users while you are doing the reindexing.
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
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:15C56BD5-DED8-41D2-9C16-9C87B664751C@.microsoft.com...
> Does anyone know of a good set of guidelines to follow, after running DBCC
> Showcontig on a publisher of replication and DBCC reindexing is called
> for?
> What are the pitfalls and things to watch out for? I can't afford to make
> a
> mistake since there are three servers involved and it's in production.
> I can do the reindexing during night time hours. I've read a lot of
> material
> but haven't found "replication considerations" and have not found anything
> other than generalities when explaining how do analyze the Index
> fragmenting
> statistics.
> --
> Matthew Mark
>
|||Here's what I understand from this.
Since I couldn't find a MSMerge table I assume you mean to reindex the
Article (tables) in MSarticles from the publisher.
If so, how does this fall into the procedure. For example, based on what I
know now, here's how I would do this.
1. Turn all merge agents off.
2. Put DB in restricted mode.
3. Run DBCC reindex, looping through all tables in this database.
4. Put dB back in Full access mode.
5. Turn merge agents back on.
So, how does your suggesting fit in or modify this plan?
Does the reindexing naturally replicate to the subscribers? or ?
thanks
Matthew Mark
"Hilary Cotter" wrote:

> For merge replication it helps to reindex or defragment the msmerge tables.
> You need to stop the merge agents which you are doing this. Ideally you
> would stop all users while you are doing the reindexing.
> --
> 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
>
> "Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
> news:15C56BD5-DED8-41D2-9C16-9C87B664751C@.microsoft.com...
>
>
|||reindexing does not propagate to the subscribers, I meant that you should
reinindex msmerge_contents, and msmerge_tombstone.
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
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:2CB7AB6E-8D3B-48D8-A092-E55A8A25540A@.microsoft.com...[vbcol=seagreen]
> Here's what I understand from this.
> Since I couldn't find a MSMerge table I assume you mean to reindex the
> Article (tables) in MSarticles from the publisher.
> If so, how does this fall into the procedure. For example, based on what I
> know now, here's how I would do this.
> 1. Turn all merge agents off.
> 2. Put DB in restricted mode.
> 3. Run DBCC reindex, looping through all tables in this database.
> 4. Put dB back in Full access mode.
> 5. Turn merge agents back on.
> So, how does your suggesting fit in or modify this plan?
> Does the reindexing naturally replicate to the subscribers? or ?
>
> thanks
>
> --
> Matthew Mark
>
> "Hilary Cotter" wrote:
|||When I do reindex, and loop through the tables, doesn't it include these
tabels too?
Matthew Mark
"Hilary Cotter" wrote:

> reindexing does not propagate to the subscribers, I meant that you should
> reinindex msmerge_contents, and msmerge_tombstone.
> --
> 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
>
> "Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
> news:2CB7AB6E-8D3B-48D8-A092-E55A8A25540A@.microsoft.com...
>
>
|||Matthew,
this partly depends on what you are reindexing - system tables or user
tables. Generally speaking though, I ensure synchronization isn't occurring
while reindexing takes place, otherwise there tend to be job failures
because of blocking.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Paul,
I have never run reindexing on a replicated db. I have ,though, run a script
that runs the reindexing on all user tables. Are these MSarticles etc tables
system tables? I'd assume they are since the replication process creates it.
To avoid asking you a lot of specific questions, is there a comprehensive
guide I can read? The microsoft info online is usually good, only if you
already know the pitfalls. I don't.
If I make a mistake here because I didn't do my homework..., well, I'm sure
you understand. We don't replicate on our development server, so I can't
exactly practice ahead of time.
Matthew Mark
"Paul Ibison" wrote:

> Matthew,
> this partly depends on what you are reindexing - system tables or user
> tables. Generally speaking though, I ensure synchronization isn't occurring
> while reindexing takes place, otherwise there tend to be job failures
> because of blocking.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||I am unsure what you mean here. Can you clarify it? I normally issue the
following dbcc dbreindex('msmerge_contents')
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
"Matthew Mark" <MatthewMark@.discussions.microsoft.com> wrote in message
news:9038947F-DAA7-476C-92ED-729E905E77E3@.microsoft.com...[vbcol=seagreen]
> When I do reindex, and loop through the tables, doesn't it include these
> tabels too?
> --
> Matthew Mark
>
> "Hilary Cotter" wrote:
|||Matthew,
I don't know of any guide about reindexing and replication. However I'm not
too sure there's much to it. If you know the difference between DBCC
DBREINDEX and DBCC INDEXDEFRAG, understand locking and blocking and can see
what the agents require access to in order to work, these are the basic
facts. All I can sey is that a simple guideline is to run DBCC DBREINDEX not
overly often and when you do, try to ensure that synchronization isn't
occurring.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .