SSDT unleashed

Covered every aspect of SSDT project from various source to start with boilerplate code for better pattern and practice.

1. Using SSDT SQL Server Data Tools @ Gomesnayagam Credit- @ lynnlangit , Peter Schott,ch9

2. What is SSDT? SSDT SQL Server Data Services Part of Visual Studio 2010/2012 Set of services SDLC New “BIDS” More Integrated Into Visual Studio Intro SSMS Into Azure

3. Can reduce common ‘pains’ for SDLC 3

4. SSDT Architecture

5. About SQL Server Data Tools Work with and on SQL Server and SQL Azure databases from within Visual Studio using activities and techniques familiar to developers

6. Getting SQL Server Data Tools

7. About DACPAC and BACPAC 7

8. Details about DACfx Component for application lifecycle services for SQL Server and Windows Azure SQL Databases Supports deployment and management scenarios Extracting/Exporting live database to DAC Deploying DAC to new or existing database Migrating from on-premise SQL Server to Windows Azure Includes command line SqlPackage.exe to create .dacpac and .bacpac Requires Visual Studio 2010/2012 Download both x86 and x64 versions if using x64 machine 8

9. Demo Using SSDT with SQL Server

10. SSDT in Visual Studio New Explorer View in VS SQL Server Object Explorer Connect to SQL Azure Connect to SQL Server LocalDB Can work online or offline

11. New – Table Designer in Visual Studio

12. Offline Projects – Visual Studio New Project Type --Set Source connection --Set Target project --Set Import settings --Creates Off-line Project

13. Add Database Reference

14. SSDT Schema Compare

15. Refactoring Find All Dependencies Rename with Preview Intellisense

16. Code Analysis Settings

17. Publishing

18. Version Targeting

19. Pattern and Practice 1. reference Add your other database project here, most importantly you may need master db reference if you use sysobjects etc ., 2. DBCompare Create a schema comparison between dev | staging | prod DB vs your project. Ensure you have your project at right hand side for safty , coz comparison window has "Update" command which will by default update "right side" which target one . Also you can choose which objects are required to compare for better visibility, i personally don't choose " User","Role " etc., Keep only relevant content here. Developer can use this compare on daily basis to quickly check he is align with target database, that means, if somebody modified directly into the database without sql project you would get to know. You can keep one compare file for each environment here.

20. Pattern and Practice 3.Publishprofiles Most important folder, as you can keep various deployment profiles for each environment. This location also will be referred later in tools section scripts for Continuous Integration . 4.Script You can keep any adhoc custom sql script here and that can be referred in postdepoyment script using :r switch. Folks often use for seed data and other post deployment preparation. 5.Security One of the important folder, personally i don't use the existing security folder when you import database or create project from DB. The reason is, it will pull all the rols , permission and user creation script here which will not be useful in real time scenario in most of the cases. Every environment will have different user id and permission. But i keep here only for schema reference, so if you have any custom schema being used. 6.Snapshots As we know it is vital to take snapshot prior to push the changes to production or any other environment, so i prefer to keep all the snapshots over here with versioning support.

21. Pattern and Practice 7.Tools Most important folder for automation, especially for continuous integration. This folder have .txt files which has command line argument for sqlpackage.exe tool for various operation like "Extract"," Deployreport "," Script","Publish " and " PublishProfile ". All you need is to change the appropriate value at run time and keep going . 8.Script.PostDeployment.sql For any post deployment activity, e.g. seed data preparation, clean up etc., This also have variable to check for new DB deployment or incremental deployment using sqlcmd variable.

22. Try it out

23. Reference http://www.slideshare.net/lynnlangit/using-sql?from_search=6 # http:// vimeo.com/56031322 http:// channel9.msdn.com/Events/TechEd/Europe/2012/DBI311 http:// schottsql.blogspot.in/2012/10/ssdt-creating-new-sql-project.html http:// msdn.microsoft.com/en-us/data/tools.aspx https://github.com/GomesNayagam/SSDT-Boilerplate