Setup SSDT and Sample db with Visual Studio and VSTS

In this lab, we will set up and use SQL Server Data Tools (SSDT) in Microsoft Visual Studio 2017 Enterprise. We will also import our database and make an initial commit to our Visual Studio Team Services repository.

DevOps MPP Course Source

Prerequisites:

Lab Tasks:

  • Add SQL Server Database Tools (SSDT) to Visual Studio
  • Create a new SSDT project
  • Make an initial commit to version control in your VSTS project

Estimated Lab Time:

  • approx. 30 minutes

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

Include the Data storage and processing workload to add the SQL Server Database Tools features to your Visual Studio installation.

If the Data storage and processing workload is already installed in Visual Studio, skip this section.

  1. Start the Visual Studio 2017 Installer from your Start Menu. (This is separate from the Visual Studio IDE).

    The Visual Studio Products tab is selected. Under Installed, Visual Studio Enterprise 2017 displays with the options to Update or Launch.

  2. Click the Update button to get a list of workloads. Check the box next to the Data storage and processing workload box.

    On the Workloads tab, the checkbox for Data storage and processing is selected.

    This process is described in this article: Modify Visual Studio 2017 by adding or removing workloads and componentst

  3. Once the installation completes, close the Visual Studio Installer.

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. 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.

  2. From the File menu, click New, then click Project (or click CTRL+Shift+N). You will receive a new project dialog. Click the SQL Server template on the left, and you should be presented with two options in the middle pane.

    New Project window, in the tree view in the left pane, Installed is expanded, Templates is expanded, and SQL Server is selected. In the right pane, SQL Server Database Project is selected.

  3. Select the SQL Server Database Project, and type and enter WideWorldImporters-SSDT 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.

  4. Click OK to create the project.

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

    In Solution Explorer, WideWorldImporters-SSDT is selected.

    You might also see a ReadyRoll Core Edition pane at the bottom of the Visual Studio window. If this is visible, you can unpin it to increase available space on your screen.

    The pin icon has a hover callout that says "Auto Hide".

  6. In Solution Explorer, right-click the WideWorldImporters-SSDT 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.

    Visual Studio Project Properties

  7. In Solution Explorer, right-click the WideWorldImporters-SSDT project, click Import, then click Database.

    In Solution Explorer, WideWorldImporters-SSDT is selected. From its right-click menu, Import and then Database are selected.

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

    Connection properties fields display: Server Name, Authentication, User Name, Password, and Database Name.

  9. In the Import Database window, click Start.

    This will read the schema from the WideWorldImporters-SSDT database and import all the schema into the database project. When it completes successfully, click Finish.

    In the Import Database window Summary page, steps to import display.

  10. 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 VSTS project

Publish our project to your VSTS project.

In this section, we want to commit our code to our Visual Studio Team Services project that we created in a previous lab.

  1. Open the Team Explorer toolbar (or select it from beside Solution Explorer if it is already open), then click Changes. You should see a list of changes that also include the various Visual Studio project items.

    Note: If prompted to connect to Git, do so at this point.

    In the Team Explorer – Changes window, the following path is expanded: C:(local path)\Application\Stored Procedures, under which a list of stored procedures displays.

  2. In the commit message (yellow) box, enter WideWorldImporters-SSDT initial schema. I typically use “Initial Commit” for the first set of code. After you have entered this, click Commit All.

  3. After the commit is complete, you should see a message that confirms that. Click Sync in the message.

    In the Team Explorer – Changes window, a message displays that the Commit was created locally. A callout points to the Sync link, which enables you to share your changes with the server.

  4. This will bring you to the synchronization dialog in Team Explorer, which allows you to publish this to a VSTS repo. Click the Publish Git Repo button under Push to Visual Studio Team Services.

    In Team Explorer – Synchronization, under Push to Visual Studio Team Services, a callout points to the Publish Git Repo button.

  5. In the next dialog, you should see your account along with the Team Services domain filled in. If not, you might need to log in to your VSTS account from Visual Studio. In the upper right corner of Visual Studio, there is a sign-in link. Then click Publish repository.

    Note: If an error is returned indicating that the repository already exists, click Advanced, then set the Team Project to your WideWorldImporters-SSDT repository, then click Publish repository.

    Advanced publish options display. Both the Team project and Repository name fields are set to WideWorldImporters-SSDT.

  6. After this is complete, you should see a message at the top of Team Explorer that confirms the repo was pushed. Click See it on the web.

    In Team Explorer – Home, a message displays that says WideWorldImporters-SSDT was pushed. A callout points to the link, See it on the web.

  7. A browser should open with your Visual Studio Team Services domain and project selected, on the Code tab. You should see your project listed. Review the contents, then close the browser window. Changes window with commit message.

Summary

In this lab you completed the following tasks:

  • Added SQL Server Database Tools (SSDT) to Visual Studio
  • Created a new SSDT project
  • Made an initial commit to version control in your VSTS project