Tech Tricks: How to Migrate Your BMC Remedy AR System Server to Unicode

This article is directed specifically towards BMC Remedy system owners and administrators seeking to utilize a Unicode-compliant BMC Remedy installation.

This article may also be useful to other stakeholders, but be warned – There’s some nerdy stuff in here…

Background – Why do I need a Unicode setup?

Unicode is a way to represent different regional characters so that they all can be utilized simultaneously. You could think of it as a globally compatible way to represent character data. Most modern systems and typically a more recent installation of BMC Remedy would be set to use Unicode.

That is not the case with older setups, however. They utilize the more traditional character set-based database models for character data. What this means, is that you would have to choose a character set, specific to your locale, like Latin-1 (https://en.wikipedia.org/wiki/ISO/IEC_8859-1), which is used in western Europe. It’s all good and dandy as you stay in your locale, but as businesses are getting more global by the minute, you’ll eventually run into problems – Not being able to store Chinese for example.

If you’ve been running on BMC Remedy for years, I’d guess you’re running a character set-based database and have been thinking of upgrading to Unicode. You might have even tried it.

Like an acquaintance of mine, you might think it was as simple as turning a switch. But it’s not, as it turns out. Not, when you have a single accented character somewhere in there. Of course, this does not apply to ASCII people, for them it’s like watching butterflies on a sunny day.

And my friend was lucky. He was operating ‘the switch’ for a new instance, so there was a backup and he still has his job. He also learned the hard lesson of regional character set peculiarities the hard way.

The Problem(s)

There are two basic issues, that you need to be aware of.

I’m Taller than I Used to Be…

The problem is twofold; One affecting Unicode conversion for databases in general, the other affecting BMC Remedy AR System specifically.

The first problem is, that where characters for a regional character set always take the same amount of storage space, the character storage space for Unicode varies per character. This leads to the fact, that a character string taking 250 bytes of storage may take, as much as a 1000 bytes, depending on the string content and Unicode Transformation Method used (UTF-8, UTF-16, UTF-32 to name the most common). The space you once specified for the character field may not be sufficient in a Unicode setup. The easiest way to examine fields, that need their length changed, can be done, by utilizing comprehensive SQL queries, that identify possible problematic fields. For example, for Western Europe, like Scandinavia, it’s typically sufficient to increase the field by 20-30%.

When One Plus One Equals Three

The other issue is related to how BMC Remedy AR System stores its internal notation for configuration data, like field labels for example.

Let’s say we have a label like “Tässä”. It would be stored in internal notation format for a regional character set, like

…/5/Tässä/…

The number 5 indicates the number of bytes in that string. In a Unicode setup this would be stored, as follows

…/7/Tässä/…

The reason, that AR System stores it this way, is that although there are still 5 characters, the ‘ä’ characters take 2 bytes each, amounting to 7 in total.

Doesn’t sound too serious, right? Well, if the former line would be encountered by AR System Server in a Unicode environment, the system would fail to start, indicating that the definitions are corrupted.

Not to worry, however. There are two ways to go around this, other of which involves, once again, a serious use of SQL.

Two Options

There are two ways to convert your existing character set-based system to Unicode. Either by exporting all definitions and data, and importing them to a Unicode environment, or by copying the database and running an SQL conversion script.

Things to Consider for Both

The main thing, that affects both methods, is the Unicode spacing issue. You should solve it for most part before trying to switch to Unicode with either of the methods.

Recommendation: Use SQL, to find and study storage allocation for character fields, to find out which fields already store regional characters, and take about 70-80% or more of the allocated field size – These fields should be enlarged to account for Unicode spacing.

Also, this project is a good timing to upgrade your database server. You should be building the Unicode database separate from the original production database, so that you can 1) rehearse the switchover and 2) your rollback procedure is very simple, if something goes wrong. Also use a secondary AR System server to operate on the new Unicode database.

Both methods require a hefty amount of systematic administrative work. So don’t save on resources with this one. An SQL expert, specific to your database technology, will be the hero of this endeavor and will save you time and money.

The Export-Import Approach

This would mostly be like a standard system migration, with an overall process, that goes like this (assuming that you have a new server setup with a Unicode database):

Prior to Go Live

  1. Study field lengths and change them, where appropriate
  2. Take all definitions and data and transfer to the new Unicode setup. Record the date and time when the export was started.

On Go Live

  1. Put production to Admin Only-mode (No users can modify data after this point) and shutdown auxiliary services, Email Engine and Escalations (Any service, that might modify the data).
  2. Start exporting remaining data from the date and time you recorded upon previous export
  3. Import the data
  4. After everything is in there, you’re ready to do a production readiness test, activate services and Go Live

PROS

  • This is the standard way
  • This is the simple way

CONS

  • With a large database and/or complicated application, even the differential import-export approach may take too much time to be completed during reasonable maintenance window. Even if you automate it (by scripting or whatever), it will take hours or days.
  • In my experience, some records will not be imported with this method, especially some attachments, so test it. Fortunately, if you use command line utilities for import, you can at least pinpoint the missing records and evaluate, whether it’s worthwhile to import them manually.
  • In a big environment, you should plan to complete this whole process in a week. It’s a lot of time to wait for a show stopper.

The Conversion Approach

With the convert approach, you’d use database tools to copy the database and then convert all the character fields to Unicode-compliant fields. In this process, you’ll also need to address the AR System internal notation issue described above.

Prior to Go Live

  1. Study field lengths and change them where appropriate
  2. Prepare the AR System internal notation for migration, by
    1. Starting a ‘no definition changes’ -period
    2. Exporting current definitions
    3. Studying your configuration with SQL to find any regional characters stored in AR System internal notation
    4. Removing regional characters from internal notation. For example, change label “Tässä” to “Tassa”. This can be done with SQL while the server is shut down, but use the Admin, if you’re not confident with using SQL to modify AR System configuration data. You may want to inform your users, that there may be mistypings during the migration period.

On Go Live

  1. Put production to Admin Only mode (No users can modify data after this point) and shutdown auxiliary services, Email Engine and Escalations (Any service, that might modify the data).
  2. Copy the current production database to a new database and use a secondary AR System Server to operate on that Database
  3. Run a Conversion SQL Script to convert all character data to Unicode
  4. Configure the AR System Server to use the new Database in Unicode mode (Or use the secondary AR System Server for testing the migration).
  5. Start the AR System Server
    1. If the AR System Server does not start, refer to your studies and error messages to find the faulty definition (If you’ve been thorough with the preparation, you should not have this problem)
  6. Import the Definitions to restore regional characters in AR System internal notation
  7. After everything is in there, you’re ready to do a production readiness test, activate services and Go Live

PROS

  • The Go Live Process can be completed in hours, even with a large database.
  • Minimal risk of losing data

CONS

  • The manual work needed to fix the internal notation issue can be huge, especially if you have a large system, with hundreds of forms that have localized views and workflow.

Choosing Between the Two

The only way to choose the correct method is to evaluate both in terms of 1) how much work is needed and 2) how big maintenance break is needed for the Go Live phase.

To simplify:

  • If you have no or little localized views & workflow and a big count of tables and rows, choose the Conversion Approach.
  • If you have extensive localized views and workflow and a relatively small count of tables and rows, choose the Export-Import Approach
  • In all other cases, evaluate and test to find out which works better for you.

I have personally used both methods and I have no clear preference without context. The right choice is always customer dependent.

Closing Words

I think it’s time we forgot about regional borders in technology. So, let’s all be Unified in Code (pun intended).

Please feel free to contact me regarding any questions on this matter.

Want to hear more?
Book a time

Jesse Lantinen
CTO

Jaa eteenpäin!