Software to monitor databases by using query on multiple servers

Question

Software to monitor databases by using query on multiple servers

michael-753984

SSC Journeyman

Points: 77
More actions
April 15, 2011 at 2:00 am

#249455

Hi everyone.
I'm in a bad position, that our developers are making lots of software, which when they they log to a database so my problem is, what's the best way to monitor for these specific errors.
I'm on the lookout for software which can execute a custom query on multiple servers and databases and return the result. Whatever the result is, it would be nice if the software could respond to it either by ignoring it or raising an alert.
I'm thinking I have to code this myself, but before I do go in crazy directions, I wanted to hear if anyone knew of such software/techniques. 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

I don't completely understand the situation, but if you need to run custom queries and respond to those results, yes, you probably need to build that yourself. But it won't be that hard. SQL Agent is great for this type of thing.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

If you just have to run the queries, you can use SSMS. You can execute queries against a group of registered servers, by right clicking the group and selecting "new query". The results will be presented as a single result set, with an additional column to display the server name.

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 3

Although, now that I'm thinking on it, you might want to take a look at Red Gate SQL Multi-Script[/url]. It might provide you with a bit more flexibility and power than just using SQL Agent.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

phoenixrisign1599 Valued Member Points: 58 More actions · Answer 4

Multi Script is an awesome tool but dangerous in the wrong hands eh? When executing, test your code first on one box, think twice/three times before executing on a group of servers.

'nix

michael-753984 SSC Journeyman Points: 77 More actions · Answer 5

I already have and use SQL Multi Script from Red Gate. Think of how that script works, now I just need to like schedule various queries say 1 time a day and return the output to me in some fancy way. Could be e-mail etc.

I guess I have to make a SQL Agent Job which runs through the servers databases and runs the query - from there sends an e-mail etc.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

Not sure if this is what you need, but here is a simple powershell script that performs an example query on any number of SQL instances and returns the result:

#Variables

$Computers = @(

"server1\instance1",

"server2\instance2",

"server3\instance3"

)

$query = "select 'database: ' + name from sys.databases where is_auto_update_stats_on = 0;"

$count = $Computers.Count

for ($i=0; $i -lt $count; $i++)

{

Write-Host $Computers[$i]

Invoke-Sqlcmd -Query $query -ServerInstance $Computers[$i]

Write-Host

}

__________________________________________________________________________________
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]

michael-753984 SSC Journeyman Points: 77 More actions · Answer 7

Didn't think about PowerShell actually, anything is possible with it.

Would just need to ajust it to execute a .sql file instead of inline sql and it would do the job together with a scheduled task 🙂

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Actually, you could also use the performance data collector and set up a custom collection.

-- Gianluca Sartori

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 9

michael-753984 (4/20/2011)
Didn't think about PowerShell actually, anything is possible with it.
Would just need to ajust it to execute a .sql file instead of inline sql and it would do the job together with a scheduled task 🙂

Here is a sample using a sql script instead of a query:

#Variables

$Computers = @(

"server1\instance1",

"server2\instance2",

"server3\instance3"

)

$script = "[…path…]\script.sql"

$count = $Computers.Count

for ($i=0; $i -lt $count; $i++)

{

Write-Host $Computers[$i]

Invoke-Sqlcmd -ServerInstance $Computers[$i] -Database "dbName1" -InputFile $script

}

You can schedule powershell scripts to run as jobs on the SQL Server Agent.

__________________________________________________________________________________
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]

Steve Jones - SSC Editor SSC Guru Points: 736252 More actions · Answer 10

The problem with multi-connect scripts is that they sometimes fail and blow up. What I might look to do is have each server execute the query and store the results locally. That way if your main job is down, or your connectivity fails, you still get the query run. Then use a central job that might pull all those results back to a central location and store it before emailing it out to you.