February 28, 2011 at 2:15 pm
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?
February 28, 2011 at 2:21 pm
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.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 2, 2011 at 10:33 am
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".
March 2, 2011 at 11:16 am
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?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 2, 2011 at 11:21 am
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"
...
March 2, 2011 at 11:29 am
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.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 2, 2011 at 12:44 pm
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???
March 2, 2011 at 2:37 pm
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"
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 3, 2011 at 2:36 pm
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?
March 3, 2011 at 3:31 pm
fannonland2 (3/3/2011)
ok, changed that line back toiWaitInMinutes = 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... 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 31, 2011 at 12:43 am
Great article - very helpful!!!
September 1, 2011 at 8:02 am
JKG (8/31/2011)
Great article - very helpful!!!
Thank you 🙂
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
January 21, 2013 at 9:43 pm
Hi..
We are using SCOM 2007 to monitor SQL Server 2008 instances. I have checked the blocked sessions alert details in SCOM console as well as in OperationsManager database. I was not able to find the head_blocker (or lead blocker spid) details when more than 2 processes are involved in blocking.
Eg: 1. SPID 87 is blocked SPID 85 (Details available, lead blocker is 85)
2. SPID 87 is blocked by SPID 90
SPID 90 is blocked by SPID 85 (Details available for SPID 87 and 90, here SPID 85 is blocker. There are no details available like hostname, logintime, cmd etc for SPID 85)
I am not sure if this is the limitation in this product or something we should configure to capture the information when there are more than 2 spids involved in blocking.
February 6, 2013 at 6:37 pm
krn045 (1/21/2013)
Hi..We are using SCOM 2007 to monitor SQL Server 2008 instances. I have checked the blocked sessions alert details in SCOM console as well as in OperationsManager database. I was not able to find the head_blocker (or lead blocker spid) details when more than 2 processes are involved in blocking.
Eg: 1. SPID 87 is blocked SPID 85 (Details available, lead blocker is 85)
2. SPID 87 is blocked by SPID 90
SPID 90 is blocked by SPID 85 (Details available for SPID 87 and 90, here SPID 85 is blocker. There are no details available like hostname, logintime, cmd etc for SPID 85)
I am not sure if this is the limitation in this product or something we should configure to capture the information when there are more than 2 spids involved in blocking.
Sometimes the blocking session is not doing anything, and there is therefore not much info on it (that info would be coming from sys.dm_exec_requests).
A good example of this would be a user-initiated session in which the user has opened a web page, and that has started a transaction that won't commit until the page is exited (code defect). In that scenario a SQL statement in the transaction could be blocking others indefinitely, even though there is no work being done.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 14 posts - 61 through 73 (of 73 total)
You must be logged in to reply to this topic. Login to reply