Remove Availability Group -Replica -Databases-Listener-

Dropping the Availability Group will remove all associated objects . 1.Database (s) will be removed from AG databases 2.Listener will be deleted (object will be dropped from Active Directory). 3.Replicas will be removed from the AG group configuration   USE [master] GO DROP AVAILABILITY GROUP [AG Group Name] GO Dropping Availability Group USE [master] GO ALTER […]

Monitor Alwayson Using SQL Server Management Studio

With every release of Microsoft SQL Server , SQL Server Management Studio has improved a lot and many new feature were added. AlwaysOn Dashboard is one of them which come with SQL Server 2012 and later. To access the AlwaysOn Dashboard Right click the [AlwaysOn High Availability] folder or Right Click the Availability Group   […]

Monitor AlwaysOn Log QueueRate and Redo Queue

Monitoring alwayson Log Send Queue and Redo Queue DMV’s sys.dm_hadr_database_replica_states & sys.availability_replicas provided the information on log send queue and redo Queue size SELECT DB_NAME(database_id) AS dbname,ar.replica_server_name, CASE drs.is_local WHEN 0 THEN ‘REMOTE’ ELSE ‘LOCAL’ END AS is_local, drs.last_hardened_time, drs.log_send_queue_size / 1024 AS MB_log_SendQueueSize, drs.log_send_rate / 1024 AS MB_log_send_rate, drs.redo_queue_size / 1024 AS MB_redo_queue_size, drs.redo_rate / 1024 […]

Enable AlwaysOn with Powershell

By default Enable-SqlAlwaysOn try to restart the sql server service if -NoServiceRestart option is not used.With -NoServiceRestart will make changes in the registery but it will only take effect once the Services re recycled. –Standalone sql instance Enable-SqlAlwaysOn -Path SQLSERVER:hostname or instance name in case of named instance -NoServiceRestart –Clustered Instance Enable-SqlAlwaysOn -Path SQLSERVER:SQLInstance Name […]

SQL Server AlwaysOn Queries Part -1

Get Availability group information T-SQL : GO select name,group_id,health_check_timeout from master.sys.availability_groups GO Get Availability group Replica information T-SQL : go select replica_server_name,create_date, modify_date,endpoint_url,read_only_routing_url,avalibility_mode, failover_mode,session_timeout,backup_priority from master.sys.avalibility_replicas go Get Availability group databases T-SQL : select database_name,is_failover_ready,is_pending_secondary_suspend,is_database_joined from master.sys.dm_hadr_database_replica_cluster_states go select * from sys.dm_hadr_database_replica_states go Get Availability group listeners T-SQL: go select * from sys.availability_group_listeners go   […]

Add Secondary Replica to Availability group Part 3

Adding Secondary Replica :Connect SecondaryReplica use [master] GO GRANT CONNECT ON ENDPOINT::[Mirroring_Endpoint] TO [DBPROXadministrator] GO :Connect PrimaryReplica 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 [DBPROXadministrator] GO :Connect PrimaryReplica IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE […]

Configure AlwaysOn Availability group Listener Part – 2

Availability Group 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 […]

Configure Always On and Availability group Part – 1

Enable AlwaysOn and Configure Availability Group with Primary Replica # Check Always On  is enabled # Enable always on if not enabled. #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 […]