Data Factory, Moving multiple lookup worksheets from Excel to one lookup table in SQL Server

A current project has an xlsx containing around 40 lookups in individual worksheets

Each worksheet consists of a code and a description

We decide that we want every single lookup in one lookups table in SQL Server.

This will have a Lookup Name, Code and Description that we can then use for the rest of the project

We want to do everything in one go in Data Factory.

For this Im going to use a simple example with just 3 worksheets

Azure Data Lake Gen 2

We are going to store the source data within a data lake.

The Source data looks like this

Lookup B worksheet

Lookup C Worksheet

SQL Server

I have an Azure SQL Database and on it I create the one table that all the reference lookups will go into

GO
CREATE TABLE [staging].[Lookups](
[LabelKey] [int] IDENTITY(1,1) NOT NULL,
[LabelName] varchar NULL,
[Code] [int] NULL,
[LabelDescr] varchar NULL,
[Importdate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [staging].[Lookups] ADD DEFAULT (getdate()) FOR [Importdate]
GO

LabelKey has been added just to create a valid key for the table. LabelName has also been added which will be the name of the worksheet.

Finally ImportDate is added because we want to know exactly what time this data was imported into the table

Now we need to provide Data Factory with a list of worksheets

CREATE TABLE [staging].[LookupNames](
[LabelKey] [int] IDENTITY(1,1) NOT NULL,
[Labels] varchar NULL,
[Importdate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [staging].[LookupNames] ADD DEFAULT (getdate()) FOR [Importdate]
GO

Lookup Names is our seed table and will provide us with the worksheet names

we have populated it like this

SELECT 'Lookup A' UNION
SELECT 'Lookup B' UNION
SELECT 'Lookup C' 

Data Factory

Linked Services

Firstly we need to provide our linked services. Source and destination

go to Linked services via

and choose new.

call it ADLS_LS and select your Azure Subscription and Storage account.

At this point the connection was tested and was successful so we didn’t need to do anything further

Next, create your Azure SQL Database Linked Service

And call is SQLDB_LS (Or what ever you feel is the right naming convention. _LS is good because you can see exactly what are the linked services in the JSON script created

Again add in your details (We used a role that we created in SQL Server DB specifically for data factory with GRANT EXEC, SELECT, INSERT, UPDATE, DELETE on all the schemas)

Ensure the connection is successful

Data Sets

Now to come up with the actual source and destination datasets. If we parameterise them then we can reuse a single data set for lots of other activities within the pipeline

Click on the and choose New dataset

Choose the Format. In this case its Excel

We don’t want to specify any of the location values until we get to the pipeline, including the worksheet

Make sure First row as header is ticked (Unless you don’t have a header in Excel)

And create parameters

This means we can use this one Data set for all the SQL data sources

Pipelines

Now to create the pipeline specifically for the lookup

This is the basic pipeline we are going to add.

Lookup

First of all In Activities search for lookup and drag this into the pane

This uses the SQL dataset because we are going to use our SQL table that contains all the names of the worksheets.

Note that first row only is not ticked because we are bringing all the information through

ForEach

@activity(‘GetLookups’).output.Value

We are going to get the entire data set (Value) fed into the GetLookups Lookup.

Sequential is ticked because we are going to move through all the worksheets names in the table (Ensure that your Worksheets have exactly the same name as what is specified in your table)

Click on the Activities (1) to get to the activity

Copy Activity within the Foreach

We now set up the source of the copy activity

We use all the parameters within the dataset and add in the information from our Azure data Lake Gen 2 in the Storage Resource.

Within our Lookups table there is a column called labelname and we are going to populate this with the Labels column from our item. Out Item in the foreach loop and was created via the Lookup. And that lookup contained all the columns from our LookupNames SQL Table

The data will go into the Lookups table

Thats everything. You should be able to test your Pipeline by clicking debug and the Foreach should move through worksheet specified within your lookupnames table and add your information into SQL

Truncating lookup tables before re adding data

we want to be able to repeat this process and unless we add a truncate statement into our process we will keep readding the same information

We can add the following Stored procedure into SQL

/*
05/10/2020 Debbie Edwards - Peak - Truncate lookups
EXEC [staging].[USP_Truncatelookups]
*/
Create PROCEDURE [staging].[USP_Truncatelookups]
AS
BEGIN
IF EXISTS(SELECT * FROM [dbo].[sysobjects] WHERE Name = 'lookups')
TRUNCATE TABLE [staging].[Lookups]
DBCC CHECKIDENT ('Staging.Lookups', RESEED, 1)
END

And this can be added to the the Pipeline before the foreach loop and Lookup with a Stored Procedure Activity

You wont be able to see the Stored procedure if you havent granted EXEC access to the specific Database Role name and schema

Always give the least amount of privileges and them move up if you need to

--Bring back information about the members in your roles
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;
SELECT DISTINCT rp.name,
ObjectType = rp.type_desc,
PermissionType = pm.class_desc,
pm.permission_name,
pm.state_desc,
ObjectType = CASE
WHEN obj.type_desc IS NULL
OR obj.type_desc = 'SYSTEM_TABLE' THEN
pm.class_desc
ELSE obj.type_desc
END,
s.Name as SchemaName,
[ObjectName] = Isnull(ss.name, Object_name(pm.major_id))
FROM sys.database_principals rp
INNER JOIN sys.database_permissions pm
ON pm.grantee_principal_id = rp.principal_id
LEFT JOIN sys.schemas ss
ON pm.major_id = ss.schema_id
LEFT JOIN sys.objects obj
ON pm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas s
ON s.schema_id = obj.schema_id
WHERE rp.type_desc = 'DATABASE_ROLE'
AND pm.class_desc <> 'DATABASE'
AND rp.name = 'db_NameofRole'

you should hopefully have a good pipeline to run in your lookup information into one lookup table and truncate that table when ever you run the process

Azure Built In Tagging Policy. Update Resource by Inheriting Subscription Tag

Ensure your Azure Resources are tagged with the Tag from Subscription

In this example we have the following requirements

  • The costCentre tag has been manually added to the Subscription
  • Every resource must be created with a costCentre
  • The CostCentre tag must exist on the resources
  • The Resources inherit from the container they are in, but can be manually overridden

Costcentre Tag Configuration

  • Modify Resources to add the CostCentre tag from the parent Subscription where missing

Time to assign a new Policy

No Need to select a Resource group because they haven’t been created yet and this applies to all resource groups that have yet to be created

Choose Inherit a tag from the subscription if missing

The Assignment Name has been changed to include the tag

And the tab name is added as a parameter

We need a Managed Identity because this has a modify effect

Then go to Review + Create and Create

Test the New Modify Policy

This is the tag on the Subscription

Create a New Resource Group (And remember to apply any tags of Policies you have already set

Within this Resource Group, Create a New Resource (Dont add the tag)

Once created, the Resource will inherit the Tag from the Subscription

Azure Built in Tagging Policy. Deny the Creation of a Resource group

Deny the creation of a resource group without applicationName Tag

Every resource group should have an application name because Resource Groups should be Application Related. However you cant get the applicationName from the Above Subscription.

As a Example, the Resource Group is for HR Performance.

applicationName Tag Configuration

  • Deny the resource group if its missing the applicationName tag

Steps to apply this policy in Azure

Search for policy and Assign

Go to Assignments. This shows you all the policies or Initiatives that are in place. The Scope for this shows that the all subscriptions are being shown in this list.

Click Assign Policy

Set Scope of Policy

First thing you need to do is set the Scope. We have Management groups in place and as a test, a Personal Subscription is being used

The Resource Group isn’t set because this is just being set at Subscription level

Exclude Resource

You can also exclude resource, and in this case resources from the above Policy. However for this policy we don’t need to do this.

Set the Policy Definition.

There are two types of definitions. Custom and built in

If you look for ‘Tag’ you get a list of the Built in Definitions for tags.

We know that we want to ‘Require a tag on resource groups’ so this built in policy can be selected

Choose the Built in policy and Select

The Assignment Name has been slightly changed to include the tag this will be set for

We are in the Basics tab at the moment

Parameters

Go to the Parameters Tab.

Remediation

Click the Remediation Tab

In order to understand the Remediation tab there are a few specific concepts to Understand

Managed Identity

Managed identities is a more secure authentication method for Azure cloud services that allows only authorized managedidentity-enabled virtual machines to access your Azure subscription.

This means that any service that ‘does something’ to another service can do so automatically using a Managed Identity

DeployIfNotExists and Modify effects

Every single Policy in Azure Policy has a single effect. Effects behave differently if they are for a new, updated or existing resource. The Effects supported are:

  • Append
  • Audit
  • AuditIfNotExists
  • Deny
  • DeployIfNotExists
  • Disabled
  • EnforceOPAConstraint (preview)
  • EnforceRegoPolicy (preview)
  • Modify

The question is. What is the effect of Require a tag on resource groups

Definitions

Go to Definitions in Policy and Search for the Built in Policy.

The effect of your chosen built in Policy the effect is to deny .

You can also see the JSON Definition of the chosen Policy

Because the Policy is deny we don’t need to set up a Managed Identity

Click Review + Create

And Click Create

Definitions

Going back to the policy Definition

You can see that this Policy has been assigned to a Subscription

Checking your Policy has worked

To test the policy create a new resource Group within the selected Subscription without adding any tags

Resource ‘Test-RG’ was disallowed by policy. Policy identifiers: ‘[{“policyAssignment”:{“name”:”Require tag applicationName on resource groups”,”id”:”/subscriptions/7e7aeec9-7cb0-4963-a029-128c29530b5f/providers/Microsoft.Authorization/policyAssignments/febb7d3199864e4ab84411f5″},”policyDefinition”:{“name”:”Require a tag on resource groups”,”id”:”/providers/Microsoft.Authorization/policyDefinitions/96670d01-0a4d-4649-9c89-2d3abc0a5025″}}]’.

Because the Name of the required tag was added into the Policy name, the warning lets you know which tag you need to set.

You can Implement this on any resource group that needs a tag setting when it cant inherit from anything above (Like Subscriptions)

Azure Use Policies for Tagging Introduction

When you get started with Azure, you want to ensure that your resource groups and resources are tagged. this is great for Management of your Azure Subscriptions/

You can categorise all your resources with tags. You can also view Billing by Tags.


When you add a tag into the Portal (Or programatically) you can add in any tag name as you go. Once created they come up in a list and you can choose from them again.

You can use Azure Policies to try and Enforce some Tagging Logic

Azure Policies

Policies allow you to use the built in or custom definitions. the Policies can then be assigned to a Management group, Resource Group or Subscription

Policies are executed when new resources are created. old resources may not be compliant

You can use built in tagging Policies that are already defined within definitions in JSON (Built in)

Or you can add a new definition by adding your own JSON or copying and amending a built in Policy (Custom)

We are going to have a look at some specific examples of how to use Policies for Tagging.

aedhttps://debbiesmspowerbiazureblog.home.blog/2020/05/05/azure-built-in-tagging-policy-deny-the-creation-of-a-resource-group/

https://debbiesmspowerbiazureblog.home.blog/2020/05/05/azure-built-in-tagging-policy-resource-group-to-inherit-subscription-tags/

https://debbiesmspowerbiazureblog.home.blog/2020/05/05/azure-built-in-tagging-policy-update-resource-by-inheriting-subscription-tag/

Azure Tagging with Powershell Example

Azure uses Tagging for easier management. Tagging is done with Key value pairs. You can create the manually in the Azure Portal but you can also do it programatically via Powershell (For Example)

For this example Log onto the Azure Portal, Open Cloud Shell (This is the new Azure Portal, Not classic)

And set it to run with powershell

Just to get warmed up, lets list all our Resource groups

Get-AzResourceGroup

You can right click in powershell and paste the code above. then click Enter

This gives you a list of all your resource Groups


You even get a list of tags for each resource group with the above command

However, the resource groups that are available to me currently are the ones on my personal visual studio subscription. the Current Subscription

Lets see what subscriptions we have available

Get-AzureRMSubscription

You get the Subscription Name, ID and TenantID

Lets find out the current Subscription Context

Get-AzureRmContext

You can also use the following command to get subscription details

(Get-AzureRmContext).Subscription

Now we want to change the current Subscription context. this example is going to look at a resource group and a resource in our Proof Of Concept Subscription

Set-AzContext -SubscriptionId "xxxx-xxxx-xxxx-xxxx"

The ID can be generated by using Get-AzureRMSubscription

And running Get-AzureRmContext again, provides you with confirmation that you have changed subscriptions

Now lets have a look at all the resource groups within this subscription with Get-AzResourceGroup again


These are the two resource groups we are interested in. POC has no tags and training Resources have a couple of tags at resource group Level.

Get-AzureRmResource | ft

This scrips allows you to get a list of all the resources and Resource groups within the current subscription

What I want to do now is check all the tags on the resource Group

(Get-AzResource -ResourceGroupName POC_Resource_Group).Tags

This function allows us to see every tag at resource level against the Resource group. So this script doesnt quite give us what we want. We know that there are no tags at resource group level. How do we establish this with Powershell?

$resourceGroup = Get-AzResourceGroup -Name POC_Resource_Group  
Get-AzTag -ResourceId $resourceGroup.ResourceId

This script allows us so see the tags on the Resource Group.

It would now be useful to get a list of Resources in the resource Group

Get-AzureRmResource -ResourceGroupName POC_Resource_Group | ft

So now we can see every resource in the Resource group

Next step is to have a look at tags per resource

(Get-AzureRmResource -Name "AdventureWorksDW").Tags

We know what our Resources are called now (See Above) so we can get specific tags of a resource group

Scripts to Add tags to a Resource Group

It would be really good to have the following tag at resource group level

  • businessProcess, Proof of Concept
  • environmentType, Dev
  • client, na
  • billingCategory, Internal Analytics

Lets have a go with powershell

$tags = @{"businessProcesst"="Proof of Concept"; "environmentType"="Dev"; "client"="na"; "billingCategory"="Internal Analytics"}
$resourceGroup = Get-AzResourceGroup -Name POC_Resource_GroupNew-AzTag -ResourceId 
$resourceGroup.ResourceId -tag $tags

The resource group now has Tags

Scripts to add tags to a resource

Completely update the List of Tags

We also want to add a tag to a resource.

If you were to use the following script

$tags = @{"costCentre"="000000"} $resource = Get-AzResource -Name AdventureWorksDW -ResourceGroup POC_Resource_Group  
New-AzTag -ResourceId $resource.id -Tag $tags 

And then run (Get-AzureRmResource -Name “AdventureWorksDW”).Tags to look at the tags, this script deletes all the tags and inserts just the one, this isn’t the logic we want to use. We want to Add tags to a Resource that already has tags

Add Tags to a resource that already has tags

$resource = Get-AzResource -Name AdventureWorksDW -ResourceGroup POC_Resource_Group 
$tags = @{"costCentre"="000000"} 
Update-AzTag -ResourceId $resource.id -Tag $tags -Operation Merge

Instead of New, Update has been used. and we gave the script a resource name and a resource group

(Get-AzureRmResource -Name “AdventureWorksDW”).Tags shows that the new tag has been added to the previous tags

Amend a Tag

After running (Get-AzureRmResource -Name “AdventureWorksDW”).Tags, adventureworkks is a value that has been inputted incorrectly and needs amending.

$resource = Get-AzResource -Name AdventureWorksDW -ResourceGroup POC_Resource_Group 
$tags = @{"applicationName"="adventureworks"} 
Update-AzTag -ResourceId $resource.id -Tag $tags -Operation Merge

Great. The incorrect value has been updated

Get Tags

a couple more really useful Scripts in regards to tags

Get-AzureRmTag

This script allows you to see all your tags and how many times they are used within the subscription

From the above script we can see that businessProcesst is a tag that has been added which needs amending to businessProcess (Its been incorrectly typed in)

Get more information about the Tag (Tag Values and Count of Use)

Get-AzureRmTag -Name "environmentType"

This script is great for looking at the values in a specific tag. And in this case we want to know more about businessProcesst

Get-AzureRmTag -Name "businessProcesst"

And to really tie it down we want to know what resource or resource Group it belongs too?

You can run the following Script to get the Specific resource Group for the tag

(Get-AzResourcegroup -Tag @{ “businessProcesst"="Proof of Concept"}).ResourceGroupName

Its in the Proof of Concept Resource group so all that is needed is to delete and recreate

And this Script checks if its against any resources

(Get-AzResource -Tag @{ “businessProcesst"="Proof of Concept"}).Name

In this case No

Remove-AzureRmTag -Name "businessProcesst"

Although we have checked, the error message stills says that the Tag is being referenced by resources.

It may be easier to update the Tag in Azure rather than using Code. there doesnt appear to be a way of changing the tag name in poweshell (Or at least I haven’t found it yet)

You can go to tags. Click on the Tag. Then click on … at the end of the tag Row and choose Edit tags.

Then Save

This just gives a flavor of how to use powershell to manage tags. This information can be saved into Scripts so we can reproduce the creation of tags if required.

Tags applied to resource Groups don’t get added to the Resources below. Powershell gives you move control over making sure the Resources and resources Groups have the correct tags applied

Introduction to Azure Tagging

Tagging is a feature that has been introduced into the Azure Resource Manager model (ARM). They can be used to Logically group and track resources. The old (Classic) version of Azure was Azure Service Manager.

Azure tagging can be done using Azure Portal, Azure Powershell, CLI (Command Line User Interface) or ARM (Azure Resource Manager) JSON templates

Tags can then be used to select resources or Resource Groups and are useful when you need to organize resources for billing or management

You apply tags to your Azure resources giving metadata to logically organize them into a taxonomy.

Each tag consists of a name and a value pair. For example, you can apply the name “Environment” and the value “Production” to all the resources in production.

Key Points

  • You can only apply tags to resources that support Resource Manager operations
    • VMs, Virtual Networks and Storage created through the classic deployment model must be re-deployed through Resource Manager to support tagging
    • A good way around this is to tag the resource group they belong to instead.
  • All ARM resources support tagging
  • Each resource or resource group can have a maximum of 15 tags.
  • Tags are key/value pairs, name is limited to 512 characters, value is limited to 256 characters
  • Tags are free-form text so consistent correct spelling is very important
  • Tags defined on Resource Groups exist only on the group object and do not flow down to the resources under them
    • Through the relationship you can easily find resource by filtering by tagged resource group Its recommended keeping the tags to the resource group unless they are resource specific.
  • Each tag is automatically added to the subscription-wide taxonomy
    • Application or resource specific tags will “pollute” the tag list for the entire subscription.

Issues with Tags

Using the recommended Tag procedure of tagging at resource group level causes issues because the Tags dont get inherited at Resource level.

The hope was that any tags that you apply at one level of the hierarchy will be inherited by the lower levels within the hierarchy and this doesnt happen.

You need to be careful that your tags stay Logical and don’t differ from higher levels. It may well be preferable to do this via Powershell Scrips that manually to ensure correct logic is maintained between resources and Resource Groups.

Resource Groups

  • The underlying technology that powers resource groups is the Azure Resource Manager (ARM).
  • ARM was built by Microsoft in response to the shortcomings of the old Azure Service Manager (ASM)
  • ARM requires that resources be placed in resource groups, which allows for logical grouping of related resources.
  • Although creating a resource group requires specifying a region for it to be stored in, the resources in that resource group could span multiple regions.
  • In the ARM architecture, resource groups not only become units of deployment, but also units of management of related resources.
  • It allows role-based access control (RBAC) at the resource group level, making it much easier to manage user access to the resources in the group.
  • When users log into the Azure Portal, they will only see resource groups they have access to and not others within the subscription. u Administrators will still be able to assign access control for users to individual resources within the resource group based on their roles. This is great to see costs associated with Each Resource Group

Successful Azure Resource Groups

If an application requires different resources that need to be updated together, such as having a SQL database, a web app, a mobile app, etc. then it makes sense to group these resource in the same resource group.

Use different resource groups for dev/test, staging, or production, as the resources in these groups have different lifecycles.

All the resources in the group should share the same environment (Dev, Test etc) because you deploy, update and delete together

If you have for example the marketing analytics database in one Resource Group and a demo database in another resource group, Each resource group needs its own server

You cant Rename a resource Group

A good naming convention to use is rg-projectorapp-subprojectorapp-dev or projectorapp-subprojectorapp-dev-rg

Examples of Resource groups

Adding Tags to a VM resource within a Resource Group

Tagging Examples

Now you have logical resource Groups set up we can set up tags which are good for larger organisations. 

Business Tags:

  • Cost centre
  • Responsible Person or Party
  • Application Name
  • Environment
    • Development
    • Testing
    • Staging
    • Production

Security Tags:

  • Data Profile

Automation Tags

  • Power Off
  • Maintenance Window
KeyExampleCommentType
costCenter12345This is your internal billing codeBusiness
managedBydebbie@peak.co.ukName or email addressBusiness
applicationNamemyappname of the Project Business
environment<production, Staging, QA>Identifies the environmentBusiness
dataProfile<Public, Confidential, Restricted, Internal>Data Sensitivity. Public: This information is public information, and can be openly shared on your website
Internal: Internal information is company-wide and should be protected with limited controls.
Confidential: Confidential information is team-wide and its use should be contained within the business.
Restricted: Restricted information is highly sensitive and its use should be limited on a need-to-know basis.
Security
powerOffyes, noCan this resource be shut down at 7pmAutomation
Example of a Subscription with Tags on the Resource groups.

Looking at this example, the Tagging has been added to the resource Groups. However if you look at a resource, you wont see the tags.

We need to look at a way of ensuring that the Tags applied to the resource group are also applied for each resource.

Adding tags to a resource in Azure Portal

Policies for Tags

  • Azure Policy is a service in Azure that you use to create, assign and, manage policies.
  • These policies enforce different rules and effects over your resources, so those resources stay compliant with your corporate standards and service level agreements https://docs.microsoft.com/en-us/azure/governance/policy/samples/apply-tag-default-value
  • Microsoft provides sample JSON scripts to create the tagging policies
  • JSON scripts can be deployed in the Azure Portal, Azure Powershell, Azure CLI or REST API.
  • When you create resource groups and Resources you should always apply tags and the default value for that tag.
  • There are Billing tags policy initiatives you can apply
  • You can also enforce tags and value via Policies on resource groups or Resources

https://github.com/Azure/azure-policy/tree/master/samples/built-in-policy/apply-default-tag-value

https://github.com/Azure/azure-policy/tree/master/samples/built-in-policy/enforce-tag-value

Locks

As an administrator, you may need to lock a subscription, resource group, or resource to prevent other users in your organization from accidentally deleting or modifying critical resources.

You can set the lock level to CanNotDelete or ReadOnly. In the portal, the locks are called Delete and Read-only respectively. it may be useful to add a tag for this

LockLevel <CanNotDelete, ReadOnly, NA>

Conclusion

We will look at tagging in more detail in other posts. What Policies you can apply. tagging via Powershell, CLI and ARM JSON Templates. How to manage and enforce good Tagging Logic.

We will also look at ways to use your tags

Links

Azure keeping your costs under control with Spending Limits and Budgets

We can now start adding Subscriptions for all our different Business domains and environments. Azure allows you to quickly get started adding Resource groups and Resources to your subscriptions. Using Management groups above subscriptions allows for even more control. You can manage access, policy, and compliance across multiple subscriptions.

This is all great but what about controlling costs? You will very likely have team members that cant wait to try resources to see what they can do but what happens when you came from after a weekend and you test Cosmos DB has racked up a huge amount of money?

We want to be able to control things more so lets look at some options.

Azure Spending Limit

The following Subscriptions have the ability to apply Azure Spending Limits

  • 0044P Free trial
  • 0059P Visual Studio Professional subscribers
  • 0060P Visual Studio Test Professional subscribers
  • 0062P MSDN Platforms subscribers
  • 0063P Visual Studio Enterprise subscribers
  • 0064P Visual Studio Enterprise (BizSpark) subscribers
  • 0029P Visual Studio Enterprise (MPN) subscribers
  • 0025P Action Pack
  • 0111p Azure in Open Licensing
  • 0170p Azure for Students
  • 0144P Microsoft Azure for Students Starter

When you get a free account, or are for example a Visual Studio Enterprise Subscriber you will get personal use credits.

for example. Going to a personal Subscription and looking at the overview

This Visual Studio Enterprise Subscription allows the user £115 Credits. You cant increase set credits.

If you reach your spending Limit, all services are disabled until the next billing period.

If you need to uncap you need to remove the spending limit by clicking on the above message.


Remove the Spending Limit for the current billing period will allow you to continue and the credits will then reset.

Remove the Spending Limit indefinitely will allow you to go over your Spending limit every month by adding your credit card details (Pay as you go)

However the recommendation is to only remove if required to keep more control

This is obviously a great choice for personal Azure Subscriptions, used for testing and trying things out.

Monitor Costs When using Azure Services

You cant cap the following subscriptions

  • 0136P Microsoft Azure EA Sponsorship
  • 0003P Pay-as-you-go
  • 0023P Pay-as-you-go Dev/Test
  • 0148P Enterprise Dev/Test
  • 0036P Microsoft Azure Sponsored Offer
  • 0243P Azure Pass
  • 0145P Azure in CSP

Track Costs with Budgets

Budgets allow you to set thresholds for your spending. You can then get alerts on these thresholds.

If for example, you have a budget for £500 and a threshold for £400 you could also start adding in automation to start shutting down resources, like VMs at the threshold.

Setting the Budget at the Subscription Level

Within the Subscription Overview

Click Next

Setting Alerts

Application Insights Smart Detection has been used as the action Group if the costs go up to 90% of the budget that has been set

‘Smart Detection automatically warns you of potential performance problems and failure anomalies in your web application’

Then Create

Targeting a Resource with a Budget

Again going to a Subscription and Clicking Cost Analysis

The resource that has a habit of becoming expensive is the database in this subscription

We want to make sure it doesn’t get out of control

Create a new budget for this subscription and then add a filter (the filter will only work once the graph is displayed

Resource Type is selected

then Microsoft SQL Servers. You could also put tags to good use in Budgets. You could for example check for tags of resources created by specific people.

You can now have Budgets that specifically check on certain services. Great if you have Services that have a tendency to create lots of costs.

So now we can rest a little easier knowing that Azure is going to let us know what is happening. It would be great if we could apply automation to shut something down if it was accumulating costs over the threshold. We can look at this next time

Setting up a Board in Azure DevOps (AGILE)

Its time to start tracking Projects with Azure DevOps boards.

With Boards, teams can manage software Projects. They can track user stories, backlog items, tasks, features etc. You can choose the environment you want to work with like AGILE or SCRUM.

For this example, there is only one developer (me) and I’m am tracking my progress on a project where I have been the single developer

AGILE is the process that going to be used

Agile is an iterative approach to project management and software development that helps teams deliver value to their customers faster and with fewer headaches. Instead of betting everything on a “big bang” launch, an agile team delivers work in small, but consumable, increments.

First Open Azure DevOPs

https://azure.microsoft.com/en-gb/services/devops/

Sign into your DevOps account.

And create a new Project under your Enterprise

Work Items

Now we have a new project we can start working with Boards but first we need to understand what our AGILE work items are and how they interact with each other.

Epic

I have specific Epics I want to achieve

  • Reporting from the companies main system
  • Social Media reporting
  • Reporting for the Surveys
  • Reporting for all the telephone enquirers
  • Reporting for Complaints
  • Main Reporting Area for all the data Auditing
  • Reporting for Report usage

So, just looking at this. I want 7 Epics to work with (To start with)

Feature

A feature is some complete behavior to implement a new business process. So for example. for the Social Media Epic we want

  • Overall View of Business Performance as provided by the surveys
  • Monthly level reporting on customer satisfaction with drill through

User Stories

User stories are within a feature. These are the smallest change that will result in behavior change. If you don’t observe a change then it cant be demonstrated

For example, as the Customer Satisfaction Manager I want to see the Survey results by month and have the ability to see how we are doing by over the year and at the same point in the year because we need to know if we are doing well as a company to see our trends in satisfaction

As the Company Head of Service I want a full review of our performance using our scoring system against customer satisfaction and how our competitors are doing for benchmarking

Task

These are within a User Story and are the smallest independently deployable change.

  • Get file of Survey data (Pilot project)
  • Move Survey data into the Azure Data Warehouse (Staging area) Incremental loading using Data Factory
  • Establish dimensions and facts
  • Create Dim 1
  • Create Dim 2…….
  • Create Power BI Data Flows
  • Create Top level report by Month of Customer Satisfaction containing last 12 months
  • Create KPIs for Satisfaction against this time last year
  • Drill through to detailed report
  • Drill through to lowest level

Bug

a Bug is an error in the code

  • Incremental Refresh is causing Duplicates
  • NULL data Items in Survey Data set

Issue

An issue is more related to a process, when the System fails to meet user expectation

  • Created a report based on poorly Served Customers but this needs changing to the new business logic.

Test Case

Test cases can validate individual parts of your code. We will look at this item in another blog post

Boards

Lets start with boards. These boards are Kanban Boards

A Kanban board is one of the tools that can be used to implement Kanban to manage work at a personal or organizational level.

When the board is first opened up, Epics don’t seem to be available

With Boards Selected Go to Configure Team Settings

Make sure that Epics are ticked in Under Backlogs

And now with Epics selected click on New Items are start adding in the required Epics

Next we need to start adding some features. It would seem that you cant add the features and then connect them to the epics. You have to create the Features from the epics

go back to the epic, click on … and Add Feature

And you can then see the feature within the Epic

Now we have a Feature we can add the user Story. Go to Features and click on the Add User Story

Same again. Move to User Stories and add Tasks

You can also go into the items and add lots more detail

This link to the Microsoft Documentation gives you lots of information regarding, effort, story points, business value, Priority etc.

Its always good to create the epic and work your way down into the Tasks

Retrospective Items

For this example, items are being added for a sprint that was closed some time ago because the project is being retrospectively moved into Azure Boards

Epics

I am starting them all from the beginning of this particular Project and for this I can add a Start Date

Stories

The Start Date also Applies to Stories but these will be set when the stories were originally created

However when you close a task and move the whole story into completed, you cant set a completed date


If you click on History and look at the state graph, you cant change the New and Resolved Times. These are set at the time of the action which makes it difficult to add past information into the Board

Backlogs

  • Backlogs help you to Quickly define work (User Stories, backlog items, requirements)
  • You can reorder the backlog so you work on the highest priority first
  • Add details and estimates
  • Assign items to team members and sprints by either bulk update or drag and drop
  • Map items within a hierarcy
  • Review the portfolio of work
  • Forecase work to estimate deliveries
  • Display rollup progress, counts and totals to show completion of work

Basically your backlog displays work items as lists and boards display them as cards

The Remaining Active User Stories have been dragged to Iteration 1

Work Items

All the work items you create can be viewed in here as well as created

Hopefully this gives you a little head start into the world of Azure DevOps boards

DevOps Organisation Settings

Lets have a look in a little more detail at the Settings for the DevOps Organisation Level and at what the Organisation is.

When setting up DevOps, it created at Organisation level of [My Name] , and another Organisation was then created manually for the company

Organisations can be treated as accounts and each organisations has its own URL. You will also hear organisations being called Collections

You must always start with one organisation but you can have multiple.

Go to Organisation Settings

General

Overview

This gives you the overview of the Devops organisation and there are a few options in here to be aware of

Privacy URL

Your Privacy URL can be added here to link to your organisations document describing how we handle internal and external guest data. If you have a public website or app you are required to have a dedicated Privacy policy URL.

Q Do you have a privacy document already in place?

https://www.freeprivacypolicy.com/blog/privacy-policy-url/

Organisation Owner

the owner is set against the person who created the organisation account but this can be changed.

Projects

New Projects can be set up here. See https://debbiesmspowerbiazureblog.home.blog/2020/03/06/create-a-devops-project/

Users


A good area to find out the Access levels for each user

  • Stakeholder: Partial access and can be assigned to unlimited users for free
  • Basic: Provides access to everything but test plans. Up to 5 users free, then £4.48 per month
  • Basic + Test Plans: Includes test plans. £38.76 per month
  • Visual Studio subscription: For users with a Visual Studio Subscription and features are enabled based on Visual Studio Enterprise, Professional Test Professional or MSDN Platform

Group Rules

DevOps includes group based licensing for Azure Active Directory (Azure AD) Groups and Azure DevOps groups.

Azure Active Directory Groups are created in Azure Active Directory


DevOps Groups and Collection Level Groups can be found within the Permissions section so we can look at this in more detail later.

Add a group Rule to assign an access level or extention to the group and resources in Azure DevOps are assigned to all members of the group.

When users leave a group the licenses are freed and returned to your pool.

Imagine this scenario

It will be easier to add the following Groups

  • Project A Contributor Group and add Debbie and Jo
  • Project A Reader Group and add Tess
  • Project A Administrator Group and add Sarah
  • Project B Reader Group and add Debbie and Tess
  • Project C Contributor and add Jo
  • Project C Administrator and add Debbie

To manage licenses and group rules, you must be a Project Collection Administrator (PCA) for the organization. Check this within Security > Permissions

Click Add a group rule

Both users within this new group have a visual Studio account so Group Level is set to to Visual Studio. However with Group Rules you assign the Access level for the Users at Group Rule Level

At this point I also added a new Group.

You can click on the … Button after Last Evaluated to get to either Manage Group Rules or Manage Members

These are the two rules for this group

What happens when your users have a mix of access levels within the one User Group?

The Users get their Access Levels from the Rule so their Access level would be reset

Billing

Before Looking at Billing you can use the Azure Calculator to get more of a feel of what you need

https://azure.microsoft.com/en-gb/pricing/calculator/?service=azure-devops

Note that you are paying for 5 basic plans, the first 5 are free but the 10 developers who need the Basic + Test Plans License are the ones adding to the monthly cost


Why would you up Additional Parallel CI/CD jobs on either Microsoft Hosted of Self hosted Pipelines?

See Parallel jobs for more information

Azure DevOps Billing is through Azure and because Billing has not yet been set up we only have access up to the Free Tier limits

If you click on Set up Billing You need to choose an Azure Subscription to add it too

You cant add it to your Personal credits because of the spending Limit caps. Once set up you can then manage the paid access for your users, bearing in mind the free usage tier

https://docs.microsoft.com/en-us/azure/devops/organizations/billing/buy-basic-access-add-users?view=azure-devops

Auditing

Allows you to see all the audit-able events and you can export the log and filter on specific events

Global Notifications

You will recieve notifications for lots of Actions in DevOps like when a build completes or a Deployment approval is pending. You can configure the organisation notifications from here

Usage

Usage allows you to see whats been going on in DevOps


You can also filter the information and choose your columns that you are interested in

You can Select

  • TFS (Any application within the Organisation Service account, e.g. TFS https://dev.azure.com/Organisation/)
  • Release Management (Any application within Release Management Service)
  • Analytics (Any application within the Analytics Service)

Statuses

  • Normal
  • Delayed
  • Blocked

And time period, for example, the last 7 days.

Extensions


You can browse the marketplace for additional DevOps Services. Like Analytics above to gain insight into the health and status of your DevOps Projects

Just Searching for Git brings up lots of free Git Services that you can use

Azure Active Directory

When DevOps was accessed, an email was used that was connected to a Tenant in Azure because we use Office 365

Office 365 uses Azure Active Directory (Azure AD) to manage user identities behind the scenes. Your Office 365 subscription includes a free subscription to Azure AD so that you can integrate Office 365 with Azure AD if you want to sync passwords or set up single sign-on with”

Because of this, Azure DevOps connected up the Azure Active Directory

Security

Policies

If we never want to allow Public Projects we can simply set the above policy to Off.

We can also dis-allow external guest access through policies

Permissions

Project Collection Administrator Has permissions to administer build resources and permissions for the collection

A collection is a container for a number of projects in Azure DevOps and corresponds to the Organisation.

Lets set up another Project Collection Administrator

We want everyone in the POC Admin Group to also be a Project Collection Administrator

The DevOps Groups that have been created and Collection Level (Enterprise Level) Groups can all be seen here

You can also look at the users and access all the Settings per user from the User tab.

Boards

Process

Gives you more information on what will be available on the board for each type, e.g. Agile, Scrum etc.

Pipelines

Agent Pools

DevOps Pipeline Pools are scoped to the entire organisation so they can be shared across Projects. You don’t need to manage Agents individually. You can organise them into Pools

An agent is installable software that runs one job at a time.

With Microsoft hosted agents, every time you run a pipeline you get a fresh virtual machine which is discarded after use

Settings

This is an example status badge in Boards

You can set variables at queue time unless the above option is enabled. Only variables Settable at queue time can be set if its limited

By default, the collection (Enterprise)-scoped identity is used, unless the Limit job authorization scope to current project is set in Project Settings > Settings.

Deployment Pools

An agent pool (Above) defines the sharing boundary for all agents in that pool. Whereas deployment Pools are about deployment target machines that have agents installed on them.

Deployment pools are convenient when you want to deploy a project to different machines

Deployment groups represent the physical environments; for example, “Dev”, “Test”, “UAT”, and “Production”.

The deployment group is a layer over the deployment pool which makes the targets available to the release definitions within a project

Parallel Jobs

As we have already established. The free tier allows you to have 1 parallel job but What is a parallel job?

A job is a set of one or more build tasks that run sequentially on the same target.

You could have for example a project that contains a web app and a Web API. They dont need to be run sequentially, they can be run in parallel to save time

Each App type can equate to 1 job within a build definition. Each build definition as its own build pool/agent

If you have two licensed build pipelines you could run them concurrently which will decrease overall build time.

You could also split up tests across jobs to be executed across parallel builds which will reduce the overall time

You need to look at your Projects and what you are building and see if Parallel jobs are relevant to you, or if you are happy for the agents to run your builds sequentially

Oauth Configurations

Register and Authorise your app

https://docs.microsoft.com/en-us/azure/devops/integrate/get-started/authentication/oauth?view=azure-devops

Artifacts

Storage

Check the storage sizes of your artifacts. Remember you get 2 GB free for your artifacts.

Hopefully this gives you a little more information of each sections of your organisational settings. You need to understand at a basic level

  • Who your teams will be and what they will require (Set Access Levels, Groups and Rules up)
  • What you will be creating and can they be built in parallel?
  • what environments will you be releasing too

Each section can then be looked at in much more detail

Create your website with WordPress.com
Get started