Basic Cluster Administration tasks through command prompt/PowerShell2.0

There are few tasks(moving Quorum disk/restarting Cluster Service on all nodes) which you can’t perform through Cluster Administrator –GUI but you can perform through command line\Powershell.

Following are the basic operations which we perform on regular basis,  below commands works on Windows2003/2008/R2

Note:

we need to import failovercluster module in PowerShell2.0 before we execute any cluster related command otherwise it throws error. 

To import that module run following command in Powershell2.0 prompt.( Powershell version1.0 doesn’t have feature to import modules)

import-module failoverclusters

These commands are very helpful in case if you are unable to open cluster administrator –GUI.

Task	Command Prompt	PowerShell
Move cluster group/Quorum disk	cluster group "cluster group" /move:LABLWLA04B	Move-ClusterGroup "Cluster Group" -Node LABLWLA04B
Move instance INS401 to LABLWLA04B node	cluster group "INS401" /move:LABLWLA04B	Move-ClusterGroup INS401 -Node LABLWLA04B
Bring down INS401 instance	cluster group " INS401" /Off	Stop-ClusterGroup INS401
Bring up  INS401 instance	cluster group " INS401" /On	Start-ClusterGroup INS401

There lot of other commands available in both the prompts, if you want to explore…. here are the links: http://technet.microsoft.com/en-us/library/ee461009.aspx , http://blogs.technet.com/b/josebda/archive/2010/09/25/mapping-cluster-exe-commands-to-windows-powershell-cmdlets-for-failover-clusters-extended-edition.aspx

Best way to read error log

There are many ways to read error log from SQL Server.

1. xp_readerrorlog
2. SSMS->Management ->SQLServer Error Logs ->

in both the cases you need to wait untill it opens the error log and then either you need to scroll down/up for particular error or search for that particular error, both the cases it consumes some amount of time.

Lets take an example: To find out "Error: 1443, Severity: 16, State: 2"

the best way is: input the results of XP_readerrorlog to a temparory table once the result of the errorlog gets in to the temporary table you need to put a where condition to get the desired output.


create table #temp1

(logdate datetime,
processinfo varchar(100),
text_ varchar(1024)
)
insert #temp1exec xp_readerrorlog
select * from #temp1where text_ ='Error: 1443, Severity: 16, State: 2.'order by logdate asc

MS12-070 Security Hotfix for Reporting Server

There is a hot fix released on 10/10, it is targeted Reporting Services. If you have Reporting Service installed on SQL box then it is for you.

Note: Actually this is a cumulative update, you can apply this on SQL Server which doesn’t have Reporting services  but it is not recommended unless there is a special need.

MS12-070: Vulnerability in SQL Server Could Allow Elevation of Privilege (2754849)

This security update resolves a privately reported vulnerability in Microsoft SQL Server on systems running SQL Server Reporting Services (SSRS). The vulnerability is a cross-site-scripting (XSS) vulnerability that could allow elevation of privilege, enabling an attacker to execute arbitrary commands on the SSRS site in the context of the targeted user. An attacker could exploit this vulnerability by sending a specially crafted link to the user and convincing the user to click the link. An attacker could also host a website that contains a webpage designed to exploit the vulnerability. In addition, compromised websites and websites that accept or host user-provided content or advertisements could contain specially crafted content that could exploit this vulnerability.

http://technet.microsoft.com/en-us/security/bulletin/ms12-070

MICROSOFT CRITICALITY: IMPORTANT

WELLS FARGO PATCH RATING: STANDARD

REBOOT NEEDED: Yes, in some cases

PATCH DUE DATE: October 22, 2012

TARGETED SOFTWARE:

Microsoft SQL Server 2005 Express Edition with Advanced Services Service Pack 4

Microsoft SQL Server 2005 for 32-bit Systems Service Pack 4

Microsoft SQL Server 2005 for x64-based Systems Service Pack 4

Microsoft SQL Server 2005 for Itanium-based Systems Service Pack 4

Microsoft SQL Server 2008 for 32-bit Systems Service Pack 2

Microsoft SQL Server 2008 for 32-bit Systems Service Pack 3

Microsoft SQL Server 2008 for x64-based Systems Service Pack 2

Microsoft SQL Server 2008 for x64-based Systems Service Pack 3

Microsoft SQL Server 2008 for Itanium-based Systems Service Pack 2

Microsoft SQL Server 2008 for Itanium-based Systems Service Pack 3

Microsoft SQL Server 2008 R2 for 32-bit Systems Service Pack 1

Microsoft SQL Server 2008 R2 for x64-based Systems Service Pack 1

Microsoft SQL Server 2008 R2 for Itanium-based Systems Service Pack 1

Microsoft SQL Server 2012 for 32-bit Systems

Microsoft SQL Server 2012 for x64-based Systems

How to block SSMS remotely

To block SSMS remotely :

There are few situations where you want to not allow users to connect to SQLServer from local machine or remotely.

How would i do that: it is very simple, you have to create a Trigger inorder to acomplish it.

 create following Trigger - ssms_remote_block1
        USE master;
GO
CREATE TRIGGER ssms_remote_block1
ON ALL SERVER
FOR LOGON
AS
BEGIN
IF EXISTS (SELECT *
   FROM sys.dm_exec_sessions AS es
   WHERE
    es.login_name = ORIGINAL_LOGIN() AND -- The current login
    es.host_name <> 'CLWTD4000' AND -- Make sure it works only on remote connections
    es.program_name LIKE 'Microsoft SQL Server Management Studio%')
 ROLLBACK;
END;

if you want to disable trigger: disable trigger ssms_remote_block1 on all server

Query to list last 20 transactions happend on the sql server

Query to list last 20 transactions happend on the sql server

SELECT TOP 20
    qs.sql_handle,
    qs.execution_count,
    qs.total_worker_time AS Total_CPU,
    total_CPU_inSeconds = --Converted from microseconds
    qs.total_worker_time/1000000,
    average_CPU_inSeconds = --Converted from microseconds
    (qs.total_worker_time/1000000) / qs.execution_count,
    qs.total_elapsed_time,
    total_elapsed_time_inSeconds = --Converted from microseconds
    qs.total_elapsed_time/1000000,
    st.text,
    qp.query_plan
FROM
    sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS apply sys.dm_exec_query_plan (qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC