Versions Compared

Old Version 31

changes.mady.by.user David Rogers

Saved on

compared with

New Version Current

changes.mady.by.user James Overell

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Child pages (Children Display)

Overview

SQL Availability Groups are a great way of providing high availability for the Synergetic database layer with an exact mirror copy of the database located on additional servers on separate hardware, along with auto-failover option. Several clients have actively deployed their Synergetic instance to multi-node clusters providing their organisation with increased server up-time and redundancy. 

...

**Note that the above includes a third node for read-only replica server which is considered optional for data extracts or backups for example. This is not actively used by the Synergetic applications however it may be possible to direct reports traffic to this node with manual configuration. Clients have commonly set up two node clusters without the read-only replica and it is still possible to make the second node readable if required (however additional SQL Server licencing would be required). Regardless of the number of clusters it is recommended to use dynamic quorum and implement either a file share or Azure cloud witness to provide improved SQL cluster health..

Licencing

...

See below sample (from the above link) of a four node AG with Azure Cloud Witness assisting with dynamic quorum vote. Keep in mind that the setup below would require three of the four nodes to have SQL Server licence per core so it is generally more feasible to implement two node AG with SQL Enterprise or with SQL Standard Basic Availability Groups.

...


Server Dependency Synchronisation

Whilst the databases can be configured in a mirrored or warm standby state, there are several design considerations and additional maintenance steps required to ensure that the required server level dependencies. Without these dependencies in place on the active SQL Server, there are some components in of Synergetic that will not function correctly.

The main issues from Synergetic perspective are keeping the server dependencies in sync which are namely:

  • SQL agent jobs

  • dbmail profiles and operators

  • server level logins - It is easier to maintain with v68+ due to the contained database logins but there are still likely to be some server level logins required. 

    • There is one server level login required to send emails (zyoursynergeticdatabasename_ServerLogin), use the following SQL Agent Job on each no

  • server level properties and database trust settings

See here for external recommendations for potential automation of the server level object sync:


Database Settings

Synergetic's database and server level properties and the required settings are as follows (current for v68/v69):

...

  • is_trustworthy_on,                                                  --1

  • suser_sname(owner_sid),                                        --sa

  • is_db_chaining_on,                                                  --0

  • is_auto_shrink_on,                                                   --0

  • is_broker_enabled,                                                  --1

  • snapshot_isolation_state_desc,                               --1

  • is_read_committed_snapshot_on,                           --1

  • compatibility_level,                                                 --130

  • page_verify_option_desc,                                       --CHECKSUM

  • is_auto_update_stats_on,                                       --1

  • is_auto_update_stats_async_on,                             --1

  • is_auto_close_on                                                    --0

...

Query to check DB properties
Code Block
collapselanguagetruesql
--The query below will return values for each database
--Check that the values for the Synergetic databases match the comments, eg. is_trustworthy_on = 1, is_broker_enabled = 1
USE master
GO
select 
  name,
  is_trustworthy_on,        --1
  suser_sname(owner_sid),   --sa
  is_db_chaining_on,        --0
  is_auto_shrink_on,        --0
  is_broker_enabled,        --1
  snapshot_isolation_state_desc, --1
  is_read_committed_snapshot_on, --1
  compatibility_level,           --130
  page_verify_option_desc,       --CHECKSUM
  is_auto_update_stats_on,       --1
  is_auto_update_stats_async_on, --1
  is_auto_close_on               --0
from sys.databases

Synergetic have a standard database restore script/procedure that should be used to ensure the initial defaults are set correctly for any system, regardless of if SQL AGs are implemented or not. When SQL AGs are implemented, some of the above settings need to be set on each node, for example the is_trustworthy_on property will need to be enabled on each node and it can only be changed when the node is active. Please refer to the SQL Agent Job - Synergetic DB Health Check.sql listed under Useful Scripts below.


Failover Support

SQL AG Failover mode can be set to automatic or manual and it is recommended to review this Microsoft Knowledge Base article for the required configuration and conditions that will trigger a failover.

...

The window below is displayed in Synmain when the SQL session drops:Image Removed

...

Click 'Yes' then the warning below is then displayed (as stated above it is best to restart the application if the error above appears):Image Removed

...

If the session can re-connect the message below is displayed then the application can still generally be used without restarting:Image Removed

...


Multi-subnet Failover

The following cluster configuration is required for prompt failover of the Synmain application.

Powershell:
Get-ClusterResource "NETWORK NAME" | Get-ClusterParameter "RegisterAllProvidersIP"

eg: Get-ClusterResource "PRDSYNAGMC_PRDSYNLSNRMC" | Get-ClusterParameter "RegisterAllProvidersIP"

The result should be zero (0) for any multi-subnet DB environments for Synmain to connect quicklyImage Removed

...

Use the corresponding Set-ClusterResource command if required.

...

**The scripts below have been tested on SQL 2016.

Description

Script file

SQL Agent job that can be run on each node to sync the zServerLogin email user account and create the required permission set.

The permission set in the script is valid and confirmed for v68+v69.

  • Script creates SQL Agent Job "Custom: Synergetic - Sync zServerLogin User"

SQL Agent Job - Sync zServerLogin User.sql

SQL Agent job to ensure that Synergetic database has the required properties set - this calls a standard Synergetic function to initialise the database on the server. Alerts are then configured by another script to trigger execution of this job on failover event.

  • Script creates SQL Agent Job "Maintenance: Synergetic Database Initialise"

SQL Agent Job - Synergetic Database Initialise.sql

Four SQL events that indicate a failover event has occurred - this is to send notifications and also trigger the above initialisation job if required.

  • Script created alerts AG Data Movement - Resumed, AG Data Movement - Suspended, AG Role Change to PRIMARY, AG Role Change to SECONDARY

  • The job "AG Role Change to PRIMARY" then needs to be manually changed to trigger execution of the above SQL Agent Job "Maintenance: Synergetic Database Initialise" (refer to the above script). This can be changed from SQL Server Agent > Alerts, edit properties for the alert > Response and then select the required SQL Agent Job to execute

AG Failover Alerts.sql

SQL Agent job that executes a stored procedure to ensure that the required properties exist for the database.

Script checks are valid for v68/v69 properties.

  • Run script on all AG nodes - it will create a stored procedure master.dbo.uspsSynergeticConfigurationChecker and SQL Agent job called "Maintenance: Synergetic DB Health Check"

SQL Agent Job - Synergetic DB Health Check.sql

Script to automate fail back to a preferred node - run the attached job on the primary replica

This script uses a function call to sys.fn_hadr_is_primary_replica which requires SQL 2016+

replace <database name here> with the required Synergetic database and <availabilty group name here> with the AG name.

  • Script creates stored proc master.dbo.[uspSQLAGFailoverToLocalReplica] and "SQL Agent job Maintenance: SQL Availability Group Failover to Preferred Node"

SQL Agent Job - Failover to preferred node.sql


Useful Resources