Visual Studio 2013 and Database Projects: Create a Database Project with Folder Structure

Visual Studio 2013 and Database Projects: Create a Database Project with Folder Structure

Overview

Data base projects in Visual Studio is a bit of an after thought on application development.  It started with Gert Drapers (the data dude) and became SSDT (SQL Server Data Tools) that had to be installed (as an add-on) to Visual Studio.  This had some varying function over the years, but was aimed at reducing the difficulty of database development in Visual Studio and keep database changes in synch across a solution (matching database and application versions) and multiple server environments through the application life-cycle.

Creating a Database Project

This is quite easy using Visual Studio 2013.  On the first landing page (can be turned off) a Start section has the option from which to create a new project, or the traditional FILE menu is just as simple.

Visual Studio for Database Development First Screen

Visual Studio for Database Development First Screen

VS Create New Database Project Dialog

VS Create New Database Project Dialog

Be mindful that if a project is created and Team Foundation Server (TFS) is being used to take care of how the solution and projects contained by the solution are demarcated.  It is not in the scope of this article to explain how to do this, but suffice to say that where solutions are stored separately from projects, to first create an empty solution and check it in.  Then the projects that are added back to the solution so that all are checked into TFS in their correct locations.

Further take not of the Visual Studio boxes, viz. Solution Explorer, SQL Server Object Explorer, and Properties for example.  These should be orientated (docked) in positions that make sense, I.e. for easy reference.  Below is an example of an instance that contains a Solution with three projects.

VS Database Project Screen

VS Database Project Screen

The two most important boxes are the Solution Explorer and the SQL Server Object Explorer.  The former depicts the Solutions and the database projects.  Where no database actually exists (this is where you create it) SQL Server Objects can be created/ added to the project, or (where a database already exists) imported by a few means.  The first is using a DACPAC (SQL Server Data-tier application extract file), or connecting directly to a SQL Server database, or create one using a script file containing all the object definitions.

Choosing a Folder Structure

Importing a database allows for the folder structure in the project to be changed.  The project folder structure should allow for ease of maintenance and readability.  The folder structure does not influence deployment.

VS Database Project Import Dialog Folder Structure

VS Database Project Import Dialog Folder Structure

An import of an existing database (where no structure is chosen, or defined dumps all files in the root of the project, which is moderately messy, so it must be carefully considered.  A folder is not created where an imported database does not have objects, e.g. Stored Procedures.  If subsequent objects are required, the folder will first have to be created.

Organising the database advances readability, consequently also maintenance, because developers are able to find things quicker.  Any database objects without any discernable grouping are dumped in the root of the project on import.

NOTE:Things that impact the display of objects and what access rights are differentiated in practice, bears on the configuration of the naming scheme.  Making use of database schemas (to group objects and assign login permissions) is an effective strategy for data architecture over using prefixes on objects like sp or pr for a Stored Procedure (source).  The folder structure in Visual Studio and SQL Server Management Studio already informs the object type and with an added schema denotes an organisational name space for such objects.  SQL Server uses a four-part naming scheme: Server.Database.DatabaseSchema.DatabaseObject.  Objects in a schema are owned by the security principle that owns the schema and a schema can contain objects owned by different users.  Any predefined schema (dbo, sys, etc) can be dropped from the model database if not required consequently preventing them from being created in new databases.  A schema owner can be changed without breaking the code, but if an object owner, say Tommy.Object is removed, an ownership change must first be performed before Tommy’s account can be deleted.  However, any fully qualified references will break in the code, e.g.  Server.Database.[tommy[.DatabaseObject. The default dbo schema is owned by the dbo user. Users assigned to the dbo schema do not inherit dbo user account permissions, but the database objects contained in the schema (source).  Using dbo as the owner of all object can greatly simplify managing objects (source).

Previous article (1) [Installation]. . . Next Article (3) [Working the Project]

Advertisements

Visual Studio 2012 Database Project (SSDT): Project Folder Structure

Introduction

Database projects have been around for a while, using Visual Studio, but they were semi-clunkers, and people avoided Visual Studio, and preferred SQL Server Management Studio, for most of their database related work.  With the advent of SQL Server Data Tools (SSDT), it became a much smoother ride.  Less rigidity, more flexibility.

The older Visual Studio Database Projects were knows as Data Dude projects, named after Gert Drapers, the original data-dude, according to Bob Beauchemin (SQL Skills) [1].  The Data Dude exemplar suffered some deficiencies, which were addressed by SSDT, the Visual Studio in-house replacement for SQL Server Management Studio [2], aimed to reduce the difficult database deployment of Visual Studio.

New Data Tools Data Project

New Data Tools Data Project

Visual Studio 2012 comes with a new template: SQL Server, which is the SQL Server Data Tools (SSDT) version of a database project.

Once a database project is opened from the template, a minimalistic project structure presents itself.  It does not create a boiler-plate folder structure at all.

To experiment with a structure, any existing database can be imported, to see how Visual Studio 2012 organises it.

Import an Existing Database

Import an Existing Database

Getting Going

SQL Server 2012 Database Folder Structure

SQL Server 2012 Database Folder Structure

Choosing a folder-structure, for the SSDT database project in Visual Studio 2012, is important from a familiarity, readability, and maintainability point of view.  The structure has no bearing on the deployment, and does not influence anything from a database point of view.

The folder-structure, as seen from the [Object Explorer] tab, in SQL Server Management Studio (SSMS) is still very much the same-olde.  Because Visual Studio has no bearing on it, the two environments could be diametrically opposed in their structures.

It will probably make a lot of sense, to mimic the SSMS structure, from a familiarity perspective, if the database design is not heavily schematic.

Choosing a Folder structure, from the Import Database wizard, provides a template, according to which Visual Studio arranges database objects.

Obviously, where nothing, or None is chosen, no folder structure is created.  A blank project leaves it up to the project team to define the structure.  An import of an existing database (where no structure is chosen, or defined), will dump all files in the root of the project, which is moderately messy, so it must be carefully considered.

Importing an Existing Database

The Import Database wizard, accessed by right-cliking the database project, and following the path: Import >> Database, is used to choose a folder structure.

Import an Existing Database

Import an Existing Database

The wizard provides four options for a project folder-structure, viz. None, Schema, Object Type, and Schema Object Type.   Because None, is the default of a new SSDT database project, it is not treated in the list below, but in the next section.

Making a folder-structure choice, really depends on the characteristics of the database project, more specifically what the visual presentation of database objects should conform to, from a organisational perspective.  Within each option, the rationale for choosing it, is discussed.

NOTE:  Where a database is imported, let’s say that has no Stored Procedures, the folder for it is also not created.  When such objects are subsequently added to the project, it would necessitate that the folder first be created.  This could infringe upon the project standards for database development, where such exists.  If no database coding standard exists, there would be little guidance, and projects could diverge greatly, from a standardising and conforming perspective.

Schema

The Schema import option for the folder structure, imports all dbo objects into the folder structure: <solution> >> <project/ database> >> <schema|[dbo]>.  It stores all objects connected with a schema, in a corresponding folder.

Schema Folder Structure

Schema Folder Structure

Schema separation for database objects, should be considered where the database is heavily schematic, i.e. object split into schemas.

Interestingly, this option exercises some intelligence, by grouping like-named objects into sub-folders, naming them Tables1 . . . n, for example.

This seems a bit counter-intuitive, because the grouping of database objects, is an Object Type function, whereas this option is Schema, or seems to indicate Schema only.  It could be, where the import wizard identifies objects starting consistently with the same text (indicative of some kind of grouping), it automatically assumes a differentiation according to Object Type.

This might be irritating, depending on whether or not it is appreciated, from an organisational perspective.

Often, objects are named by providing a prefix, denoting a grouping that causes the sorting of the objects in the folder, accordingly.  If Visual Studio provides an intuitive grouping, through the Schema Object Type  import option, it overstates the obvious, by placing all like-named objects in sub-folders, something already achieved through the sorting of the names.

It does, however, stay in tune with the dbo (schema) folder idea.  Where other schemas are created, these are all stored in their own folder structures, following the convention of storing all similarly named objects in their corresponding sub-folder structures, within their schemas.

It also seems, that where objects cannot be categorised, in accordance with the schema, or type, or and naming convention, they are dumped under the schema folder root.  These could be anything, ranging tables, views, functions, and stored procedures.  It’s relatively easy to remedy this, after the import (given the size of the project, i.e. the number of objects) by manually creating folders for the skipped objects, and dragging the artefacts into those folders.

Object Type

The Object Type import option for the folder structure, imports all objects by Object Type, placing them in a folder structure.

Object Type Folder Structure

Object Type Folder Structure

This structure follows the SQL Server database folder structure, as displayed through SQL Server Management Studio.  The choice between this option and the next (Schema Object Type) is probably the one  through which a database project should be defined.

Where a database is not heavily schematic, specialised into schemas, it would not be required to provide such a level of separation.  By looking at Objects, might be the easiest way to sort, and find them.

Where a database is very large, and separated by various schemas, the additional level of separation, makes finding artefacts easier, and this type of folder structure, i.e. Object Type, may not be as suitable.

The Object Type folder structure may thus be more suitable for databases with a singular schema definition.

Schema Object Type

Folder Structure Schema_Object Type Sub-Folders

Folder Structure Schema_Object Type Sub-Folders

The Schema Object Type import option, for the folder structure, imports all objects in similar fashion to the previous data-dude folder-structure for Visual Studio.

As the name suggested, this is a combination between schema, and object type.   For example, database objects are sorted into sub-folders, in accordance with similar naming conventions.  NOTE: Any unidentified artefacts, are dumped under the schema folder, similarly to the Schema option above.

Choosing this folder structure, for the database project, would be done where a project is large, and specialised into a few schemas, probably also according to Object Types, having them split into sub-folders, according to their systems, by providing indicative naming conventions.

NOTE: This discussion does not explore how folders are created, when importing another (previous) database project from an earlier Visual Studio version.  There is a MSDN.social discussion about this, titled: SSDT project structure after conversion from VS 2010 DB project [3].

Creating a Database from Scratch

Blank-slate for Database ACMECorporation

Blank-slate for Database ACMECorporation

To create a database from scratch, should incorporate the same thought processes (on the folder structure of the database project) as when importing an existing database,and  for the same reasons.

When presented with a blank-slate, it does not propose going ape.

It’s intuitive that sticking with a folder-structure definition, like that seen through SSMS, is probably the better option, bar the reasons explained in the respective folder-structure options, above.

The reason that a blank-project starts with no folder structure, is probably because Microsoft does not know (and don’t want to propose they do) what kind of project (database) is being envisaged, or imported.  Rather than casting a structure in stone, which is more often than not counter intuitive, it is left up to the project team to define.

A problem (one that is not new) is that the folder structure in SSMS is not conducive to multi-schema systems, in that it does not provide a Schema Object folder structure, for ease of reference.  Visual Studio to SSMS alignment is only possible where the Object Type folder structure is chosen.  It is probably the one that would be chosen by most database professionals.

Summary

Visual Studio 2012 (SSDT) provides options for creating a database project folder-structure, when importing an existing database, or Visual Studio Project.  However, when creating a project from scratch, it starts with no folder structure.

It seems intuitive to follow the structure proposed by SQL Server Management Studio, for a database, keeping the project’s structure as familiar as possible.  However, a various schematic-database, might benefit from a schema specialised folder structure, and in that instance be divergent from the normative SSMS folder structure.

Citations

No. Author Name URL Data Authored Date Cited
 1.  Bob Beauchemin The Rest of the Story: Co-existing VS201 and SSDT Data Projects  http://www.sqlskills.com/blogs/bobb/post/The-Rest-of-the-Story-Co-existing-VS2010-and-SSDT-database-projects.aspx  17 April 2012  12 November2012
 2.  David Ramel

SQL Server Data Tools: A Work in Progress

 http://visualstudiomagazine.com/blogs/data-driver/2012/06/sql-server-data-tools-a-work-in-progress.aspx  05 June 2012  12 November2012
 3.  DNETFX SSDT project structure after conversion from VS 2010 DB project http://social.msdn.microsoft.com/Forums/da-DK/ssdt/thread/cd57aa97-57e9-44bd-9040-79bbce818e04 13 June 2012 12 November2012
 4.

Last Updated: 12 November 2012, 13h04