May 31, 2007 at 4:28 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
May 31, 2007 at 11:22 pm
Just out of interest, have you looked to see if the Health & History tool could do some of the heavy lifting for you?
Steve.
June 1, 2007 at 4:32 pm
I knew i'd seen something better more recently -> grab yourself the May edition of SQL Server Magazine; there's a 5 page article on how to do exactly this (repository of SQL Server info etc). The InstantDocId is 95385 but you may need a subscription to view it online.
Steve.
June 2, 2007 at 3:05 pm
for detail on each database, you might want to check out SqlSpec, a shareware app I wrote. It's at the link in my sig.
---------------------------------------
elsasoft.org
June 4, 2007 at 5:43 am
OSQL, SQLCMD, and SSIS are all covered very well in the SQL Server documentation (Books Online). Another option would be SMO.
June 8, 2007 at 6:54 am
I downloaded this document from the SQLMag website. It is to create a configuration database and seems like a good template. After beginning to set this up, I debugged it. Currently everything runs fine until the package hits load server info. When it tries to load the servers, it turns red. Also, I can't make any changes to the MultiServer Connection Manager because I always get the error "the connection string is not valid." As far as I can tell, this should never be valid when it comes to dynamic connections. Thus, I am perplexed. I really need to get this running, and I think I have narrowed down the error. From what I can tell, MultiServer is a dynamic list of all of the servers on the network. If anybody else has encountered this perticular error, please reply.
-Kyle
June 8, 2007 at 8:58 am
You don't *want* to make changes to the connection string in the MultiServer connection object as this is done as part of the loop container.
At a guess, i would say that one of the entries (server names) in your table (the one that gets read up in to the ado variable) is incorrect.
Ignoring the first section of the package, the 'Populate ADO Variable' step reads in the server names that you'll want to query into an ADO recordset (using th query SELECT RTRIM(Server) AS servername FROM ServerList_SSIS WHERE (Server IS NOT NULL)). So first step, run this query and see what it returns, maybe then try to connect to one or more of these names using sql mgmt studio.
Assuming the names then get in to the variable (SQL_RS) correctly, the foreach loop loops thru the records in that record set and sets another (string) variable to be the server name (variable is SRV_Conn). As/when the dataflow task (in the first case, load servers) starts, the connection (MultiServer) uses an expression (go to the properties page for the connection, look down the list for 'Expressions' then click the ellipses button to see them , or it, in this case). This expression uses the value in the SRV_Conn string variable to populate the Server section of the connection. Note: in the properties window you should be able to see the start of the conxn string, see how Data Source=;, ie they haven't set the servername of the conxn string, the foreach loop and the expression will do this for you.
So, either you've got a server name that's incorrect, or the servers you're trying to connect to can't/won't accept SSPI (windows) connections from you.
Steve.
November 30, 2007 at 2:15 pm
Using the code from the referenced article,I ran into a problem with connecting to non-default SQL Server instances. The multiserver connection bulks when I use server\instance to populate the ADO variable, but works when I use server,port. This is inconvenient if you attempt to later join from the server list to the collected data to determine if any failed to connect.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply