3

I am contemplating four types of environments (i.e., tech, development, certification, and production), each contains one to dozens of Azure SQL Data Warehouse databases. Each database will contain from a few to several dozen schemas and from hundreds to thousands of tables, views, procedures. Schemas will be added over time. Tables/Views would be added and modified (e.g., column addition) over time.

I want to introduce all change first to one database called "tech". I would then have an automated process that promotes those changes to all the databases in higher environments (based on stakeholder approval). The ADW databases, at least for production, will be deployed in the available Azure regions.

To date, I have been keeping all my deployment scripts in a source code repository and rerunning the scripts (on Linux using sqlcmd) for each ADW database on each environment.

I haven't found the functionality I would use in other MPP databases that I have supported that allow such automation. Teradata has "SHOW TABLE|VIEW|MACRO;" commands that allow one to dump specific DDL. Greenplum has a pg_dump command that backs up DDL.

In Azure SQL Data Warehouse, how could the "version of" DDL be determined? In Azure SQL Data Warehouse, how can specific DDL to be unloaded from one database and applied to another?

Steve
  • 548
  • 8
  • 24
  • Had one suggestion to review how SSDT works and if it supported ADW, would it be an option. – Steve Feb 01 '17 at 13:38
  • SSDT supports basic connectivity to ADW but does not support database projects at this time. There is a [feedback item](https://feedback.azure.com/forums/307516-sql-data-warehouse/suggestions/13313247-database-project-from-visual-studio-to-support-azu) which you can vote on but it's off the roadmap for now. Pinging @JRJ for a view on this question - how do people do this? – wBob Feb 03 '17 at 16:53
  • Pinging @JRJ... – wBob May 25 '17 at 08:00
  • We don't currently support Database projects and I'm not sure that we have it currently in our timeline. That being said, you could write some scripts that dump the create table statements programmatically. I'll be publishing a blog/docs that cover this scenario pretty soon. – hiroki Nov 06 '17 at 18:13

1 Answers1

1

This is an old question so I'm updating the answer.

Azure Synapse Analytics (formerly Azure SQL Data Warehouse) now supports CI/CD through SSDT projects. You can setup a database project targeting the DW workload, check in to GIT In Azure Devops and build full deployment pipelines to all of your environments.

https://azure.microsoft.com/en-us/updates/continuous-integration-and-deployment-with-ssdt-is-now-generally-available-with-azure-synapse/

Matt Usher
  • 1,325
  • 6
  • 10