SQLServer to SQLCompact 'downgrade'

I’ve implemented SambaPOS3 on one of our point of sale terminals and it has begun trials in one of our small fast-food outlets. Functionality-wise it works great and does everything that we could possibly need, however performance is proving to be an issue.

In terms of hardware the POS terminal itself is running on an ATOM Dual Core D525 1.8Ghz with 4GB RAM. When I installed SambaPOS originally I deployed it alongside SQL Server as I wanted remote access to the data and to trial using PDAs with it. However it seems the stress of running both SQL Server and SambaPOS is too much for the hardware to handle.

Since the current location does not require any extra terminals such as PDAs, I want to downgrade from SQLServer to SQLCompact to try and give it a bit of a performance boost. Has anyone accomplished this before? Any pointers on how I might go about it?

Why are you using samba v3?
We are about to see release of v5 - get up to date :smile:

In theory you would have to go to the v3 forum as this is for v4+ and many people inc myself have very limited experience with v3.

BUT I would not recommend CE database, sql server is the way to go!

1 Like

I agree with @JTRTech why using old hardware and old software.
The POS system is the most essential part of your daily sales and bookkeeping.
As I have not worked with V3 I don’t know what the support is.

For V3 support please go to http://www.sambapos.org/en

The performance of CE is very poor compared to SQL Express. You will not see an improvement by downgrading - in fact, it will get slower to respond.

How do you know this? Show some performance shots.

1 Like

Basic observation more than anything else is what’s giving me this hunch - my WEPOS2009 installation was performing admirably on the hardware prior to the SQLServer installation, at which point response times for almost everything took a bit of a hit. Plus, past experience tells me that Compact tends to perform better in single user scenarios with relatively low query transaction volume.

I’ve now successfully made the ‘downgrade’ and disabled SQLServer for the timebeing - I will feedback at the end of tomorrow as to whether or not my staff notice any significant improvement or worsening of performance.

As a side note I would hasten to point out again that v4 does not have multi-lingual support, and since both v3 and v4 can be installed with SQLServer or SQLServerCE, the ‘downgrade’ methodology would essentially be the same regardless of version.

1 Like

When you have time giving more details about how you downgraded might help someone else. Thank you.

You can also try SQL Server Local DB. (google it) Will work slightly slower than CE but you won’t have performance issues in long term.

Not a problem - that was my intention as soon as I had a little more time! This process should work for versions 3 and 4, and potentially 5 if it still can use both SQLServer and SQLCompact.

Step 1 - Software required

In order to complete the downgrade you’ll need to install Compact if you haven’t already, plus get your hands on two nifty tools from codeplex by ErikEJ (whose blog provides a wealth of insight into Compact and SQLite):

  1. Microsoft SQL Server Compact 4.0
  2. SQL Server Compact & SQLite Toolbox (Standalone 3.7 for SQL Server Compact 4.0)
  3. SQL Compact Command Line Tool (v1.2 for SQL Server Compact 4.0)

For peace of mind, ensure you backup up your SambaPOS SQLServer database prior to attempting this.

Step 2 - Ready your SambaPOS installation

Remove the connection string from your SambaPOS settings and restart SambaPOS in order to revert your SambaPOS installation to SQLServer Compact Mode - this will have the effect of creating an SDF file in your My Documents SambaPOS directory with the default installation settings.

Step 3 - Script Database Schema

From here on in I’ve borrowed other screenshots from ErikEJ, so forgive me if they’re not exactly right, but they still show what’s needed.

Open up the SQL Server Compact & SQLite Toolbox and create a 4.0 connection to the newly created DB in My Documents\SambaPOSX\SambaPOSX.sdf (X being your SambaPOS version number).

Right click the newly connected database and choose Script Database Schema…

Choose a filename and destination for the output sqlce script file…

Choose the tables you wish to script. You should select all of them.

The toolbox should then give you a success indicator.

Step 4 - Script SQL Server data import

From the root node, right click and select Script SQL Server Database Data…

This will present a standardised SQL Server connection dialogue - enter your SQL Server details, select the SambaPOS database and then follow the same process as above to create a data script file.

Step 5 - Create a new SQL Compact database

Now that the schema and data scripts are ready, delete the existing SambaPOS SQL Compact database in your My Documents\SambaPOS directory.

Using the SQL Compact Command Line Tool, create a new empty SQL Compact database in the same directory, with the same name as the previously deleted SambaPOS sdf file, for example:

sqlcecmd40 -d "Data Source=C:\Users\$USER\My Documents\SambaPOSX\SambaPOSX.sdf" -e create

Next, create the schema in the newly created sdf file using the schema script that we generated, remembering to check the logfile for any errors:

sqlcecmd40 -d "Data Source=C:\Users\$USER\My Documents\SambaPOSX\SambaPOSX.sdf" -i schema.sqlce > log_schema.txt

Lastly, import your data into the newly created sdf file to complete the ‘downgrade’, again remembering to check the logfile for any errors:

sqlcecmd40 -d "Data Source=C:\Users\$USER\My Documents\SambaPOSX\SambaPOSX.sdf" -i data.sqlce > log_data.txt

Step 6 - Enjoy!

Your data should now have been successfully migrated to SQL Server Compact. Re-launch SambaPOS and log in as you would have done previously.

Some important notes

You may notice during step 4 there is also an option to simply export an entire SQL Server DB to SQLCompact, do not choose this option. The toolbox will make unnecessary alterations to some of the datatypes in the tables, which will prevent SambaPOS from executing queries correctly. Similarly the same will happen if you script the schema directly from SQL Server.*

If anybody wonders why you have to delete and recreate the Compact database with the schema from the original, this is because the default install of SambaPOS populates the DB with default data - primary key conflicts and all sorts of other hassles will arise if you attempt to import data directly into this file.

As a final note, you should be very wary of performing this downgrade if your SambaPOS install has been in use for a long time, as the data import script will insert each record line by line - there is a BLOBs option that should in theory handle greater quantities of data, but I did not test this option. As an indicator, my install had been running for a week (approx 1200 transactions/3000 items) and the data import ran successfully - I wouldn’t be very keen to try it if my data went much beyond a month.

I hope this guide helps - initial reports from my staff are that performance has improved on the terminal, but I will know better when I speak to them properly after close of business.

2 Likes

As a follow up to my post relating to performance, I can confirm that my SambaPOS3 installation has seen significant performance improvement since the switch to Compact.

Where as previously navigating between menu categories, adding orders to a ticket and processing payments could take upwards of a second, the reaction timing is now near instantaneous. My only potential concern remaining is in relation to filesize of the database, and whether or not performance will eventually take a hit over time as the SDF inevitably increases in size.

If it occurs my initial thinking to solve this problem will be a simple powershell script that uses ADO to create monthly snapshots of the databases, and then reset the data inside of the application database to return it to a fresh installation with no transactional data present.

If and when it happens I will report back again with my scripted solution.

2 Likes