Store subscription data using Microsoft SQL Server 编辑

Note:

This document assumes basic knowledge of MS SQL server and T-SQL queries. Administrators must be comfortable configuring, using, and administering SQL server before attempting to follow this document.

Introduction

ESENT is an embeddable, transactional database engine which Windows can use. All versions of StoreFront support the use of a built in ESENT database by default. They can also connect to a Microsoft SQL server instance if the store is configured to use an SQL connection string.

The main advantage of switching StoreFront to using SQL instead of ESENT is that T-SQL update statements allow you to manage, modify, or delete subscription records. If you use SQL, you do not need to export, modify and re import the entire ESENT subscription data whenever minor changes to the subscription data are performed.

To migrate existing subscription data from ESENT to Microsoft SQL server, the flat ESENT data exported from StoreFront needs to be transformed into an SQL friendly format for bulk import. For new deployments without any new subscription data, this step is not required. The data transformation step is only needed once. This article describes the supported configuration which can be used in all StoreFront versions from version 3.5, which introduced the -STF PowerShell SDK referenced in the article.

Note:

Failures to connect to the SQL server instance used by StoreFront to store the subscription data due to network outages do not render the StoreFront deployment unusable. Outages only result in a temporarily degraded user experience; users cannot add, remove, or view favorite resources until the connection to SQL server is restored. Resources can still be enumerated and launched during the outage. The expected behavior is the same as if the Citrix Subscription Store service were to stop while using ESENT.

Tip:

Resources configured with KEYWORDS:Auto or KEYWORDS:Mandatory behave the same way when using both ESENT or SQL. New SQL subscription records are created automatically when a user first logs on if either KEYWORD is included in the user’s resources.

Advantages of ESENT and SQL server

Disadvantages of ESENT and SQL server

Deployment scenarios

Note:

Each store configured within StoreFront requires either an ESENT database or a Microsoft SQL database if you want to support user subscriptions. The method of storing the subscription data is set at the store level within StoreFront.

Citrix recommended all store databases reside on the same Microsoft SQL server instance to reduce management complexity and reduce the scope for misconfiguration.

Multiple stores can share the same database, provided they are all configured to use the same identical connection string. It does not matter if they use different delivery controllers. The disadvantage of multiple stores sharing a database is that there is no way to tell which store each subscription record corresponds to.

A combination of the two data storage methods is technically possible on a single StoreFront deployment with multiple stores. It is possible to configure one store to use ESENT and another to use SQL. This is not recommended due to increased management complexity and the scope for misconfiguration.

There are four scenarios you can use for storing subscription data in SQL Server:

Scenario 1: Single StoreFront Server or Server Group using ESENT (default)

By default, all versions of StoreFront since version 2.0 use a flat ESENT database to store and replicate subscription data between members of a server group. Each member of the server group maintains an identical copy of the subscription database, which is synced with all other members of the server group. This scenario requires no additional steps to configure. This scenario is suitable for most customers who do not expect frequent changes to Delivery Controller names or do not need to perform frequent management tasks on their subscription data like removing or updating old user subscriptions.

Scenario 2: Single StoreFront Server and a local Microsoft SQL server instance installed

StoreFront uses a locally installed SQL server instance and both components reside on the same server. This scenario is suitable for a simple single StoreFront deployment where customers might need to make frequent changes to Delivery Controller names, or they need to perform frequent management tasks on their subscription data like removing or updating old user subscriptions, but they do not require a high availability StoreFront deployment. Citrix do not recommend this scenario for server groups because it creates a single point of failure on the server group member that hosts the Microsoft SQL database instance. This scenario is not suitable for large enterprise deployments.

Scenario 3: StoreFront server group and a dedicated Microsoft SQL server instance configured for high availability (recommended)

All StoreFront server group members connect to the same dedicated Microsoft SQL server instance or SQL failover cluster. This is the most suitable model for large enterprise deployments where Citrix administrators want to make frequent changes to delivery controller names or want to perform frequent management tasks on their subscription data like removing or updating old user subscriptions and require high availability.

Scenario 4: Multiple StoreFront server groups and a dedicated Microsoft SQL server instance in each data center per server group

Note:

This is an advanced configuration. Only attempt it if you are an experienced SQL server administrator familiar with transaction replication, and you have the necessary skills to deploy it successfully.

This is the same as scenario 3, but extends it to situations where multiple StoreFront server groups are required in different remote data centers. Citrix Administrators may choose to synchronize subscription data between different server groups in the same or different data centers. Each server group in the data center connects to its own dedicated Microsoft SQL server instance for redundancy, failover, and performance. This scenario requires considerable extra Microsoft SQL server configuration and infrastructure. It relies entirely on Microsoft SQL technology to replicate the subscription data and its SQL transactions.

Resources

You can download the following scripts from https://github.com/citrix/sample-scripts/tree/master/storefront to help you:

Configuration scripts

• Set-STFDatabase.ps1 – sets the MS SQL connection string for each Store. Run on the StoreFront server.

• Add-LocalAppPoolAccounts.ps1 – grants the local StoreFront server’s app pools read and write access to the SQL database. Run for scenario 2 on the SQL server.

• Add-RemoteSFAccounts.ps1 – grants the all StoreFront servers in a server group read and write access to the SQL database. Run for scenario 3 on the SQL server.

• Create-StoreSubscriptionsDB-2016.sql – creates the SQL database and schema. Run on the SQL server.

Data transformation and import scripts

• Transform-SubscriptionDataForStore.ps1 – exports and transforms existing subscription data within ESENT into an SQL friendly format for import.

• Create-ImportSubscriptionDataSP.sql – creates a stored procedure to import the data transformed by Transform-SubscriptionDataForStore.ps1. Run this script once on the SQL server after you have created the database schema using Create-StoreSubscriptionsDB-2016.sql.

Configure the StoreFront server’s local security group on the SQL Server

Scenario 2: Single StoreFront Server and a local Microsoft SQL server instance installed

Create a local security group called <SQLServer>\StoreFrontServers on the Microsoft SQL server, and add the virtual accounts for the IIS APPPOOL\DefaultAppPool and IIS APPPOOL\Citrix Receiver for Web to allow the locally installed StoreFront to read and write to SQL. This security group is referenced in the .SQL script that creates the store subscription database schema, so ensure that the group name matches.

You can download the script Add-LocalAppPoolAccounts.ps1 to help you.

Install StoreFront before running the Add-LocalAppPoolAccounts.ps1 script. The script depends on the ability to locate the IIS APPPOOL\Citrix Receiver for Web virtual IIS account, which does not exist until StoreFront has been installed and configured. IIS APPPOOL\DefaultAppPool is created automatically by installing the IIS webserver role.

# Create Local Group for StoreFront servers on DB Server
$LocalGroupName = "StoreFrontServers"
$Description = "Contains StoreFront Server Machine Accounts or StoreFront AppPool Virtual Accounts"

# Check whether the Local Group Exists
if ([ADSI]::Exists("WinNT://$env:ComputerName/$LocalGroupName"))
{
    Write-Host "$LocalGroupName already exists!" -ForegroundColor "Yellow"
}
else
{
Write-Host "Creating $LocalGroupName local security group" -ForegroundColor "Yellow"

# Create Local User Group
$Computer = [ADSI]"WinNT://$env:ComputerName,Computer"
$LocalGroup = $Computer.Create("group",$LocalGroupName)
$LocalGroup.setinfo()
$LocalGroup.description = $Description
$Localgroup.SetInfo()
Write-Host "$LocalGroupName local security group created" -ForegroundColor "Green"
}
$Group = [ADSI]"WinNT://$env:ComputerName/$LocalGroupName,group"

# Add IIS APPPOOL\DefaultAppPool
$objAccount = New-Object System.Security.Principal.NTAccount("IIS APPPOOL\DefaultAppPool")
$StrSID = $objAccount.Translate([System.Security.Principal.SecurityIdentifier])
$DefaultSID = $StrSID.Value

$Account = [ADSI]"WinNT://$DefaultSID"
$Group.Add($Account.Path)

# Add IIS APPPOOL\Citrix Receiver for Web
$objAccount = New-Object System.Security.Principal.NTAccount("IIS APPPOOL\Citrix Receiver for Web")
$StrSID = $objAccount.Translate([System.Security.Principal.SecurityIdentifier])
$WebRSID = $StrSID.Value

$Account = [ADSI]"WinNT://$WebRSID"
$Group.Add($Account.Path)

Write-Host "AppPools added to $LocalGroupName local group" -ForegroundColor "Green"
<!--NeedCopy-->