Post reply

MONITORING 60+ SQL SERVERS

  • June 18, 2014 at 1:56 am

    #287002

    Hi

    I have been placed at client side which has 60+ live production server.I want some mechanism through which we can monitor servers.Second i want some mechanism through which a daily report is made/mailed through which we can have various parameters such as database size,disk space job status etc.

    Please suggest how to proceed ahead with daily report part.

    We can have any product from market as we donot have rights

    Thanks and regards

    Anoop

  • June 18, 2014 at 2:29 am

    #1722634

    For this you probably want to buy a 3rd party monitoring tool. It'll be the fastest and easiest. Is there budget?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 18, 2014 at 4:25 am

    #1722677

    There are a ton of mechanisms built into Windows and SQL Server that will let you set all this up on your own. It's just a bunch of work and time to get it done. Look to Performance Monitor, the Dynamic Management Views and Trace or Extended Events. If you don't have time, then a third party tool is absolutely your best bet. My favorites are SQL Sentry and Red Gate SQL Monitor. DISCLOSURE: I work for Red Gate.

    "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

  • June 18, 2014 at 11:00 pm

    #1723067

    GilaMonster (6/18/2014)

    For this you probably want to buy a 3rd party monitoring tool. It'll be the fastest and easiest. Is there budget?

    Grant Fritchey (6/18/2014)

    There are a ton of mechanisms built into Windows and SQL Server that will let you set all this up on your own. It's just a bunch of work and time to get it done. Look to Performance Monitor, the Dynamic Management Views and Trace or Extended Events. If you don't have time, then a third party tool is absolutely your best bet. My favorites are SQL Sentry and Red Gate SQL Monitor. DISCLOSURE: I work for Red Gate.

    Thanks @gilamonster and @Grant Fritchey for your replies and suggestions but problem is that original client has outsourced to some client who has in turn outsourced to our firm so we can not recommend third party products as we donot have say in budgets.neither thy would agree for one

    Presently what i want is some mechanism for developing daily moinoting task so that i donot have to visit every servers and note down .

    I want some mechanism that through one particular server could capture info for other servers and is saved directly i some kind sheets whether excel or html files.then this sheets or html miles could be mailed to boss

    After this gets over i would go more deeply as suggested by @Grant Fritchey

  • June 19, 2014 at 3:01 am

    #1723108

    Hi Anoop,

    I've set something like this up a couple of times now. I've set up a Central Management Server on a SQL Server and used that to do the data collection and reporting. Once all the servers you want to report on are registered in the CMS you can develop a SSIS package that runs on the CMS to collect data from each server in turn and log the results in a new database created on the CMS for this purpose. The SSIS package can query sysmanagement_shared_registered_servers_internal in msdb for a list of Servers to query. You will need a foreach component in the SSIS package. If you install SSRS on this central server you can also get it to email out the daily reports to you.

    I've used this mechanism to report on failed SQL Agent Jobs, low disk space, low data file space, databases without a backup, databases without a log backup, and failed SSRS subscriptions on a daily basis.

  • June 19, 2014 at 5:01 am

    #1723161

    If you're going to set this up yourself, then I agree, Central Management Server is the way to go. There's plenty of documentation on that in the Books Online.

    "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

  • June 19, 2014 at 12:14 pm

    #1723391

    There are some powershell scripts out there that can be used to do this kind of thing. They can be coded to produce very pretty excel spreadsheet type reports. Glen Barry has a set of queries to monitor SQL Server that are also very useful for this type of work.

  • June 21, 2014 at 8:03 am

    #1723763

    eyespi20 (6/19/2014)

    There are some powershell scripts out there that can be used to do this kind of thing. They can be coded to produce very pretty excel spreadsheet type reports. Glen Barry has a set of queries to monitor SQL Server that are also very useful for this type of work.

    Can u provide links ???

  • June 21, 2014 at 8:04 am

    #1723764

    Grant Fritchey (6/19/2014)

    If you're going to set this up yourself, then I agree, Central Management Server is the way to go. There's plenty of documentation on that in the Books Online.

    Hi grant can you provide links or links with egamples.

  • June 21, 2014 at 8:10 am

    #1723767

    tripleAxe (6/19/2014)

    Hi Anoop,

    I've set something like this up a couple of times now. I've set up a Central Management Server on a SQL Server and used that to do the data collection and reporting. Once all the servers you want to report on are registered in the CMS you can develop a SSIS package that runs on the CMS to collect data from each server in turn and log the results in a new database created on the CMS for this purpose. The SSIS package can query sysmanagement_shared_registered_servers_internal in msdb for a list of Servers to query. You will need a foreach component in the SSIS package. If you install SSRS on this central server you can also get it to email out the daily reports to you.

    I've used this mechanism to report on failed SQL Agent Jobs, low disk space, low data file space, databases without a backup, databases without a log backup, and failed SSRS subscriptions on a daily basis.

    Hi @tripleaxe can u provide a small eg or a website links with eg which would help me to ubnderstand how to do it

    U know problem is that to access server we have to use cyberark software which manages password and access to servers.Can your suggestion works with cyberark software too

  • June 21, 2014 at 8:14 am

    #1723768

    anoop.mig29 (6/18/2014)

    Hi

    I have been placed at client side which has 60+ live production server.I want some mechanism through which we can monitor servers.Second i want some mechanism through which a daily report is made/mailed through which we can have various parameters such as database size,disk space job status etc.

    Please suggest how to proceed ahead with daily report part.

    We can have any product from market as we donot have rights

    Thanks and regards

    Anoop

    Are they on the same physical network.are third party tools an option.

    🙂

  • June 21, 2014 at 9:36 am

    #1723778

    anoop.mig29 (6/21/2014)

    Grant Fritchey (6/19/2014)

    If you're going to set this up yourself, then I agree, Central Management Server is the way to go. There's plenty of documentation on that in the Books Online.

    Hi grant can you provide links or links with egamples.

    I don't have an article that I've written on monitoring through Central Management Servers. I do have one I wrote on using them to manage backups[/url]. Here's the starting point for these in the Books Online.

    "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

  • June 23, 2014 at 2:13 am

    #1723914

    I've never used or seen CyberArk so I can't comment on that.

    Here are a few links to get you started:

    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

  • June 24, 2014 at 6:04 am

    #1724296

    Here are three links to PowerShell scripts that do this work in various methods. A Google search of "powershell sql server inventory" brings up quite a few links. Do like I did and take some from here and some from there to create your own that serves your exact needs. It's not hard to do, but may take a bit of time -- not nearly as much time as doing the inventory by hand though.

    http://blogs.technet.com/b/heyscriptingguy/archive/2013/05/07/use-powershell-to-discover-diagnose-and-document-sql-server.aspx

    http://gallery.technet.microsoft.com/scriptcenter/PowerShell-SQL-Inventory-e9b92dac

    http://colleenmorrow.com/2012/05/29/building-a-sql-server-inventory-part-1/

  • June 26, 2014 at 12:02 am

    #1724908

    eyespi20 (6/24/2014)

    Here are three links to PowerShell scripts that do this work in various methods. A Google search of "powershell sql server inventory" brings up quite a few links. Do like I did and take some from here and some from there to create your own that serves your exact needs. It's not hard to do, but may take a bit of time -- not nearly as much time as doing the inventory by hand though.

    http://blogs.technet.com/b/heyscriptingguy/archive/2013/05/07/use-powershell-to-discover-diagnose-and-document-sql-server.aspx

    http://gallery.technet.microsoft.com/scriptcenter/PowerShell-SQL-Inventory-e9b92dac

    http://colleenmorrow.com/2012/05/29/building-a-sql-server-inventory-part-1/

    Thanks for links but i have no knowledge of powershell can u help me what it is and how it works plus provide me link to learn powershell

Viewing 15 posts - 1 through 15 (of 15 total)

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