Monitoring Database Mirroring

Couple of days back I was working for a client on creating a database mirroring monitor report to show how log has been sent to DR in last couple of hour. How much log is left to be sent based on the send rate and long will it.

Stored Procedure sp_dbmmonitorresults in msdb database gives all this detail for Principal database

sp_dbmmonitorresults database_name , rows_to_return , update_status

 

Exec msdb..sp_dbmmonitorresults ‘SGPData’,1,0

 

database_name :  Database Name

rows_to_return

Specifies the quantity of rows returned:

0 = Last row

1 = Rows last two hours

2 = Rows last four hours

3 = Rows last eight hours

4 = Rows last day

5 = Rows last two days

6 = Last 100 rows

7 = Last 500 rows

8 = Last 1,000 rows

9 = Last 1,000,000 rows

 

update_status

Specifies that before returning results the procedure:

0 = Does not update the status for the database. The results are computed using just the last two rows, the age of which depends on when the status table was refreshed.

1 = Updates the status for the database by calling sp_dbmmonitorupdate before computing the results. However, if the status table has been updated within the previous 15 seconds, or the user is not a member of the sysadmin fixed server role, sp_dbmmonitorresults runs without updating the status.

 

 

Database Recovery Status

The stored procedure is written to check the recovery status of the Databases. With Full , Incremental and Log back sequences.

 

Lets say we have a full backup on Sunday along with Incremental once in a day from Monday to Saturday and Log backup every 15 mins. The DR drill is scheduled for Thursday.The Stored Procedure will let us know what sequence will be required to perform a recovery  in case DR Test failed for any reason, this will also check if the backup file still exists or  not.

 

 

USE [master] GO /****** Object: Table [dbo].[CommandLog] Script Date: 1/5/2015 12:05:17 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[CommandLog]( [ID] [int] IDENTITY(1,1) NOT NULL, [DatabaseName] [sysname] NULL, [SchemaName] [sysname] NULL, [ObjectName] [sysname] NULL, [ObjectType] [char](2) NULL, [IndexName] [sysname] NULL, [IndexType] [tinyint] NULL, [StatisticsName] [sysname] NULL, [PartitionNumber] [int] NULL, [ExtendedInfo] [xml] NULL, [Command] [nvarchar](max) NOT NULL, [CommandType] [nvarchar](60) NOT NULL, [StartTime] [datetime] NOT NULL, [EndTime] [datetime] NULL, [ErrorNumber] [int] NULL, [ErrorMessage] [nvarchar](max) NULL, CONSTRAINT [PK_CommandLog] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO

 

Stored Procedure  T-SQL

 

USE master GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[usp_DatabaseRecoveryStatusCheck] @Databases sysname=‘USER_DATABASES’, @LogToTable nvarchar(max) = ‘N’, @Execute nvarchar(max) = ‘Y’, @LoggingLevel int=0 —# 0=NONE, 1=Minimum , 2=FULL AS BEGIN
SET NOCOUNT ON SET ANSI_WARNINGS OFF DECLARE @DbStartLoop int DECLARE @DbENDLoop int DECLARE @Database_Name sysname=NULL DECLARE @ServerName sysname =@@ServerName DECLARE @rowcount int DECLARE @FullDatabaseCheckPointLSN numeric(25,0) DECLARE @FullDatabaseLastLSN numeric(25,0) DECLARE @FullDatabaseBackupDate datetime; DECLARE @FullBackupFiles varchar(MAX); DECLARE @CheckFileStart int DECLARE @CheckFileEND int DECLARE @BackupFileLocation nvarchar(500) DECLARE @CheckFileExists int DECLARE @DatabaseMessage nvarchar(MAX) =NULL DECLARE @Command nvarchar(4000) DECLARE @xml xml DECLARE @delimiter varchar(MAX) DECLARE @CurrentCommand01 nvarchar(max) DECLARE @ErrorMessage nvarchar(max) DECLARE @PrintLog nvarchar(max)=” CREATE TABLE #BackupHistory (database_name varchar(max), physical_device_name varchar(max), backup_start_date datetime, first_lsn numeric(25,0), last_lsn numeric(25,0), checkpoint_lsn numeric(25,0), database_backup_lsn numeric(25,0), [type] char(1), servername varchar(500), RecoveryModel varchar(100), rm int) CREATE TABLE #user_database_backup_chain (id int identity(1,1), dbname varchar(MAX), file_location varchar(MAX), backup_datetime datetime, first_lsn numeric(25,0), lsn_chain numeric(25,0), checkpoint_lsn numeric(25,0), database_backup_lsn numeric(25,0), file_type char(1), file_status varchar(10) NULL) DECLARE @User_database_recovery TABLE (id int , dbname varchar(MAX), file_location varchar(MAX), backup_datetime datetime, first_lsn numeric(25,0), lsn_chain numeric(25,0), checkpoint_lsn numeric(25,0), database_backup_lsn numeric(25,0), file_type char(1), file_status varchar(10) NULL) DECLARE @SELECTedDatabases TABLE (DatabaseName nvarchar(max), DatabaseType nvarchar(max), SELECTed bit) DECLARE @tmpDatabases TABLE (ID int IDENTITY, DatabaseName nvarchar(max), DatabaseNameFS nvarchar(max), DatabaseType nvarchar(max), SELECTed bit, Completed bit, PRIMARY KEY(SELECTed, Completed, ID)) —————————————————————————————————- –// SELECT databases //– —————————————————————————————————- SET @Databases = REPLACE(@Databases, ‘, ‘, ‘,’); WITH Databases1 (StartPosition, ENDPosition, DatabaseItem) AS ( SELECT 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(‘,’, @Databases, 1), 0), LEN(@Databases) + 1) AS ENDPosition, SUBSTRING(@Databases, 1, ISNULL(NULLIF(CHARINDEX(‘,’, @Databases, 1), 0), LEN(@Databases) + 1) – 1) AS DatabaseItem WHERE @Databases IS NOT NULL UNION ALL SELECT CAST(ENDPosition AS int) + 1 AS StartPosition, ISNULL(NULLIF(CHARINDEX(‘,’, @Databases, ENDPosition + 1), 0), LEN(@Databases) + 1) AS ENDPosition, SUBSTRING(@Databases, ENDPosition + 1, ISNULL(NULLIF(CHARINDEX(‘,’, @Databases, ENDPosition + 1), 0), LEN(@Databases) + 1) – ENDPosition – 1) AS DatabaseItem FROM Databases1 WHERE ENDPosition < LEN(@Databases) + 1 ), Databases2 (DatabaseItem, SELECTed) AS ( SELECT CASE WHEN DatabaseItem LIKE ‘-%‘ THEN RIGHT(DatabaseItem,LEN(DatabaseItem) – 1) ELSE DatabaseItem END AS DatabaseItem, CASE WHEN DatabaseItem LIKE ‘-%‘ THEN 0 ELSE 1 END AS SELECTed FROM Databases1 ), Databases3 (DatabaseItem, DatabaseType, SELECTed) AS ( SELECT CASE WHEN DatabaseItem IN(‘ALL_DATABASES’,’SYSTEM_DATABASES’,’USER_DATABASES’) THEN ‘%‘ ELSE DatabaseItem END AS DatabaseItem, CASE WHEN DatabaseItem = ‘SYSTEM_DATABASES’ THEN ‘S’ WHEN DatabaseItem = ‘USER_DATABASES’ THEN ‘U’ ELSE NULL END AS DatabaseType, SELECTed FROM Databases2 ), Databases4 (DatabaseName, DatabaseType, SELECTed) AS ( SELECT CASE WHEN LEFT(DatabaseItem,1) = ‘[‘ AND RIGHT(DatabaseItem,1) = ‘]’ THEN PARSENAME(DatabaseItem,1) ELSE DatabaseItem END AS DatabaseItem, DatabaseType, SELECTed FROM Databases3 ) INSERT INTO @SELECTedDatabases (DatabaseName, DatabaseType, SELECTed) SELECT DatabaseName, DatabaseType, SELECTed FROM Databases4 OPTION (MAXRECURSION 0) INSERT INTO @tmpDatabases (DatabaseName, DatabaseNameFS, DatabaseType, SELECTed, Completed) SELECT [name] AS DatabaseName, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE([name],’\’,”),’/‘,”),’:’,”),’*‘,”),’?‘,”),’“‘,”),'<‘,”),’>’,”),’|’,”),’ ‘,”) AS DatabaseNameFS, CASE WHEN name IN(‘master’,’msdb’,’model’) THEN ‘S’ ELSE ‘U’ END AS DatabaseType, 0 AS SELECTed, 0 AS Completed FROM sys.databases WHERE [name] <> ‘tempdb’ AND source_database_id IS NULL ORDER BY [name] ASC UPDATE tmpDatabases SET tmpDatabases.SELECTed = SELECTedDatabases.SELECTed FROM @tmpDatabases tmpDatabases INNER JOIN @SELECTedDatabases SELECTedDatabases ON tmpDatabases.DatabaseName LIKE REPLACE(SELECTedDatabases.DatabaseName,’_’,'[_]’) AND (tmpDatabases.DatabaseType = SELECTedDatabases.DatabaseType OR SELECTedDatabases.DatabaseType IS NULL) WHERE SELECTedDatabases.SELECTed = 1 UPDATE tmpDatabases SET tmpDatabases.SELECTed = SELECTedDatabases.SELECTed FROM @tmpDatabases tmpDatabases INNER JOIN @SELECTedDatabases SELECTedDatabases ON tmpDatabases.DatabaseName LIKE REPLACE(SELECTedDatabases.DatabaseName,’_’,'[_]’) AND (tmpDatabases.DatabaseType = SELECTedDatabases.DatabaseType OR SELECTedDatabases.DatabaseType IS NULL) WHERE SELECTedDatabases.SELECTed = 0 IF @Databases IS NULL OR NOT EXISTS(SELECT * FROM @SELECTedDatabases) OR EXISTS(SELECT * FROM @SELECTedDatabases WHERE DatabaseName IS NULL OR DatabaseName = ”) BEGIN SET @ErrorMessage = ‘The value for the parameter @Databases is not supported.’ + CHAR(13) + CHAR(10) + ‘ ‘ RAISERROR(@ErrorMessage,16,1) WITH NOWAIT — SET @Error = @@ERROR END —————————————————————————————————- –// Get and loop each database //– —————————————————————————————————- WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE SELECTed = 1 AND Completed = 0) BEGIN SELECT TOP 1 @Database_Name=DatabaseName FROM @tmpDatabases WHERE SELECTed = 1 AND Completed = 0 ORDER BY ID ASC –Print ‘Database :’+@Database_Name SET @DatabaseMessage = ” SET @DatabaseMessage =CHAr(13)+ ‘Database :’+@Database_Name +CHAR (13) RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT —————————————————————————————————- –// Populate #BackupInfoHistory Temp Table with Backup Information for each database //– —————————————————————————————————- SET @CurrentCommand01=” SET @CurrentCommand01=’SELECT s.database_name,m.physical_device_name, s.backup_start_date, s.first_lsn, s.last_lsn,s.checkpoint_lsn,s.database_backup_lsn, s.[type],s.server_name,s.recovery_model ,row_number() Over(Partition by s.[type] Order by s.backup_finish_date DESC ) rn FROM msdb.dbo.backupset s INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id WHERE s.database_name =”’+ @Database_Name + ”’ AND server_name=”’+@ServerName+”” IF (@Execute=’Y’ OR @Execute=’N’) BEGIN INSERT INTO #BackupHistory EXEC(@CurrentCommand01) END IF @LogToTable=’Y’ BEGIN INSERT INTO [CommandLog] (DatabaseName,Command,CommandType,StartTime,EndTime,ErrorNumber,ErrorMessage) VALUES (@Database_Name,@CurrentCommand01,’SELECT’,GETDATE(),GETDATE(),0,’NONE’) END —————————————————————————————————- –// Populate Database Backup History Chain FROM last full backup //– —————————————————————————————————- IF (@Execute=’Y’ OR @Execute=’N’) BEGIN insert into #user_database_backup_chain (dbname,file_location,backup_datetime,first_lsn,lsn_chain,checkpoint_lsn,database_backup_lsn,file_type) SELECT @Database_Name,physical_device_name,backup_start_date,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,[type] FROM #BackupHistory WHERE backup_start_date>=(SELECT top 1 s.backup_start_date FROM msdb.dbo.backupset s Where s.[type]=’D’ and s.server_name=@ServerName AND s.database_name = @Database_Name Order by s.backup_finish_date DESC) and [type]=’D’ END IF @LogToTable=’Y’ BEGIN INSERT INTO [CommandLog] (DatabaseName,Command,CommandType,StartTime,EndTime,ErrorNumber,ErrorMessage) VALUES (@Database_Name,@CurrentCommand01,’SELECT FULL’,GETDATE(),GETDATE(),0,’NONE’) END —————————————————————————————————- –// Insert [NULL] values for databases never backedup //– —————————————————————————————————- IF (SELECT COUNT(*) FROM #BackupHistory WHERE database_name=@Database_Name and [type]=’D’) < 1 BEGIN insert into #user_database_backup_chain (dbname,file_location,backup_datetime,first_lsn,lsn_chain,checkpoint_lsn,database_backup_lsn,file_type) values (@Database_Name,NULL,NULL,NULL,NULL,NULL,NULL,NULL) END —————————————————————————————————- –// Populate Database Incremental Backup History Chain FROM last full backup //– —————————————————————————————————- SELECT @FullDatabaseCheckPointLSN=checkpoint_lsn FROM #user_database_backup_chain IF (@Execute=’Y’OR @Execute=’N’) BEGIN insert into #user_database_backup_chain (dbname,file_location,backup_datetime,first_lsn,lsn_chain,checkpoint_lsn,database_backup_lsn,file_type) SELECT top 1 @Database_Name,physical_device_name,backup_start_date backup_start_date,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,[type] FROM #BackupHistory WHERE database_backup_lsn=@FullDatabaseCheckPointLSN and [type]=’I’ SET @rowcount=@@RowCount END IF @LogToTable=’Y’ BEGIN IF (@rowcount >= 1) BEGIN INSERT INTO [CommandLog] (DatabaseName,Command,CommandType,StartTime,EndTime,ErrorNumber,ErrorMessage) VALUES (@Database_Name,@CurrentCommand01,’SELECT DIFF’,GETDATE(),GETDATE(),0,’NONE’) END END —————————————————————————————————- –// Populate Database Log Backup History Chain FROM last full backup //– —————————————————————————————————- SELECT @FullDatabaseCheckPointLSN=MAX(checkpoint_lsn) FROM #user_database_backup_chain IF (@Execute=’Y’ OR @Execute=’N’) BEGIN insert into #user_database_backup_chain (dbname,file_location,backup_datetime,first_lsn,lsn_chain,checkpoint_lsn,database_backup_lsn,file_type) SELECT @Database_Name,physical_device_name,backup_start_date,first_lsn,last_lsn,checkpoint_lsn,database_backup_lsn,[type] FROM #BackupHistory WHERE checkpoint_lsn>=@FullDatabaseCheckPointLSN and [type]=’L’ order by backup_start_date ASC SET @rowcount=@@RowCount END IF @LogToTable=’Y’ BEGIN IF (@rowcount >= 1) BEGIN INSERT INTO [CommandLog] (DatabaseName,Command,CommandType,StartTime,EndTime,ErrorNumber,ErrorMessage) VALUES (@Database_Name,@CurrentCommand01,’SELECT LOG’,GETDATE(),GETDATE(),0,’NONE’) END END —————————————————————————————————- –// Check backup files status //– —————————————————————————————————- SET @CheckFileStart=1 SELECT @CheckFileEND=count(*) FROM #user_database_backup_chain WHILE @CheckFileStart <= @CheckFileEND BEGIN SET @CurrentCommand01=” SELECT @BackupFileLocation=file_location ,@Database_Name=dbname FROM #user_database_backup_chain where id=@CheckFileStart –print @filepath –SET @cmd=’RESTORE VERIFYONLY FROM DISK=’+””+@filepath+”” –exec (@cmd) SET @CurrentCommand01=’File status check :’+’ EXEC Master.dbo.xp_fileexist ‘+@BackupFileLocation+’ , ‘+’@CheckFileExists OUT ‘ SET @DatabaseMessage = ” SET @DatabaseMessage =CHAr(13)+ISNULL(@CurrentCommand01,’Status unknown’) +CHAR (13) IF (@Execute=’Y’) BEGIN EXEC master.dbo.xp_fileexist @BackupFileLocation , @CheckFileExists OUT END IF @LoggingLevel=1 or @LoggingLevel=2 or @Execute=’N’ BEGIN RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT END IF @LogToTable=’Y’ BEGIN INSERT INTO [CommandLog] (DatabaseName,Command,CommandType,StartTime,EndTime,ErrorNumber,ErrorMessage) VALUES (@Database_Name,ISNULL(@CurrentCommand01,’Missing File’),’FILECHECK’,GETDATE(),GETDATE(),0,’NONE’) END IF @CheckFileExists =1 BEGIN update #user_database_backup_chain set file_status=’Exists’ where id=@CheckFileStart END ELSE BEGIN update #user_database_backup_chain set file_status=’Missing’ where id=@CheckFileStart END SET @CheckFileStart=@CheckFileStart + 1 END — SELECT * FROM #user_database_backup_chain insert into @User_database_recovery SELECT * FROM #user_database_backup_chain truncate table #user_database_backup_chain SET @CheckFileStart=1 — Update that the database is completed UPDATE @tmpDatabases SET Completed = 1 WHERE SELECTed = 1 AND Completed = 0 AND DatabaseName = @Database_Name END —————————————————————————————————- –// Drop Temp Tables //– —————————————————————————————————- IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects where name like ‘#BackupHistory%’) BEGIN DROP TABLE #BackupHistory END IF EXISTS (SELECT name FROM tempdb.dbo.sysobjects where name like ‘#user_database_backup_chain%’) BEGIN DROP TABLE #user_database_backup_chain END —————————————————————————————————- –// Show Status //– —————————————————————————————————- IF @Execute=’Y’ BEGIN IF EXISTS (SELECT dbname ,’No Recovery Possible.No Backup found.’ FROM @User_database_recovery where file_location IS NULL) BEGIN SELECT DISTINCT dbname ,’No Recovery Possible.No Backup found.’ FROM @User_database_recovery where file_location IS NULL END IF EXISTS (SELECT dbname , ‘Point in time Recovery not possible.Backup file missing.’ RecoveryStatus FROM @User_database_recovery where file_location IS NOT NULL and file_status=’Missing’) BEGIN SELECT DISTINCT dbname , ‘Point in time Recovery not possible.Backup file missing.’ RecoveryStatus FROM @User_database_recovery where file_location IS NOT NULL and file_status=’Missing’ END END —————————————————————————————————- –// Log information //– —————————————————————————————————- IF @LoggingLevel>=0 BEGIN SELECT id,dbname,file_location,backup_datetime,file_type,file_status FROM @User_database_recovery END SET NOCOUNT OFF END —————————————————————————————————–

 

Configure Always On and Availability group Part 3

Add Secondary Replica

 

--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
:Connect SQL03\GOSGPRDSQL02
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Mirroring_Endpoint] TO [DBPROX\administrator]
GO
:Connect SQL02\GOSGPRDSQL01
IF (SELECT state FROM sys.endpoints WHERE name = N'Mirroring_Endpoint') <> 0
BEGIN
    ALTER ENDPOINT [Mirroring_Endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Mirroring_Endpoint] TO [DBPROX\administrator]
GO
:Connect SQL02\GOSGPRDSQL01
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END
IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO
:Connect SQL03\GOSGPRDSQL02
USE [master]
GO
ALTER AVAILABILITY GROUP [GLBAGSQL0203]
ADD REPLICA ON N'SQL02\GOSGPRDSQL01' WITH (ENDPOINT_URL = N'TCP://SQL02.dbprox.local:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO
:Connect SQL02\GOSGPRDSQL01
ALTER AVAILABILITY GROUP [GLBAGSQL0203] JOIN;
GO

Configure Always On and Availability group Part 2

Listener Configuration

 

An availability group listener is a virtual network name (VNN) to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. An availability group listener enables a client to connect to an availability replica without knowing the name of the physical instance of SQL Server to which the client is connecting. The client connection string does not need to be modified to connect to the current location of the current primary replica.

An availability group listener consists of a Domain Name System (DNS) listener name, listener port designation, and one or more IP addresses. Only the TCP protocol is supported by availability group listener. The DNS name of the listener must also be unique in the domain and in NetBIOS. When you create a new availability group listener it becomes a resource in a cluster with an associated virtual network name (VNN), virtual IP (VIP), and availability group dependency. A client uses DNS to resolve the VNN into multiple IP addresses and then tries to connect to each address, until a connection request succeeds or until the connection requests time out.

#Create Availability Group Listener

TSQL :

USE [master]
GO
ALTER AVAILABILITY GROUP [GLBAGSQL0203]
ADD LISTENER N'GLBAGSQL0203LST' (WITH IP ((N'192.192.0.14', N'255.255.255.0'))
, PORT=1522);
GO

 

 

PowerShell :

#Creating AlwaysOn availability group Listener

Import-Module SQLPS

[string[]] $ServerList= "SQL03\GOSGPRDSQL02" #SQL Instance Name on Availability Group is Online
$Agname='GLBAGSQL0203' #Availability Group Name
$Port='1522' #Mention Listener Port Number (By Default it listens on 1433)
$ListenerName='GLBAGSQL0203LST' #Listener Name
$StaticIP='192.192.0.14' #Static unique IP for Listener)
$Subnet='255.255.255.0' #Listener IP Subnet

$StaticSubnetIP=$StaticIP+'/'+$Subnet


$serverObjects = @()
$replicas = @()
foreach ($Server in $ServerList)
{
write-host ''

write-host '[ '$Server' ]' -ForegroundColor Yellow

Write-Host "[-]Creating SMO Object for Server: $Server" `n

$serverObject=New-Object Microsoft.SQLServer.Management.SMO.Server($Server)
if ($serverObject) {Write-Host " |-SMO Object for Server: $Server created successfully" -ForegroundColor Yellow `n} 

#Create Availability Group Listener
Write-Host "[-]Creating Availability Group Listener for : $Server" `n

$Path='SQLSERVER:\SQL\'+$Server+'\AvailabilityGroups\'+$Agname
Write-host $Path
$sqagl=New-SqlAvailabilityGroupListener -Name $ListenerName `
                                 -StaticIp $StaticSubnetIP `
                                 -Path $Path
  if ($sqagl -eq $null)
  {
    Write-Host " |-Availability Group Listener  $Agname created successfully" `n
  }
 
Write-Host "[-]Setting Availability Group Listener for : $Server" `n   

$sqaglpath='SQLSERVER:\SQL\'+$Server+'\AvailabilityGroups\'+$Agname+'\AvailabilityGroupListeners\'+$ListenerName

$saglp=Set-SqlAvailabilityGroupListener -Port $Port `
                                        -Path $sqaglpath
if ($saglp)
  {
    Write-Host " |-Availability Group Listener for : $Server configured successfully" `n
  }

}

Configure Always On and Availability group Part 1

Enable AlwaysOn and Configure Availability Group with Primary Replica

# Check Always On  is enabled

PS C:\> Set-Location SQLServer:\SQL\SQL03\GOSGPRDSQL02
PS SQLSERVER:\SQL\SQL03\GOSGPRDSQL02>Get-Item . |Select IsHADREnabled |Format-Table -AutoSize

# Enable always on if not enabled.

PS C:\> Enable-SqlAlwaysOn -Path SQLSERVER:\SQL\SQL03\GOSGPRDSQL03

#SQL Server services should be recycled  (Stopped and started)

#Check AlwaysOn is Enabled.

#Create Database Mirroring Endpoint @ Primary SQL Instance

 

#Create Database Mirroring Endpoint @ Secondary SQL Instance

 

#Create Availability Group  with Primary Instance as Replica (Secondary Replica will be added later)

T-SQL

USE [master]
GO
CREATE AVAILABILITY GROUP [GOSGPRDAGSQL02SQL03]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY)
FOR 
REPLICA ON N'SQL03\GOSGPRDSQL02' WITH (ENDPOINT_URL = N'TCP://SQL03.dbprox.local:5022', 
FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, SESSION_TIMEOUT = 10, 
BACKUP_PRIORITY = 50, PRIMARY_ROLE(ALLOW_CONNECTIONS = ALL), 
SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO

 

Powershell

$primaryReplica = New-SqlAvailabilityReplica -Name "SQL03\GOSGPRDSQL02" -EndpointURL "TCP://SQL03.dbprox.com:5022" -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" -Version 12 -AsTemplate

New-SqlAvailabilityGroup -Name "GLBAGSQL0203" -Path "SQLSERVER:\SQL\SQL03\GOSGPRDSQL02" -AvailabilityReplica @($primaryReplica)

 

 

 

#Availability Group service is create and can be seen in the Windows Cluster

 

Is Lock Pages In Memory enabled

If lock pages in memory is enabled you can see the “Using locked pages for buffer pool”  is recorded in the SQL Server error log.

 

exec xp_readerrorlog 0, 1, 'locked pages'

Database pages in the buffer pool

When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server. sys.dm_os_buffer_descriptors returns cached pages for all user and system databases. This includes pages that are associated with the Resource database.

SELECT 
    DB_NAME(database_id) AS [Database Name]
    ,CAST(COUNT(*) * 8/1024.0 AS DECIMAL (10,2))  AS [Cached Size (MB)]
FROM sys.dm_os_buffer_descriptors WITH (NOLOCK)
WHERE database_id not in (1,3,4) --{Do not include system database pages}
AND database_id <> 32767  -- ResourceDB
GROUP BY DB_NAME(database_id)
ORDER BY [Cached Size (MB)] DESC

 

Non Buffer Pool Information

select name,sum(pages_kb)/128.0 [Cache Size (MB)] from sys.dm_os_memory_cache_entries
where pages_kb > 0 group by name order by sum(pages_kb) desc