May 31, 2007 at 4:27 pm
I am working on creating a configeration database based on the information pulled from a large number (over 40) of servers. I am newly out of college and don't have a lot of experience with things like this, but I am trying to create a configuration database. I need to know the best way to create a connection to all of the SQL servers on my network and then automate the pulling of data from these SQL servers. The types of data I would need are things like Instance ID, DBMS Version, Database Properties, Database Name, Modification Date, Last full backup, last transaction log backup, etc. I have been pointed towards either working with OSQL/SQL CMD, in which case could someone please point me towards a guide, and SSIS, I am reposting this thread in the SSIS area. I need to take all of the gathered data and place it into a central repository database after formatting it. Any help is greatly appreciated.
-Kyle Schlapkohl
June 6, 2007 at 7:36 am
Kyle,
If you are familiar with a .NET programming language, you should take a look at SMO (or SQL DMO, for SQL Server 2000). This programmatic interface will allow you to connect to instances of SQL Server and perform a number of useful tasks, including those you mentioned. Check SQL Server Books Online or MSDN, those will give you a ton of information about how to use SMO.
hth
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
June 8, 2007 at 12:57 pm
The easiest way is to create an XML Task in SSIS which reads in an Xml File that contains all the Servers and Credentials that you need to connect to each Server / Database. A ForEach NodeList Enumerator can be used to read each "section" of the Xml file and modify properties on a SINGLE Connection Manager. Next, the connection manager would connect and you could execute a query using System Tables and System Functions (@@Version for example) to query each Sql Server (and/or Database).
The SSIS Package would take about a day to create.
Your Xml File would look something like this:
<Connections>
<Connection>
<Server>(local)</Server>
<Database>pubs</Database>
<Authentication>Trusted</Authentication>
<UserID></UserID>
<Password></Password>
</Connection>
<Connection>
<Server>(local)\Instance2</Server>
<Database>pubs</Database>
<Authentication>Standard</Authentication>
<UserID>sa</UserID>
<Password>password</Password>
</Connection>
</Connections>
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply