none
DAC deployment history (i.e. sysdac_history_internal) is not working RRS feed

  • Question

  • Hi there,

    I was expecting that whenever a publish (aka deploy) operation was done to a registered data tier application, that a record was supposed to be written to the sysdac_history_internal table in msdb.dbo.

    However, I have yet to see that happen, under any publishing or deployment technique I've used so far.

    Am I missing something?

    Most of my testing has been on SQL Server 2012.  I thought this was going to be one of my favorite features, and would replace our need to track that kind of information ourselves in a custom table or something.

    I do see records in msdb.sysdac_instances and that all seems to be working fine, and publish/deploy even upgrades the version number properly.

    Do I just have to enable the history tracking somehow?

    Thanks very much!

    Ryan

    Friday, June 21, 2013 2:25 PM

Answers

  • The fix for this issue is planned for inclusion in our SQL Server 2014 release, due around the time of the SQL Server 2014 RTM this year. 

    Kevin

    Monday, January 27, 2014 9:08 PM
    Moderator

All replies

  • Found a related post on Microsoft Connect.

    http://unsafe6.chirasu.com/browser.php?indx=4756045&item=2071

    I wonder if this means the next version of DacFx will have it.  However, I'm not getting records in that table no matter what way I publish, it seems.

    So, it would be great to hear from someone...

    Thanks!

    Ryan

    Friday, June 21, 2013 3:45 PM
  • I have not yet installed the August version of SSDT, but I just noticed that some of my recent activities are showing up in msdb.dbo.sysdac_history_internal now.  I'm not sure if it's totally working but it's better than it was.  Maybe this was fixed in the July version of SSDT and we can mark this as answered?

    Thanks!

    Ryan

    Thursday, September 5, 2013 12:41 PM
  • Ok, this is weird now.  Now there are records created but they don't relate back to the instances properly.

    I just replied to a related post in the general SQL Server Forum, where someone else had noticed this same thing.

    Here is that thread:

    http://social.msdn.microsoft.com/Forums/en-US/83ed9e84-669f-4c5a-97c5-f008951f550c/instanceid-doesnt-match

    Hope this can be fixed.  I'd like to think of instance_id like a FK and inner join on it.

    Thanks,

    Ryan

    Friday, September 6, 2013 2:59 PM
  • We looked into this last week.  Thanks Ryan!

    Sure enough there are some bugs in this area.  We'll let you know when the fix is available.

    Patrick

    Monday, September 9, 2013 4:01 AM
  • Any update on this ? I have instances where the history table has entries and others where the history table is empty.
    Monday, November 25, 2013 9:59 AM
  • Ryan_FEI, has there been any update or workarounds you have come up with on this? I'm not getting any records to this table when I publish and I need a way to log when publishes happen and what is in them. 
    Thursday, January 23, 2014 8:56 PM
  • The fix for this issue is planned for inclusion in our SQL Server 2014 release, due around the time of the SQL Server 2014 RTM this year. 

    Kevin

    Monday, January 27, 2014 9:08 PM
    Moderator
  • Hi Tony,

    Sorry for the late response.

    Actually, we haven't yet adopted the DAC version number and DAC publishing in our development/release/deployment process.  We are still in a conversion phase (doing lots of other things) and working in a hybrid environment, where we're basically using SSDT now to generate our scripts every day, and then again at the end of each release, but... the big difference for us is we're still saving the SQL script each release, and then deploying based on those, rather than relying on DacFx to figure things out again as it deploys along the way.  BTW - I don't think we'll ever get to the point of trying to ask DacFx to skip over several releases and expect it to know how to take care of things from point A to point E, if you know what I mean.  In other words, the next step for us would be to keep a DACPAC for every release, and it's associated pre/post scripts, and to ditch the saved SQL script, but when it came time to upgrade a database through a series of releases, we would 'bump it along' through the different DACPACs, and associated scripts, rather than trying to do one big publish at the end.

    I guess all that is kind of beside the point, sorry.

    Anyway, as far as tracking the version information, I haven't yet adopted the DAC version system, and instead we store a version number in our own table.  I would like to move away from that soon though, and, actually, I suppose we'll be able to do that, and rely on the sysdac_instances table.  It's just that we won't be able to rely on the history table unfortunately, given that we're still migrating from 2008 to 2012 and won't be moving to 2014 for quite a while.

    Hope that helps...good luck,

    Ryan

    Monday, January 27, 2014 9:26 PM
  • Thanks Kevin... please see my reply to Tony toward the end of this thread, but will mark as an answer for now I suppose, even though we'll be on 2012 for a while.  Thanks again for following up...
    • Marked as answer by Ryan _ FEI Monday, January 27, 2014 9:27 PM
    • Unmarked as answer by Ryan _ FEI Monday, January 27, 2014 9:27 PM
    Monday, January 27, 2014 9:27 PM
  • Thanks for the response Kevin....

    I agree with you.  From my sandbox play with DACFx, I'm not sure that I would feel totally comfortable automating it completely and I'm also leary about a big final publish at the end of a release cycle.  I was hoping it would be auto-incrementing the version with every publish so we could get to a 'point-in-time' version if we wanted. The part I do like about it is that we struggle with manual script ordering because of dependencies and things like that is all taken care of with DACPAC.  I see us adopting something like you with applying incremental scripts generated by DAC throughout the course of the release cycle so that the production environment goes through the same changes that the non-prod environments did. And then some home-grown table for versioning or something....hopefully future versions of DACFx handle this a little more thoroughly as you say.

    In a related note, since we're having a discussion here....the other thing I'm very unhappy about is in the scripts which output from publish process, it tries to 'DROP USER..' from the target db because it treats users as an object like a table/proc. It's natural for different environments to have different security models.  So do you go into the script manually and chop out these DROP USER statements or is your security an exact match for source/targets?  I would have to go in and manually remove these from the scripts? I hate to introduce this step to the process, but it may be a necessary evil.  Threads I have read on this sound like MS is not going to address this any time soon. Not even with a publish option to ignore users.

    All of this said, I like how easy it is for a developer to keep their local environments up to date with the code base, which is something we have struggled mightily with. Keep a UI/UX developer up and running without continually breaking their db's is a time saver because they don't want to know everything about the database environment. Anyways, just some thoughts....thanks for the discussion!

    Tony

    Tuesday, January 28, 2014 3:39 PM
  • Wow Tony, great thoughts.  I'm sorry to say I don't have any great answers for you yet, but I understand exactly what you're saying and have some of the same questions.

    Just this week I finally sat down to try to resolve some differences between our SSDT project and some of our 'Test' databases, to try to get us closer to being able to automate applying changes in a safe way to those environments at least.  The main issue was that, before yesterday, the 'Logins' that the users were mapped to in the databases, were not represented in our SSDT project.  So the 'FOR LOGIN' piece of the CREATE USER statement was basically responsible for showing our expected users/groups as different between the project and database.  I was able to resolve that by adding explicit 'Security' items to the project with the correct names, organized into a 'Security' folder ( (see add new item > user, and login, and roles, etc, which you probably already know about).

    Anyway, I suppose that environment, and actually our other environments (the hosted ones anyway) do end up being exact matches, so what you're running into isn't a problem for us otherwise.  I think part of why that works well is b/c we're now using Windows domain groups (basically one for Developers, one for Managers, and one for Support people), and all we're doing is adding logins and users for those groups, rather than users themselves.  Therefore, when people are hired or whatever, that change just gets made in A/D.  Been working well.

    Also, I suspect there might be a way to ignore users and groups in the publish, b/c I see that SchemaCompare has that, and ultimately all that stuff breaks down, I think, to the DacServices .NET object, and the DeployOptions class.  I'd have to research though...  You could try clicking it in SchemaCompare, opening the file as XML and see what property gets added, to get a clue of the name of the option you might need.

    Otherwise, I suspect I might run into the same issue as you for our non-hosted environments, where they may have additional security/users, so I might need to know that option too.

    Feel like I'm rambling and not helping so will stop...

    Good discussion.  Email me anytime (not sure I can help but will tell you if I know something).

    Take care,

    Ryan White

    ryan.white@feisystems.com

    Thursday, January 30, 2014 6:22 PM