Like most DBAs I wear many different hats at work, one of which is network administrator. Recently I started a project to document our production servers. I know there's some great tools available that do this but they can get pricey and I'm always up for looking into alternative (free) ways to accomplish things. After a bit of searching around I stumbled across SYDI-Server, a free VBScript that uses WMI to gather information about Windows servers with the ability to output results as an XML file. I figured I could leverage SQL 2005's native XML support to work with SYDI's output to produce my documentation.
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:
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.
Download links:
SYDI-Server
xpFIleUtils Lite
My SYDI scripts
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.
Download links:
SYDI-Server
xpFIleUtils Lite
My SYDI scripts
About Kendal
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
11 comments
Hi Kendal.
Many many compliment.
Your project it's very useful.
Bye
Andrea
Hello Kendal,
This saved me from having to re-invent the wheel.
One question: does your DB account for running Sydi collections against the same servers, say on a Monthly Basis? If it does not, how much modification would be required to allow for that?
Thank you for the good work.
Yes, usp_Import_SYDIServer_XML checks if a row exists for the server already; if it does, it deletes the row. Then it bulk inserts the XML data for the server and adds a row into SYDIServer_History so that you have some history data on what the server looked like over time.
Hi Kendal,
it is not possible to download your scripts. The Error is, that the Group does not exist. Do i something wrong ? Is there an alternative download Option ?
Thanks for your Help.
Norbert.
The download link to your scripts no longer works. Are they still available for download?
Kendal
Very interesting work, unfortunately your Sydi.zip file is not available. Could provide an updated link?
Thanks!
Ditto. Can't find SYDI.zip. Would greatly appreciate this, as this is exactly the solution I am looking for.
Figured it out - sort of.
If you go here:
http://groups.google.com/group/kendal-van-dyke/files
there should be a "download all files"-link at the bottom of the page. At the least that way one can get to the "sydi.zip".
Those looking to implement this should also note that the current release of xpFileUtils has renamed their SP prefixes - the sql files in your scripts reference "master...blahblah_SP", whereas xpFileUtils names them "xpn_blahblah_SP" (or something similar, don't have the console in front of me atm).
Limo Services TorontoWe also are proud to have some of the lowest prices around for data recovery services. Toronto limo service
Looking for the best moving companies and movers services in Toronto Ontario CA! Check moving prices before you start moving! At "High Level Movers" offer local and long distance moving.
Post a Comment