How To Create SQL Server Database Project In Visual Studio

Posted September 21, 2019 by Vishwanath Dalvi in Database, SQL Server

SQL Server Database Project In Visual Studio_7

SQL Server database project imports multiple database objects from a single/ multiple databases under one visual studio solution. Generally, converting all database objects into .sql files and bringing them under a solution & version control. Database objects include tables, schemas, stored procedures, views, etc.

Without having SQL server database under database project & version control is cumbersome in the multi developers’ project. Imagine your changes to a stored procedure getting overwritten by some other developer. This problem arises when we do not have a version control & shared development environment.

Introduction – Database project


1Import multiple database schema/objects within same solution

2Converting database objects (schemas/ tables/ stored procedures) into .SQL files

3Manage database changes under a shared development environment & version controlManage > Develop > Deploy

Benefits of Database Project


1Database under source control

2Compare schema and data before deployment

3Easy development & deployment to Dev > QA > UAT > Production

4Single database can be deployed to multiple servers

5Eliminate errors while building the database solution, avoid bad code getting promoted

6Code refactoring – saviour

7Continuous integration & Continuous Deployment (CI & CD)

Prerequisites / Requirements

Visual Studio 2012 +
SQL Server Data Tools (SSDT)
SQL Server 2012+

Coupled with benefits and ease of database project. Furthermore, we will learn how to create a basic database project for a single database in visual studio. In this tech-recipe post, I am using visual studio 2017 (SSDT) to create a database project. You may use any version starting with visual studio 2012 to set up.

How To Create Database Project In Visual Studio


1.Navigate to visual studio and create a new project, File > New > Project.

2.On the new project tab, in the name box, enter the database project name and click OK.

SQL Server Database Project In Visual Studio_1
Under solution explorer, an empty solution & project is created as AdventureWorksLT.

3.Right-click on AdventureWorksLT project name and navigate Import > Database. Selected database objects will be imported under this project.

SQL Server Database Project In Visual Studio_2

4.On import database tab, select connection to a database, by default windows auth is selected. You may specify SQL server auth as per your requirement. Furthermore, select folder structure as per requirement. Here, I have selected as Object Type which segregates all objects by their type, tables, views, stored procedures under different folders. Click on start.

Schema – Classify database objects as per their schema in a different folder based on schema name.
Object Type – Classify database objects as per their type. Likewise, a different folder for tables, views, stored procedures.
Schema\Object Type – Import of database objects as per their schema and object types. For e.g all Sales related procedures, tables, views under the Sales schema folder.

SQL Server Database Project In Visual Studio_3

SQL Server Database Project In Visual Studio_4

5.Import of database objects has finished.

SQL Server Database Project In Visual Studio_5

6.Navigate to solution explorer, and expand each object type folder. You can see .sql files created for each object in database.

SQL Server Database Project In Visual Studio_6

We can create a dacpac file based on this database project and deploy all the objects to another database server.

Summary

As a result, we have learned to create a database project in visual studio (SSDT). Next series of article covers more on deployment and getting database project under version control. If you like this post then browse through Tech-Recipes database archive posts to learn more useful stuff.

 

About Vishwanath Dalvi

Vishwanath Dalvi is a gifted engineer and tech enthusiast. He enjoys music, magic, movies, and gaming. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.
View more articles by Vishwanath Dalvi

The Conversation

Follow the reactions below and share your own thoughts.

Leave a Reply