none
Data-Tier Apps and SQL Server Editions

    Question

  • It appears that in the Nov CTP, I can install a data-tier app without the requirement for a utility control point. If so, are there any restrictions of which Editions of SQL Server 2008 R2 can contain data-tier apps?

    Also, in either the Nov CTP BOL or VS 2010 B2 BOL, it mentions that I can create a DACPAC from SQL Server 2000 and  above databases, but the "you can deploy on" server choice appears blank. Just to confirm, data-tier apps can only be deployed on SQL Server 2008 R2 instances? Or does SQL Server 2008 support them also?

    Thanks,
    Bob Beauchemin
    SQLskills
    Friday, January 8, 2010 9:06 PM

Answers

  • Hi Bob,

    Hopefully the following charts will help you with all your questions. With regards to SQL 2000, we don't support creating DACs from SQL 2000. It might work, but is not supported. Do you have a link for the VS 2010 B2 BOL where is states this?


    SKU and SQL Server Version Support for DAC

    All features of Data-tiered Applications are supported in all SQL 2008 R2 SKUs.

    ·         Data Center Edition

    ·         Developer Edition

    ·         Enterprise Edition

    ·         Evaluation Edition

    ·         Standard Edition

    ·         Standard for Small Business Edition

    ·         Web Edition

    ·         Work-group Edition

    Table 1 shows which DAC features are supported on each SQL Server version. 

    Table 1: DAC Feature Support for each SQL Server Version

    SQL Server Version

     DAC Feature

    2005

    2008

    2008 R2

    Extract DAC

    Yes

    Yes

    Yes

    Deploy DAC

    No <sup>1</sup>

    No <sup>1</sup>

    Yes

    Delete DAC

    No <sup>1</sup>

    No <sup>1</sup>

    Yes

    Register DAC

    No <sup>1</sup>

    No <sup>1</sup>

    Yes

    Upgrade DAC

    No <sup>1</sup>

    No <sup>1</sup>

    Yes

    Unpack DAC <sup>2</sup>

    N/A

    N/A

    N/A


     

    1 - Note that a SQL Server 2005 and 2008 patch will be required to perform these operations. The release date of patch is not confirmed, but the SQL 2008 patch will be the next SP (PCU), while the SQL 2005 patch will be via a CU for SQL 2005 SP3. On these SQL instances SSMS will not show a Data-tier Applications folder under the Management folder.

    2 - Unpack DAC is a Windows command shell integration. Users need to only install Management Tools (Basic/Complete) from SQL Server 2008 R2 to enable the command shell integration on their system.

    If you only have Visual Studio 10 (Dev 10) installed, then you will have the following behavior:

    ·         You will not be able to Unpack a DAC file. You can import and export a .dacpac file, but not unpack it.

    ·         There will be no file association between the .dacpac file to any program (i.e., you can’t right-click or double-click it to view it anywhere).

    ·         You can start Dev10, start a new Data-tier Application project, right-click the solution and the say Import Data-tier Application. In the wizard, you can then specify the location/path of the .dacpac file to load the DAC.


    Eric Burgess
    Microsoft SQL Server Beta Support
    Tuesday, January 12, 2010 11:11 PM
  • One update to Eric's post is that we actually do support extracting DACs from SQL Server 2000 instances.  Using SQL Server 2008 R2 tools, you can connect to downlevel instances (SQL Server 2000, 2005, or 2008) and extract a DAC.  Currently you can only deploy a DAC to an R2 instance, but as Eric mentioned, we are intending on releasing a patch for both SQL Server 2005 and 2008 to support the other DAC operations (Deploy, Delete, Register, & Upgrade). 

    Thanks,
    Sam Lester (MSFT)


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 15, 2010 6:40 PM
    Moderator
  • There is now a table at the bottom of this SQL Server 2008 R2 Books Online topic that specifies DAC support by version: http://msdn.microsoft.com/en-us/library/ee240739.aspx. As noted in the topic, for any version that supports a DAC operation, the operation is supported by all Editions in that version.

    As announced on the SQL Server 2008 SP2 CTP download page, SP2 will support all DAC operations. The topic I linked to above will be updated at the end of the month to reflect that. The CTP download page is at http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=65606fdd-093f-4c70-91f6-dc1f24520e8f.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, August 11, 2010 9:39 PM

All replies

  • thanks for the reply and sorry for the delay Bob. We'll get someone to look at them.
    Tuesday, January 12, 2010 10:15 PM
    Moderator
  • Hi Bob,

    Hopefully the following charts will help you with all your questions. With regards to SQL 2000, we don't support creating DACs from SQL 2000. It might work, but is not supported. Do you have a link for the VS 2010 B2 BOL where is states this?


    SKU and SQL Server Version Support for DAC

    All features of Data-tiered Applications are supported in all SQL 2008 R2 SKUs.

    ·         Data Center Edition

    ·         Developer Edition

    ·         Enterprise Edition

    ·         Evaluation Edition

    ·         Standard Edition

    ·         Standard for Small Business Edition

    ·         Web Edition

    ·         Work-group Edition

    Table 1 shows which DAC features are supported on each SQL Server version. 

    Table 1: DAC Feature Support for each SQL Server Version

    SQL Server Version

     DAC Feature

    2005

    2008

    2008 R2

    Extract DAC

    Yes

    Yes

    Yes

    Deploy DAC

    No <sup>1</sup>

    No <sup>1</sup>

    Yes

    Delete DAC

    No <sup>1</sup>

    No <sup>1</sup>

    Yes

    Register DAC

    No <sup>1</sup>

    No <sup>1</sup>

    Yes

    Upgrade DAC

    No <sup>1</sup>

    No <sup>1</sup>

    Yes

    Unpack DAC <sup>2</sup>

    N/A

    N/A

    N/A


     

    1 - Note that a SQL Server 2005 and 2008 patch will be required to perform these operations. The release date of patch is not confirmed, but the SQL 2008 patch will be the next SP (PCU), while the SQL 2005 patch will be via a CU for SQL 2005 SP3. On these SQL instances SSMS will not show a Data-tier Applications folder under the Management folder.

    2 - Unpack DAC is a Windows command shell integration. Users need to only install Management Tools (Basic/Complete) from SQL Server 2008 R2 to enable the command shell integration on their system.

    If you only have Visual Studio 10 (Dev 10) installed, then you will have the following behavior:

    ·         You will not be able to Unpack a DAC file. You can import and export a .dacpac file, but not unpack it.

    ·         There will be no file association between the .dacpac file to any program (i.e., you can’t right-click or double-click it to view it anywhere).

    ·         You can start Dev10, start a new Data-tier Application project, right-click the solution and the say Import Data-tier Application. In the wizard, you can then specify the location/path of the .dacpac file to load the DAC.


    Eric Burgess
    Microsoft SQL Server Beta Support
    Tuesday, January 12, 2010 11:11 PM
  • That's perfect Eric, thanks. The link to BOL that talks about 2000 support is here: http://msdn.microsoft.com/en-us/library/ee362013(VS.100).aspx

    Features Supported in Data-tier Applications<!---->
    <!--Content type: Devdiv1. Transform: orcas2mtps.xslt.-->
    [This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

    <!---->

    supports the most commonly used SQL Server Database Engine objects in a data-tier application (DAC).

    <!-- ApplyClick with current id -->  Supported Versions of SQL Server
    <!---->

    Data-tier applications can only be deployed to instances of the Database Engine running . A DAC can be extracted from a database in an instance of , SQL Server 2008, SQL Server 2005, and SQL Server 2000.

    Cheers,
    Bob

    Wednesday, January 13, 2010 12:20 AM
  • One update to Eric's post is that we actually do support extracting DACs from SQL Server 2000 instances.  Using SQL Server 2008 R2 tools, you can connect to downlevel instances (SQL Server 2000, 2005, or 2008) and extract a DAC.  Currently you can only deploy a DAC to an R2 instance, but as Eric mentioned, we are intending on releasing a patch for both SQL Server 2005 and 2008 to support the other DAC operations (Deploy, Delete, Register, & Upgrade). 

    Thanks,
    Sam Lester (MSFT)


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, January 15, 2010 6:40 PM
    Moderator
  • There is now a table at the bottom of this SQL Server 2008 R2 Books Online topic that specifies DAC support by version: http://msdn.microsoft.com/en-us/library/ee240739.aspx. As noted in the topic, for any version that supports a DAC operation, the operation is supported by all Editions in that version.

    As announced on the SQL Server 2008 SP2 CTP download page, SP2 will support all DAC operations. The topic I linked to above will be updated at the end of the month to reflect that. The CTP download page is at http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=65606fdd-093f-4c70-91f6-dc1f24520e8f.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, August 11, 2010 9:39 PM
  • Don't know if this will end up being a double, my earlier reply does not appear to have gone through.

    Now that SP2 has released, you can get the released version at http://www.microsoft.com/downloads/en/details.aspx?FamilyID=8fbfc1de-d25e-4790-88b5-7dda1f1d4e17.


    Alan Brewer [MSFT] SQL Server Documentation Team This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, January 12, 2011 9:38 PM