SQL Express 2014 Installation - SambaPOS4 Database Setup - [BONUS] How to Convert from CE to SQL Express

Microsoft SQL Express 2014

SQL Express 2014 Download Link

Versions Available

  • Express 32BIT WoW64\SQLEXPR32_x86_ENU.exe
  • Express 32BIT\SQLEXPR_x86_ENU.exe
  • Express 64BIT\SQLEXPR_x64_ENU.exe
  • ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe
  • ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe
  • ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe
  • ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe
  • LocalDB 32BIT\SqlLocalDB.msi
  • LocalDB 64BIT\SqlLocalDB.msi
    :warning: - MgmtStudio 32BIT\SQLManagementStudio_x86_ENU.exe
    :warning: - MgmtStudio 64BIT\SQLManagementStudio_x64_ENU.exe

:heavy_exclamation_mark: Last 2 versions are not the actual SQL Express Database they are only the Management Studio Tool used to browse and administer the Database. This tool is included in the ExpressADV and the ExpressAndTools versions.


Versions Recommended

  • ExpressAdv 32BIT\SQLEXPRADV_x86_ENU.exe
  • ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe
  • ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe
  • ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe

Version and OS Used For Tutorial

  • ExpressAdv 64BIT\SQLEXPRADV_x64_ENU.exe
  • Windows 10 Developer Preview Build 9860

SQL Express 2014 Advanced 64bit Installation

It will run various checks to ensure it can install correctly if it finds a service or windows feature not installed that it needs it will recommend you install the windows feature before it will continue.

:warning: .net framework is the most common Windows Feature it may tell you to enable.


New Installation / Add Features

Choose New Installation or Add features to existing install. For this example we are performing a new installation.


Accept the License terms


Installation options

The options chosen are all you need for SambaPOS. You can read about the other options if you are curious but they are not required.

Reporting Services is useful if you have more advanced knowledge of SQL; if not, you can de-select this and it will not affect SambaPOS functionality in any way. SQL Reporting Services has nothing to do with SambaPOS Reporting features.

Management Tools is not required but highly recommended so it is selected. Choose the instance directory: Default is fine.

:warning:You will notice it lists Prerequisites for selected installation features. These are typically just Windows Features you can install through control panel / programs.


Instance Configuration

You can choose default instance (SQLEXPRESS) or rename it to whatever you want.

:warning: The Instance is not the same as the Database. Do not confuse the 2 terms. You can choose to have many Databases running under a single Instance.


Service Accounts

This tells it what to name the service that it runs as in the background and how it should start up. You should leave this default.


Authentication mode and User Accounts

For Database Engine Configuration you can leave everything default and allow Windows authentication mode it will run just fine.

:warning: If you plan to run multiple Terminals, it is recommended to enable Mixed-mode Authentication. In that case, you need to set a password for the administrative account. The initial admin account is named sa and you can set the password to whatever you like. We recommend sambapos as the password. You can optionally set up more admin users if you wish, but there is rarely a need to do so.


This concludes the installation you now have SQL Express 2014 installed correctly and ready for SambaPOS to connect to it.


Testing the Installation

Lets open up Management Tools and test our installation. This program should be installed if you followed the above Tutorial steps. SQL Server Management Studio (or SSMS as it is often referred to as) is the tool we use to check if everything installed properly.



Screen shots for exporting SQL CE data to SQL Server Express.

Download SQL Server Compact Toolbox tool from http://sqlcetoolbox.codeplex.com/.

Visit http://sqlcetoolbox.codeplex.com/releases and choose Standalone Version 4.0.

Right click on left pane and choose Add SQL Server Compact 4.0 Connection... menu item.

A toolbox window will appear. Click Browse button to choose your sdf file.

Under [MyDocuments]\SambaPOS4 folder find SambaPOS4.sdf file or the sdf file you’re actually using.

Click Test Connection button. If it succeeds click Close button.

SambaPOS4.sdf file should appear here.

Right Click on SambaPOS4.sdf and choose Script > Script Database Schema and Data menu item.

File Save dialog will appear. Enter a name to the export file (I’ve named is MySambaPOSData) and click Save button.

Table Selection Dialog will appear. Don’t change anything on this screen and click OK. __MigrationHistory table is deselected by default and there is no need to export it.

Few seconds later export will complete and you’ll see this confirmation message.

Tool Created a file named as MySambaPOSData.sqlce. This file contains required SQL scripts to recreate your database under SQL Express. If you edit that file with a text editor like notepad you should see some auto generated SQL code similar to that.

Open SQL Management Studio. Create new Database, Create new Query, Copy & Paste everything that file contains inside Query editing window and execute Query. That will create tables and data.

4 Likes

Emre & Kendash,
Thank you for a comprehensive tutorial I was able to follow it and now have an SQL database with 2 further RDP clients one on an ipad and the other on a galaxy note III. Thankyou very much for an excellent product and great forum support. If only my old POS that died after 3 years only to find no support, a proprietary formatting of the hard drive and having to back to pen and paper!

Thanks again.

Regards

Mike

3 Likes

Hi @Jesse

Im trying to do you turorial to set up everything.

I openen the installation and choise for:

“New SQL server stand alone…”

When i startup the setup i dont get the Installation Type option…
it go’s directy to Licence Terms:

Did i something wrong?

No if you do not already have it installed it skips this step as it already knows your installing for first time.

Also make sure you downloaded the right package. Read my description of them carefully.

You should be fine if you just continue with install.

EDIT: It looks like you downloaded just management studio and not the actual server. Look at the file you downloaded what is its name?

I CANT VIEW THE OPTIONS YOU EXPLAIN IN TUTORIAL WHAT I DO?
I DOWNLOAD THE SQL Server Compact Toolbox for runtime 4.0 the add option is off.

Can you explain how can i configure local setting for this server.

Can you expand the tree on left that says Databases?

your connection string would look something like this:

Data Source=BOMDIA-PC\SAMBAPOS; Database=YourDatabaseName;

@Jesse
This is how it look likes

So I do not see any SambaPOS databases. Are you wanting to start from scratch like a new setup? Or do you have a database for SambaPOS you want to use already?

I see you have a Query which looks like an import query? If you ran that on master under System Databases then you did the wrong thing. IF this is the case then right click databases and create a new one then click on the new database and run that import query in that database.

So to sum it up

  • Left Click Databases to highlight it
  • Right Click it and choose New Database name it whatever you want
  • Left Click the new database and then right click it and select New Query then paste the query you wish to run and press f5 to execute it.

Once this is completed then use this in your Connection String under Settings > Local Settings:

Data Source=BOMDIA-PC\SAMBAPOS; Database=YourNewDatabaseName;
2 Likes

@Jesse

Thank you, it works, But i am unable to login Samba from non-administrator. it says database could not be found.
I use one PC in my restaurant and i have two shift, so i want to give two windows uers so they can access terminal in there users. is it possible? Database server is also installed in the same PC.

Not sure why you would require two computer logins for this but yes its possible you just need to give the other user permission to the database by using mssms

1 Like

@Jesse
Can you explain how to give permissions to non-administrative users.

I have found the answer thanks for great help :smiley:

1 Like

only i download this version of Microsoft SQL - ExpressAndTools 32BIT\SQLEXPRWT_x86_ENU.exe
and it works fine, does i need to download all or just i need one single setup ?

that one is fine no need for them all

1 Like

Hi, Ken, contratulations for the tutorial, it is pretty intuitive. Just a question: In the last step (within SAMBA), must I fill a rute or could I let just “auto migrate compact SQL files”?

Iin “data source” field, would be “localhost\SAMBA4” (where I titled "SAMBA4 at instance)? Or “localhost” is an general example? I imagine it is a fool question, but I don’t know exactly. Sorry.

Thank you,
Carlos.