difference between Data Tier Applications and database projects

Question

Hello,

We have a shrink wrapper solution that contains database schemas, and we are looking for the best mechanism to distribute and upgrade database schema. Currently we use the visual studio database projects with vsdbcmd command in our installer to install/upgrade database scripts.

Apart from multiserver management, does Data Tier applications provide any other advantage ? I could not find a clear reference on it

Answer 1

Hi, you might want to review this article: http://msdn.microsoft.com/en-us/library/ee240739.aspx

You can perform the following tasks with database projects:

• Create and deploy a database.

• Put your database under version control.

• Create a database project by importing the objects from an existing deployed database.

• Compare your database project to a deployed database.

• Import changes made to a deployed database so that the database project again matches the deployed database.

• Add, modify, and delete database objects and scripts.

• Create one or more data generation plans to populate your database with test data.

While a data-tier application (DAC) is an entity that contains all of the database and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects instead of having to manage them separately. A DAC allows tighter integration of data-tier development with the development of the associated application code. It also gives administrators an application level view of resource usage in their systems.

Hope I answered your questions.

Best regards, Vlad.

Answer 2

>>>"...A DAC allows tighter integration of data-tier development with the development of the associated application code...."

I'm really having a hard time picturing this, but it might be just what I need.

Can I use it to "check in" C# code, like I do stored procedure?

Can the data-tier application hit on any database? Or only a database dedicated to the app?

Thanks.

 

---

-------------- Bill Ross

Answer 3

We have a shrink wrapper solution that contains database schemas, and we are looking for the best mechanism to distribute and upgrade database schema.

You need database experts and a tools like SQL Compare and SQL Data Compare.

Almost impossible to upgrade populated tables automatically at client sites. So many things can go wrong.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 4

Seeker,

I highly recommend reading this white paper as well:  http://msdn.microsoft.com/en-us/library/ff381683(SQL.100).aspx.  One example that is not necessarily obvious is the ability to determine if a change to your schema has occurred outside the standard DAC upgrade process.

 

Bill,

Essentially, DAC is providing the beginnings of an application model for a database and enhances the deployment aspect of the application upgrade lifecycle (develop, deploy, manage) by providing a deployment artifact which can be deployed as a new database or as an upgrade to an existing database.  While I'm not sure I entirely understand the "dedication" question, a database doesn't need to be dedicated to an application since you may create a new database application instance with a DAC.

 

 

SQLUSA,

While nice to have, seasoned experts and additional tools may not always be available or affordable.  As a database becomes more complicated, upgrade services may not be able to handle all of the subtle nuances necessary for a perfect upgrade, however, I encourage people to give the upgrade services a try.  For more information on the upgrade service, please take a look at the white paper I linked at the top of the post.

Answer 5

 As a database becomes more complicated, upgrade services may not be able to handle all of the subtle nuances necessary for a perfect upgrade, however, I encourage people to give the upgrade services a try. 

It's more than that. Customer may have done customization changes to the distributed software. Just reality.

I agree with you, any automation is very helpful in the upgrade/migration process.

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012

Answer 6

Okay, I see. Thanks.

---

-------------- Bill Ross