Home → Problems →

Building MS SQL Server SSDT .dacpac & Deploy to Azure

• Edit

John Carroll

22 Oct, 2014 02:10 PM

Hi,
Does AppVeyor support building of SSDT projects?
Reading the following (http://sqlblog.com/blogs/jamie_thomson/archive/2014/08/08/want-a-headless-build-server-for-ssdt-without-installing-visual-studio-you-re-out-of-luck.aspx) I believe this is possible if Visual Studio & SSDT is installed on the build server.

We have SSDT projects as part of our Azure cloud solution. We can build & deploy the cloud services to Azure when we commit changes to BitBucket...this works very well. However, will Appveyor allow us to build and deploy our SSDT project?

Kind regards
John

1. 1 Posted by gep13 on 22 Oct, 2014 03:31 PM

   

   There are some versions of Visual Studio installed on the AppVeyor Worker image, as described here:

   http://www.appveyor.com/docs/installed-software

   Do these versions support the project type you are looking for?

   Gary

   • Edit

2. 2 Posted by John Carroll on 22 Oct, 2014 03:38 PM

   

   Thanks for the reply Gary, yeah...it looks like Visual Studio Express 2013 includes the SSDT build components which is good news.
   I come across this message: http://help.appveyor.com/discussions/questions/589-database-project-publishing which implies that building SSDT projects is OK, however deploying (to Azure in my case) appears to be an issue.
   I'll have a look into it and see if I can resolve the deployment side of things

   Thanks
   John

   • Edit

3. Support Staff 3 Posted by Feodor Fitsner on 22 Oct, 2014 05:46 PM

   

   Hi John,

   Yes, SSDT projects are supported at AppVeyor and you can produce .dacpac file. This is a sample project with .dacpac in artifacts: https://ci.appveyor.com/project/appvyr/test/build/1.0.2

   There is no issue with deploying SSDT (yet), but we'd like to integrate syncing of database changes into AppVeyor. To do that I'd like to better understand your current scenario - how do you deploy database changes right now and what would be ideal way of doing this? Let's discuss.

   • Edit

4. 4 Posted by John Carroll on 23 Oct, 2014 10:01 AM

   

   Hi Feodor,

   At the moment we only have one Solution which contains Azure Cloud Services / Worker roles. We push changes to BitBucket and we have configured Appveyor to build our multiple project solution but not deploy automatically. We deploy our cloud services to Azure manually from AppVeyor...this all works great for our cloud services. On the Database side, we are manually creating these and running scripts when necessary etc...

   Moving forward, we are integrating SSDT projects into our solution and would like to automate the building and deployment of our database changes via Appveyor to the relevant SQL databases on Azure.

   The plan is to move away from a single Repo and create multiple Repos for our solution / project (micro-service) & SSDT project.
   For our test environment in Azure, we would like to automatically build and deploy all changes (Cloud Services & SSDT projects - DB changes).
   For our live system in Azure, we want to build automatically and deploy manually via the usual button / link in Appveyor.

   We currently have the cloud service side of things building and deploying to the staging area in Azure. We are just trying to figure out how we build and deploy a SSDT (DB changes) to a SQL database in Azure via Appveyor?

   Kind regards
   John

   • Edit

5. Support Staff 5 Posted by Feodor Fitsner on 24 Oct, 2014 06:53 PM

   

   OK, great! I understand your scenario.

   So, SSDT project produces .dacpac file containing database schema and other scripts. You can put resulting dacpac file(s) into build artifacts. It's already working today, but it's half of the problem.

   I think we need another deployment provider called, say, SQL Database which accepts .dacpac artifact as a source and SQL Server connection string as destination. This provider will be available during the build and under Environments section. For syncing databases we'd use dbDacFx Web Deploy provider, so there maybe some additional settings controlling its behavior.

   Also, in case SQL Server instance is behind a firewall we'd allow specifying remote Web Deploy connection details.

   Does it sounds as a solution for you?

   Relevant links:
   http://www.iis.net/learn/publish/using-web-deploy/dbdacfx-provider-...
   http://msdn.microsoft.com/en-us/library/hh550081%28v=vs.103%29.aspx
   http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dac.dac...
   http://stackoverflow.com/questions/20972104/right-parameter-setting...
   http://stackoverflow.com/questions/26248219/msdeploy-3-and-dbdacfx-...
   http://stackoverflow.com/questions/22324576/why-is-my-msdeploy-comm...

   • Edit

6. 6 Posted by John Carroll on 27 Oct, 2014 11:39 AM

   

   Hi Feodor,

   Thanks for the reply, what you have proposed sounds like a potential solution!

   Before going down that road can you please look at the following links:

   http://www.tuicool.com/articles/uqaEZz

   http://sqlblog.com/blogs/jamie_thomson/archive/2013/01/27/continuous-deployment-of-ssdt-database-projects-to-windows-azure-using-team-foundation-service.aspx

   The first link uses MSBuild and the Azure *.Publish.xml file to do the deployment...maybe this is an alternative option?

   The second link shows an example of what we are trying to achieve albeit using TFS...we are using BitBucket / Git / AppVeyor, however the idea is similar.

   I'll try the MSBuild option today and see how far I get.

   Kind regards
   John

   • Edit

7. Support Staff 7 Posted by Feodor Fitsner on 28 Oct, 2014 04:20 AM

   

   Whoa, both solutions are really awfully complex! :)

   I see at least the following issues with both approaches:

   1. Storing database publishing information in XML and thus in repository.
      
   2. Database publishing (syncing) is only as part of the build. You can't publish it to a different location else without re-building.
      
   3. As a result of #2 there is no following to "build once deploy anywhere" principle.

   AppVeyor approach would be:

   1. Build solution and place .dacpac to artifacts. Also, looking at comments to the second article there is no ability to publish multiple SSDT projects or a solution with multiple projects and SSDT. With AppVeyor you'll be able to push .dacpac files from all projects.
      
   2. Setup "SQL Database" deployment step (to run as part of a build) or new "SQL Database" environment. Specify connection string for the target database, Web Deploy connection details, syncing options (like prevent data loss) and artifact name to deploy.
      
   3. From Environments deploy the same .dacpac multiple times to multiple environments.
      
   4. ...and you can use environment variables in deployment provider settings like everywhere else.

   It can't be that easy - I must be missing something :)

   Btw, we've got an early prototype of "SQL Database" provider that is based on dbDacFx Web Deploy. Let me know if you want to be a beta tester when it's ready!

   • Edit

8. 8 Posted by John Carroll on 31 Oct, 2014 09:58 AM

   

   Hi Feodor,

   The solution I am trying also appears to be very simple and this works great on my local machine. However, when deploying to Azure Sql database, I get a connection error even though the server, database, username and password are correct. The project I have set up in AppVeyor is called LivSmarter.Analytics.Database, I have attached a build output screenshot if you could spare two minutes taking a look.

   Given it works local on my machine, I wonder if an ip-address for Appveyor needs adding to the whitelist on SQL Azure?

   All I am trying to do is publish my SSDT SQL project to an Azure Sql database.

   Kind regards
   John

   • LivSmarter.Analytics.Database.png 211 KB
   • Edit

9. Support Staff 9 Posted by Feodor Fitsner on 31 Oct, 2014 04:47 PM

   

   Yes, you should add AppVeyor worker IPs to SQL Azure database firewall: http://www.appveyor.com/docs/build-configuration#build-environment

   • Edit

10. Support Staff 10 Posted by Feodor Fitsner on 12 Dec, 2014 03:52 AM

    

    New "SQL Database" deployment provider for publishing SSDT package to local/remote SQL Server and Azure SQL Database: http://www.appveyor.com/docs/deployment/sql-database-ssdt

    • Edit

11. Ilya Finkelshteyn closed this discussion on 25 Aug, 2018 01:50 AM.