Akka.Persistence.SqlServer 1.5.15

The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org. Prefix Reserved
dotnet add package Akka.Persistence.SqlServer --version 1.5.15
NuGet\Install-Package Akka.Persistence.SqlServer -Version 1.5.15
This command is intended to be used within the Package Manager Console in Visual Studio, as it uses the NuGet module's version of Install-Package.
<PackageReference Include="Akka.Persistence.SqlServer" Version="1.5.15" />
For projects that support PackageReference, copy this XML node into the project file to reference the package.
paket add Akka.Persistence.SqlServer --version 1.5.15
#r "nuget: Akka.Persistence.SqlServer, 1.5.15"
#r directive can be used in F# Interactive and Polyglot Notebooks. Copy this into the interactive tool or source code of the script to reference the package.
// Install Akka.Persistence.SqlServer as a Cake Addin
#addin nuget:?package=Akka.Persistence.SqlServer&version=1.5.15

// Install Akka.Persistence.SqlServer as a Cake Tool
#tool nuget:?package=Akka.Persistence.SqlServer&version=1.5.15

Akka.Persistence.SqlServer

Akka Persistence journal and snapshot store backed by SQL Server database.

Configuration

Both journal and snapshot store share the same configuration keys (however they resides in separate scopes, so they are definied distinctly for either journal or snapshot store):

Remember that connection string must be provided separately to Journal and Snapshot Store.

Please also note that unless circuit breaker settings are configured, the defaults from Akka.Persistence will be used. If these defaults are less than the Database connection timeout (default or provided in connection string) and provided command timeout, Warnings will be logged upon initialization of the Journal or Snapshot Store.

akka.persistence{
	journal {
	        plugin = "akka.persistence.journal.sql-server"
		sql-server {
			# qualified type name of the SQL Server persistence journal actor
			class = "Akka.Persistence.SqlServer.Journal.SqlServerJournal, Akka.Persistence.SqlServer"

			# dispatcher used to drive journal actor
			plugin-dispatcher = "akka.actor.default-dispatcher"

			# connection string used for database access
			connection-string = ""

			# default SQL commands timeout
			connection-timeout = 30s

			# SQL server schema name to table corresponding with persistent journal
			schema-name = dbo

			# SQL server table corresponding with persistent journal
			table-name = EventJournal

			# should corresponding journal table be initialized automatically
			auto-initialize = off

			# timestamp provider used for generation of journal entries timestamps
			timestamp-provider = "Akka.Persistence.Sql.Common.Journal.DefaultTimestampProvider, Akka.Persistence.Sql.Common"

			# metadata table
			metadata-table-name = Metadata
			
			# Recommended: change default circuit breaker settings
			# By uncommenting below and using Connection Timeout + Command Timeout
			# circuit-breaker.call-timeout=30s
		}
	}

	snapshot-store {
	        plugin = "akka.persistence.snapshot-store.sql-server"
		sql-server {

			# qualified type name of the SQL Server persistence journal actor
			class = "Akka.Persistence.SqlServer.Snapshot.SqlServerSnapshotStore, Akka.Persistence.SqlServer"

			# dispatcher used to drive journal actor
			plugin-dispatcher = ""akka.actor.default-dispatcher""

			# connection string used for database access
			connection-string = ""

			# default SQL commands timeout
			connection-timeout = 30s

			# SQL server schema name to table corresponding with persistent journal
			schema-name = dbo

			# SQL server table corresponding with persistent journal
			table-name = SnapshotStore

			# should corresponding journal table be initialized automatically
			auto-initialize = off
			
			# Recommended: change default circuit breaker settings
			# By uncommenting below and using Connection Timeout + Command Timeout
			# circuit-breaker.call-timeout=30s
		}
	}
}

Batching journal

Since version 1.1.3 an alternative, experimental type of the journal has been released, known as batching journal. It's optimized for concurrent writes made by multiple persistent actors, thanks to the ability of batching multiple SQL operations to be executed within the same database connection. In some of those situations we've noticed over an order of magnitude in event write speed.

To use batching journal, simply change akka.persistence.journal.sql-server.class to Akka.Persistence.SqlServer.Journal.BatchingSqlServerJournal, Akka.Persistence.SqlServer.

Additionally to the existing settings, batching journal introduces few more:

  • isolation-level to define isolation level for transactions used withing event reads/writes. Possible options: unspecified (default), chaos, read-committed, read-uncommitted, repeatable-read, serializable or snapshot.
  • max-concurrent-operations is used to limit the maximum number of database connections used by this journal. You can use them in situations when you want to partition the same ADO.NET pool between multiple components. Current default: 64.
  • max-batch-size defines the maximum number of SQL operations, that are allowed to be executed using the same connection. When there are more operations, they will chunked into subsequent connections. Current default: 100.
  • max-buffer-size defines maximum buffer capacity for the requests send to a journal. Once buffer gets overflown, a journal will call OnBufferOverflow method. By default it will reject all incoming requests until the buffer space gets freed. You can inherit from BatchingSqlServerJournal and override that method to provide a custom backpressure strategy. Current default: 500 000.

Table Schema

SQL Server persistence plugin defines a default table schema used for journal, snapshot store and metadata table.

CREATE TABLE {your_journal_table_name} (
  Ordering BIGINT IDENTITY(1,1) NOT NULL,
  PersistenceID NVARCHAR(255) NOT NULL,
  SequenceNr BIGINT NOT NULL,
  Timestamp BIGINT NOT NULL,
  IsDeleted BIT NOT NULL,
  Manifest NVARCHAR(500) NOT NULL,
  Payload VARBINARY(MAX) NOT NULL,
  Tags NVARCHAR(100) NULL,
  SerializerId INTEGER NULL
  CONSTRAINT PK_{your_journal_table_name} PRIMARY KEY (Ordering),
  CONSTRAINT QU_{your_journal_table_name} UNIQUE (PersistenceID, SequenceNr)
);

CREATE TABLE {your_snapshot_table_name} (
  PersistenceID NVARCHAR(255) NOT NULL,
  SequenceNr BIGINT NOT NULL,
  Timestamp DATETIME2 NOT NULL,
  Manifest NVARCHAR(500) NOT NULL,
  Snapshot VARBINARY(MAX) NOT NULL,
  SerializerId INTEGER NULL
  CONSTRAINT PK_{your_snapshot_table_name} PRIMARY KEY (PersistenceID, SequenceNr)
);

CREATE TABLE {your_metadata_table_name} (
  PersistenceID NVARCHAR(255) NOT NULL,
  SequenceNr BIGINT NOT NULL,
  CONSTRAINT PK_{your_metadata_table_name} PRIMARY KEY (PersistenceID, SequenceNr)
);

Underneath Akka.Persistence.SqlServer uses a raw ADO.NET commands. You may choose not to use a dedicated built in ones, but to create your own being better fit for your use case. To do so, you have to create your own versions of IJournalQueryBuilder and IJournalQueryMapper (for custom journals) or ISnapshotQueryBuilder and ISnapshotQueryMapper (for custom snapshot store) and then attach inside journal, just like in the example below:

class MyCustomSqlServerJournal: Akka.Persistence.SqlServer.Journal.SqlServerJournal
{
    public MyCustomSqlServerJournal() : base()
    {
        QueryBuilder = new MyCustomJournalQueryBuilder();
        QueryMapper = new MyCustomJournalQueryMapper();
    }
}

Migration

From 1.1.2 to 1.3.1
ALTER TABLE {your_journal_table_name} ADD COLUMN SerializerId INTEGER NULL
ALTER TABLE {your_snapshot_table_name} ADD COLUMN SerializerId INTEGER NULL
From 1.1.0 to 1.1.2
ALTER TABLE {your_journal_table_name} DROP CONSTRAINT PK_{your_journal_table_name};
ALTER TABLE {your_journal_table_name} ADD Ordering BIGINT IDENTITY(1,1) PRIMARY KEY NOT NULL;
ALTER TABLE {your_journal_table_name} ADD Ordering BIGINT IDENTITY(1,1) NOT NULL;
ALTER TABLE {your_journal_table_name} ADD CONSTRAINT PK_EventJournal PRIMARY KEY (Ordering);
ALTER TABLE {your_journal_table_name} ADD CONSTRAINT QU_{your_journal_table_name} UNIQUE (PersistenceID, SequenceNr);
From 1.0.8 to 1.1.0
-- helper function to convert between DATETIME2 and BIGINT as .NET ticks
-- taken from: http://stackoverflow.com/questions/7386634/convert-sql-server-datetime-object-to-bigint-net-ticks
CREATE FUNCTION [dbo].[Ticks] (@dt DATETIME)
RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
DECLARE @year INT = DATEPART(yyyy, @dt)
DECLARE @month INT = DATEPART(mm, @dt)
DECLARE @day INT = DATEPART(dd, @dt)
DECLARE @hour INT = DATEPART(hh, @dt)
DECLARE @min INT = DATEPART(mi, @dt)
DECLARE @sec INT = DATEPART(ss, @dt)

DECLARE @days INT =
    CASE @month - 1
        WHEN 0 THEN 0
        WHEN 1 THEN 31
        WHEN 2 THEN 59
        WHEN 3 THEN 90
        WHEN 4 THEN 120
        WHEN 5 THEN 151
        WHEN 6 THEN 181
        WHEN 7 THEN 212
        WHEN 8 THEN 243
        WHEN 9 THEN 273
        WHEN 10 THEN 304
        WHEN 11 THEN 334
        WHEN 12 THEN 365
    END
    IF  @year % 4 = 0 AND (@year % 100  != 0 OR (@year % 100 = 0 AND @year % 400 = 0)) AND @month > 2 BEGIN
        SET @days = @days + 1
    END
RETURN CONVERT(bigint,
    ((((((((@year - 1) * 365) + ((@year - 1) / 4)) - ((@year - 1) / 100)) + ((@year - 1) / 400)) + @days) + @day) - 1) * 864000000000) +
    ((((@hour * 3600) + CONVERT(bigint, @min) * 60) + CONVERT(bigint, @sec)) * 10000000) + (CONVERT(bigint, DATEPART(ms, @dt)) * CONVERT(bigint,10000));

END;
ALTER TABLE {your_journal_table_name} ADD Timestamp_tmp BIGINT NULL;
UPDATE {your_journal_table_name} SET Timestamp_tmp = dbo.Ticks(Timestamp);
DROP INDEX [IX_EventJournal_Timestamp] ON {your_journal_table_name};
ALTER TABLE {your_journal_table_name} DROP COLUMN Timestamp;
ALTER TABLE {your_journal_table_name} ALTER COLUMN Timestamp_tmp BIGINT NOT NULL;
EXEC sp_RENAME '{your_journal_table_name}.Timestamp_tmp' , 'Timestamp', 'COLUMN';
CREATE NONCLUSTERED INDEX [IX_EventJournal_Timestamp] ON {your_journal_table_name}([Timestamp] ASC);
ALTER TABLE {your_journal_table_name} ADD Tags NVARCHAR(100) NULL;
From 1.0.6 to 1.0.8
CREATE TABLE {your_metadata_table_name} (
  PersistenceID NVARCHAR(255) NOT NULL,
  SequenceNr BIGINT NOT NULL,
  CONSTRAINT PK_Metadata PRIMARY KEY (PersistenceID, SequenceNr)
);

INSERT INTO {your_metadata_table_name} (PersistenceID, SequenceNr)
SELECT PersistenceID, MAX(SequenceNr) as SequenceNr FROM {your_journal_table_name} GROUP BY PersistenceID;

ALTER TABLE {your_journal_table_name} ALTER COLUMN PersistenceID NVARCHAR(255) [NOT NULL];
From 1.0.4 to 1.0.5
ALTER TABLE dbo.EventJournal ADD Timestamp DATETIME2 NOT NULL DEFAULT GETDATE();
ALTER TABLE dbo.EventJournal DROP CONSTRAINT PK_EventJournal;
ALTER TABLE dbo.EventJournal DROP COLUMN CS_PID;
ALTER TABLE dbo.EventJournal ADD CONSTRAINT PK_EventJournal PRIMARY KEY (PersistenceID, SequenceNr);
sp_RENAME 'EventJournal.PayloadType', 'Manifest', 'COLUMN';
sp_RENAME 'SnapshotStore.PayloadType', 'Manifest', 'COLUMN';

Tests

The SqlServer tests are packaged and run as part of the default "All" build task.

In order to run the tests, you must do the following things:

  1. Download and install SQL Server Express 2014 from: http://www.microsoft.com/en-us/server-cloud/Products/sql-server-editions/sql-server-express.aspx
  2. Install SQL Server Express with the default settings.
  3. Create a new user called akkadotnet with the password akkadotnet and give them rights to create new databases on the server.
  4. The default connection string uses the following credentials: Data Source=localhost\SQLEXPRESS;Database=akka_persistence_tests;User Id=akkadotnet; Password=akkadotnet;
  5. A custom app.config file can be used and needs to be placed in the same folder as the dll
Product Compatible and additional computed target framework versions.
.NET net5.0 was computed.  net5.0-windows was computed.  net6.0 was computed.  net6.0-android was computed.  net6.0-ios was computed.  net6.0-maccatalyst was computed.  net6.0-macos was computed.  net6.0-tvos was computed.  net6.0-windows was computed.  net7.0 was computed.  net7.0-android was computed.  net7.0-ios was computed.  net7.0-maccatalyst was computed.  net7.0-macos was computed.  net7.0-tvos was computed.  net7.0-windows was computed.  net8.0 was computed.  net8.0-android was computed.  net8.0-browser was computed.  net8.0-ios was computed.  net8.0-maccatalyst was computed.  net8.0-macos was computed.  net8.0-tvos was computed.  net8.0-windows was computed. 
.NET Core netcoreapp2.0 was computed.  netcoreapp2.1 was computed.  netcoreapp2.2 was computed.  netcoreapp3.0 was computed.  netcoreapp3.1 was computed. 
.NET Standard netstandard2.0 is compatible.  netstandard2.1 was computed. 
.NET Framework net461 was computed.  net462 was computed.  net463 was computed.  net47 was computed.  net471 was computed.  net472 was computed.  net48 was computed.  net481 was computed. 
MonoAndroid monoandroid was computed. 
MonoMac monomac was computed. 
MonoTouch monotouch was computed. 
Tizen tizen40 was computed.  tizen60 was computed. 
Xamarin.iOS xamarinios was computed. 
Xamarin.Mac xamarinmac was computed. 
Xamarin.TVOS xamarintvos was computed. 
Xamarin.WatchOS xamarinwatchos was computed. 
Compatible target framework(s)
Included target framework(s) (in package)
Learn more about Target Frameworks and .NET Standard.

NuGet packages (5)

Showing the top 5 NuGet packages that depend on Akka.Persistence.SqlServer:

Package Downloads
wyvern.entity

Package Description

Akka.Persistence.SqlServer.Hosting The ID prefix of this package has been reserved for one of the owners of this package by NuGet.org.

Akka.Persistence.SqlServer Microsoft.Extensions.Hosting support.

EventSaucing

An event source stack based on NEventStore and Akka

FAkka.Server.Linux

Package Description

FAkka.Shared.Linux

Package Description

GitHub repositories (1)

Showing the top 1 popular GitHub repositories that depend on Akka.Persistence.SqlServer:

Repository Stars
Horusiath/AkkaCQRS
Event sourcing example build on Akka.NET with persistence plugin
Version Downloads Last updated
1.5.15 2,359 1/18/2024
1.5.13 4,967 10/4/2023
1.5.4 31,220 4/25/2023
1.5.3 349 4/21/2023
1.5.2 1,145 4/6/2023
1.5.1 1,218 3/16/2023
1.5.0 2,207 3/2/2023
1.5.0-beta3 708 2/28/2023
1.4.35 89,451 3/24/2022
1.4.32 29,259 1/19/2022
1.4.31 10,301 12/21/2021
1.4.29 426 12/15/2021
1.4.25 19,504 9/9/2021
1.4.18 26,592 5/12/2021
1.4.16 27,804 1/25/2021
1.4.14 4,387 1/5/2021
1.4.13 563 12/29/2020
1.4.11 12,377 11/11/2020
1.4.10 22,478 8/21/2020
1.4.1 36,666 3/11/2020
1.4.1-rc3 434 3/10/2020
1.4.1-rc1 421 3/3/2020
1.3.17 21,163 1/20/2020
1.3.14 20,455 7/30/2019
1.3.13 17,646 4/30/2019
1.3.7 124,643 6/4/2018
1.3.2 21,227 10/29/2017
1.3.1 6,162 9/11/2017
1.1.1.7-beta 40,320 10/27/2016
1.1.1.6-beta 847 10/26/2016
1.1.1.5-beta 1,469 8/4/2016
1.0.8.4-beta 4,205 5/31/2016
1.0.6.3-beta 4,877 1/22/2016
1.0.5.2-beta 1,930 12/10/2015
1.0.4.1-beta 1,479 8/8/2015
1.0.3-beta 1,078 6/12/2015
1.0.2.9-beta 1,048 6/3/2015
1.0.1-pre1504282013 1,220 4/28/2015