Monitor SQL database status across the environment using SSIS Package

Question

Monitor SQL database status across the environment using SSIS Package

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
March 25, 2016 at 5:41 am

#314405

Anyone has a documentation how to achieve monitoring of all the SQL database status in an environment using a SSIS package. My goal is to run this package every 15 minutes and send an alert if database state is not online or the server is unable to connect.
Many thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

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

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

I'm unclear why you would do this using SSIS? If you're just polling the instances for connectivity, you could easily do this through T-SQL or Powershell. Building SSIS is an odd choice.

"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

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 2

Was trying to avoid linked servers(sql) and extra server level permissions(Windows).

Basically running an SSIS package from an instance will need to have access on sql server instance level, not on server level.

Thanks!

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

You should be able to do anything with other mechanisms without having to use SSIS. I'm really not aware of anyone who uses SSIS as a monitoring tool. I'll be interested to see what kinds of follow-ups you get.

"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

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 4

Thanks Grant. Do you have a documentation to accomplish this with out ssis.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 5

Not precisely. It's just a question of running a query against the database or server through a SQL Agent Job. There isn't that much to it. Agent can run independent from the server. The issue is if it's offline. You can also use windows scheduler to the same thing with a Powershell script to run the command. It should all be pretty straight forward.

"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

tripleAxe SSCertifiable Points: 5706 More actions · Answer 6

I've done this before using SSIS running on a Central Management Server.

Here are some links which you may find useful.

http://www.mssqltips.com/sqlservertip/2946/centralized-ssis-solution-to-monitor-failed-sql-server-agent-jobs-across-the-enterprise/

https://www.simple-talk.com/sql/sql-tools/registered-servers-and--central-management-server-stores/

http://ask.sqlservercentral.com/questions/34972/central-management-servers.html