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.
Known Issues
CTX110534 -
SQL Server 2005 Configuration for Resource Manager Summary Database
Transactional Replication Issue (Resolved in SQL 2005 SP2)
Additional Resources
CTX107708 –
Tuning IMA Data Store Setting to Improve Resiliency
CTX106684 –
IMA Kill SQL Script
Improved Data Security in SQL 2005
Source
Linear Mode
