Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server / SQL Server 2008

SQL Server Database Development in Visual Studio

Rate me:
Please Sign up or sign in to vote.
4.89/5 (38 votes)
1 Sep 2016CPOL5 min read 184.7K   70   28
SQL Server database project creation using Visual Studio 2013

Introduction

One of the challenging tasks on any software project is to manage the database changes and keep all changes in sync. The work is tougher when we have multiple test environments or multiple servers in place. Unlike the database changes, managing code changes is easy due to availability of version control. It is a very responsible task for any DBA to keep track of all the changes and deploy these changes to multiple servers. This is quite error prone and needs lots of testing.

We have the above issue until we work with SQL database projects using Visual Studio. We can develop, manage, compare and deploy the database changes using Visual Studio very easily. We can also keep all the database object changes under version control.

The SQL database development was present on Visual Studio since VS.NET version 2010 but in 2013, we have many powerful features present. We can create a new database project from an existing database with just a button click or we can create a database project from scratch. For now, let’s see how to create a DB project from scratch. Later, we will show how to create a database project from an existing database.

Create Database Project on Visual Studio 2013

To create a new SQL server database project, open “New Project” dialog and from there, select SQL Server from the default template. In the right side pane, select SQL server database project and provide the project name. I have given the project name as ProjectTracker.Database.

Image 1

Create new Database Project

Once we are happy with the project location and project name, click ok. After that, the project will be created. Then go to the project properties, from the solution explorer and select the project settings. Change the target platform to the correct version of the database where we are going to deploy our changes. For my case, I am selecting it as SQL server 2008. Then select the checkbox underneath the properties button saying "Create script (.sql file)". We can also change the database collations and other setting from the Database Settings button.

Image 2

Project Property

If we observe the SQL Server Object Explorer at the right side panel, we can find a blank database being created with no objects in it.

Image 3

SQL Server Object Explorer

From the Solution explorer, we can add objects like tables, stored procedures, functions, views, etc. in the same way as we add pages while working with web projects in Visual Studio. Here, all the objects will be added as script files to the project. Once we add an object, we can check if that is available on the SQL server object explorer under the respective nodes.

Let’s add a new table and we can see Visual Studio is opening the table in 2 ways. We will get a designer view and the T-SQL editor underneath it with the DDL statement which creates it. We can change either of those and the other one will be reflected the changes automatically. If you make any changes on the script incorrectly, it will immediately show you the list of errors.

Image 4

Syntax error

Image 5

Edit table

In the same way, we can add other database objects. It is very easy to work with the database projects. Once everything has been done, we can build projects in the same way as web projects and then publish them to a database server.

Deploying Database

Database created on the database project can be easily published to the server. From the solution explorer, right click on the project and select Publish. From the publish database dialog, select edit connection string. From the connection properties, choose the correct target server. Once you provide all the details, select ok. Test Connection might not work now as the database is not created on the server.

Image 6

Select the target database

Image 7

Publish Database

If we want to generate the scripts and execute those manually on the server, then we can click on the Generate Script button. It would create the database deployment script which can be executed on the server.

If we click on Publish button, Visual Studio will create the database directly on the target server. So make sure which way we want to deploy the database to the server. Using the Advance button, we can select many additional options for publishing database like, recreate database, take backup before publish, etc.

Image 8

Database Publish options

Schema Compare in Database Project

This is a very cool feature in the database project that everyone would like. We can compare the local development database schema with the target server schema before deployment. That way, we can synchronize all database versions easily.

From the solution explorer, right click and select Schema Compare and it will ask for the source database and target database. Select source database as your project in the solution and Target database as the database where you want to update the schema. Once these are selected, you can click on compare and it will show all the differences in the schema in the 2 databases. Form there, you can select the difference and changes will be shown in the underneath window. If we are fine to move all the changes to the target server all at once, we can click on update all. Else, we can exclude some of the changes by right clicking on any row and selecting Exclude. We can easily toggle between source and target by using the 2 way arrow button. If we want to move changes to server using the deployment scripts, then we can click on the generate script button.

Image 9

Compare database

We can save the schema compare files for future use. Create a new Folder as “SchemaCompare” and then include the saved files in the project. We can have numbers of schema compare files for different target servers. That way, we can easily sync schema with multiple deployment servers.

Conclusion

Visual Studio Database project is a very powerful tool that needs to be used very carefully. The above details provide basic knowledge on how to create a new database project. Later, we will create projects from existing database and play with many advanced functionalities.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Mindfire Solutions
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
drixman7623-Jan-20 6:33
drixman7623-Jan-20 6:33 
QuestionHow to connect from a node to sql server 2014 express database in a vb 2013 application Pin
Member 1414722711-Feb-19 15:52
Member 1414722711-Feb-19 15:52 
Questionexecutable for production deployment Pin
Rajeev Sood26-Jul-17 3:23
Rajeev Sood26-Jul-17 3:23 
AnswerRe: executable for production deployment Pin
NaibedyaKar27-Jul-17 23:25
professionalNaibedyaKar27-Jul-17 23:25 
QuestionCan you publish the database using command line ? Pin
anand91844-Apr-17 7:51
anand91844-Apr-17 7:51 
AnswerRe: Can you publish the database using command line ? Pin
NaibedyaKar18-Apr-17 3:10
professionalNaibedyaKar18-Apr-17 3:10 
GeneralSlightly off topic: Only useful for small shops focused only on SQL Server Pin
Stephen Brannan1-Sep-16 7:41
Stephen Brannan1-Sep-16 7:41 
GeneralRe: Slightly off topic: Only useful for small shops focused only on SQL Server Pin
RickZeeland1-Sep-16 21:10
mveRickZeeland1-Sep-16 21:10 
GeneralRe: Slightly off topic: Only useful for small shops focused only on SQL Server Pin
NaibedyaKar2-Sep-16 0:26
professionalNaibedyaKar2-Sep-16 0:26 
GeneralRe: Slightly off topic: Only useful for small shops focused only on SQL Server Pin
NaibedyaKar1-Sep-16 22:14
professionalNaibedyaKar1-Sep-16 22:14 
Question"selecting the output types as create scripts" Pin
Sam Hobbs30-Aug-16 19:25
Sam Hobbs30-Aug-16 19:25 
AnswerRe: "selecting the output types as create scripts" Pin
NaibedyaKar30-Aug-16 20:58
professionalNaibedyaKar30-Aug-16 20:58 
GeneralRe: "selecting the output types as create scripts" Pin
Sam Hobbs30-Aug-16 22:43
Sam Hobbs30-Aug-16 22:43 
GeneralRe: "selecting the output types as create scripts" Pin
NaibedyaKar31-Aug-16 23:57
professionalNaibedyaKar31-Aug-16 23:57 
QuestionTemplate Missing Pin
Member 1201228426-May-16 23:23
Member 1201228426-May-16 23:23 
AnswerRe: Template Missing Pin
NaibedyaKar27-May-16 0:56
professionalNaibedyaKar27-May-16 0:56 
Questioncreate a database project from an existing database Pin
spvarapu31-Dec-15 1:53
spvarapu31-Dec-15 1:53 
AnswerRe: create a database project from an existing database Pin
NaibedyaKar27-May-16 0:57
professionalNaibedyaKar27-May-16 0:57 
GeneralMy vote of 5 Pin
hungndv15-Nov-15 17:49
hungndv15-Nov-15 17:49 
Thanks for sharing. Smile | :)
QuestionWell done Pin
Steve Paine9-Sep-15 12:05
Steve Paine9-Sep-15 12:05 
AnswerRe: Well done Pin
NaibedyaKar16-Sep-15 4:31
professionalNaibedyaKar16-Sep-15 4:31 
Questionhi Pin
nytro042-Jun-15 23:40
nytro042-Jun-15 23:40 
QuestionClickonce Pin
AdelleL14-Jan-15 20:47
AdelleL14-Jan-15 20:47 
GeneralMy vote of 3 Pin
Member 1076371727-Oct-14 23:16
Member 1076371727-Oct-14 23:16 
GeneralRe: My vote of 3 Pin
NaibedyaKar28-Oct-14 20:35
professionalNaibedyaKar28-Oct-14 20:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.