To hook the two together I set up batch scripts which call SYDI to create one XML file per server, then use osql to call a stored procedure which bulk inserts the file and makes a copy of the XML in a history table.
Here's how to set it up:
- Download and extract SYDI-Server on the machine that you'll use to gather your data (In my case I have a dedicated admin server in each of my datacenters to do this)
- In the same directory that you extracted SYDI-Server to, create a subdirectory named "output" and share it as "sydi".
- Download and extract my scripts in the same directory that you extracted SYDI-Server to. There should be two DOS batch scripts (sydi_all_servers.bat and sydi.bat), a text file (servers.txt), and SQL scripts file (sydi schema.sql). sydi.bat does the work of gathering information for a server and calling the stored procedure to save the results. sydi_all_servers.bat is a wrapper that passes the content from each line in servers.txt to sydi.bat; each call to sydi.bat opens in a new command window (I did it this way so I could gather server information from multiple servers simultaneously).
- Install xpFileUtils Lite on your SQL 2005 instance that you want to store all your server documentation on. This is used by the stored procedure which bulk inserts the XML output from SYDI
- Create a new database named "SYDI"; apply the script "sydi schema.sql" (extracted in step 3) which will create two tables and the stored procedure which bulk inserts the output from SYDI-Server. (If you prefer to create the tables and procedure in some other DB that's no problem - the batch scripts can handle this)
- Modify servers.txt (from step 3) to include the servers you want to document. Each line contains the server name and IP address separated by a tab
One note about security...the account used to run the batch scripts will need administrator rights on the servers it documents, write rights to the directory created in step 2, and read rights to the network share (also created in step 2). The scripts make a trusted connection to your SQL Server. In SQL 2005 bulk inserts using trusted connections require some additional steps be done in Active Directory to work properly if your SQL Service is running under a domain account. If you're in this boat then you can work around it in one of two ways: Run your SQL Server as Local System (not a recommended best practice) or use a SQL login. If you prefer to use a SQL login you'll need to change the osql switches in sydi.bat. You'll also need to make sure that the domain account your SQL Server service is using has read rights to the network share created in step 2. I'll cover the bit about bulk inserts using trusted connections in a future post.
Once you've run the batch scripts against all of your servers you can use XML-DML to query the data and build custom reports\documentation. I've included a sample query which shows some high level information about each server. SYDI-Server collects a LOT of information (look at one of the XML output files and you'll see what I mean) so there are lots of possibilities. I could easily see this being exposed to reporting services so there's a friendly way for IT folks to create their own reports. I could also see this being set up as a scheduled task and integrated into an alerting system so that generates notifications when a configuration change is detected. And of course the data is there for ad hoc queries when you need to know something specific about your servers (e.g. performing an audit of who the members of the local administrators group are for all machines, finding machines where security patch X isn't applied, or listing the shares on every machine)
Give it a try and let me know if you use this in any interesting ways.
My SYDI scripts