Synapse Serverless SQL Pool support

[Johannes-Vink]

At this moment there is no Synapse Serverless SQL Pool support, effectively blocking any enterprise usage/deployment.

Even a schema compare just to check differences errors out, because the version of Serverless is not understood.

[yorickbouma]

@ssreerama do you have an update about this feature request that was placed on the backlog?

Thanks!

[azaricstefan]

Hi @yorickbouma / @Johannes-Vink , engineering team is actively working on support for Synapse serverless SQL pools.

We will update this issue and this when a version with the support is released.

[adriennn]

I see this has been pushed to backlog / next release, is there any news besides this? And does someone have a better solution than either of these? thanks!

A) a very large sql script B) hack something with an azure sql database project C) use synapse pipelines

1. code the objects in scripts
2. publish
3. pull the sqlscripts from rest api by paginating, copying the json outputs to storage
4. read json from storage using openrowset and serverless sql (ironic, I know, otherwise you cannot order the scripts)
5. run a Script activity with the outputof the above.

[dzsquared]

Hi @adriennn - it is still underway as @azaricstefan mentioned, but as it wasn't complete at the release in November it is now slated for the next release. @azaricstefan may have other work arounds to suggest in the meantime.

[Alpott]

Excited for this

[bentcg]

@dzsquared Any news on this or a rough ETA for next release? It would be really useful to be able to schema compare Synapse Serverless so we can ensure our different environments stay in sync.

[kevchant]

I see this has been pushed to backlog / next release, is there any news besides this? And does someone have a better solution than either of these? thanks!

A) a very large sql script B) hack something with an azure sql database project C) use synapse pipelines

1. code the objects in scripts
2. publish
3. pull the sqlscripts from rest api by paginating, copying the json outputs to storage
4. read json from storage using openrowset and serverless sql (ironic, I know, otherwise you cannot order the scripts)
5. run a Script activity with the outputof the above.

Apologies for the delay in replying, you could try using the DBUp and the dbops PowerShell module like I do in this video: https://www.youtube.com/watch?v=Dcpd4Z783Zs

[brain246]

Apologies for the delay in replying, you could try using the DBUp and the dbops PowerShell module like I do in this video: https://www.youtube.com/watch?v=Dcpd4Z783Zs

Thanks for posting this video @kevchant. Was already thinking about giving DBUp a chance in this scenario and your video might just accelerate that process... 👍🏼

[azaricstefan]

@brain246 @kevchant @bentcg @robejbee @adriennn @yorickbouma @Johannes-Vink Support for serverless SQL pools is out! Check the release notes.

[kevchant]

Thanks Stefan, found the time to test it last week when saw the update:

https://www.kevinrchant.com/2023/02/15/deploying-a-dacpac-to-a-serverless-sql-pool/

[azaricstefan]

Great to hear that @kevchant ! Let us know if you have feedback for us regarding DacFx support here.

[kevchant]

I will, right now patiently waiting for official way to create database projects to be made available.

[brain246]

@azaricstefan: Awesome, thanks!

A first quick feedback: I extracted a small database and i got an error regarding a view that is basically an openrowset-select that also extracts the filename and path from the source files.

Log:

Microsoft.Data.Tools.Diagnostics.Tracer Error: 19 : 2023-02-20T22:10:59 : Microsoft.SqlServer.Dac.DacServicesException: Validation of the schema model for data package failed.
Error SQL71501: Error validating element [dbo].[vwCheckImport]: View: [dbo].[vwCheckImport] has an unresolved reference to object [res].[filename].
Error SQL71501: Error validating element [dbo].[vwCheckImport]: View: [dbo].[vwCheckImport] has an unresolved reference to object [res].[filepath].
Error SQL71501: Error validating element [dbo].[vwCheckImport].[Filename_]: Computed Column: [dbo].[vwCheckImport].[Filename_] has an unresolved reference to object [res].[filename].
Error SQL71501: Error validating element [dbo].[vwCheckImport].[Filepath_]: Computed Column: [dbo].[vwCheckImport].[Filepath_] has an unresolved reference to object [res].[filepath].

A simplified view definition:

create view dbo.vwCheckImport  as 
select
     Filename_    = res.filename()
    ,Filepath_    = res.filepath()
    ,<other_columns>
from openrowset 
(
     bulk 'upload/**'
    ,data_source      = <external_data_source>
    ,format           = 'CSV'
    ,fieldterminator  = '|'
    ,firstrow         = 4
    ,data_compression = 'gzip'
) with (<column_definitions>) res

When disabling the verification i am receiving a .dacpac file 👍🏼

Sadly Visual Studio can't create a database project out of it (yet):

20.02.2023 22:25:52 Internal Error. 
The database platform service with type Microsoft.Data.Tools.Schema.Sql.SqlServerlessDatabaseSchemaProvider 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.

But i guess that is the todo of someone else 😄

[azaricstefan]

SSDT support is one of the next improvements that we are planning to work on.

[SaschaProdware]

SSDT support is one of the next improvements that we are planning to work on.

@azaricstefan. Hello Stefan, as this Issue is closed is there an other Issue for this? Particular Schema compare with SSDT is something that i am missing.

[azaricstefan]

@SaschaProdware that feature will be coming together with SSDT support. Since this repository is for DacFx, I suggest to track this in idea forum.

[SaschaProdware]

@SaschaProdware that feature will be coming together with SSDT support. Since this repository is for DacFx, I suggest to track this in idea forum.

Fine with this, i found that 4 month ago, hope there will be progress and status updates there.

[al13nus]

Is it possible to exclude objects from publish like in dedicated deployments?

Deploying DACPAC from not-serverless SQL Servers with following task worked fine, but now I'm getting Error SQL72018: Database Scoped Credential could not be imported but one or more of these objects exist in your source.. Obviously I don't want to overwrite users on TST environment with DEV users. Is it still in development or am I missing something? Nice to have would be excluding this information during the extraction step.

Extract:

- task: SqlAzureDacpacDeployment@1
  displayName: 'Extract database'
  inputs:
    DeploymentAction: 'Extract'
    deployType: 'DacpacTask'
    azureSubscription: ${{ parameters.azureServicePrincipal }}
    AuthenticationType: 'servicePrincipal'
    ServerName: "$(SQL-SERVER-NAME)-ondemand.sql.azuresynapse.net"
    DatabaseName: ${{ parameters.databaseName }}
    AdditionalArguments: '/p:VerifyExtraction=true'

Publish:

- task: SqlAzureDacpacDeployment@1
  displayName: 'Deploy SQL dacpac to Server'
  inputs:
    DeploymentAction: 'Publish'
    deployType: 'DacpacTask'
    azureSubscription: ${{ parameters.azureServicePrincipal }}
    AuthenticationType: servicePrincipal
    ServerName: "$(SQL-SERVER-NAME)-ondemand.sql.azuresynapse.net"
    DatabaseName: "${{ parameters.databaseName }}"
    DacpacFile: '$(Agent.BuildDirectory)\${{ parameters.databaseName }}-DACPAC\${{ parameters.databaseName }}.dacpac'
    AdditionalArguments: '/p:ExcludeObjectTypes="Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials;DatabaseWorkloadGroups;WorkloadClassifiers" /p:BlockOnPossibleDataLoss=false /p:IgnoreDatabaseWorkloadGroups=true /p:DropPermissionsNotInSource=false /p:DropRoleMembersNotInSource=false'

We are using Credentials, to access Storage Accounts from outside.

[SaschaProdware]

@SaschaProdware that feature will be coming together with SSDT support. Since this repository is for DacFx, I suggest to track this in idea forum.

Fine with this, i found that 4 month ago, hope there will be progress and status updates there.

@azaricstefan Hm, in Idea Forum nobody is answering. Is the Team working on it?