cross server update problem

[dotnetfellow]

The system has worked for two years.

SQL Server 2000 runs on Windows 2000 Server "A".

Another instance of SQL Server 2000 was moved from Windows 2000 Server
"B" old to Windows 2003 Server "B" new, by restoring a backed
up copy from old to new.

The system has cross server updates, where Server "A" is updating
records in very large tables on Server "B".

After the move from Server "B" old to Server "B" new, suddenly
the cross server updates fail, but only when they are conducted within
a Begin and Commit Transaction batch.

The system works when I remove the Begin and Commit Transaction, and no
longer have them run in a batch.

Also, I had a cross server update, having "A" update records in
"B", from a delete trigger residing on "A" table. Deletes
against the "A" table fail so long as the trigger remains. Once I
remove the delete trigger from the "A" table, I can now delete
records on the "A" table.

Thanks. Dotnet Fellow

[Erland Sommarskog]

(do**********@yahoo.com) writes:

The system has worked for two years.

SQL Server 2000 runs on Windows 2000 Server "A".

Another instance of SQL Server 2000 was moved from Windows 2000 Server
"B" old to Windows 2003 Server "B" new, by restoring a backed
up copy from old to new.

The system has cross server updates, where Server "A" is updating
records in very large tables on Server "B".

After the move from Server "B" old to Server "B" new, suddenly
the cross server updates fail, but only when they are conducted within
a Begin and Commit Transaction batch.

The system works when I remove the Begin and Commit Transaction, and no
longer have them run in a batch.

Any error message you could share with us?

Since it works without a transaction, it sounds as if DTC is not running
on the new server B.

Also, I had a cross server update, having "A" update records in
"B", from a delete trigger residing on "A" table. Deletes
against the "A" table fail so long as the trigger remains. Once I
remove the delete trigger from the "A" table, I can now delete
records on the "A" table.

A trigger always operates in a transaction defined by the statement that
fired it, so this is consistent with the above.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[dotnetfellow]

DTC is running on both computers.

[dotnetfellow]

There is no error message returned. Just hangs for hours until a
timeout is encountered. Looking at it through Profiler Trace it
appears that no activity is occurring on either server once the
transaction is begun. Or, in the case of the delete triggers, it
appears that everything comes to a complete standstill once the delete
statement is executed.

[Erland Sommarskog]

(do**********@yahoo.com) writes:

There is no error message returned. Just hangs for hours until a
timeout is encountered. Looking at it through Profiler Trace it
appears that no activity is occurring on either server once the
transaction is begun. Or, in the case of the delete triggers, it
appears that everything comes to a complete standstill once the delete
statement is executed.

Let me guess: the source server runs Windows 2000, and the target server
runs Windows 2003? Where as the server you replaced ran Windows 2000?

No, I don't know anything. But I think I've seen this scenario at work. As
that has only been with development and test servers I have not dug into it.

But chance had it that one of our customer ran into precisely this
problem today. They have a brand-new server (which I assume runs Win 2003),
and in one place our gateway database that runs on another server (which
I suspect runs Win 2000) tries to use INSERT-EXEC to the linked server.
Worked find Friday. Today it just hung. SQL 2000 on both machines.

We will probably open a case with Microsoft on this. I suspect it's one
of these RTFM things, but I just don't know which manual. My gut feeling
is that this is a DTC problem. Maybe as simple, as opening the correct
port in Windows firewall. Whatever, I believe it's more a Windows problem
than an SQL Server problem per se.

If we open a case, and I hear about the resolution, I'll try to post
back.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[dotnetfellow]

All servers involved, old and new, have a default collation of
SQL_Latin1_General_CP1_CI_AS.

My Cross Server update that I execute on Server "A" is below, where

@p_str_source_workspace VARCHAR(50) = 'vit16.ais70.dbo.', and
@p_str_destination_workspace VARCHAR(50) = 'vit2.opus.dbo.'

Again, it doesn't work when I uncomment the BEGIN TRANSACTION

---------

/*
Update the source database, ensure that the archived flag is accurately
marked as 'N' if the record does not yet exist in Opus.
*/

SELECT @l_str_update = ' UPDATE ' + @p_str_source_workspace +
'pr_history SET archived = ''N'' FROM ' + @p_str_source_workspace +
'pr_history as pr0 WHERE archived = ''Y'' AND pr0.pr_hist_search NOT IN
(SELECT pr_hist_search FROM ' + @p_str_destination_workspace +
'pr_history)'

--BEGIN TRANSACTION

EXEC (@l_str_update)

---------

Thanks for keeping me in mind Erland.

[Erland Sommarskog]

(do**********@yahoo.com) writes:

All servers involved, old and new, have a default collation of
SQL_Latin1_General_CP1_CI_AS.

I would not expect collations to have anything to do with it. In fact, as I
said, my feeling is that it is not really an SQL Server issue, but a Windows
issue.

You did not confirm my guesses about the operating system. By the way,
when you replaced B, did you give the same IP address to the new B
server? There is another potential source for problem, some reference
to the old machine lingering around.

I spoke to my colleague who has been in touch with Microsoft. Apparently
they did not just say "did you check that...", but instead they asked tim
to send logs and similar information.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Here is an update:

In our case, it turned out that the machine from which we made the
connection is running SQL 2000 SP2 and Windows 2000 SP2. The Microsoft
engineer suggested that the SQL Server should be upgraded to SQL 2000 SP4
and Windows 2000 SP4. Whether this alone will resolve the issue, I don't
know yet. The simple reason the Support Professional wanted us to upgrade
is that they don't support neither SQL 2000 SP2 nor Win 2000 SP2.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

It seems that we now have a resolution. As I suspected it's a DTC issue.
Windows 2003 adds improved security which gets in the way.

On the server you are connecting to, open Component Services and select
properties for My Computer. Go to the MS DTC tab. To the lower left, there
is a button Security Configuration. This opens a new dialogue. In this
dialogue, enable 1) Network DTC Access. 2) Allow Inbound. 3) No
Authentication Required.

Really what the implication of these settings are from the security side
point of view, I don't know.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

This is some advice that we got from the support professional on our case:
Windows 2003 R2 has new DTC protection measures built-in and enabled
by default, since MSDTC is used for SQL Server purposes and this
machines don't have internet access there are no problem in set no
authentication required. In future if you upgrade your Windows 2000
machine to Windows 2003 SP1 or Windows 2003 R2 and they are in same
domain (this is a requirement) you can activate mutual authentication
in MSDTC and will not have any problem.
These steps are not documented but I let you some recommendations to
troubleshooting MSDTC problems.
1) Put security setting at the minimum level

2) Enable MSDTC logging and verify if anything appears on log file

3) Use DTCPing utility to avoid firewall problems

4) Verify if RPC is started before MSDTC and SQL Server is started
after MSDTC and RPC services.

5) Verify in SQL Server error log that MSDTC started with success

6) Please verify if you have COM/COM+ components enabled and with all
hot fixes installed

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx