SQL Server-Based Cluster Configuration (Windows Server AppFabric Caching)

  • Article

Windows Server AppFabric provides the option of using a SQL Server database to store the cluster configuration settings. The AppFabric Configuration Wizard automatically prepares the target database for managing the cluster configuration. AppFabric then uses this database as the primary point of reference for defining the cache cluster, cache hosts, and named caches. All cache servers in the cluster must be able to access the cluster configuration database.

For more information about preparing a SQL Server database for the cluster configuration storage location, see Windows Server AppFabric Installation Guide (https://go.microsoft.com/fwlink/?LinkId=169172).

During the configuration of the AppFabric caching features, you have the option to specify an existing SQL Server database or you can choose to have the wizard create a new database for you on the specified SQL Server. All cache servers in the cluster must be able to access the cluster configuration database.

Tip

By default, the AppFabric Caching Service does not start automatically. Instead, you must start the service with the Start-CacheCluster command from an administrative Windows Powershell command prompt. However, when SQL Server is used for the configuration store, you can change the service on each cache host to automatically start. You can do this by modifying the properties of the “AppFabric Caching Service” in the Services administrator tool on each cache server to change the startup type to automatic.

Availability Considerations

The cluster configuration storage location can be a single point of failure for your distributed cache system. For this reason, we recommend that you take steps to increase the availability of the SQL Server database that stores the cluster configuration settings. One option is to use Microsoft Windows Server 2008 Failover Clustering (https://go.microsoft.com/fwlink/?LinkId=130692) to host a "clustered" database resource for the cache cluster configuration storage location.

Another option is to use SQL Server Database Mirroring. For more information, see Using Database Mirroring (https://go.microsoft.com/fwlink/?LinkId=190691). Note that to use Database Mirroring, there are three requirements:

  • Add the Failover Partner property to the connection string.

  • Add the machine account for each cache host as a SQL Server login on the partner server. For example, if the machine name is CacheServer1 in the Domain1 domain, the machine account is Domain1\CacheServer1$. This is done automatically for the primary SQL Server, but it must be done manually for the failover partner server.

  • Add the machine account for each cache host as a user in the mirrored configuration database on the partner server. This user should have db_datareader and db_datawriter permissions.

If the cache cluster is already configured, you can modify the connection string manually using Windows PowerShell commands. Use the following steps on each cache host machine:

  1. Open a Windows PowerShell command window, and run the Use-CacheCluster command.

  2. Run the Remove-CacheHost and Remove-CacheAdmin commands.

  3. Run the Add-CacheHost command, specifying the new connection string with the Failover Partner property. For example:

    Add-CacheHost -Provider System.Data.SqlClient -ConnectionString "Data Source=SQLServer1;Initial Catalog=CacheClusterConfigurationDB;Integrated Security=True;Failover Partner=SQLServer2"  -Account "NT Authority\Network Service"

  4. Run the Add-CacheAdmin command, specifying the new connection string with the Failover Partner property. For example:

    Add-CacheAdmin -Provider System.Data.SqlClient -ConnectionString "Data Source=SQLServer1;Initial Catalog=CacheClusterConfigurationDB;Integrated Security=True;Failover Partner=SQLServer2"

Tip

You can download a script, ChangeConnString.ps1, to automate connection string changes across all cache hosts. For more information, see ChangeConnString.ps1 download.

Note

The cache cluster administrator needs to have db_owner permissions on the cache cluster configuration store database in order to run caching commands in Windows PowerShell after a failover.

Permissions

All of the cache hosts must have logon permissions to the database server that hosts the cluster configuration database. The cluster configuration database must also have its security settings configured so that each cache server has a SQL Server login, db_datareader, db_datawriter, and EXECUTE permissions to the database.

Note

The installation program will attempt to configure these permissions automatically. Note that the security identity of the person performing the installation must have db_owner permissions to the SQL Server database, and a SQL Server login to the instance of SQL Server.

If you create a Windows security group to manage access to the cache configuration database, you need only set up SQL Server logon and database permissions once. For example, you could set up a Windows security group named ClusterACacheServers to indicate the cache servers that have permission to become part of cache cluster ClusterA.

After you create the ClusterACacheServers security group, grant the domain computer accounts of the applicable cache servers membership to this group (for example, domain\computername$). Then grant the ClusterACacheServers security group a SQL Server login, db_datawriter, db_datareader, and EXECUTE permissions to the database. After you set this up, every time that you add a server to the cache cluster, you need only add the domain computer account of the new server to the security group.

Database Structure

When you first configure the AppFabric caching features with the SQL Server-based configuration option, the configuration tool creates many tables in the specified database. There are also some stored procedures added for internal operations of the cluster.

Note

The data in the cluster configuration database is not meant to be edited manually. To help ensure continuous operation of the cluster, only the cache hosts and the installation program should write to the database.

Database Security

Use of passwords in connection strings presents a security risk and should be avoided whenever possible. Connection strings are stored in clear text in the cache host configuration file on each cache server, in DistributedCacheService.exe.config. To minimize such risks, use integrated security to make a trusted connection with SQL Server. By using this approach, you do not have to store a password in the connection string. In the absence of integrated security, a clear-text password will be needed in the connection string. The best way to help secure your connection string is as follows, in increasing order of risk:

  1. Use integrated security.

  2. Secure connection strings with passwords and minimize passing around connection strings.

  3. Minimize lifetimes and touch points for all connection strings.

Database Connections

When you use a SQL Server 2005 or later database to store cluster configuration settings, make sure that the server is configured to allow enough concurrent connections to accommodate all cache hosts in the cluster. It is not supported for the number of cache hosts in the cluster to exceed the amount of available concurrent connections.

The server may be configured not to have any limits on concurrent connections, but the database administrator may also configure that setting very low for administrative or other reasons. In such cases, make sure that the server can support additional connections before you add cache hosts to the cache cluster.

See Also

Concepts

Shared Folder-Based Cluster Configuration (Windows Server AppFabric Caching)
Cluster Configuration Settings (Windows Server AppFabric Caching)
Client Configuration Options (Windows Server AppFabric Caching)
Configuring the Cache Cluster (Windows Server AppFabric Caching)
Developing a Cache Client (Windows Server AppFabric Caching)