SQL SERVER DATA TOOLS(SSDT)

SQL Server data tools have the capacity to transform database development by introducing a ubiquitous, declarative model that provides all the phases of database development visual studio. We can use this SQL server Data Tools transact–SQL design capabilities to build, debug, maintain and refractor databases. We can work with the database project or may be directly with a connected database instance on premise or off premise. The developers will use the similar visual studio tools for database development.

Some features of SSDT are:

Treat the database like code:

With this SSDT all our database objects, tables, views, stored procedures, functions are code. When we write in visual studio, we build it in visual studio and we publish it to our desired destination. SSDT supports source control integration such as git repository in Azure Devops. When this source control we will be creating many versions of the database, by using GIT branches.

Reverse Engineer Existing Database

We will create a database from starting in SSDT but what if we have already existing database? we can import the schema into SSDT with just few clicks

Schema Compare of two databases

This feature will allow us to compare the schema of two databases and report all the differences between them. We can easily check for example what differences are between our test and production database. A database will be either by a physical database or it will described in a database project in SSDT. This feature is also helpful when someone will be making database changes outside of SSDT.

Publishing Database schema

When we finish the development of the database objects, we can publish the solution. This may build the solution verifying for any discrepancies and push out the database object to a target server. We can do an incremental deployment, where only changes are pushed to the database. We can also do a re-create of the database, although I will recommend doing this production.

A brand new database is pushed out by publishing it. For example, suppose we have configured a new development server. We want our database with all the objects but with no data, we may publish the database from SSDT to new server.

Generate Report with changes

Every time we build the solution, a dacpac file is created which contains all the information about the database schema. Using sqlpackage.exe we can generate an xml report with all the changes that will be deployed when a publish is executed.

Sqlpackage /action:DeployReport /SourceFile:”mydatabase.dacpac” /TargetConnectionString:”Data Source=localhost;Integrated Security=True;Initial Catalog=mydatabase” /OutputPath:”C:\temp\DeployReport.xml”

This command can be run in the command prompt to generate such report.

Editor and Templates:

If we don’t like to write whole sql creating a table with many columns, there’s a tabular editor where we can edit all the information

Refactor Code

SSDT will support refactoring. When we rename a table, it will also change the name of the table in objects referencing the table.

How to open DataTools in Sql server?

In solution explorer, after you right click the Wideworldimporters- SSDT project-> cl import -> Database. In the import Database window, click SelectConnection. In the connect window, configure our connection properties, then click connect, then import Database window, click start.

The advantages of SSDT are

Database source code is checked into the repository with the commit ids.
It will be work on branches with apply branch policies with a peer review and controlled code check-in.
It has continuous build and continuous deployments.

SSMS- SQL Server Management Studio

SQL server management studio is called as single client version of DTS designer with many features on the ETL platform where the sql server competed with other ETL products of the market.

The one and only difference between SSMS and SSDT were in terms of packaging. SSMS will be bundled together with the sql server 2014 installation. SSDT is a separate web installer which is not bundled with the sql server 2014 installation on the download page.