Monday 31 March 2014

SQL Server : Logical CPU Count

Scheduler is a component of SQLOS which evenly distributes load amongst CPUs.

scheduler_id – ID of the scheduler. All schedulers that are used to run regular queries have ID numbers less than 1048576. Those schedulers that have IDs greater than or equal to 1048576 are used internally by SQL Server, such as the dedicated administrator connection scheduler.

SELECT  cpu_count / hyperthread_ratio AS physical_cpu_sockets
FROM    sys.dm_os_sys_info ;

SELECT  cpu_count FROM    sys.dm_os_sys_info ;

SELECT  hyperthread_ratio FROM    sys.dm_os_sys_info ;

SELECT  * FROM    sys.dm_os_sys_info ;


Brgds,

Chhavinath Mishra
Sr. Database Administrator
Microsoft Certified IT Professional (MCITP)


Monday 3 March 2014

SQL Server : Change the startup accounts (Service account) for SQL Server services in cluster

While working on a project to build SQL Server 2 node cluster, I have seen that few times we have requirement to change the service account post installation of SQL Server 2008/R2 or 2012 due to any reason like same account is used with other instance and now business need to have separate account which should be dedicated to that particular instance only.

In case of Standalone instance, this task is quite simple as we need to change the account from SQL Server configuration and restart the underlying services to ensure that change is effective from that point of time.

In case of cluster, We have 2 sets of services ( if we are having 2 node cluster) or more than that depending upon how many nodes cluster we are running with.

We need to make changes only on Active node and then this change will be effective automatically on all passive nodes.

Step1:  On active node, GO to Configuration Tools, and then click SQL Server Configuration Manager
Step2:  In SQL Server Configuration Manager, click SQL Server Services
Step3:  Click Properties then click the Log On tab and select a Log on as account type
Step4:  After selecting the new service startup account, click OK
Step5:  Now, either take the SQL Server resource group offline and then bring it online or restart the server if you are running only single instance

Hope it will help you.

P.S: While changing service account to a new domain user by SQL Server Configuration Manager in a clustered SQL Server instance, the server threw exception 0x80010105” may arise. Please follow below article which I found very helpful and hope it would be helpful to you as well if you face same issue.



Brgds,

Chhavinath Mishra
Sr. Database Administrator
Microsoft Certified IT Professional (MCITP)