Azure SQL Database Dev to Production Part 4

I have had quite a lot of issues in regards to the whole dev to prod process with the SQL database. my last attempt which I wrote in this blog worked well until I shut the project down. Then once reopened I would always lose my Project or GIT, so I went back to the drawing board and did a lot more research and here are my new findings.

There is a Data Factory part to this but I have already made a blog about this and it has consistently worked ever since setting up the dev to prod process

Resources used

  • Azure SQL Database and Server
  • Visual Studio (Enterprise)
  • Azure DevOps

Azure Devops Repository

First of all you need to have an Azure Devops set up (I wont go into detail on this here)

In the Devops repos I have a Folder for Data Factory in the repository. The Folder for SQLDB will be created later.

  • In Devops Ensure you have a GIT repos Created Then Click CLONE to copy the GIT location URL

In this example I am cloning right at the top of the repository

  • Click the Copy Button

Visual Studio

You cannot do this as yet in Visual Studio code. It has to be Visual Studio and I have Visual Studio Enterprise 2019

  • You can Copy the Repository Location from the Clone Copy (Or Browse a Repository – Azure DevOps or GitHub)
  • make sure you are happy with the path for the Local Copy
  • Click Clone. Your Local repository is then shows in Solution Explorer
  • This has added the folder on your C Drive (It added the top level and the dataFactory and SQL DB Folders)
  • And You can see this project in Solution Explorer
  • Copy path of C Drive (And the folder for example SQLDB)
  • Visual Studio – In the top bar choose create File – New – Project
  • Choose SQL Server Database Project – Next

The Project with be SQLDB and will contain the SQL Objects

  • Click Create

In Visual Studio SOLUTION EXPLORER: You can see your empty database objects.

On the C Drive, Note you now have a SQLDB folder along with the Data Factory Folder

  • Right click on the database name in Solution Explorer and go to properties

Its important to be in the right version for the target platform

  • Right click on database name and Import – database
  • Select the Connection location of the development database (and Show connection properties to make sure your username and password are ok and the database connects.
  • Import objects into the Local Project (No need to Select it in the above box)
  • then Click Finish Note that all your objects are now in Solution Explorer and on the C: Drive (Your local copy)
  • Is the project Complete? – Build – Rebuild solution which checks and validates the objects

Any time anything changes you need to rebuild your solution to update the code.

Warnings and Errors

For Warning and errors you can see all the issues by clicking on them. The build may fail because of errors. These always need resolving before you send to the target DB e.g. Production

Error Example Warning:  SQL71558: The object reference [staging].[].[KEY] differs only by case from the object definition [staging].[ST2].[Key].

  • Click click on the database in Solution Explorer and go to Properties.
  • In project Settings untick validate casing on identities

Error Example Warning SQL71502: Procedure: [dim].[USP_Date] has an unresolved reference to object [sys].[all_objects].

You can add the master database as a reference (Right Click on references)

Add Database Reference

Rebuild your codebase. Its important here to make sure your warnings and errors have been dealt with

Rebuild updates your project locally after updating – I will look at how making changes with for example SQL Server Management Studio changes the process in a later blog.

  • Publish to Git Repository – GIT Changes and make a note of your change Commit All

Then click the arrow to Push changes to the GIT repository

We now have the code in the repository in DevOps

It seems annoyingly easy to slightly mess your folder structure up. Here I have a SQL DB Folder and another SQLDB Folder inside.

I only wanted the one. This does keep happening to me and its very frustrating. Any pointers to where I went wrong would be really appreciated

Create your CI (Continuous Integration) Pipeline in Azure Devops

Now we have the Code in GIT we can create our artifacts for the release pipeline.

  • In Azure Devops go to Pipelines and Release Pipelines
  • Click New Pipeline
  • And choose your repository
  • Select a template

Right click on tasks and remove selected tasks until you are left with the following

You don’t really have to do much with these three jobs

At the Pipeline level ensure you use the right Agent. For example Windows -2019. We had errors because we use an  OpenJson function in the SQL code but setting the right agent resolved this issue

All the other jobs are parameterised. This should now be all set

  • Save and Queue and you can then run the pipeline to create your artifacts Save and Run
  • There may be warnings here. For some reason the warnings you clear in Visual Studio seem to show in Devops. I would like to do a bit more research on this.
  • But If a warning hasn’t failed the process you should now have your Continuous Integration artifacts.

Create your CD (Continuous Delivery) Release Pipeline in Azure Devops

Now we are onto Continuous Delivery. Moving the new code into Azure SQL DB

  • In Azure Devops to to Pipelines – Releases – New release
  • So for Artifacts click Add

We want to use the latest build

now Add a Stage. In our case we are using Prod so its a simple release

  • Start with an Empty Job

Add a task to the job

Here we chose the production Subscription

And we link to the DACPAC file that was created with the build from visual Studio. The DACPAC contains all the objects in SQL

The database uses variables and you can set these up in the variables tab

You can create a release to update your Production database

Once pushed, check your SQL database in Production to make sure you are happy that your changes have gone through.

And you can save your visual studio project and reopen. the next stage is to update some objects and go through the process again so watch this space

Power BI Premium Deployment Pipelines and Sub Workspaces – Publishing a live report to a pro workspace from a premium workspace

I wanted to have a look at a specific set up we have in regards to our Power BI Workspaces and apps

  1. We have Power BI Premium P1 Node
  2. We use dataflows
  3. We use a Deployment Pipeline for Dev Test and Production Premium Workspaces
  4. We have a scenario where we have a sub workspace with a smaller number of reports than the main reports

Deployment Pipelines

Lets have a look at the master Workspace that contains all our dataflows, datasets and reports live connected to the dataset

And for all three areas there is also an app to test usability for Viewers (and for the viewers of all the reports in Prod

Current issues with Deployment Pipelines and dataflows

The great thing about pipelines is that we don’t have to keep copies of Test and Prod reports. There are just Dev Pbix files. However, for Production. How do you know that your Production reports are using the dataflows connected to the Production SQL database?

The issue at the moment is that there is a bug that leaves the development data source in with the Production data source for the data flow. You can see this if you go into Lineage View

In the Prod Pipeline we have set the reports to use Prod dataflow and updated the user and password but from here how do you know that this has worked?

The best way I have found to do this is to export the dataflow Json … Export to Json

And look at the file in Notepad and check the source Source = Sql.Database(\”prd-

Its showing as production so I can be happy that the production reports are against the production database

The Sub Workspace

So we have a model for a production app to sit on where users can view all the reports.

However we have another cohort who can only see the reports B and not report A or C.

We can only have one app per workspace so we cant create another app with just one report.

The answer here is to create a sub workspace and republish the Production report across to this new workspace.

The question here is….. Can the sub workspace be a none premium workspace if we are live connected to a dataset in a Premium workspace?

Lets find out by first assessing the situation we want to set up

Process to move the production reports in the deployment pipeline to another workspace

We want to publish the production report to a new workspace (With just the one report in it)

And as a further complication, We want to live connect to a dataset in a Premium workspace and recreate a report in a Pro Workspace.

Not only that, but the Pbix file is set to dev not Prod and there is no production pbix file.

First of all in your Production Workspace go to your report (content) Click … and Save a copy

And Here is the Lineage in Power BI

So how do we check that this report is connected to Dev data flow not prod dataflow

At this point I would probably recommend having something in SQL to connect to like a view or a table containing the datasource name to test with. This would be really handy.

We have to assume that this is the case because it comes from Production workspace which is definitely connected to Prod its good. Also, You could check the data between dev and Prod at this point too.

To test, an app was published and the data was exactly the same as the report in the master workspace.

The good thing about this set up, you only have one copy of the data in master. You don’t have to have the same data duplicated in another workspace, taking up more space in the Power BI Premium Node.

And because its such a small workspace, we don’t have to create it in Premium so we don’t need our Power BI Admin to set it up as a Premium workspace

Licensing

Now you have to think about Licensing. Everyone who needs access to the report in the Pro workspace now needs a Pro account themselves. Great if you have a P3 License which gives everyone free Pro accounts.

But what if you don’t have everyone on Power BI Pro licenses. Or you have external users and you haven’t given them a Pro license?

Lots to think about here with this Mix of Premium and Pro. this might then be a case of going to your Power BI Admin and asking them to switch the Workspace to Premium simply due to the Licenses against the report viewers.

Create your website with WordPress.com
Get started