Sql Server LocalDB Installation for New SmartViews

Sql Server LocalDB Installation for New SmartViews

Installation Instructions

As of version 462.1000, SmartViews uses LocalDB 2017 in place of the previous local cache. As such, LocalDB 2017 or later needs to be installed on the machine, and an important patch needs to be applied.


LOCALDB 2019 Installer

The installer can be downloaded from this URL: https://go.microsoft.com/fwlink/?LinkID=866658

Once it's downloaded, do NOT double-click the executable to run it. Instead, open up Windows PowerShell and navigate to where the file was downloaded. Then run the following command:
.\SQLEXPRADV_x64_ENU.exe /qs /IACCEPTSQLSERVERLICENSETERMS /ACTION=Install /FEATURES=LocalDB /UpdateEnabled=False /SkipInstallerRunCheck=1

LocalDB 2017 Installer(Only use if 2019 is not supported on your O\S)


Once it's downloaded, do NOT double-click the executable to run it. Instead, open up Windows PowerShell and navigate to where the file was downloaded. Then run the following command:
.\SQLEXPRADV_x64_ENU.exe /qs /IACCEPTSQLSERVERLICENSETERMS /ACTION=Install /FEATURES=LocalDB /UpdateEnabled=False /SkipInstallerRunCheck=1

There should be a loading bar that finishes relatively quickly, followed by another small pop-up window without a loading bar. Once that's done, the LocalDB installation should be complete. You can verify this by finding it in the list of installed programs on the machine.


Patch CU-31


We will once again run a command in PowerShell to apply the patch:
.\sqlserver2017-kb5016884-x64_ff02621395c103b1381924f4c4b137901a870261.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /SkipInstallerRunCheck=1
If you want to check if a pending reboot is required, you can double-click the executable first, and installer windows will pop up telling you if there is a pending reboot or not. Alternately you can look at the install logs which are located in "C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\{date_code}\Summary_---.txt or SystemConfigurationCheck_Report.htm. Look for the "Reboot Required" message. If this is the case reboot and then process the CU patch update again.

Solutions to Common Issues if SmartViews Install Fails

Old Installer

If this is a fresh install rather than an in-app update, make sure that you're running the latest SmartViews installer. You should verify this by downloading the latest installer from the SmartViews User Guide.

Reboot Required

If there are pending Windows updates, you will probably need to reboot before the installation can finish running. You will see a warning like the one below telling you to restart. Rebooting is also a good idea in general if the no-loading-bar window stays on screen with seemingly nothing happening for a long time. You'll need to run the command in PowerShell again.


Delete Installer Folder After Failed Install

When the above command is executed, a folder called SQLEXPRADV_x64_ENU is created in the same directory as the installer executable. If for whatever reason the install fails and needs to run again, that folder will cause problems if it's already there. The folder should be deleted before trying again to install.
Don't forget to do this step again if you encounter multiple issues installing.

Reorder Environment Variables

In PowerShell, run the following command:
where.exe sqllocaldb
This will display the list of LocalDB versions that are installed. Due to another Microsoft bug, the SmartViews installation can fail if the version numbers are not listed in descending order (e.g. 130 should be listed above 120 for this to work).

If the filepath on the first line has a version less than 140, you can fix this by reordering the Environment Variables. Search for "edit the system environment variables" in the start menu or Control Panel, and then click "Environment Variables...". In the System variables list (the bottom one), select the variable called Path and then click "Edit...". There, click the item containing the latest version's filepath and then click "Move Up" until it is above all of the lesser versions. Finally, click OK to back out of all of these windows.


If you open a new PowerShell window and run where.exe sqllocaldb again, you should see the latest version's filepath listed first.

Uninstall SQL Server 2012 Native Client

Another Microsoft bug may cause an installation of SQL Server 2012 Native Client to prevent the installation of SQL Server LocalDB 2017. If the installation process just seems to stop after the setup window closes, open up Event Viewer and go to Windows Logs -> Application. If you see an error around the time that the install process seemed to stop that mentions 2012 Native Client, then it will need to be temporarily uninstalled.



Make sure to take note of the version number of 2012 Native Client you have installed so that when you reinstall it after LocalDB 2017 is successfully installed, you reinstall the correct version. Note that the LocalDB 2017 installation may automatically set up 2012 Native Client, so check for that in your programs list before you try reinstalling 2012 Native Client from scratch.


Security Update KB5021126

If during the patching process, you are seeing conflicting information about whether the version installed is 14.0.1000.169 or 14.0.3456.2, install security update KB5021126 and then try again.


Solutions to Common Issues if SmartViews Installs Successfully but Has Errors

Sector Size Mismatch (Windows 11 only)

If you have SmartViews installed on Windows 11, there could be issues if the number of your system's physical bytes per sector for atomicity and performance aren't equal. You can check this by navigating to System32 in Command Prompt as an administrator and running this command:
fsutil fsinfo sectorinfo C:


If those numbers are not equal (usually one is 512 and the other is 4096), then this can be resolved by changing the smaller number with a registry key hack. Still running Command Prompt as an admin, run the following command (note that 4095 is 1 less than the greater number):
REG ADD "HKLM\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" /v "ForcedPhysicalSectorSizeInBytes" /t   REG_MULTI_SZ /d "* 4095" /f

You can validate that this worked by running the following command afterwards:
REG QUERY "HKLM\SYSTEM\CurrentControlSet\Services\stornvme\Parameters\Device" /v "ForcedPhysicalSectorSizeInBytes"

Install LocalDB 2019 Instead of Patching LocalDB 2017

Sometimes the CU-31 patch for LocalDB 2017 may fail to install due to other, older installed versions of SQL Server interfering. As long as you are not on Windows 7, Windows Server 2012, or Window Server 2008, you can install LocalDB 2019 instead. Download SQL Server 2019 Express here: https://www.microsoft.com/en-us/download/details.aspx?id=101064
After downloading the EXE, you may need to go into the file properties and unblock it in order to run it.


Run SQL2019-SSEI-Expr.exe. Select "Custom" and then "Install," and wait for SQL Server Express 2019 to finish installing.


When that's done, the SQL Server Installation Center should appear. Select the first option: "New SQL Server stand-alone installation or add features to an existing installation."


Just click next through the first several steps, leaving the default settings as they are. After the "Install Setup Files" step is finished, you'll arrive at the Feature Selection screen. Uncheck "Database Engine Services" and check "LocalDB" before clicking Next.
  

Let the install finish, and then try to run SmartViews again. If you continue to get a "Local Open Connection Error" after starting SmartViews again, see the next step in this article.

Recreate the Database Instance

When starting up SmartViews, users might get an error labelled "Local Open Connection Error." Alternatively, they might receive errors like SQLException error 50 that mention the database instance failing to start (this error is common if LocalDB 2014 was installed previously).




Either of these errors can be resolved by running the four commands below in Windows PowerShell to recreate the LocalDB instance with the newer version.
SQLLocalDB stop "MSSQLLocalDB"
SQLLocalDB delete "MSSQLLocalDB"
(wait at least a minute before running the next command)
SQLLocalDB create "MSSQLLocalDB"
(wait another 30 seconds at least)
SQLLocalDB start "MSSQLLocalDB"
LocalDB v14 is the same as LocalDB 2017, not 2014. Don't be concerned about the version number used to create the new instance being v14.


    • Related Articles

    • SmartViews - Release Notes

      v462.2001 (8/9/2024) Fixed memory limitations and leaks when loading large amounts of data in a View Turned off automatic syncing upon opening/closing SmartViews when the DisableSync flag is true v462.2000 (7/22/2024) Fixed tracking of versions used ...

    • SmartViews Error “The specified table does not exist [sv_Users]” - On Premise Installations

      SmartViews has an "On Premise" type of installation that allows customers to use a local SQL Server instead of the Azure cloud database.   Depending on the version they are on, they may experience this error after updating to later versions: “The ...

    • SmartViews - User Guide

      Prerequisites: Windows Server 2016-2022 Windows 10-11 SQL Server 2012R2-SQL 2019 SQL Server 2017 minimum Cumulative Update (CU) is 31 (14.0.3456.2) Visual 7x-10 .NET 4.6.2 if outgoing ports are blocked at the organization’s firewall, the following ...

    • SmartViews Scheduler User Guide

      PREREQUISITES Supported Visual version are 7.0 – 10.x Windows Server 2016-2022 Windows 10-11 SQL Server 2012R2-SQL 2019 SQL Server 2017 minimum Cumulative Update (CU) is 31 (14.0.3456.2) .NET 4.6.2 ADMINISTRATION INSTALLING Use the following link to ...

    • SmartViews - Local DB / Cache Reset

      What is it? SmartViews has a feature called "Reset Local DB," or "Cache Reset" as it was called before v462.1100. When you make changes in SmartViews, those changes don't immediately get pushed to the Cloud; instead, they are only made in a local ...