Archive

Archive for January 1, 2015

Configure Always On and Availability group Part 1

January 1, 2015 Leave a comment

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

image

# Enable always on if not enabled.

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

image

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

image

#Check AlwaysOn is Enabled.

image

#Create Database Mirroring Endpoint @ Primary SQL Instance

image

 

#Create Database Mirroring Endpoint @ Secondary SQL Instance

image

 

#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)

 

image

 

image

 

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

 

image

Categories: AlwaysOn 2012

Is Lock Pages In Memory enabled

January 1, 2015 Leave a comment
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'

Categories: T-SQL-Scripts

Database pages in the buffer pool

January 1, 2015 Leave a comment

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

 

image

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

image

Categories: BufferPool