Automated audit of all SQL Servers over multiple domains

• terry.home

  Ten Centuries

  Points: 1004

  More actions

  May 19, 2013 at 2:07 pm

  #277916

  I recently joined a company that has merged with another. Both companies had Enterpise licensing and seem to have set up loads of SQL databases without question. Since the companies have merged IT department have decided to create a new windows domain and started setting up even more SQL servers there to hopefully migrate all systems over.

  So at present I have three domains where I am discovering more and more SQL servers crawling out of the woodwork almost daily. Some I have discovered are still SQL 2000 databases and I know they are already starting installation of SQL2012 databases.

  I would like to audit all databases but due to the size of the organisation, I think it would be best to setup an automated task to audit all the SQL Server daily centrally from one SQL Server

  The task should determine the following

  1. All the Servers that have SQL Server installed on them

  2. All the instances of each SQL Server

  3. Version of Each Instance

  4. List all databases on each instance, its files, sizes, free space etc.

  5. List all failed jobs in the last 24 hours

  6. List of where backups are stored, how many there are

  7. Write all this information into a relational database

  8. Set up automated reports to

  8.1 Warn of servers that are no longer available

  8.2 Warn of new servers that have become available

  8.2 Report on all Versions of SQL so we can start upgrade project

  8.3 Report on databases that are no longer used

  8.4 Report on Databases using large amounts of disk space that could be freed up

  8.5 Report on database/table growth and show a graph on growth or decline

  I realise SQL Server 2008 has made an attempt to do this but this does not work for older versions so I thought it might be worth writing this as a training exersize. At the same time I would like to forward plan a bit to ensure it will be compatible with 2012

  The first thing I need to do is to run sqlcmd -L and have this execute on each of the servers to detect what other servers (or/and instances) are out there. Since I have seen that this does not always render all the SQL instances for some reason, I would like set this to be run on each of the servers that my Audit server is aware of..

  What I have so far is (sorry I know its not much, but it is a start)

  /* Start */

  EXEC sp_configure 'xp_cmdshell', 1

  GO

  EXEC sp_configure 'show advanced options', 1

  GO

  RECONFIGURE

  if exists (select name from sysobjects where name ='temp_audit_file') drop table temp_audit_file

  create table temp_audit_file (all_instances varchar(200))

  insert temp_audit_file

  exec xp_cmdshell 'sqlcmd -L'

  select * from temp_audit_file

  where Upper(all_instances) not in ('Servers:','')

  order by 1

  drop table temp_audit_file

  /* End */