Citrix MetaFrame, SQL Server, and the DBA

Introduction

I first learned about Citrix and the company's software in 1998.  A

query about the possibilities of thin client computing came across my desk when

I was a technical team lead and I thought then the possibility was there, but I

wasn't quite sure if it was "the answer."  It's now four years

later and I find myself having supported Citrix MetaFrame as a DBA for nearly a

year.  I've seen us transition from MetaFrame 1.8 to XP.  And in that

time, I've seen how Citrix has become invaluable for our given

environment.  It isn't "the answer" in all cases, but where it's

designed to go, it is certainly among the strongest choices available.  My

goal in this article is to show why using SQL Server with MetaFrame is a good

idea, and also to give a real world case of how a DBA can save the day.  My

intent isn't to sell Citrix MetaFrame to anyone as I'm going to look at it from

the perspective of a DBA. 

For those who aren't familiar with MetaFrame, I'll give a brief

synopsis.  In the days before client-server, mainframes were the rule with

dumb terminals for communication.  The processing occurred on the mainframe

and the dumb terminal served to get input from a keyboard and display output to

a monitor.  Then came the world of client-server and distributed

computing.  Now we're seeing the pendulum swing back... except we're still

working conceptually in a client-server world.  Windows applications are

published in a model where the work is being done on a Windows Terminal Server

host and a client sends keyboard and mouse strokes while receiving screen

updates.  Getting anymore into what Citrix MetaFrame is really becomes a

sales pitch and I'd advise anyone to check out Citrix's homepage at http://www.citrix.com

or visit a thin client community site like http://www.thinplanet.com

for more information.

How Citrix MetaFrame Uses SQL Server

MetaFrame works on the concept of server farms.  Servers service

multiple users by providing applications to them.  The load for all

applications is distributed across all servers in the farm (with the appropriate

add-on software).  Users are able to use the applications essentially as if

they were running on their desktop.  As a result, print capabilities and

the like are usually provided as well.  And as with any Windows NT kernel

OS, security has to be considered.  Citrix MetaFrame has to store all this

information about applications, printers, and security somewhere.  That

somewhere is called a data store which also stores the relevant licensing

information for the Citrix MetaFrame installation.

What is used as the data store is an organization's choice.  Microsoft

Access, Microsoft SQL Server, and Oracle are all approved.  However, if one

starts talking about farms distributed across an enterprise, Access isn't

recommended.  This is also the case with large farm environments (greater

than 50 servers).   SQL Server is certainly more reliable than

Microsoft Access.  It has capabilities to replicate data (which is why SQL

Server is recommended for distributed farms) built-in.  The backup-restore

method is quick, reliable, and easy.  And chances are a company leveraging

Microsoft technology is also leveraging SQL Server.  As a result, SQL

Server is recommended for any configuration.

In a MetaFrame server farm, there is at least one server that serves the role

of "data collector."  A data collector receives all the

information about published applications, security, and printers from all the

servers in a given zone.  It also keeps track of sessions and application

use for all servers in the zone.  Zones are logical groupings of Citrix

MetaFrame servers usually based on geographical location.  A data collector

in a given zone will also be the server responsible for communicating with data

collectors in other zones.  This prevents every server from trying to

communicate with every other server.  All the information collected is kept

in the data store.  And this is where SQL Server comes in if it's chosen as

the back-end.  It holds all the information which makes Citrix MetaFrame

work.  It's a critical piece to the whole configuration.  In the Advanced

Concepts for MetaFrame XP guide, a short list is given:

• Published application configurations
• Servers in the farm
• Administrators for the farm
• Trust relationships
• Licenses.

Farm servers can be set up to access the data store indirectly or

directly.  If they do indirect access (to a local Access database on the

data collector), they go through the data collector (as the holder of the

database).  If they use direct access, they'll open a connection to the

data store and communicate with it.  Citrix MetaFrame servers access the

data store upon startup and at a set interval to check for changes.  This

interval is 10 minutes by default, but can be changed by modifying a registry

key.  SQL Server operating in direct mode is a good choice to service these

requests.  Due to the architecture of Citrix MetaFrame, Access can support

more than 50 servers, but obviously the other considerations cause one to lean

more to an enterprise-class database solution.

I've stated the data store is critical.  Let me define how

critical.  If a Citrix MetaFrame server is unable to contact the data store

for 48 hours, it will lose its licensing.  For a SQL Server DBA, 48 hours

seems like a lifetime, especially with a good backup and restore

methodology.  We're frequently looking how to get databases back in less

than a hour, and certainly not more than a handful.  So 48 hours is a

luxury.  Also, without the data store, only limited updates can be made and

they won't propagate to other servers in the farm until the data store is

restored.  Incidentally, updates are also stalled if the database and/or

log has no room to grow (which makes sense).  With that said, database

storage requirements is relatively low: about 20 MB of data for every 100

servers.

Now, if the Microsoft Access option is chosen, there is a command dsmaint

backup that can be run to create an on-line backup.  In addition,

whenever the data collector is brought down gracefully (actually the IMA service

on the data collector), the Access database functioning as the data store is

backed up, compacted, and copied over to another file name.  This ensures a

backup is kept, even in the Access environment.  However, if the data

collector isn't brought down often, scripting  dsmaint backup to run

on a regular basis becomes a necessity.

The Role of the DBA

Since the data store is critical for the operation of the Citrix MetaFrame

farm, the DBA becomes an essential person in the support staff.  The

primary responsibility of the DBA is to do backups and restores. 

Unfortunately, there's not a whole lot more for the DBA to do.  Since

MetaFrame is a third- party application, DBAs don't serve a role in the schema

design.  Looking at the IMA database for a Citrix MetaFrame XP farm, we see

only the following tables:

• DATATABLE
• INDEXTABLE
• KEYTABLE

The Citrix MetaFrame manuals warn not to try and manipulate the data in the

data store except with the Citrix IMA tools.  A quick look at the data

confirms why.  If we take KEYTABLE, we'll find a field title rdn,

which is a varbinary column.  Most all of the MetaFrame data store

information is in binary format and masked from a DBA's prying eyes.

So what role does a DBA play?  This isn't an application which can be

performance tuned, because the data is not decipherable.  The amount of

data is relatively small; it is certainly more in the Microsoft Access or MSDE

range.  There are no opportunities to offer suggestions on the

architecture, if for no other reason than it's a third-party product.  The

DBA is pretty much left to take backups and do restores as well as build the

appropriate maintenance plans.  That is, unless the farm is spread over

multiple geographic locations.

In this case, the Citrix data store can be distributed across multiple SQL

Server and kept in sync using transactional replication.  The Advanced

Concepts for Citrix Metaframe guide devotes twelve pages to getting

replication setup properly.  The key is to use Immediate-Updating

subscriptions, meaning a two-phased commitment between Publisher and

Subscriber.  This may create some latency if a Citrix administrator is

making changes, but it ensures that all zones are kept up to date, which is

mandatory.  Since Citrix MetaFrame has its own replication scripts which

are run by the dsmaint utility, coordination with the Citrix

Administrator is a necessity.  SQL Server's built-in functionality gives us

more options in the Citrix MetaFrame world.

A Simple Backup-Restore Rescue

Citrix MetaFrame gives a lot of control to the Citrix Administrator.  If

the Citrix Administrator isn't careful, the data store can get ruined (not in

the sense of being corrupted but rather overwritten by incorrect data as

submitted by the Administrator) easily.  The wrong application could be

unpublished, printer mappings could be undone, etc.  Since the Citrix

Administrator performed a valid operation so far as MetaFrame is concerned, none

of the MetaFrame utilities which operate on the data store will undo the

changes.  It's at this point where the DBA armed with a valid backup can

save a ton of work.

One such case happened this past Friday.  My current job is as a server

team lead, but I still hold some DBA responsibilities, one of them being for the

MetaFrame farm.  My team had just worked back to back 16 hour days with

Microsoft and Compaq on-site to assist with a SAN.  The last thing any of

us needed was to have to stay at work late another night.  The Citrix

Administrators are part of a different group, but if they run into issues, we

support them, just as they support us.  As a result, we were ready t get

home just as soon as we could.

A call came in about 1 PM indicating users were no longer able to print

through Citrix.  Strange error messages previously unseen were occurring

when connected users tried to print.  When new users connected, they

weren't getting printer mappings at all.  The Citrix Management Console

showed the printers, but no mappings to printer drivers.  Things were not

looking good.

Since it wasn't ascertained what was causing the problem, the Citrix

administrators tried to troubleshoot with their toolset.  This went on for

several hours with nothing working and both teams were tired.  At 6 PM a

Citrix Administrator gave me a call and asked when the latest backup had run

prior to the onset of the issue.  It had run at noon.  Since changes

to the MetaFrame farm are very infrequent and the size of the database is small,

we perform a full backup of the database every 8 hours.  The backups are

time-stamped to reflect when they were taken.  No changes should have

happened between 12:00 PM and 12:50 PM, so this was a good backup to use.

The first thing I did was log in to data store using Query Analyzer. 

Knowing I needed to get the database into single-user mode and also the

MetaFrame servers were connecting directly to the data store, I knew I would

have to kill the connections.  Thankfully, the refresh interval was still

set to the default of ten minutes and I was able to disconnect the Citrix

servers and start the backup running.  Failing this, I would have ended up

denying logins by the specific user account in order to get exclusive access to

the database.  Since the Citrix IMA service will automatically connect to

the data store based on the interval, denying logins for the user account is

probably the easiest way to solve this issue.

It took a few minutes, but the database restore completed successfully. 

Since the data is predominantly binary, all we could do was verify the restore

had completed successfully.  A Citrix Administrator went back into the

Citrix Management Console and saw the mappings had reappeared.  Something

had definitely altered the data store, and exactly what is still an open

question.  However, the printer mappings were back and that was the

important thing.  Users logged out of Citrix, logged back in, and had their

printers mapped as they did prior to 12:50 PM.  They were then able to

print successfully.  The noon backup had saved the day.

Concluding Remarks

SQL Server continues to prove its worth in the enterprise.  Citrix

MetaFrame can use it as a data store and such implementations are of great

practical value.  SQL Server offers stability and data recovery, two

important pieces to a successful Citrix MetaFrame installation.  While

there's typically not a lot of responsibilities for a DBA when dealing with

MetaFrame, the responsibilities are indeed important.  Without the data

store, Citrix MetaFrame servers lose licensing after 48 hours.  The data

store is the heart of any successful Citrix MetaFrame installation.  As a

result, the basic DBA tasks are magnified with respect to this

application.  Citrix MetaFrame is like most other applications: take care

of the basics and most of the headaches are avoided.  Such was the case

this Friday when a simple restore undid changes that had essentially crippled

the farm.  Untold hours of work were avoided when the printer mappings came

back.  I've simplified some of how MetaFrame operates, but I think I've

covered the basic gist.  If your organization has a Citrix farm in place

and is still using Access or if your organization is considering going with a

Citrix farm, certainly consider SQL Server as the choice for keeping the data

store.