Azure-pipelines-tasks: Deploying Azure SQL DACPAC succeed but truly failed

[MaximRouiller]

I have the following report from VS Release Management:

When I go see the logs, I have the following:

2016-08-23T17:44:33.2521753Z ##[command]Import-Module -Name C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Azure.psd1 -Global
2016-08-23T17:44:34.2951803Z ##[command]Set-AzureSubscription -SubscriptionName Microsoft Azure Enterprise -SubscriptionId d8fabf37-7a70-4e1d-8677-292627ff0556 -Certificate ******** -Environment AzureCloud 
2016-08-23T17:44:34.7021831Z ##[command]Select-AzureSubscription -SubscriptionId d8fabf37-7a70-4e1d-8677-292627ff0556 
2016-08-23T17:44:34.8111846Z DacpacFilePath= Find-VstsFiles LegacyPattern C:\a\e1e41692b\xxxxxxxxxxxx\DB\xxxxxxx.DB.dacpac
2016-08-23T17:44:35.1011859Z packageFile= C:\a\e1e41692b\xxxxxxxxxx\DB\xxxxxx.DB.dacpac
2016-08-23T17:44:36.5644721Z ##[debug][Azure RDFE Call] Creating firewall rule 09e753b3-5585-45db-b123-23e2f548e3fe
2016-08-23T17:44:38.5047565Z ##[debug][Azure RDFE Call] Firewall rule 09e753b3-5585-45db-b123-23e2f548e3fe created
2016-08-23T17:44:39.5060271Z Publishing to database '<DATABASE>' on server 'xxxxxxxxxxxxxxx.database.windows.net'.
2016-08-23T17:44:40.5720086Z Initializing deployment (Start)
2016-08-23T17:44:56.8253318Z *** The column [dbo].[Role].[IsMembershipRole] on table [dbo].[Role] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
2016-08-23T17:44:57.3903355Z Initializing deployment (Complete)
2016-08-23T17:44:57.4133346Z Analyzing deployment plan (Start)
2016-08-23T17:44:57.5003360Z Analyzing deployment plan (Complete)
2016-08-23T17:44:57.5053362Z Updating database (Start)
2016-08-23T17:45:00.4424194Z An error occurred while the batch was being executed.
2016-08-23T17:45:00.4544174Z Updating database (Failed)
2016-08-23T17:45:00.4694169Z *** Could not deploy package.
2016-08-23T17:45:00.4704160Z Warning SQL72015: The column [dbo].[Role].[IsMembershipRole] on table [dbo].[Role] must be added, but the column has no default value and does not allow NULL values. If the table contains data, the ALTER script will not work. To avoid this issue you must either: add a default value to the column, mark it as allowing NULL values, or enable the generation of smart-defaults as a deployment option.
2016-08-23T17:45:00.4704160Z Error SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 6 Rows were detected. The schema update is terminating because data loss might occur.
2016-08-23T17:45:00.4714165Z Error SQL72045: Script execution error.  The executed script:
2016-08-23T17:45:00.4714165Z IF EXISTS (SELECT TOP 1 1
2016-08-23T17:45:00.4724164Z            FROM   [dbo].[Role])
2016-08-23T17:45:00.4724164Z     RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127)
2016-08-23T17:45:00.4734171Z         WITH NOWAIT;
2016-08-23T17:45:00.4934174Z ##[debug][Azure RDFE Call] Deleting firewall rule 09e753b3-5585-45db-b123-23e2f548e3fe on azure database server: xxxxxxxxxxxxxxxxxxxxx
2016-08-23T17:45:02.0672536Z ##[debug][Azure RDFE Call] Firewall rule 09e753b3-5585-45db-b123-23e2f548e3fe deleted on azure database server: xxxxxxxxxxxxxxxxxxxxxx

The update of the database definitely failed with an error. However, it seem that RM flagged it as successful. So that build was deployed and, with the missing column, was non-fonctionnal.

Is it a problem on our side or RM side?

[kfstorm]

I have the same issue. The task was reported as succeeded but actually the deployment failed.

2016-08-29T06:45:09.7670439Z ##[debug][Azure RDFE Call] Creating firewall rule 19fe232f-f79b-486c-b8e5-b03dba966504
2016-08-29T06:45:10.9633968Z ##[debug][Azure RDFE Call] Firewall rule 19fe232f-f79b-486c-b8e5-b03dba966504 created
2016-08-29T06:45:11.3704214Z Publishing to database '********' on server '********'.
2016-08-29T06:45:11.8093976Z Initializing deployment (Start)
2016-08-29T06:45:22.1583185Z Initializing deployment (Complete)
2016-08-29T06:45:22.1583185Z Analyzing deployment plan (Start)
2016-08-29T06:45:22.2063189Z Analyzing deployment plan (Complete)
2016-08-29T06:45:22.2063189Z Updating database (Start)
2016-08-29T06:45:24.1337956Z An error occurred while the batch was being executed.
2016-08-29T06:45:24.1397968Z Updating database (Failed)
2016-08-29T06:45:24.1517959Z *** Could not deploy package.
2016-08-29T06:45:24.1517959Z Error SQL72014: .Net SqlClient Data Provider: Msg 33270, Level 16, State 1, Line 2 Cannot find the object "rls.********" or this object is not an inline table-valued function.
2016-08-29T06:45:24.1517959Z Error SQL72045: Script execution error.  The executed script:
2016-08-29T06:45:24.1527954Z DROP SECURITY POLICY IF EXISTS [rls].[tenantAccessPolicy];
2016-08-29T06:45:24.1527954Z GO
2016-08-29T06:45:24.1527954Z CREATE SECURITY POLICY [rls].[tenantAccessPolicy]
2016-08-29T06:45:24.1537949Z     ADD FILTER PREDICATE [rls].********
2016-08-29T06:45:24.1537949Z     ADD BLOCK PREDICATE [rls].********
2016-08-29T06:45:24.1537949Z     ADD FILTER PREDICATE [rls].********
2016-08-29T06:45:24.1547958Z     ADD BLOCK PREDICATE [rls].********
2016-08-29T06:45:24.1547958Z     ADD FILTER PREDICATE [rls].********
2016-08-29T06:45:24.1547958Z     ADD BLOCK PREDICATE [rls].********
2016-08-29T06:45:24.1557957Z     ADD FILTER PREDICATE [rls].********
2016-08-29T06:45:24.1557957Z     ADD BLOCK PREDICATE [rls].********
2016-08-29T06:45:24.1557957Z     ADD FILTER PREDICATE [rls].********
2016-08-29T06:45:24.1697960Z ##[debug][Azure RDFE Call] Deleting firewall rule 19fe232f-f79b-486c-b8e5-b03dba966504 on azure database server: ********
2016-08-29T06:45:24.6964735Z ##[debug][Azure RDFE Call] Firewall rule 19fe232f-f79b-486c-b8e5-b03dba966504 deleted on azure database server: ********

[rohit-batra]

Hello Maxime, Kai,
We have Added the fix to Solve Silent Passing issue. Task should now fail. Fix will be available post next Deployment. Please update this thread once you have verified.

[MaximRouiller]

@rohit-batra Thanks! Any documentation on when deployments are occurring?

[rohit-batra]

Hello @MaximRouiller The fix will be deployed in all Scale Units in next couple of weeks

[kmkumaran]

Closing it based on @rohit-batra response.

[MaximRouiller]

Everything seems good on our end so far. Will reopen (or open a new issue) if the issue happens again.

[MaximRouiller]

@rohit-batra I had the issue again. Has it been deployed yet?

[rohit-batra]

Hi Maxime,
I will check and let you know by EOD tomorrow.

In meantime , please let me know what agent are you using , private or hosted and your Account name .
In case its private agent and you have access to it, please share the logs in the _work folder on the agent.

Also please retry after deleting SqlAzureDacpacDeployment folder from the tasks folder inside _work and share the version of the SqlAzureDacpacDeployment task being used. (Look for the folder name inside SqlAzureDacpacDeployment)

Sent from Ninehttp://www.9folders.com/

---

From: Maxime Rouiller [email protected]
Sent: Sep 13, 2016 12:30 AM
To: Microsoft/vsts-tasks
Cc: Rohit Batra; Mention
Subject: Re: [Microsoft/vsts-tasks] Deploying Azure SQL DACPAC succeed but truly failed (#2322)

@rohit-batrahttps://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fgithub.com%2frohit-batra&data=02%7c01%7crbatra%40064d.mgd.microsoft.com%7ca823a0228c154a0046c708d3db3f1d8a%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636093036526776618&sdata=0nCyWyQu7%2fG18RctC2celtw79BsbAsBN0OFfq7Vq5CU%3d I had the issue again. Has it been deployed yet?

You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fgithub.com%2fMicrosoft%2fvsts-tasks%2fissues%2f2322%23issuecomment-246454708&data=02%7c01%7crbatra%40064d.mgd.microsoft.com%7ca823a0228c154a0046c708d3db3f1d8a%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636093036526776618&sdata=AvPTwj%2bT03f2I8LxUWmdrNmsu%2f05JpczOPPEjPG2bvQ%3d, or mute the threadhttps://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fgithub.com%2fnotifications%2funsubscribe-auth%2fAUGWQipa1kNc4sZ7di3RZu7T7bfnUhDdks5qpaFUgaJpZM4JrRuS&data=02%7c01%7crbatra%40064d.mgd.microsoft.com%7ca823a0228c154a0046c708d3db3f1d8a%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636093036526776618&sdata=aOozV0wN4kMwHfI66GQmwoVq8VbKjdxovc6HUSSfBSo%3d.

[rohit-batra]

Hello @MaximRouiller ,

I just verified that one of our Rings are waiting deployment. This should be done in a day or two (optimistically By EOD Today, sans any un-foreseen issues) Once that is done , the fix should be available to you.

In meantime if you can verify what version of SqlAzureDacpacDeployment task is being used by you, that would help. (In case its Private Agent, please login to that and look for subfolder inside SqlAzureDacpacDeployment folder from the tasks folder inside */_work. In case you don't have access to the agent , please look at the UI of the Sql Dacpac Task. If you see "Deployment Package" section & "Task" dropdown inside it, its 1.0.26 else its earlier version).

I am guessing its 1.0.25 in which case post deployment new version will kick in along with the change, if its not and version is 1.0.26, then we would need to do some cleanup as mentioned in above comment , post deployment, to make sure the new files with fixes are used instead.

Please let us know if it fixes, or you face any issues post that.

[MaximRouiller]

We're using the hosted agents. Showing Deployment Package right now. So... I'll be waiting. 😄

[rohit-batra]

Hi Maxime,

I verified a few minutes back. Deployment is done. Please retry the task and now it should not pass when actually failing.

In case you see the issue of silent passing again, please download logs from the log console and send them across along with the account name for RCA.

Sent from Ninehttp://www.9folders.com/

---

From: Maxime Rouiller [email protected]
Sent: Sep 13, 2016 18:56
To: Microsoft/vsts-tasks
Cc: Rohit Batra; Mention
Subject: Re: [Microsoft/vsts-tasks] Deploying Azure SQL DACPAC succeed but truly failed (#2322)

We're using the hosted agents. Showing Deployment Package right now. So... I'll be waiting. ?

You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fgithub.com%2fMicrosoft%2fvsts-tasks%2fissues%2f2322%23issuecomment-246676096&data=02%7c01%7crbatra%40064d.mgd.microsoft.com%7cb78f94d097304e83848208d3dbd78c4f%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636093691245738770&sdata=KqbzcnQpNf1jOS4839HJ6byZ205192bEv%2fcHqMNJiVw%3d, or mute the threadhttps://na01.safelinks.protection.outlook.com/?url=https%3a%2f%2fgithub.com%2fnotifications%2funsubscribe-auth%2fAUGWQrqAjcNDVYU9bZRc4fZYO0PNJFNeks5qpqEfgaJpZM4JrRuS&data=02%7c01%7crbatra%40064d.mgd.microsoft.com%7cb78f94d097304e83848208d3dbd78c4f%7c72f988bf86f141af91ab2d7cd011db47%7c1%7c0%7c636093691245738770&sdata=PKbk5TvVUmQ9mElB2DvqtwA5623gNUDzWRJjjhE78O4%3d.

[MaximRouiller]

Thanks @rohit-batra. We'll be validating very soon.

[rohit-batra]

Thanks @MaximRouiller
In case issue persists please send across details requested above at RM_Customer_Queries (at) microsoft (dot) com