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. Read more: net curry
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. Read more: net curry