This article explains how to migrate a Lansweeper database hosted in the Microsoft SQL Compact database server to Microsoft SQL Server.
If you perform an Easy Install of the Lansweeper software, the Lansweeper database is installed under Microsoft's free SQL Compact database server. SQL Compact has a built-in database size limit of 4GB. This is a limitation imposed by Microsoft, not by Lansweeper. 4GB should allow you to comfortably scan several hundred assets. If you have a network of more than 1,000 assets however, a SQL Compact database may be insufficient, for database size as well as performance reasons. Luckily, you can easily move all of your data, reports and settings to SQL Server if required. Recent versions of the free SQL Server Express (2008 R2, 2012, 2014, 2016 and 2017) have a size limit of 10GB instead of 4GB. You can of course move to a non-Express (non-freeware) edition of SQL Server as well, which is not limited in size.
To migrate a SQL Compact database to SQL Server, do the following:
- Download the latest Lansweeper installer.
- If your current SQL Compact installation is not on the latest Lansweeper version, use the installer you just downloaded to update the installation. Update instructions can be found in this knowledge base article.Don't skip this step! The migration won't work if your SQL Compact database is on a different Lansweeper version than the SQL Server database you'll be creating later on.
- 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).
- Create a copy of your SQL Compact database, which stores all of your data, reports and settings, 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
- 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.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.
- If you are using Lansweeper 6.0 or a more recent Lansweeper release, back up the following file as well:Program Files (x86)\Lansweeper\Key\Encryption.txt
- Replace your existing SQL Compact installation with a SQL Server one by performing a new, Advanced Install with the latest Lansweeper installer. Make sure you select SQL Server as your database server. Detailed instructions for performing an Advanced Install can be found in this knowledge base article.You must reinstall all Lansweeper components: database, service and web console. You don't have to install them all on the same machine, but it is absolutely critical that you set up a new SQL Server database and connect a new service and web console to it. Don't try to manually create the database either. Let the installer do this.
- Configure your own custom database password to replace the random password generated by the Lansweeper installer, by following the instructions in this knowledge base article.
- Once again stop the Lansweeper and web server services in Windows Services.
- 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 the correct instance is not already selected, hit the lower Browse... button to submit the name of the SQL Server instance you want to migrate to. You can use the built-in lansweeperuser SQL user and the database password you configured earlier to make the connection.
- The schema versions of both databases should match, indicating that they're both on the same Lansweeper version. Hit Migrate Database to start the migration process. Note that this may take a while to complete.
- Restore the Website subfolders and Encryption.txt file you created a backup of earlier.
- Restart the Lansweeper and web server services in Windows Services.