Installing/updating a MSSQL server database with a dacpac - Advanced Installer Community
Fulgan
Posts: 101
Joined: Mon Feb 27, 2012 10:42 am

Installing/updating a MSSQL server database with a dacpac

Hello,

One step that is currently missing from my installer is the creation of the database itself. That database is deployed as a data-tier application package (dacpac) which makes it very easy to handle schema update.

Unfortunately, i can't find a way to deploy or update that database from within advanced installer.

Something else: apparently, I can't find any solid notion of upgrade: every install seems to be a complete uninstall/reinstall of the old one, just keeping the basic choices. That, of course, doesn't work when a specific step - like updating the database schema - has to be done differently on install or on update. Is there a way around this or am I forced to create a separate utility for that ?
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: Installing/updating a MSSQL server database with a dacpa

Hello,
One step that is currently missing from my installer is the creation of the database itself. That database is deployed as a data-tier application package (dacpac) which makes it very easy to handle schema update.

Unfortunately, i can't find a way to deploy or update that database from within advanced installer.
In order to achieve this you can use our "SQL Databases" support. Please take a look on our "Using SQL Scripts" tutorial.
Something else: apparently, I can't find any solid notion of upgrade: every install seems to be a complete uninstall/reinstall of the old one, just keeping the basic choices. That, of course, doesn't work when a specific step - like updating the database schema - has to be done differently on install or on update. Is there a way around this or am I forced to create a separate utility for that ?
I'm afraid I don't fully understand what you mean. Can you please give us more details about this (maybe exemplify)?

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
Fulgan
Posts: 101
Joined: Mon Feb 27, 2012 10:42 am

Re: Installing/updating a MSSQL server database with a dacpa

Hello,

Thank you for your answer.

For the first part of my question, I do not want to use SQL script to maintain my database schema. Rather, I would like to use MS's dacpac system: these are XML documents describing the expected database schema. The local tool then compares that schema with the existing DB and performs the necessary adjustment. It allows for easy update of the schema no matter what the original state the database was in (or deploying a new one).

For the second part of the question, here are the two scenarii that causes me trouble:

First scenario: database schema and data
- User install product version 1. This will setup some database using schema version 1
- User adds data to the database.
- Users wants to update to version 1.1 which requires a schema update. Since it's a reinstall, the second step of the installation (install) should know that it should update existing data and not setup a new database.

Second scenario: XML config file
- User install products which includes an XML file containing some configuration data.
- Program runs and updates this XML file
- User updates to version 1.1. This will delete the XML file (during uninstall) and then recreate it (with default values).

I hope it is more clear.
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: Installing/updating a MSSQL server database with a dacpa

Hello,

I apologize for my delayed reply.
For the first part of my question, I do not want to use SQL script to maintain my database schema. Rather, I would like to use MS's dacpac system: these are XML documents describing the expected database schema. The local tool then compares that schema with the existing DB and performs the necessary adjustment. It allows for easy update of the schema no matter what the original state the database was in (or deploying a new one).
For this you can simply add your .dacpac file under the desired directory path from "Files and Folders" page. Also, if you need to call a specific tool (e.g. SqlPackage.exe) to deploy or update your database schema then you can use a custom action (it should be added as deferred after the "Install Execution Stage -> Add Resources" actions group). For example you can use our "Launch File" predefined custom action.
First scenario: database schema and data
- User install product version 1. This will setup some database using schema version 1
- User adds data to the database.
- Users wants to update to version 1.1 which requires a schema update. Since it's a reinstall, the second step of the installation (install) should know that it should update existing data and not setup a new database.
Please keep in mind this is the normal behavior when using the "Uninstall old version first and then install new version" option from "Upgrades" page. In this case, during an upgrade installation first it will be removed the old version of your product (during "RemoveExistingProducts" standard action) and afterwards it will be installed the new version. In order to preserve an installed file you should use the approach exposed in our "Prevent Repair or Uninstall" how to or you can use the "Install new version first and then uninstall old version" upgrade order.
Second scenario: XML config file
- User install products which includes an XML file containing some configuration data.
- Program runs and updates this XML file
- User updates to version 1.1. This will delete the XML file (during uninstall) and then recreate it (with default values).
This is the normal behavior when using the "Uninstall old version first and then install new version" upgrade option. As a workaround you can mark as "Permanent" the component of your XML file.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
Hotzi
Posts: 5
Joined: Thu Oct 02, 2014 5:57 am

Re: Installing/updating a MSSQL server database with a dacpa

Hi,

i also want to install a DACPAC file via SQLPackage.exe
After Installation the database i want to execute a SQL-Script.

For this you can simply add your .dacpac file under the desired directory path from "Files and Folders" page. Also, if you need to call a specific tool (e.g. SqlPackage.exe) to deploy or update your database schema then you can use a custom action (it should be added as deferred after the "Install Execution Stage -> Add Resources" actions group). For example you can use our "Launch File" predefined custom action.

So what is the right way?
My approach:
1. Add the DACPAC File unter Temporary in the "Files and Folder" Section
2. Add my SQL-Script under the section "SQL Scripts" (the script should be executed after the deployment via SqlPackage.exe)
3. Add the SQLPackage.exe execution with Parameters under "Launch file or open URL"
=> How can I check the return-value?

Currently the database is not created yet.
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: Installing/updating a MSSQL server database with a dacpa

Hello,

All the SQl scripts added in "SQL Databases" page (formerly "SQL Scripts" page in older AI versions) are executed during the "Install Execution Stage -> Add Resources" actions group. Therefore in order the database to be created before the SQl scripts are executed, your "SQLPackage.exe" custom action should be scheduled before the "Install Execution Stage -> Add Resources" actions group.

Also, in order to check the return value of a custom action you can create a verbose log of the installation and search for the related action return value. As another solution you can create your own custom action which will launch the "SQLPackage.exe" and check the return value or save it into an installer property.

If you have any questions just let us know.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube
Hotzi
Posts: 5
Joined: Thu Oct 02, 2014 5:57 am

Re: Installing/updating a MSSQL server database with a dacpa

Hi Daniel,

thanks for your answer.

Ok, i have added the SQL-Script in the SQL Scripts page:
SQL Script.jpg
SQL Script.jpg (17.86 KiB) Viewed 13788 times
under the File Section i have add the DACPAC file, which i would deploy with the SQLPackage.exe:
DACPAC to Publish.jpg
DACPAC to Publish.jpg (47.12 KiB) Viewed 13788 times
and under the Custom Actions I have add to execute the SQLPackage.exe with the dacpac file as Parameter:
LaunchFile.jpg
LaunchFile.jpg (175.45 KiB) Viewed 13788 times
Following questions:
- How can I check a return value of the SQLPackage call (break when special return code)
- How can I ensure that the DACPAC file (under the File-Section) is copied before the execution of Sqlpackage.exe
(but the SQL script should be executed after the SQLPackage call)

Thanks.
Daniel
Posts: 8283
Joined: Mon Apr 02, 2012 1:11 pm
Contact: Website

Re: Installing/updating a MSSQL server database with a dacpa

Hello,
- How can I check a return value of the SQLPackage call (break when special return code)
Just run the setup package with a command line which will create a verbose log file. Then, after the installation is finished search in the created log file the return value of the "LaunchFileOrURL" action. Please take a look on the "Logging of Action Return Values" article which should be helpful.
- How can I ensure that the DACPAC file (under the File-Section) is copied before the execution of Sqlpackage.exe
(but the SQL script should be executed after the SQLPackage call)
You should add the DACPAC file as a temporary file in "Files and Folders" page, by using the [Add Temporary Files] toolbar button. Also, your "LaunchFileOrURL" action should be scheduled before the "Install Execution Stage -> Add Resources" actions group.

Let us know if this helped.

All the best,
Daniel
Daniel Radu - Advanced Installer Team
Follow us: Twitter - Facebook - YouTube

Return to “Common Problems”