SQL Server目标平台的DACPAC不会发布到Microsoft Azure SQL v12。

huangapple go评论132阅读模式
英文:

SQL Server target platform dacpacs won't publish to Microsoft Azure SQL v12

问题

我正在重新创建一个包含正常运行的Azure SQL服务器的Azure资源组部署。我正在将一个dacpac重定向到具有完全相同规格的Azure SQL服务器实例,我相信我是以与一年前相同的方式进行操作。

我的方法使用了一个从ASP Net Core Web App解决方案中分离出来的Visual Studio数据库项目(在VS SQL Server Object Explorer中的Web应用数据库>创建新项目):

创建目标dacpac

  • 在VS Solution Explorer中右键单击项目名称并选择属性。
  • 在VS项目设置*下选择目标平台。
  • 在属性>调试中替换默认的目标连接字符串。
  • 导航回VS Solution Explorer。再次右键单击VS项目并选择项目快照。
  • 以下文件类型rollbasesqldatabase_20230301_14-19-35.dacpac将被写入VS项目中的快照文件夹。
  • 在VS SQL Server Object Explorer中导航到连接的Azure SQL服务器实例。
  • 右键单击并选择发布数据层应用程序。

我遇到的错误很熟悉。但我对所有版本都遇到了这个错误:
发布失败。在生成部署计划期间发生错误。 无法继续部署。 指定了SQL Server 2014作为目标平台的项目无法发布到Microsoft Azure SQL Database V12。 这对于我来说在目标平台SQL Server 2012、2016和2019上都发生了。

我的数据源服务器是:

Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=aspnet-rollbase-6D7D2449-EB25-45CE-A8D3-EDAB69CDCAB3;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

我在VS中的发布数据库连接如下:

Data Source=(localdb)\ProjectsV13;Initial Catalog=master;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

我的属性>调试编辑来自:

Data Source=(localdb)\ProjectsV13;Initial Catalog=rollbasesqldatabase;Integrated Security=True;Pooling=False;Connect Timeout=30

编辑后的
Data Source=rollbasesqlserver.database.windows.net;Initial Catalog=rollbasesqldatabase;Persist Security Info=True;User ID=rollbaseAdmin;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=True;TrustServerCertificate=False

我注意到了(右键单击)发布数据层应用程序的服务器引用V13。这是否相关?这篇文章ProjectsV13 LocalDB实例的目的对我来说不太明确。

除此之外,我不知道为什么这个dacpac部署在我以前做过的情况下失败了。

英文:

I am recreating an Azure resource group deployment that contains a working azure sql server. I am repeating the targeting of a dacpac to an azure sql server instance that has exactly the same specs. I believe I am doing it the same way I did it a year ago.

My method uses a Visual Studio database project spun off ASP Net Core Web App Solution (web app database in VS SQL Server Object Explorer>Create New Project)

Create targeted dacpac

  • right-click on project name in VS Solution Explorer & choose Properties
  • under VS Project Settings* choose the target platform
  • replace default Target Connection String in Properties>Debug
  • navigate back to VS Solution Explorer. Right-click on VS Project again & choose Project Snapshot
  • the following file type rollbasesqldatabase_20230301_14-19-35.dacpac is written to Snapshots folder in VS project
  • navigate to connected Azure sql server instance in VS SQL Server Object Explorer
  • right click and choose Publish Data-tier Application

The error I'm getting is familiar. But I'm getting it for all versions:
Publish failed. An error occurred during deployment plan generation. Deployment cannot continue. A project which specifies SQL Server 2014 as the target platform cannot be published to Microsoft Azure SQL Database V12.
This occurs for me on target platforms SQL Server 2012, 2016 & 2019 too

My data source server is:

Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=aspnet-rollbase-6D7D2449-EB25-45CE-A8D3-EDAB69CDCAB3;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

My publish database connection is on this server in VS:

Data Source=(localdb)\ProjectsV13;Initial Catalog=master;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False

My Properties>Debug edit is from:

Data Source=(localdb)\ProjectsV13;Initial Catalog=rollbasesqldatabase;Integrated Security=True;Pooling=False;Connect Timeout=30

Edited
Data Source=rollbasesqlserver.database.windows.net;Initial Catalog=rollbasesqldatabase;Persist Security Info=True;User ID=rollbaseAdmin;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;Encrypt=True;TrustServerCertificate=False

I noticed the V13 reference of the server which hosts the database that I (right-click) Publish Data-tier Application. Is this relevant? This post Purpose of ProjectsV13 LocalDB instance seems inconclusive to me.

Other than this I have no idea why this dacpac deployment fails when I have done it before.

答案1

得分: 1

以下是您要翻译的内容:

有多种方法可以将ASP Net Core Entity Framework SQL Server数据库部署到Azure,以便在Azure SQL后端运行Web应用程序。

  • 使用GitHub Actions/Workflow的dacpacYAML文件
  • 使用Visual Studio发布数据层应用程序,使用dacpac
  • 使用Microsoft SQL Server Management Studio(与上述方法相同)
  • 还有其他方法,如Azure Data Studio和命令行中的sqlpackage,都使用dacpac文件

但我发现另一种方法可以生成更有帮助的警告/错误消息。假设Azure资源已存在,因此有Azure SQL Server和数据库。

  • 在MSSM中连接到Azure SQL Server数据库
  • 右键单击数据库名称
  • 选择任务
  • 选择升级数据层应用程序

这种方式使我升级了从空白的Azure SQL数据库模板到带有架构的数据库。

我仍然遇到错误,但这些错误是具体的,因此非常有帮助。

SQL Server目标平台的DACPAC不会发布到Microsoft Azure SQL v12。

这里的第一个警告是以前的'发布失败。发生错误...等等',但这只是一个警告。它通过表示可能存在'兼容性问题'来限定此警告。真正的错误是我在开发后留下了一个sa用户,删除此用户允许升级成功进行。

这显示了错误消息有多大的变化。在获取这些信息之前,我不知道为什么我的数据库部署会失败。

英文:

There are several ways to deploy an ASP Net Core Entity Framework SQL Server Database to Azure. In order to run a Web App with an Azure SQL backend.

  • GitHub Actions/Workflow dacpac with YAML file
  • Visual Studio publish Data-tier Application using dacpac
  • Microsoft SQL Server Management Studio (same as above)
  • As well as others like Azure Data Studio & the command line sqlpackage all using dacpac file

But I found another way that produced more helpful warning/error messages.
Assuming the Azure resources are present. And therefore there is a Azure SQL Server & Database.

  • In MSSM connect to the Azure SQL Server Database
  • Right click on the database name
  • Choose Tasks
  • Choose Upgrade Data-tier Application

So in this way I take it I was upgrading from blank Azure SQL Database template to one with a schema.

I still got errors. But these were specific so helpful.

SQL Server目标平台的DACPAC不会发布到Microsoft Azure SQL v12。

Here the first warning is the previous 'Publish failed. An error occurred ... etc' But it's just a warning. And qualifies this by saying there may be 'compatibility issues'. The real error is that I made a dumb mistake by leaving an sa user hanging around after development. Deleting this user allowed the upgrade to go ahead successfully.

This shows how much variation there is with error messages. Prior to getting this information I had no idea why my database deployment was failing.

huangapple
  • 本文由 发表于 2023年3月1日 11:31:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/75599338.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定