September 9, 2021 at 9:02 am
Sorry if this is the wrong forum, but it is SQL Server 2016 Developement issue.
I have a SQL Server Database Project in VS2017, and I'm creating dacpacs from it which are subsequently used to deploy to various environments from Test through to Production.
After deployment I know I can check where the latest dacpac version and description are held:
SELECT *
FROM msdb.dbo.sysdac_instances_internal
WHERE instance_name = 'AdventureWorks';
But I'd like to retain a history of that within my database.
So I've created a table called dbo.Version with the relevant columns.
CREATE TABLE dbo.Version
(
VersionID INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Version PRIMARY KEY,
InstallUTC DATETIME NOT NULL CONSTRAINT DF_Version_InstallUTC DEFAULT ( GETUTCDATE() ),
DacpacVersion VARCHAR(10) NOT NULL,
ReleaseNotes VARCHAR(MAX) NOT NULL
);
In the SQL Server Database Project I've been experimenting with the Post Deployment Scripts.
My first attempt was a straight forward INSERT statement
INSERT dbo.Version (DacpacVersion, ReleaseNotes)
SELECT type_version, description
FROM msdb.dbo.sysdac_instances_internal
WHERE instance_name = 'AdventureWorks';
However when I subsequently use the dacpac to update the database I seem to be getting the previous version recorded.
I've tried to call another script (that contains the same INSERT statement as above) from within the Post Deployment Script but I get exactly the same outcome, therefore I must conclude that msdb.dbo.sysdac_instances_internal only gets updated after the Post DeploymentScript has run.
I've looked at SQLCMD Variables but I didn't find one for the dacpac version so now I'm stuck.
I'd be grateful if anyone could advise or guide me in the right direction.
Regards
G
September 9, 2021 at 9:48 am
I have not tried this, nor have I used that functionality, so this may not be of help, but have you considered putting a simple trigger on msdb.dbo.sysdac_instances_internal to push all of the history to another table?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 9, 2021 at 9:52 am
Thanks for the prompt response.
It did cross my mind but as the production server isn't under my control I ruled it out quite early on.
September 9, 2021 at 11:13 am
Fair enough.
Does the table msdb.dbo.sysdac_history_internal contain anything useful?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 9, 2021 at 1:00 pm
msdb.dbo.sysdac_history_internal doesn't contain anything regarding the dacpac version, so in this situation I would say that it doesn't contain anything useful, but thanks for continuing to look at my problem.
Giles
September 9, 2021 at 1:10 pm
msdb.dbo.sysdac_history_internal doesn't contain anything regarding the dacpac version, so in this situation I would say that it doesn't contain anything useful, but thanks for continuing to look at my problem.
Giles
No problem, this is interesting.
Going back a step, how are you creating and deploying DACPACs? Have you scripted and automated the entire process?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 9, 2021 at 1:27 pm
At the moment I'm still a newbie to dacpacs, so I'm open to correction if I'm doing this wrong.
The objective is to execute the .dacpac on different environments that I don't and will not have access to, so I can't use Publish.
When I build the Project it produces a *.dacpac file in the bin\Debug\ folder of the Project.
I then execute that manually in SSMS against the target instance and database, the idea being that I can pass this over to Production DBAs to do that going forward.
Another solution I've considered, but I'm really not sold on, is in the Post Deployment Script I change the INSERT statement so it just inserts the hardcoded values I give it into my dbo.Version table, but it seems wrong (duplicating data) to do that if I've already set the Version and any release notes in the Data Tier Properties. It wouldn't take long before either the Version or the Script were forgotton about (although I did consider putting a UNIQUE constraint on the DacpacVersion column, only the dacpac won't rollback if that fails so it would leave the Version table in a mess)
September 9, 2021 at 2:13 pm
You're not doing anything wrong. Deployment processes often vary from one organisation to another.
Perhaps the simplest solution would be to hand the DACPAC to the DBAs along with a .sql file which updates your dbo.Version table.
Tell them to deploy the DACPAC and then run the .sql file afterwards. Make the .sql file idempotent, so that it can be run multiple times without creating duplicates.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 9, 2021 at 2:39 pm
It's certainly an option, although whilst I try to make all my scripts "idiot proof" I can't vouch for how meticulous the DBAs are that I'll be handing the DACPAC and script to be certain of them running them both or in the correct order!
September 9, 2021 at 2:47 pm
Dacpac works as a deployment mechanism (although there are shortcomings). However, it's now how you track a history of changes to objects in the database. There's just no easy mechanism that will help you here. Instead, put the database code into source control. That will allow you to develop your databases the same way you develop code, and then you get the history of changes, who made those changes, and what, specifically, was changed.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 9, 2021 at 2:48 pm
It's certainly an option, although whilst I try to make all my scripts "idiot proof" I can't vouch for how meticulous the DBAs are that I'll be handing the DACPAC and script to be certain of them running them both or in the correct order!
True 🙂 But if you have at least read access to the prod databases, you can run an after-deployment check to make sure. You (or someone) should probably be doing this anyway, to verify that the release was successfully deployed.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 9, 2021 at 3:51 pm
Dacpac works as a deployment mechanism (although there are shortcomings). However, it's now how you track a history of changes to objects in the database. There's just no easy mechanism that will help you here. Instead, put the database code into source control. That will allow you to develop your databases the same way you develop code, and then you get the history of changes, who made those changes, and what, specifically, was changed.
I very much agree with the Source Control recommendation but my objective is so that the DBA on any one of our customer's instances can tell us what "version" their database is at when we need to investigate an issue. We will not have access to these customers and rely on their DBAs to assist so knowing what DACPAC was last deployed really helps, as I mentioned earlier, we can get that from msdb.dbo.sysdac_instances_internal but it would be nice to see what the previous version was, especially if something has stopped working for example.
I'm always open to alternative solutions, and even being shown the "right way", so if you know of a way of getting the version from the Source Control then I'd be really grateful for any pointers. (before you ask I don't know what Source Control we use yet as this is prep before I start my new job in earnest).
September 9, 2021 at 4:05 pm
Hmmm... No. You'd have to mark the system in some manner. You can avoid tables by using Extended Properties. That's an easy one.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
September 10, 2021 at 7:43 am
But surely regardless of where and how I store it, the issue I've got is how to extract it (the version)
I'm sure I'll figure something out once I've been exposed to the Source Control and if I do then I'll be sure to post back.
September 10, 2021 at 10:58 am
But surely regardless of where and how I store it, the issue I've got is how to extract it (the version)
I'm sure I'll figure something out once I've been exposed to the Source Control and if I do then I'll be sure to post back.
Source Control is not going to help much in terms of understanding which version of database code has been deployed at a particular client.
But I do have another suggestion. It's not pretty, but going back to your very first post, how about this:
CREATE TABLE dbo.VersionHistory
(
VersionID INT NOT NULL IDENTITY(1, 1)
CONSTRAINT PK_Version PRIMARY KEY
,DeactivatedUTC DATETIME NOT NULL
CONSTRAINT DF_Version_DeactivatedUTC
DEFAULT (GETUTCDATE())
,DacpacVersion VARCHAR(10) NOT NULL
,ReleaseNotes VARCHAR(MAX) NOT NULL
);
You know that you can always get current version from sysdac_instances_internal, so that, in combination with this table, gives you all the information.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply