Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
marinavin
Starting Member
1 Post |
 Posted - 2012-09-24 : 16:43:38
|
| 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
52326 Posts |
Posted - 2012-09-24 : 16:46:59
|
| using SSIS package is one methodAdd 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 MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-24 : 21:15:33
|
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 } |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-09-25 : 07:33:56
|
| 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 |
|
|
|
|
|
|
|
|
getting cpu, memory usage from different servers