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 |
|
ffarouqi
Starting Member
3 Posts |
 Posted - 2013-01-24 : 05:23:00
|
| Here's what I am really looking for...We have a 2 node cluster setup in our environment, and in the recent times we had to face quite a lot of trouble as all the resources on suppose node a fails over and sits there on node b, so for e.g: if I am having 5 instances running one node a, and 5 on node b, if for some reason (may be a n/w hiccup), quorum is lost and the node gets evicted (in this case node a), and hence all the resources/instances from node a flips on to node b, and remains there causing excessive load on the server. Ok now to address our main concern. The problem here is we don't want to overburden the server with all the resources junked up on one node, but instead we want the nodes to be balanced for e.g: 5 instances on node a & 5 instances on node b. It doesn't matter who is the owner of these instances, it could be node a or node b, but at least the nodes should be balanced. Now, my concern is on setting up an alert on this to mail me, only when certain criteria is met (for e.g: if there are more than 5 instances on node b, I should get an alert to re balance the nodes, and vice versa). So, basically what I meant is that I need to setup an alert on SQL Cluster which will send me an alert if & only if I meet a certain condition wherein any of the node seems to be out of balance.I have a vague idea for this...something to start of with.The condition probably should benSQLNode > round(nSQL/nNode)where nNode = total # of nodes in a windows cluster (in our case = 2)nSQL = total # of sql instances in the cluster (depends)nSQLNode = # of SQL instances on any of the node (for e.g: node a/node b)round() is a math function to round the decimal number.I have laid the foundation to build this, but I would really appreciate if you guys can put around your coding skills, and get this thing working. It would be really helpful for all of us. I hope to see either a script that you might already been using in your environment or either pointing out to something from where we can build things further (like some kind of a blog or generalized script which can be customized to suit our requirements). Any kind of help and assistance would be highly appreciated.You can always mail me on my e-mail id faisalfarouqi@gmail.com, if you found any useful scripts for setting this up, it can be t-sql (priority on top), powershell or any other thing but it should work to get the alert going...if and only if the condition is met.Thanks, Faisal |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2013-01-26 : 15:20:10
|
| A. Why do you have 5 instances per node? Seems like that is a little excessive.B. You should just be alerted anytime there is a failover.-Chad |
 |
|
|
ffarouqi
Starting Member
3 Posts |
Posted - 2013-01-27 : 03:26:22
|
| Hi Chad,It's not the point why I am having 5 instances on a node, my only problem is a solution to get the stuff about the alert working. Pls. if you could direct me to any links or a script which can actually do this for me.Regards,Faisal |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2013-01-29 : 11:43:06
|
| Here's an easy way:Go to the services applet and in double-click SQL Server Agent service.On the recovery tab, first failure, choose run a program.Write a script to alert you. A command script that sends an SSMS message and/or email. |
|
|
|
ffarouqi
Starting Member
3 Posts |
Posted - 2013-02-21 : 01:23:49
|
| I've tested this powershell script, and it's working amazingly well. The only problem that I am facing now, is that my boss is looking for something which needs to be more generic rather than a hard coded solution. For example, if we have 15 instances on a 2 node windows cluster, then we don't have to change the value in this line IF ($param.Count -gt 5) {sendmail} (like changing the value from 5 to 6 etc). Now, I need to take this through parameters, not manually stating the value. For e.g: this could be something like this if (nSQLNode > round(nSQL/nNode)) {sendmail} where nNode = total # of nodes in a windows cluster -- for e.g if it's a 2 node cluster, we are only interested in getting the count, not the node name. I know there is something cluster node to view the name of the nodes, but we just need the countnSQL = total # of SQL instances in the cluster --same applies here...we need countnSQLNode = # of Sql instances in any of the node.round() is a math function to round the decimal number.I know for sure, this can be implemented in powershell, but I don't know how I can get the count using cmdlets for each of these parameters. Can we take the parameter value using t-sql code, and incorporate it in powershell, and run it through windows task scheduler. Is that possible?I believe rest of the script will remain same.I don't have an idea whether we have anything called round() as a powershell function, but I do know there is something for e.g$param = [System.Math]::Round().Pls. provide your valuable inputs on how can programmatically get the counts.Here is the code:function sendmail{ Write-Host "Sending Email" #SMTP server name $smtpServer = "" #Creating a Mail object $msg = new-object Net.Mail.MailMessage #Creating SMTP server object $smtp = new-object Net.Mail.SmtpClient($smtpServer) #Email structure $msg.From = "" $msg.To.Add("") $msg.subject = "To many instances on $env:COMPUTERNAME" $msg.body = "" #Sending email $smtp.Send($msg) }#Get the number of sqlservr.exe processes running and assign it to the param$param = Get-WmiObject win32_process | where-Object {$_.ProcessName -eq "sqlservr.exe"} | select name#If the number of sqlservr.exe process > 5 then execute the sendmail functionIF ($param.Count -gt 5) {sendmail}Regards,Faisal |
|
|
|
|
|
|
|
|
To setup an alert for balancing sql cluster nodes