Download to read offline
![@NowinskiK Know issue: temp table CREATE PROCEDURE [dbo].[TempDemo1] AS CREATE TABLE #TempTable (Id INT); EXEC TempDemo2; CREATE PROCEDURE [dbo].[TempDemo2] AS SELECT * FROM #TempTable; RETURN 0 SSDT says: I don’t know that table!](https://image.slidesharecdn.com/sqlsatkyivnowinskikssdtdevops-190909130932/75/The-databases-in-SSDT-A-work-with-project-and-best-practices-26-2048.jpg)
![@NowinskiK Know issue: temp table CREATE PROCEDURE [dbo].[TempDemo1] AS CREATE TABLE #TempTable (Id INT); EXEC TempDemo2; CREATE PROCEDURE [dbo].[TempDemo2] AS IF 0=1 CREATE TABLE #TempTable (ID int); SELECT * FROM #TempTable; RETURN 0](https://image.slidesharecdn.com/sqlsatkyivnowinskikssdtdevops-190909130932/75/The-databases-in-SSDT-A-work-with-project-and-best-practices-27-2048.jpg)
![@NowinskiK Know issue: Disable/Enable trigger #1 • Disable/Enable trigger from other database ALTER TABLE [$(ContosoRetailDW)].dbo.DimChannel DISABLE TRIGGER [Trigger_DimChannel];](https://image.slidesharecdn.com/sqlsatkyivnowinskikssdtdevops-190909130932/75/The-databases-in-SSDT-A-work-with-project-and-best-practices-28-2048.jpg)
Uploaded byKamil Nowinski
The databases in SSDT: A work with project and best practices
This document summarizes a presentation about using SQL Server Data Tools (SSDT) for database projects. It discusses what SSDT is, how to get started with database projects, common issues like circular dependencies, and how to deploy database changes and include data. The presentation covers topics like installing SSDT, importing databases, resolving dependencies, working with temporary tables, disabling triggers across databases, and including SQL jobs in database projects. Resources for further information are also provided.
The databases in SSDT: A work with project and best practices
- 1. @NowinskiK SQLSat Kyiv Team EugenePolonichko Mykola Pobyivovk Yevhen Nedashkivskyi Oksana Tkach Oksana Borysenko Denis Reznik Anton Artomov
- 2.
- 3. @NowinskiK Session will beginvery soon :) Please complete the electronic evaluation form for this session and for the event. Your feedback will help us to improve future conferences and speakers will appreciate your feedback! Enjoy the conference!
- 4. Kamil Nowiński @SQLPlayer The databasesin SSDT A work with project and best practices
- 5. Kamil Nowiński Microsoft DataPlatform MVP Speaker, blogger, data enthusiast Senior Data Engineer at ASOS (www.asos.com) 15+ yrs experience as DEV/BI/(DBA) Member of the Data Community PL Project member of „SCD Merge Wizard” Founder of blog SQLPlayer (www.SQLplayer.net) SQL Server Certificates: MCITP, MCP, MCTS, MCSA, MCSE Data Platform, MCSE Data Management & Analytics Moreover: Bicycle, Running, Digital photography @NowinskiK, @SQLPlayer
- 6.
- 7.
- 8. @NowinskiK DevOps – definition(Wikipedia) DevOps (a clipped compound of "development" and "operations") is a software engineering culture and practice that aims at unifying software development (Dev) and software operation (Ops). The main characteristic of the DevOps movement is to strongly advocate automation and monitoring at all steps of software construction, from integration, testing, releasing to deployment and infrastructure management. DevOps aims at shorter development cycles, increased deployment frequency, and more dependable releases, in close alignment with business objectives.
- 9. @NowinskiK DevOps approach acrossyour project • Already adopted across SOME projects • Already adopted across ALL projects • Plan to adopt in the next 2 years • Not adopted and no plans in the next 2 years
- 10. @NowinskiK Redgate – Stateof database DevOps
- 11. @NowinskiK AGENDA • What isthe SSDT? • Do I need it? • How to start with DACPAC’s? • (Well) known issues • Deploy/Publish database to target server • Circular dependencies!
- 12. @NowinskiK What is theSSDT? • SQL Server Data Tools • Now, it’s a part of Visual Studio • Free! • Database project, including: • Schema, Stored Procedures, Functions, • Tables, Views, Security, CLR • ... and much more!
- 13. @NowinskiK SSDT: How toinstall?
- 14. @NowinskiK SSDT: How todownload, install?
- 15. @NowinskiK DACPAC, BACPAC • DACPAC= Data Tier AppliCation PACkage – Doesn’t contain DATA – Contains SCHEMA Only • BACPAC = BACkup PACage – Contains SCHEMA – Contains DATA (BCP native format) • ZIP format
- 16. @NowinskiK How to startwith database project? • Install SSDT • Create new db project and import: • From script • Directly from SQL server • Data-Tier Application (DACPAC)
- 17.
- 18. @NowinskiK (Well) known issues •Same database references • Circular references/dependencies • Unvisible temp tables • Disable/Enable trigger across databases • How to manage of data? • Can I deploy SQL Jobs?
- 19. @NowinskiK Database dependencies &references Tables: • Customer • InvoiceHeader • InvoiceLines • CustomerAddress • CustomerEmail Triggers: • Tr_Customer • Tr_CustomerEmail Stored Procedures: • AddLogItem CRMCRM_Audit Tables: • Customer_Audit • CustomerAddress_Audit • CustomerEmail_Audit • TransactionLog
- 20. @NowinskiK Circular dependencies CRMCRM_Interface Stored Procedures: •GetCustomerDetails • MergeCustomers • AddLogItem • GetCustomerInvoice Tables: • Customer_Audit • CustomerAddress_Audit • CustomerEmail_Audit • TransactionLog Tables: • Customer • InvoiceHeader • InvoiceLines • CustomerAddress • CustomerEmail Triggers: • Tr_Customer • Tr_CustomerEmail Stored Procedures: • AddLogItem
- 21.
- 22.
- 23. @NowinskiK DEMO • Create emptydatabase project • Import database from server • Create references & variables • Resolve first issues • Resolve circular references
- 24.
- 25.
- 26. @NowinskiK Know issue: temptable CREATE PROCEDURE [dbo].[TempDemo1] AS CREATE TABLE #TempTable (Id INT); EXEC TempDemo2; CREATE PROCEDURE [dbo].[TempDemo2] AS SELECT * FROM #TempTable; RETURN 0 SSDT says: I don’t know that table!
- 27. @NowinskiK Know issue: temptable CREATE PROCEDURE [dbo].[TempDemo1] AS CREATE TABLE #TempTable (Id INT); EXEC TempDemo2; CREATE PROCEDURE [dbo].[TempDemo2] AS IF 0=1 CREATE TABLE #TempTable (ID int); SELECT * FROM #TempTable; RETURN 0
- 28. @NowinskiK Know issue: Disable/Enabletrigger #1 • Disable/Enable trigger from other database ALTER TABLE [$(ContosoRetailDW)].dbo.DimChannel DISABLE TRIGGER [Trigger_DimChannel];
- 29. @NowinskiK Know issue: Disable/Enabletrigger #2 • Alternative #1: • Use dynamic SQL (not recommended)
- 30. @NowinskiK Know issue: Disable/Enabletrigger #3 • Alternative #2: • Create SP in the second database (locally for the trigger) • Call that SP from ’Remote’ database
- 31. @NowinskiK How to includedata in a database project? • SSDT has no built-in solution for including data • Use Post-Deployment script to populate table • Wrap the scripts into stored procedures • Make sure the order of referenced tables
- 32. @NowinskiK How to includedata in a database project? • Scenario #1: initial values only • For the very first time (run) only • Target table is empty • INSERT • Example script
- 33. @NowinskiK How to includedata in a database project? • Scenario #2: User has NO access to data • Full MERGE statement • Include DELETE clause • Example script
- 34. @NowinskiK How to includedata in a database project? • Scenario #3: users CAN add values from app • MERGE statement • Exclude DELETE clause • Example script
- 35. @NowinskiK DEMO • Create publishprofile • Deploy manually • Data script in Stored Procedure • Post-Deployment script • How to change data & deploy it
- 36. @NowinskiK SQL Jobs indatabase project • Facts: • DACPAC is a database level project • SQLJobs are on server-level • How to cope with: • Add separate database project • Only T-SQL scripts • Pre/Post deployment script to include above • Use PowerShell and SALT module from Sabin.io
- 37. @NowinskiK Resources • SQL ServerData Tools (MSDN) • SQLPackage.exe (MSDN) • Data-tier Application Framework (DACFx) • Microsoft SQL Server Data Tools Team Blog • GIT – branch organization • Continuous Delivery and the Database (Redgate) • Alex Yates - model vs mig • Version Control tools for SQL Server • SQLPlayer.net blog
- 38.
- 39. Thank you! Kamil Nowinski MicrosoftData Platform MVP MCSE Data Platform & MCSE Data Management and Analytics kamil@nowinski.net @NowinskiK @SQLPlayer SQLPlayer.net https://github.com/NowinskiK/CommunityEvents