locked
Dacpac troubles RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello,

    We have SQL 2008 R2 and VS 2010 Premium to do to the database development. Our databases are SQL server 2008  project. We deploy them by schema comparing the projects to the actual database and then create a "change" script which we deploy. This is pretty time consuming.

    We are now "migrating" them to a data tier application (dacpac). The big advantage is that you only have one file to deploy. When I build a database I get a new dacpac.

    However, it  is does not build the dacpac when a post deployment file has changed. Anyway to fix this (except clean or rebuild) ?

    Is it possible to get date/time/machine name in the Version or Description ?

    When deploying the dacpac to a server, an administrator can lauch SQL Managent studio click on upgrade data tier/next /next finish. This generates a change script which can be executed immediately. One of the "bugs" is that it keeps dropping and adding calculated columns in tables all the time (this seem to be fix newer versions). However I want to automated it futher. The only way so far is powershell. I found a script on the internet that deploys a dac pac. (link : http://blogs.msdn.com/b/managingsql/archive/2010/04/28/dac-powershell-samples.aspx)

    However, the script does not deploy the post deployment script included in the dacpac. How can I fix this ?

    Finally the deployment process creates a new databases with the new structure. It copies the data from the old datbase to the new database and then renames it. If the database is huge it is not smart way to do it. Any way to make this deployment smarter ?

    Is it wise to update to Microsoft® SQL Server® Data-Tier Application Framework (June 2014) ? (link : http://www.microsoft.com/en-us/download/details.aspx?id=43370)  Are the dacpac generated by VS 2010 compatible with that version ? Do I need to install DACFramework.msi on all servers ? Can I still deploy the dacpac using SQL Managament tools v2008 or do I need then the SSDT from sql 2014 ?

    Thanks for your answers

    Constantijn Enders

    Wednesday, July 9, 2014 10:12 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote
    Firstly you definitely need to use the more recent DAC to solve some of your problems. For example here's an up to date post on using powershell with the newer DacServices API: http://www.systemcentercentral.com/deploying-sql-dacpac-t-sql-script-via-powershell/
    My recommendation would be to at a minimum download SSMS for SQL Server 2014. It's a free download, includes the latest Dac version and supports future Dac updates. This can deploy older dacpacs such as those created from the old VS2010 database projects, works with SQL 2008 and should unblock you on a number of the bugs you raised. Note that you only need to install Dac / SSMS on the client side as that's where it runs from. It doesn't have any server side component.
    Ideally you should use SSDT too, and upgrade your SQL 2008 project to an SSDT project. Once you do this, I'd search for examples of using msbuild targets in the .sqlproj file to set the Dac version / description at build time. I know this has come up on this forum before so other people have given solutions.
    Finally for huge databases import/export using Dac might not be the most performant option. If performance is your concern there are other options such as using .bak files. Kevin
    Saturday, July 12, 2014 6:23 AM