SQL Remote Passthrough of Alter Publication Failing

[JimDiaz]

I have an ASA 12.01 Consolidated and a mix of ASA 9.02 and 12.01 remotes. We recently added several tables to the remotes via a passthrough statement. We then added them to the publication via another passthrough statement (provided below).

Before performing this on the live system we tested several times on our test environment to both 9.0.2 and 12.0.1 remotes.

Sunday we passed this through to the live system and 7 out of 10, version 12.01 remotes have failed in that dbremote hangs for hours on the first ALTER PUBLICATION statement. All 9.0.2 remotes applied without error.

I am able to connect to some of these 12.0.1 remotes via RDP, I ran the ALTER PUBLICATION statements manually on one of these remotes without error. I also checked connections and locks to see if I could figure out why these remotes are failing, nothing obvious.

To fix this I intend to calculate the offsets and apply a sa_setremoteuser to both the consolidated and remotes to force replication to skip this message.

PASSTHROUGH FOR SUBSCRIPTION TO xxx.name;
SETUSER DBA;
ALTER PUBLICATION xxx.name ADD TABLE LMS.Majors;
ALTER PUBLICATION xxx.name ADD TABLE LMS.Certificates;
ALTER PUBLICATION xxx.name ADD TABLE LMS.CertificatesFieldNames;
ALTER PUBLICATION xxx.name ADD TABLE LMS.CertificatesDataEntryFields;
ALTER PUBLICATION xxx.name ADD TABLE LMS.Courses;
ALTER PUBLICATION xxx.name ADD TABLE LMS.MajorsCourses;
ALTER PUBLICATION xxx.name ADD TABLE LMS.Lessons;
ALTER PUBLICATION xxx.name ADD TABLE LMS.UsersCourseStatus;
ALTER PUBLICATION xxx.name ADD TABLE LMS.UsersLessonStatus;
SETUSER;
COMMIT WORK;
PASSTHROUGH STOP;

[JimDiaz]

My fix worked however the very next ALTER PUBLICATION statement gave the same error. The 12.0.1 build is 3750. I have RDP into the successful 12.0.1 remotes and they are using the same version.

[VolkerBarth]

Wild guess: Are the tables used in the PUBLICATION locked during the SQL Remote run?

As to this FAQ, v12 has introduced a behaviour change w.r.t. ALTER PUBLICATION... to cite Reg:

In v12, you now need exclusive access to all the tables in the publication, as well as all the tables referenced in the command when executing an ALTER PUBLICATION command.

...whereas in older versions, only exclusive access on the referenced tables was needed (still that way documented in the v12 docs):

Requires exclusive access to all tables referred to in the statement.

Note: That might explain the problem (and if so, should be re-producable in your staging system) but does not tell on the remedy:(

[JimDiaz]

Very interesting thanks for the information. The only active connection was dbremote. I'll look deeper.