Database Development with Visual Studio 2010: Database Projects and Database Server Projects
Posted by: Gouri Sohoni ,
on 7/24/2010,
in
Category Visual Studio
Abstract: Visual Studio 2010 Data-Tier (DAC) applications are targeted towards SQL Server 2008 R2 and meant for small applications. In this article, we will discuss various components of Database projects and Server projects.
In last article Database Development with Visual Studio 2010: Overview of SQL Server Data-Tier Application (DACPAC) we discussed what Data-Tier (DAC) applications are and how to create and deploy it using Visual Studio 2010. DAC applications are targeted towards SQL Server 2008 R2 and meant for small applications. In this article, we will discuss various components of Database projects and Server projects. Database projects or Database Server projects are targeted towards various versions of SQL Server like 2005, 2008 as well as 3rd party databases.
Database project can be used to define user database whereas Server projects can be used to define server level objects as well as modifications to master database.
With Database project and Server Project types, you can manage the scripts for database by using Visual Studio 2010. With Visual Studio 2010, you have additional features as build and deployment process. With this Build and Deployment, the changes can be applied to actual database. Both of these projects contain Build settings and deployment settings. Database Project contains Database settings whereas Server Project contains server settings.
Database project or Server projects are used to create schema for several objects and for doing changes to existing schemas by using .sql files. You can keep the objects in Visual Studio 2010 till you are satisfied with the schemas and then later deploy the database. These objects can also be stored in the source control, with multiple versions of the database schemas. You can decide which version needs to be actually deployed and only that version from the source control can be used.
Structure of project for Database or Server
Data Generation Plans: These plans contain information about how to generate test data for the database which you will deploy later. This test data can be used to do the unit testing of stored procedures which are designed with database project.
Schema comparisons: These contain information about comparison between the current project related schema and some other schema. It has a file extension `.scmp’ which can be used when required.
Schema Objects: These are .sql files which have various objects stored. Mostly these objects are declared in different files. These objects can be tables, stored procedures, functions etc.
Scripts: These can be used for pre and post deployment of database.
Property files: There are 4 property files namely `Database.sqlcmdvars’ , `Database.sqldeployment’, `Database.sqlpermissions’, `Database.sqlsettings’. These files are in the Properties folder in Database Project and can be viewed from Solution Explorer.
· Database.sqlcmdvars: SQL CMD variables like Default data path, database name and default log path
· Database.sqldeployment: Deployment related settings like whether to re-create database, whether to backup database before deployment etc.
· Database.sqlpermissions: GRANT or DENY statements to provide permissions of the objects with xml based file
· Database.sqlsettings: Settings related to database like backup policy or collation, default filegroup or filestream filegroup, compatibility level , state of database (online/offline) etc.
With Server Project you can view `Master.sqlcmdvars’, `Master.sqldeployment’, `Master.sqlpermissions’, `Master.sqlsettings’, `Server.sqlsettings’ as property files.
· Master.sqlcmdvars: SQL CMD variables like default data path, database name, default log path.
· Master.sqldeployment: Deployment specific settings like whether to deploy database properties, backup database before deployment or target server.
· Master.sqlpermissions: Xml based way of specifying GRANT or DENY statements for permissions to objects in master database
· Master.sqlsettings: Configuration settings to master database like ANSI NULLS, ARITHABORT, QUOTED_IDENTIFIER etc
· Server.sqlsettings: Server settings which can be verified during deployment like CLR enabled, server collation, transaction abort etc. These settings cannot be deployed to server as certain settings require restarting database engine. But as a precaution you can block the deployment if the values differ. Thus the deployment will fail if any of the values are not acceptable.
With this article we observed different components of Database projects and Server projects. In next article we will discuss how to actually create database project, add objects and deploy it.
This article has been editorially reviewed by Suprotim Agarwal.
C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.
We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).
Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.
Click here to Explore the Table of Contents or Download Sample Chapters!
Was this article worth reading? Share it with fellow developers too. Thanks!
Gouri is a Trainer and Consultant on Azure DevOps and Azure Development. She has an experience of three decades in software training and consulting. She is a graduate from Pune University and PGDCA from Pune University. Gouri is a Microsoft Most Valuable Professional (MVP) - Developer Technologies (Azure DevOps), Microsoft Certified Trainer (MCT) and a Microsoft Certified Azure DevOps Engineer Expert. She has conducted over 150 corporate trainings on various Microsoft technologies. She is a speaker with Pune User Group and has conducted sessions on Azure DevOps, SQL Server Business Intelligence and Mobile Application Development. Gouri has written more than 75 articles on Azure DevOps, TFS, SQL Server Business Intelligence and SQL Azure which are published on
www.sqlservercurry.com and
www.dotnetcurry.com. You can connect with her on
LinkedIn.