How to: Prepare a SQL Server Database for Cluster Configuration Settings (Velocity)

Article
06/24/2009

[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This topic describes the general steps of preparing a SQL Server database so that it can be used to store cluster configuration settings for the Microsoft project code named "Velocity" distributed cache system.

Alternatively, you can instead choose to store your cache cluster configuration settings in a shared network folder. For more information, see How to: Prepare a Shared Network Folder for Cluster Configuration Settings (Velocity).

There are many factors to consider when choosing which storage location is right for your application. For enterprise applications, we highly recommend that you use a SQL Server database for storing cluster configuration settings. For smaller applications or development workstation scenarios, the ease of creating a shared network folder may outweigh the availability benefits of SQL Server. For more information, see Cluster Configuration Storage Options (Velocity).

Note

After performing these steps, we highly recommend that you use SQL Server Management Studio to confirm access to the cache cluster configuration database. Although it may not be possible to do this with the domain computer account identity of the cache server, you may be able to (temporarily) use a security identity that does not already have access to the database. Granting permissions to that security identity as described in step five of the following procedure and then confirming access helps ensure that SQL Server configuration issues do not impede the "Velocity" deployment.

For more detailed information about using SQL Server as the cluster configuration storage location, see SQL Server-Based Cluster Configuration (Velocity).

Preparing a SQL Server database to store the cache cluster configuration settings

Select the appropriate server to host the database and install SQL Server if necessary. Doing this is outside the scope of this documentation. For more information, see Microsoft SQL Server Books Online.
Configure the SQL Server instance so that it can receive connections in a way consistent with corporate guidelines. Doing this is also outside the scope of this documentation. For more information, see Microsoft SQL Server Books Online.
Create the SQL Server database. A small distributed cache system may only need a database of a few MB in size. Installing the database in a location with 50MB of space available should be more than sufficient for even a 100 server cache cluster. For more information about how to do this, see the Microsoft SQL Server Books Online.
Create a SQL Server login for the security identity that will be performing the cache server installations. Be sure to grant this security identity db_owner permissions on the cache cluster configuration database. To create the cache server accounts, this security identity also needs sysadmin permissions on the instance of SQL Server. The second example shows how to set these permissions with a script.
Create a SQL Server login used by the cache server(s) to run the cache host Windows service. This step is done automatically by the "Velocity" installation program for each of the cache servers. By default, "Velocity" uses the Network Service identity, which means that there must be a logon created for each of the domain computer accounts of the cache servers. Each of the cache host SQL Server logins needs db_datareader, db_datawriter, and EXECUTE permissions on the cache cluster configuration database.

Example

The following is an example of the SQL Server permissions granted by the installation program during a cache server installation. The actual Transact-SQL code will vary based on the name of the security identity that is used to run the cache host Windows service on the cache server.

In this example, the name of the cache server is CacheServerComputerName and that server is a member of the corporate domain named CorporateDomainName. The name of the database used to store the cluster configuration settings is CacheConfigDatabase. The value CorporateDomainName\CacheServerComputerName$ is used because the Network Service uses the domain computer account of the cache server to authenticate remotely to the SQL Server. If the cache server installation was occurring on the same physical computer as the SQL Server, this example would have to change and use NT AUTHORITY\Network Service instead of CorporateDomainName\CacheServerComputerName$.

Note

The installation program only supports Windows Authentication when creating SQL Server logins.

-- create logon for cache host service
CREATE LOGIN [CorporateDomainName\CacheServerComputerName$] 
FROM WINDOWS WITH DEFAULT_DATABASE=[CacheConfigDatabase]
GO

-- create user account for cache host service
USE [CacheConfigDatabase]
GO
CREATE USER [CorporateDomainName\CacheServerComputerName$] 
FOR LOGIN [CorporateDomainName\CacheServerComputerName$] 

-- grant permissions for cache host service
EXEC sp_addrolemember N'db_datareader', N'CorporateDomainName\CacheServerComputerName$'
EXEC sp_addrolemember N'db_datawriter', N'CorporateDomainName\CacheServerComputerName$'
GRANT EXECUTE TO [CorporateDomainName\CacheServerComputerName$] 
GO

The following is an example of the SQL Server script that needs to run for the security identity of the person performing the "Velocity" installation. System administrator permissions are required because the installation program attempts to create new accounts in the instance of SQL Server for each of the cache servers.

In this example, the name of the security identity used by the person performing the cache server installations is PersonInstallingCacheServer. That security identity is a member of the corporate domain named CorporateDomainName. The name of the database used to store the cluster configuration settings is CacheConfigDatabase.

-- create login for security identity of person installing
CREATE LOGIN [CorporateDomainName\PersonInstallingCacheServer] 
FROM WINDOWS WITH DEFAULT_DATABASE=[master] 
GO

-- grant sysadmin permissions for security identity of person installing
EXEC master..sp_addsrvrolemember 
     @loginame = N'CorporateDomainName\PersonInstallingCacheServer', 
     @rolename = N'sysadmin'

-- create user account for security identity of person installing
USE [CacheConfigDatabase]
GO
CREATE USER [CorporateDomainName\PersonInstallingCacheServer] 
FOR LOGIN [CorporateDomainName\PersonInstallingCacheServer] 
WITH DEFAULT_SCHEMA=[dbo]

-- grant permissions for security identity of person installing
EXEC sp_addrolemember N'db_owner', N'CorporateDomainName\PersonInstallingCacheServer'
GO

Security

The security identity of the person performing the installation requires sysadmin permissions on the instance of SQL Server because they will be creating accounts for the cache servers when they run the installation program. That security identity also needs db_owner permissions on the cache cluster configuration database. See the second example in this topic for an example of the actions required for this identity.

In order for the cache cluster to function, the security identities used to run the cache host Windows service on each of the cache servers also require SQL Server logins and database-level db_datareader, db_datawriter, and EXECUTE permissions so that they can use the cluster configuration database.

How to: Prepare a SQL Server Database for Cluster Configuration Settings (Velocity)

Preparing a SQL Server database to store the cache cluster configuration settings

Example

Security

See Also

Tasks

Concepts

Other Resources

Additional resources