5

With the new release of SSDT I noticed the addition of Azure objects such as External Tables and such. However in the Target Db platform Configuration of the Database project I do not see Azure SQL Data Warehouse as an option, just Azure SQL Server. Does anyone know of a tool to effectively do database development against Azure SQL Data Warehouse (such as SQL Schema Compare and change script generation)?

SQL Data Ninja
  • 78
  • 1
  • 5
  • DB Best Database Compare Suite now supports SQL Data Warehouse! You can do things like schema/data compare and change script generation: https://www.dbbest.com/company/latest-news/database-compare-suite-azure-sql-dwh/ – Ngovkevin Jun 21 '17 at 21:13

4 Answers4

2

Please vote and add your comments to the feedback request too.

We have recently added SQL DW support to SSMS. While it might do all that you are looking for, the Generate Scripts feature might help.

Sonya Marshall
  • 506
  • 2
  • 4
  • thanks for the answer, but I'm not seeing any features in SSMS for Diff/Change script generation based on a source and target db. I did see I could generate create scripts though. – SQL Data Ninja Aug 17 '16 at 22:06
  • SSDT Database Projects is currently not supported with Azure SQL Data Warehouse so you cannot perform offline database development through SSDT so deployment is strictly a manual process 1) CREATE TABLE followed by IF..EXISTS for changes to the definition 2) VIEWS and SPs you would have to drop and re-create them (re-instantiating object permissions). This can be automated through power shell. – Ngovkevin Aug 17 '16 at 23:17
  • @KevinNgo Thanks for the response! That's what I was afraid of, I was hoping I was just missing something. I hope SSDT will support Azure SQL Data Warehouse soon. I also downloaded Redgate SQL Compare and APEX Compare as well - neither seems to support Azure SQL Data Warehouse yet either. – SQL Data Ninja Aug 18 '16 at 02:31
  • @SQLDataNinja - SQL Compare doesn't have SQL DW support yet. If desirable please post a request on Uservoice! https://redgate.uservoice.com/forums/141379-sql-compare . You might, however, find Redgate's free SQL Data Warehouse Importer useful: https://www.dataplatformstudio.com/ – David Atkinson Aug 22 '16 at 14:20
1

To elaborate on a manual workaround, you can use the SQL Server 2016 project template as a logical container for your DDL in source control (VSTS) ignoring all DW specific syntax when building the project. This is available in Visual Studio today for free. Change management is a manual process where you have to drop and recreate your objects with permissions be re-instantiated in the target environment. This can be somewhat automated via TSQL and Powershell. For tables, you’d check if columns exist and adding them accordingly. This can also be automated via PowerShell and TSQL scripts. If you use VSTS, you can leverage build server functionality (triggers) for continuous integration or delivery/deployment.

If you would like to automate scripting, also check out the mssql-scripter tool for SQL DW:

https://azure.microsoft.com/en-us/updates/azure-sql-data-warehouse-support-for-mssql-scripter/

Ngovkevin
  • 153
  • 6
  • I've added some sample scripts in the link below which will do the aforementioned workaround. Please feel free to contribute and submit any pull requests! https://github.com/Microsoft/sql-data-warehouse-samples/tree/master/samples/scripts/deployments – Ngovkevin Dec 28 '17 at 20:06
0

There is also the DWScripter which can be leveraged. It's currently hosted here for Schema Compare.

Ngovkevin
  • 153
  • 6
0

Support for Azure SQL Data Warehouse has recently been added to SSDT and Azure DevOps (formerly VSTS). See here for more details:

https://azure.microsoft.com/en-us/blog/azure-sql-data-warehouse-provides-a-frictionless-development-using-sql-server-data-tools/

wBob
  • 13,710
  • 3
  • 20
  • 37
  • It is supported to SSDT (still in private preview though, not GA). I find it somewhat overstated to say that it is "supported" by Azure DevOps, as the "support" is limited to Azure Devops Repos (which is essentially a Git repo). – vstrien Feb 13 '19 at 08:03
  • 1
    It is now GA, and this is only not Git but visual studio sql project. – Janusz Nowak Nov 14 '19 at 13:32