June 23, 2017 at 11:40 am
I have created dacpac files and am familiar with the technology but I don't under what a "Data-tier App" is. Can you clarify this?
What is the benefit of registering a DB as a Data-tier app?
I presume that a "registered Data-tier Application" is not the same thing as a dacpac file.
TIA,
BD
June 27, 2017 at 5:15 pm
Barkingdog - Friday, June 23, 2017 11:40 AMI have created dacpac files and am familiar with the technology but I don't under what a "Data-tier App" is. Can you clarify this?What is the benefit of registering a DB as a Data-tier app?
I presume that a "registered Data-tier Application" is not the same thing as a dacpac file.
TIA,
BD
You can have applications that are two tier, three tier, N-tier and all the different names someone bored comes up with. It's just different areas or layers of an application. Business Logic tier or layer (or bll) has exactly what it's called - the business logic. Presentation tier has the front end or user interface. Data tier is just the data part of the application.
For an application, you can define all of the database objects - tables, stored procedures, functions, views, etc. A logical grouping of all those database objects for the application is the DAC. The dacpac is just the package of all of those objects in a file. In order to use a dacpac, SQL Server needs to be registered as a data tier app - otherwise it would have no idea to do with the file. So you register SQL and then it tracks the metadata about the database- the different properties, versions for the data end of the application. So your presumption is correct in that the dacpack is not the same as registering a data tier application. The dacpac has the definitions and properties and registering allows SQL Server to use the files particularly with deployments.
It's intended to be for managing the life cycle and deployment process better. There is a list of benefits as well as a better explanation in this Microsoft doc:
Data-tier Applications
Sue
June 27, 2017 at 8:00 pm
Sue,
That's a great answer. I have one question: you wrote
>>> The dacpac has the definitions and properties and registering allows SQL Server to use the files particularly with deployments.
Before I knew about Data-tier applications I created a dacpac file using SSMS for a Dev DB. Then I applied the dacpac (using Sqlpackage.exe) to the same DB in Staging and it updated the Stage DB as expected. To my knowledge I didn't register any DB, just used the dacpac file, but successfully "deployed" to Staging.
From what I have read I think that registration is most valuable in the case where, say, a DBA, makes a change to the prod DB (adds in index) and forgets to update the other environments. There is an option in SqlPackage that prevents the new dacpac file from updating the live DB if the metadata (captured during registration) does not agree with the current live DB state. (I think I have that right).
BD
June 27, 2017 at 8:11 pm
Barkingdog - Tuesday, June 27, 2017 8:00 PMSue,That's a great answer. I have one question: you wrote
>>> The dacpac has the definitions and properties and registering allows SQL Server to use the files particularly with deployments.
Before I knew about Data-tier applications I created a dacpac file using SSMS for a Dev DB. Then I applied the dacpac (using Sqlpackage.exe) to the same DB in Staging and it updated the Stage DB as expected. To my knowledge I didn't register any DB, just used the dacpac file, but successfully "deployed" to Staging.
From what I have read I think that registration is most valuable in the case where, say, a DBA, makes a change to the prod DB (adds in index) and forgets to update the other environments. There is an option in SqlPackage that prevents the new dacpac file from updating the live DB if the metadata (captured during registration) does not agree with the current live DB state. (I think I have that right).
BD
The database can get registered as a Data Tier App implicitly - performing a dac type of activity. I'm not sure which times it needs to be explicitly done and which times it will just happen on it's own. I've gotten the error before that I needed to register the database but can't remember what activity it was.
And yup, I agree. I think the validation part and setting that up probably provides the most value. You have it right - it knows how the database was from the metadata on the last or most recent version.
Sue
June 27, 2017 at 9:13 pm
Thank you again. It's amazing to me how the notion of "data-tier app" and registration is so valuable yet most documentation is largely unclear on this on their purpose.
BD
June 30, 2017 at 4:25 pm
There are some limitation when registering database as Data Tier App, I will let you google that info or wizard will help you identify unsupported features, but yes it is amazing feature and can be used for version control.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply