Converting a deprecated SQL Compact database

From March 2020 onward, Microsoft SQL Compact is deprecated and no longer a supported database server for hosting the Lansweeper database. SQL Compact is no longer offered as an option in the Lansweeper installer. In addition, when updating a SQL Compact installation to Lansweeper version 8.0 or higher, you are informed that a conversion to SQL LocalDB or SQL Server is required prior to updating. This article explains how to convert a Lansweeper database hosted in SQL Compact to SQL LocalDB or SQL Server. Existing data, reports and settings remain intact.

The exact procedure you should follow for converting your SQL Compact database depends on which Lansweeper version you are currently using. It also depends on whether you have a fully functional Lansweeper installation under SQL Compact, or just a SQL Compact database file. If you have a functional Lansweeper installation, you can check its version number in the Configuration\Your Lansweeper License section of the web console.

Convert SQL Compact installation that is on version 7.2.107.4 or lower

If your SQL Compact installation is on Lansweeper version 7.2.107.4 or lower, you can easily migrate to SQL LocalDB by updating your Lansweeper installation using this 7.2.108.6 installer. Run the installer on your Lansweeper server and choose the Upgrade option. The update converts your database to SQL LocalDB. This process is automatic and requires no user input, though the installer will indicate that this conversion is about to happen. Once your Lansweeper installation has been updated to version 7.2.108.6 and converted to SQL LocalDB, you can continue to update it to more recent Lansweeper versions.

Convert SQL Compact installation that is on version 7.2.108.6

If your SQL Compact installation is on Lansweeper version 7.2.108.6, you must manually convert it to SQL LocalDB or SQL Server. The steps for completing this process can be found below.

Step 1: choose your preferred database server

Your SQL Compact installation can be converted to SQL LocalDB or SQL Server. If you choose to convert your SQL Compact installation to SQL Server, first install your SQL Server instance on a machine of your choice. If you choose to convert your SQL Compact installation to SQL LocalDB, you don't need to set up the SQL instance yourself. The Lansweeper installer will do this for you. If you're unsure whether you should choose SQL Server or SQL LocalDB as your database server, we recommend reviewing this database server comparison article.

Step 2: stop the services

Stop the Lansweeper Server service in Windows Services. Stop your web server service in Windows Services. Keep in mind that this will log everyone out of the console. Your web server service is either IIS Express or World Wide Web Publishing Service (IIS).

stopping the Lansweeper Server service
stopping the web server service

Step 3: back up your SQL Compact database and other Lansweeper files

Your SQL Compact database stores all of your data, reports and settings. Create a copy of your SQL Compact database file and move it to a safe location outside of the Program Files folder. Your SQL Compact database file is: Program Files (x86)\Lansweeper\SQLData\lansweeperdb.sdf

creating a backup of a SQL Compact database

If you added any documents, images, widgets or other files to Lansweeper, back these up as well. Information on which folders store which files can be found in this knowledge base article. Make sure to back up the following file as well, which is used to encrypt and decrypt the credentials in your database: Program Files (x86)\Lansweeper\Key\Encryption.txt

Do not back up the entire Website folder. Only back up the specific subfolders you need. Backing up and restoring the entire Website folder can lead to issues.

Step 4: perform an Advanced Install under SQL LocalDB or SQL Server

Replace your existing SQL Compact installation with a SQL LocalDB or SQL Server one by performing a new, Advanced Install with this 7.2.108.6 installer. Run the installer, choose New installation and then Advanced install. You can then select your preferred database server, SQL LocalDB or SQL Server. If you select SQL Server, you will be asked to point the installer to your SQL Server instance.

Reinstall all Lansweeper components: database, service and web console.

Step 5: migrate the SQL Compact data

Once again, stop the Lansweeper and web server services in Windows Services. Afterward, run the following executable, found on the machine hosting your Lansweeper service: Program Files (x86)\Lansweeper\Tools\CompactToServer.exe

Hit the upper Browse... button, select the lansweeperdb.sdf file you created a backup of earlier and hit Open. If you chose SQL Server as your preferred database server, you may also need to hit the lower Browse... button to select your SQL Server instance.

The schema versions of the source and destination databases should match, indicating that they're both on the same Lansweeper version. Hit Migrate Database to start the migration process. Note that, depending on the size of your database, this process may take a while to complete.
migrate SQL Compact database to SQL LocalDB using CompactToServer.exe
CompactToServer.exe database schema versions

Step 6: restore Lansweeper files and restart services

Restore the Website subfolders and Encryption.txt file you created a backup of earlier. Afterward, restart the Lansweeper and web server services in Windows Services.

starting the web server service

Convert SQL Compact database file, lansweeperdb.sdf

If you only have a SQL Compact lansweeperdb.sdf file and not a fully functional Lansweeper installation, you can convert it to SQL LocalDB or SQL Server by following the steps below.

Step 1: restore your database file in a new Lansweeper installation

Perform a new, Easy Lansweeper installation using this 7.2.107.4 installer. Afterward, stop the Lansweeper Server and IIS Express services in Windows Services. Place your lansweeperdb.sdf file in the Program Files (x86)\Lansweeper\SQLData folder, overwriting the default file already in there, and restart the services. You now have a functional 7.2.107.4 SQL Compact installation that you can convert to SQL LocalDB or SQL Server.

Step 2: choose your preferred database server

Your SQL Compact installation can be converted to SQL LocalDB or SQL Server. If you choose to convert your SQL Compact installation to SQL Server, first install your SQL Server instance on a machine of your choice. If you choose to convert your SQL Compact installation to SQL LocalDB, you don't need to set up the SQL instance yourself. The Lansweeper installer will do this for you. If you're unsure whether you should choose SQL Server or SQL LocalDB as your database server, we recommend reviewing this database server comparison article.

Step 3: stop the services

Stop the Lansweeper Server service in Windows Services. Stop your web server service, IIS Express, in Windows Services. Keep in mind that this will log everyone out of the console.

stopping the Lansweeper Server service
stopping the web server service

Step 4: back up your SQL Compact database and other Lansweeper files

Your SQL Compact database stores all of your data, reports and settings. Create a copy of your 7.2.107.4 SQL Compact database file and move it to a safe location outside of the Program Files folder. Your SQL Compact database file is: Program Files (x86)\Lansweeper\SQLData\lansweeperdb.sdf

creating a backup of a SQL Compact database

If you added any documents, images, widgets or other files to Lansweeper, back these up as well. Information on which folders store which files can be found in this knowledge base article. Make sure to back up the following file as well, which is used to encrypt and decrypt the credentials in your database: Program Files (x86)\Lansweeper\Key\Encryption.txt

Do not back up the entire Website folder. Only back up the specific subfolders you need. Backing up and restoring the entire Website folder can lead to issues.

Step 5: perform an Advanced Install under SQL LocalDB or SQL Server

Replace your SQL Compact installation with a SQL LocalDB or SQL Server one by performing a new, Advanced Install with the same 7.2.107.4 installer you used earlier. Run the installer, choose New installation and then Advanced install. You can then select your preferred database server, SQL LocalDB or SQL Server. If you select SQL Server, you will be asked to point the installer to your SQL Server instance.

Reinstall all Lansweeper components: database, service and web console.

Step 6: migrate the SQL Compact data

Once again, stop the Lansweeper and web server services in Windows Services. Afterward, run the following executable, found on the machine hosting your Lansweeper service: Program Files (x86)\Lansweeper\Tools\CompactToServer.exe

Hit the upper Browse... button, select the lansweeperdb.sdf file you created a backup of earlier and hit Open. If you chose SQL Server as your preferred database server, you may also need to hit the lower Browse... button to select your SQL Server instance.

The schema versions of the source and destination databases should match, indicating that they're both on the same Lansweeper version. Hit Migrate Database to start the migration process. Note that, depending on the size of your database, this process may take a while to complete.
migrate SQL Compact database to SQL LocalDB using CompactToServer.exe
CompactToServer.exe database schema versions

Step 7: restore Lansweeper files and restart services

Restore the Website subfolders and Encryption.txt file you created a backup of earlier. Afterward, restart the Lansweeper and web server services in Windows Services.

starting the web server service

 

Related Articles