Citrix Presentation Server and Microsoft SQL 2005 ConfigurationPost title

Citrix Presentation Server and MS SQL 2005 Configuration

The following steps below detail the best practices for creating the Citrix Presentation Server data store on SQL 2005.

1. Start SQL Server Management Studio.

2. Right-click Database Folders\New Database.

3. Enter the name of the database.

4. Enter the size of the initial database files to be created. To avoid database fragmentation, it is recommended to use the following formula:

Database Size = Number of Total planned Citrix servers X 1 MB

Two hundred servers should result in the initial size of 200 MB. The Transaction log should be at least 10 percent of this initial value. For maximum performance, it is recommended these files not be located on the system drive, nor should they be located together on the same drive.

5. The primary database options that should be set are as follows. 
Note: This is usually the default.

a. Auto Close = False
b. Auto Create Statistic = True
c. Auto Shrink = False
d. Auto Statistics = True

6. The Recovery Option depends on your backup strategy. The default is Full, which basically means you are responsible for backing up and purging the database’s transaction logs. 
Note: If database maintenance is not performed, the transaction log will grow until it is out of disk space. Once this occurs, no changes can be committed to the database until the transaction log is cleared. Full recovery mode allows for point in time recovery and provides the highest level of safe guarding the database. To reduce administrative over head the recovery model can be set to Simple, but this means that you are now dependent on the last backup of the database. For more information, refer to Overview of the Recovery Models.

7. Create a user for Citrix Presentation Server. When creating a user for the IMA data store, this user must have database owner (DBO) rights to the database. The reason for this is that all objects are created with this ownership automatically. 
Note: Failure to due so results in the database objects not being created.

8. Under the Security\Logins folder in the SQL Server Management Studio, right-click and select New Login. SQL Server Authentication or Windows Authentication can be used. If Windows Authentication is used, it is recommend that the account has the PASSWORD NEVER EXPIRES setting or you will need to change the password on every Citrix server connecting to the database when it expires. SQL Server 2005 now supports password policies on SQL Authentication accounts. It is also recommend for the same reason to avoid setting Enforce password expiration.

SQL Performance Tuning for Citrix Presentation Server

On large Citrix server farms (100+ servers), it is recommended that the tempdb size be increased over the default size to at least the maximum size this database has reached in the environment. It is not uncommon to have tempdb set to 2 gigabytes to prevent fragmentation.

Another option is to restart SQL Server more frequently to control the size. The tempdb is rebuilt every time SQL restarts as it serves as a scratch pad area for SQL. Citrix server doesn’t use this directly, but over time can grow. You can monitor the size by selecting tempdb > Summary tab >Report > Disk Usage. (This requires report services to be installed.)

For more information, see Working with tempdb in SQL Server 2005

Monitoring Connection to SQL 2005 Server from the Citrix Server

You can verify Presentation Server IMA Connections to the database from SQL Server Management Studio by going to Management > Activity Monitor. Click the Filter icon and enter Citrix IMA for Application.

Verifying SQL 2005 Connections from Citrix Presentation Server Side

To verify the Citrix server has an active connection to the data store , run qfarm from the command line with no parameters. If connected to the database, the output should list all the servers in the farm.

If the database is down, nothing is displayed.

You can also use Performance Monitor to track database connectivity in Resource Manager or Performance Monitor.

Object - Citrix MetaFrame Presentation Server
Counter - Data Store Connection Failure

Database Maintenance Plan Step-by-Step Setup

A database maintenance plan allows you to automate database administrative tasks. In SQL 2005, unlike SQL 2000, a maintenance plan can only have one schedule associated with it.

1. Before creating a Database maintenance plan, verify SQL Agent is running.

2. In SQL Server Management Studio\Management\Maintenance Plans, right-click and select Maintenance Plan Wizard.

3. Enter the connection information.

The following tasks are an example of what can be automated.

4. A schedule needs to be defined in order to automatically run the tasks.

5. Once completed, the different tasks can be viewed graphically under the Maintenance Plans folder.

Backup / Restoring the Data Store

Backup

To do a full backup of the database,complete the following actions in SQL Server Management Studio.

1. Select the database and right click.

2. Select Tasks\Backup.

3. Click OK to use the default setting. Backups can be performed while the database is online.

Restoring over the original database

You cannot restore the database when there are open connections. You must kill all open connections before restoring the database.

1. Connect with SQL Server Management Studio, open a new query.

2. Pause SQL Server to prevent new connections.

3. Use the following script to kill all IMA connections: CTX106684 – IMA Kill SQL Script

4. Unpause SQL Server.

Restoring database to a different database or SQL server

1. Select Overwrite the existing database on the Options page to prevent the following error message:
Restore failed for Server xx.x.x.xxx (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing database. (Microsoft.SqlServer.Smo)

2. Verify the Restore path is pointing to a valid location on the target server.

Schema Ownership

Schema ownership is a new feature in SQL 2005 that can help in the administration of searching for database objects.

SQL 2000 objects are searched by user.object then dbo.object, while SQL 2005 searches by userschema.object then dbo.object. The difference is SQL objects need to be explicitly created with dbo or a member of the sysdmin group. For SQL 2005, the default schema is dbo if left undefined.

A good example is Resource Manager. The connection account could be different on the Data Connection Server versus the account that runs the report center. Under SQL 2000 this could pose a problem if the objects were not owned by dbo. This is no longer an issue in SQL 2005.