SQL Server Express 2016 / 2017

[QMcKay]

It is best to run the latest version of SQL Server Express when you can. This Topic gives some details on SQL Server 2016 Express Edition, which package to download, and how to install it.

ATTENTION: SQL Server 2016 is only available in a 64-bit version. This means you must be running a 64-bit version of Windows. You can NOT install SQL 2016 on 32-bit Windows.

TIP: SQL Express comes in a few different flavors or “packages”. If you intend to run multiple SambaPOS Terminals, you can NOT use the LocalDB version of SQL Express. You must install the “Full” or “non-LocalDB” version of SQL Express. Even if you do not intend to run multiple SambaPOS Terminals, it is still highly recommended to use the “non-LocalDB” version.

---

Downloads

This link provides a small (3.1 MB) installer named SQLServer2016-SSEI-Expr.exe:

https://www.microsoft.com/en-us/download/details.aspx?id=52679 (no longer available)

http://go.microsoft.com/fwlink/?LinkID=799012 (SQL 2016 SP1)

https://go.microsoft.com/fwlink/?linkid=853017 (SQL 2017)

When you run the installer, you get this:

We recommend downloading the installation Media so that you have it always on-hand. When you choose this option, then you can choose which Package to download:

Express Core (SQLEXPR) [301 MB]
Express edition includes the SQL Server database engine only. Best suited to accept remote connections or administer remotely. This is the one you should download, since it contains everything you need to run a multi-Terminal setup.

Express Advanced (SQLEXPRADV) [713 MB]
This package contains Full Text Search and Reporting Services features in addition to SQL Database Engine. You do not need the extra features in this package, so there should be no reason to download this big package.

LocalDB (SqlLocalDB) [??? MB]
LocalDB is a lightweight version of Express that has all its programmability features, yet runs in user mode and has a fast, zero-configuration installation and short list of pre-requisites. This is a single-user engine, so you do not want it if you are running more than 1 Terminal.

---

Direct Links to media downloads can be found here:

http://www.hanselman.com/blog/DownloadSqlServerExpress.aspx

---

SQL Server Management Studio (SSMS) 2016 [771 MB]

SSMS used to be included in the largest installer for SQL 2014, but this is no longer the case. I didn’t have any issues using SSMS 2014 to connect to a 2016 Engine, but I didn’t use it for long. You can find the newest version of SSMS 2016 here:

Download SQL Server Management Studio (SSMS) - SQL Server Management Studio...

---

SQL 2016 Command Line Utilities

I also downloaded the latest version of Command Line Utilities. This contains, for example, sqlcmd.exe which can be used to run T-SQL statements or saved *.sql script files from the command-line (CMD.EXE), or from a *.BAT file.

https://www.microsoft.com/en-us/download/details.aspx?id=53591

---

SQL Native Client (SNAC)

The SQL Native Client can be used for DB connectivity from other applications including PHP. It was last provided with SQL 2012 (v11) and is included as part of the SQL 2016 installation. (reference: Installing - SQL Server Native Client | Microsoft Learn)

---

SQL 2016 ODBC (connector)

Since 2012 was the last SQL version officially supported by SNAC, the preferred connector for SQL is provided by ODBC. The package also includes command-line utilities sqlcmd.exe and bcp.exe. Download here:

Microsoft ODBC Driver for SQL Server - ODBC Driver for SQL Server

… or directly:

Download ODBC Driver for SQL Server - ODBC Driver for SQL Server

---

Installation

When you run the downloaded Media installer, it will prompt for a path to extract, then the Installer will launch. Choose Upgrade if you have an existing installation of SQLEXPRESS, or choose New SQL Server if this is your first installation …

Verify Install

When the installation is complete, you can run the Setup again, choose Tools > Discovery Report to ensure everything installed properly …

The Discovery Report looks like this when all is good…

---

Cleanup

When you have confirmed that everything is working properly, you can use Control Panel > Programs and Features to Uninstall any previous versions of SQL Express (2014 and earlier). If you are warned that uninstallation of one of the components might affect anything to do with 2016, do not proceed - cancel and move on to the next program in the list …

I did this just today after running with 2016 for about a month. After cleanup, when you run the Discovery Report, things look a lot cleaner since previous version are gone (I previously had installations all the way back to 2008 R2) …

[QMcKay]

SQL 2016/2017 JSON Features

Why do you want JSON features in SQL 2016/2017?

A lot of data in the SambaPOS Database is stored in JSON format. Dealing with JSON in SQL Queries in earlier versions of SQL Server was very difficult. With SQL Server 2016/2017, several features were added to make dealing with JSON data much easier. You might not think you need to use these features now, but enabling support for JSON is very simple.

TIP: More and more Tutorials and DB Tools files contain SQL code that relies on the JSON features in SQL Server 2016/2017. So if you follow a Tutorial or install a DB Tools file and it does not seem to work properly, you should check to make sure you have enabled the JSON features of SQL Server 2016/2017 for your Database.

---

Enabling JSON Features

To enable new JSON support in SQL 2016/2017, you need to verify the Compatibility Level of your Database, and change it if it is not set to SQL Server 2016 (130) (or SQL Server 2017 (140)). An upgrade from a previous version will probably have the Compatibility Level set to a lower value (ie. SQL Server 2014 (120)), so you will need to change it. A new installation or DB will probably already have the Compatibility Level set to SQL Server 2016 (130) (or SQL Server 2017 (140)), but you should verify this anyway.

The easiest way to check and set the Compatibility Level is by using SSMS …

[mjb2000]

Great info @QMcKay

This prompted me to upgrade to SQL Express 2016 (I was originally using an old version of 2014 which I already had on my server).

I couldn’t get the direct links to work, do the links with GUID’s expire?

The links on this page seem to work:

Download SQL Server Express

[QMcKay]

@mjb2000

I couldn’t get the direct links to work, do the links with GUID’s expire?

Maybe they do. EDIT: yes, most of the above links are broken

I split that post from another Topic to create a Topic of it’s own, which I had meant to do a long time ago, so that I could update the Welcome to SambaPOS 5 Topic to include a link to this Topic.

---

@mjb2000

The links on this page seem to work:

Thanks for the update! That is a great reference!

[plume]

Hi QMacKay,

I am installing SambaPOS again from scratch, so, as you suggest, I’ll put SQL EXPRESS 2016 (2017 says no compatible with my computer) to be able to take advantage of JSON.

Would you have a small example of how JSON in SQL can help to make report, for instance?

Marc

[JTRTech]

He posted a nice little query the other day to get entity custom data which is stored in json via SQL;

Dual Pins for Users

Documenting to get FOB/User from CustomData (SQL 2016) … ##Query SELECT e.[Id] ,e.[Name] ,jsonData.jsonValue as [FOB] --,e.[CustomData] FROM [Entities] e JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId] JOIN [EntityCustomFields] cf on cf.[EntityTypeId] = e.[EntityTypeId] -- here we "join" the [Entities] table to itself and use the OPENJSON function -- on the [CustomData] column CROSS APPLY OPENJSON(e.[CustomData]) -- this WITH portion allows explicit definition of the schema JSON Keys for o…

[plume]

Yep … but SQL Server 2016 will not install on windows 7 …
And windows 10 is too “intrusive/cloudy” we have a very bad internet conection.

[Jesse]

It will install on win 7. Do you mean it won’t install on 32bit? You don’t need Internet for win10.

[plume]

You have SQL Server 2016/2017 installed on win7?

I have:

• Windows 7 SP1 64bits
• .NET 4.6

[Jesse]

You are right looks like it’s time to upgrade. I was thinking 2014. Win7 was released in 2009 it’s about time to move on. Win 10 is not cloud driven I’m not sure where you get that.

[plume]

@Jesse

Win 10 is not cloud driven I’m not sure where you get that.

I mean that is is heavy, slow, really talkative, infernal update, has a store …

but yes … I think it will be time to abandon my beloved windows 7 (after having abandoned my DOS, 3.1, NT, XP) Oups I forgot windows 2000
The more we go the more OSes are slow … did I tell you that I have a bad internet I woke up today at 3:30 am to be able to have a chance to download (.NET, SQL SERVER, …) it’s now 8:49 and good times are over, just displaying a post starts to take time, so working with OS that communicate a lot with the outside world on a few hundreds kbps network is not simple.

[Jesse]

I understand slow Internet I just don’t see how that relates to win10. You don’t need Internet for it. Anyway looks like you need to update to take advantage of the feature.

[JTRTech]

Windows 10 isn’t slow, Vista and 8 we’re shit I’ll grant that and I was hesitant about 10 but other than the new pretty control panel which has been simar for so long I quite like it.
And it’s definitely not slow on my 13" xps laptop even running 3 he screens - perhaps it’s time to not just upgrade is but hardware too :-p
If you haven’t already a SSD on SQL server is a good upgrade if nothing else.