As with the installation of SSMS, SQL Server Data Tools is also offered as a separate download. This can be found using the SQL Server setup screen shown at the beginning of the chapter. Clicking on Install SQL Server Data Tools will launch a web browser, directing you to the Downloads page for SSDT. This Downloads page offers the latest stable build and also the latest release candidate of the next version of SSDT (with the usual warning of a release candidate not being production ready). SSDT is delivered with the same Visual Studio Integrated Shell as SSMS, and can be installed as a standalone tool. However, SSDT is aimed at developers and the workflows associated with developing database solutions as a team member. This includes the processes of source control and the packaging of project deployments. With this in mind, it is also possible to install SSDT on a machine that has the full Visual Studio environment installed. Doing so will integrate SSDT into Visual Studio and incorporate the database development templates and workflows, allowing Visual Studio to remain in the development environment, instead of adding a separate environment just for database development.

If Visual Studio is already installed, the SSDTs update can be installed from inside Visual Studio. To download and install this update, navigate to Tools | Extensions and Updates and select the node Updates on the left side of the Extensions and Updates modal window, as shown in the following screenshot:

Once installed, SSDT (whether installed as a standalone tool or integrated into Visual Studio) provides four separate project templates to help jump-start development of SQL Server projects:

• Relational databases: This is a template designed for traditional relational database development and it supports SQL Server versions 2005 through to 2016 (although SQL Server 2005 is now a deprecated version). SSDT also supports on-premises installations and also Azure SQL Database projects (the Database as a Service solution hosted in Microsoft Azure). It is also possible to design queries (but not full projects) for Azure SQL Data Warehouse (the cloud-based data warehouse solution, hosted in Microsoft Azure).
• Analysis Services models: This template is designed to assist in the design and deployment of Analysis Services projects and it supports SQL Server versions 2008 through to 2016.
• Reporting Services reports: This template is designed to assist in the design and deployment of Reporting Services projects and it supports SQL Server versions 2008 through to 2016.
• Integration Services packages: This template is designed to assist in the design and deployment of Integration Services projects and it supports SQL Server versions 2012 through to 2016.

The template choice dictates what files and folders are automatically prepared at project creation time. Choosing File | New Project presents the new project dialogue shown in the following screenshot. There are two additions to the project type navigation tree. Business Intelligence groups Analysis Services, Reporting Services, and Integration Services projects together. The relational database project type is found under the SQL Server navigation node, as shown in the following screenshot:

As shown in the preceding screenshot, a project folder is created and the option to add the project to a source control system is available. Visual Studio offers the option to natively access the source control system's Visual Studio Team Services (a hosted source control system from Microsoft) or alternatively to use a local source control system such as Git. Furthermore, source control systems can be added through the extensive extensions library offered through Visual Studio and they can be accessed through the Tools | Extensions and Updates menu described earlier in this section.

Working with SSDT and the aforementioned templates should be familiar to any developer that has used Visual Studio before. Upon creating a project, the next step is to begin by adding new items (tables, view, stored procedures, and so on). The dialogue for this is filtered down to the project type, and for an SQL Server database project, we have the option of creating items ranging from Application Roles to XML Schema Collections. Of note is the ability to create SQL CLR objects (C# based common language runtime objects), which provide the ability to create more complex calculations that are otherwise not possible (or perform poorly) in the T-SQL language:

The advantages of using SSDT over SSMS for developers is the focus on development workflows and the integrations in the program: source control integration, project structuring, and object templates. This developer focus is further strengthened through the possibility of connecting the source control system to a build system and the option to extend the build system to include continuous integration/deployment (CI/CD). Both automated builds and CI/CD have become ubiquitous in application development circles in the past decade. This area has only seen limited support for database development until now, because databases also permanently store data. Now that application development environments have matured, the ability to introduce CI/CD to database projects has become a reality. Luckily, the foundation for CI/CD has long been laid for application development and so the work to implement CI/CD into a database project is greatly reduced. SSDT is therefore fully capable of integrating SQL Server database projects into a source control system and to extend those projects into automated build, test, and deployment workflows.

There is now a wealth of options to cover CI/CD in the SQL Server world. The tools TeamCity for continuous integration and Octopus Deploy are two products that have been proven to work well in conjunction with SSDT to provide a smooth process for CI/CD in SQL Server projects.