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):
- Microsoft SQL Server Compact 4.0
- SQL Server Compact & SQLite Toolbox (Standalone 3.7 for SQL Server Compact 4.0)
- 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.
