X
06Apr

SQL Server Data Tools (SSDT)

SQL Server Data Tools (SSDT) is a modern development tool for building SQL Server relational databases, Azure SQL Databases, Analysis Services (AS) data models, Integration Services (IS) packages, and Reporting Services (RS) reports, which comes integrated with Visual Studio. With SSDT, you can design and deploy any SQL Server data models with the same ease as you would develop an application in Visual Studio.

The advantage over using SSMS is that, with SSDT in Visual Studio & Azure DevOps you get:

  • Database source code checked into repository with commit id’s
  • Work on branches, apply branch policies with peer review & controlled code-check-in (with pull request builds)
  • Continuous build and continuous deployments

Task 1: Add SQL Server Database Tools (SSDT) to Visual Studio

 Before creating a SSDT database project, launch Visual studio installer and select Data storage and processing under Workloads, then select SQL Server Data Tools and click on modify.

 If Visual Studio is already installed, you can edit the list of workloads to include SSDT.

1

Task 2: Create a new SSDT project

Use SSDT to create a new project and connect this to your database.

1.    Start Visual Studio 2017/ 2019. If you have not signed into Visual Studio.com, you may receive a dialog to do so. Use the credentials for your VisualStudio.com account.

From the File menu, click New, then click Project (or click CTRL+Shift+N). You will receive a New Project dialog box. Click the SQL Server template on the left, and you should be presented with two options in the middle pane. (For SSIS, SSAS, SSRS projects, the approach is little differrent, where project is available under Business Intelligence section)

2.    Select the SQL Server Database Project, and type and enter SSDT­_Database as the project name. Ensure the Add to Source Control check box is selected. Change the folder if you want to store this in a different location.

3.    Click OK to create the project.

2

4.  After the project is created, you will have a new project in the Solution Explorer. This will look similar to the image below.

3

5.  In Solution Explorer, right-click the SSDT_Databse project and choose Properties. Select the Project Settings tab and ensure the Target platform is SQL Server 2016. On the toolbar, click Save Selected Items, then close the tab.

4

6.  In Solution Explorer, right-click the SSDT_Database project, click Import, then click Database.

5

7.  In the Import Database window, click Select Connection. In the Connect window, configure your connection properties, then click Connect.

6

8. In the Import Database window, click Start.

7

Click on Finish.

9.  Use Solution Explorer to browse the objects that have been imported to see how the folders are structured.

Task 3: Make an initial commit to version control in your Azure DevOps project

1.  Open the Team Explorer toolbar, then click Changes. You should see a list of changes that also include the various Visual Studio project items.

8

2.  In the commit message box (yellow box), enter test commit and select Commit all and Push. So, the changes will be pushed to Azure DevOps repository.

Task 4: Create Build and Release pipeline for SSDT database

1.  Select Pipeline as shown in the figure and choose New Pipeline.

9

2.  Now define the build pipeline by adding the jobs as described in the image below.

10

3.  Define the Publish symbols path job added in the pipeline. In the Search Pattern fill it as **\*.dacpac. So it publishes only the .dacpac files which are generated during the build.

11

4.   Then click on Save and Queue.

5.  Once the build is successful, dacpac artifact will be published which can be seen under the build summary.

126.  Expand published artifact to verify if dacpac is published successfully. This dacpac will be used in release pipeline.

137.  Click Releases tab. Create a new release pipeline.

14

8.  Define the release pipeline as shown in the image targeting the database to be updated.

15

9.  Select Create release after saving.

16

10.     Now the changes in dacpac will be deployed into target database. If CI-CD trigger’s are enabled, this would be a continous process where build and release happens with every commit to the branch. Aditionally, branch poicies could be added to each branch to have gated check-ins. 

When database source code is not under source control, you may miss out on tracking the changes through sprint cycles & commits, code quality, automated build, test & deployments. With integration of SSDT into Visual Studio & Azure DevOps we can bring your existing database into Agile SLM and make end-to-end tracking possible.

Related

Git Command line Options

Git is a free and open source distributed version control system designed to handle everything from ...

Read More >

HTML Parser

Are you looking for any HTML Parser?Want to traverse through HTML DOM elements?Want to read properti...

Read More >

New and Important things to know for better RDLC report design in NAV 2013 R2.

We are all learning new things about RDLC report design and we know that still there are lot of thin...

Read More >

How to pass Images from Report Body to Header/Footer in RDLC

When using SetData and GetData functions to pass data from report body to report header/footer we ca...

Read More >

Creating a Simple Form with Validation Using ASP.NET MVC 4 Empty Template

There are multiple Validation attributes available in MVC. Validation attributes comes from System.C...

Read More >

Customer Feedback

Your feedback is important to us. Please share your experience working with Canarys & the journe...

Read More >

Webinar - Streamline Software Development Using Microsoft ALM

Please watch the LIVE RECORDING Video of the webinar session below which took place on 3rd...

Read More >

Using XMPP and Openfire Server in Windows Phone 8

Implementing Extensible Messaging and Presence Protocol (XMPP) in Windows phone 8 is really a tough ...

Read More >

Google Analytics

Google Analytics is a service offered by Google that generates detailed statistics ab...

Read More >

Namespaces in Kubernetes

A default namespace is created automatically when the cluster is being setup. To isolate or prevent ...

Read More >

Share

Post a Comment

  • Recent
  • Popular
  • Tag
  • The Ansible Architecture

    Ansible is an open source, IT automation tool. It can configure systems, deploy software, and orchestrate more advanced IT tasks such as continuous deployments or zero downtime rolling updates. In thi...
  • Subversion (Centralized Version Control System)

    Version control system is a repository of files, often the files for the source code of computer programs with monitored access. They are essential for any form of distributed, collaborative developme...
  • Volume management in Kubernetes

    Since pods created in k8s are ephemeral, we are able to get the data as long as pods are alive, but if pods are terminated data stored in it completely lost and it cannot be get back, for that we need...
  • LDAP Integration with Sonatype Nexus

    Sonatype Nexus supports the Repository Manager integration with various active directory systems for authentication, such as Microsoft Exchange / Active Directory, OpenLDAP, ApacheDS, and others. In o...
Tags
Monthly Archive
Subscribe
Name

Contact Us
  • *
  • *