Versions Compared

Old Version 23

changes.mady.by.user Steve Lynch (Deactivated)

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 system 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. 

Synergetic recommend engaging with third party SQL Server specialists or Synergetic Professional Services to help with configuration of the platform and , synchronisation of the required server level dependencies . The Synergetic Consulting team have assisted clients with design and implementation of two, three and four node setup of SQL Server Availability Groups based on the individual requirements of the schooland for ongoing support of the cluster. The Synergetic Service Desk can provide assistance with installation of the Synergetic software suite and understanding of the dependencies, but not for advanced SQL Availability Group setup or troubleshooting. See Synergetic Systems Responsibilities for more information on platform support. Please note the setup below is considered an advanced setup and would therefore only be recommended for schools with in-house DBA expertise or ongoing IT managed services.

...

**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

Adequate SQL Server licencing is required, also if the failover nodes are being used for workloads, see here for details:

...

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
collapse
Code Block
languagetruesql
--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.

On a failover event the Synergetic Windows client and Arrival/Departure Terminal do not support automatic resume however prompts are displayed to the user when the software detects the session has failed, asking the user if they would like to re-connect to SQL Server. This is because the Windows client requires a persistent session (and in many cases multiple sessions) to SQL Server. The web applications do not keep persistent sessions open to SQL Server so failover occurs more seamlessly for the user. To cleanly re-establish the sessions it is recommended to restart the application if the connection ever drops from a failover event.

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

...

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

...

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

...


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 quickly

...

Use the corresponding Set-ClusterResource command if required.

The web application configuration files have a 'MultiSubnetFailoverFlag' option that should also be set to true if multi-subnet AGs are used. See SynWeb - SQL Connection Pooling for details.


Other Considerations

Nodes should share the same hardware configuration, notably drive assignment. If synchronous commit is utilised then the server should have identical hardware for performance. Also need to consider network latency between cluster nodes and failover behaviour, in particular if setting synchronous commit with auto failover.

...

**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