BMCPatrol/BackupExec with MSDE and SQL Server Enterprise Edition

I have a odd situation and am unsure of my best option.
We use BMC Patrol to monitor an Enterprise Edition database. A server guy
came along and installed Backup Exec to make some one-off tape backups for a
vendor. He installed and decided to use MSDE. MSDE is now installed on my
server as well. I would never had known this except that BMC Patrol now has
no idea where my Enterprise Edition instance has gone to nor does it
recognize its existance.
BMC Patrol support bounced back the request for help by saying that MSDE has
"corrupted" registry settings and promoted itself somehow as the primary
instance in the registry.
Questions: Uninstalling MSDE did not resolve the situation.
- Is there such a registry setting?
- Is there a known, supported way to reverse this situation?
- Is my only alternative to re-install the SQL Server Enterprise Edition?
Thanks
hi Andy,
Andy Sprecher wrote:
> I have a odd situation and am unsure of my best option.
> We use BMC Patrol to monitor an Enterprise Edition database. A
> server guy came along and installed Backup Exec to make some one-off
> tape backups for a vendor. He installed and decided to use MSDE.
> MSDE is now installed on my server as well. I would never had known
> this except that BMC Patrol now has no idea where my Enterprise
> Edition instance has gone to nor does it recognize its existance.
> BMC Patrol support bounced back the request for help by saying that
> MSDE has "corrupted" registry settings and promoted itself somehow as
> the primary instance in the registry.
> Questions: Uninstalling MSDE did not resolve the situation.
> - Is there such a registry setting?
> - Is there a known, supported way to reverse this situation?
> - Is my only alternative to re-install the SQL Server Enterprise
> Edition?
I knew that installing MSDE sp3a on servers running SQL Server 2000 sp4 will
corrupt the registry in the
HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup
SQLPath key
that's to say that breaks accessing Query Analyzer and SQL Profiler from
Enterprise manager, but this is the only problem I'm aware of.. BTW there's
no concept of "primary instance", just default and named instance, but
installing MSDE on a previously installed SQL Server (default or named
instance) server does not involve the reported scenario... this has been
tested for sure :D
I do not know which registry key is BMC Patrol tool querying on, and this
can be related to the reported corruption, but I really have no further
hints... I think you have to open a case with Microsoft PSS..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea Montanari wrote:
> I do not know which registry key is BMC Patrol tool querying on, and
> this can be related to the reported corruption, but I really have no
> further hints... I think you have to open a case with Microsoft PSS..
just an additional note... before calling Microsoft PSS, I'd check that your
Enterprise instance runs smoothly and as expected, that no errors are raised
(scan your log and event viewer) and I'd contact again BMC for the registry
key they thin are messed up...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Blocking when using MS DTC

Hi,
I have a blocking situation occurring that I am having trouble sorting out. It occurs when using MS DTC and using SSIS as the transaction co-ordinator. I suppose you might argue that this should be in the SSIS forum but as the blocking is occurring in SQL Server I thought I'd try here as well.

I have 2 data-flows in SSIS. One of them INSERTs to mytable, the other UPDATEs mytable. The UPDATE is getting blocked by the INSERT which holds a lock on the table (I can see this thru sp_lock). Now although this is from SSIS all you really need to know is:

-These 2 operations occur using seperate connections
-I am running the 2 operations under the same MS DTC transaction
-mytable has a PK on it. If I remove the PK then my observations so far suggest that the blocking problem disappears
-The default isolation level on the DTC transaction is Serializable. I have tried various others and got the same problem
-The locks are held by SPID=-2 (which, as far as I am aware, is DTC)

Also note that:
-I can put the 2 operations in the same data-flow which means they occur under the same connection

Now, I'm not too hot on transactions and DTC etc... Is there anything I can do here to stop my blocking problem? Or am I just scuppered?

Feel free to ask questions and I'll answer qwith as much detail as I can. I'd appreciate any advice anyone has in resolving this.

-Jamie

P.S. Here's the thread I posted on the SSIS forum about this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=154834&SiteID=1

It seems as though page level locking is occurring. Does anyone have any advice as to how to go about eradicating this? I understand that row-level locking is possible now in SQL Server. In theory this would solve my problem because I am not updating records that I am inserting.

-Jamie

Blocking when using MS DTC

Hi,
I have a blocking situation occurring that I am having trouble sorting out. It occurs when using MS DTC and using SSIS as the transaction co-ordinator. I suppose you might argue that this should be in the SSIS forum but as the blocking is occurring in SQL Server I thought I'd try here as well.

I have 2 data-flows in SSIS. One of them INSERTs to mytable, the other UPDATEs mytable. The UPDATE is getting blocked by the INSERT which holds a lock on the table (I can see this thru sp_lock). Now although this is from SSIS all you really need to know is:

-These 2 operations occur using seperate connections
-I am running the 2 operations under the same MS DTC transaction
-mytable has a PK on it. If I remove the PK then my observations so far suggest that the blocking problem disappears
-The default isolation level on the DTC transaction is Serializable. I have tried various others and got the same problem
-The locks are held by SPID=-2 (which, as far as I am aware, is DTC)

Also note that:
-I can put the 2 operations in the same data-flow which means they occur under the same connection

Now, I'm not too hot on transactions and DTC etc... Is there anything I can do here to stop my blocking problem? Or am I just scuppered?

Feel free to ask questions and I'll answer qwith as much detail as I can. I'd appreciate any advice anyone has in resolving this.

-Jamie

P.S. Here's the thread I posted on the SSIS forum about this: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=154834&SiteID=1

It seems as though page level locking is occurring. Does anyone have any advice as to how to go about eradicating this? I understand that row-level locking is possible now in SQL Server. In theory this would solve my problem because I am not updating records that I am inserting.

-Jamie

Blocking 101

Pretty new to SQL Server and support multiple applications on SQL Server.
Just encountered a situation where "blocking" was occurring.
Can someone provide me more information or a link explaining Blocking and
how I can use SQL Server Enterprise Manager to determine who and what is
causing the blocking.
Any help is appreciated and I Thank You in advance for your help.
wnfisbaINF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Typically, it's caused by long running transactions that hold row, page or
table locks.
"wnfisba" <wnfisba@.discussions.microsoft.com> wrote in message
news:2DC94438-B2D4-4BEE-A781-AC16DE69A7B7@.microsoft.com...
> Pretty new to SQL Server and support multiple applications on SQL Server.
> Just encountered a situation where "blocking" was occurring.
> Can someone provide me more information or a link explaining Blocking and
> how I can use SQL Server Enterprise Manager to determine who and what is
> causing the blocking.
> Any help is appreciated and I Thank You in advance for your help.
> wnfisba