Network documentation with SQL 2005 and SYDI

<div>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 <a href="http://sydiproject.com/" target="_blank">SYDI-Server</a>, 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.<br /><br />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.<br /><br />Here's how to set it up:<br /><ol><li><a href="http://sydiproject.com/download" target="_blank">Download</a> 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) </li><li>In the same directory that you extracted SYDI-Server to, create a subdirectory named "output" and share it as "sydi".</li><li><a href="http://kendal-van-dyke.googlegroups.com/web/SYDI.zip?gda=f9AlwzkAAAB0tVVsCGtg5niPvCkhcMw0Kqyd4oK7hSnmFjJj5eEC9WG1qiJ7UbTIup-M2XPURDRamcKSRW952QtCV_OP24mq&gsc=urDS2AsAAACOwGW3exllKPAmAyAshSC1" target="_blank">Download</a> 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). </li><li>Install <a href="http://mastercluster.com/xprocFileUtils.html" target="_blank">xpFileUtils Lite</a> 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 </li><li>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) </li><li>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 </li></ol>Once it's all set up simply execute sydi_all_servers.bat to start documenting your servers. It takes one required parameter - the name of the SQL Server to bulk insert the results into. By default the scripts bulk insert into a database named SYDI. If you created the tables and procedure in some other database (step 5) you can pass its name as the second parameter after the server name.<br /><br />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.<br /><br />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)<br /><br />Give it a try and let me know if you use this in any interesting ways.<br /><br />Download links:<br /><a href="http://sydiproject.com/download" target="_blank">SYDI-Server</a><br /><a href="http://mastercluster.com/xprocFileUtils.html" target="_blank">xpFIleUtils Lite</a><br /><a href="http://kendal-van-dyke.googlegroups.com/web/SYDI.zip?gda=f9AlwzkAAAB0tVVsCGtg5niPvCkhcMw0Kqyd4oK7hSnmFjJj5eEC9WG1qiJ7UbTIup-M2XPURDRamcKSRW952QtCV_OP24mq&gsc=urDS2AsAAACOwGW3exllKPAmAyAshSC1" target="_blank">My SYDI scripts</a><br /><br /><br /><br></div>

Share This

• Twitter
• Google+
• Linked In
• Facebook

Related Posts

Documentation SYDI XML