April 15, 2011 at 2:00 am
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. 🙂
April 15, 2011 at 6:02 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 15, 2011 at 6:04 am
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
April 15, 2011 at 6:08 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 15, 2011 at 10:05 am
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
April 15, 2011 at 10:53 am
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.
April 19, 2011 at 2:04 pm
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]
April 20, 2011 at 4:46 am
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 🙂
April 20, 2011 at 6:50 am
Actually, you could also use the performance data collector and set up a custom collection.
-- Gianluca Sartori
April 20, 2011 at 10:20 am
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]
April 20, 2011 at 10:49 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply