SQL Gateway Storage

SQL Gateway Storage provides deduplication storage for the gateway component in Microsoft SQL Server using only a configured DbConnection.

Usage

Both System.Data.SqlClient and Microsoft.Data.SqlClient are supported. Using the ConnectionBuilder method allows creating and configuring the connection using the desired package.

By default, the component uses a default schema and table name of [dbo].[GatewayDeduplication].

var gatewayConfiguration = new SqlGatewayDeduplicationConfiguration();
gatewayConfiguration.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new SqlConnection(connectionString);
    });

var gatewaySettings = endpointConfiguration.Gateway(gatewayConfiguration);

Token-credentials

Microsoft Entra ID authentication is supported via the standard connection string options.

Customizing schema and table name

The following code shows how to customize the schema and table name:

var gatewayConfiguration = new SqlGatewayDeduplicationConfiguration();
gatewayConfiguration.Schema = "custom_schema";
gatewayConfiguration.TableName = "CustomTableName";
gatewayConfiguration.ConnectionBuilder(
    connectionBuilder: () =>
    {
        return new SqlConnection(connectionString);
    });

var gatewaySettings = endpointConfiguration.Gateway(gatewayConfiguration);

Using the endpoint name

By including the name of the endpoint in the table name, the resulting table can mimic the pattern used by table names in the SQL persister.

gatewayConfiguration.TableName = $"{endpointName}_GatewayDeduplication";

Table creation

The deduplication table will be created if installers are enabled, which is useful during development.

In controlled environments the creation of the table should be scripted and executed by a user with rights to create database schemas.

The following script can be used to create the deduplication table, replacing the schema/table name if necessary:

if not exists (
	select * from sys.objects
	where
		object_id = object_id('[dbo].[GatewayDeduplication]')
		and type = 'U'
)
begin

	create table [dbo].[GatewayDeduplication] (
		Id nvarchar(255) not null primary key clustered,
		TimeReceived datetime null
	)
end
if not exists (
	select *
	from sys.indexes
	where
		name = 'Index_TimeReceived'
		and object_id = object_id('[dbo].[GatewayDeduplication]')
)
begin
	create index Index_TimeReceived
	on [dbo].[GatewayDeduplication] (TimeReceived asc)
end

Cleaning up old records

After a certain amount of time, duplicates are no longer likely and deduplication data should be cleaned up. However, the SQL gateway storage component provides no built-in mechanism to do this. Duplication data should be cleaned by an outside process like SQL Agent.

A script similar to the following will delete records in batches to prevent excessive database locking:

declare @BatchSize int = 5000
declare @ReceivedBefore datetime = dateadd(day, -7, getutcdate())

while 1=1
begin

	set rowcount @BatchSize
	delete from [dbo].[GatewayDeduplication]
	where TimeReceived < @ReceivedBefore

	if @@ROWCOUNT < @BatchSize
		break;
end