Detect all running instances

Question

Detect all running instances

Green Armchair

Hall of Fame

Points: 3561
More actions
April 20, 2011 at 9:07 am

#237022

Hi
I need to write some code to:
For each Instance running on this server:
Connect to instance and adjust memory allocation
Would Powershell be the best option for this? If so, any help would be appreciated (with the powershell, I'm OK with the SQL).
We have a 6-node cluster running 13 instances. I'd like to schedule a job that automatically adjusts the memory allocation according to how many instances are running on that node.
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

tjaybelt SSCrazy Points: 2604 More actions · Answer 1

in the past, i have had a Monitoring DB Server that I could use to hit each of my other servers. From there, from TSQL or jobs I could execute code against each db in my topology.

Currently, I do not have a Monitoring DB server, and have reverted to running Powershell scripts to hit each db. There are different things that i need to take into consideration, but its working well. And scripts end up being a lot smaller than all the connectivity from 1 DB server to another.

So, im not saying one is better than the other. But i am saying that you can do what you suggest with either.

Good idea to do it in both to get yourself the experience in both.

rahulr.sharma Ten Centuries Points: 1034 More actions · Answer 2

I suppose that this can be made easy using powershell but what exactly do you mean by saying that you want to adjust the memory allocation for the nodes.

I am assuming an example here that the nodes that are running the 13 instances are N1,N2....N6 and the instances running on them are INS1,INS2......INS13. Can you please specify here what you want to do on these nodes and Instances?

Green Armchair Hall of Fame Points: 3561 More actions · Answer 3

I'd like to be able to dynamically adjust the memory allocated to each instance running on the individual nodes.

So, for instance, if one node fails and the instance(s) failover to another node, I'd like a scheduled job to lower the memory allocated to all the instances that now run from this node.

mendesm Ten Centuries Points: 1074 More actions · Answer 4

There probably many different ways of doing this, but the first thing that comes to mind is to create a sql job that runs on start-up. It would run a PowerShell script that get's the instances currently running on that node and would set the sql memory to something like ((Total Ram - (leave this much for the OS)) / Number of Running Instances), or similar.