none
Need to find location for Microsoft.SqlServer.Management.Dac.dll to use SQL Azure features

    Question

  • I want to use the Microsoft.SqlServer.Management.Dac.DacStore object to automate the import/export of SQL Azure databases. The documentation at http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.dac.dacstore(v=sql.110).aspx
    indicates that the object is in :

    Namespace:  Microsoft.SqlServer.Management.Dac

    Assembly:  Microsoft.SqlServer.Management.Dac (in Microsoft.SqlServer.Management.Dac.dll)

    After installing SQL 2012 on to my machine, I cannot find Microsoft.SqlServer.Management.Dac.dll from 11.0 in order to add a reference. I can find a copy in the GAC at C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Dac\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Dac.dll,
    but this is the SQL Server 2008 R2 version, which lacks the Import/Export methods. What do I need to install in order to get access to DacStore and the related SQL Azure features? I have also tried installing the MICROSOFT SQL SERVER 2012 MANAGABILITY FEATURE PACK COMPONENTS from http://www.microsoft.com/download/en/details.aspx?id=29065, but the assembly still isn't appearing on my machine.

    Where would I find (and install) the DacStore object in the SQL Server 2012 bits?


    Scott Seely

    Friday, March 16, 2012 5:39 PM

Answers

  • This is the information I got from the DAC development team. The issue is that the DAC API has been replaced for SQL Server 2012 and SQL Server Data Tools. It is mentioned briefly in the following backward compatibility topic in the SQL Server 2012 Books Online:

    http://msdn.microsoft.com/en-us/library/cc879339(SQL.110).aspx

    I have filed doc bugs that the writers need to make the practical effects of this more clear in the SQL Server 2012 Books Online.

    What this means in more practical terms is:

    The client DAC software is called the DAC Framework (DACFx). DACFx 2.0 shipped in SQL Server 2008 R2 and the database project in Visual Studio 2010. DACFx 3.0 shipped in both SQL Server 2012 and the SQL Server Data Tools, adding new formats for DAC packages and DAC BACPAC files.

    Applications and scripts using the new DACFx 3 API can read files created using either DACFx 2 or 3.
    Applications and scripts using the old DACFx 2 API cannot read DAC packages created using DACFx 3. They have to be rewritten to call the DACFx 3 API before they can process DACFx 3 files.

    The DACFx 2 API was primarily the  Microsoft.SqlServer.Management.Dac namespace in the assembly Microsoft.SqlServer.Management.Dac.dll. That assembly and namespace are discontinued in DACFx 3.0. The assembly is no longer installed by the DAC Framework that is included in the RTM versions of SQL Server 2012, the SQL Server Data Tools, or the SQL Server 2012 Feature Pack.

    The new API for the DACFx 3 is the set of namespaces in the assembly Microsoft.SqlServer.Dac.dll, primarily:

    Microsoft.SQlServer.Dac (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac(SQL.110).aspx)
    Microsoft.SqlServer.Dac.Extensions (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.extensions(SQL.110).aspx)

    The old DAC tools in SQL Server 2008 R2 and the database projects in Visual Studio 2010 call the DACFx 2 API. They cannot work with DAC files created by DACFx 3 because they do not call the new DACFx 3 API. You can install the SQL Server Data Tools into a Visual Studio 2010 environment to get a version of the database project code that will work with both DACFx 2 and 3 files. The SQL Server 2012 tools such as SQL Server Management Studio will also work with files from either DACFx 2 or 3.

    There is another issue in regards to using the DACFx 3 API in PowerShell scripts. You must recode your PowerShell DAC scripts to use the new DACFx 3 API to process files created with DACFx 3. However, the DAC team used some .Net Framework 4 features in DACFx 3, so the new Microsoft.SqlServer.Dac assembly is a .Net Framework 4 assembly. PowerShell 2, the currently shipping version of PowerShell, does not support .Net Framework 4 assemblies. You must use PowerShell 3 with .Net 4 assemblies like Microsoft.SqlServer.Dac. PowerShell 3 is currently in a beta release from http://www.microsoft.com/download/en/details.aspx?id=28998.

    The doc changes I requested in the SQL Server 2012 Books Online include:

    Clarify the issues above.

    The DAC task topics still use the DACFx 2 API in their PowerShell examples, so they won't work with DACFx 3 files. I've requested that the examples be recoded to use the new DACFx 3 API, and a warning put in stating that the new API requires PowerShell 3.

    Remove the API reference for the Microsoft.SqlServer.Management.Dac namespaces that are not installed by SQL Server 2012.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, March 23, 2012 7:12 PM

All replies

  • I am in the process of writing an update to the SQL Azure docs that clarify that, but they won't publish for a few weeks.

    Now that SQL Server 2012 and the SQL Server Data Tool have released, the version of the DAC Framework that is installed by those tools is the best to use with SQL Azure. You can get that version of the DAC Framework by installing the client tools from SQL Server 2012, such as SQL Server Management Studio, or by installing the SQL Server Data Tool. Also, read this topic for compatibility between versions of the DAC Frameowork and SQL Server:

    http://msdn.microsoft.com/en-us/library/ee210549(SQL.110).aspx

    You can also upgrade your version of the DAC Framework by installing these three packages from the SQL Server 2012 Feature Pack:

    • Microsoft System CLR Types for Microsoft SQL Server 2012
    • Microsoft SQL Server 2012 Transact-SQL Script DOM
    • Microsoft SQL Server 2012 Data-tier Application Framework

    The feature pack downloads are on http://www.microsoft.com/download/en/details.aspx?id=29065.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, March 16, 2012 7:46 PM
  • Also, the task topics under the Data-tier Application node in the SQL Server 2012 Books Online include PowerShell examples that use the DAC namespace objects to perform DAC tasks such as extracting and deploying a DAC package, or exporting and importing a DAC BACPAC file.

    DAC overview: http://msdn.microsoft.com/en-us/library/ee210546(SQL.110).aspx

    Extract a DAC package: http://msdn.microsoft.com/en-us/library/hh231291(SQL.110).aspx

    Deploy a DAC package: http://msdn.microsoft.com/en-us/library/ee210569(SQL.110).aspx


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, March 16, 2012 7:51 PM
  • I've tried installing those three packages. I've tried installing all features for SQL Server 2012. I'm not seeing the assembly, Microsoft.SqlServer.Management.Dac.dll for version 11.0 on my machine. I've attempted this on a clean VM as well as a clean "bare bones" box, and no luck. The only version of Microsoft.SqlServer.Management.Dac.dll that I find is the 10.0 version. I went so far as to install the x64 versions, then the x86 versions when I had no luck.

    I've searched my entire hard drive. Copies do show up in

    c:\program files (x86)\Microsoft SQL Server\100\SDK\Assemblies

    as well as in the GAC at

    c:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Dac\10.0.0.0__89845dcd8080cc91

    I'm looking for the type, AzureEdition (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.dac.azureedition(v=SQL.110).aspx), and do not see it in the Microsoft.SqlServer.Management.Dac.dll assembly installed on my box. The machine is running SQL Server 2012 and VS 2010 Ultimate.

    Any other ideas?


    Scott Seely


    Tuesday, March 20, 2012 4:02 PM
  • I have asked the DAC dev team for clarification.

    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

    Tuesday, March 20, 2012 10:20 PM
  • Alan,

    I've got the same scenario as described by Scott; basically with SSDT 2.0 (the latest release), including all the MSI packages specified from the page you're linking to, as well as a complete install of SQL Server Management Tools 2012 (SSMT). I guess the only difference between my setup and the one Scott describes is that I only installed SSMT 2012 (have SQL Server 2008 R2 installed).

    I can't find the Microsoft.SqlServer.Management.Dac.dll assembly, version 11.0.0.0 anywhere.

    If I create a simple C# console application, add a reference to Microsoft.SqlServer.Management.Dac.dll, version 10.0.0.0, and write this single line of code:

     DacType.Load(File.OpenRead("db.dacpac"));

    Then I get an exception that the stream is invalid. This error is caused by differences in the contents of the dacpacs, as previous versions of dacpacs (those without xmlnl attributes in the definition file inside the dacpac) can be loaded (i.e. go to SSMT 2008, extract a Date Tier Application as a dacpac and use that with above line of code).

    Clearly the 10.0.0.0 version of the assembly is not the current assembly to load DacTypes for dacpacs created with SSDT 2.0 (unless I'm doing something completely wrong).


    Anders Borum / SphereWorks

    Thursday, March 22, 2012 5:33 PM
  • This is the information I got from the DAC development team. The issue is that the DAC API has been replaced for SQL Server 2012 and SQL Server Data Tools. It is mentioned briefly in the following backward compatibility topic in the SQL Server 2012 Books Online:

    http://msdn.microsoft.com/en-us/library/cc879339(SQL.110).aspx

    I have filed doc bugs that the writers need to make the practical effects of this more clear in the SQL Server 2012 Books Online.

    What this means in more practical terms is:

    The client DAC software is called the DAC Framework (DACFx). DACFx 2.0 shipped in SQL Server 2008 R2 and the database project in Visual Studio 2010. DACFx 3.0 shipped in both SQL Server 2012 and the SQL Server Data Tools, adding new formats for DAC packages and DAC BACPAC files.

    Applications and scripts using the new DACFx 3 API can read files created using either DACFx 2 or 3.
    Applications and scripts using the old DACFx 2 API cannot read DAC packages created using DACFx 3. They have to be rewritten to call the DACFx 3 API before they can process DACFx 3 files.

    The DACFx 2 API was primarily the  Microsoft.SqlServer.Management.Dac namespace in the assembly Microsoft.SqlServer.Management.Dac.dll. That assembly and namespace are discontinued in DACFx 3.0. The assembly is no longer installed by the DAC Framework that is included in the RTM versions of SQL Server 2012, the SQL Server Data Tools, or the SQL Server 2012 Feature Pack.

    The new API for the DACFx 3 is the set of namespaces in the assembly Microsoft.SqlServer.Dac.dll, primarily:

    Microsoft.SQlServer.Dac (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac(SQL.110).aspx)
    Microsoft.SqlServer.Dac.Extensions (http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.extensions(SQL.110).aspx)

    The old DAC tools in SQL Server 2008 R2 and the database projects in Visual Studio 2010 call the DACFx 2 API. They cannot work with DAC files created by DACFx 3 because they do not call the new DACFx 3 API. You can install the SQL Server Data Tools into a Visual Studio 2010 environment to get a version of the database project code that will work with both DACFx 2 and 3 files. The SQL Server 2012 tools such as SQL Server Management Studio will also work with files from either DACFx 2 or 3.

    There is another issue in regards to using the DACFx 3 API in PowerShell scripts. You must recode your PowerShell DAC scripts to use the new DACFx 3 API to process files created with DACFx 3. However, the DAC team used some .Net Framework 4 features in DACFx 3, so the new Microsoft.SqlServer.Dac assembly is a .Net Framework 4 assembly. PowerShell 2, the currently shipping version of PowerShell, does not support .Net Framework 4 assemblies. You must use PowerShell 3 with .Net 4 assemblies like Microsoft.SqlServer.Dac. PowerShell 3 is currently in a beta release from http://www.microsoft.com/download/en/details.aspx?id=28998.

    The doc changes I requested in the SQL Server 2012 Books Online include:

    Clarify the issues above.

    The DAC task topics still use the DACFx 2 API in their PowerShell examples, so they won't work with DACFx 3 files. I've requested that the examples be recoded to use the new DACFx 3 API, and a warning put in stating that the new API requires PowerShell 3.

    Remove the API reference for the Microsoft.SqlServer.Management.Dac namespaces that are not installed by SQL Server 2012.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.

    Friday, March 23, 2012 7:12 PM
  • Thanks for the thorough explanation. I'll be trying this soon. I am already using .NET 4 from PowerShell 2. I am curious to see if that is sufficient to do this work. If not, I'll pull in PS 3. I'll post later when I see the results. Thanks again!

    Scott Seely

    Friday, March 23, 2012 7:33 PM
  • Alan,

    your detailed reply cleared things up; I just wrote a small test project to verify that DACfx 3 files can be opened using your suggested references and lights are green. In retrospect, it would have been nice if the starting page for DACfx 3 API documentation had stated, that the new API for the DACFx 3 is the set of namespaces in the assembly Microsoft.SqlServer.Dac.dll (http://msdn.microsoft.com/en-us/library/hh753459(v=sql.110).aspx), but I'm glad that is sorted now; hopefully the doc changes are given high priority.


    Anders Borum / SphereWorks

    Friday, March 23, 2012 7:45 PM
  • Thanks for the detailed response. I am still not clear as to how I can resolve this issue.

    I run:

    dacCli.exe -s "servername" -u sa -p xxxxxx -d dbname -f d:\temp\bacpacname.bacpac -x

    and get:

    Unhandled Exception: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Management.Dac, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.

    My questions:

    What is: Remove the API reference for the Microsoft.SqlServer.Management.Dac namespaces that are not installed by SQL Server 2012

    How to: Export DACFx 2 files from SQL2008 as it looks like Azure doesn't do DACFx 3?

    Sunday, July 08, 2012 11:44 AM