Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678»»

Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts Expand / Collapse
Author
Message
Posted Monday, February 28, 2011 2:15 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 3, 2011 4:51 PM
Points: 5, Visits: 13
Just trying to understand what you are doing here with your scripts. I am trying to stop my IT department from buying Quest Tools or Foglight and hoping SCOM can do the job. We currently have 10 SQL servers ranging form 2000 to 2008 and each has multiple databases. Do I have to edit each script and create a new rule for every single server? Or can I just make one rule for 2000, 2005, 2008 and it will find all my SQL servers and run the alert?
Post #1070830
Posted Monday, February 28, 2011 2:21 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
fannonland2 (2/28/2011)
Just trying to understand what you are doing here with your scripts. I am trying to stop my IT department from buying Quest Tools or Foglight and hoping SCOM can do the job. We currently have 10 SQL servers ranging form 2000 to 2008 and each has multiple databases. Do I have to edit each script and create a new rule for every single server? Or can I just make one rule for 2000, 2005, 2008 and it will find all my SQL servers and run the alert?


Yes, in fact this is the power SCOM gives you. You create a rule for SQL 2000 and one for SQL 2005/8 (it should be able to accomodate both of these versions).
Then SCOM will propagate these scripts to all servers on which a SCOM agent has been deployed and that will be it.
The more servers you have in your environment, the larger the benefit of the centralized management SCOM provides.

Actually, to be precise, there are a couple of rules for each SQL version, if I remember correctly: one for detecting the condition and one for generating the alert.
But it is the same idea.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1070836
Posted Wednesday, March 2, 2011 10:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 3, 2011 4:51 PM
Points: 5, Visits: 13
Hmm, then I guess I am misreading your instructions. It says I need to run scripts scom blocking.sql. I do that on the scom server? And then I need to edit the rules to reflect settings in my environment. I am looking at my SCOM SQL database and I don't see a database that says "Analysis".
Post #1072065
Posted Wednesday, March 2, 2011 11:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
fannonland2 (3/2/2011)
Hmm, then I guess I am misreading your instructions. It says I need to run scripts scom blocking.sql. I do that on the scom server? And then I need to edit the rules to reflect settings in my environment. I am looking at my SCOM SQL database and I don't see a database that says "Analysis".


Yes, sorry the instructions on ReadMe.txt file are not very clear.

The Analysis db could be any centralized database in your environment that is collecting historical blocking information.
It is not the SCOM database, but a database created by you to collect the custom info generated by the rules here.

If you already have such a db in your environment for collecting other such info, you can use the same db for this implementation.
You may need to edit the name of the database from "Analysis" to whatever the name of your db is in a few places though.

Does that make sense?


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1072089
Posted Wednesday, March 2, 2011 11:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 3, 2011 4:51 PM
Points: 5, Visits: 13
ok, so it sounds like I need to go into SQL management Studio, create a new database called "Analysis" and then run the blocking tables.sql against that new database. Does that sound right?

And then when I need to customize it it will be...

...
sAnalysisInstance = "scomserver\scomsql"
sAnalysisDB = "analysis"
...
...
sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"
...
Post #1072091
Posted Wednesday, March 2, 2011 11:29 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
fannonland2 (3/2/2011)
ok, so it sounds like I need to go into SQL management Studio, create a new database called "Analysis" and then run the blocking tables.sql against that new database. Does that sound right?

And then when I need to customize it it will be...

...
sAnalysisInstance = "scomserver\scomsql"
sAnalysisDB = "analysis"
...
...
sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"
...


Yes, that sounds right. Basically, you need to specify a place where you will be storing all the info collected by the custom rules.


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1072096
Posted Wednesday, March 2, 2011 12:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 3, 2011 4:51 PM
Points: 5, Visits: 13
I appreciate you taking the time to talk me through this. I am not a DBA by any stretch but I am trying to convince the DBA's that scom can do this and obviously they aren't interested in helping me. So, I do this on my own. So I have gotten to the point where I created a new database called, "Analysis" on the scom server. This sits right alongside the OperationsmangerDW and ReportServer databases. I ran the scripts, it created tables, I copied and pasted the PRC files and ran those. Had a little problem because I hadn't created a database role called "ROLE_UID". So I did and made it a dbowner. Now, I am going back to change the scripts in SCOM.

I get this...

sAnalysisInstance = "SCOMP001\SCOMSQL"
sAnalysisDB = "Analysis"

But I don't get this....

sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"

What do I need to change in this line???
Post #1072144
Posted Wednesday, March 2, 2011 2:37 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
fannonland2 (3/2/2011)
I appreciate you taking the time to talk me through this. I am not a DBA by any stretch but I am trying to convince the DBA's that scom can do this and obviously they aren't interested in helping me. So, I do this on my own. So I have gotten to the point where I created a new database called, "Analysis" on the scom server. This sits right alongside the OperationsmangerDW and ReportServer databases. I ran the scripts, it created tables, I copied and pasted the PRC files and ran those. Had a little problem because I hadn't created a database role called "ROLE_UID". So I did and made it a dbowner. Now, I am going back to change the scripts in SCOM.

I get this...

sAnalysisInstance = "SCOMP001\SCOMSQL"
sAnalysisDB = "Analysis"

But I don't get this....

sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"

What do I need to change in this line???


sAnalysisInstance and sAnalysisDB take their values from the variable definitions so you don't have to worry about changing those.

UID: this is a server login and database user on Analysis db that has execute permissions on stored procedure SCOM_InsertBlockingInfo.
This procedure is installed on Analysis db.
See below code excerpt to get an idea of what I am talking about:


sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=xxx;PWD=xxx"

cnAnalysisInstance.Open sConnString

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnAnalysisInstance
cmd.CommandText = "[dbo].[SCOM_InsertBlockingInfo]"
cmd.CommandType = 4 'adCmdStoredProc


In my case I have a login, ssrs, mapped to a database role, ROLE_ssrs, in the Analysis db.

ROLE_ssrs has EXECUTE permissions on procedures SCOM_InsertBlockingInfo and SCOM_InsertBlockingInfo_2000.

Then the line you were asking about could read like this:

sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=ssrs;PWD=insert-complex-passwd"



__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation
Post #1072214
Posted Thursday, March 3, 2011 2:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 3, 2011 4:51 PM
Points: 5, Visits: 13
ok, changed that line back to

iWaitInMinutes = 1
iAlertFreq = 1
sAnalysisInstance = "server\instance"
sAnalysisDB = "dbName"

and the other line is now....

sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=SCOMADMIN;PWD=mysupercomplexpassword"


But I have yet to receive a single notification.

I made sure I created a new subscription based on Sql Servers, too. Anything I can check on the SQL side to see if it is even collecting anything yet?

Post #1072943
Posted Thursday, March 3, 2011 3:31 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Saturday, August 16, 2014 5:48 AM
Points: 1,862, Visits: 3,602
fannonland2 (3/3/2011)
ok, changed that line back to

iWaitInMinutes = 1
iAlertFreq = 1
sAnalysisInstance = "server\instance"
sAnalysisDB = "dbName"

and the other line is now....

sConnString = "Server=" & sAnalysisInstance & ";Database=" & sAnalysisDB & ";UID=SCOMADMIN;PWD=mysupercomplexpassword"


But I have yet to receive a single notification.

I made sure I created a new subscription based on Sql Servers, too. Anything I can check on the SQL side to see if it is even collecting anything yet?



Try simulating blocking by running the following in SSMS:

In 1st window; run this in a test database of your choice with a table of your choice:

USE dbName;
set nocount on
begin tran
while 1=1
update tblName set test ='abcd' --
--rollback tran

In a 2nd window of SSMS:

--2nd window:
USE dbName;
SELECT * FROM tblName

That will establish a blocking condition where the 2nd query is blocked indefinitely by the 1st.

Now on a 3rd SSMS window run the following.

Run the attached query after a minute, do you see any blocking? (this is the same query running in the .vbs script for SQL2005/2008 servers).

BTW, I hope you imported the custom management pack into SCOM...


__________________________________________________________________________________

Turbocharge Your Database Maintenance With Service Broker: Part 2
Turbocharge Your Database Maintenance With Service Broker: Part 1
Real-Time Tracking of Tempdb Utilization Through Reporting Services
Monitoring Database Blocking Through SCOM 2007 Custom Rules and Alerts
Preparing for the Unthinkable - a Disaster/Recovery Implementation


  Post Attachments 
BlockingSPIDs_2005.sql.txt (12 views, 1.66 KB)
Post #1072980
« Prev Topic | Next Topic »

Add to briefcase «««45678»»

Permissions Expand / Collapse