Author / Samuel Kastberg

Posts by Samuel Kastberg

Integration consultant working Solidify Sweden. My opinions only, most certainly not necessarily those of my employer.
by Samuel Kastberg

Availability Group improvements with SQL Server 2016 SP2 and BizTalk 2016 CU5

SQL Server 2016 Availability Groups has a limitation, two databases involved in a distributed transaction cannot reside in the same SQL Server instance. For a BizTalk Server installation that will be using Availability Groups this means you need to separate the databases in several SQL Server instances, making the installation more complicated than desired.

SQL Server 2016 SP2 adds the functionality that solves this problem, thus two databases involved in a distributed transaction can be in the same SQL Server instance. BizTalk Server 2016 CU5 and FP3 adds support for this functionality. As a result, BizTalk Server 2016 installations using Availability Groups can be configured using less SQL Server instances if desired.

Database location rules when using Availability Groups

Rules for database location when using Availability Groups with BizTalk Server installations.

Version Rules
Prior to SQL Server 2016 – BizTalk Server 2016 Distributed transactions not supported, thus not supported for BizTalk Server.
SQL Server 2016 (RTM-SP1)

BizTalk Server 2016 (RTM-CU4)

Instance Role BizTalk Databases in that group
1 Authentication SSODB
2 Management BizTalkMgmtDb
3 Runtime BizTalkMsgBoxDb
BizTalkRulesEngineDb
BAMPrimaryImport
BAMStarSchema
BAMAlertsApplication
4 Tracking BizTalkDTADb
EsbItineraryDb
EsbExceptionDb
SQL Server 2016 SP2 and later

BizTalk Server 2016 CU5 and later

 Free database placement

 

Note: Other limitations as described in the documentation are still valid.

SQL Server 2016 SP2 https://go.microsoft.com/fwlink/?linkid=869608

BizTalk Server 2016 CU5 https://support.microsoft.com/en-us/help/4132957/cumulative-update-5-for-microsoft-biztalk-server-2016

by Samuel Kastberg

BizTalk Health Monitor Custom Rules and Queries

BizTalk Health Monitor is a valuable tool to validate the current state of a BizTalk Server Environment. I encourage you to use recurrently review warnings and act on the issues. We use it extensively when doing BizTalk Health Checks and working with support cases. A useful feature is the possibility to add custom queries and rules.

If you want to read more about basic functions of BHM visit the BizTalk Health Monitor Team blog
https://blogs.msdn.microsoft.com/biztalkhealthmonitor/
https://blogs.msdn.microsoft.com/biztalkhealthmonitor/2014/06/26/overview-of-biztalk-health-monitor-bhm/

My sample: ExtBHM-CustomExamples

Adding a Custom Query

BHM Queries collect information to which you can apply rules. If you have some information about your solution that you want to include as part of the health review. Custom queries can be of different types, the possibilities are a bit different, the table below enumerates the types and shows how information is presented. The option I used most is PowerShell, generally I create a script that I can create, and test manually and then just add the full path to the script and send parameters.

Type Comment
WMI WQL Select Statement, all properties included on the select will be accessible for rules and display. About WQL
Example:
SELECT AdapterName, HostName, IsDefault FROM MSBTS_SendHandler2 Where ( HostName = ‘TrackingHost’ )ExtBHM-02
SQL SQL Select Statement, all columns included in the Select statement will be accessible for rules and display. Do not query the BizTalk databases. Changes to the BizTalk databases is not advisable and not supported.
Example:
SELECT @@SERVERNAME AS ServerName, name, recovery_model_desc FROM sys.databases
Where database_id > 4ExtBHM-03
VBS VB Script, each line printed to the console will be collected but no additional columns. Validate the line text or row count.
Example:
Set g_objShell = createobject(“Wscript.shell”)
MaxDTCLogSize = g_objShell.RegRead (“HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSDTC\MaxLogSize”)Wscript.Echo MaxDTCLogSize
PowerShell PowerShell Script each line printed to the console will be collected but no additional columns. Validate the line text or row count.
Example:
$QueryString = Gwmi Win32_OperatingSystem -Comp MyComputer
Write-Host $QueryString.CaptionExtBHM-04
BAT A batch of commands each line printed to the console will be collected but no additional columns. Validate the line text or row count.
Example:
ECHO LISTENING && netstat -ano | find /c “LISTENING”
ECHO SYN_SENT && netstat -ano | find /c “SYN_SENT”
ECHO SYN_RCVD && netstat -ano | find /c “SYN_RCVD”
ECHO ESTABLISHED && netstat -ano | find /c “ESTABLISHED”
ECHO FIN_WAIT_1 && netstat -ano | find /c “FIN_WAIT_1”
ECHO CLOSING && netstat -ano | find /c “CLOSING”
ECHO FIN_WAIT_2 && netstat -ano | find /c “FIN_WAIT_2”
ECHO TIME_WAIT && netstat -ano | find /c “TIME_WAIT”ExtBHM-05
CMD Output from one command each line printed to the console will be collected but no additional columns. Validate the line text or row count.
Example:
Tasklist.exe

ExtBHM-01

  1. Navigate to the profile you want to customize, tab Queries
  2. Click on New Query after selecting the type you want to use
  3. Add the Query commands
  4. Set properties like Caption and target
  5. Test and select Ok when done

Targets

Each query has a target which is the name of the server where to run the query. There are some macros to address the query to multiple servers in an environment. You can use ALLBTSVR to run a query on every BizTalk Server by setting the TargetID element in the configuration file.

Note: I have only made it work with PowerShell queries.

Adding a Custom Rule

A rule acts on the data collected with a query. Rules can be set to give a warning on each failing instance or once for all errors. You can have more than one condition for a rule. In a condition you can validate a column value (%GLOBALPROP_REPORTVALUE:##COLNAME##%) or the number or rows generated by the query(%GLOBALPROP_ROWCOUNT%). Actions give you the possibility to act when conditions are met. Actions can use data from the failing rows and have URLs to documentation, i.e. to a wiki with information on what to do.

  1. Navigate to the profile you want to customize, tab Rules
  2. Write a name and add description comments. Set evaluation rows and trigger actions.
    ExtBHM-06
  3. Select Commit changes
  4. Select and edit the default condition
    ExtBHM-07
  5. Select Commit changes
  6. Add more conditions if needed
    ExtBHM-10
  7. Edit the default action to match your requirements
    ExtBHM-08
  8. Select Commit changes
  9. Test and Save
    ExtBHM-09

Activating the rules

Now that we have rules we need to activate them by going to the Information Level tab and select the custom queries we want to have in the profile.

ExtBHM-11

Moving the profile to another environment

The profile configuration files are stored under %ProgramData%\Microsoft\BizTalkHealthMonitor as xml files with a name starting with MBVSETTINGS_. Simply edit the file to match the names on the new environment and save it in the new location.

by Samuel Kastberg

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-1.1.0  Version 1.1.0 Add functionality to take into account SQL Server 2016 SP2 and BizTalk 2016 CU5 improvements.

AG-Report-1.0.9  Version 1.0.9 fixes an issue when using ports as part of the litener name.

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
by Samuel Kastberg

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 with some fixes for failed backups in the chain and secondary replica not readable RestoreScriptForBizTalkJob.4.1.0 

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.

by Samuel Kastberg

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!

by Samuel Kastberg

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:
Read about important improvements here Availability Group improvements with SQL Server 2016 SP2 and BizTalk 2016 CU5
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 modeclip_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.

 

by Samuel Kastberg

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.