Using the MSDE to Centralize Your IMA Data Store

As a consultant, I’ve run across several small environments that benefit from Citrix MetaFrame's Load Balancing and Resource Management capabilities.

As a consultant, I’ve run across several small environments that benefit from Citrix MetaFrame's Load Balancing and Resource Management capabilities. These clients are small enough that they have no need for a SQL Server or Oracle database server, but they could benefit from a centralized data store on a non-MetaFrame server.

Solution: MSDE

Since the release of Citrix MetaFrame XP Feature Release 3, Citrix has recommended using an MSDE database in place of Access for a locally stored IMA data store. In fact, it’s now the default option when installing a new MetaFrame farm. But did you know you can actually use the MSDE as a centralized database for your IMA data store and your Resource Manager Summary Databases? This means that you can realize the benefits of a centralized IMA architecture without having to purchase SQL or Oracle.

MSDE stands for "Microsoft SQL Desktop Engine," and it's been around in one form or another for several years. As the name implies, it's based on Microsoft SQL Server, and is in fact so close to SQL that it could be called "SQL Server Lite."

Typically, the MSDE is used for desktop applications that require a more sophisticated DB engine than a simple Jet (Access) database. With a few installation switches, though, the MSDE can be transformed into networkable SQL server that works quite well for small server-based computing environments.

You’re probably thinking, "Man, this is great. How can I shower gifts upon you for eliminating the need for a SQL server in my small business?" Well, don’t send any fruit baskets yet--there is one main drawback.

Since MSDE is so full-featured, Microsoft built in a governor that limits the MSDE server to 5 full-service concurrent connections. Without this governor, companies would be less inclined to go with Microsoft’s "pay-for" database server, SQL Server 2000, since the bulk of the features would be available for free in MSDE. The MSDE server will support more than 5 concurrent connections, but performance is artifically reduced. Microsoft coded this into the MSDE as another way to push heavier MSDE users to the SQL Server 2000 platform.

The good news is that in a MetaFrame XP environment that doesn’t use Resource Manager, each MetaFrame server only connects to the database every 10 minutes. This allows you to feasibly point more than 5 servers to the MSDE server without incurring the performance hit. A Resource Manager environment that uses the MSDE for the Summary Database is a bit more chatty, and it is recommended to upgrade to MS SQL Server 2000 if your Resource Manager environment grows beyond 5 servers.

Pre-Install

While it's possible to configure an MSDE database from a command line, I personally found it a little too MYSQL-ish, and decided to purchase an admin tool that eerily resembles SQL Enterprise Manager (although again "lite"). The tool is called MSDE Admin and is available from http://MSDE.biz for the very acceptable fee of US$22.00. Using the tool is very straightforward, so I'll only cover the command-line method here. If you’d like assistance with the MSDE Admin tool, shoot me an email.

Before we begin, you should probably get MSDE.

Before you jump the gun and do your normal "click Setup, Next, Next, Next, Finish" install, there’s a few more things you’ll need to know.

By default, network access to MSDE is disabled, so the "DISABLENETWORKPROTOCOLS=0" switch needs to be used. In addition, you’ll want to set the SA password. To do that, you’ll need to use the "SAPWD=blahdyblahblah" switch. These can be added to the setup.ini file that comes with the distribution. If you like, you can download a setup.ini file with these options already configured. Just change the SAPWD line to your password and go.

A few more things before we get moving. First, if MSDE is installed on a domain controller, you will not be able to use SQL Authentication. Since this solution is tailored for smaller environments, this article will focus on installing MSDE on a domain controller. And second, a fantastic reference is available from Microsoft that will assist in configuring SQL user accounts in the MSDE. It can be found here.

The Nitty Gritty

Here’s the part we’ve all been waiting for. Step by step instructions to simplify your administration life. Sounds like SPAM doesn’t it?

  1. On a system other than your Citrix servers (that your figured out in your typical pre-rollout documentation….right?), extract the package you downloaded.
  2. Replace the setup.ini file with the one you downloaded and run setup.exe (or drop to a cmd prompt, switch that folder, and run: setup DISABLENETWORKPROTOCOLS=0 SAPWD=LETMEIN ).
  3. After the installation is completed, go to a command prompt and type: osql –E. This will start the MSDE command line interface. Note: If you receive an error saying that the SQL Server does not exist, the service probably isn't started. Type "net start MSSQLSERVER" and then run osql -E again.
  4. At the 1> prompt, enter the following: CREATE DATABASE IMA (or whatever you want to call the IMA datastore.) Note: If you are also going to use Resource Manager, you can create a DB for that at the same time. On the next line, just issue the CREATE DATABASE command again followed by the database name.
  5. Type "Go" to execute the CREATE DATABASE command. After the "Go" command executes, type "Exit" to leave osql.
  6. At the command prompt, type: osql -E -d IMA (or whatever you named your database) and press Enter.
  7. At the 1> prompt, enter: EXEC sp_grantlogin 'domain_name\CTXIMA' . This will grant the specified user access to the MSDE server. You will probably want to create a new user just for accessing the data store. I chose CTXIMA for my user.
  8. Next, we have to grant the user mentioned above access to the new database(s). To do this, execute the command: EXEC sp_grantdbaccess ‘domain_name\CTXIMA', ‘IMA’.
  9. Once you’ve entered in all your lines, type “Go” and the changes will be committed to the db. Once the "Go" command has executed, you can type "Exit" and leave osql.

Note: If you also created an RM database, you will need to follow steps 6 through 9 again to give your user permission to that database.

That’s it, folks! Well, at least for the SQL configuration. “Why would you buy a $22 tool for that,” you might ask? Well, honestly, the first time I did this, I actually configured it by the command line correctly, but thought I didn’t. I essentially paid $22 to prove that I did it right the first time, which is far better than spending 4 more hours trying to do it over and over again.

Now to connect your servers up to your new MSDE SQL database.

When configuring the data store location, either via DSMigrate or through a new install, proceed as if you were connecting to a SQL Database.

  1. Select “Use the following database on a separate database server” option, and select “SQL Server” from the list. Click Next.
  2. Enter a description in the window that pops up, and in the Server field, enter the name or IP of the server where you installed MSDE.
  3. On the next screen, select “With Windows NT authentication using the network logon ID” and click Next.
  4. Select the checkbox next to “Change the default database to:” and select the database IMA (Or whichever db you want to use for your IMA data store). Click Next.
  5. Verify that the default settings are acceptable and click Finish.
  6. Select “Test Data Source” just to put a smile on your face.
  7. Enter the username and password for which you’ve granted access to the database.
  8. Click Next, and continue with your normal MF setup process.

One thing to note: If you find that the IMA service is starting, but you still cannot log in to the CMC, you should check the C:\Program Files\Citrix\Independent Management Architecture\mf20.dsn file for the following line: Trusted_Connection=Yes. If you see this line, remove it, and you should be all set.

Congratulations! You’ve set up your Citrix environment to use a free, centralized data store. The only thing left to do is configure the Summary Database in Resource Manager.

  1. Choose a Citrix server and open up the “Data Sources” control panel applet.
  2. Click on the System DSN tab, and click Add.
  3. Select SQL Server from the list and click Finish.
  4. At this point, you’ll be asked to enter the same information you entered when setting up your IMA data store. For Name, enter rmsummarydatabase. You must enter this name.
  5. Chose whatever you want for the description, and select or enter the servername of your MSDE server. Click Next.
  6. Ensure that “With Windows NT authentication using the network logon id” is selected and click Next.
  7. Change the default master database to the RM database that you specified in OSQL and click Next.
  8. Verify the information on this screen and click Finish.
  9. Again, test the data source if you want a feeling of victory, then click OK. You can close ODBC.
  10. Start up Citrix Management Console, select Resource Manager from the left-hand pane.
  11. Click on the Summary Database tab, then on the Configure button.
  12. Check the “Summary Database enabled” box. 
  13. Select the server name of the main server (since this is the first time configuring the summary database, it will be this server). 
  14. For User, enter the same username used for the IMA data store. It must be in domain\username format. 
  15. Enter the password for the user, and change any other settings you would like. Click Test, just to get one last hit of satisfaction, and you’re done!

That’ll do it. The IMA data store is set up, and the Summary Database is summarizing away.

I’ve only used this solution in a few environments, but it seems to work great. If anyone else out there takes this on, I’d love to hear what you have to say. Did I leave any steps out? Does it perform well in your environment? What other uses can you imagine? You can either post in the comments or send me an email.

Now, about that fruit basket...

Join the conversation

18 comments

Send me notifications when other members comment.

Please create a username to comment.

This message was originally posted by Erik Blom on April 28, 2004
I do a lot of Citrix installs and this is great info! I'm just wondering wether it would be possible to use the SQL enterprise manager on the SQL cd if you want to create/edit/connect to the database. Or maybe you didn't mention this because this isn't a free tool...
Cancel
This message was originally posted by Gabe Knuth on April 28, 2004
I'm not sure if that can be done or not, but if you try and let me know, I'll gladly add it to the article. The tool I mentioned from MSDE.biz is great, though. Totally worth the $22. Thanks for the comment!
Cancel
This message was originally posted by Mike on April 29, 2004
This is good stuff. I do a lot of small farm (under 5 server) installs so this will work out great.

Check out the SQL Server Web Data Administrator - http://tinyurl.com/3cuzt
Its a free download from MS and you can create and edit databases on MSDE.
Cancel
This message was originally posted by Friedrich on May 5, 2004
Thank you for this inspiring stuff. But how about the error 26013? And what's your opinion to the Citrix' KB article CTX102799?
Cancel
This message was originally posted by Gabe Knuth on May 16, 2004
I've not run into that error before when doing this. I'd be interested in finding out the intricacies of the configuration that is getting this error. I've used this a few times in FR3 environments without a problem, so I wonder if there is some other semi-external factor that is causing it. Perhaps it actually is an authentication problem. The tool I've mentioned above might be helpful (I promise I don't work for them, I just really like the program!).
Cancel
This message was originally posted by MikeM on July 14, 2004
I was able to use SQL Enterprise Manager to adminster my MSDE. You have to connect to "ServerName\InstanceName" EX. MYSERVER\CITRIX_METAFRAME and that will connect you to the MSDE.

Hope this helps - thanks for the article.
Cancel
This message was originally posted by Andy on August 12, 2004
http://lab.msdn.microsoft.com/express/sql/Features/default.aspx
New version of MSDE in Beta 2 right now... 1 CPU support, 4GB DB size max limit, and 1GB ram limit, does not state any limitations on number of connections. I am curious when Citrix will officially support this.
Cancel
This message was originally posted by Aaron Harrison on September 3, 2004
A couple of things to try if you get this error -

1) Make sure you are specifying the user as domain\username when running the MF install.

2) Try adding db_owner rights to the user:
osql -E
create database 'IMA'
go
use database 'IMA'
go
sp_grantlogin 'domain\username'
go
sp_grantdbaccess 'domain\username'
go
sp_spaddrolemember 'db_owner','domain\username'
go
quit

Of course, the above assumes you haven't already created the db - just skip the bits you've done already or drop your db and start again.

Cancel
This message was originally posted by Aaron Harrison on September 3, 2004
Apologies for the formatting above - after the word user:, each instance of the word 'go' should be on a line of it's own so hit return before and after it - hope that makes sense :-)
Cancel
This message was originally posted by Mark on November 15, 2004
Hi,

Can anyone tell me if there is a way I can us a different name for the RM Database. I have multiple farms and I'm using a clustered SQL environment to host the dtastores, now I want to use the same cluster to host the Rm database, which naturally causes problems if I can only use the default name of RMSUMMARYDATABASE.

Thanks,

Mark
Cancel
This message was originally posted by ljames18@yahoo.com on December 8, 2004
Anyone tried this on an instance of MSDE already installed by Veritas BackupExec 9.1? I can't seem to authenticate using command line or the SQL Web Data Administrator.
Cancel
This message was originally posted by ljames18@yahoo.com on December 8, 2004
Well, I got MSDE installed and the DBs setup on the new instance, but when I went to install Citrix, the only SQL server it found was the SERVERNAME\MSSQL$BKUPEXEC instance. Worth trying, but I gave up and put it in MS Access.
Cancel
I would install a seperate instance of MSDE (you can even do it on the same box, but I wouldn't try that if I didn't have to). I don't know anything about the Veritas MSDE install, but it could be customized with stuff way over my head.

Also, I wanted to let everyone know that regular old SQL Enterprise Manager can also edit an MSDE database! This way, you get the nice interface rather than the bland MSDE Admin interface. Of course, to have the rights to use SQL Enterprise Manager, you have to have SQL server, so you probably wouldn't be following this howto then, would you?
Cancel
Mark,

In the future, please post technical questions to the forum. This time, though, I'll take care of it here.

You can call the database whatever you want, as long as the DSN that points to the database is called RMSUMMARYDATABASE. This means that you can have RMSDB1, RMSDB2, RMSDB3, ... on your SQL Servers, and have your Citrix silo's broken out however you like. Just change the DSN to point to a different database, leaving the DSN name the same.

If you need something, post in the forum, or if it's specifically about this article, shoot me an email.

Gabe
Cancel
First install MSDE using the SetupMsdeForMetaFrame.cmd file located in the support/msde directory of the metaframe cd. Change this file and install a second instance of MSDE in a new location. Backup exec should be located in c:\prog files\etc etc etc...

"%~dp0MSDE\SETUP.EXE" INSTANCENAME="%INSTANCENAME%" SAPWD="%SAPWD%" DISABLENETWORKPROTOCOLS=0 TARGETDIR="C:\CITRIX\MSDE"

I had to then manually start the MSSQL$CITRIX_METAFRAME service. After that I was able to connect and migrate the datastore as normal.
Cancel
When i issue the command sp_grantdbaccess 'domain\user' i get the following

Msg 15063, Level 16, State 1, Server ServerName, Procedure sp_grantdbaccess, Line
116
The login already has an account under a different user name.

any help is very much appreciated.
Cancel
hi Frnds
Please culd u ppl solve this problem i wanna add all my windows domain users with single "sp_grantdbaccess" query
also i wanna to give all domain user the table privileges
Cancel

sp_spaddrolemember 'db_owner','domain\username'


Just to be clear - that should be just
sp_addrolemember 'db_owner','domain\username'

And it is necessary, at least for me.  I wanted to install the whole IMA stuff on my AD domain controller, which is also the license server for my little farm.  I just didn't want the IMA database stuff on one of my random farm app servers, cause I tend to image them often.  The domain controller/license server is redundant, etc.  The farm app servers aren't.

I tried to install MPS4 on the domain controller and couldn't.  Apparently, as of MPS4, you cannot install it on an AD-Domain Controller.  I didn't really want all of MPS on my DC anyway, but didn't know how else to get the actual IMA database stuff on it.  Then I stumbled across this article (yyay for this website and for google.)

After fussing w/the original set of commands in the article, I still couldn't get MPS4 installed on my basic app server box.  Rereading this article, I came across this particular "db_owner" reply.  I added that to my overall and it now works.

I don't really know much on SQL/MSDE (I actually come from a basic Unix and MySQL background.)  A few stabs and guesses at sp_spaddrolemember later, I got it.

Thanks to all, I think this works now w/a little better IMA-only system.  I dunno how critical the IMA database and stuff is (yet) but I just didn't want to put it on a box that had no redudnancy, and might get imaged and blown away w/out warning.  Maybe that actually doesn't matter to IMA or not.  I guess I'll learn more as time goes on.

Thanks again!
PH

Cancel

-ADS BY GOOGLE

SearchVirtualDesktop

SearchEnterpriseDesktop

SearchServerVirtualization

SearchVMware

Close