Support multidacpac deployment on hosted agent 2017

[sandorfr]

Since the release of SQL Server 2017, sqlps is replaced by SqlServer powershell module which is a standalone package which can be installed with install-module command 🎉 . The even better news is that install-module is works just fine on current hosted agent images. So the following script just works:

Install-module -Name SqlServer -Scope CurrentUser -Force
Import-Module SqlServer 
Invoke-Sqlcmd -ServerInstance "IDontExist"

SSDT (sqlpackage) is also available on agents. It is also available as a standalone nuget package.

It means that we could add the dependencies automatically if not found and support any agent without any manual steps (as long as it as nuget tools and powershell gallery tools).

[sandorfr]

@asiffermann I'd be happy to have your feedback on the recent changes I've made on that matter before I publish it :)

[sandorfr]

Actually, we may want to move SQLServer/SQLPS and sqlpackage install to some tool installer task.

[asiffermann]

@sandorfr your changes seems fair to me, but it would be better with a tool installer indeed.

I have to publish a new version of the task due to #25 with your recent changes, but it won't be a problem as all agents running our tasks at the moment should have SQLPackage and sqlps installed.

[sandorfr]

They have changed the nuget package for sqlpackage which is now there https://www.nuget.org/packages/Microsoft.SqlServer.DacFx.x64/

I was running into that problem : https://blogs.msdn.microsoft.com/azuresqldbsupport/2017/09/19/deployment-fails-with-unable-to-connect-to-master-or-target-server-mydb_svr-you-must-have-a-user-with-the-same-password-in-master-or-target-server-mydb-db/

And it seems the probleme is related to azure receiving updates and hosted agent having a by design outdated version of sql package.

It feels like a tool installer is now a must have.

[sandorfr]

Arf no DacFx does not contain the exe. and https://www.nuget.org/packages/Microsoft.Data.Tools.Msbuild/ mentioned here https://blogs.msdn.microsoft.com/ssdt/2016/08/22/releasing-ssdt-with-visual-studio-15-preview-4-and-introducing-ssdt-msbuild-nuget-package/ lags one version behind

[sandorfr]

Currently the detection script picks an older version that the last one available (which should be 140 instead of 130): C:\Program Files (x86)\Microsoft SQL Server\140\Dac\bin\SqlPackage.exe

2017-12-12T12:30:38.6794755Z ##[debug]Sql Versions installed on machine BUILD-AGENT as read from registry: 110
2017-12-12T12:30:38.6794755Z ##[debug]Sql Version Specific Root Dir for version 110 as read from registry: C:\Program Files\Microsoft SQL Server\110\
2017-12-12T12:30:38.6794755Z ##[debug]Sql Version Specific Root Dir for version 110 as read from registry: C:\Program Files (x86)\Microsoft SQL Server\110\
2017-12-12T12:30:38.6794755Z ##[debug]Dac Framework installed with SQL Version 110 found at C:\Program Files (x86)\Microsoft SQL Server\110\Dac\bin\SqlPackage.exe on machine BUILD-AGENT
2017-12-12T12:30:38.6794755Z ##[debug]Dac Framework (installed with DAC Framework Msi) found on machine BUILD-AGENT at C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe
2017-12-12T12:30:38.6794755Z ##[debug]Visual Studio versions found on machine BUILD-AGENT as read from registry: 15.0 14.0 12.0 11.0 10.0 9.0
2017-12-12T12:30:38.6794755Z ##[debug]Visual Studio install location: C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\
2017-12-12T12:30:38.6794755Z ##[debug]Dac Framework installed with Visual Studio found at C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe on machine BUILD-AGENT
2017-12-12T12:30:38.6794755Z ##[debug]SqlPackage Path: 'C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe'
2017-12-12T12:30:38.6794755Z Deploying Version: 0.6.0.196638
2017-12-12T12:30:38.6951101Z ##[debug][CMD Call] Executing: & "C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\130\SqlPackage.exe" /SourceFile:"C:\gl-vso-agent-core\_work\r14\a\Server - Develop CI\Packages\Database\bin\Release\SaintGobain.Glassol.Database.dacpac" /Action:Publish /TargetServerName:"sqlserverdh7ubgfiist5c.database.windows.net" /TargetDatabaseName:"IntDevApiDatabase" /TargetUser:"SqliAdmin" /TargetPassword:"********" /p:DropObjectsNotInSource=True /p:DoNotDropObjectTypes=users /p:ExcludeObjectTypes=RoleMembership;users /p:BlockOnPossibleDataLoss=False /p:BlockWhenDriftDetected=False /p:RegisterDataTierApplication=True 2>''
2017-12-12T12:30:43.2932553Z ##[debug][CMD Call] Executed
2017-12-12T12:30:43.3088793Z ##[debug]Publishing to database 'IntDevApiDatabase' on server 'sqlserverdh7ubgfiist5c.database.windows.net'.
2017-12-12T12:30:43.3088793Z ##[debug]Initializing deployment (Start)
2017-12-12T12:30:43.3088793Z ##[debug]Initializing deployment (Failed)
2017-12-12T12:30:43.3401301Z ##[error]*** Could not deploy package.
2017-12-12T12:30:43.3401301Z ##[debug]Processed: ##vso[task.logissue type=error]*** Could not deploy package.
2017-12-12T12:30:43.3401301Z ##[error]Unable to connect to master or target server 'IntDevApiDatabase'. You must have a user with the same password in master or target server 'IntDevApiDatabase'.

[sandorfr]

Oops the log was from our out of date agent which explains a lot. Which is the perfect illustration of the need of a tool installer especially since azure team expects it to be up to date at all times