Do you need to stop replication for a distributor upgrade?

Question

Do you need to stop replication for a distributor upgrade?

alen teplitsky

SSC-Dedicated

Points: 30014
More actions
December 14, 2006 at 2:59 pm

#171576

I just read this today in my 70-431 study book, but didn't see anything concrete in BOL. I think they meant you need to stop replication when upgrading a publisher.
Do you need to stop replication when upgrading a distributor?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Robert Davis One Orange Chip Points: 28027 More actions · Answer 1

What is the "this" that you read today?

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 2

little line about stopping replication for the install

anyway it went pretty bad. we ended going back to SQL 2000 on this distributor upgrade. I think what we are going to decide tomorrow is to set up a new box and transfer all the replication to it and then take the old one offline.

Robert Davis One Orange Chip Points: 28027 More actions · Answer 3

What were you upgrading it to? SQL 2005? Yes, you need to stop replication for any distributor upgrade.

What steps did you follow?

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

MohammedU SSCoach Points: 19644 More actions · Answer 4

http://msdn2.microsoft.com/en-us/library/ms143470.aspx

Trusted connections between a Publisher and a remote Distributor are no longer supported because they did not require a password (trusted connections were used by default in versions prior to SQL Server 2000 Service Pack 3). If you use a remote Distributor, before upgrading to SQL Server 2005, convert trusted connections to non-trusted connections (this issue does not affect Publishers that use a local Distributor). For more information about the distributor_admin account, see Securing the Distributor.

To determine the type of connection being used

Execute sp_helpdistpublisher (Transact-SQL) at the Distributor. If the value in the trusted column is 1, you must change to a non-trusted connection.

To change to a non-trusted connection

Execute sp_changedistpublisher (Transact-SQL) at the Distributor, specifying a value of 'trusted' for the parameter @property and a value of 'False' for the parameter @value.
Note:
Some versions of the SQL Server 2000 Books Online do not list 'trusted' as a valid value for @property. It is valid for all SQL Server 2000 releases.
Execute sp_changedistributor_password (Transact-SQL) at both the Publisher and the Distributor, specifying a strong password for the parameter @password.

MohammedU
Microsoft SQL Server MVP

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

someone else was doing it and not sure what went wrong. but we even ended up uninstalling sql2005 and reinstalling it from scratch. Replication still didn't work, but with our test distributor it worked perfectly with production servers

Robert Davis One Orange Chip Points: 28027 More actions · Answer 6

Are you only upgrading the distributor or are you upgraing the other servers as well?

There are some considerable differences with replication in 2005, and I would advocate dropping replication, upgrading, and then starting replication up from scratch.

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

MohammedU SSCoach Points: 19644 More actions · Answer 7

Always it is better to do side by side upgrade for CLUSTER and REPLICATED servers instead of inplace...

MohammedU
Microsoft SQL Server MVP

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 8

we upgraded another server in our environment, and that was a side by side. It went pretty smooth. For now things are on hold, but we will probably try to do some side by side upgrades in the future and talk about the other servers we can't replace like our cluster and a few others.

For the ditributor in question we ended up cutting our losses and reinstalling SQL 2000 SP4 and replication is working for now.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 9

for the curious we finally upgraded one of our distributors. ended up doing a move server to another machine. we tried a second in place and it failed. it really succeeded but there was a bug in SQL 2005 we didn't know about.

We kept getting 14151 errors and a pop up saying distrib.exe failed to initialize. We went to another machine with a fresh install and it seemed to work until we started creating a lot of jobs on it.

Turns out there is a piece of memory called the interactive desktop heap that we needed to increase by a registry change. it worked until we added more publications. then i changed all the jobs to run on a schedule rather than continous. it fixed it as well.

We have around 50 - 60 publications going through that distributor to 8 subscribers.