Creating a database project versus Synapse SQL pool from scratch

Question

Hi, I'd like to find a good and complete reference tutorial in order to create from scratch a database project versus a Synapse dedicated SQL pool and to check a my project in progress. I've Visual Studio 2022 and SSDT extension (I can use this tool or is it better Azure Data Studio?) and I can implement the version control by an Azure DevOps Repos. I've a database inside the dedicated SQL pool without any schemas, tables and views. I need to author the sql scripts to create schemas, tables, views and possible stored procedures. Moreover, then I need to deploy the database project versus the target database in the SQL pool. Any helps to me, please? Thanks

Answer 1

Hi @pmscorca

Welcome to Microsoft Q&A platform and thanks for posting your question here.

You can use Visual Studio 2022 and SSDT extension to create a database project for your Synapse dedicated SQL pool. Here's a step-by-step guide to help you get started:

1. Install Visual Studio 2022 and SSDT extension.
2. Open Visual Studio and create a new SQL Server Database Project.
3. Right-click on the project and select "Properties".
4. In the "SQL Server" tab, select "Microsoft Azure SQL Data Warehouse" as the target platform.
5. In the "Debug" tab, set the "Target database connection" to your Synapse dedicated SQL pool.
6. In the "Build" tab, set the "Output path" to a local folder.
7. Add new items to your project for each schema, table, view, and stored procedure you want to create.
8. Write the SQL scripts to create the objects in each item.
9. Right-click on the project and select "Publish".
10. In the "Target database connection" section, select your Synapse dedicated SQL pool.
11. Click "Publish" to deploy your database project to the target database.

For more information on creating a database project for Synapse dedicated SQL pool, you can refer to the following Azure documentation:

• Source control integration for dedicated SQL pool in Azure Synapse Analytics
• Continuous integration and deployment for dedicated SQL pool in Azure Synapse Analytics

Regarding your question about whether to use SSDT or Azure Data Studio, both tools can be used to create and manage database projects for Synapse dedicated SQL pool. However, SSDT is a more comprehensive tool with more features and capabilities, so it may be a better choice for more complex projects.

I hope this helps! Let me know if you have any further questions.