Stephen Smith's Blog

Musings on Machine Learning…

Archive for December 2010

Accpac on the Amazon Cloud

with 10 comments


The Amazon Elastic Compute Cloud (EC2) ( is a service offered by that allows people to rent virtual computers to run applications on. Some of the innovations offered by this solution include:

  • Very easy to get started, you just need an Amazon account, attach it to EC2, and off you go.
  • Very inexpensive, with a good (nearly) free trial (
  • Scalable and expandable depending on your needs.

Often the simplicity of getting started with this solution gets lost, since people are usually confronted with the advanced features at the beginning, which you don’t need to worry about until later. Just be re-assured that this is a solution that can grow with you. Below is a diagram of some of the services offered:

In this blog posting we will look at how to deploy Accpac on the Amazon EC2 cloud and discuss some of the trade-offs and choices that can be made along the way.


One thing that makes using Amazon EC2 intimidating is the terminology. But here is a quick guide to the key points.

  • Amazon Machine Image (AMI) – These are virtual machine snapshots that you take as a starting point to doing work. Amazon provides a number of these as starting points, there are a number of public ones offered by other people plus you can create your own. Basically when you want a new virtual machine you take one of these as your starting point.
  • Instances – You create an instance from an AMI and the instance is the virtual machine that you actually run. When you specify the instance you specify the resources it has including memory, disk space and computing power. For more on the instance types see:

You manage all these things from the Amazon Management Console:

Deploying Accpac

Deploying Accpac to Amazon EC2 is fairly straight forward. You just need to select a starting virtual image (AMI) of something that Accpac supports, create an instance of that, run the instance, install and configure Accpac into that image and off you go. There are a couple of “gotchas” to watch out for that we will highlight along the way.

  1. Go to and sign up for an account.
  2. Run the AWS Management Console ( and create a PKI security key pair. You will need to do this before doing anything else. This will be the security token you use to connect to your virtual image running on EC2.
  3. On the upper left of the management console, make sure it is set to the region that is closest to you like perhaps “US West”.
  4. Click the “Launch Instance” button on the AWS Management Console. You will now be prompted to choose a starting AMI. A good one to choose is: “Getting Started on Microsoft Windows Server 2008” from the Quick Start tab. This one has IIS and SQL Server Express Installed.
  5. Select “Small” for the instance type, unless you know you will need more resources quickly. Then accept the defaults for the advanced instance options. Same for the tags screen (i.e. you probably don’t need any).
  6. On the “create key pair” screen, select the key you created in step 2 (or if you skipped that then you need to create a pair now).
  7. On the configure firewall screen, remove the opening for SQL Server, you don’t need this. The only two holes in the firewall should be RDP and HTTP. If you are hosting client data, then you should add HTTPS and setup Accpac to use that (see
  8. Now you can review your settings and Launch your instance. It can take 15 minutes or so for the instance to launch, mostly due to the time it takes Windows Server 2008 to boot. So this is a good time to go get a coffee.

At this point we have created a virtual image and have it running. From the AWS Management Console EC2 dashboard, we should see one running instance. We should also see 1 EBS volume. The EBS volume is the disk image of your virtual image. If you want to you can create snapshots of your EBS volume (you have to pay to store these) so you can go back to them if you mess up your image. So now we have our own Windows 2008 server running in the Amazon cloud, great, but now what do we do? How do we connect to it? How do we transfer files to it? How do we browse to it? What are the Administrator and SQL Server passwords? Now we’ll go through the steps of getting the Administrator Password, connecting via RDP and installing Accpac.

  1. Select the instance that you have running in the management console. From the instance actions menu, choose “Get Windows Admin Password”.  If this doesn’t work, you may need to give the instance a bit more time to start. You will get a dialog that wants you to take the file you downloaded back at step 2, load it into notepad and copy/paste its contents into this dialog. Then this dialog will go off and do a long cryptographic calculation and tell you the Windows Password.
  2. Now you can run Remote Desktop and connect to your instance (if you choose Connect from the instance menu it will download a file that will start RDP with the right parameters). Use the public DNS as the computer name (from the pane with the instance details below the instance list). Administrator is the login. Be careful because copy/pasting the password can be difficult because Windows tends to add a space when you copy the password. If copy/paste doesn’t work, try just typing the password. Now you are logged in and running. Perhaps the first thing you want to do is change the Administrator password to something easier to type and remember. Now you can treat this virtual Windows Server 2008 just like any other remote server.
  3. Copy the installation image for Accpac onto the virtual machine. You can use an FTP site or any other file copy mechanism to do this. On convenient method that Windows 7 has is that RDP can make local drives accessible to the remote computer. If you choose Options – Local Resources you can expose some drives to the remote computer and then they will show up in Windows Explorer there.
  4. Now we need to enable SQL Server, by default the service is disabled and authentication is set to Windows Authentication only. Go to Admin Services – Services and set the SQL Server services to Automatic and start them. In the SQL Server configuration manager enable TCP/IP and set the port to 1433. In the management console set the authentication to SQL Server and authentication, then go to the sa user and enable it. Now restart the SQL Server service. Create your Accpac databases such as PORTAL, SAMSYS, SAMINC, SAMLTD, …
  5. Run the Accpac installation you copied into the image and perform the usual steps to get Accpac up and running. When running database setup, make sure you use localhost as the server name and not the current Windows instance name, because this will change each time you run the image.

We now have Accpac up and running and can access Accpac via RDP. To access the Portal use the public DNS as the server name in the usual URL for running the portal:


Viola you are running in the cloud.

If you shutdown this instance and restart it, you will get a new computer name and a new public DNS. This can be rather annoying if you like to setup Browser shortcuts and such. If you want to avoid this you need to allocate a static Elastic IP address from the AWS (doing this costs a small amount of money). Then you can associate this IP address with the instance and now it will stick. Further you could purchase a meaningful URL and associate it with this IP address. If you don’t want to purchase a URL, another trick is to use to generate a URL for your IP address. This isn’t a very meaningful URL but it’s better than the raw IP address.

How Well Does It Run?

Once running, how does it compare to a local server? With the small configuration you are limited a bit in memory. It seems that running the Sage ERP Accpac 6.0A portal on the virtual image in a RDP image is a bit slow.  However running the browser locally and hitting the server remotely is quite quick. This implies that the small image is sufficient for the server processes for a few users. However you will need to increase the memory and/or the processing power for more. The nice thing with Amazon is that you can change this fairly easily and only pay for what you are using. It also shows that the Amazon datacenters have quite good network latency, probably better than you can get hosting yourself for remote users.

Going Production

So can you go production with this? Certainly the platform can support it. The current sticking point is terminal server or Citrix licenses. These are available through various programs such as: However you need to be part of one of these Microsoft or Citrix programs where they give you specific permission to migrate your licenses to EC2. While we still have Windows Desktop components this is a potential sticking point. However once Sage ERP Accpac 6.1A comes out and we can run all the main accounting applications through the web, then this problem goes away.

Amazon is also addressing other compliance type concerns, for instance achieving PCI DSS Level 1 Compliance ( and ISO 27001 Certification ( Receiving these sort of certifications remove a lot of obstacles to using Amazon for a production environment.

Also if you want to back up your data locally then you will need to copy a backup of your SQL Server database over the Internet which could be quite time consuming, but you can let it run in the background.


Amazon’s EC2 Service offers an excellent way to access extra computing resources at a very low cost. You can deploy services to regions around the world and dynamically adjust the computing resources you are using. For developers this is a very cheap way to obtain access to test servers when in development. For partner this is an excellent way to establish demo servers. For education this is an excellent method to learn how to work with different operating systems and to practice installations.

Written by smist08

December 17, 2010 at 9:41 pm

Customizing the Order Details in Quote to Orders

with 4 comments

The new Order and Quote Entry screens in the Sage ERP Accpac 6 Quote to Order feature are the first real accounting document entry web based screens produced in the new “Orion” technology. As these go to final release there is a lot of interest in how to customize these screens. In the previous blog posting we started to talk about how to customize these screens. We discussed the declarative layout XML file that describes the layout and performed some simple edits to these screens. In this blog posting we are going to continue customizing these screens, this time customizing the Order Detail Line Table/Grid controls. In this blog posting, we will look at the XML that defines this table along with a couple of customizations.

For more background on Accpac’s new customization model and the XML Declarative layout files see: and

Definition of a SwtTable

The XML file is: C:\”Program Files (x86)\Common Files\Sage\Sage Accpac”\Tomcat6\portal\swtServices\uiDefinitions\oe60a\eng\sagecrmorderui\SageCRMOrderUIUIDefinition.xml. Below is part of the definition of the table control (a widget of type SwtTable) from the declarative layout (many columns omitted):

 <widget type=”swt:SwtTable” id=”orders_DETAILS” datasourceID=”oeorderdetails” preferencestoreID=”DetailTableSettings” height=”250″ width=”980″ autoLoad=”false” showRecordNumber=”true” showCustomFields=”true”>
       <item propertyBinding=”LINETYPE” dataType=”Int” width=”100″>
               <transText text=”xType” textID=”oe60a_sagecrmorderui_colType”/>
      <item propertyBinding=”CALC_ITEMNO_MISCCHARGE” dataType=”Char” width=”160″ formatPattern=”[:printupper:]{0,24}”>
              <transText text=”xItem No./Misc. Charge” textID=”oe60a_sagecrmorderui_colItemMisc”/>
   <item propertyBinding=”DESC” dataType=”Char” width=”200″>
                <transText text=”xDescription” textID=”oe60a_sagecrmorderui_colDesc”/>


The “<widget type=”swt:SwtTable” …” tag defines the SwtTable, sets its initial size along with some other global attributes. You can change these, but might not want to, for instance setting autoLoad to true would cause the table to load when it’s initialized, but the program will load it programmatically so the end effect will be to load it twice.  The main thing you will want to customize is the collection of columnHeaders which define all the columns that are displayed in the SwtTable.

Between the <columnHeaders> and </columnHeaders> tags are a number of <item> tags which each define a column in the table. Each item has a number of attributes, the possible attributes are:

  • propertyBinding: field in the SData feed that the column is bound to.
  • dataType: type of the column possible values are: string, Bool, Byte, Char, Date, Decimal, Int, Long, Real, Time and Object.
  • Width: default width of the column.
  • readOnly: set to true or false whether the column is read only (versus editable).
  • hyperlink: set to true or false whether the column is a hyperlink.
  • Sorting: set to true or false whether you can sort of this column.
  • formatPattern: format pattern for the column.
  • fractionDigits: number of decimals.
  • totalDigits: total number of decimal digits.

Some attributes are only valid for certain data types, for instance fractionDigits only makes sense for Decimal and Real types.

Then there is a <text> sub-element inside the <item> tag where you can specify either the text for the column heading or the textID of a string identifier in the uiContent table on the server. The textID will get the appropriate text for the language the user signed in on as. Make sure you set the textID to “” (empty string) if you want to hard code the string in the declarative layout. Editing the strings in the uiContent table in the Portal database is also a good way to customize strings, or to even specify new strings.

Adding a Column

To add a column to the SwtTable we need to add another <item> to the <columnHeaders> collection. For instance if we wanted to add the “Non-stock Clearing Account” to the table then we would add the following to the collection of <columnHeaders> just before the </columnHeaders> tag:

 <item propertyBinding=”GLNONSTKCR” dataType=”Char” width=”80″ readOnly=”true” visible=”true”>
               <transText text=”Non-stock Clearing Account” textID=””/>

Even though we’ve now added the field GLNONSTKCR to the SwtTable it wouldn’t display since it isn’t in the O/E Orders SData feed. So we need to add it to the SData feed. To do this we need to edit another XML file, namely: C:\Program Files (x86)\Common Files\Sage\Sage Accpac\Tomcat6\portal\sageERP\oe60a\resourceMap\OEOrderViewMapping.xml. This file among other things defines which Order fields to include in the SData feed. In the <includedFields> section for the detail, add the line:

    <resourceViewField viewFieldName=” GLNONSTKCR”       />

This will add the GLNONSTKCR feed to the detail. Make sure you add this to the includeFields for the detail and not one of the header. The field names used in the SData feeds is exactly the same as the field names specified in the Accpac Object Model (AOM) located at:

Whenever you change one of the SData definition files, you need to restart Tomcat to have it take effect.  To do this: run the Service applet from the Administrative Tools icon in the Control Panel and restart the “Sage Accpac Tomcat 6” service. Or do it from the “Configure Tomcat Service” icon under “Sage Accpac” under the start menu.

Note that is you are using sample data then there are a lot of optional fields defined for the Order details. With this new screen all the optional fields appear in columns of the table rather than from a popup form. This means that when we added our new field it won’t appear as the last column in the table, but somewhere in the middle since all the optional fields will come after it.

When the user customizes the column widths, order and visibility of table columns, these customizations are stored in the PreferenceStore table in the Portal database. If you have played with the columns in the table previously and the new column doesn’t show up, you might want to delete the SageCRMOrderUIUIDefinition-DetailTableSettings row from this table to clear the preferences. This table stores what we used to store in the *_p.ism files from our VB UIs.

Also make sure you have a backup of your customized declarative XML file, since you wouldn’t want future product updates to overwrite your one copy.

Below we see a screen shot of the table with our new column added between the Instructions and the Backorder optional field:


Hopefully this gives an idea of a few things you can do with the SwtTable that displays and edits the Order/Quote Detail Lines in the new Quotes to Orders. This article showed how to do this by editing the XML directly; but, if you have the SDK then you can use the SwtUIDesigner to do this visually.

Written by smist08

December 11, 2010 at 6:41 pm

Accpac in the Cloud

with 8 comments

It used to be that running in the cloud ( meant using a generic SaaS based web application, but now a days technology advances have offered many choices beyond this basic option. Virtualization technologies have advanced in leaps and bounds. Hosted solutions providers now offer many innovative options based on both regular and virtualized solutions. Plus the capabilities of SaaS web applications have improved quite a bit. Today Accpac has several cloud based solutions available and in the future we will have a full spectrum of solutions. Combining Terminal Services or Citrix with Virtualization is now a very powerful method of economically hosting enterprise applications. It is at the point where you can host your Windows based Enterprise application in the cloud and access it from an iPad (by running the Citrix client application

In some ways this wealth of riches has led to some confusion. There are so many options on how to deploy an application that it gets quite difficult to wade through all the choices and all the various claims being made by various hosting and application vendors. This blog post will attempt to outline the main choices and trade-offs, along with pointing out the various places that Sage ERP Accpac plays now and will play in the future.

Cloud Categories

The cloud usually means that a client is running an application on a remote server via the Internet. There are many ways this can be achieved. Many of the options come down to who owns what (you or the vendor) and what type of application is being talked about (Windows or Web based). However the following are the 4 main categories.

  1. Hosted Server. Server is owned by customer and maintained in a central data center but accessed via the Internet.  This is usually a Windows based application and the customer owns everything (server and software), but is outsourcing the physical care of the server and often other services like backup. A typical company in this area is Rackspace (
  2. Shared Virtual Server. A server farm is owned by the data center vendor and the client owns virtual server images that run on that farm. Here the customer owns the software (usually Windows based), but the data center owns the hardware. Typical of this is the Amazon EC2 service (
  3. Single Tenant SaaS. Vendor owns servers and images. Runs each client in their own image (whether virtual or multiple processes). Here the application is usually Windows based (but often we see web based applications here also). AccpacOnline is a good example of this.
  4. Multi-tenant SaaS. Vendor owns everything, client runs within a shared image or process (usually distributed over many servers).Here the application is always a Web application.

Any of these could be running web applications or desktop applications, but we highlight the most common cases above. The costs and who pays them is different in each situation and there are pros and cons to each. The differences between these are often blurred as companies offer hybrid solutions. Also some vendors try to define a category exactly as they implement it and claim anyone that does it differently is incorrect, but like anything there are always many choices.

Customer Goals

So why are customers demanding cloud based solutions rather than just buying software and installing it on premise? Below are some of the goals that clients are trying to achieve:

  1. Save costs by not having a data center. Save on requiring extra air conditioning, backup power supplies, hard disk backup and space. Save on routine maintenance like performing backups.
  2. Save capital equipment costs by not purchasing hardware which devalues quickly and needs to be replaced often. Have fixed constant monthly expense instead.
  3. Save capital software purchase expenses. Don’t want large up-front purchase, would rather pay much smaller monthly fee. Even if this is more expensive over x years, doesn’t require initial outlay. (Some on-premise software can now be “rented” as another solution to this).
  4. Save HR expenses by not needing to hire IT staff to run and manage corporate hardware and software.
  5. Don’t have to perform software installation or maintenance.
  6. Ability to access their applications safely and securely from anywhere in the world whether through laptops or other mobile devices.
  7. Desire to use a modern web-based application which has the ease of use of Facebook or Amazon.

When considering cloud solutions there are a number of problems that are usually cited that clients try to avoid:

  1. Lack of customization in cloud offerings.
  2. Lack of ability to use/integrate other software.
  3. Lack of owner ship of data. Lack of ability to get their data. What happens if the cloud vendor goes out of business?
  4. Security concerns, how safe and private is their data?

All solutions have an answer to these; customers just have to determine if those answers are sufficient, cost effective and achievable.

Vendor Goals

To be fair, many vendors are pushing cloud solutions quite strongly, and this isn’t for purely altruistic reasons. What are the vendors trying to achieve? Below are the goals that vendors are trying to achieve:

  1. Obtain a more steady cash flow. Steady subscription revenue, rather than relying on less frequent large purchases. Easier for financial planning and more recession proof.
  2. Obtain access to a larger market by being able to server the world from a single location.
  3. Can organize so only need to support one version of the software, reducing costs. Similarly you only need to support one hardware/operating system environment.
  4. Can have more direct contact with customers since you are sharing an operating environment – more control of ECE (Extraordinary Customer Experience).
  5. For hosting only vendors that aren’t software vendors as well, then this is their entire business, so of course they are selling it as hard as they can.

Sage ERP Accpac in the Cloud

Now let’s look at the options for Accpac in the cloud. At the beginning of this article we looked at four cloud categories, now we’ll look into how Accpac serves these four categories.

  1. Anyone can do category 1. This is basically just installing an on-premise application like Sage ERP Accpac 5.6A on a terminal server and then physically moving that server to a hosting provider for care and feeding. Then clients access the server either using RDP or Citrix client.
  2. This option is very similar to category 2, except rather than install on a physical server you install your software into a virtual image either provided or specified by the hosting vendor. Then you transfer this image to the vendor who runs and maintains it. If the vendor is associated with Sage or Accpac already, they may provide a virtual environment with Accpac already installed.
  3. Both Accpac and SageCRM have operated single tenant SaaS environments for some time with and  Accpac operates the desktop version of Accpac in a SaaS manner using Citrix to manage things, but each client runs in their own separate server memory space when running. SageCRM is web based but requires a unique instance of SageCRM running for each customer. Here you pay by the month, can run VBA macros, Microsoft Office and a selection of ISV products.
  4. Sage ERP Accpac 6.x has better multi-tenancy support than the current AccpacOnline because all clients run in the same server processes and share the same server memory. The goal is that once we have moved all the accounting screens to be true web based screens (after 6.1A), then we can deploy them as a SaaS solution. As part of the development of the infrastructure of Accpac 6.x, we ensured we plumbed in the support for a multi-tenanted deployment of this type. Once we enter this world we will be a try true web based application that doesn’t require Terminal Server, Citrix or Virtualization Technologies to run. It will be a modern web based application that you can run either on-premise or as a true SaaS web application.


It used to be that a big competitive advantage of a true web based SaaS solution was a much lower cost by being able to run far more users per server. However with all the advances in Virtualization Technology and Terminal Server/Citrix a lot of this gap has been narrowed, making solutions of this type very cost competitive.

As we can see Accpac already has many options for cloud deployment that achieves many of the customer’s goals in considering a cloud application. Then in the future we go beyond this to offer a true Web Based SaaS solution. As these technologies progress the TCO (Total Cost of Ownership) will come down as we can host more users per server. For clients it becomes easier to get customized solutions in the cloud with all the attendant cost savings, including better usability and accessibility.

Written by smist08

December 4, 2010 at 6:30 pm