Recently I have had a great need to see inside about 39 servers (Production, Dev, Test, etc) and find out things like
* How many logins have Sysadmin privileges
* How many users have the db_owner role
* How many databases total do we have in the Enterprise
* How much space is being used in total by Databases and their Log files
With this in mind, I came across the article in February in SQL Server Magazine about the DBA Repository and have not had enough time to really dive into it. But now I have had a chance and a great need, so amidst my powershell and SMO work to get the information I want, I have also leaned on this SSIS package to gather this data.
Those who have not seen this or loaded it for actionable results, it comes with an SSIS package to load the information and a SSRS reporting project that displays some of the data in the tables. The reporting is interesting, but not yet complete, so I will be venturing there next to get some good reports that can show me some exceptions that are out there and also some of the security concerns. I will also be tweaking it to allow me to keep a history of the Drive space and the size of the databases to see when they grow, etc. But most of this is for trending of the different statistics kept inside the tables of the database repository.
I have made some changes for those that use this repository.
* Added a GroupID field from the sysfiles into the Database_Info table so that I can separate Data and Log groups to know how much space is Datafile and how much is Log file
* Changed the query for SQL 2000 jobs so that I got the jobs for SQL 2000. For some reason it failed to execute so I change the query now it does not fail
* Change the Validation to Delayed so that the generic connections for 2005 and 2000 would not try to connect to the servers, when the connection was incorrect because it was missing the server by design
* Added a few stored procedures and reports for getting some data back the way I wanted to
I will be updating this package and the reporting package as I go along and will be releasing it to the Author and to the wild with permission from the original author, so stay tuned. For the information it gathered, and for 39 servers my workstation ran it in 8 minutes for all the data.
But the other reason for writing this blog entry is that when I put the package into the SSIS store, I ran into something that I did not know about. When you have a named instance of SQL instead of a default instance on your machine and there is no default instance, expanding MSDB inside a connection to SSIS, causes an error because it is looking for the default instance. Most DBAs know that when you connect to SSIS you connect to the machine name and not to an instance and that there is ever only 1 instance of SSIS on a machine, so this could be a problem when you want to store a package inside MSDB.
In the documentation of SQL (BOL), there is hidden a little note on how to get this to work. In the directory (default place without change on install) C:\Program Files\Microsoft SQL Server\90\DTS\Binn there is a file called MsDtsSrvr.ini.xml. In this file there is a property that you must change to get it to connect to your instace to get at MSDB. It is <ServerName>.</ServerName>. By default it is pointing to a . (period) which is the local default instance. Because I did not have one, I had to change it to point to my instance .\DESK. I saved the file and restarted SSIS and I could then expand the MSDB and store the package in the SSIS Package Store.
The other wierdness I came across is that I wanted to schedule it in a job. The only option by default is to run the step as SQL Server Agent, which is not a very privileged account on my local machine. So it would not run anything against other servers, just mine. So I read up on Credentials and Proxies in SQL 2005 and found out that I could create a Credential with a user that had permissions on the other servers (Windows Permissions) and after creating a credential, I created a proxy in SQL Server Agent and told it to use the Credential. Then I edited the Step and in the dropdown for the Run As... there was my proxy, and sure enough my job with the SSIS package ran successfully, and I am a happy camper.