For those of you who are using DXC, you will probably quickly figure out that you have limited options when it comes to deploying to the integration environment database yourself. When you get access to the integration environment SQL you won't have delete/create database permissions. This makes sense as accidently deleting the database will incur costs and it can also mess up the reporting in the background.
I'm a bit of a continuous integration geek so on most of the projects I set up, I build a fully automated Ci/Cd process. Any manual steps are bad steps. For integration, I like to tear down and re-build the databases with test data. As DXC doesn't give you full fledge access to drop and re-create a database if you want to achieve a similar thing you'll be limited to executing commands within SQL. This approach will allow you to do everything you need, except it's a bit more long-winded compared to working with a normal Azure SQL database.
First, you should be aware that this article is a bit of a continuation from Wiping Your Episerver Database. That program works brilliantly on a local on-prem PC, however, when it comes to running the update.bat file against a DXC hosted web app you will encounter a fundamental issue. If you have no idea what the update.bat file is, then have a look here first. When you run update.bat you need to pass in the folder location of your webroot, like so:
Within DXC you won't have access to this folder. You can use a tool called Kudu that gives you access to an Azure web apps file system. This approach looks like it would work, however, after digging through the code within the epideploy.exe it was apparent that if you set it to run in SQL mode, you could fake this directory. Faking a directory is much easier! When I say fake directory, what I mean is that epideploy doesn't need access to your real webroot, all it needs is for you to add a folder that has a web.config in it. This web.config only needs an empty app settings section and a valid connection string that points to your integration environment database. Epideploy (the thing that update.bat calls) only need the connection string in order to run the update SQL scripts. So creating a fake webroot with an empty web.config will allow this tool it to do its magic, otherwise, it will crash and moan. An example of how this web.config should look is shown below:
You can now run update.bat file, like so:
Epideploy.exe will now happily run, create your database tables and apply any patches that are required :)
If you write your own custom clear database program and you want Octopus to trigger that program in your build you will need some PowerShell. When writing this PowerShell script, don't forget to set the PowerShell directory first. If you don't, when the batch file tries to load the scripts, it will look in the current home directory, which is usually Windows\\System32. For reference, a PowerShell script to run an exe could look like this:
There you have it, this process outlines how you can clean a database in DXC integration and then re-run the Episerver tables. This should give you more confidence that your site is working as expected! Happy Coding 🤘
