Validate your BizTalk Availability Groups

An installation using SQL Server Always On Availability Groups synchronizes the databases between replicas. It is important that the databases in the different replicas are synchronized. Since transactional consistence is a priority for BizTalk Server Synchronous commit is a requirement. SQL Server 2016 introduced support for distributed transactions but only at the database level. BizTalk Server databases must be separated into at least 4 availability groups with databases divided based on how BizTalk uses distributed transactions internally. Other constraints are DTC support enabled, Synchronous commit used for all replicas and backup preference must be set to primary. When we do health checks we collect information and validate the results. To get an overview of the involved availability groups we have a PowerShell script to collect information and do most of the validations.

The script

Run the script AG-Report.ps1. On a BizTalk Server, the script can get group information from Registry, from another server send parameters.

Download the script: AG-Report

Parameter Comment Required
outputFolder The folder where the file aginfo_yyyyMMddhhss.html will be saved. Yes
mgmtServer The name of BizTalk Management listener. If you don’t provide this parameter management server and DB will be retrieved from “HKLM:\SOFTWARE\Microsoft\BizTalk Server\3.0\Administration” No
mgmtDatabase The name of BizTalk Management database, default “BizTalkMgmtDb” No

The data collection report is saved to a html file.

As always:

The script is licensed “as-is.” You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. Test it in safe environment and change it to fit your requirements.

The report

Output of the report with some explanations.

AG-Report

Availability group

One row for each availability group.

Column Description
Name Name of the availability group
ListenerName Network name of the availability group
Health Health status of synchronization
IpFromCluster Address information from the cluster
DtcSupport Must be true and tells if the AG is configured to support distributed transactions
BackupPreference Must be primary and tells on which replicas backups are taken.

Replicas

One row for each replica in the availability group

Column Description
ServerName Local servername of the replica
Role Current role in the availability group
Health Health status of synchronization for this replica
Mode Tells how data is synchronized for this replica. Must be synchronous commit
MDOP MDOP setting for this replica
LinkedServers List of linked servers for this replica. This setting should be the same on all replicas in the group but might be different if the instance is used for other purposes. A diff renders a warning.
JobsPrimaryCheckFail Checks if the SQL type steps have the if statement to ensure that the step will only run on the primary replica. OK or a list of jobs failing.
SecondaryRead Behavior of this replica when being in secondary role. ALL means readable.

Databases

One row for each database in this AG and can include non-BizTalk databases.

Column Description
DatabaseName Name of the database
Status Online status of the database
Health Health status of synchronization for this database
IsSuspended Tells if synchronization is suspended for this database. Generally, it should be False
IsJoined Has the database started to synchronize? Should be True

Generating restore scripts for BizTalk Server – Version 4

I have created a new version of my PowerShell script to generate restore scripts for BizTalk Server. The basic idea is the same and you can read more about it in my previous post here

I have rewritten the script and done some changes.

Download the script RestoreScriptForBizTalkJob.4.0.7

Support for Availability groups

With the release of BizTalk Server 2016 we can use SQL Server availability groups. The previous version of the script didn’t work with availability groups. I have added a function that checks if the running server is the primary replica or not in an availability group to return true and false if it is a secondary replica. The script just exits if it is a secondary replica.

Script parameters

The new script is intended to be called from the job step using parameters to the file.

Parameter Default value Comment
ServerName   Name of the SQL Server including instance containing BizTalk Management database i.e “Server01\myinstance”.

Note: If you use the listener name in an AG you will need to add the instance name.

Mandatory

MgmtDb BizTalkMgmtDb Name of BizTalk Management database

Not mandatory

SqlScriptDestination NULL By default, the script stores the scripts in the full backup folder, use this parameter to redirect to another folder.

Not mandatory

 

Removed loading SQL Server PowerShell provider

The script does not load the SQL Server PowerShell provider, the reason is that I just wanted the script to be a bit cleaner.

Check if database exists

This version checks if the database exists before setting it to single user mode to avoid unnecessary error messages while recovering.

Tested scenarios

This version of the script has been tested with:

  • BizTalk 2016 using SQL Server 2016 with Availability groups
  • BizTalk 2016 using SQL Server 2016
  • BizTalk Server 2013 R2 using SQL Server 2014

I have tested both generation of the scripts and recovery.

Reminders

When you recover BizTalk server databases it is all or nothing. All the databases for the group that are included in the backup job must be restored together and to the same transaction mark.

While recovering, ensure that you have stopped all activity in your BizTalk group.

The script is licensed “as-is.” You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. Test it in safe environment and change it to fit your requirements.

Using BizTalk Management REST APIs from PowerShell

FirstLookAtRestAPI.Module

With BizTalk Server 2016 Feature Pack 1 we have a lot of nice functionality to use. The feature I just couldn’t keep my hands from was the Management REST API, it gives a fantastic opportunity to create new tailored tools. Since I love PowerShell my first thought was how to use this new feature in my scripting.

Installation and configuration

The installation and configuration of the Feature Pack was straightforward. It’s well described here, I just followed the steps and had no problems at all. Navigating to http://yourserver/BizTalkManagementService/swagger/ will show you the available APIs.

01-Using BizTalk Management REST APIs from PowerShell

Start scripting

I thought, keep it simple and started with a simple script to get all the applications in my environment. Hey, that was deadly simple!

02-Using BizTalk Management REST APIs from PowerShell

So, next step would be to do something a bit more complex, create a new application since that means that I need to send data. We instantiate a new PsCustomObject setting some properties we need, convert it to Json and then post.

03-Using BizTalk Management REST APIs from PowerShell

Conclusion

The Management REST API is a good addition to our toolbox. With just some simple PowerShell cmdlets, Invoke-RestMethod and ConvertTo-Json, we can do a lot. I created a small PowerShell module that handles applications as a sample. You can download it as a starter. Happy scripting!

Setting up my BizTalk Server 2016 using Availability groups LAB

 This document summarizes my experience setting up my first BizTalk Server 2016 using SQL Server availability groups. The document is based on the description in https://msdn.microsoft.com/en-us/library/mt743081.aspx

Note: Some security settings that I used in this lab would never make to a real environment and were used just to move forward during the limited time I had.

Note: Here you have a tool to do basic validation of your configuration: Validate your BizTalk Availability Groups.

1.    Installing the servers

I installed Windows 2016 servers. I added the servers to my lab domain that I have on my local Hyper-V environment.

2.    Creating the Cluster

I created a two-node cluster. The process is quite straight forward and had no issues here.

  1. Added names and the addresses to use to my DNS. Cluag for the cluster and the others for my listerners.
    clip_image002[4]
  2. Add Failover Clustering Feature to the servers.
    clip_image004[4]
  3. Create the cluster
    clip_image006[4]
    clip_image008[4]
  4. Configure File Share Witness pointing to a share on the DC.
    clip_image010[4]
    clip_image012[4]

 

3.    Installing SQL Server

From the documentation

Make sure to have at least four different SQL instances which will host the various BizTalk databases. The secondary replicas should also be set up on different SQL instances. This results in a minimum of 8 SQL instances (1 primary and 1 secondary replica for each of the 4 instances), and a minimum of 4 Availability Groups. See the above illustration for this Availability Group configuration. Make sure Availability Groups are created with the Per Database DTC Support option as this cannot be changed later.

3.1.  Installing and configuring SQL Server

1.       Install 4 SQL instances. In my case, MGMT, MBOX, DTA and SSO

a.       Ensure to have the protocols enabled as usual.
Note: Had an issue with SQL Server Agent needing Shared memory enabled to work. This should not be a requirement and is not recommended in a BizTalk environment, I prioritized to continue since this is a lab environment. Later I found out that I needed to set HostServer setting for SQL Server agent to work without shared memory.

b.       I configured the same Service accounts on all the database server instances. This saves a lot of security configuration job giving the service accounts from the different servers access to each other as needed.

2.       Enable Availability group feature in SQL Server Configuration Manager

clip_image014[4]clip_image016[4]

3.2.  Prepare the AG creation scripts

The required Per Database DTC Support option cannot be applied from the Microsoft SQL Server Management Studio (13.0.16100.1) wizards so the creation must be done in script.

The easiest way to create the Ags is to have a database to synchronize. The BizTalk Databases are created while configuring which is a bit late for SQL. Create an empty database on each database server instance, in my case AGTest. You need to take a backup of each of them before running the scripts. The upside of this is that you will be able to test the AGs before starting to configure BizTalk.

1.       Create a share that can be accessed from all the servers, you will need it to startup synchronization. I created one share with one folder for each database server instance.

2.       Run the wizard to generate the Create the Availability groups script
clip_image018[4]

3.       Specify a name for the AG clip_image020[4] 

4.       Select your test databaseclip_image022[4]

5.       On the Replicas Tab: The local Server instance is automatically added.Add the Secondary Server, specify automatic failover and Synchronous commit. Also, make the replicas readable, if you don’t the jobs will fail on the secondary servers which pollutes the logs.clip_image024[4]

6.       On the Endpoints Tab: Change the default port (5022) I change them to match my IP numbers.
When you have several instances using the default endpoint port 5022 will only work on the first instance. Note: on my version of SQL Management Studio the port number in the generated script was the default even if I changed it.
clip_image026[4]

7.       On the Backup Preferences tab: I Selected Primary.
Note: I don’t know if it is a requirement that Backups should be performed on the primary replica. My AG knowledge is not good enough here but primary sounded safe.
clip_image028[4]

8.       On the Listener tab:Add a listener name and IP address from the ones you prepared previously. I selected the default SQL port 1433. clip_image030[4] 

9.       Move to the next page. Decide how to handle the start of synchronization. Now we use the share we created previously. Note: I don’t see that something else than full is reasonable for us.
clip_image032[4]

10.   Now a validation is performed and if it is ok we move on and on the summary page select to get the script.
clip_image034[4]

11.   Select cancel to move further to use the script.

Created one script for each AG changing DTC_SUPPORT from none to PER_DB instance names and ports used. 
— YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.

:Connect SERVER01\MGMT

 

USE [master]

GO

 

CREATE ENDPOINT [Hadr_endpoint]

            AS TCP (LISTENER_PORT = 5050)

            FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)

GO

 

IF (SELECT state FROM sys.endpoints WHERE name = N’Hadr_endpoint’) 0

BEGIN

            ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED

END

GO

 

use [master]

GO

 

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\SA-BTS-SVC]

GO

 

:Connect SERVER01\MGMT

 

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=‘AlwaysOn_health’)

BEGIN

  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);

END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=‘AlwaysOn_health’)

BEGIN

  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;

END

GO

 

:Connect SERVER02\MGMT

USE [master]

GO

CREATE ENDPOINT [Hadr_endpoint]

            AS TCP (LISTENER_PORT = 5050)

            FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)

GO

 

IF (SELECT state FROM sys.endpoints WHERE name = N’Hadr_endpoint’) 0

BEGIN

            ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED

END

GO

use [master]

GO

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [MYDOMAIN\SA-BTS-SVC]

GO

 

:Connect SERVER02\MGMT

 

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name=‘AlwaysOn_health’)

BEGIN

  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);

END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name=‘AlwaysOn_health’)

BEGIN

  ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;

END

 

GO

 

:Connect SERVER01\MGMT

 

USE [master]

GO

 

CREATE AVAILABILITY GROUP [AGMGMT]

WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY,

DB_FAILOVER = ON,

DTC_SUPPORT = PER_DB) — Changed from none to PER_DB

FOR DATABASE [AGTest]

REPLICA ON N’SERVER01\MGMT’ WITH (ENDPOINT_URL = N’TCP://SERVER01.mydomain.local:5050′, FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL)),

            N’SERVER02\MGMT’ WITH (ENDPOINT_URL = N’TCP://SERVER02.mydomain.local:5050′, FAILOVER_MODE = AUTOMATIC, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

 

GO

 

:Connect SERVER01\MGMT

 

USE [master]

 

GO

 

ALTER AVAILABILITY GROUP [AGMGMT]

ADD LISTENER N’AGMGMT’ (

WITH IP

((N’10.100.100.50′, N’255.255.255.0′)

)

, PORT=1433);

 

GO

 

:Connect SERVER02\MGMT

 

ALTER AVAILABILITY GROUP [AGMGMT] JOIN;

 

GO

 

:Connect SERVER01\MGMT

 

BACKUP DATABASE [AGTest] TO  DISK = N’\\SERVER01\AG-Backup\MGMT\AGTest.bak’ WITH  COPY_ONLY, FORMAT, INIT, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

 

GO

 

:Connect SERVER02\MGMT

 

RESTORE DATABASE [AGTest] FROM  DISK = N’\\SERVER01\AG-Backup\MGMT\AGTest.bak’ WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

 

GO

 

:Connect SERVER01\MGMT

 

BACKUP LOG [AGTest] TO  DISK = N’\\SERVER01\AG-Backup\MGMT\AGTest_Startup.trn’ WITH NOFORMAT, NOINIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 5

 

GO

 

:Connect SERVER02\MGMT

 

RESTORE LOG [AGTest] FROM  DISK = N’\\SERVER01\AG-Backup\MGMT\AGTest_Startup.trn’ WITH  NORECOVERY,  NOUNLOAD,  STATS = 5

 

GO

 

:Connect SERVER02\MGMT

 

 

— Wait for the replica to start communicating

begin try

declare @conn bit

declare @count int

declare @replica_id uniqueidentifier

declare @group_id uniqueidentifier

set @conn = 0

set @count = 30 — wait for 5 minutes

 

if (serverproperty(‘IsHadrEnabled’) = 1)

            and (isnull((select member_state from master.sys.dm_hadr_cluster_members where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty(‘ComputerNamePhysicalNetBIOS’) as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) 0)

            and (isnull((select state from master.sys.database_mirroring_endpoints), 1) = 0)

begin

    select @group_id = ags.group_id from master.sys.availability_groups as ags where name = N’AGMGMT’

            select @replica_id = replicas.replica_id from master.sys.availability_replicas as replicas where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

            while @conn 1 and @count > 0

            begin

                         set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

                         if @conn = 1

                         begin

                                     — exit loop when the replica is connected, or if the query cannot find the replica status

                                     break

                         end

                         waitfor delay ’00:00:10′

                         set @count = @count 1

            end

end

end try

begin catch

            — If the wait loop fails, do not stop execution of the alter database statement

end catch

ALTER DATABASE [AGTest] SET HADR AVAILABILITY GROUP = [AGMGMT];

 

GO

 

 

3.3.  Create the Availability Groups

1.       Open the scripts

2.       Set the Query to SQLCMD mode
Note: The generated scripts must run in SQLCMD mode
clip_image036[4]

3.       Run the scripts one by one

4.       Review the created roles and resources in the cluster.
clip_image038[4]

5.       Test to fail over and back all the roles.

4.    Install and configure BizTalk Server

4.1.  Install and prepare

The installation procedures are the same as any normal BizTalk Server Installation. Review Set up and install prerequisites for BizTalk Server 2016 and Install BizTalk Server 2016.

5.    Configuring BizTalk Server

When configuring BizTalk Server and specifying the SQL server name, use the Availability Group’s listener name instead of the actual machine name. This creates the BizTalk databases, logins, linked servers and SQL Agent jobs on the current primary replica as local resources.

clip_image040[4]

clip_image042[4]

 

 

clip_image044[4]

clip_image046[4]

The resulting linked servers:

Server instance

Linked to

MGMT

DTA, SSO and MBOX

MBOX

DTA and MBOX

DTA

None

SSO

None

clip_image048[4]

6.    Join the BizTalk Databases to the availability groups

 

1.       Configure the backup job and run the job once so you have a full backup which is a requirement to join the AG.
Note: If you don’t take a full backup the configuration will fail.

2.       Stop BizTalk processing (Host Instances, SSO Service, IIS, Rules Engine Update Service, BAMAlerts Service, and so on), and stop the SQL Agent Jobs.

3.       Now add BIzTalk databases to the respective Availability Groups.
clip_image050[4]

4.       Select databases to join

clip_image052[4]

5.       Select initial data synchronization FULL and the location to a share that is accessible to both servers.
clip_image054[4]

6.       Connect to the secondary replica in the AG.
clip_image056[4]

7.       Step forward until joined.

8.       Enclose body of all SQL Agent job steps within IF block to make sure they run only if the target is the primary replica.
clip_image058[4]

clip_image060[4]

Note: I used a new version of RestoreScriptForBizTalkJob.ps1 (4.0.5) to generate restore scripts that internally implements this functionality.

9.       Script Linked servers, Script Logins and SQL Agent Jobs to replicate them on corresponding replica. I used the Export-LoginsAndJobs.ps1 from the DR fast start to export logins and jobs, linked servers exported manually.

10.   Start all services and review that the jobs work as expected.

 

Generate restore scripts for BizTalk Server backups

The BizTalk backup process

A BizTalk group’s data consists of a number of databases with data moving between them. To keep a consistent state when taking a backup, the job uses a marked transaction. A description of that process can be found here. Every time the backup job runs some rows are added to the adm_BackupHistory table in the BizTalkMgmtDb database containing information like the MarkName and files. This information is used by BizTalk Log Shipping to keep track of backups.

BizTalk Log Shipping

BizTalk Log Shipping is the only fully supported way of disaster recovery. When backups are taken SQL Server jobs will restore them to a passive server. You can find a description of BizTalk Log Shipping here and how to configure it here. There might be situations when you need to manually restore the databases. This process is both tedious and error prone, especially during a critical situation.

The task explained

You have a number of backup files of data and logs and need to restore manually. When restoring the last transaction logs, you must use the STOPATMARK argument to keep the databases in a consistent state. What can we do to be prepared in this situation? Having a script prepared to restore is a good way. The best moment to generate the script is directly after we have taken the backups. The idea is to add an extra step to the backup job that generates a restore script based on the information in the adm_BackupHistory table, using the same information as the BizTalk Log Shipping.

Note: You should never change the logic of the BizTalk Server jobs; in this case we’re augmenting the process without changing what the backup job does.

The script

How it works

What do the script need to do to create the restore script?

  • Get last mark name from adm_BackupHistory
  • Get last full backup file information from adm_BackupHistory
  • Loop all databases
    • Generate full database restore script for the current database
    • Get log file information for the current database from adm_BackupHistory
    • Generate log restore script for the current database using with no recovery for all except for the last one which have the current mark. For the file with the current mark generate script using STOPATMARK.
Configure the script

In the top of the script you have three variables that might need to be updated.

Variable Comment
$ServerName Database Server name where BizTalk management database is located. If it is located on a named instance the instance name must be provided.
$MgmtDb The database name for the BizTalk management database.
$SqlScriptDestination Default set to $null, then the generated scripts will be stored in the same location as the full backups. Set a path if you want the scripts saved on a different location.

clip_image002

Getting the script in place

1. Add an extra step to the backup job of PowerShell type. Add your version of the script in the command field or the full path to the PowerShell script.
Note: If you have implemented Log Shipping you must use a file since the script is longer than the 3200 characters that the Log Shipping process will copy.
clip_image004

2. Change the ”Clear Backup History” step to go to next step on success. Also set the new step to quit responding success or failure.
clip_image006

The resulting SQL scripts

For each backup a new set of script files is created, one file for each database instance that is included in the environment. The naming is “Server_Instance” (Green rectangle in the picture), “Markname of starting full backup” (Blue) and then the “last mark” (Red).

clip_image008

clip_image010
Each database will be set to single user mode and close all open connections. All the databases in the backup have a restore database using with move in the expression so you have the possibility to change the location of the database files. Every restore of logs have NORECOVERY except the last one that use STOPATMARK to stop at the transaction mark.

Restore process

When you are in the process of restoring and have an environment with no activity, I.e. all BizTalk Services and SQL Server agent turned off, you can run the scripts on the database instances where you want to restore. The scripts can be edited to have a different file location if needed. If you’re moving databases to another server don’t forget the normal procedure running the UpdateDatabase.vbs script with and updated SampleUpdateinfo.xml file.

The script is licensed “as-is.” You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. Test it in safe environment and change it to fit your requirements.
Download the code and description 

Note:
This version only supports writing the full path to the script in the jobstep.
This version don’t work with Availability groups, a new post is coming soon.