Here, we are going to learn about SQL Server Database Project (Template) available in Visual Studio. I will cover following points:

  • Introduction of SQL Server Database Project.
  • Create New SQL Server Database Project.
  • Import database schema from an existing database, a .sql script file or a Data-tier application (.dacpac) the Project.
  • Publish to create new Database in SQL server.
  • Compare Two Database and find differences.
  • Compare, Update or Create update Scripts.

Database plays a most important role in any application and it becomes difficult to manage the project when number of tables, views, stored procedures increases.

Consider scenarios where multiple developers are working on a project for next release, some are working on Bugs or adding new features which again require some or many changes in database. Most of the cases developers take note of DB (Database) changes manually. Some time they miss some changes which cost in production. There are many cases that Dev, UAT and production DB are different which is again hassle to identify the differences.

There are number of tools available in market for comparing DBs but are costly or paid solution.

So, in this article we will discuss and learn about SQL Server Database Project with is available in Visual Studio which is free. Yes Free!!

Prerequisites: Visual Studio (2015 or 2017) and MS SQL  Server. I am using VS2017 and SQL Server 2017 Developer for illustration.

Introduction:

You can create a new database project and import database schema from an existing database, a .sql script file or a Data-tier application (.dacpac). You can then invoke the same visual designer tools (Transact-SQL Editor, Table Designer) available for connected database development to make changes to the offline database project, and publish the changes back to the production database. The changes can also be saved as a script to be published later. Using the Project Properties pane, you can change the target platform to different versions of SQL Server (including SQL Azure). (copied MS Docs)

Create New SQL Server Database Project

  1. Open Visual Studio and create a blank solution.

2. Add a Project.

3. Select SQL Server from Left panel and SQL Server Database Project. Name the project ( here SampleAccount.)

Import database schema from an existing database, a .sql script file or a Data-tier application (.dacpac) the Project

4. Right click on project and select import. There will be three options: Data-Tier Application (.dacpac), Database, Scripts. Select Database.

5. Provide Connection string. i.e. Select/Insert Server, authentication type, Database.

6. Set the import settings as highlighted.

7. Click Start which will show the progress window as:

After Finish we will see the tables, views and stored procedures in our project.

This is how we can add the database in the SQL Server Database Project.

Publish to create new Database in SQL server.

The database above in the project can be used to create new database with same schema.

  1. Right click on the project and choose the publish option.

2. Provide Connection: Server name, Authentication Type , credentials. If we want to publish as new database then choose default database, or choose specific database to publish.

3. We can generate script or publish directly. You can explore advance options as well to apply rules on publish.

Now our new database is created or generated script can used to create.

Compare Two Database and find differences.

This section we will how to compare two database to identify the differences like Dev, UAT or UAT, Prod likewise.

  1. Right click in the SQL Database project and choose schema compare.

2. Select Source and Target database. Prove connection. Note: we can compare two databases or another database with the project database.


3. Click Compare

The we will the differences between source and target database.

There will be deleted, edited and added objects as shown.

Compare, Update or Create update Scripts.

Another we can update the target database or generate update script to push source version.

  1. Complete the compare part. then there is option to do the job.

Update option directly update to target database and generate script will give sql script.

From above consideration, we don’t need to create migration script or plan for DB. We can easily compare the relevant database tables, views and stored procedures in no time.

So, any changes can be incorporated and make database managing in effective and efficient way.

Leave a Reply

Your email address will not be published. Required fields are marked *