Cluster - Basic Terms

 In this post I want to describe basic terms used in windows cluster. Let's have a look :

Cluster Resource is anything that you want clustered or virtualized.

 SQL Server Cluster includes :

•  The SQL Server virtual name
•  One or more SQL Server IP addresses
•  The SQL Server Service
•  The SQL Server Agent Service
•  The Full Text resource.

 Cluster group is a group of related cluster resources. You can only fail over the entire group not the  individual resources.

In the context of cluster a shared disk is a disk that can be owned over time by any configured node in the cluster but it can be owned by one node at a time.

Each resource has a LooksAlive lightweight check to see whether it appears to be working. For the SQL Server Service resource , this is a query to the operating system to see whether it looks like the service is up.
Each resource also has IsAlive check that is bit more through.  For thacte SQL Server Service resource , the IsAlive check is a simple query against the master database.This actually verifies that the cluster service can connect to SQL Server and retrieve a small result set within the configured time limit.
There are time when looksalive check succeeds but IsAlive check fails. When the IsAlive check fails the configured number of times, it causes the group to fail.

Quorum is the agreed-upon place for storing information common to the windows cluster. Microsoft Windows clustering stores this information on shared disk called Quorum disk. The Quorum disk is disk resource in the same resource group that also has the windows cluster virtual name and the Windows cluster IP address. This group can reside on any node in the cluster and can be failed between nodes without disrupting the activity of the other clustered groups. The is often called the windows cluster group or the quorum group.
Along with the data stored on the quorum, registry keys exist that need to be kept in the sync. The quorum stored information about what ' checkpointed' registry keys have changed. A checkpointed key can be identified by it's existence in the cluster registry key hive. SQL Server set up adds several of it's keys to the checkpoint list , including the keys that store the location of the master database data and log and the location of the SQL server error log. The propagation of changes to these keys to other nodes in the cluster is called registry cloning.

Terminology

RTM means Release to Manufacturing. It is the original, released build version of the product, i.e. what you get on the DVD or when you download the iso file from MSDN.
A Hotfix is designed to fix a single issue, usually after a case has been escalated through Microsoft CSS to the SQL Server Product Team.
Cumulative Update is a cumulative package of hotfixes (usually 20-40) that also includes all previous cumulative updates for that Service Pack. Cumulative Updates are not fully regression tested. Cumulative Updates are released every eight weeks.
Service Pack is a much larger collection of hotfixes that have been fully regression tested. Service Packs are typically released every 12-18 months.

Difference between....

What is difference between database Mirroring and Replication

1. In Replication we can do object level copying where Mirroring is databse level. 2. There is no automatic fail over in Replication where as in Mirroring it is possible 3. We can maintain as many as subscription server in Replication in Mirroring there is only one primary server and one Mirror. Difference between lazy writer and checkpoint ? Lazy Writer Lazy writer finds dirty pages in the buffer pool and write them to disk and drop out those pages from cache. It does this to keep certain amount of free pages available with in the buffer pool for data that may be requested by other queries.The pages that it writes out are 'older pages' , ones that haven't been used for a while. If there are lot of available buffers, the lazy writer will be doing relatively little work and the number of pages written to disk will be quite low. If the lazy writer's consistently writing lot of data, it may indicate that there is memory bottleneck. In short lazy writer concentrates on clearing out older buffer pages. Checkpoint The checkpoint process also writes dirty pages to disk but there it has no interest in keeping available buffers or memory pressure.The job of the checkpoint is to keep the potential time needed to recover the database to a small value. What is the difference between Checkpoint_duration and Recovery_interval ? Checkpoint_duration is how long the checkpoint can run for. Recovery_interval affects how often it runs. Using checkpoint duration to force the checkpoint to run in less time will increase IO, because it’s forcing the X amount of writes to be done in a shorter time than SQL would be default. Using recovery interval to make the checkpoint run more often will reduce IO, because it has less to do each time it runs.

Script to see only errors in SQL error log

When we encouter some issue in SQL , first we check SQL errorlog. As lot of other information is also present in errorlog sometimes it takes time to reach to actual error.Here is small script which you can use to efficiently fetch errors from errorlog.

CREATE TABLE #errorlog_info
  (
     logdate     DATETIME,
     processinfo VARCHAR(30),
     text_data   VARCHAR(MAX)
  )
INSERT INTO #errorlog_info
EXEC Sp_readerrorlog
SELECT *
FROM   #errorlog_info
WHERE  processinfo != 'backup'
       AND text_data LIKE '%error%'
DROP TABLE #errorlog_info

Ghost Records

When you delete data in your database, SQL Server can mark those objects as "ghosts" (meaning that deletion is pending) and clean them up later by using a background task. This process is called Ghost Record Cleanup. Ghost Record Cleanup improves the performance of the DELETE command because SQL Server doesn't have to deal with the physical cleanup right away.

About Amazon

Amazon, a Fortune 500 company based in Seattle, Washington, is the global leader in e-commerce.
Jeff Bezos started Amazon in 1995.
Amazon has revolutionised online shopping experience.
Amozon is customer-centric company where people can find and discover anything they want to buy online.
Amazon also has a "cloud" reader to allow users to read, and purchase, Kindle books from a web browser.
The Amazon Kindle is a series of e-book readers. Amazon Kindle eReaders enable users to shop for, download, browse, and read e-books, newspapers, magazines, blogs, and other digital media via wireless networking.

Command to shut down remote computer

The shutdown command has a few options called switches. You can always see them by typing shutdown -? in the command prompt if you forget any of them.

-i: Display GUI interface, must be the first option
-l: Log off (cannot be used with -m option)
-s: Shutdown the computer
-r: Shutdown and restart the computer
-a: Abort a system shutdown
-m \\computername: Remote computer to shutdown/restart/abort
-t xx: Set timeout for shutdown to xx seconds
-c “comment”: Shutdown comment (maximum of 127 characters)
-f: Forces running applications to close without warning
-d [u][p]:xx:yy: The reason code for the shutdown u is the user code p is a planned shutdown code xx is the major reason code (positive integer less than 256) yy is the minor reason code (positive integer less than 65536)