2005 SP3 via Windows Update breaks Merge Replication
Has anyone else noticed this problem we are starting to see it on machines now that are attempting to get SP3 for SQL Express 2005 via Windows Update.
The update failes to apply and the replmerg.exe agent can no longer connect to the distributor. Enterprise Manager can still connect to the distributor from the machine.
Attempting to remove SQL Express causes a faital error.
The only fix we have found so far is to remove SQL Express manually (the installer does not work anymore). Then reinstall and reinstate all of the databases (master, msdb, custom databases etc).
Once the reinstall is complete the agent can connect just fine.
It seems SP3 via Windows Update is some how corrupting something when it fails.
I also tried to apply SP3 manually on these machines and that fails too.
Martin
All Replies
- Hi Martin
IIRC - you need to remove the XML components from add remove programs, then apply the sp. The sp will reinstall the xml components.
looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941 - That didnt fix it unfortunately. Althought it did allow SP3 to install correctly but the SP3 manual version doesnt upgrade the core database services. It didnt add back the MSXML component either.
Martin - What does @@Version return.
Also can you send me your logs?
looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941 - We ended up doing a reinstall of SQL and restoring the databases. I will let you know next time it happens.
Martin - Well it happened again on another server. After KB969947 was installed. Its a Windows Security Update.
The information from Replmerge is
No connection could be made because the target machine actively refused it.
@@VERSION returns
Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) May 26 2009 14:24:20 Copyright (c) 1988-2005 Microsoft Corporation Express Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Connecting fine still using EM to the same machine and login details etc. - what does
sp_configure
'remote access'
return?
looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941 - remote access 0 1 1 1Actually I lied about the security updates its actually Office updates that were installed on the day it stopped working.KB973443KB975958KB973475I have opened a case with MS business support. Just awaiting the call back.
- There is also a DCOM error in the event log.DCOM was unable to communicate with the computer SQL3 using any of the configured protocols.Martin
- Got it solved with only 2 hours on the phone haha.
It seems that after certain Office updates are installed the SQL Native Client suddenly requires a port number even if SQL is running on the default port. OLE connection would connect to the server fine however Native client would fail until we updated the alias to have the default port 1434.
Whats weird is OLE is using the same alias record and connecting fine without the port number.
Martin
