To check the MAX Worker Thread value

To check the MAX Worker Thread value
select max_workers_count from sys.dm_os_sys_info
to configure worker thread
USE master;
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE ;
GO
EXEC sp_configure 'max worker threads', 2048 ;
GO
RECONFIGURE;
GO

To Know Domain account details

To lists the domain account status details and part of Domain Groups
net user vgemanj /domain

kill all connections to a particular database

First create Stored Procedure -KillConnectionsToDB by executing following statements on Master db after that

Exec KillConnectionsToDB (Pass your db which you want to kill the connection )



USE [Master]
GO
/****** Object:  StoredProcedure [dbo].[KillConnectionsToDB]    Script Date: 08/13/2011 05:49:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[KillConnectionsToDB]
 @DBName SYSNAME
, @Debug BIT = 0
AS
BEGIN
 DECLARE @SPID     INT
 DECLARE @SQL     NVARCHAR(1000)
 DECLARE Connection_Cursor CURSOR
  FOR  SELECT spid
   FROM master.dbo.sysprocesses sysprocesses LEFT OUTER JOIN master.dbo.sysdatabases sysdatabases ON sysprocesses.dbid = sysdatabases.dbid
   WHERE sysdatabases.name IN (@DBName) AND sysprocesses.spid BETWEEN 0 AND spid AND sysprocesses.status <> 'background'

 OPEN Connection_Cursor

 FETCH NEXT FROM Connection_Cursor INTO @SPID
 WHILE @@FETCH_STATUS=0
 BEGIN
  print @spid
  SET @SQL = 'KILL ' + STR(@SPID)
  IF @Debug = 0
   EXEC master.dbo.sp_executesql @statement = @SQL
  ELSE
   PRINT @Sql
  FETCH NEXT FROM Connection_Cursor INTO @SPID
 END
 CLOSE Connection_Cursor
 DEALLOCATE Connection_Cursor
END

High Availability Group

High Availability Group: - it is advance version of existing Mirror DB
it is a group of user databases that fails over together, like in mirror HAG has Primary Replica and Secondary Replica concept, Primary Replica server which is hosting userdbs where the live data comes in and goes out where as Secondary Replica is a stand by server- like a copy of the Primary.

It supports Automatic and Manual Failovers, in case of primary goes down secondary server becomes primary.

This feature is dependent on Windows Failover Cluster, this is not a something like our traditional cluster where in we have shared disks, quorum disk and special network configuration. A cluster without share disk and quorum. Any person who has little knowledge on cluster can create/form a cluster. How to create a cluster we will discuss more in detail

How it works: -

There is term called Availability Group Listener, which plays a vital role in HAG, actually this listener provides a way of connecting to an availability group via SQL Network Name- Virtual Network Name. in simple terms… listener listens all incoming requests and routes the user to Primary Replica.

This process uses compression methodology to sync data from Primary to Secondary/Replica. At any given moment, only a single SQL Server instance may host the primary replica of an availability group's databases.

Advantages: -

Ø  It supports 1 Primary and up to 4 Secondary Replicas.

Ø  Active Secondary’s, Secondary Replicas are accessible and we can use them for read-only or reporting purpose

Ø  You can take all kinds of backups – Full/Log/ Secondary replica – differential is not allowed. Copy only backup is allowed on secondary where as direct log backup is allowed.

Ø  Automatic Page repair, it is something like whenever a page gets corrupted, first it checks whether that page is available on other replicas in HAG if available it takes the information from that server and repairs the page

Prerequisites & How to Configure:

Ø  All the servers (Primary & Secondary Replicas) should be in same domain and must partake in Single Windows Server Failover Cluster.

This cluster is not core/fully formed cluster; it doesn’t required Shared disks and Quorum disk.

Creating cluster is very simple all you need is cluster name and what are the nodes/replica servers you want to form.

·         Add failover feature on each server – in ServerManager à Feature -> Add feature

·         Form a cluster by clicking Create Cluster in Cluster Administrator.