High Availability and Disaster Recover

Alwayson Availability Group Replica is Not Synchronizing (Error: 976, Severity: 14, State: 1.)

tech-coffee-sql-server2
For some reason you can lose communication between the primary and secondary replicas.

As known, there is a policy on SQL Server to check the data synchronization state of the database replica. The policy is in an unhealthy state when the data synchronization state is NOT SYNCHRONIZING or the state is not SYNCHRONIZED for the synchronous-commit database replica.

After investigating the sql server errorlog, I got the following msg:

The target database, ‘YourDatabase’, is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. (Microsoft SQL Server, Error: 976)

According to Technet, this issue can be caused by the following:

  • The availability replica might be disconnected.
  • The data movement might be suspended.
  • The database might not be accessible.
  • There might be a temporary delay issue due to network latency or the load on the primary or secondary replica.

Further information: https://technet.microsoft.com/en-us/library/hh245199.aspx

Resolution:
You can fix it, using the following T-SQL command to force the resume synchronization:

ALTER DATABASE YourDatabase SET HADR RESUME;

 

You can also follow the link: How to resume an AG database.

Check out these related tips:

How to monitor Timeout and Changes Roles from Availability Groups with Alerts
How to Monitor AlwaysON – Primary / Secondary Replicas

Advertisements

Introducing Cloud Witness – [SQL Server]

We know that when we create a Cluster environment between two sites, we must take care about Quorum Witness.
If you need automatic failover SLA in your company, the best way is to separate the (Quorum) FileShare Quitness at a third site. So it is not lost if one of the other two sites has problems.

But, What if you dont have the third site?

Now, It’s possible using Cloud Witness. A new type of Failover Cluster quorum witness with Windows Server 2012 R2.
Follow the link below to an article with steps required to configure it.

http://blogs.msdn.com/b/clustering/archive/2014/11/14/10572766.aspx

How to monitor Timeout and Changes Roles from Availability Groups with Alerts

tech-coffee-sql-server2

Agent alert can send alerts by email when occur some action/timeout between databases in your Availability Group Environment. It allows you to become proactive.

The following script shows how to create these alerts.

EXEC msdb .dbo. sp_add_operator @name =N'AlertTeam',
               @enabled=1 ,
               @weekday_pager_start_time=90000 ,
               @weekday_pager_end_time=180000 ,
               @saturday_pager_start_time=90000 ,
               @saturday_pager_end_time=180000 ,
               @sunday_pager_start_time=90000 ,
               @sunday_pager_end_time=180000 ,
               @pager_days=0 ,
               @email_address=N'email@email.com.br' ,
               @category_name=N'[Uncategorized]'
GO

-- 1480 - AG Role Change (failover)
EXEC msdb .dbo. sp_add_alert
        @name = N'AG Role Change',
        @message_id = 1480,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb .dbo. sp_add_notification
        @alert_name = N'AG Role Change',
        @operator_name = N'AlertTeam',
        @notification_method = 1;
GO

-- 35264 - AG Data Movement - Resumed
EXEC msdb .dbo. sp_add_alert
        @name = N'AG Data Movement - Suspended',
        @message_id = 35264,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb .dbo. sp_add_notification
        @alert_name = N'AG Data Movement - Suspended',
        @operator_name = N'AlertTeam',
        @notification_method = 1;
GO

-- 35265 - AG Data Movement - Resumed
EXEC msdb .dbo. sp_add_alert
        @name = N'AG Data Movement - Resumed',
        @message_id = 35265,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb .dbo. sp_add_notification
        @alert_name = N'AG Data Movement - Resumed',
        @operator_name = N'AlertTeam',
        @notification_method = 1;
GO

-- 35206 - AG Timeout to Secondary Replica
EXEC msdb .dbo . sp_add_alert
        @name = N'AG Timeout to Secondary Replica',
        @message_id = 35206,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb .dbo . sp_add_notification
        @alert_name = N'AG Timeout to Secondary Replica',
        @operator_name = N'AlertTeam',
        @notification_method = 1;
GO
-- 35202 - AG Timeout to Secondary Replica
EXEC msdb .dbo . sp_add_alert
        @name = N'AG Connection has been successfully established',
        @message_id = 35202,
    @severity = 0,
    @enabled = 1,
    @delay_between_responses = 0,
    @include_event_description_in = 1;
GO
EXEC msdb .dbo . sp_add_notification
        @alert_name = N'AG Connection has been successfully established',
        @operator_name = N'AlertTeam',
        @notification_method = 1;

You can also configure to execute another SQL Agent job to perform your required action.
I recommend you to execute a Job with the Stored Procedure inside the link below to show how is the synchronism status.
https://danilocbraga.wordpress.com/2014/10/20/how-to-monitor-alwayson-primary-secondary-replicas/

I hope this post can help

High Availability and Disaster Recover – Syncronize logins [SQL Server]

Often I connect in new environments and I see solutions of High Availability and Disaster Recovery. But just the simple solution is sufficient for your applications?
I’m saying that because in many cases a just simple detail is forgotten. Many professionals forget to create a option to syncronize logins and when you need to use the otherside, application not run and your solution of High Availability failed.

Follow below one example of script to syncronize your environment.

You need to create one LINKEDSERVER between instances and run one job for a specific times or a trigger to execute this jobs when you have a new user or when the user has been modified.

After you need to create a procedure hexadecimal according with your version. Look that and create in both instances: http://support.microsoft.com/kb/918992

And finaly, create the procedure below. Don’t forget to change the name of LINKEDSERVER

This script is simple and just create login without administratives privileges and with the same SID and when password has been modified. If you need sysadmin for example, you will need input manually. I think is better because sometimes you have a diferent instance and maybe you can’t grant acess in a especific server.

BEGIN
SET NOCOUNT ON

	CREATE TABLE #Logins
	(
		loginId int IDENTITY(1, 1) NOT NULL,
		loginName nvarchar(128) NOT NULL,
		passwordHash varbinary(256) NULL,
		default_database_name nvarchar(128) NULL,
		sid varbinary(85) NOT NULL
	)

	INSERT INTO #Logins(loginName, passwordHash, sid, default_database_name)
	SELECT *
	FROM OPENQUERY([INSTANCE], '
	SELECT name, CONVERT(varbinary(256), LOGINPROPERTY(name, ''PasswordHash'')), sid, default_database_name
	FROM master.sys.server_principals
	WHERE
		type = ''S'' AND 
		name NOT IN (''sa'', ''guest'',''##MS_PolicyEventProcessingLogin##'',''usr_alwayson'') AND
		create_date >= ''01/05/2014''
	ORDER BY name')

	DECLARE 
		@count int, @loginId int, @loginName nvarchar(128), 
		@passwordHashOld varbinary(256), @passwordHashNew varbinary(256), 
		@SID_varbinary varbinary(85), @sql nvarchar(4000), @password varchar(514), @default_database_name nvarchar(128), @SID_string varchar (514)

	SELECT @loginId = 1, @count = COUNT(*)
	FROM #Logins

	WHILE @loginId <= @count
	BEGIN
		SELECT @loginName = loginName, @passwordHashNew = passwordHash, @SID_varbinary = sid, @default_database_name = default_database_name
		FROM #Logins
		WHERE loginId = @loginId

		IF NOT EXISTS (SELECT * FROM master.sys.server_principals WHERE name = @loginName)
		BEGIN
			EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT
			EXEC master.dbo.sp_hexadecimal @SID_varbinary,@SID_string OUT

			SET @sql = 'CREATE LOGIN ' + @loginName + ' WITH PASSWORD = '
			SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL')) 
			SET @sql = @sql + ' HASHED , SID = ' + CONVERT(nvarchar(512), @SID_string)
			SET @sql = @sql + ' , DEFAULT_DATABASE = [' + @default_database_name + ']'
			SET @sql = @sql + ' , CHECK_POLICY = OFF' 
			PRINT @sql
			EXEC (@sql)

			PRINT 'login created'
		END
		ELSE
		BEGIN
			SELECT @passwordHashOld = CONVERT(varbinary(256), LOGINPROPERTY(@loginName, 'PasswordHash'))

			-- only bother updating if the password has changed since the last sync
			IF @passwordHashOld <> @passwordHashNew
			BEGIN
				EXEC master.dbo.sp_hexadecimal @passwordHashNew, @password OUTPUT

				--SET @sql = 'DROP LOGIN ' + @loginName
				--PRINT @sql
				--EXEC (@sql)

				SET @sql = 'ALTER LOGIN ' + @loginName + ' WITH PASSWORD = '
				SET @sql = @sql + CONVERT(nvarchar(512), COALESCE(@password, 'NULL'))
				SET @sql = @sql + ' HASHED, CHECK_POLICY = OFF' 
				PRINT @sql
				EXEC (@sql)

				PRINT 'login "altered"'
			END
		END

		SET @loginId = @loginId + 1
	END

	DROP TABLE #Logins

END