How to update database during publish using different connection string

Question

I have a database that I need to update while publishing the web application to a remote server. I want to have a local version of the database and a remote (published) version, so I can make schema changes to the local version without it affecting the published database.

I originally used the remote db connection string in the local copy of my project, and it published fine. I have since changed this connection string to a local db, and specified the remote connection string in my publish settings, ticking both the 'Use this connection string at runtime' and 'Update database' options.

Now when I try to publish, I get this error:

Web deployment task failed. (Could not generate deployment script.
Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service.
Internal Error. The database platform service with type Microsoft.Data.Tools.Schema.Sql.Sql130DatabaseSchemaProvider is not valid. You must make sure the service is loaded, or you must provide the full type name of a valid database platform service.
  Learn more at: http://go.microsoft.com/fwlink/?LinkId=221672#ERROR_EXECUTING_METHOD.)

Both connection strings are definitely valid - I have updated both databases using them. I have successfully published using only the remote connection string. It only seems to fail when I have a different connection string (i.e. my local db) mentioned in my local Web.config. If I change my local Web.config connection string back to the remote one, it works again.

Apologies if this is a duplicate - I have had a look online for the solution, but haven't found anything that seems to be similar to what I'm experiencing.

Answer 1

I think part of the issue was that the database was local, and using (LocalDb) in the connection string - I assume this could not be connected to when publishing. I changed this to a database on another server, and manually edited the publish settings to (effectively) these settings, and it now works as I wanted.

.pubxml file:

<ObjectGroup Name="SomeDatabaseContext" Order="1" Enabled="True">
  <Destination Path="Data Source=TestServer;Initial Catalog=SomeDatabase;Persist Security Info=True;User ID=AppUser;Password=SomePassword;Application Name=EntityFramework" Name="Data Source=TestServer;Initial Catalog=SomeDatabase;Persist Security Info=True;User ID=AppUser;Password=SomePassword;MultipleActiveResultSets=True;Application Name=EntityFramework" />
  <Object Type="DbDacFx">
    <PreSource Path="Data Source=DevServer;Initial Catalog=SomeDatabase.dev;Persist Security Info=True;User ID=AppUser;Password=SomePassword;Application Name=EntityFramework" includeData="False" />
    <Source Path="$(IntermediateOutputPath)AutoScripts\ECommerceSiteContext_IncrementalSchemaOnly.dacpac" dacpacAction="Deploy" />
  </Object>
  <UpdateFrom Type="Web.Config">
    <Source MatchValue="Data Source=DevServer;Initial Catalog=SomeDatabase.dev;Persist Security Info=True;User ID=AppUser;Password=SomePassword;Application Name=EntityFramework" MatchAttributes="$(UpdateFromConnectionStringAttributes)" />
  </UpdateFrom>
</ObjectGroup>

Web.config:

<connectionStrings>
  <add name="SomeDatabaseContext" connectionString="Data Source=DevServer;Initial Catalog=SomeDatabase.dev;Persist Security Info=True;User ID=AppUser;Password=SomePassword;Application Name=EntityFramework" providerName="System.Data.SqlClient" />
</connectionStrings>

Doing this allows me to use a dev version of the database while I am working on the site (potentially needing to change the database schema) without affecting the test (published) version, and allows me to publish changes without having to remember to Update-Database against the test database when publishing.