SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 getting cpu, memory usage from different servers
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

marinavin
Starting Member

USA
1 Posts

Posted - 09/24/2012 :  16:43:38  Show Profile  Reply with Quote
Hello all,

I need to get the cpu,memory, servername, date of all our servers and put that info into a table everyday.

I have figured out how to get most of that information.

Query:
SELECT getdate()as Date, cpu_count AS [Logical CPU Count], hyperthread_ratio AS [Hyperthread Ratio],cpu_count/hyperthread_ratio AS [Physical CPU Count],
physical_memory_in_bytes/1048576 AS [Physical Memory (MB)],
sqlserver_start_time --, affinity_type_desc -- (affinity_type_desc is only in 2008 R2)
FROM sys.dm_os_sys_info WITH (NOLOCK) OPTION (RECOMPILE);

Here is the table I've created:

TABLE CREATED IN DBAWORK db
create table Infrastructure_changes
(date datetime not null,
ServerName varchar (15) not null,
Logical_CPU_Count int not null,
Hyperthread_Ratio int not null,
Physical_CPU_Count int not null,
Physical_Memory int not null,
sqlserver_start_time datetime not null)

We have a table in DBAWork db that lists all our servers.
How do I get the info from the query above for all of our servers on the network?

Your advice would be greatly appreciated.
Thank you.

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/24/2012 :  16:46:59  Show Profile  Reply with Quote
using SSIS package is one method

Add a for each loop to iterate through servers, configure a dynamic connection manager for dynamically connecting to each server and then use the source query in OLEDB source task to capture the required information and populate your table using a static oledb destination

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/24/2012 :  21:15:33  Show Profile  Reply with Quote
If you are on server 2008 you can use a Powershell script. If you are on 2003, you will need to install Powershell if you want to run this. I tested the code below on my two windows 7 machines with windows authentication. Be sure to modify the names in the list of servers and the names of the server and database where you are going to store the results.
# Servers to scan
$servers = ( 
	"server1",
	"server2",
	"server3"
	);

# Server and database where data is stored.
$InfrastructureInfoServer = "InfServer";
$InfrastructureDatabase = "InfDatabase";

## Get data from each server and store.
foreach ($s in $servers)
{
	# Your query to be run against each server. Assumes windows auth.
	$result = Invoke-sqlcmd " `
	 	SELECT `
	 		GETDATE() AS Date, `
	 		@@servername As servername, `
			cpu_count AS [Logical CPU Count], `
			hyperthread_ratio AS [Hyperthread Ratio], `
			cpu_count / hyperthread_ratio AS [Physical CPU Count], `
			physical_memory_in_bytes / 1048576 AS [Physical Memory (MB)], `
			sqlserver_start_time `
		FROM `
			sys.dm_os_sys_info WITH (NOLOCK) OPTION(RECOMPILE); " `
	-ServerInstance "$s" ;
	
	#Store the results into the Infrastructure server. Assumes windows auth
	Invoke-Sqlcmd " `
  		INSERT INTO Infrastructure_changes values ( `
  			'$($result[0])', `
  			'$($result[1])', `
  			'$($result[2])', `
  			'$($result[3])', `
  			'$($result[4])', `
  			'$($result[5])', `
  			'$($result[6])'  `
  			) " `
	-ServerInstance $InfrastructureInfoServer `
	-Database $InfrastructureDatabase 
}
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2062 Posts

Posted - 09/25/2012 :  01:26:30  Show Profile  Visit jackv's Homepage  Reply with Quote
Powershell is good for this iteration of servers. As well as option outlined abover, There's a few other options, which may fit in with your current management practises:
1) Powershell and SMO - http://www.sqlserver-dba.com/2011/06/powershell-for-sql-smo.html
2) Invoke SQLMD through Powershell - http://www.sqlserver-dba.com/2011/07/powershell-run-script-on-all-sql-servers.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 09/25/2012 :  07:33:56  Show Profile  Reply with Quote
Another seemingly very interesting approach is to use SQL Server Powershell Provider, which lets you work with SQL Server instances and objects as though they are objects in a hierarchy like a directory structure.

I have only read about it and so I don't know if the problem that OP is trying to solve can be addressed using this approach, but it seemed very novel and seemed like it can be very intuitive to use: http://technet.microsoft.com/en-us/library/cc281947(v=sql.100).aspx
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000