Walkthrough: Configuring Always On for the xDB Collection database

Current version: 10.3

You can use Always On to achieve high availability (HA) for the xDB Collection database with an on-prem SQL Server. This walkthrough describes how to:

Set up and configure Always On
Synchronize login Security Identifiers
Update shard server names in the ShardsGlobal table
Update the xConnect connection strings

Note

You can also configure Always On for the XP service databases and the Core role databases.

Set up and configure Always On

To set up and configure Always On:

Refer to Getting Started with Always On Availability Groups on docs.microsoft.com.

Keep the following in mind when setting up availability groups:

Ensure that you are using synchronous commit mode when you create secondary replicas.
You must configure Availability Group listeners.
The xDB Collection database is actually made up of multiple databases, which include a shard map manager and a shard set. Depending on your edition of SQL Server, you can set up an availability group for each database or an availability group that includes all databases.

By default, all xDB Collection databases have a single user named collection_user. As part of configuring Always On, you must synchronize the user's Security Identifier (SID) between the primary and secondary replicas.

To synchronize the user's SIDs between primary and secondary replicas:

Get the collection_user SID from the primary replica by running the following query against the SQL Master database:
RequestResponseshell
```
SELECT @@SERVERNAME SERVERNAME, name, sid 
FROM sys.server_principals 
WHERE name='collection_user'; -- Specify login name
```
Delete the collection_user from the secondary replica.

Re-create the collection_user login for the secondary replica using the SID from the primary replica.

RequestResponseshell

CREATE LOGIN [collection_user] -- Specify login name
WITH 
PASSWORD=N'Password12345'
-- Use the SID retrieved from the Primary Replica
, SID = 0x9AE4CE9DC738EC4BA1968FF2DF9DD787
, DEFAULT_DATABASE = [master]

Update shard server names in the ShardsGlobal table

The shard map manager's __ShardManagement.ShardsGlobal table stores information about each shard in the shard set, including the shard database name and server.

To update a shard's server name:

Change the value of the ServerName field from the server name to the availability group listener. For example, change the value from SQLServer-ABCD to tcp:Shard0Listener, 5025. Repeat for all shards in the __ShardManagement.ShardsGlobal table.

Update the xConnect connection strings

Finally, you must update the connection strings of all roles that reference the xDB Collection database.

To update the xConnect connection strings:

In the <root>\App_Config\ConnectionStrings.config file, change the collection connection string to reference the shard map manager replica's listener. In the following example, the listener is tcp:CollectionList,5025:

RequestResponseshell

<connectionStrings>
        <add name="collection" connectionString="user id=exampleuser;password=SXglRtUUQYVJwUT9OLfL;data source=tcp:CollectionList,5025;Initial Catalog=sample_Xdb.Collection.ShardMapManager" />
</connectionStrings>

Walkthrough: Configuring Always On for the xDB Collection database

Set up and configure Always On

Synchronize login Security Identifiers

Update shard server names in the ShardsGlobal table

Update the xConnect connection strings