How to get the SQL Express Offline Installer

When working in a test environment you don't exactly want something to have internet access; so you need to be able to download files in an offline setting.  I had this need when I was working on a SQL numbering issue where SQL server added 1000 to the item count.  Not particularly helpful and annoying to fix.  The SQL version I was working with was 12 (or 2014) for those at home keeping track by year.  At any rate I needed to fix this 1000 item count but part of the issue was that the count is a primary key.

To get the offline install of SQL Server in this case we are going to use Express 2019, we need to go to https://www.microsoft.com/en-ca/sql-server/sql-server-downloads

https://www.microsoft.com/en-ca/sql-server/sql-server-downloads

When you download the Express download you will get a Application download called SQL2019-SSEI-Expr.  

Run the application.  When the application runs you get three options.  Basic, Custom and Download media which we are going to do.

When you select download media you get a few more options.  For the purposes of what I want I am selecting Express Advanced.

After pressing download; it will download to the directory where you have specified it to download.  In my case C:\Users\Trevor Tye\Downloads

Here I have it in my download directory.

Upgrading From SQL Compact DB to SQL Express (LocalDB)

As of version 7.2.107.4 (Jan 2020) of lansweeper it was the last to support sql compact (express) as it was removed in version 7.2.108.6.  Doing a simple double click upgrade is not possible, and the process for doing the migration is pretty straight forward.  

I have a video here of the whole migration.

If your running a version lower then 7.2.108.6 you need to upgrade to 7.2.108.6.  You can see your version of lansweeper after you login by going to Configuration -> Your Lansweeper License

Before you start make a backup of your Lansweeper data.  To do that in my documents folders I created two folders labelled 721086 and 8013014.  In 721086 with the previous versions of lansweeper (in my case 721054) just in case you have to recover, and repeat the process for your upgrade to version 8013014.  In there after stopping the Lansweeper Server and IIS Express Services

Go to your Lansweeper program directory (typically C:\Programs (x86)\Lansweeper) and backup 2 Folders SQLData and Key.  Key has your encryption key (if enabled) and SQLData has your SQL COMPACT and SQL Express databases (SQL Express only after you have upgraded to version 7.2.108.6)

You can read about the entire process from this Lansweeper KB
https://www.lansweeper.com/knowledgebase/backing-up-your-installation/

Lansweeper has done a great job automating this, after you upgrade to version 7.2.108.6 your database will be converted, it takes about 5 minutes.  Once the installer has finished you will see a new service called Lansweeper LocalDB Service.

To upgrade to version 8 download the lastest version of Lansweeper https://www.lansweeper.com/update-lansweeper/ and repeat the steps from the beginning of the blog but save the SQLDATA and Key to the 8013014 directory.  Once done you will be on the latest version of lansweeper (at the time of this post).  The version of SQL Express Lansweeper is using is version 2014.

If you want to upgrade the database you can follow this KB from Lansweeper, https://www.lansweeper.com/knowledgebase/moving-your-database-from-sql-compact-to-sql-server/

***UPDATE***

After this update I had an issue with email tickets being 10 tickets lower then the last Lansweeper ticket. To fix the issue I reset the ticket id counter as described in https://www.lansweeper.com/knowledgebase/resetting-the-ticket-id-counter/

The section we want to follow is the "Resetting the ticket ID counter if you are using SQL LocalDB or SQL Server" and before you go ahead and do that be sure to take a backup of your Lansweeper LocalDB https://www.lansweeper.com/knowledgebase/backing-up-your-installation/#heading2

I will be doing a post about doing this later.

Reference

https://www.lansweeper.com/knowledgebase/sql-compact-deprecated/
https://www.lansweeper.com/knowledgebase/sql-compact-deprecated/#heading2
https://www.lansweeper.com/news/installing-lansweeper-end-of-support-for-sql-compact/
https://www.lansweeper.com/knowledgebase/moving-your-database-from-sql-compact-to-sql-server/
https://www.lansweeper.com/knowledgebase/resetting-the-ticket-id-counter/