by
Gabe Knuth
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?
- On a system other than your Citrix servers (that your figured out in your typical pre-rollout documentation….right?), extract the package you downloaded.
- 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 ).
- 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.
- 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.
- Type "Go" to execute the CREATE DATABASE command. After the "Go" command executes, type "Exit" to leave osql.
- At the command prompt, type: osql -E -d IMA (or whatever you named your database) and press Enter.
- 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.
- 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’.
- 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.
- Select “Use the following database on a separate database server” option, and select “SQL Server” from the list. Click Next.
- 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.
- On the next screen, select “With Windows NT authentication using the network logon ID” and click Next.
- 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.
- Verify that the default settings are acceptable and click Finish.
- Select “Test Data Source” just to put a smile on your face.
- Enter the username and password for which you’ve granted access to the database.
- 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.
- Choose a Citrix server and open up the “Data Sources” control panel applet.
- Click on the System DSN tab, and click Add.
- Select SQL Server from the list and click Finish.
- 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.
- Chose whatever you want for the description, and select or enter the servername of your MSDE server. Click Next.
- Ensure that “With Windows NT authentication using the network logon id” is selected and click Next.
- Change the default master database to the RM database that you specified in OSQL and click Next.
- Verify the information on this screen and click Finish.
- Again, test the data source if you want a feeling of victory, then click OK. You can close ODBC.
- Start up Citrix Management Console, select Resource Manager from the left-hand pane.
- Click on the Summary Database tab, then on the Configure button.
- Check the “Summary Database enabled” box.
- Select the server name of the main server (since this is the first time configuring the summary database, it will be this server).
- For User, enter the same username used for the IMA data store. It must be in domain\username format.
- 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...
(Note: You must be logged in to post a comment.)