RG02D – Splitting up the WM datastore

By Max Ranzau

 

This article describes one of the very useful additions to the brand-spankin’-new RES Workspace Manager 2011. Yes, as of today Nov 7th, it’s still in Interim, so if you are not on the Early Adopter Program, you will need to wait a little longer until releaseday. Also, bear in mind that things may change. The described feature here is nevertheless very important if you are considering large scale logging or utilization of Usage Tracking. So use this article to become familiar with what will be possible in short while.

The ability to split off log and Usage Tracking data out of the main configuration datastore, has been an issue very dear to my heart for a long time, especially since I’ve seen the consequences in the field: Once, I visited a customer who inadvertently had left on learningmode for the Read-Only Blanketing security subsystem, for approx 800 users for close to two years. The result was a whopping +14GB additional log data, which was bogging everything down. Over lunch, we purged over 9.5 million records from the log table. If you’re in a similar situation then article RG00C will help you recover. However this article is to help you prevent this kind of situation and design your WM infrastructure to accomodate heavy logging from the start:

Architechture

Workspace Manager 2011 allows you to split off non-configuration data to a secondary datastore. We know this principle from the Citrix Resource Manager’s Summary Database. When all is said, done and executed, you will be able to have a Workspace Manager infrastructure that looks like illustrated to the right. While it’s perfectly possible to have the two datastores on the same database server/instance, the point is that we likely want to keep the logging data as far away from the config data. The important thing to remember about logdata: If you loose it, it isn’t the end of the world, as it will rebuild it self over time as users are utilizing the system.

Prerequisites

Before you can begin to split up your existing database, there are a couple of things you need to check. First of all, it might be wise to get an idea how much space your current logs and/or usage tracking data takes up. This is quite easy to figure out by following these steps on a MSSQL box (sorry, I don’t know how to do this on Oracle, etc. yet)

    1. Fire up ye olde SQL Management Studio or equivalent.
    2. Browse to your existing PowerFuse database
    3. Run a query on any table in the database
    4. In the SQL window (where you see something like SELECT*) delete all the text and enter: sp_spaceused ‘tblLogs’ (be sure to include both single quotation marks.
    5. Hit the run icon on the query box (may look like ). This calls a stored procedure on MSSQL which will return the size of the named tblLogs. The result looks like shown here on the right, where we are interested in the fourth columb, called data. Note the size in kb. This will give you the size of all current logfiles together, as they share the tblLogs table.
    6. If you currently have Usage Tracking enabled and want to check the size of that, you will need to repeat steps 4-5, replacing tblLogs with the following table names: TBLapp, TBLhistory, TBLserver, TBLsession, TBLuser and perhaps also TBLcurrentact, although it may be irellevant.

 

Update: After the release of this article, I was notified that final release of Workspace Manager 2011 will include the ability to show the size of the current tables. This is already included in the IR2, which was released yesterday on Dec. 15th 2010. Go to Datastore | Connection and the console will show you the current space taken up by the config data itself, the logs and Usage Tracking respectively as shown on the right. This is double cool as that means you will have access to this information, no matter what type of datastore you choose. As previously anounced, WM2011 will be available January 2011.

Now, back to the opera. By adding up the tablesizes one way or the other you will know how much of your existing datastore is log data. To see the total size of the DB, just click properties on the database level in the console. You can now dimension your database after the current state. As for future growth and what-if scenarios, I’d urge you to take a look at article RG007, where I have a calculator page which may help you to guess the size of the log data based on estimated future user load.

Once you have a clear picture of the database size requirement, you need to go have a look at your Workspace Manager agents. Reason is that in order to split the database, all current agents must be upgraded to version 9.4.0.3 or higher. The Workspace Manager IR1 release is 9.4.1.0,  so as you are reading this, a newer version is likely to be available.

Go to the Setup|Agents node and  have a look at the version column as shown above. If you have any agents older than 9.4.0.3, you will need to either run a Servicepack on them to upgrade to the latest version at hand. Alternatively you can delete the agent. Either way, you will not be allowed to continue with the database split, if older agents still are registered. If you try, you will be met by this message shown on the right, and that’s as far as you will get

At this point I have to issue a word of caution. If you delete the Agent entry for a machine which is still active, the agent will re-register itself next time that machine is rebooted or the RES service is restarted. If that agent is older than 9.4.0.3 it doesn’t know anyting about split databases and how to address them. The old agent will start writing it’s information to tblLogs and the other Usage Tracking tables as usual, but this data would be lost as the Consoles won’t be looking for it there anymore. Bestcase, you’ll have orphaned data. Worst case, I’d rather not think about.. The takeaway is this:

  1. Do not split your Workspace Manager database until you’re sure that every single agent has been upgraded to Workspace Manager 2011.
  2. Do not roll back any agent to an earlier version, unless you have already rejoined the split databases back together again. More on how to do this below.

How to set it up

  • At this point I presume you’re all squared off with datastore requirements and agent versions. The first place to go is into the Workspace Manager 2011 console and navigate to Setup | Datastore | Connections. Here you will see that things have changed quite a bit. Hit the browse button marked in red to start the Split/Join wizard.
  • The next step is pretty much obvious. Here you select what part of the logfiles go where. Note that you cannot move the configuration data away from the primary datastore. To move everything, do as shown on the right.
  • Next you chose if you’ll be using an existing location, i.e. a database with login which has been created earlier. Alternatively, you can chose to create a new location, in which case a wizard will run, allowing you to create the datastore from scratch.
  • Presuming you will be using Workspace Manager to create a new database, you will get a dialog box similar to the one you receive when you installed Workspace Manager initially. Here you will supply temporary DBcreate credentials (usa sa if we don’t know any better). Note these credentials are also only used at this stage to create the PowerFuse db.
  • If you get this far, it measn that db name and credentials are good. If you need to go bak to specify an instance, regular SQLserver’s default instance is \MSSQL after the hostname and for SQL Server Express (the free one) it’s \SQLEXPRESS. At this point Workspace Manager will suggest the name PowerFuse_2nd (that name might change with the final release).
  • Now it’s time to size the new log datastore. The screen here is the one you’d usually get when you create a new database. Workspace Manager will suggest the default 150 Meg per default, however you may want to increase that to the size you found when looking at the tablesizes in the Prerequisites section above.
  • Next you specify the authentication type and credentials for the new database. You can chose SQLauthentication or Windows Auth. Remember if you use SQLservr 2005 and up, you may have to enable that on the server first. Again the suggested PowerFuse_2nd username may change in the future. Bear in mind that the credentials here will be used by all agents to access the datastore for writing logs.
  • Finally Workspace Manager gives you an overview of what’s going to happen. The important thing here is that the new database is created and loging and/or usage tracking will be copied. In other words, no serious change is being made to the datastore, so if all you want is to copy out the data, just stop after the next step.
  • In this example we’ll go through the end though. When you hit next, the database creation and the copying will start. Mind you, if you have a big log database you want to keep or a ton of usage tracking data sitting around, you might want to grab a Cuppa Joe at this point. The speed will be entirely dependant of the connectivity and performance of both the old and the new datastore.
  • The next screen prompts you to confirm that you want to finalize the changes. You’ll have to select the checkbox and hit the finalize button. At this point, two things happen: 1) all agents will from there on use the new datastore for logs/usage tracking as you selected previously. 2) All that data will be purged from the first/old datastore. This will be show on yet another confirmation box as shown.
  • The wizard will crunch this for a short while, purging records etc. Once it’s completed, you will be returned to the console where  you now have both datastores online and being “pinged”. Note: The ping is not a regular ICMP ping as it doesn’t tell you anything about the performance of the datastores. Instead Workspace Manager actually does a small query against the datastores and times these. This only happens when you are on the Setup | Datastore | Connection node.

That’s it, you’re done with splitting the datastore. Now, as mentioned before, this process is not necessarily a one-way street. You can also join the datastores back together again, either partially or completely. For the example below, let’s presume we’ve had a change of heart and actually didn’t want the logs in the secondary datastore, only the Usage Tracking. That is entirely possible. All you have to do is to click the Browse button next to the secondary datastore. This will display the current connection properties of the secondary datastore. Note that if you compare this dialog box with the one for the primary datastore, you will notice that there is no ability to export a connection string for the secondary datastore. Reason is that the connection information for the secondary datastore is embedded in the primary datastore. Once an agent connects to the primary it will then know where to go to dump log and/or usage tracking data.

  • On the properties of the secondary datastore, click the Split/Join button. After an introductory (and remarkably blank) dialog page, you are able to reconfigure which database goes onto what server. As shown here on the right I just want to move the log data back to the primary datastore.
  • Like before, a summary page is shown. Next, the data will be copied and finally you will have to confirm twice that you want to commit the changes to the datastore configuration.
  • Once you’re finished, the status of the connection cofiguration node is updated and should look like shown on the right.

No Comments

No comments yet.

RSS feed for comments on this post.

Leave a comment

Comments are welcome as always. Just do the math below. * Time limit is exhausted. Please reload the CAPTCHA.