Usefull DMV's

Dynamic Management Views and Functions (Transact-SQL) SQL Server 2014 Other Versions 5 out of 5 rated this helpful - Rate this topic Dynamic management views and functions return server state information that can be used to monitor the health of a server.

https://gallery.technet.microsoft.com/Usefull-DMVs-14b6bb92

SQL Server 2012 Instalation

https://gallery.technet.microsoft.com/SQL-Server-2012-Instalation-fcf5fb9e

DataCompression_Scripts and Steps

Database Compression document enclosed. This will do only page level compression if we run the scripts as it is mentioned in the document.If we need to do the ROW level compression please change the script, Just remove the commets where i have commented for the ROW.

https://gallery.technet.microsoft.com/DataCompressionScripts-and-40780587

Backup Details Scrpit _Power shell

Download script will provide the backup details in a csv format.

https://gallery.technet.microsoft.com/Backup-Details-Scrpit-6bb5de67

To Find Lead Blocking - Use Below Query

This will list out the lead blocker spid, based on this we can ask our application teams take decision to kill or not. This will be used in day to day basis when ever application team reports hampering performance or complaining the time taken to process is longer than the usual.

https://gallery.technet.microsoft.com/Lead-Blocking-Query-df3f0e21

Register a SPN for SQL Server Authentication with Kerberos

When it comes to configuring your SQL Servers to use Kerberos authentication there are a couple of prerequisites that must be met. First, the clients and servers must be joined to a domain. If they are joined, but they are in different domains then a two-way trust must be setup between these domains. Secondly an SPN must be successfully registered for the SQL Server service so that it can be identified on the network.

Please download the enclosed document which found blog, explained very neatly and easily.

https://gallery.technet.microsoft.com/Register-a-SPN-for-SQL-570f50fe

Always On HA Overview

1.    Introduction

High Availability of an application or service provided to the end user and reducing the downtime of the outages occurred if any (Disaster Recovery Management) plays a major role in the lifecycle of any application. System outages are either anticipated (planned) or result of failures (unplanned) which may lead to data loss directly/indirectly impacts the end user requirements. In these situations, the primary objective of the software engineers/developers is to bring back the system online as soon as possible with minimal the data loss. AlwaysOn is a feature in the SQL server 2012 which provides flexible and cost efficient high availability and disaster recovery solution. AlwaysOn provides automatic recovery from failures avoiding the downtime which increases the high availability of critical applications online thereby reducing the data loss. AlwaysOn can be configured at both database level and instance level.

1.1.       Availability Groups (AG) & AG Listener

Availability group is a collection of databases in which AlwaysOn is configured at database level. Availability group enhances capabilities of database mirroring and ensures the availability of databases. Client application connects to the Availability group’s databases through a virtual network name called Availability group listener (AG Listener).Client need not required to find the active physical instance of the SQL server instead Client connects to the AG Listener which determines the available primary SQL server. A SQL server instance can have multiple availability groups.

1.2.       Availability Replicas and Roles

Availability Replicas are the user databases residing in the Availability Group (AG). Each database in the availability group can have a maximum of four availability replicas but only one replica in the AG can act as primary replica which is enabled for read-write operations and remaining replicas act as secondary read only replicas. The secondary replicas are considered as the backup.

Please Download :

https://gallery.technet.microsoft.com/Always-On-HA-Overview-33efee38