---
title: "Walkthrough: Configuring Always On for the xDB Collection database"
slug: "walkthrough--configuring-always-on-for-the-xdb-collection-database"
abstract: "How to configure Always On for the xDB Collection database."
---
# Walkthrough: Configuring Always On for the xDB Collection database

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](https://doc.sitecore.com/xp/en/developers/91/platform-administration-and-architecture/configure-sql-server-always-on-for-the-core,-master,-web,-and-reporting-databases.html "Configure SQL Server Always On for the Core, Master, Web, and Reporting databases").

<details>
<summary><h2>Set up and configure Always On</h2></summary>

To set up and configure Always On:

- Refer to [Getting Started with Always On Availability Groups](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/getting-started-with-always-on-availability-groups-sql-server?view=sqlallproducts-allversions) on docs.microsoft.com.

Keep the following in mind when setting up availability groups:

- Ensure that you are using [synchronous commit mode](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-2017#SyncCommitWithAuto) when you create secondary replicas.
- You must [configure Availability Group listeners](https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-ver15).
- 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.

</details>

<details>
<summary><h2>Synchronize login Security Identifiers</h2></summary>

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:

1. Get the `collection_user` SID from the primary replica by running the following query against the SQL Master database:

    ```csharp
    SELECT @@SERVERNAME SERVERNAME, name, sid
    FROM sys.server_principals
    WHERE name='collection_user'; -- Specify login name
    ```

2. Delete the *collection_user* from the secondary replica.
3. Re-create the *collection_user* login for the secondary replica using the SID from the primary replica.

    ```

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

</details>

<details>
<summary><h2>Update shard server names in the ShardsGlobal table</h2></summary>

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.

</details>

<details>
<summary><h2>Update the xConnect connection strings</h2></summary>

Finally, you must update the connection strings of [all roles that reference the xDB Collection database](https://doc.sitecore.com/xp/en/developers/91/platform-administration-and-architecture/xdb-collection-database.html "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`:

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

</details>