How to: Create a New Database Project using SSDT

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).  SSDT comes with visual studio as embedded tool. The following two procedures essentially achieve the same goal by creating a new database project and importing schema from an existing database. Each database object will be represented as a SQL script file (.sql) in Solution Explorer. 

To create a new project and import existing database schema

1.      Click File, New, then Project. In the New Project dialog box, select SQL Server in the left pane. Notice that there is only one type of database project: the SQL Server Database Project. There is no platform-specific project as in previous versions of Visual Studio. You will be able to set your target platform in the Project Settings dialog box after the project has been created. Such task will be covered in the How to: Change Target Platform and Publish a Database Project topic.

2.      Change the name of the project to yours one and click OK to create the new project.

3.      Right-click the newly created project in Solution Explorer, select Import, and then Database.

The Import Database dialog box opens. In the Source database connection section, click choose a database and select the newly created project. If Project is absent from the drop down list, use the New Connection button to edit the Connection Properties.

4.      In the Import Settings section, notice the options for importing specific objects and settings, and creating folders for each schema and/or object type. For an organized hierarchy of all your database objects, accept all default settings and click Start.

5.      The Import Database dialog shows a progress bar and displays a list of objects SSDT is importing. When the import operation has completed, click Finish to exit the final screen.

The maximum number of DB object files that can be grouped into a folder are 1000.  You may select a lower number from the drop down.

6.      Examine the hierarchy in the Solution Explorer. Expand the dbo folder and you will find separate Functions, Tables and Views folders. Notice that the tables and function are grouped under their schema folders.

7.      Double-click a .sql file under Tables. The Table Designer opens, showing the visual interpretation of the table in the Columns Grid, and the script definition of the table in the Script Pane. This is identical to what we see in the Connected Database Development section.

8.      Right-click the Project in Solution Explorer and select Build to build the database project.

In the following snap you can see the hierarchy of the imported DB objects. All object are grouped under their on folder.

In the following snap you can see there are number of Procedure folders. Each folder has 1000 DB items maximum.

DB Projects settings:

Following are some DB settings that are usually required for smooth usage of DB project.

If analyzing database schema takes time the do the following

1.       Go to Project Settings

2.       click on the Database Settings button

3.       Under the Operational tab

3.1 uncheck Auto create statictics and

3.2 Uncheck Auto update statistics checkbox.

This will reduce your numbers of operations by about 90% and the analysis should finish instantly

Benefits of Using a Database Project in SQL Server Data Tools (SSDT)

DB projects serve the following benefits:

·         Easy availability to DDL for all objects (tables, views, stored procedures, functions, etc) without having to script them out from the server and/or restore a backup. (See additional benefits in the next list if you also integrate with source control, which is highly recommended.)

·         Functionality to script out schema comparison differences for the purpose of deployment between servers. If you've ever migrated an SSIS package change and then it errored because you forgot to deploy the corresponding table change, then you'll appreciate the schema comparison functionality (if you use it before all deployments that is).

·         Excellent place for documentation of a database which is easier to see than in extended properties. For example, recently I added a comment at the top of my table DDL that explains why there's not a unique constraint in place for the table.

·         Provides a location for relevant DML (data manipulation language) statements as well, such as the unknown member rows for a dimension table. Note: DML statements do need to be excluded from the build though because the database project really only understands DDL.

·         Snapshot of DDL at a point in time. If you'd like, you can generate snapshot of the DDL as of a point in time, such as a major release. 

Additional benefits *if* you're using a DB project also in conjunction with source control such as TFS or vsts /Azure dev-ops:

·         Versioning of changes made over time, with the capability to quickly revert to a previous version if an error has occurred or to retrieve a deleted object. Useful comments should be mandatory for all developers who are checking in changes, which provides an excellent history of who, when, and why a change was made. Changes can also be optionally integrated into project management processes (ex: associating a work item from the project plan to the checked-in change set).

·         Communicates to team (via check-outs) who is working on what actively which improves team effectiveness and potential impact on related database items.