Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Citrix MetaFrame, SQL Server, and the DBA

By Brian Kelley, (first published: 2002/05/13)

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.

Total article views: 26394 | Views in the last 30 days: 4
 
Related Articles
BLOG

Citrix Presentation Server Becomes XenApp

 Citrix is changing the name of its flagship product from Citrix Presentation Server (CPS) to XenApp...

FORUM

SQL and Citrix Migration

SQL and Citrix Migration

FORUM

Citrix MetaFrame, SQL Server, and the DBA

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/column...

FORUM

MSDE on non Citrix metaframe connect with Citrix

Hello, We installed MSDE on a server. Connecting to the database with an application using MSDE fro...

FORUM

SQL Server 2008 - CItrix environment

SQL Connectivity issue via Citrix Environment

Tags
administration    
miscellaneous    
sql server 7    
strategies    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones