MONITORING 60+ SQL SERVERS

  • 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

  • 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
  • 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

  • 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

  • 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.

  • 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

  • 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.

  • 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 ???

  • 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.

  • 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

  • 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.

    🙂

  • 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

  • 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/

  • 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