Hi, in order to create from scratch a Synapse Serverless SQL pool database by a Visual Studio database project I'm searching a good article about this subject. F.e. I'd like know where putting the create database, create master key and create database scoped credential statements (as a pre-deployment script?) or already the db has to exist in the serverless sql pool, and so on. Any helps to me, please? Thanks

For the database, you set how you want the database configured in the project properties. In there, you'll find database settings. For the master key and database scoped credentials, you just add those as objects within the database project. One tip though: I've found if you have the master key and the database scoped credentials in separate .sql script files (i.e., one per object), it throws errors if you try to build. Put the master key definition and the database scoped credentials in a single .sql file. But yes, it can just live in the security node in your project.

Creating a database project for a Serverless SQL pool db

Accepted answer

Greg Low 1,760 Reputation points Microsoft Regional Director

2024-03-03T10:30:26.47+00:00

For the database, you set how you want the database configured in the project properties. In there, you'll find database settings.

For the master key and database scoped credentials, you just add those as objects within the database project.

One tip though: I've found if you have the master key and the database scoped credentials in separate .sql script files (i.e., one per object), it throws errors if you try to build. Put the master key definition and the database scoped credentials in a single .sql file. But yes, it can just live in the security node in your project.
Please sign in to rate this answer.
pmscorca 987 Reputation points

2024-03-03T16:39:54.49+00:00

Hi Greg, thanks for your reply.
For the master key and the database scoped credentials I've tried to use the proper security template script:

I've published them with no errors.

About the "create database mydatabase" statement, thinking also to deploy to a prod environment, do I need to specify it as a pre-deployment script or a simple script?

For now, I've created my database running the statement directly on the serverless sql pool by SSMS.

I'm very glad that Greg Low has replied to me; I've followed some your sessions :-)

Greg Low 1,760 Reputation points Microsoft Regional Director

2024-03-03T21:56:11.15+00:00

Hope the sessions have helped!

That's really good, as previously if they were separate, it would have failed. Sounds like they've fixed that. (I must go back and change it in some of my projects).

For the database, the main thing is to make sure the target platform is correct. There is one specifically for the serverless SQL pool.

When you use the Publish option, as part of the connection, it asks you for the DB name.

You can also create a dacpac by doing the build, and move it to the correct environment and then use options in SSMS to deploy the dacpac. You should get the option during the deployment to create what's needed.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Creating a database project for a Serverless SQL pool db

0 additional answers

Your answer