One of the challenges I face when overseeing a project is keeping the database changes in sync across the solution and the multiple server environments throughout an application’s lifecycle. From small projects where developers have their own copy of a database to multiple server environments, tracking changes via scripts and keeping those scripts changes in sync with the version of the code it should be checked in with becomes an onerous task. Add to that the compilation of a deployment script that merges together SQL object changes (some conflicting) and I find this to be quite taxing and often prone to errors.
Recently, I have begun to employ the use of Visual Studio SQL Server Database Projects as part of all my custom development projects with the hopes it would bring some organization to this chaos. SQL Server Database Projects are not a brand new feature for 2013, they have been around since Visual Studio 2010, but the 2013 iteration has evolved into a feature rich tool. Utilizing the SQL Server Database Project gives developers the ability to manage a database’s schema, check the schema in source control (along with related code changes in the solution) and perform schema comparisons between the database and the SQL Project or two different databases.
In this three part blog series I will review how Visual Studio SQL Server Database Projects can be leveraged as part of an application’s solution and used it to improve productivity and maintainability through out a project. Part one will focus on creating a project and how to add and edit SQL objects. Part two will review the schema comparison tools. Part three will concentrate on publishing the database project and promotion strategies around integration with Team Foundation Server.
Creating a Project
To start a SQL Server Database Project, a new project must be added to a new or existing Visual Studio solution. The SQL Server Database Project is found in “Other Languages > SQL Server” in the “Add New Project Dialog”.
Once the project is named and created, SQL Server Objects can be added to the project. If the target database already exists, the tool provides a means to import the database’s schema three different ways.
- Using a SQL Server Data-tier Application extract file (*.dacpac)
- Connecting directly to a SQL Server database
- Using one or multiple script files with all the object definitions
All three options are quite easy to utilize and ultimately provide the same end result of all the objects in the database being loaded as individual script objects into the project. I typically just connect directly to the server and import the schema but the other two options are useful if direct access to the server or database is not available. While importing the database, Visual Studio allows the option to define the folder structure that will organize the script objects the solution generates. Objects can be organized by Schema, Object Type and SchemaObject Type.
Creating and Editing Objects
Alternatively, objects can be added manually. Existing or new stored procedures can be written as a script file and added to the solution. User Defined Functions, Views, Sequences, Queues and other objects all can be created and added to the solution via a create script file. All edits for these objects work the same as editing them in a SQL query window on the server. The only real difference is all scripts are CREATE scripts. Alters and drops are not used as part of the solution.
Tables can be created the same way, but Visual Studio also has a visual table designer that is an improvement over the SQL Management Studio Table Designer.
- Columns, keys, constraints and indexes are all listed out for quick reference on the design surface.
- A T-SQL Editor pane along with the design surface that syncs edits between the script and design surface. It also allows the user to review and edit all SQL script code related to the table.
- The ability to add additional columns to the design surface. Identity, identity seed, default and description are all available as part of the table designer grid where they were only accessible via the properties window in SQL Management Studio.
There are some additional configuration settings that are still found in the properties panel that are there to further define the table. For instance, the index editor will define the index name and the index columns. Included columns cannot be managed from the designer or properties panel, but those additions can be added by editing the SQL created in the T-SQL editor pane. The designer also will generate stub templates in the T-SQL pane for Triggers that can be modified.
In the next post I will take an in depth look at the Schema Compare tool and how it can be used to identify and promote database changes between environments.