Migration from SQL Compact to SQL Express

[bbeavis]

Cautionary tale for those considering this migration. My question is what else am I missing/do I need to look for?

I just completed the migration. I first upgraded from 5.3.0.8 to 5.3.0.13. I connected (via single sign-on) to confirm that the site was up and running. I admit I did not do any further inspection.

Next I followed the instructions provided by Lansweeper. I had previously installed SQL Express 2014 a few weeks back in preparation. Just installed defaults.

I was concerned that the instructions required a new install, but assumed the configuration was all in the database and plodded forward.

I did receive the following errors, but could not find any recommendations from Lansweeper kb on these errors. So I went forward to see what I had.

<Start migration error>
Checking schemas...
Deleting data from destination database...
Copying to database...

One or more errors prevented correct data transfer:
Create View or Function failed because no column name was specified for column 2. (view: web50rep72100e6d845d44238bf9f9d9ce584625)
Create View or Function failed because no column name was specified for column 1. (view: web50rep9221aa4438994764845d41eecba6c2c7)
Create View or Function failed because no column name was specified for column 2. (view: web50repcfb523090293423c9471575b9075fbe0)
The given ColumnMapping does not match up with any column in the source or destination.
Execution time was 00:15:00
<End migration error>

The list of issues I have found thus far:

- Had to reapply license file
- Had to run through "first time configuration wizard" twice. Once when reinstalled and again after migration
- IISExpress certificate was set to default. I had to redo our internal ssl certificate. I was not totally surprised by this, because the Lansweeper version upgrade changed some ssl certificate behavior. Such as adding the force ssl feature.
- Had to re-enable authorization on IISExpress. However, once enabled, all of the prior roles settings were restored. I was not looking forward to rebuilding that.
- All of my dynamic groups vanished.
- Some of the dashboard alerts have been duplicated.


Overall, I am content. I expected some bumps, especially since I learned about the re-install part of the process. I am a little surprised that I did not see any notes on the instructions about possible issues such as the license key. I would have gotten that ahead of time and been prepared, versus grumbling about it and digging up the key from email. Perhaps the errors I received are all related to the issues I have had.

What have others experienced? Are there any issues I have yet to discover? Does anyone know what those errors indicate and should I bother? How would I go about accessing/dumping the data from SQL Compact file and finding the missing/bad data/columns?

Hope this helps others and thanks for any comments.

[Daniel_B · Accepted solution]

Our latest installer (5.3.0.15) includes a new migration tool which resolves issues like the one described in this forum topic. Instructions on how to update Lansweeper can be found here.

[bbeavis]

FYI, I figured out the Dynamic Groups. I went to recreate them and I was told the group already existed. So I trolled through the DB and found them in tblAssetGroups (who'd thunk?). They were all in there, but none of them were check off for either Builtin or Dynamic fields. I checked off the Dynamic Field and now they are back.

Not sure how these values got wiped out, but the fix was easy enough.

[bbeavis]

That's good news. I can always rebuild custom reports, so I'm not going to lose sleep over those.

[Daniel_B]

Most issues you are facing seem to have been caused by custom reports with invalid column names. Therefore this migration wasn't successful. Our new release (which isn't published yet) has an updated migration tool which has a better stability to avoid issues in such cases.

If you are still facing issues, could you send a mail to support@lansweeper.com, so we can provide an updated version of the SQL Compact to SQL Server migration tool.

Our next release will also provide a better way to use your own SSL certificates. Details will follow in the KB as soon as the release has been published.