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;

asked 05 Dec '12, 12:32

J%20Diaz's gravatar image

J Diaz
830253144
accept rate: 14%

edited 06 Dec '12, 05:09

Reimer%20Pods's gravatar image

Reimer Pods
4.2k334583

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.

(05 Dec '12, 16:54) J Diaz

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:(

permanent link

answered 06 Dec '12, 07:58

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

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

(06 Dec '12, 09:25) J Diaz
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×78
×44
×39

question asked: 05 Dec '12, 12:32

question was seen: 1,683 times

last updated: 06 Dec '12, 09:25