Walkthrough: Configuring Always On for the xDB Collection database

Abstract

How to configure 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.

Set up and configure Always On

To set up and configure Always On:

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 includes 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.

Synchronize login Security Identifiers

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:

    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]
    

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:

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