AzureDevOps-Azure SQL Server部署问题和解决方案 [英] AzureDevOps - Azure SQL Server deployment Problems and Solution
问题描述
部署Azure SQL服务器时遇到的众所周知问题的解决方案是什么?
What are the solutions to well known issues encountered while deployment Azure SQL server.
##[error]System.Management.Automation.ParentContainsErrorRecordException: *** Deployment cannot continueAn error occurred during deployment plan generation.
问题2
##[error]Publishing to database 'testdb' on server 'ajyatest.database.windows.net'. Initializing deployment (Start) Initializing deployment (Failed) *** Could not deploy package. Unable to connect to master or target server 'testdb'. You must have a user with the same password in master or target server 'testdb'.
问题3
##[error]System.Management.Automation.RuntimeException: No resource found with serverName ajyatest1, serverType Microsoft.Sql/servers in subscription <SubscriptionId>. Specify the correct serverName/serverType and try again.
问题4
Server name 'ajyatest1' is not in the right format. Use FQDN format like 'yyy.database.windows.net'
问题5
##[error]System.Net.WebException: The remote server returned an error: (404) Not Found.
问题6
##[error]Start IP address of firewall rule cannot exceed End IP address.
问题7
##[error]Invalid value provided for parameter: startIpAddress
问题8
##[error] Named pipes provider, error: 40 – could not open a connect to SQL.
Possible suggestions
问题9
如何为SQLPackage.exe配置超时?
Problem 9
How to configure timeout for SQLPackage.exe ?
托管代理是否支持具有AAD集成身份验证的Azure SQL数据库部署?
Does Azure SQL Database deployment with AAD integrated authentication supported on hosted agent?
Azure SQL数据库部署-导入的Invoke-Sqlcmd不支持connectionString
Azure SQL Database Deployment - Imported Invoke-Sqlcmd doesn't support connectionString
SqlPackage.exe参数/p:BackupDatabaseBeforeChanges = true 在Azure上失败
SqlPackage.exe argument /p:BackupDatabaseBeforeChanges=true fails on Azure
Azure SQL发布-SQL脚本文件-无法将秘密值作为变量传递
Azure SQL Publish - SQL Script File - Not able to pass secret value as Variable
推荐答案
问题1
##[error]System.Management.Automation.ParentContainsErrorRecordException: *** Deployment cannot continueAn error occurred during deployment plan generation.
原因
您要在其上部署dacpac的SQL Server的目标平台与dacpac文件的目标平台不同.
Target platform of SQL server on which you are trying to deploy your dacpac is different than target platform of dacpac file.
可能的解决方案
-
在dacpac项目中更改目标平台，使其与SQL Server的目标平台相同.
Change target platform in your dacpac project as same as target platform of SQL server.
将/p:AllowIncompatiblePlatform = true参数添加为WinRm SQL Server任务的附加参数.
Add /p:AllowIncompatiblePlatform=true argument as additional argument to WinRm SQL server task.
问题2
##[error]Publishing to database 'testdb' on server 'ajyatest.database.windows.net'. Initializing deployment (Start) Initializing deployment (Failed) *** Could not deploy package. Unable to connect to master or target server 'testdb'. You must have a user with the same password in master or target server 'testdb'.
原因
SQL服务器的用户名或密码错误.
Wrong username or password to SQL server.
可能的解决方案
检查用户名和密码是否适合SQL Server.[您可以使用SSMS验证用户名密码]
Check if username and password are right for SQL server. [You can verify username password using SSMS]
##[error]System.Management.Automation.RuntimeException: No resource found with serverName ajyatest1, serverType Microsoft.Sql/servers in subscription <SubscriptionId>. Specify the correct serverName/serverType and try again.
原因
错误的SQL Server名称错误.
Wrong SQL server name is wrong.
可能的解决方案
检查Azure门户上是否存在具有相同名称的SQL Server.
Check if SQL server with same name exist on Azure Portal.
Server name 'ajyatest1' is not in the right format. Use FQDN format like 'yyy.database.windows.net'
原因
SQL服务器作为输入，未作为FQDN提供.
SQL server as input in not being provided as FQDN.
可能的解决方案
错误中提到的本身以'yyy.database.windows.net'格式提供SQL Server
As mentioned in error itself provide SQL server in format 'yyy.database.windows.net'
##[error]System.Net.WebException: The remote server returned an error: (404) Not Found.
原因
检查用于创建防火墙规则的端点URL.验证端点的URL是否为最新.
Check for endpoint URL for creating firewall rules. Verify if URL for endpoint is current.
可能的解决方案
检查端点有什么问题.用户如何创建端点.
Check what’s wrong with endpoint. How user have created endpoint.
##[error]Start IP address of firewall rule cannot exceed End IP address.
原因
用于创建防火墙规则的结束IP"小于开始IP".
End IP is smaller than Start IP for creating firewall rule.
可能的解决方案
起始IP的IP地址应小于结束IP地址.
IP address for start IP should be smaller than end IP address.
##[error]Invalid value provided for parameter: startIpAddress
原因
IP地址格式不正确.
IP address is not in right format.
可能的解决方案
起始IP的IP地址应小于结束IP地址.
IP address for start IP should be smaller than end IP address.
##[error] Named pipes provider, error: 40 – could not open a connect to SQL.
Possible suggestions
建议
对于onPrem SQL Server1. SQL Server名称应采用正确的格式server \ instance.2.验证连接字符串.3.检查是否在SQL实例上启用了命名管道(NP).4.是否启用了远程连接.
For onPrem SQL server 1. SQL server name should be in right format server\instance. 2. Verify connection string. 3. Check if Named Pipes (NP) is being enabled on the SQL instance. 4. Is Remote connection enabled.
For Azure SQL server
1.它可能导致按应用程序节流.2.尝试使用端口1433给出一个Azure SQL服务器名称.servername.database.windows.net，1433;
1. It could cause as per application throttled. 2. Try to give azure SQL server name with port 1433. E.g. servername.database.windows.net,1433;
如何为SQLPackage.exe配置超时?
How to configure timeout for SQLPackage.exe ?
解决方案
/p:CommandTimeout=1200 /TargetTimeout: 1200
问题10
托管代理是否支持具有AAD集成身份验证的Azure SQL数据库部署?
Problem 10
Does Azure SQL Database deployment with AAD integrated authentication supported on hosted agent?
解决方案
否，AAD集成身份验证尝试使用与运行代理程序的用户相同的用户登录SQL Server.由于安全原因，托管代理用户将没有对SQL Server的权限，因此它将失败.换句话说，托管代理不支持AAD集成身份验证.以为您可以使用Active Directory-密码身份验证.
No, AAD integrated authentication tries to login to SQL server with same user with which Agent is running. Since hosted agent user won't have permission on the SQL server because of security reasons.Hence it will fail. In other words, AAD integrated authencation is not supported for Hosted agent. Thought you can use Active Directory - Password authentication.
Azure SQL数据库部署-导入的Invoke-Sqlcmd不支持connectionString
Azure SQL Database Deployment - Imported Invoke-Sqlcmd doesn't support connectionString
解决方案导入的Invoke-Sqlcmd不支持connectionString.安装SQLServer PS模块.
Solution Imported Invoke-Sqlcmd doesn't support connectionString. Install SQLServer PS module.
SqlPackage.exe参数/p:BackupDatabaseBeforeChanges = true 在Azure上失败
SqlPackage.exe argument /p:BackupDatabaseBeforeChanges=true fails on Azure
解决方案
/p:BackupDatabaseBeforeChanges = true 选项适用于本地数据库.该选项不适用于Azure，因为备份系统完全不同.
/p:BackupDatabaseBeforeChanges=true options is for on-premise databases. That option doesn’t apply to Azure since the backup system is completely different.
Azure SQL发布-SQL脚本文件-无法将秘密值作为变量传递
Azure SQL Publish - SQL Script File - Not able to pass secret value as Variable
解决方案
SQL脚本文件支持秘密变量.确保按如下方式传递变量的参数.
SQL script file supports secret variables. Make sure you are passing arguments for variables as follow.
Invoke-Sqlcmd -ServerInstance "test.database.windows.net" -Database "Testdb" -Username "test" -Password ****** -Inputfile "C:\test.sql" -variable "MYVAR1='$(variablevalue1)'", "MYVAR2='String2'" -ConnectionTimeout 120
调试工具和建议
SQLPackage.exe参数的文档
为SQLPackage.exe配置诊断日志
/diagnostics:true
从任务获取调试日志
将发布定义变量 system.debug 设置为值 true
Set release definition variable system.debug with value true
SqlCMD参数
比较SqlCmd和Invoke-SqlCmd参数
这篇关于AzureDevOps-Azure SQL Server部署问题和解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
