Database Project to Validate database script and easy to manage your changes from Developer.

Recently I am working on development and migration of Database. Lot of developer creating and modifying the SQL objects.

I have how to manage and validate those objects as well migrated one. I surfed and found the solution using Data Tier Application. The moment I refreshed the Memory, that I show a Microsoft Webinar on the same by Jacob Sebastian.

Data Tier Application is feature of SQL Server 2008 R2 and the SQL Server 2012 has some enhancements in that.

Lets go through with this new feature, how to manage, validate and publish changes to Database as well compare the schema of 2 different databases as well.



1. Extract the Data Tier Application

The first step of the process is to Register the Data Tier Application and Extract it from the Database as DACPAC  package. This file goes to developers who are creating and modifying SQL object. They will do change and update the Package. Build that and again pass back to DBA. See the below images how to extract Data Tier Application?



2. Create Database Project

Next Steps is open VS 2010 and Create new Project "Data Tier Application".

The project contains template same like in SSMS under database node. (Tables, Schema, SPs, Function etc.)

3. Load Data Tier Application to Database Project

Import the created DACPAC (Data Tier Application)  package to project.

It will load the Scripts for your all database objects available in DACPAC package.

4. Convert Project to SQL Server Database Project

Now time to Convert this Project to SQL Server Database project. The Option comes when you have SSDT installed on you system. The tool is for Accessing SQL Server 2012 in VS 2010.


5. Compare Schema of 2 Database s and Upgrade the Target Database

After converting the project to Database project you lot to do and you play with project for Schema comparison, Deployment, Add/Modify object and create DACPAC package to pass back to DBA after changes.

In below screens you can how to compare the 2 different databases. It will show you difference with categorized way New, Modified and deleted objects.


You can upgrade the target database after the schema comparison.


6. Set Project and Database Properties

Before we move to development and other process we always set the project properties and some configuration related to target system.

As like, here we are also doing the same for project properties. Like Target SQL Server version, Database Backup before update etc etc.




7. Deploy/Publish changes to Database

Same thing occur here before deployment or publish of the work. We have to set some of properties for Database.

i.e
Some of Set Options,
Database Recovery,
File Group,
Partition,
Collation,
Database State (DB Access, Encryption, Mode),
Transaction Log,
Log Shipping etc.




When you build the Project, you will get script validation errors. Those are against best SQL practice. Check errors and resolve those. It's very tough to go to single object Modify it through SSMS. It's good way to validate your objects.


Now, you are ready for publish your work. See the image and follow the steps as in image.


8. Create Data Tier Application Package and Update Changes to Database using DAC Package

The same way to deployment from Project. We do the DACPAC package and update the DB. (If user/developer has no permissions to do that). It will create the DACPAC package and
send back to DBA.

See image for create DAC PAC package.


After sending back the package to DBA. DBA will upgrade the Database for the changes done by developer to scripts. See the user friendly steps for same as in below images.








Thanks. You have learned some thing new.