Using SQL 2017 LocalDB as Shadow Database

eric_m

We have a project using SQL Change Automation, building and deploying via VSTS. One of our projects needs to use some SQL 2017-specific functionality; we're targeting Azure SQL. We've set up a private agent with the SQL 2017 LocalDB tools installed and set the build to run on this agent. The "Set Target Database" step works fine and starts the localdb, but the "Build" step fails:

Set Target Database:

2018-09-21T16:20:01.0072755Z ##[section]Starting: Set Target Database

2018-09-21T16:20:01.0077371Z ==============================================================================

2018-09-21T16:20:01.0077593Z Task : ReadyRoll Set Target Database

2018-09-21T16:20:01.0077798Z Description : Set ReadyRoll target database name and login credentials

2018-09-21T16:20:01.0077969Z Version : 1.0.8

2018-09-21T16:20:01.0078135Z Author : Redgate Software

2018-09-21T16:20:01.0078350Z Help : [More Information](https://www.red-gate.com/readyroll)

2018-09-21T16:20:01.0078573Z ==============================================================================

2018-09-21T16:20:02.7175901Z ##[command]"SqlLocalDB.exe" create ReadyRollShadow -s

2018-09-21T16:20:12.4941610Z LocalDB instance "ReadyRollShadow" created with version 14.0.1000.169.

2018-09-21T16:20:12.4955167Z

2018-09-21T16:20:12.4987617Z LocalDB instance "ReadyRollShadow" started.

2018-09-21T16:20:12.4991202Z

2018-09-21T16:20:12.7779243Z ##[section]Finishing: Set Target Database

Build:

018-09-21T16:20:12.7799613Z ##[section]Starting: Build solution EY.GTP.Ingestion.Databases.sln
2018-09-21T16:20:12.7804890Z ==============================================================================
2018-09-21T16:20:12.7805163Z Task         : Visual Studio Build
2018-09-21T16:20:12.7805443Z Description  : Build with MSBuild and set the Visual Studio version property
2018-09-21T16:20:12.7805830Z Version      : 1.126.0
2018-09-21T16:20:12.7806185Z Author       : Microsoft Corporation
2018-09-21T16:20:12.7806395Z Help         : [More Information](https://go.microsoft.com/fwlink/?LinkID=613727)
2018-09-21T16:20:12.7806652Z ==============================================================================
2018-09-21T16:20:14.3658159Z ##[command]"J:\agent1\_work\_tasks\VSBuild_71a9a2d3-a98a-4caa-96ab-affca411ecda\1.126.0\ps_modules\MSBuildHelpers\vswhere.exe" -version [15.0,16.0) -latest -format json
2018-09-21T16:20:14.9924040Z ##[command]"C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\MSBuild\15.0\Bin\msbuild.exe" "J:\agent1\_work\5\s\src\Databases\Databases.sln" /nologo /nr:false /dl:CentralLogger,"J:\agent1\_work\_tasks\VSBuild_71a9a2d3-a98a-4caa-96ab-affca411ecda\1.126.0\ps_modules\MSBuildHelpers\Microsoft.TeamFoundation.DistributedTask.MSBuild.Logger.dll";"RootDetailId=1cbe657c-5df1-405c-ae68-0ddec74ab168|SolutionDir=J:\agent1\_work\5\s\src\Databases"*ForwardingLogger,"J:\agent1\_work\_tasks\VSBuild_71a9a2d3-a98a-4caa-96ab-affca411ecda\1.126.0\ps_modules\MSBuildHelpers\Microsoft.TeamFoundation.DistributedTask.MSBuild.Logger.dll" /p:GenerateSqlPackage=True @J:\agent1\_work\5\s\setTargetDatabase.rsp /p:platform="any cpu" /p:configuration="release" /p:VisualStudioVersion="15.0" /p:_MSDeployUserAgent="VSTS_e1e91471-823a-4879-ab5b-bd7d94ef6b5d_build_120_0"

...

2018-09-21T16:20:20.7757331Z   Validating Deployment Scripts...
2018-09-21T16:20:20.7757658Z       Ensuring Shadow database is up-to-date
2018-09-21T16:20:20.9229283Z ##[error]src\Databases\Databases.sqlproj(0,0): Error : An error occurred while attempting to verify your deployment scripts: The Sql Server LocalDB instance 'ReadyRollShadow' does not exist. To create it, open a Command Prompt window and execute the following: SqlLocalDb create ReadyRollShadow

Is there a specific parameter we need to set or something we need to update to get LocalDB 2017 to work?

Greg_Smulko

Hello Eric,

I do confirm that LocalDB 2017 is supported by SQL Change Automation projects and doesn't require any additional configuration.
However, there is a known issue regarding LocalDB itself. It should be enough just to delete the instance by running: 

SqlLocalDB.exe delete ReadyRollShadow

For a more detailed guidance, see https://www.red-gate.com/sca/localdb-instance-troubleshooting .

Please also note, that in case you encounter an error similar to:

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Users\usernameDatabaseName.mdf'

you just need to update the LocalDB with the Cumulative Update >= 6. For more details see https://dba.stackexchange.com/a/191748/161450 .

I also recommend updating the legacy ReadyRoll VSTS pipeline to a recent one that uses SQL Change Automation PowerShell.

Also, I'm pretty sure that you've done this already, but for the future reference, it's recommended to target a proper database version in a sqlproj file. You can do this either using the project properties page or by directly editing the sqlproj file and setting the below value (in this case for SQL Server 2017):

<DSP>Microsoft.Data.Tools.Schema.Sql.Sql140DatabaseSchemaProvider</DSP>

Alex B

Hi All,

The error ended up being "LocalDB parent instance version is invalid: MSSQL11E.LOCALDB" which was resolved by deleting the GUID entry located at HKEY_CURRENT_USER\SOFTWARE\Microsoft\Microsoft SQL Server\UserInstances (this may have just needed the data for the ParentInstance value modified to MSSQL14E.LOCALDB).

Kind regards,
Alex