BI-Ops – 4/5 – Deploy DB, SSIS and SSAS (CD)

BI-Ops – 1/5 – What we are trying to do
BI-Ops – 2/5 – Source Control (Git)
BI-Ops – 3/5 – Build (CI)
BI-Ops – 4/5 – Deploy DB, SSIS and SSAS (CD)
BI-Ops – 5/5 – Deploy PowerBI (CD)

Now that we have an artifact that we can deploy, lets push it onto our target environments. In this first article I will cover how we do it for DBs, SSIS, SSAS and why we do it. I’ll cover PowerBI in a later article as there is no task available for the On Premise version of PowerBI Report Server that we use and we had to rely on a custom script.

Why automatic deployment?

Using Visual Studio, you only need to right click on the project, select “Publish” and you’re done, your DB is in production. So why bother?

I talked about the general philosophy of why we do the in the first article of the series but where it makes most sense for me is in the deployment.

Production control

The main reason is to control who has sysadmin rights in production. We don’t want to restrict people in the team to deploy in production. Everybody is senior enough for the project he is working on. Even juniors, as strange as it might sound. Taking on the responsibility to push in production is in my mind the best way for them to learn how to manage their project and their customer. If they screw up, we’ll help them recover and there is a good chance they won’t do it again. But how do we prevent them to not screw the WHOLE server, just their perimeter? We let robots publish for them.

It was also important for us to establish trust with sysadmins in charge of production infrastructure and that is what the essence of DevOps is. Sysadmins provide PaaS solutions to developers who become empowered and more efficient.

Repeatability

Have you ever forgotten one step in your deployment process? Can’t figure out what is wrong? The idea here is that once the pipeline is setup, it doesn’t change, it is self-documented and you know what is being done.

It also saves us a lot of time. Instead of deploying on two servers manually, making sure everything is done right, we just press a button, get approval and go get something to drink.

So how do we do it?

Well, again, Azure DevOps server. I mean, we paid for it so we might as well use the whole of it.

We create one pipeline per project to deploy its content. We don’t have one for our whole platform as we want to keep things manageable but it also means we need to make sure that we update dependencies when required. For example when we update our Referentials DWH, other DWH projects might need a small update to be in sync.

You can see we have two environments: UAT and production. We didn’t want to mulitply them too much for cost reasons. So let’s dive in.

The release pipeline

In itself the pipeline is pretty simple: one artifact previously built that we deploy onto two stages (=environments).

We do have a bit of manual validation throughout our process:

• UAT deployment is run when the pipeline is launched. Nothing particular there.
• UAT deployment needs to be validated once done. This is to make sure that we received validation from the business. Today this is done by a team member but in the future I would like for my Business Owner to come and click on “Approve”. Just so that he shares the responsibility with us and not be able to shrug it off saying “oh but, I didn’t test, I didn’t have time”.
• Production will trigger after UAT has been approved. It will however not launch, it needs to be manually triggered. We don’t want to deploy as soon as UAT is done. We at least need to communicate ahead of time.
• Once deployed, production also needs to be approved. Just to make sure, again, that somebody tested and approved what is now in production before we can say that the version is a success, and go on to the next one.

Here is what our production environment settings look like:

The trigger is set to “after the previous stage”, as shown from the line coming from the previous step on the visual.

We implemented a manual approval as said previously so that production deployment doesn’t start as soon as UAT is approved. Somebody from the team has to approve the deployment, and it can’t be the person who initiated the deployment. This allows a bit of cross-check, communication and planning from team members.
The deployment is rejected if not approved within 30 days.

The settings are the same for the UAT and Production stage validations.

Deployment steps

Now that we had discussed the two stages, lets dive into the details of one. I’m going to select Production. Our Production Data Warehouse is made up of two servers, part of an Availability Group with no listener and no underlying windows cluster. I’ll discuss why we decided this some other time but it does influence a bit the deployment.

Here is what de details of our stage look like

Deployment groups

We deploy using deployment groups. I see three main benefits to it rather than using a regular agent to deploy:

• It makes it easier to deploy things like our DBTools to all servers. Instructions are applied to all servers without having to create extra steps in the pipeline
• Each server has its own agent, running with a service account dedicated to the server and with proper access. Running a single deploy server would mean one service account would have remote admin access on all our environments. We’d rather not have that.
• Each server’s agent polls the Azure DevOps job queue. This means that from a network security point of view, the servers, whichever security zone they are in, contact the Azure DevOps frontend web server through https. We’d rather have flows going this way than from a dedicated server towards security zones hosting more sensitive data.

We have 4 different Deployment Group jobs in this stage. We use the same deployment group but use the tags to restrict actions to the proper servers. Here are the details of the first job.

As you can see, we just selected the PROD deployment group, and specified DWH1 to target the primary node on our Availability Group.

We will do the same for the secondary with tag DWH2 and then for the SSIS machine and the SSAS machine as you can see lower in the list of jobs.

Database deployment

I will detail our Deploy Views DB task but the Staging and Business tasks are very similar (simpler even).

We use DACPAC deployment to sync the DB’s current state with our new desired state, making SQL Server compare the objects and apply changes. This will however fail if data loss occurs, which is something we would need to plan fore with preDeployment.sql scripts in the project to prepare the DB and prevent such a loss.

The DACPAC is in the Artifact we built previously, in the DB folder. We just have to point to it.

We then select the Server connection mode as we are running the script on an agent located on the server itself. This is also why we target localhost with Windows Authentication.
We do not use a custom profile as for now, everything we do fits with the default profile.

For this particular views DB, unlike staging and business DBs, we need to reference another DB on which the views are pointing. This is why we added the argument shown on the screenshot to set the variable we have in our DACPAC.

With all these steps, we are done deploying one database and just repeat the same thing for the other two.

On the secondary node, we only replicate the Business DB as it holds the data. We don’t replicate the staging one as it hold nothing and if we need to have it on that node, we’d deploy it from code in this pipeline. Same for the views DB which is the only DB we are deploying here, pointing to the Read-Only replica of the Business DB on that secondary node.
We also decided not to script adding the Business DB to the AG. We do that once upon creation of the DB and all further changes are replicated when we deploy. I’ll detail all this in another post anout our architecture to make it clear and explain our choices. And probably get improvement suggestions from you all.

We have a slight difference in our UAT tasks. We don’t have a secondary and we don’t want out databases in Recovery Full on that server because we don’t run backups on it. As our Business database hold the data and is replicated in our AG, we need it to be with a Recovery Full option. We don’t want to mess this up so we set the option in the project, but need to modify the DB post-deployment in UAT. So we do this in the pipeline by adding a Deploy DB step with the following options. As you can see, its a simple ALTER DATABASE statement running as Inline SQL.

SSIS Deployment

Now that we have deployed our DataWarehouse using our pipeline, lets look into how we deploy our ETL and our Cubes to keep our whole environment coherent at every release.

Like for building our SSIS package, we use the free Microsoft SSIS tasks from the Azure DevOps Marketplace.

We created a deployment group job and restrict it only to the SSIS server using the proper tag. And on that job we simply add the deployment task.

As for the DB, we point to the proper deployment .ispac file in our artifact.

We use SSISDB on that dedicated server to deploy so we select that and as the agent is on the server we target localhost.

The destination path points to the project folder in the SSISDB. The project will be created if it doesn’t already exist.
We say Yes to overwrite in order to update previous versions of the project with the new deployment. If we don’t do this, existing projects will just be skipped. This kinds of defeats the purpose here.

And that is it!

We use another task in another pipeline to deploy all our system variables to SSIS Environments so we don’t have to update those every time and reduce the risk of error.
We select to manually set the variables using SSMS after the first deployment, linking packages to environments. But once this is done and we don’t add a new source or target to our pipeline, it doesn’t need updating on each release.

SSAS Deployment

To deploy our SSAS cubes, we rely on the SSAS Tabular Cube Deployment Tools extension from John Tunnicliffe that is available for free on the Azure DevOps Marketplace.

As for SSIS, we have a dedicated job pointing to the SSAS production server for its agent to executed the tasks.

Deploy

First we deploy the cube itself

As for SSIS, we select the source in the artifact, here the .asdatabase file.

We select the localhost server, and the name of the Cube DB to deploy to.

As for builds, we select the latest version of the deployment tools. The task requires SSMS 17.x to be deployed on the server to run.

We redeploy all partitions from the project.

We redeploy roles as well in case we added some but we retain the members. We add those manually as we don’t want to have to redeploy every time we have to add a new group of users.

As for partitions, we redeploy configuration and optimisation settings from the project.

Proces

Now that the cube is deployed, we need to process it for it to be ready for users to query.

The task is pretty straightforward here. We just select the server, the database and process.

We select Full processing after deployments to make sure everything is in sync.

Success

Once we have set all our steps we then just have to run our pipeline and everything should be ready for our users to query and analyse.

As you can see, Production was approved by my unicorn-loving colleague after she checked with end users that everything was going according to plan.

If you try this and struggle with your first deployment, don’t worry. It always take a bit of tweaking to get all the parameters and variables right. Even for us after years of experimenting. But as in the details of the stages and jobs we have access to all the execution logs from each agent it makes it quick to diagnose issues and fix them.

Once we’re set, its just develop, commit, PR, build and release. Oh, yeah, and document 😎