none
References in a DAC

    Question

  • I'm using VS 2010 to build a Data-Tier Application for a database. This database has references to tables in other databases, so the Build process generates warnings. I would like to add a reference to another database (either directly or through a .dbschema file), but the References node in the Project has only two options in the context sensitive menu: "Add master database" and "Add msdb database".

    I seem to recall that in regular Database Projects, you could add references to other projects/dbschema files. How do I do that in a DAC project?

    Also looking for any decent advise about planning this whole thing as a DAC project or a DB project. There looks to be a menu option to "Convert to SQL Server Database Project..." for a DAC project, but not an option on a DB project to "Convert to a Data-tier Application Project..." Why? Is Micrososft trying to funnel us into using Database Projects?

    Thanks


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Thursday, November 29, 2012 3:06 PM

Answers

  • I think with a DAC registered on a SQL instance, you can update it in place with a newly published .dacpac file and not have to worry about data loss (assuming the meta data changes support that). For instance, you can have  Table1 with columns A, B, and C. Then in the DAC project, have the following:

    CREATE Table1(
    ColA...,
    ColX...,
    ColB...,
    ColC...)

    and the upgrading of the Data-Tier Application with the newly built .dacpac file will actually leave you with a table with columns in that order.

    From what I can see, and please correct me if I'm wrong, using a SQL Server Database project you don't get that kind of flexibility. I have always had to script out all tables and then all the table population routines in Post-Deployment scripts. To then deploy that database project on top of the existing one, for me at least, required DROPPING the database and starting all over.

    I can assure you that you DO get that sort of flexibility with SQL Server Database Project, after all, the way they publish the schema is via the use of dacpacs thus if you can do it with a dacpac, you can do it with a SQL Server Database Project. I constantly use SQL Server Database Projects for the sort of scenario you describe.

    So now we're into the realm of my original post: "Also looking for any decent advise about planning this whole thing as a DAC project or a DB project"

    I would love to hear if it can be done any differently. For example, in VS 2008: New>>Project: Under Database Project, I see 4 types: SQL CLR, SQL Server 2000, SQL Server 2005, and SQL Server 2008. Then under "Other Project Types" is plain old "Database". What's the difference? Why two categories. In VS 2010 the choices are similarly confusing, with Data-Tier Applications thrown in.

    I agree, its very confusing. There seems to be lots of project types which are similarly and ambiguously named. All I can tell you is I use "SQL Server Database Project" and, well, it works for me!

    So, last question and we'll put this dialog to bed: do you know of any good resources (books, links, white papers, etc) on using SQL Server Database Projects (of any flavor)?

    Going to take the opportunity to blow my own trumpet here :) I blog about SSDT at http://sqlblog.com/blogs/jamie_thomson/archive/tags/SSDT/default.aspx
    Gert Drapers' blog is useful: http://blogs.msdn.com/b/gertd/
    Gert also has this site: http://sqlproj.com/ (as well as at least one other - I have no idea why he seems to spread his content over multiple places :))
    Jens Suessmeyer has a whitepaper which is a little old as it refers to Visual Studio Database Projects (i.e. in VS2012) rather than SSDT (which only came out less than 12 months ago) but I suspect is still very relevant: http://blogs.msdn.com/b/jenss/archive/2010/08/21/alm-visual-studio-database-projects-guidance-is-out-in-the-wild.aspx

    Hope that helps
    JT


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    • Marked as answer by Todd C Thursday, December 13, 2012 1:10 PM
    Thursday, December 13, 2012 9:42 AM

All replies

  • Hi Todd,

    Thank you for your question. 
    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated. 

    Thank you for your understanding and support.


    Maggie Luo
    TechNet Community Support


    Wednesday, December 05, 2012 3:45 PM
  • As I have discoverd, DAC projects are meant to be 'stand-alone' type databases. Therefore, there can be no 'cross-database' actions or references in the views, functions, stored procedures, etc. Therefore, no references to other (user) databases.


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Wednesday, December 12, 2012 6:41 PM
  • Hi Todd,

    For the purposes of this reply I am assuming that by DAC project you mean an SSDT database project,, the output of which is a .dacpac. If I'm wrong in this assumption please let me know.

    As I have discoverd, DAC projects are meant to be 'stand-alone' type databases. Therefore, there can be no 'cross-database' actions or references in the views, functions, stored procedures, etc. Therefore, no references to other (user) databases.


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    I don't think that is true. I am currently using SSDT to maintain 4 separate projects and there are multiple cross-database-references in there and it works just fine. I am able to deploy (aka publish) my dacpacs to SQL Server 2012.

    To answer your other questions:

    I seem to recall that in regular Database Projects, you could add references to other projects/dbschema files. How do I do that in a DAC project? Right-click on the "References" folder in your SSDT DB project and select 'Add database reference...'

    Convert to SQL Server Database Project..." for a DAC project, but not an option on a DB project to "Convert to a Data-tier Application Project..." I'm a little confused by your nomenclature here. The output of a SQL Server Database Project is a dacpac thus I would assume a "SQL Server Database Project" and a "Data-tier application project" to be one and the same.

    Hope that helps.

    JT


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Wednesday, December 12, 2012 7:23 PM
  • Jamie:

    Thanks for your response.

    To be clear, I am using Visual Studio 2010. I start a new project and for the type, select "Data-Tier Application". (DAC is the acronym for Data-Tier Application). By the way, you can also get there by selecting a database in SSMS and 'Registering' it as a Data-Tier Application. Then exporting the DAC to a .dacpac file, then importing that .dacpac file into Visual Studio, as a Data-Tier Application.

    In Visual Studio, if you right-click on a Data-Tier Application project, you have the option to "Convert to a SQL Server Database Project". By the way, that option also showed up for some other Projec type which really made no sense at all. I think it was maybe a VB or C# project. (I didn't know that Microsoft had a way to conver a VB or C# project to a SQL Database Project! :-)  )


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Wednesday, December 12, 2012 7:40 PM
  • OK, well I wasn't even aware that the "Database"->"SQL Server"->"SQL Server Data-tier Application" option was even there. I use "SQL Server"->"SQL Server Database Project" and frankly; having read the descriptions of both, I don't understand the difference between them both. Both output dacpacs (I know DAC=Data tier app BTW :) ), so I'm really interested to know what the difference between them is.

    Anyway, as I said before, I use "SQL Server"->"SQL Server Database Project" and have had a lot of success with it, including the use of cross-database-references.

    Regards
    Jamie


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Wednesday, December 12, 2012 10:05 PM
  • I think with a DAC registered on a SQL instance, you can update it in place with a newly published .dacpac file and not have to worry about data loss (assuming the meta data changes support that). For instance, you can have  Table1 with columns A, B, and C. Then in the DAC project, have the following:

    CREATE Table1(
    ColA...,
    ColX...,
    ColB...,
    ColC...)

    and the upgrading of the Data-Tier Application with the newly built .dacpac file will actually leave you with a table with columns in that order.

    From what I can see, and please correct me if I'm wrong, using a SQL Server Database project you don't get that kind of flexibility. I have always had to script out all tables and then all the table population routines in Post-Deployment scripts. To then deploy that database project on top of the existing one, for me at least, required DROPPING the database and starting all over.

    So now we're into the realm of my original post: "Also looking for any decent advise about planning this whole thing as a DAC project or a DB project"

    I would love to hear if it can be done any differently. For example, in VS 2008: New>>Project: Under Database Project, I see 4 types: SQL CLR, SQL Server 2000, SQL Server 2005, and SQL Server 2008. Then under "Other Project Types" is plain old "Database". What's the difference? Why two categories. In VS 2010 the choices are similarly confusing, with Data-Tier Applications thrown in.

    So, last question and we'll put this dialog to bed: do you know of any good resources (books, links, white papers, etc) on using SQL Server Database Projects (of any flavor)?

    Much appreciated.


    Todd C - MSCTS SQL Server 2005, MCITP BI Dev 2008 Please mark posts as answered where appropriate.

    Thursday, December 13, 2012 2:24 AM
  • I think with a DAC registered on a SQL instance, you can update it in place with a newly published .dacpac file and not have to worry about data loss (assuming the meta data changes support that). For instance, you can have  Table1 with columns A, B, and C. Then in the DAC project, have the following:

    CREATE Table1(
    ColA...,
    ColX...,
    ColB...,
    ColC...)

    and the upgrading of the Data-Tier Application with the newly built .dacpac file will actually leave you with a table with columns in that order.

    From what I can see, and please correct me if I'm wrong, using a SQL Server Database project you don't get that kind of flexibility. I have always had to script out all tables and then all the table population routines in Post-Deployment scripts. To then deploy that database project on top of the existing one, for me at least, required DROPPING the database and starting all over.

    I can assure you that you DO get that sort of flexibility with SQL Server Database Project, after all, the way they publish the schema is via the use of dacpacs thus if you can do it with a dacpac, you can do it with a SQL Server Database Project. I constantly use SQL Server Database Projects for the sort of scenario you describe.

    So now we're into the realm of my original post: "Also looking for any decent advise about planning this whole thing as a DAC project or a DB project"

    I would love to hear if it can be done any differently. For example, in VS 2008: New>>Project: Under Database Project, I see 4 types: SQL CLR, SQL Server 2000, SQL Server 2005, and SQL Server 2008. Then under "Other Project Types" is plain old "Database". What's the difference? Why two categories. In VS 2010 the choices are similarly confusing, with Data-Tier Applications thrown in.

    I agree, its very confusing. There seems to be lots of project types which are similarly and ambiguously named. All I can tell you is I use "SQL Server Database Project" and, well, it works for me!

    So, last question and we'll put this dialog to bed: do you know of any good resources (books, links, white papers, etc) on using SQL Server Database Projects (of any flavor)?

    Going to take the opportunity to blow my own trumpet here :) I blog about SSDT at http://sqlblog.com/blogs/jamie_thomson/archive/tags/SSDT/default.aspx
    Gert Drapers' blog is useful: http://blogs.msdn.com/b/gertd/
    Gert also has this site: http://sqlproj.com/ (as well as at least one other - I have no idea why he seems to spread his content over multiple places :))
    Jens Suessmeyer has a whitepaper which is a little old as it refers to Visual Studio Database Projects (i.e. in VS2012) rather than SSDT (which only came out less than 12 months ago) but I suspect is still very relevant: http://blogs.msdn.com/b/jenss/archive/2010/08/21/alm-visual-studio-database-projects-guidance-is-out-in-the-wild.aspx

    Hope that helps
    JT


    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    • Marked as answer by Todd C Thursday, December 13, 2012 1:10 PM
    Thursday, December 13, 2012 9:42 AM
  • Gert Drapers' blog is useful: http://blogs.msdn.com/b/gertd/
    Gert also has this site: http://sqlproj.com/ (as well as at least one other - I have no idea why he seems to spread his content over multiple places :))

    Jamie, main reason for having two blogs is that MSDN is a corporate Microsoft presence and http://sqlproj.com my private presence.

    As I am no longer associated with SQL Server or SQL Server Data Tools on a professional basis, I am only using and maintaining sqlproj.com.


    -GertD @ www.sqlproj.com

    Thursday, December 13, 2012 11:12 PM
    Moderator
  • As I am no longer associated with SQL Server or SQL Server Data Tools on a professional basis

    That's news to me!

    ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me
    Jamie Thomson

    Friday, December 14, 2012 11:16 AM