DxPlus Testing Guide

DxPlus offers you the ability to boost your availability group replication throughput. DxPlus uses DH2i's patented Zero Trust Network Access (ZTNA) tunneling technology to create secure tunnels between replica servers. These ZTNA tunnels transmit TCP payloads over UDP for superior performance and security. All with direct communication between servers (no middle-man or broker).

This guide provides the testing methods that were used to gather comparison data on standard and boosted availability group replication traffic.

Test Environment

• (1) Server in Ft Collins, Colorado (primary replica)
  • Microsoft Hyper-V Virtual Machine
  • Microsoft Windows Server 2019
  • 2 virtual CPUs
  • 8GB memory
• (2) Servers in Portland, Oregon (secondary replicas)
  • Microsoft Hyper-V Virtual Machines
  • Microsoft Windows Server 2019
  • 2 virtual CPUs
  • 8GB memory
• Microsoft SQL Server 2019 (version 15.0.2000.5)
• Availability group created between all 3 replicas and configured for synchronous commit
• VPN connectivity between sites

Test Database

The publicly available Microsoft AdventureWorks2019 sample database was added to the availability group and then used to create and execute the simulated workload.

• Microsoft OLTP AdventureWorks2019

Simulated Workload

The following T-SQL was used to create a SQL Server query file that inserts 10,000 rows into a tmpCustomer table and performs a commit every 250 rows.

USE AdventureWorks2019
GO

DECLARE @tb SYSNAME = 'tmpCustomer';

SELECT 
  'if(OBJECT_ID(' + QUOTENAME(@tb, '''') + ',''u'') is not null)
DROP table ' + @tb + ';
GO';

SELECT 'CREATE TABLE [dbo].[' + @tb + '](
       [CustomerKey] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
       [FirstName] [nvarchar](50) NULL,
       [LastName] [nvarchar](50) NULL,
       [EmailAddress] [nvarchar](50) NULL,
       [Notes] [nchar](2000) DEFAULT(replicate(''0'',2000))
)
GO';

SELECT 'BEGIN TRANSACTION ';

DECLARE @i INT = 10000;

WITH random
AS (
	SELECT TOP (@i) ROW_NUMBER() OVER (
			ORDER BY (
					SELECT 0
					)
			) AS Row#
		,c1.[FirstName]
		,c2.[LastName]
		,CAST(RAND(CHECKSUM(NEWID())) * 3 AS INT) randomemail
	FROM [dbo].[DimCustomer] c1
	CROSS JOIN DimCustomer c2
	)
SELECT CASE 
		WHEN (convert(INT, Row#) % 250) = 0
			THEN 'COMMIT TRANSACTION BEGIN TRANSACTION '
		ELSE ''
		END + 'INSERT ' + @tb + '(FirstName,LastName,EmailAddress) Values(' + QUOTENAME(Firstname, '''') + ',' + QUOTENAME(Lastname, '''') + ',' + QUOTENAME(CASE 
			WHEN randomemail = 0
				THEN lower(left(FirstName, 1) + [LastName]) + '@hotmail.com'
			WHEN randomemail = 1
				THEN lower(left(FirstName, 1) + [LastName]) + '@gmail.com'
			ELSE lower(left(FirstName, 1) + [LastName]) + '@yahoo.com'
			END, '''') + ') '
FROM random;

SELECT 'COMMIT TRANSACTION ';

tip

For more information on how to save the output of this script to a file:

• 8 Ways to Export SQL Results To a Text File

Test Script

The following PowerShell script was used to run the simulated workload against the primary replica. The script calls the SQL query file generated in the previous step (10k_inserts.sql), executes it 5 times, measures the amount of time it takes to complete each run and then writes the results to a CSV log file.

# Environmental Variables

$SQLServer = "server1\sql2019"
$DB = "AdventureWorks2019"
$User = "sa"
$Pass = "PassW0rd!"
$Query = "10k_inserts.sql"

# Log file

$LogDate = (Get-Date).ToString("yyMMddHHmmss")
$Logfile = "C:\Temp\DxPlus-Test-$LogDate.csv"

# Logging function

Function Write-Log {
    $Stamp = (Get-Date).toString("yyyy/MM/dd HH:mm:ss")
    $Line = "$Stamp" + "," + "$Duration"
    Add-Content $Logfile -Value $Line
}

$Count = 1

# Test Loop

Do {
    $Start = (Get-Date)
    Invoke-Sqlcmd -ServerInstance $SQLServer -Database $DB -InputFile $Query -QueryTimeout 10000 -Username $User -Password $Pass
    $End = (Get-Date)
    $Duration = New-TimeSpan –Start $Start –End $End | Select-object -ExpandProperty TotalMinutes
    Write-Log
    $Count ++
}

Until ($Count -gt 5)

Test Results

The test script above was then run on the primary replica for the availability group with standard endpoints. DxPlus was then installed and used to boost the endpoints using DH2i tunneling technology. The results are as follows:

Run #	Standard Endpoints	Boosted Endpoints	% Difference
1	2.39398066	1.392317682	41.84%
2	2.36427044	1.324741958	43.97%
3	2.409448647	1.39701942	42.02%
4	2.299016413	1.348093127	41.36%
5	2.410946505	1.384490773	42.57%
Averages	2.375532533	1.369332592	42.36%

Additional Information

• DxPlus Admin Guide
• DxPlus DxCLI Admin Guide