Finding SQL Primary and Secondary Replica in SQL 2012 AG Groups

 

Whenever I try to find out which is primary or secondary in SQL 2012 AG groups, I find it out through SSMS by connecting to AG Group and then opening Availability Groups. This is a tedious process sometimes and I wanted to find much better process. I posted a question on Twitter and got great info from Allan Hirt @SQLHA. He suggested two links which give great information on using DMV. Here are the links

• http://t.co/pmRal95p4l
• http://t.co/Nu8LF3xAf1

Here is another script compiled by one of my co-worker (Tim) which displays the following

• Availability Group Listener
• Availability Group
• State
• IP Address
• Databases within AG groups
• Replicas
• Primary Replica

Hope this helps

   1:  --

   2:  -- This script provides a 1 line view by AG indicating the basic AG summary information 

   3:  --

   4:  SELECT AvailabilityGroupListener, AvailabilityGroup, state_desc, ip_address, Databases, Replicas, primary_replica

   5:  FROM  (

   6:  SELECT   distinct qo.*,qi.*

   7:  FROM    (select distinct ag.name as [AvailabilityGroup], ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc, ags.primary_replica

   8:  from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

   9:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id

  10:  join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id ) qo

  11:  CROSS APPLY

  12:          (

  13:          SELECT  CASE ROW_NUMBER() OVER(ORDER BY database_name) WHEN 1 THEN '' ELSE ', ' END + qi.database_name

  14:          FROM    (select ag.name as [AvailabilityGroup],ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc ,adc.database_name from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

  15:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id) qi

  16:          WHERE   qi.AvailabilityGroup = qo.AvailabilityGroup

  17:          ORDER BY database_name  FOR XML PATH ('')

  18:          ) qi(Databases) join sys.availability_replicas ar on ar.group_id=qo.group_id

  19:                

  20:  ) Fo

  21:  CROSS APPLY

  22:          (

  23:          SELECT  CASE ROW_NUMBER() OVER(ORDER BY replica_server_name) WHEN 1 THEN '' ELSE ', ' END + Fi.replica_server_name

  24:          FROM    (

  25:  SELECT   qo.*,qi.*,ar.replica_server_name

  26:  FROM    (select distinct ag.name as [AvailabilityGroup], ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

  27:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id) qo

  28:  CROSS APPLY

  29:          (

  30:          SELECT  CASE ROW_NUMBER() OVER(ORDER BY database_name) WHEN 1 THEN '' ELSE ', ' END + qi.database_name

  31:          FROM    (select ag.name as [AvailabilityGroup],ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc ,adc.database_name from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

  32:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id

  33:         ) qi

  34:          WHERE   qi.AvailabilityGroup = qo.AvailabilityGroup

  35:          ORDER BY database_name  FOR XML PATH ('')

  36:          ) qi(Databases) join sys.availability_replicas ar on ar.group_id=qo.group_id

  37:  ) Fi

  38:  WHERE   Fi.AvailabilityGroup = FO.AvailabilityGroup

  39:  ORDER BY replica_server_name  FOR XML PATH ('')

  40:  ) Fi(Replicas)

SQL ALTER DATABASE RESTRICTED Statement

ALTER DATABASE Command:

ALTER DATABASE command as described by Microsoft, a command that modifies a database, or the files and filegroups associated with the database. Adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. Also it can also modify user permissions.
ALTER DATABASE(T-SQL)

I work with Developers who are constantly working on projects in DEV, QA, and PROD. We have been migrating from SQL 2008 to SQL 2012. Whenever a database is migrated, it is set to 
Setting Database to Single User Mode:

This setting is very useful when you are trying to get exclusive access to database to do the following

• Restoring Database
• Deleting Databases
• Changing Containment Type (SQL 2012)
• Changing Recovery Model
• Making changes to database to get exclusive access

The format is very simple

SQL ALTER DATABASE SINGLE USER Statement

I usually run into using Alter statements in my daily DBA work but cannot end up remembering commands (DBA ADHD). So decided to write about them and share which will help others and also help me memorize.

ALTER DATABASE Command:

ALTER DATABASE command as described by Microsoft, a command that modifies a database, or the files and filegroups associated with the database. Adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. Also it can also modify user permissions.
ALTER DATABASE(T-SQL)

I use it in following scenarios

Setting Database to Single User Mode:

This setting is very useful when you are trying to get exclusive access to database to do the following

• Restoring Database
• Deleting Databases
• Changing Containment Type (SQL 2012)
• Changing Recovery Model
• Making changes to database to get exclusive access

The format is very simple

ALTER DATABASE <database_name> 

SET SINGLE USER 

This will prevent any new connections to be made but any active connections will block SINGLE USER command. Sometimes there are background processes that will block this command indefinitely. The command can be issued with NO_WAIT but it will fail if there are active connections and will not cause indefinite blocking. Other will option is

ALTER DATABASE <database_name> 

SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

ROLLBACK IMMEDIATE will rollback any transactions which are running on the SQL Server database and bring it to single user mode. Basically it will drop all connections at the same time and anything that has not been committed will be dropped. I would recommend contacting your users before issuing this command to prevent any data loss.

If you know there are transaction that will be completed in certain amount of time, a time can be added to be allowed to be blocked and then be executed after it is reached.

ALTER DATABASE <database_name> 

SET SINGLE_USER WITH ROLLBACK AFTER 120 SECONDS;

ROOKIE MISTAKE: I have made  rookie mistakes with SINGLE USER command and hoping that you don’t have to deal with it. Always try to issue ALTER COMMAND along with the change you are making to prevent any application taking over your single user connection and blocking you. For example

USE [master]

ALTER DATABASE [database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [database_name] FROM  DISK = N'F:\database_name.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5

I have had this happen to me when I was trying to restore production database. I set the database to SINGLE USER mode and then try to issue restore command. The application that use the database made the connection within a second and I lost access to it. I had to figure out which session was making connection to the database using sp_who and sp_who2. You can also use Adam Mechanic sp_whoisactive to find the session. As I killed one session, another session sprung up within a second. I had to request system administrator to shut the application to prevent it from happening.

When you are completed with restoring, database can be changed to multi user mode by issuing

USE [master]

ALTER DATABASE [database_name]

SET MULTI_USER;

GO

I hope this is helpful to you and please feel free to add your comments.

Thanks!

SQL Availability Group deletion issues – SQL 2012

 

Today I learned a valuable lesson about deleting SQL Availability Groups in SQL 2012 running on Windows 2008 R2 (2 node cluster). Luckily I was working in DEV environment. Basically we started having issues with Availability Group (SQLAG_D). The AG listener was not working so I decided (don’t do it this way) to delete the AG from Windows Cluster. This went fine and I was about to recreate SQLAG_D. Everything looked perfect and then at the end, I got the error

TITLE: Microsoft SQL Server Management Studio
Creating availability group resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

ADDITIONAL INFORMATION:
Create failed for Availability Group 'SQLAG_D'. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The availability group 'SQLAG_D' already exists. This error could be caused by a previous failed CREATE AVAILABILITY GROUP or DROP AVAILABILITY GROUP operation. If the availability group name you specified is correct, try dropping the availability group and then retry CREATE AVAILABILITY GROUP operation.

Failed to create availability group 'SQLAG_D'. The operation encountered SQL Server error 41042 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry CREATE AVAILABILITY GROUP command. (Microsoft SQL Server, Error: 41042)

I tried the following

• Looked for SQLAG_D on both nodes and didn’t find anything
• Looked in Failover Cluster Admin and still no signs
• Looked in sys.availability_group and still no SQLAG_D

I tried to create it again and it failed with same error. At this point, I posted my question on dba.stackexchange.com SQL AG Group Issue and got good feedback. An answer from Kin resolve my issues

 

You have to do following :

1. Drop the availability group then
2. using regedit, (first backup the registry of old availability group that starts withHKEY_LOCAL_MACHINE\Cluster\HadrAgNameToldMap) and then delete that key and
3. Finally create a new Availability Group with new name.

Note: Extra Caution .. as you are dealing with Windows Registry, Back it up first

Thanks to All SQL DBA community. You guys are amazing.

Issue encountered with Idera SQLdm installation

 

We currently use Idera  SQL Diagnostic Manager to monitor our SQL instances. I got my laptop from the company running Windows 7 and pre-imaged. Although every time I tried to install SQLdm, ran into same issue. It started with installation fine but then suddenly fail within few seconds. Looked through Windows Error Logs but couldn’t determine anything till I noticed one thing in error logs. It kept on pointing out faulting application IDriver.exe is the main culprit.

I researched on the internet and couldn’t figure out what would be the main reason. After multiple hours of research, I told a snapshot of the machine and decided to rename C:\PROGRAM~2\COMMON~1\INSTAL~1\Driver\1150 to C:\PROGRAM~2\COMMON~1\INSTAL~1\Driver\1150-1. I started SQLdm install again and it created a new folder and the install was successful. I am open to suggestions if there is another way to resolve this.

Thanks!

One Feature I really like in SSMS 2012 – Close Existing Connections

I always used to get frustrated whenever I was doing restores in SSMS 2008 and previous versions where I would get blocked because databases had connections and I cannot get exclusive lock. I would have to script out and add

1. Set Single User with rollback immediate
2. Restore database
3. Set database to multiuser mode

With SSMS 2012 GUI, they already include the option to drop all connections and run the restore(Basically setting single user and multiuser) . It runs pretty seamless and worth it.

This Feature works with SQL 2012, SQL 2008 R2, SQL 2008, and SQL 2005. I haven’t much opportunity to test on SQL 2000 and prior versions. This option didn’t come up for

Here is a screenshot

 

Thanks!