• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Tag Archives: Introduction

Kubeflow for data scientists introduction

January 8, 2021   BI News and Info

Kubeflow is a Machine Learning platform that runs on top of Kubernetes and provides end to end functionality for executing machine learning projects. Google created it for internal use of running Tensorflow jobs on Kubernetes, and they later released it as open-source in 2018. Kubeflow has become an essential toolkit for data scientists today since it abstracts them from the underlying complexities of Kubernetes and provides a seamless platform for easy execution and faster delivery of machine learning projects. To appreciate how Kubeflow can make a remarkable difference in a machine learning project, you first need to understand the pain points of data scientists.

word image 36 Kubeflow for data scientists introduction

Why is Kubeflow important for data scientists?

Around four to five years back when the hype of machine learning and data science had just started, everyone tried to capitalize on the trend in a rush. Individuals spent a considerable amount of time and effort to learn machine learning. In contrast, companies pumped millions of dollars overnight to launch their ML and DS projects. Yet, according to a Dec 2019 report, only 22% of companies running machine learning projects could deploy a model to production at all. And more than 43% of the respondents admitted they struggle to scale the ML projects according to the company’s needs.

The main reason behind this high failure rate is that everyone focused only on learning ML and DS concepts with POC work on their local Jupyter notebooks in the initial days. There was no thought process on how to practically execute real-world ML projects and deliver them to production successfully. This lack of understanding became visible when these projects started to fail in companies.

word image 37 Kubeflow for data scientists introduction

Since the ML project life cycle differs from the traditional software life cycle, the concept of MLOPs was soon introduced as a framework similar to DevOps to speed up the delivery of ML projects. To bring consistency and ease in the scalable model deployment process, containerization technologies like Docker and Kubernetes were also introduced for ML projects. Kubernetes is an orchestration framework for containers, specifically allowing easier deployment, horizontal scaling, and load balancing for the ML models.

However, as another report suggests, 39% of the data scientists still find it difficult to work with Docker and Kubernetes. This skill gap becomes a challenge for deploying ML models successfully to production. Even though Docker and Kubernetes can make life easy, they are separate technologies and require different expertise than machine learning to make the best use of them.

There was a growing realization that the data scientists should not be exposed to the complexities of managing the infrastructure side of the ML projects and should be given an abstracted platform where they can focus on what they can do best, crunch data, and create ML models. This is where the release of Kubeflow by Google became a game-changer for data scientists, and you’ll see how in the next section.

Features of Kubeflow

As mentioned in the beginning, Kubeflow is an end-to-end platform for creating, training, and deploying ML models and can run on any place where Kubernetes is already present. Kubeflow is now available on Google Cloud Platform, AWS, and Azure as services but you can also install Kubeflow on-premises or your local laptop. Let us now do a deep dive into Kubeflow offerings.

Model building and training

Kubeflow provides managed Jupyter Notebook instances that can be used for experimenting and creating prototypes of the ML models. It supports the popular libraries of Scikit Learn, Tensorflow, PyTorch, XGBoost and you can also carry out distributed training with the help of TF Jobs.

word image 38 Kubeflow for data scientists introduction

Jupyter Notebook on KubeFlow (Source)

Hyperparameter tuning

Finding the right set of hyperparameters for your model is not an easy manual task as it can be very time-consuming and may not even guarantee an optimal set of hyperparameters.

Katib is Kubeflow’s Hyperparameter tuning system that runs on Kubernetes underneath it to automatically optimize hyperparameters for the best results in less time.

word image 39 Kubeflow for data scientists introduction

Hyperparameter Tuning using Kubeflow Katib (Source)

Model deployment and serving

As shown above, deployment and serving of ML models in production in a scalable manner is the most challenging task for data scientists, but Kubeflow has made this task very easy with plenty of serving tools available for your needs

First of all, it provides KFServing which is a model serving tool that supports multiple frameworks like Tensorflow, PyTorch, Scikit Learn, XGBoost, ONNX. Under the hood, KFServing sets up serverless inference on Kubernetes by hiding the underlying complexity from the user. It takes care of autoscaling and health check of the underlying Kubernetes cluster on its own.

Besides, KFServing, there is another option of Seldon Core and BentoML which are other multi-framework supported serving tools. And in case you are working on the TensorFlow model you can also use the TensorFlow Serving that is available on Kubeflow.

word image 40 Kubeflow for data scientists introduction

KFServing (Source)

Portability and flexibility

Even though Kubeflow has various components to cater to different phases of an ML project life cycle, it does not restrict you to use it only for end-to-end purposes. It gives the flexibility to choose one or more components as per your needs, and, to support this flexibility, it also ensures portability across multiple infrastructures and clouds. This enables you to build and train the model externally and then use KubeFlow only for model deployment purposes. Or you may create and train the model on KubeFlow and then deploy it on some cloud for serving.

word image 41 Kubeflow for data scientists introduction

Kubeflow provides portability across clouds and other infrastructure (Source)

KubeFlow pipelines for CI/CD

The concept of machine learning pipelines for MLOPs actually comes from the DevOPs pipeline to ensure continuous integration and continuous deployment. Kubeflow CI/CD pipelines not only ensure automation of the ML workflows for faster delivery of changes but are also useful to create workflows that are reproducible for scalability.

word image 42 Kubeflow for data scientists introduction

Kubeflow Pipeline (Source)

Kubeflow Fairing

Kubeflow provides a high-level Python SDK – Fairing for creating, training, and deploying machine learning models locally and more importantly, remotely on Cloud. Fairing abstracts the users from the complexity of working with Cloud by streamlining the training and deployment process with just a few lines of codes so that you can focus only on ML models as data scientists.

As per the current documentation, Fairing supports working with GCP, AWS, Azure, and IBM Cloud.

Example – Kubeflow Fairing with AWS

The example below deals with the House Pricing Prediction problem and shows model creation, training, deployment, and serving using Fairing.

  1. ML Code – This snippet shows the code for training and prediction written inside the HousingServe class. (Additional details are omitted from here to keep the focus on the Fairing part, original code can be found here )

word image 43 Kubeflow for data scientists introduction

  1. AWS Setup – The next section shows how to set up Kubeflow Fairing with an AWS account, Docker registry, S3 bucket. You will have to replace the details with your AWS details, but the steps remain similar.

word image 44 Kubeflow for data scientists introduction

  1. Training remotely on AWS – You can submit your ML training job on AWS in just two lines by using TrainJob module of Fairing. The HousingServe class, training data, and AWS docker image are passed as arguments.

word image 45 Kubeflow for data scientists introduction

  1. Deploy Model on AWS – Similarly, deployment of the ML model on AWS is quite easy with the help of PredictionEndpoint module of Fairing. Make note, this time, you are passing the trained model file in the argument.

word image 46 Kubeflow for data scientists introduction

  1. Serving Prediction – The earlier step will generate a prediction endpoint which can be used in the following way for serving prediction. Replace the <endpoint> with the output of the above section.

word image 47 Kubeflow for data scientists introduction

As shown in the example, a Data Scientist only needs to focus on step 1, the ML model creation and other related data pre-processing tasks. All other steps from 2 to 5 are standard Fairing code which is relatively easy to execute for remote training and deployment on the cloud.

Conclusion

This article gave a gentle introduction to Kubeflow for data scientists and touched upon why Kubeflow is an important machine learning toolkit for data scientists. You also saw various functionalities offered by Kubeflow and finally understood Kubeflow Python SDK, Fairing with the help of an example.

If you like this article, you might also like Building Machine Learning Models to Solve Practical Problems – Simple Talk (red-gate.com)

Let’s block ads! (Why?)

SQL – Simple Talk

Read More

Dynamics, Mixed Reality & HoloLens Part 1 – Introduction

January 11, 2020   Microsoft Dynamics CRM

Introduction

Welcome to the first of a multi-part series on Dynamics, Mixed Reality, and HoloLens.  This series is aimed at current Dynamics 365 users that may not be familiar with Mixed Reality or how it can be used with Dynamics.  In this post we’ll cover what Mixed Reality and HoloLens are, and introduce the Dynamics Mixed Reality apps.  In later posts we will dive into each application in more detail and discuss uses cases for each. Let’s get started.

If you’re like me, you were first introduced to virtual reality many years ago through movies like the Lawnmower Man.  The idea of immersing yourself in a totally different world was fascinating and easy to imagine.  A few years later I started hearing the terms Mixed Reality and Augmented Reality, and like a lot of people I just lumped them in with Virtual Reality. 

Then, Augmented Reality glasses came along that projected some 2-dimensional contextual information on to your real field of view, and I thought that’s what Mixed and Augmented Reality must be.

A few years later I heard about this thing called the HoloLens and I assumed it was Microsoft’s version of Google Glass.  There was talk of holograms, but that had to be a gimmick right?  Holograms are only in movies and cool stickers right?  It wasn’t until I had the opportunity to try a HoloLens that it all clicked.  In an instant I knew I was experiencing a completely new and transformative technology.  I also now understood the true meaning of all three reality terms, which I’ll get to.

At that time I had recently started working at Microsoft as a Dynamics 365 Premier Field Engineer (great job for a great company).  It seemed worlds apart from HoloLens, and I couldn’t exactly run to Best Buy and pick one up, so I waited.  Then finally, about a year ago, those two worlds started to merge.  Microsoft released its first set of Dynamics applications for Mixed Reality and HoloLens.  I was able to acquire a HoloLens 1 (there’s a 2 now) and began exploring.

So why am I telling you all this?  I’m hoping you can relate to my journey to Mixed Reality enlightenment and that it might help you gain some clarity as well.

What are all these realities?

If you find the various reality terms confusing, you’re not alone.  Let’s quickly examine the three main terms in this space – Virtual Reality (VR), Augmented Reality (AR), and Mixed Reality (MR).

  • VR is fully immersive, meaning you can’t see anything in the real world.  This is great for gaming or taking a tour of Venice, but not so great if you need to walk around or work in a real space.
  • AR overlays text and images on your real world environment, but is not spatially aware and therefore cannot “mix” with or “anchor” to the space you’re in.  I like to think of it like a heads up display.  Something like what the Terminator or a fighter pilot might see.
  • MR, which is often confused with AR, takes things to another level.  It “mixes” virtual 3-dimensional objects (holograms) with objects in the real world allowing the two to coexist and interact.  It’s spatially aware and allows you to walk around and interact with these objects as though they were really there.  Place a model on your floor, walk out the front door, come in the back door, and your model stays right where you left it.

Mixed Reality and Augmented Reality are often used interchangeably so it’s easy to get confused, but they are not the same thing.

On a side note, recently one of my kids’ cereal boxes had a cardboard cut out on the back to make “Actual Reality” glasses, which gave us a good laugh.

What is HoloLens?

HoloLens is a self-contained, untethered MR headset with a holographic processor that melds 3-dimensional holograms seamlessly into the real world. No phones, additional computing, or cords required.  The lens is clear so you can see everything around you, and by using hand gestures and/or voice commands you can open applications, anchor browser windows and documents on walls, place 2-way video calls, and place 3-dimensional, holographic objects on tables or floors and interact with them.  Think of Tony Stark in his lab modeling new elements and Iron Man suits.

4863.pastedimage1578085670981v1 Dynamics, Mixed Reality & HoloLens Part 1   Introduction

There’s no substitute for trying one, but a video is the next best thing.  This introductory video should help connect the dots and leave you wanting to see more.

So What’s The Big deal?

Who cares if you can see things that aren’t really there?  Well that’s exactly the point. Now you can work with people and objects as if they really were there.  It literally adds a new dimension to computing by moving things off your screen and fusing them with the real world.  We can learn, teach, design, build, repair and play in entirely new ways.  This technology has the potential to transform how we do business.  To get your creative juices flowing, here are some practical examples of what you can do with MR:

  • Medical – Imagine practicing medical procedures on detailed, life-size, 3-dimensional models of the human anatomy with interactive guidance from a doctor, without an actual human subject.
  • Assembly and maintenance of equipment – A field tech can refer to documentation, view step by step instructions with animations, see markers on the actual equipment, and receive remote guidance, while keeping their hands free throughout.
  • Construction & Architecture – Imagine being able to walk through a detailed, life-sized model of a building on the actual jobsite before construction even starts.  Imagine walking through a building in progress with the ability to see what’s right, what’s wrong, and what’s left to be done while your team watches and comments remotely.
  • Design & Layout – Imagine walking through your new warehouse or factory (or that of a customer) and being able to view and rearrange life sized fixtures and equipment in the actual space.  Imagine seeing how 10 different couches look in your living room, without leaving your living room.

You can start to see the possibilities.  Use of MR in training & education, field service, construction, logistics, Inspections, sales, and other areas has the potential to reduce costs, boost productivity, and improve customer service. 

 Dynamics, Mixed Reality & HoloLens Part 1   Introduction

What Does This Have to do with Dynamics?

When you consider the use cases cited above and the fact that Dynamics sits at the core of sales, customer service, and field service operations for many companies, it is the ideal launch pad for commercial applications of MR.  The introduction of a set of MR applications for Dynamics has made it easy for companies to start taking advantage of MR now, without custom development or a huge investment.

With a little bit of setup, resources in the field can access their service calendar, view products and equipment, launch interactive guides, and get expert remote assistance.  There are currently four Mixed Reality Applications for Dynamics:

Remote Assist

Through integrations with Dynamics and Microsoft Teams, resources in the field can view their service calendar and place 2-way video calls for remote assistance.  Off-site experts can see what the onsite resource sees, and even place anchored holographic arrows and other markers into the workspace to help diagnose and repair issues.  You can place your best techs anywhere in the world in an instant, reduce travel costs, and improve customer service.

https://www.youtube.com/watch?v=J-C6GE2gFYw

Guides

Administrators can create step by step guides for resources in the field, without custom code, to walk them through procedures.  Guides can contain text, videos, and holograms for each step and can be linked directly to services in Dynamics. Using HoloLens a field resource can select a service appointment from their calendar, open the associated guide, and step through the procedure, keeping their hands free the whole time. Train your team and complete service calls in less time by keeping their eyes and hands on task.

https://www.youtube.com/watch?v=V8c3pDKdHEc

Layout

Layout allows you to pull life-size 3-diemsional objects into your space, move them around, measure distances, and then actually walk through the space.  You can use predefined layouts or let the HoloLens map the space you’re in.

https://www.youtube.com/watch?v=9viR6U-D2Co

Product Visualize

Product Visualize allows you to see life-size, 3-dimensional objects in your real space using a phone or tablet running iOS.  It does not use HoloLens, but is a useful sales tool that provides a good introduction to MR using devices that many field resources already have.

https://www.youtube.com/watch?v=lk2HzxZfkW4

For more details on these apps you can visit https://dynamics.microsoft.com/en-us/mixed-reality/overview/

Summary

One of the early challenges with Mixed Reality is being able to apply it in practical ways without spending an arm and a leg on custom development.  With the introduction of these tools, Microsoft has done a great job of packaging up the core value proposition in a way that makes it accessible to everyone.  Do these applications meet every advanced MR need?  Hardly.  We are only just scratching the surface.  But if you’re a Dynamics customer with resources in the field, these solutions deserve serious consideration.

If you’d like to hear more, follow this blog and watch for part two of the series where I’ll cover Remote Assist.  Feel free to post comments and questions below as well.  Thanks for reading.  Now back to reality.

Let’s block ads! (Why?)

Dynamics 365 Customer Engagement in the Field

Read More

Introduction to DevOps: 10 Guidelines for Implementing DevOps

November 19, 2019   BI News and Info
SimpleTalk Introduction to DevOps: 10 Guidelines for Implementing DevOps

The series so far:

  1. Introduction to DevOps: The Evolving World of Application Delivery
  2. Introduction to DevOps: The Application Delivery Pipeline
  3. Introduction to DevOps: DevOps and the Database
  4. Introduction to DevOps: Security, Privacy, and Compliance
  5. Introduction to DevOps: Database Delivery
  6. Introduction to DevOps: 10 Guidelines for Implementing DevOps

Throughout this series, I’ve touched upon various aspects of the DevOps application delivery model, covering such topics as the delivery pipeline, security as an integrated process, and the importance of incorporating database deployments into application delivery. This article ties up the series by providing ten important guidelines for implementing DevOps in your data center. Although each DevOps environment is unique to an organization—addressing its specific needs and circumstances—these guidelines can offer a starting point for planning and implementing your DevOps strategy, discussing many of the considerations to take into account along the way.

1. Build an effective DevOps culture.

You might be tempted to view this first guideline as nothing more than introductory fluff to round out the article, and certainly many teams implement DevOps before fully appreciating the value of an effective culture. Failure to establish and encourage the right culture will inevitably translate to an excruciating and inefficient DevOps process or, worse still, a complete operations meltdown.

Implementing DevOps in any organization requires a shift in thinking, a new mindset that values communication and collaboration over inflexible roles and siloed teams that can’t see beyond their own borders. Those who participate in the DevOps process must be willing to work together and recognize that they’re accountable for application delivery from beginning to end and that they have a stake in the outcome.

To establish such a culture, you need a firm commitment from management and other leadership that clearly demonstrates a willingness to dedicate the time and resources necessary to establish and encourage transparent communications, information sharing, cross-team collaboration, and a general attitude that application delivery is everyone’s responsibility.

2. Take baby steps when getting started.

DevOps is not an all-or-nothing proposition. You do not need to revamp your entire operation overnight. Forget about the massive implementation scheme and instead take small steps toward achieving your goals. A DevOps implementation requires thorough planning and careful rollout while taking into account business needs that can evolve over time. For this, you need plenty of leeway.

You should be thinking long-term toward full implementation and not try to accomplish everything in a couple of months, especially for a large-scale operation. Rushing a DevOps implementation can be as big a mistake as failing to establish the right culture. You need time to assess requirements, train participants, choose the right tools, and deploy the infrastructure. Trying to implement DevOps before you’re prepared can result in buggy applications, compromised data, and a lot of wasted time and money.

When planning your DevOps implementation, it’s better to start small than risk the entire operation. For example, you don’t need to automate every task at once or move all your applications to DevOps at the same time. You can start by automating one or two processes or by developing smaller, less critical apps. After you’ve succeeded with one phase, you can then move on to the next.

3. Plan and document your development projects.

For any DevOps project, your development process should start with a thorough planning phase to ensure that development efforts run efficiently, remain on schedule, and come in under budget. All teams involved with application delivery—including development, testing, and operations—should participate in the planning process.

As part of this process, you should set realistic milestones, taking into account the time necessary to implement new tools and infrastructure and to allow those who are new to DevOps to adjust to a different way of working. Development efforts should focus on small, incremental changes, with more frequent release cycles. This approach can lead to releases that are more reliable and predictable while helping to avoid issues that can complicate and disrupt the application delivery process.

In addition, DevOps teams should document all necessary information throughout the planning and application delivery processes. Proper documentation is crucial to establishing a culture of collaboration and communication. It helps team members understand the systems, what has changed, what caused specific issues, and how to resolve those issues. Detailed documentation can also help improve subsequent release cycles for the current project and better streamline operations for future projects.

4. Take a security-first approach to DevOps.

Security, compliance, and privacy should be factored into your DevOps processes from the beginning and continue through all phases of application delivery, whether planning your applications, setting up infrastructure, writing code, or deploying to production. Security should not be treated as an afterthought, or a segregated phase squeezed into the application delivery process right before deployment, or worse still after the application goes live. Security must be integrated into all phases, implemented continuously, and treated as a priority by all team members.

As with application development, automated testing can be integral to ensuring that data remains secure and protected, with checks performed during each release cycle. If a test exposes a potential issue, it can be tagged for a security review, and the issue addressed quickly by developers before that application has an opportunity to find its way into production. In addition, peer code reviews should look for potential security and compliance issues, along with application-specific concerns.

DevOps teams should also take the steps necessary to secure the DevOps environment and processes themselves, such as storing all code in a secure source control repository, isolating the DevOps systems in a secure network, verifying all third-party code, or adhering to the principles of least privilege. There are, in fact, several best practices an organization should follow to implement continuous DevOps security, and teams must commit to ensuring those practices are always being followed.

5. Implement a code management strategy.

All DevOps teams should be using a source control solution that versions and protects files. The solution should provide a single source of truth for all files and ensure that they have a record of who changed what while providing easy access to any specific version of the application. But version control alone is not enough. You must also ensure that you check in all relevant files—not only application code, but also configuration and change scripts, test scripts, database deployment scripts, reference data, and any other files relevant to application delivery.

A code management strategy should also address how to handle branching, which lets developers work on different features simultaneously, without stepping all over each other. You’ll have to determine the best approach to branching based on your organization’s requirements. Just be sure that the source control solution you choose contains branching tools that are robust and sophisticated enough to ensure that branching works in your favor, rather than complicating operations.

You should also take into account other considerations when planning your code management strategy. For example, you might want to implement a policy that requires developers to check in their code at least once a day, or more often if necessary. Infrequent check-ins can complicate operations unnecessarily and slow down the development effort. Also, be sure that source code and other critical files are not being managed directly on local workstations or network shares. Again, everything should be in source control.

6. Automate, automate, automate.

For DevOps to be effective, you must automate as many operations as possible and practical. The more operations that can be automated—especially the mundane, time-consuming, repetitive ones—the more efficient the overall process and the fewer the risks. Where possible, avoid manual one-off tasks in favor of repeatable operations that can be automated (keeping in mind the second guideline about taking baby steps).

You won’t be able to eliminate all manual processes, but try to make them the exception, rather than the rule. Manual processes can slow development efforts or bring them to a halt, such as when a developer stops working on code to set up a virtual machine or implement a database environment.

Most standardized DevOps operations can now be automated, helping to improve efficiency and speed up application delivery. An automated operation can be used multiple times by multiple team members in multiple circumstances. In addition, the operation can be altered and improved to address changing business requirements. Test automation is a good example of this. You can write unit tests that kick off automatically when updated code is checked into source control. The tests can run as often as necessary, and they can be updated as needed to accommodate new requirements.

7. Think continuous everything.

In the world of DevOps, application delivery is not a one-time operation but rather an ongoing process that makes it possible to continuously update and improve the application until it reaches the end of its lifecycle. As with automation, continuous practices must be ongoing and deeply integrated into the DevOps environment. DevOps is not so much a linear process as it is a continuous flow of consecutive iterations that last until the application is no longer being actively developed or maintained.

Discussions about the continuous nature of DevOps often focus on continuous integration, delivery, and deployment because of the pivotal roles they play in defining the DevOps pipeline. Continuous integration, for example, makes it possible for developers to check in frequent code changes and know that those changes are automatically verified so they can be immediately incorporated into the codebase.

But continuous integration does not operate in a vacuum. It goes hand-in-hand with continuous testing, which validates the code by running automated tests that have been predefined to look for specific issues, making it possible to identify problems early in the development process, when they’re much easier to address. Also important to continuous integration—and the DevOps environment in general—are continuous security, monitoring, and feedback, which ensure that projects stay on track, DevOps processes work efficiently, and sensitive data is not put at risk.

8. Make quality assurance a priority.

One of the foundations of effective QA is automated, continuous testing that’s integrated into the DevOps pipeline. The software should be tested at each phase of the application development process, with development and testing done in tandem, beginning with the first code check-in. In addition, the testing strategy should incorporate the environment in which the application will be running, such as verifying that the correct software versions are installed or that environmental variables are properly configured.

Other factors critical to effective QA are continuous monitoring and feedback. You must be able to track the application’s health in order to identify any issues with the application or the environment in which it runs, even as the application scales. You should also be tracking the DevOps infrastructure itself in order to optimize application delivery and alert the team to any issues in the delivery process.

DevOps teams should consider using key performance indicators (KPIs) that measure such metrics as failure rates, time to resolution, completed operations, incomplete tasks, milestones accomplished, or any other factors that can help the team understand and improve operations and the application. Teams might also consider automatic dashboards that provide real-time insights into the environment and development efforts. When planning your DevOps infrastructure and projects, be sure you include QA experts to ensure that no aspect of QA is being overlooked.

9. Manage your environments.

Application planning must take into account the environments in which the application will be developed, tested, staged, and deployed to production. For example, you’ll likely need separate environments for developing an application so developers can work on different parts of the application without conflicting with each other. The same goes for testing. Different environments are usually needed to ensure the accuracy of the testing processes. Then there are the environments needed for staging and deploying the application, which can vary depending on the deployment model.

To address environmental requirements, many DevOps teams are now implementing infrastructure as code (IaC), a process of automating infrastructure creation. To incorporate IaC into your DevOps processes, you start by writing scripts that define the infrastructure necessary to support your application. For example, a script might provision a virtual machine, configure its operating system, install a database management system, apply security updates, and carry out several other operations.

With IaC, the application code and configuration scripts are linked together, rather than the application being tied to a single machine or cluster. The configuration scripts run automatically whenever the application is deployed. IaC ensures that the application environment is always the same, no matter where that environment runs while eliminating the need to set up environments manually. IaC also allows you to create as many temporary environments as necessary to support the application development process while ensuring that everyone is working in the same environment.

10. Choose the right tools and technologies.

To implement a successful DevOps environment, you need tools that increase efficiency and simplify tasks. But keep in mind the second guideline about taking baby steps. You don’t need every DevOps tool out there, and you don’t need to implement every chosen tool at once. Select your tools carefully, looking for those that integrate easily with other systems, facilitate automation, foster communication and collaboration, and provide visibility into your DevOps environment and processes.

A DevOps environment can require a wide range of tools. A source control solution is, of course, a given, but you’ll also need tools for automating infrastructure, monitoring systems and applications, integrating security, tracking tasks and development cycles, managing database releases, and carrying out several other processes. Fortunately, many vendors now offer solutions that support DevOps application delivery, but they differ, so you’ll need to evaluate them carefully.

As part of this process, you should take into account the architectures and technologies you’ll be employing. I’ve already pointed to IaC as an essential strategy, but there are other technologies that can also be effective in supporting DevOps, such as containers or microservices, all of which require their own set of tools. In addition, you should evaluate the extent to which you might be using cloud technologies to augment or host your DevOps operations and how to integrate systems between platforms.

Tailor-made DevOps

There are, of course, other considerations than what I’ve discussed here, and those that I have discussed could easily justify articles of their own. Even so, what I’ve covered should help you get started in planning your DevOps strategy and provide an overview of some of the factors to take into account as part of that process.

Keep in mind, however, that each DevOps implementation is unique and should be tailored to your organization’s specific needs and circumstances. Although DevOps has proven a valuable strategy for many organizations, there is no one-size-fits-all approach to implementing DevOps, and even if there were, there would be no guarantee that each implementation would work exactly the same in all circumstances. A DevOps environment should be designed to fit your organization’s requirements and improve application delivery, not make the process more difficult for everyone involved.

Let’s block ads! (Why?)

SQL – Simple Talk

Read More

Introduction to DevOps: Security, Privacy, and Compliance

September 22, 2019   BI News and Info

The series so far:

  1. Introduction to DevOps: The Evolving World of Application Delivery
  2. Introduction to DevOps: The Application Delivery Pipeline
  3. Introduction to DevOps: DevOps and the Database
  4. Introduction to DevOps: Security, Privacy, and Compliance

A DevOps approach to application delivery offers a number of advantages over traditional methodologies, most notably faster and more efficient deployments, yet there’s nothing inherent in DevOps that promises more secure delivery or better protection for the applications and their data. In fact, DevOps can potentially lead to greater risks if security is not integrated into the development process right from the start. In today’s word of escalating cyberattacks and increased privacy regulations, security should be as much a part of application delivery as continuous testing and ongoing integration. Anything less could put your entire organization at risk.

DevOps Security Challenges

Cyberattacks are on the rise, and they’re becoming more sophisticated every day. If data is compromised, the consequences can have far-reaching implications, not only to the bottom line, but also to an organization’s long-term reputation.

Hackers know where to find security holes, how to exploit them, and what rewards they might be able to reap. They also recognize the opportunities that can result from accelerating software delivery without taking special care to protect applications and their data. Development teams that implement DevOps methodologies need to be particularly vigilant to guard against the many security challenges that come with delivering applications at a high velocity.

One of the biggest hurdles to security in a DevOps environment is the failure to factor in security right from the start—when planning and designing the application. Instead, security is treated as an afterthought, a clean-up operation, a reactive process separate from the primary development effort, resulting in slower application delivery or applications being released before they’ve been fully vetted.

Part of the reason for this disconnect is that traditional methods for ensuring application and data security do not fit easily within DevOps processes and, in fact, can inhibit operations. In addition, development and security teams often assume that, even with DevOps, it’s business as usual when it came to security, with the two teams operating independently of each other, often without coordinating efforts. The DevOps team will deliver the application, and the security team will identify and address any security issues, kicking the application back to the developers if it becomes necessary.

Unlike development and operations, where efforts merge together in a more organic fashion, development efforts can often seem at odds with the time-consuming processes that come with ensuring application security. On some DevOps teams, there can be a cultural resistance to security for fear it will slow down application delivery, an attitude often coupled with a general resistance to changing the way security is traditionally handled.

At the same time, DevOps teams are under incredible pressure to deliver application updates as fast and as frequently as possible, with upper management demanding that nothing slow down operations. Many believe that addressing security issues early in the development process will throw a roadblock in the way of their efforts.

In some organizations, there’s also uncertainty around who’s responsible for which aspects of security, especially if there’s no established security team. At the same time, some DevOps team members might lack adequate training in security, leaving them ill-equipped to deal with today’s increasingly sophisticated threats.

On top of everything else, a team might utilize risky application technologies without fully addressing potential risks. For example, developers might incorporate newly released open-source code, expose application programming interfaces (APIs) that are not fully secure, or deploy containers without proper controls. They might also introduce risks into the code through poor practices or human error. Without formal security checks in place, those risks can go unnoticed until well into the development process or until the deployed application comes under attack.

Compliance and privacy

Discussions around application security must inevitably include issues related to compliance and privacy. Stricter regulations, increased liabilities, and heightened public awareness mean that protecting personally identifiable information (PII) is no longer a luxury, but rather a requirement and priority. If your application lacks the security necessary to protect PII, your organization risks steep fines, costly litigation, and a violation of public trust, from which you might never recover.

To make matters worse, regulations can vary significantly from one region to the next, adding to the complexity of ensuring compliance throughout the data’s lifetime. And some of those regulations can have far reaching implications. For example, the General Data Protection Regulation (GDPR) went into effect in the European Union (EU) in May 2018, and one year later, the industry is only now starting to feel its full impact.

In that time, there’s been a massive increase in the number of reported data breaches. Prior to that, the EU had no single mechanism to support notifications, and it’s believed that many breaches went unacknowledged. But much has changed with the GDPR, which requires organizations to report breaches within 72 hours of being discovered. In addition, the regulation more clearly defines what constitutes protected data, provides individual users with greater control over their data, and regulates how organizations can use that data.

Organizations that fail to comply with the GDPR can face stiff penalties—up to €20 million or 4% of their worldwide annual revenue of the prior financial year, whichever is higher. Google, for example, was fined €50 million in January 2019 for GDPR violations, representing the majority of fines collected in the first year of GDPR enactment.

Compliance laws, particularly the GDPR, can also cause a butterfly effect, which refers to the law’s wider impact across the globe. One way this is being felt is in the enactment of other regional laws that take their cue from the GDPR in defining user protections and corporate responsibilities. The GDPR can also impact organizations outside the EU that do business either directly or indirectly with the EU, affecting their operations, workloads, and data protection strategies, wherever that data resides.

Organizations that face compliance laws in multiple regions might choose to adhere to the strictest requirements for all their data in order to ensure compliance, so if the GDPR represents the severest of the laws, the organization might follow those standards for all PII, wherever it resides. For development teams, this means ensuring that data is protected during all phases of the DevOps process, and the best way to do that is to build security into the application delivery pipeline right from the start.

Incorporating security into DevOps

A secure DevOps environment incorporates the tools and processes necessary to protect the application and its data at every stage of the operation. Security starts with the planning and design phases and continues through the entire application lifecycle, baked into the continuous integration and delivery processes. In this sense, security merges with development and operations to create a cohesive three-pronged approach to application delivery, as shown in Figure 1.

word image 8 Introduction to DevOps: Security, Privacy, and Compliance

Figure 1. Incorporating security into the DevOps process

Secure DevOps calls for a shift left in thinking that pushes security to the beginning of the application delivery pipeline, rather than dangling it out at the end, where it can be the most disruptive to application delivery. Instead, security is built into the planning, development, testing, deployment, and operation phases.

As with other DevOps processes, the goal is to automate security wherever possible, using testing and monitoring to ensure the proper implementation of comprehensive protection policies. In this way, potential risks can be better identified and mitigated early in the development process, while ensuring the continued velocity of the DevOps process.

Implementing secure DevOps starts with a change in mindset toward security and its role in application delivery. Critical to this attitude are support and buy-in from upper management and other stakeholders. Secure DevOps calls for open collaboration, greater transparency, and a shared set of objectives, along with the appropriate use of automation, proper tooling, and advanced application technologies such as infrastructure-as-code.

Secure DevOps, sometimes referred to as DevSecOps, is as much a process as a philosophy, one that sees security as a shared responsibility by everyone involved in application delivery. DevSecOps bridges the gap between development and security teams, helping to reduce the contention that can sometimes occur with traditional approaches to security.

By embedding security processes directly into the application delivery pipeline, DevSecOps offers early and continuous risk management through testing and monitoring, which provide in-depth insights into possible vulnerabilities.

Secure DevOps can also promote a culture of security, encouraging team members to be mindful of the importance of data and application protection at all times. With DevSecOps, developers are more likely to consider security when planning and building applications, QA professionals are more likely to take security risks into account when testing applications, and operation specialists are more likely to adhere to security best practices when deploying application infrastructures.

DevSecOps also leads to more secure and compliant applications because continuous testing and monitoring are integrated into the pipeline at a granular level, making the process more efficient at identifying and limiting threats and compliance violations. With DevSecOps, security becomes a natural part of the application design and fully integrated into the entire delivery process.

Secure DevOps can also make application delivery faster and, as a result, cheaper. Security flaws identified early in the design and development cycles can be addressed much more efficiently than if identified later in the process. As with any bugs, the sooner you find security issues, the quicker and easier they are to fix.

The DevSecOps approach also makes it possible for teams to respond more quickly to changing requirements and circumstances because security is baked in from the start, rather than ladled on top of the application at the end. This integration can also translate to a better user experience, in part because of security is built in, but also because feature updates and application fixes get to users faster.

Implementing secure DevOps

The logistics of integrating security in DevOps will vary from one team to the next, but most teams will follow several basic practices, some of which they might have already incorporated into their infrastructures. For example, in a DevSecOps environment, all source code should be stored in a secure version control repository, leveraging such technologies as encryption and digital signing.

The secure DevOps team should also practice the principle of least privilege so that only authorized individuals can access system resources, with access specific to their job roles. In addition, developers should work with code in small chunks so it’s easier to identify vulnerabilities, and all code should undergo peer reviews, including any scripts used to build infrastructure or deploy applications. The code reviews should look specifically for security risks, compliance violations, and adherence to best security practices.

As with DevOps itself, automation is key to successfully implementing DevSecOps, which requires tools that can seamlessly integrate into the continuous integration and delivery processes. For example, an organization might use Puppet for its automation framework and use Splunk in conjunction with Puppet to provide the analysis necessary to determine when a configuration goes out of compliance. To provide masked copies of databases for development and testing, organizations might use an automated solution like SQL Provision.

An organization might also use a monitoring tool such as WhiteSource, which detects open source components in the code and alerts team members to security risks and software bugs. There are plenty of other tools out there as well, which can help address security throughout all phases of application delivery—and more tools are emerging every day.

Continuous testing is a critical component of automating security, and there are many types of tests that can be performed. For example, DevOps teams might use static application security testing (SAST) to automatically analyze code for security vulnerabilities, without running the application. They might also use dynamic application security testing (DAST) to analyze the application for vulnerabilities when it’s running. Teams might also utilize penetration tests, threat investigations, vulnerability assessments, data flow analytics, and numerous other strategies to help ensure security and compliance.

Just as important as testing is continuous monitoring, which automatically tracks security-related metrics throughout the application delivery lifecycle. The collected information should reflect a wide range of information for identifying security issues. It should also include a system for alerting team members when issues are discovered, such as when code fails a compliance test. In addition, the monitoring solution should offer systemwide information that provides team members with a security overview as well as specifics that might be required for a security or compliance audit.

Moving from DevOps to DevSecOps

Although security might seem at odds with DevOps methodologies at first glance, incorporating security into the DevOps process is an inevitable outcome of today’s sophisticated cyberattacks and expanding privacy regulations.

Organizations that fail to incorporate a DevSecOps mindset are likely to lose ground when trying to implement safe applications that meet the necessary security and compliance standards. For most teams, incorporating security in the early stages of application development is the only viable option in a world of increasing threats, stiffer penalties, and greater risks to the organization’s reputation and the public’s trust.

Let’s block ads! (Why?)

SQL – Simple Talk

Read More

Introduction to DevOps: DevOps and the Database

August 10, 2019   BI News and Info

The series so far:

  1. Introduction to DevOps: The Evolving World of Application Delivery
  2. Introduction to DevOps: The Application Delivery Pipeline
  3. Introduction to DevOps: DevOps and the Database

DevOps has been gaining steady ground in the world of application development, with ongoing improvements in delivery processes and the tools needed to support them. For the most part, however, DevOps has focused primarily on the application itself, with databases left to their own devices, despite the integral role they play in supporting many of today’s data-driven workloads.

According to the 2018 Accelerate: State of DevOps report, “database changes are often a major source of risk and delay when performing deployments.” But the report then states that integrating databases into the application delivery process could have a positive impact on continuous delivery. The key, according to the report, is good communication and comprehensive configuration management that incorporates databases. In other words, database changes should be managed in the same way as application changes.

Redgate’s 2019 State of Database DevOps report adds to these findings with their own survey results. According to the report, 77% of the respondents claim that their developers work across both databases and applications, and 75% say that developers build the database deployment scripts. However, 63% acknowledge that DBAs are responsible for deploying database changes to production.

The report also states that the two most significant challenges to integrating database deployments into the DevOps process are “synchronizing application and database changes” and “overcoming different approaches to application and database development.” In contrast, the two biggest reasons for integrating databases into DevOps are “to increase speed of delivery of database changes” and “to free up developers’ time for more added value work.” More importantly, 61% believe that such a move would have a positive impact on regulatory and compliance requirements.

Clearly, integrating database deployments into the DevOps process remains a challenge for many organisations, yet there’s a growing recognition that databases need to be brought into the DevOps fold. And in fact, this is beginning to happen, as more organisations incorporate database deployments into their integration and delivery processes. Not only does this promise to help improve application delivery, but it can also benefit database development itself.

The DevOps Promise

When application and database development efforts are more tightly integrated—with teams working in parallel and sharing a common process—they can release builds that are more consistent and stable, while reducing unnecessary risks and delays. The teams use the same infrastructure to develop and deploy application and database code, making it easier to standardise processes and coordinate efforts.

In a more traditional development environment, database changes can hold up application delivery, impacting productivity and costs. By incorporating databases into DevOps, database deployments can benefit from the established processes, while more tightly integrating database and application development.

A DevOps approach to database development also makes the process more agile and adaptable. Teams implement smaller, more frequent changes as part of a coordinated effort while receiving continuous feedback on the delivery processes and application components. In this way, database development and deployment are no longer disconnected from the larger delivery workflow but become integrated into the process throughout the application lifecycle.

The DevOps Challenge

As good as database DevOps might sound, there’s a good reason that organisations have been slow to jump on board. Transitioning from a siloed-database model to a DevOps-database approach is no small effort.

To begin with, databases were never part of the original DevOps vision. Processes and tools were developed for application code and application deployments, not for the peculiarities of database management. It’s only been recently that databases have been incorporated into DevOps processes in a meaningful way.

Database tools and operations are very specific to database development and management and the environment in which those databases reside. In the early days of DevOps, many viewed the database as existing in a separate world, and both sides of the aisle preferred to keep it that way.

One of the significant differences between application and database deployments comes down to data persistence. With applications, it’s much easier to update or replace code because there’s no data to manage along the way. With databases, however, you can’t simply overwrite schemas or copy changes from one environment to another without pulling the data along with you. Careful consideration must be given to how changes impact existing data and how to preserve that data when changes are made. Otherwise, you risk data loss, integrity issues, or privacy and compliance concerns.

Databases also bring with them size and scalability issues that differ from application code. Implementing schema changes on a massive database in a production environment can be a significant undertaking and lead to performance issues and degraded services. Database changes become even more complicated if the database is being accessed by multiple applications, especially if no single application has exclusive ownership.

Perhaps the biggest hurdle to implementing database DevOps has been a siloed mindset that makes it difficult for various players to come together in a way that makes integration possible. According to the Redgate report, only 22% of the respondents said that their developers and DBAs were “great” at working together effectively as a team, and without a team-focused attitude, an effective DevOps strategy is nearly impossible to implement.

Despite these challenges, however, database DevOps has made important inroads in recent years. DevOps tools now better accommodate databases, and database tools better integrate with DevOps systems. Attitudes, too, are beginning to change as more participants come to understand the value that incorporating databases can offer. Although database DevOps is still a challenge, many teams have now demonstrated that application and database delivery can indeed be a unified effort.

Moving Ahead with Database DevOps

To make DevOps work, the database team must adopt the same principles and technologies as those used by the application team when delivering software. Figure 1 provides an overview of what the database DevOps process might look like when integrated into the application delivery pipeline.

word image 41 Introduction to DevOps: DevOps and the Database

Figure 1. Integrating database deployment into the DevOps process

The first step is to store all database code in source control (also referred to as version control). A source control solution provides the foundation on which all other DevOps operations are based.

With source control, every developer works from the one source of truth, resulting in fewer errors and code conflicts. Source control also tracks who made what changes and when those changes were made. In addition, source control maintains a version history of the files, making it possible to roll back changes to previous versions. Source control also makes it easier to repeatedly build and update a database in different environments, including QA and development.

Database teams should store all their database code in source control. This includes the scripts used to build the databases as well as the migration (change) scripts that modify the database or data. For example, source control should be used for scripts that create, alter, or drop database objects—such as tables, views, roles indexes, functions, or stored procedures—as well as any scripts that select, insert, update, or delete data. The teams should also store static data, such as data used to populate lookup tables, as well as configuration data, when appropriate.

Once all the code and data are in source control, the database deployment operations can be incorporated into the same DevOps continuous integration (CI) and continuous delivery processes used for application deployments. When a developer checks in database code, it triggers an automated build operation that runs alongside the application release process, making it easier to coordinate database and application deployments.

When code is checked into the source control repository, the CI service kicks in and runs a series of commands that carry out such tasks as compiling source code and running unit tests. If a database is part of the deployment, the CI service tests and updates the database—or alerts developers to errors. If problems are discovered, developers can fix them and resubmit the code, which relaunches the CI process.

To incorporate databases into CI, a team might turn to a tool such as Redgate’s SQL Change Automation, which can integrate with any CI server that supports PowerShell. Redgate also provides extensions for CI products such as Jenkins, TeamCity, or Azure DevOps for enabling database integration.

When the CI service processes the SQL code, it produces an artifact that includes the deployment script necessary to update the applicable database objects and static data. The artifact also contains details about the deployment process, including a diff report that shows what has changed in the database. The artifact represents a specific, validated version that can be used as a starting point for the database release process.

At this point, the artifact is deployed against a staging database that is ideally an exact copy of the production database. Here DBAs can verify and confirm the changes to ensure the staging database is production-ready. If necessary, they can also make additional changes. Out of this process, a final deployment script is generated, which can then be passed down the pipeline for deployment against the production database.

Not surprisingly, the exact approach to database delivery is much more involved than what I’ve described here, especially when it comes to migration script versioning, and that process can vary significantly depending on the tools being used and how those tools are configured.

What this does demonstrate is that database DevOps is indeed doable, providing database teams with a process that automates repetitive deployment and testing operations. With DevOps, teams can deliver smaller releases that are easier and faster to deploy, while having in place a consistent, steady mechanism for ongoing database deployments that work in conjunction with application delivery.

Testing and Monitoring

One of the most important aspects of the application delivery pipeline is ongoing testing. Changes to the database should undergo rigorous testing before the release reaches the staging environment. In this way, developers learn of problems quickly and early in the development process. When issues are discovered, they’re immediately alerted so they can check fixes into source control and keep the development effort moving forward.

Developers and DBAs should invest the time necessary to write comprehensive tests that provide for as many scenarios as possible. Many of these tests will be based on frameworks specific to database testing. For example, tSQLt is a popular open-source framework for SQL Server that lets you write unit tests in T-SQL. Regardless of the framework, the goal is the same: to identify as many issues as possible before database changes make it to production.

Unit tests offer your first line of defence against problem SQL code. Because they run when the code changes are checked into source control, they provide the fastest response to possible issues. A unit test is a quick verification of specific functionality or data, such as ensuring that a user-defined function returns the expected result. A unit test should be concise and run quickly, producing a single binary value—either pass or fail.

Also important to the application delivery pipeline is ongoing monitoring, which needs to be as comprehensive as the testing processes themselves. Monitoring can be divided into two broad categories: the feedback loop and system monitoring.

A DevOps application delivery pipeline includes a continuous feedback loop that provides participants with ongoing details about the delivery process. For example, when the CI service notifies developers about problems in their code, those alerts can be considered as part of the feedback loop. Development teams (application and database) should have complete visibility across the entire pipeline. Not only does this help identify issues with the application and database earlier in the delivery cycle, but it also helps to monitor the delivery process itself to find ways to improve and streamline operations.

System monitoring is more typical of what you’d expect of database operations. Once the changes have been deployed to production, you must closely monitor all related systems to check for unexpected issues and ensure that systems are running as they should, before users or data are seriously impacted. To this end, DBAs should monitor database systems for performance and compliance, taking into account regional differences and regulatory requirements. Developers and DBAs alike should have a real-time understanding of issues that might need immediate attention or those that should be addressed in the foreseeable future.

Testing and monitoring also play a critical role in ensuring a database’s overall security. Developers, IT administrators and DBAs must take into account security considerations throughout the entire application lifecycle, addressing issues that range from SQL coding to server security in order to ensure that data is protected at every stage of the application delivery process.

Proper testing and ongoing monitoring can go a long way in helping to enforce these protections. By using source control and automating the delivery process, organisations have a process that is more predictable and easier to manage, while providing an audit trail for tracking potential issues. Database teams still need to ensure proper configurations and secure environments, but an effective DevOps pipeline can also play an important role in your security strategy.

The New Era of Database DevOps

Database DevOps offers the promise of quicker, easier and more secure deployments while bringing application and database development efforts in line with one another. But DevOps is not a one-size-fits-all solution to application delivery, nor is it meant to be a developer-first strategy that leaves DBAs and IT administrators behind.

Developers, DBAs, and operation professionals must work together to implement an application delivery strategy that takes into account the needs of all participants, with the goal of delivering the best applications possible as efficiently as possible in the shortest amount of time. Anything less and your database DevOps efforts are destined to fail.

Let’s block ads! (Why?)

SQL – Simple Talk

Read More

Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines – Part 1.5

May 25, 2019   Microsoft Dynamics CRM

In our last blog we learned why it is important to version control our solutions and explored how to pack a solution from a repository for deployment to downstream environments. Now it’s time to update our script a bit to take advantage of some cool new features and get ready for our next blog. During the Microsoft Build 2019 developer conference, multi-stage pipelines were announced enabling us to create a full CI/CD pipeline in YAML, which will allow us to properly perform continuous deployment on our solutions.

For a deeper look into changes announced during the Build conference check out the What’s new with Azure Pipelines blog and the announcement for YAML Release in Azure Pipelines session at Build.

Rather than re-hash the basics and everything else we’ve done to this point I will urge you to check out the first blog in the series. In this blog, we will look at the new pipeline features, add stages and complete a solution deployment using multi-stage YAML pipelines. The goal, for now, being to automate solution deployment from our source control management (SCM) system as highlighted in the flow diagram below. In the future, we will look at adding more automation and explore different concepts.

 Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines   Part 1.5

Contents

Getting started

If multi-stage pipelines are not enabled by default when you read this you will first need to enable the preview feature. This can be accomplished using the following steps.

  1. Navigate to your Azure DevOps instance (e.g. https://dev.azure.com/example/project)
  2. Click your profile icon in the top right of the page.
  3. In the drop-down click Preview features
  4. In the Preview feature pane toggle Multi-stage pipelines
  5. Close the pane and you are all set to go.

 Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines   Part 1.5

Updating our build script for a multi-stage setup

Below you will find the complete script from the first blog that enables us to deploy any unpacked solution stored in version control. The difference is that this time the script has some new syntax to enable the code to be used in multiple stages. The notable changes in the section are the addition of the stages and job schema. Stages are collections of jobs that will allow us to logically divide our pipeline between various continuous integration and continuous deployment processes. Jobs are collections of steps that will help us logically divide work within stages. In both cases, we can set dependencies and conditions on other processes or run in parallel.

If you are following along from the first blog I have called out the changes that have been made. Otherwise, follow the steps below to implement the updated solution deployment script using a multi-stage pipeline. If you haven’t unpacked your solution and checked it into version control go back to the first blog for more information. Alternately, you can fork the tutorial repository on GitHub and use the Lesson-1.5 folder for setup.

If you are starting from scratch or have forked the tutorial repository follow the steps below to create a new pipeline.

  1. Navigate to your Azure DevOps project repository. For example, https://dev.azure.com/{username}/D365-CE-DevOps-Tutorial
  2. Hover over Pipelines, then click Pipelines in the fly-out menu.
  3. Click New, then click New Pipeline
  4. On the next screen, we will select the location of our unpacked solution: Azure Repos, GitHub, or another service. Note that choosing GitHub or other services requires that you authorize Azure DevOps to access the repository, the behavior otherwise is the same in our build pipelines.
  5. Select the repository containing the unpacked solution files.
  6. Configure pipeline using the Starter pipeline option and copy/paste the script below replacing the starter script. If you have forked the repo on GitHub choose the existing YAML script in the Lesson-1.5 directory.
  7. Click Save and run
  8. Enter a commit message and click Save and run again
  9. Click Cancel to stop the build
  10. Click the ellipsis (…) in the top right
  11. In the drop-down, click edit pipeline. We will need to add some variables to make things function correctly. follow the steps to create pipeline variables below to continue.

name: $  (BuildDefinitionName)-$  (Date:yyyyMMdd).$  (Rev:.r)

trigger:
- master

stages:

 - stage: Build

   jobs:

    - job:
      displayName: "Pack Solution from repository"

      pool:
        vmImage: 'vs2017-win2016'

      steps:
      - script: md tools
        displayName: 'Create tools directory'

      - powershell: |
          Invoke-WebRequest `
            -Uri https://dist.nuget.org/win-x86-commandline/latest/nuget.exe `
            -OutFile tools\nuget.exe
        displayName: 'Download nuget.exe'

      - powershell: |
          tools\nuget.exe install Microsoft.CrmSdk.CoreTools -O tools
          md "tools\CoreTools"
          $  coreToolsFolder = Get-ChildItem tools | Where-Object {$  _.Name -match 'Microsoft.CrmSdk.CoreTools.'}
          move "tools\$  coreToolsFolder\content\bin\coretools\*.*" "tools\CoreTools"
          Remove-Item "tools\$  coreToolsFolder" -Force -Recurse
        displayName: 'Install CoreTools'

      # Don't forget to add the env variables to your pipeline settings
      - powershell: |
          Start-Process tools/CoreTools/SolutionPackager.exe `
          -ArgumentList `
            "/action: Pack", `
            "/zipfile: $  (Build.ArtifactStagingDirectory)\packedSolution$  env:SolutionName.zip", `
            "/folder: $  env:SolutionPath", `
            "/packagetype: Both" `
          -Wait `
          -NoNewWindow
        env:
          SolutionPath: $  (solution.path)
          SolutionName: $  (solution.name)
        displayName: 'Solution Packager: pack solution'          

      - task: PublishBuildArtifacts@1
        inputs:
          pathtoPublish: $  (Build.ArtifactStagingDirectory)
          artifactName: drop
        displayName: 'Publish build artifacts'

If you do not want the build to automatically run on commits to master set trigger to none.

Schema changes

Note that the only change above to the original script is the following 5 lines and an adjustment to the indentation level of code following our additions.

stages:
 - stage: Build
   jobs:
    - job:
      displayName: "Pack Solution from repository"

Review the Microsoft Docs page for Jobs for examples of how to setup jobs with varying degrees of complexity and the Stages page for similar examples.

Steps to create pipeline variables

For our pipeline to be able to access the variables defined in code, we will need to manually create the variables in our pipeline settings using the following steps:

  1. Click the ellipsis (…) on the top right of the page and in the drop-down click Variables
  2. Click + Add in the pipeline variables view to add a new variable.
  3. Perform step 3 two times to add the following variables
    • name: solution.name
        • value: <desired zip file name. In this example, contosoUniversity>
    • name: solution.path
        • value: <path to repository location of extracted solution. in this example, Lesson-1.5/ExtractedSolution/>
  4. Checkmark both as Settable at queue time
  5. Click the Save & queue drop down and click Save
  6. Enter a comment and click Save

 Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines   Part 1.5

You may now run the script setting the variables for solution name and solution path if you have not defined them in your variable settings. The result should be a new build artifact named drop that contains you packed solution as both managed and unmanaged.

Adding the release stage

Now that we have completed our build stage we can create a new stage to consume and deploy the solution artifact we published earlier. For added utility, we will use a deployment job to track deployments to our downstream environments. For this example, our release stage will be dependent on the success of the build stage using dependencies and conditions. Note that by default stages are sequential so this change is more for demonstration than necessity in this example.

- stage: Release

   dependsOn: Build
   condition: succeeded('Build') 

   jobs:

    - deployment: Deploy
      displayName: "Import solution artifact"

      pool:
        vmImage: 'vs2017-win2016'

      environment: test
      
      strategy:
        runOnce:
          deploy:
      
            steps:

Schema explanation

  • deployment - a deployment is a special type of job focused on deploying apps to your environments allowing you to track deployments more granularly.
  • environment - the name of the environment we wish to deploy to. Note that if you provide an environment name for an environment that you have not explicitly created yet one will be automatically created without associated resources, which is what we want in this case.
  • strategy - at the time of writing only the runOnce strategy is available, this step will run the deployment exactly one time.
  • deploy - the entry point to our deployment steps.

Download artifacts

Continuing on with our script we will need to download the solution artifact we publisher earlier. The reason we need to download the artifact is because we have logical separation using jobs in which artifacts are not shared across boundaries.

          - task: DownloadBuildArtifacts@0
              inputs:
                buildType: 'current'
                downloadType: 'single'
                artifactName: 'drop'
                downloadPath: '$  (System.ArtifactsDirectory)'

*Note that indentation has been left for copy/paste purposes. If you have issues with the script check your indentation.

While this snippet of code is very simple I will demonstrate adding it via task assistant in the YAML editor as it can be very handy for adding pre-made tasks in a quick and efficient manner.

  1. Open your pipeline editor
  2. On the right, you should see a task pane. If not, click Show assistant in the top right of the editor window.
  3. In the search box type download build artifacts
  4. Click the Download Build Artifacts task
  5. Enter your artifact name (e.g. drop)
  6. Ensure that you have clicked the line in your editor where you would like to have the task added.
  7. Click Add
  8. Update indentation as needed.
  9. Save your pipeline and run to test your changes.

 Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines   Part 1.5

Import solution into target environment

The only change to our actual deployment code from the first blog other than moving to a release stage and using deployment jobs is that we are now importing asynchronously. Importing asynchronously will help us avoid import timeout issues as noted by readers of the first blog.

            - powershell: Install-Module Microsoft.Xrm.Data.Powershell -Scope CurrentUser -Force
              displayName: 'Install Microsoft.Xrm.Data.PowerShell'

            # Don't forget to add the env variables to your pipeline settings
            - powershell: |
                $  connection = Get-CrmConnection `
                  -ConnectionString `
                    ("AuthType = Office365;" + `
                    "Username = $  env:ServiceAccountUpn;" + `
                    "Password = $  env:ServiceAccountPassword;" + `
                    "Url = https://$  env:EnvironmentName.crm.dynamics.com")

                Import-CrmSolutionAsync -BlockUntilImportComplete `
                  -conn $  connection `
                  -SolutionFilePath $  (System.ArtifactsDirectory)\drop\packedSolution$  ($  env:SolutionName)_managed.zip
              env:
                EnvironmentName: $  (environment.name)
                SolutionName: $  (solution.name)
                ServiceAccountUpn: $  (serviceAccount.upn)
                ServiceAccountPassword: $  (serviceAccount.password)
              displayName: 'Import solution'

*Note that indentation has been left for copy/paste purposes. If you have issues with the script check your indentation.

Add deployment variables

Important - We have added some environment variables. We will need to edit our pipeline settings once more following the steps to create pipeline variables section to add the three new variables below.

  • name: environment.name
    • value: <Dynamics 365 CE org name e.g. contoso.crm.dynamics.com, name only>. If your org is in another geo update the YAML script accordingly.
  • name: serviceAccount.upn
    • value: example@contoso.onmicrosoft.com
  • name: serviceAccount.password
    • value: hopefully not hunter2
    • For the password variable be sure to select the lock symbol to change the type of the field to secret.

That’s it, now click Save to commit your changes, then click Run to start the pipeline. Once started you will be redirected to the pipeline summary page to see the action in real-time. Click on the various stage cards to view details. Once the build is completed you can click Environments on the left navigation pane to view past deployments to the environment we created earlier.

If you would like to use some pre-made tasks and leverage the task assistant we discussed in the download artifacts section check out Dynamics 365 Build Tools.

In our upcoming blog(s) we will explore templates to make our pipeline code reusable and look at some new tooling to improve our continuous integration process.

Additional Resources

Let’s block ads! (Why?)

Dynamics CRM in the Field

Read More

Introduction to SQL for Cosmos DB

March 12, 2019   BI News and Info

One of the interpretations of the term NoSQL is “Not Only SQL.” Whether this is a factual statement, or an aloof remark is open to debate. However, no one can deny that more and more non-relational data sources are being used in a range of environments. This often leads to both SQL and NoSQL technologies being used side by side despite the differences in both the structures of the data itself and the engines that store the information.

The inevitable challenge for traditional database developers and DBAs is the investment in time and effort required to master a new set of technologies and any new languages required to handle NoSQL data. Fortunately for SQL developers (and indeed for anyone with a decent grounding in SQL) the developers of Cosmos DB have spared a thought for the millions of SQL users who need a path into the brave new world of NoSQL. They have achieved this by providing an SQL API to access JSON-formatted documents stored in Cosmos DB.

This approach brings one instant and overarching advantage for SQL Server (or other relational database) programmers: you are instantly at home with the core language used to extract data.

However, JSON documents are far removed from relational structures and NoSQL document stores are very different beasts compared to relational databases. Consequently, the SQL used to query JSON documents is different in many ways to the conventional SQL that you use with SQL Server. Moreover, Cosmos DB SQL is severely limited compared to T-SQL Yet despite its restrictions, the Cosmos DB SQL API provides an easy path to understanding and exploiting document databases. Acquiring a working knowledge of how to query Cosmos DB could open up new horizons that enable you to integrate data from the relational and NoSQL worlds.

The Basics

Cosmos DB is a multi-model NoSql database. Currently it can handle three types of non-relational data:

  • Document databases
  • Graph databases
  • Key-value databases

Only one of these data models can be queried using SQL in Cosmos DB. This is the document database. Indeed, this is probably a good place to add that Cosmos DB SQL only concerns querying document databases. There is no DDL (data definition language) involved.

A document database is a non-relational database designed to store documents that have no fixed structure. The leading document databases use JavaScript Object Notation (JSON) as the format for structuring the data. The good news with a lightweight and easily modifiable approach like this, is that you are not bound by a rigid schema. The bad news is that you are not bound by any schema at all. This fluidity has many sterling advantages but comes at a cost when querying the JSON data. The two main challenges that you are likely to face as a SQL developer are:

  • Schema on read – Instead of the database schema being part of the database structure, any required structure is defined when the query is written.
  • Nested structures – JSON documents are objects that can contain the complete data describing a self-contained unit. They can be extremely complex and represent a structure that would require a series of tables in a SQL Server database.

Fortunately, Cosmos DB SQL has been adapted to help you overcome the challenges inherent in using a relational database query language to query non-relational data stored in the nested structures of JSON-as you will see in the subsequent article.

When Would you Need Cosmos DB SQL?

Before leaping in to the minutiae of a new or at least a slightly different-dialect of a programming language, you may be forgiven for wondering why you should need to make the effort to master it in the first place. A few potential reasons are:

  • Cosmos DB can store JSON documents at a scale that is truly impressive, and that makes it into an excellent repository when dealing with terabytes of JSON files. SQL Server’s capabilities as a JSON document store are completely overshadowed by Cosmos DB.
  • Cosmos DB can become an ideal complement to SQL Server as a JSON storage service. This is because SQL Server provides analytical capacities that are missing from Cosmos DB. In practice, you can extract finely filtered data from a vast store of documents in Cosmos DB and then load this (infinitely smaller dataset) into SQL Server tables as rowsets for in-depth analysis. This can avoid having to implement a totally different set of technologies to deliver analytics over JSON document stores.
  • You can use PolyBase in SQL Server 2019 to connect to Cosmos DB collections. In this case you will probably need some basic Cosmos DB SQL to flatten the JSON data so that it can be used as an external table.
  • Even if you rely only on Cosmos DB to analyze JSON data you will likely need this Cosmos DB flavor of SQL when writing JavaScript-based stored procedures and user-defined functions.
  • If you are connecting to Cosmos DB using ODBC you are likely to need to flatten the JSON data. This approach will inevitably require the use of Cosmos DB SQL.
  • You can use Cosmos DB SQL SQL API (and SQL over JSON document collections) as the data source in Azure Data Factory activities.

There are, doubtless many other reasons, but I hope that some of these will encourage you to start looking at Cosmos DB.

The Required Toolkit

You need one of two things to practice SQL in Cosmos DB:

  • A Cosmos DB account in Azure
  • The Cosmos DB Emulator which is beautifully explained in this Simple-Talk article.

For the purposes of this article, I will presume that you are using the Cosmos DB Emulator-although either the emulator or Cosmos DB in Azure will work equally well when learning to query JSON documents using SQL.

Sample Data

As this article is a gradual introduction to querying Cosmos DB documents, you will be using simple JSON documents stored in a single collection in Cosmos DB.

The JSON structure that you can use to practice the basics of SQL queries using Cosmos DB is completely flattened, and looks like this:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

{

   “CountryName”:“United Kingdom”,

   “MakeName”:“Ferrari”,

   “ModelName”:“Testarossa”,

   “Cost”:52000.0000,

   “RepairsCost”:2175.0000,

   “PartsCost”:1500.0000,

   “TransportInCost”:750.0000,

   “Color”:“Red”,

   “SalePrice”:65000.00,

   “LineItemDiscount”:2700.00,

   “InvoiceNumber”:“GBPGB001″,

   “SaleDate”:“2015-01-02T08:00:00″,

   “CustomerName”:“Magic Motors”,

   “SalesDetailsID”:1

}

Using the Sample Data

The sample documents are in the attached zip (CosmosDBQueries.zip) file which you should install onto your C: drive in the C:\CosmosDB directory. The seven documents that make up the collection are in the simplecars subdirectory.

The first thing to do is to create a collection. I suggest using /CountryName as the partition key in this example. As creating databases and partitions is explained in the article referenced above, I will not go through it again it again here. Then you need to load the documents. As there are only a handful of them, you can just click the New Document button and paste the contents of each file into a separate document, and then save it.

There is also the Upload option for loading multiple files which you can use instead. However, this does not seem to work at the moment in the Cosmos DB Emulator.

Of course, in the real world, there are solutions that you will need to learn if you are loading hundreds of thousands of documents and terabytes of data. However, these techniques are out of scope for this simple introduction.

Basic Terminology

For the sake of clarity, there are a few basic definitions that should help you bridge the gap between SQL and the world of JSON documents:

  • A collection can be considered to be a database
  • A document equates broadly to a recordset (although the nested structure makes it more similar to XML)
  • An attribute is a field or column

These points of comparison are not destined to be taken too literally and are simply provided as an initial stepping stone to help with your initial understanding if you have never seen document databases before.

Basic SQL Queries

Begin with the simplest possible example. With the Cosmos DB Emulator running, enter and execute the following query:

SELECT * FROM C

You will see all the documents in the current collection returned as the output, each one similar to the sample document shown above.

As simple as this query may be, as it is the first query that you have created with Cosmos DB SQL a few comments are in order. Firstly, the data is returned as JSON, even if you are using SQL to query the documents. In effect, JSON is the data format. A second point is that you cannot specify the collection to query in the SQL. Consequently, you need to ensure that you open a query window from the required collection. However, you can use anything as the definition of the data source. The query could read:

SELECT * FROM thecurrentcollectionbecauseIlikedreamingaboutcars

When running simple queries, the source JSON is returned as it exists in the collection. You will learn how to shape the output JSON later in this article and in the second article in this series.

A final point to note is that, technically, you can also return a complete JSON document with this SQL:

SELECT * FROM ROOT

Now to be a little more selective, try this short query:

SELECT   saleselements.InvoiceNumber

        ,saleselements.TotalSalePrice

FROM    saleselements

This is the output you should see the following (here, obviously, is a truncated view of the output):

{

        “InvoiceNumber”: “GBPGB001″,

        “TotalSalePrice”: 65000

    },

    {

        “InvoiceNumber”: “GBPGB011″,

        “TotalSalePrice”: 89000

    }

Executing this query only returns two of the available JSON attributes. What is worth noting here is that you must use the collection name or alias when referencing attributes. It is generally easier to use short aliases for the collection, like this:

SELECT s.InvoiceNumber, s.TotalSalePrice  

FROM saleselements AS s

The AS keyword when aliasing collections is optional. Attribute names are case-sensitive, although misspelling them will not stop the query executing it will prevent the attribute from being returned in the output.

Of course, you can add aliases to attributes:

SELECT s.InvoiceNumber, s.SalePrice AS Price

FROM   saleselements AS s

Doing this will deliver the following JSON:

    {

        “InvoiceNumber”: “GBPGB011″,

        “Price”: 8500

    }

However, when aliasing attributes you need to remember that:

  • The AS keyword is optional
  • Aliases are case-sensitive.
  • Aliases must respect JavaScript naming conventions – you cannot start an alias with a number or other non-alphabetical character except the dollar sign or underscore

You cannot add an alias to * so you cannot write

SELECT c.* FROM c

I realize that the JSON specification calls attributes “members” and the data for each member an element, yet the use of the word attribute to describe the name of a piece of data is so prevalent I prefer to use it.

Cosmos DB SQL will return strings, numbers and dates exactly as they are stored in the JSON document – as you can see if you try this query:

SELECT   s.InvoiceNumber

         ,s.SaleDate

         ,s.SalePrice

FROM     saleselements AS s

The result will be:

{

        “InvoiceNumber”: “GBPGB011″,

        “SaleDate”: “2015-04-30T00:00:00″,

        “SalePrice”: 8500

}

Of course, Cosmos DB SQL can perform basic arithmetic:

SELECT   s.InvoiceNumber

         ,s.SalePrice - s.Cost AS GrossProfit

FROM     saleselements AS s

As you can see below:

{

        “InvoiceNumber”: “GBPGB011″,

        “GrossProfit”: 1700

}

As a variation on a theme, you can always write SELECT clauses like this:

SELECT   s[“InvoiceNumber“]

         ,s[“SaleDate“]

FROM     saleselements AS s

Indeed, you can mix and match the ‘alias dot attribute’ notation and the ‘alias square bracket and double quote’ notation (in which case there are no dots used to link the alias and the attribute name) inside the same query.

Simple WHERE Clauses

Time to move on to data selection. After all, Cosmos DB can store terabytes of JSON documents and allows you to scale queries to use more or less processing power so that you can balance the requirements of query time and cost (in the financial sense) to suit each individual requirement.

A Basic Text Filter

Suppose that you want to isolate all invoices where a Bentley was sold. This should not tax your SQL knowledge, and hopefully will reassure you that Cosmos DB SQL cleaves to basic SQL tenets:

SELECT   s.InvoiceNumber ,s.SalePrice

FROM     s

WHERE    s.MakeName = “Bentley“

You should see a result like the following:

{

        “InvoiceNumber“: “GBPGB011“,

        “SalePrice“: 80500

}

Yes, you are using double quotes to enclose a string. This may come as a surprise to SQL Server developers (although possibly less so to users of other databases). To be fair, you can also use single quotes if you feel that you are otherwise betraying your SQL heritage.

Numeric Filters

Filtering on numbers in JSON attributes is probably what you would expect it to be. You could write a WHERE clause like this one to specify a precise figure:

SELECT   s.InvoiceNumber ,s.SalePrice

FROM     s

WHERE    s.LineitemNumber = 2

Or like this one to define a numeric range:

SELECT   s.InvoiceNumber ,s.SalePrice

FROM     s

WHERE    s.Cost BETWEEN 50000 AND 100000

Here, the result will be:

{

        “InvoiceNumber”: “GBPGB011″,

        “SalePrice”: 8500

}

And now is the disconcerting part. You can filter on a field and include it in the SELECT clause at the same time-like this (although this will indicate the filter validity in the results):

SELECT   s.InvoiceNumber, s.Cost BETWEEN 50000 AND 100000

           AS InCostRange, s.SalePrice

FROM     s

This query will give the following result:

{

        “InvoiceNumber”: “GBPGB011″,

        “InCostRange”: false,

        “SalePrice”: 8500

}

To conclude the subject of elementary WHERE clauses, you can also reassure yourself that:

  • The AND, OR, NOT operators function pretty much as they do in T-SQL
  • =, !=, >=, <=, <> operators allow you to specify threshold values in queries
  • You can use parentheses to create more complex query logic

Alphabetical Ranges

If you ever need to return documents based on an alphabetical range in an attribute, then one way of obtaining the desired result is to use this kind of SQL:

SELECT   s.CustomerName ,s.SalePrice

FROM     saleselements AS s

WHERE    SUBSTRING(s.CustomerName, 0, 1) BETWEEN “A“ AND “M“

Date and Time Filters

Cosmos DB SQL does allow for some more advanced filtering methods in the WHERE clause. Although the possibilities are, it has to be said, considerably more limited than those offered by T-SQL, you can, nonetheless, include (and exclude) irrelevant JSON documents by filtering on date and time elements.

JSON does not have a date type, as such. Instead it uses a string. Consequently, you risk encountering dates in any of a myriad of formats. The upshot is that you will be delving into the specific string format to filter on dates and times.

The format in the sample data is in something close to the ISO 8601 format, which looks like this: YYYY-MM-DDTHH:MM:SS. This means that searching for a specific date and time means representing the datetime as an ISO string, in this way:

SELECT s.SaleDate

FROM   s

WHERE  s.SaleDate = “2015-01-02T08:00:00“

If you are looking to set an upper or lower boundary for a date or time you can use the standard comparison operators: >=, <=, <> or !=.

Using Date Ranges

It follows that defining a date range requires nothing more than a simple AND operator in the WHERE clause.

SELECT s.SaleDate

FROM   s

WHERE  s.SaleDate >= “2015-01-01T00:00:00“

       AND

       s.SaleDate <= “2015-01-31T23:59:59“

This returns data from three documents:

1

2

3

4

5

6

7

8

9

10

11

[

    {

        “SaleDate”: “2015-01-02T08:00:00″

    },

    {

        “SaleDate”: “2015-01-25T00:00:00″

    },

    {

        “SaleDate”: “2015-01-02T10:33:00″

    }

]

Or, possibly more elegantly, you can use the standard BETWEEN … AND construction:

SELECT s.SaleDate

FROM   s

WHERE  s.SaleDate BETWEEN “2015-01-01T00:00:00“

       AND “2015-01-31T23:59:59“

This returns data from the same three documents:

1

2

3

4

5

6

7

8

9

10

11

[

    {

        “SaleDate”: “2015-01-02T08:00:00″

    },

    {

        “SaleDate”: “2015-01-25T00:00:00″

    },

    {

        “SaleDate”: “2015-01-02T10:33:00″

    }

]

Applying Time Filters

Filtering by time essentially means extracting the hour, minute and possibly second or millisecond elements from the date string. So, to find all cars sold between 8:45AM and 12AM you would write:

SELECT s.SaleDate

FROM   s

WHERE  SUBSTRING(s.SaleDate, 11, 2) BETWEEN “08:45“ AND “12:00“

This should return data from two documents in the collection:

[

    {

        “SaleDate”: “2015-02-03T10:00:00″

    },

    {

        “SaleDate”: “2015-01-02T10:33:00″

    }

]

The lack of a schema in the JSON documents means that the date could be specified in a completely different string format, or even as a number (say in the YYYYMMDD format where the data shown above would be 20150131).

Clearly the requisite queries would have to be adapted to the date format used, in which case a theoretical query on a totally different collection (where the dates were stored differently) could read:

SELECT s.SaleDate

FROM   s

WHERE  s.SaleDate BETWEEN 20150101 AND 20150131

Yet again the ‘free-form’ nature of JSON means that you will have to display both ingenuity and mental agility in the queries that you write.

Traps for the Unwary

Cosmos DB SQL requires you to wrap non-standard attribute identifiers in double quotes (and square brackets). As I mentioned above, no dot notation is used for the collection alias in this case. If you want to output one of the collection’s built-in attributes, you need to write SQL like this:

SELECT s.InvoiceNumber ,s.SalePrice, s[“_rid“]

FROM   saleselements AS s

It is also worth noting that you cannot use unauthorized elements such as leading numbers in aliases even when you enclose the alias in double quotes.

When filtering on texts that contain special characters you need to know that certain characters are escaped in Cosmos DB SQL. Consequently, this is what you need to enter to include these characters in a WHERE clause:

  • Single quote: \’
  • Double Quote: \”
  • Backslash: \

There are, of course, many others, but I will let you delve into the documentation to find them.

Now that the basics have been covered (and hopefully you are reassured that your SQL skills can be applied to document databases) it is time to look at some aspects of Cosmos DB SQL in greater depth.

Sorting Output

Cosmos DB SQL also contains the ORDER BY clause, so you can write queries like this one:

SELECT   s.InvoiceNumber ,s.SalePrice AS Price

FROM     saleselements AS s

ORDER BY s.InvoiceNumber

It probably comes as no surprise that you will see a result set something like the following:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

{

        “InvoiceNumber”: “EURDE004″,

        “Price”: 11500

    },

    {

        “InvoiceNumber”: “EURFR005″,

        “Price”: 19950

    },

    {

        “InvoiceNumber”: “GBPGB001″,

        “Price”: 65000

    },

    {

        “InvoiceNumber”: “GBPGB002″,

        “Price”: 220000

}

However, at this juncture a series of limitations appear on the horizon. For, although you can add the ASC / DESC keywords to an ORDER BY clause (as you can in T-SQL) the following restrictions apply here:

  • The ASCENDING and DESCENDING keywords are not supported
  • Sorting on multiple attributes is not possible
  • You cannot sort on an alias for an attribute – you must reference the attribute preceded by the collection alias and a dot as you do in the SELECT clause
  • You cannot order by anything other than a field name
  • You cannot sort by a computed value

Summarizing Results

Here are several examples for common aggregates:

Finding the Number of Documents in a Collection

The query to return the number of documents in a collection is a slight extension of standard SQL:

SELECT VALUE COUNT(1)

FROM         s

Here you are adding the VALUE keyword to return a number only and not a JSON structure. Consequently, the result looks like this:

[    7

]

You could also write this query as:

SELECT VALUE COUNT(s.id)

FROM s

Very Basic Aggregations

If you want to aggregate data from a collection, you could try a query like this one:

SELECT VALUE SUM(s.SalePrice)

FROM         saleselements AS s

The output is extremely simple:

[

    424950

]

However, some fairly profound restrictions appear here (at least with the current version of Cosmos DB).

  • There can only be one aggregation per query
  • No alias can be added to the aggregation if you are using the VALUE keyword
  • You will get an error if the query is a cross-partition query, and you do not use the VALUE keyword. Cosmos DB partitions (as you might expect) are a core storage and query acceleration design approach. However, creating partitions is a subject in its own right that I cannot go into here

Intriguingly, you can get around the forced use of the VALUE keyword when querying across partitions with the use of a subquery like this:

SELECT * FROM

(SELECT  SUM(s.SalePrice) FROM s)

In this case, the output looks something like this:

[

    {

        “$ 1″: 424950

    },

    {

        “$ 1″: 0

    }

]

You are probably wondering where the $ 1 attribute names come from. This is, quite simply, what happens when you do not use an alias for a subquery or a function in Cosmos DB SQL. Think of it as being similar to non-aliased columns in T-SQL appearing as (no column name) in the query output.

Using a subquery also allows you to use multiple aggregate values in a calculation. In the following example you can see the difference between the maximum and average sale prices:

SELECT * FROM

(SELECT VALUE MAX(s.SalePrice) - AVG(s.SalePrice)

FROM s)

As you can see, you do not necessarily have to alias the subquery:

[

    159292.85714285716

]

Of course, you can filter the data set that the calculation is applied to:

SELECT * FROM

(SELECT VALUE MAX(s.SalePrice) - AVG(s.SalePrice)

FROM s WHERE s.MakeName = “Ferrari“)

This gives the corresponding output:

[

    77500

]

As you can see here, applying the VALUE keyword returns just that-a value-and consequently there is no attribute name in the output.

Shaping Output

Cosmos DB SQL allows you to shape the output of an SQL query in several ways – pretty much as ‘ordinary’ SQL does. Some of the techniques that you can apply are described in this section.

Returning the Top N Documents

Cosmos DB SQL imitates T-SQL when it comes to returning only a specified number of documents from a collection. This one does not even need me to show you the result of the query.

SELECT   TOP 3 s.InvoiceNumber, s.SalePrice

FROM     s

ORDER BY s.SalePrice DESC

Concatenate Attributes

The Cosmos DB SQL CONCAT() function is simple yet powerful. Take a look at the following example:

SELECT   CONCAT(s.CustomerName

               ,“ Date of Sale: “

               ,s.SaleDate) AS CustomerName

FROM     s

This function allows you to join multiple fields and/or static values to produce a single output attribute-like this:

{

“CustomerName”: “Wonderland Wheels Date of Sale: 2015-04-30T00:00:00″

    },

{

“CustomerName”: “Wonderland Wheels Date of Sale: 2015-04-30T00:00:00″

    }

Replacing Values in the Output

Should you need to replace specific values in the resulting JSON, you can use the REPLACE() function. And, yes you can use multiple nested replace operations if you need to.

SELECT   REPLACE(

                 REPLACE(s.CountryName, “United Kingdom“, “Blighty“)

                 ,“France“, “Hexagone“) AS Country

FROM     s

Here is the corresponding query output (from the final objects returned by the query):

    {

        “Country”: “Blighty”

    },

    {

        “Country”: “Germany”

    },

    {

        “Country”: “Hexagone”

    }

String Functions – Simple Classics

Cosmos DB SQL has most of the basic string functions that you could require. Some of these are used in the following query:

SELECT   UPPER(s.CustomerName) AS CustomerName

        ,LOWER(s.CountryName) AS CountryName

        ,TRIM(s.CountryISO3) AS CountryISO3

        ,LEFT(s.MakeName, 3) AS MakeAbb

        ,RIGHT(s.InvoiceNumber, 3) AS InvoiceNumber

        ,SUBSTRING(s.InvoiceNumber, 3, 2) AS SaleCountry

FROM    s

Here is the result for a single document:

{

        “CustomerName”: “WONDERLAND WHEELS”,

        “CountryName”: “united kingdom”,

        “CountryISO3″: “GBR”,

        “MakeAbb”: “Por”,

        “InvoiceNumber”: “011”,

        “SaleCountry”: “GB”

}

You need to be aware that:

  • SUBSTRING() is 0 based.
  • You can also use the LTRIM() and RTRIM() functions if you need to.

A Final Few String Functions

Cosmos DB SQL only has a couple of dozen string functions, and we have seen many of the most useful ones already. Should you need them, you can always add the following to your armoury:

REPLICATE()

LENGTH()

INDEX_OF()

Mathematical Functions

Cosmos DB SQL comes complete with a core set of mathematical functions.

SELECT   ROUND(s.TotalSalePrice) AS RoundedSalePrice

        ,FLOOR(s.RepairsCost) AS RepairsCost

        ,CEILING(s.PartsCost) AS PartsCost

        ,TRUNC(s.TransportInCost) AS TransportInCost

FROM    s

Take a quick look at the result:

    {

        “RoundedSalePrice”: 89000,

        “RepairsCost”: 250,

        “PartsCost”: 225,

        “TransportInCost”: 150

    }

These are considerably more limited than their T-SQL equivalents, and only carry out basic rounding.

There are a few others such as ABS(), SQRT(), SQUARE() plus a small set of trigonometric functions should you need them.

Logic (Imitation CASE Statements) – Ternary Functions

ANSI SQL has the CASE statement. Cosmos DB SQL instead uses a ternary logic approach. That is, it uses the ? and : constants to break up the code into test ? true : false elements.

To see this in action, take a look at the following Cosmos DB SQL snippet:

SELECT s.CountryName IN (“France“, “Germany“, “Spain“)

                        ? “EU“ : “Other“ AS Region

FROM   s

This code tests for any of France, Germany or Spain in the CountryName attribute and returns EU if any of these elements are found-and Other if they are not found, as you can see, below:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

[

    {

        “Region”: “Other”

    },

    {

        “Region”: “Other”

    },

    {

        “Region”: “Other”

    },

    {

        “Region”: “Other”

    },

    {

        “Region”: “Other”

    },

    {

        “Region”: “EU”

    },

    {

        “Region”: “EU”

    }

]

Fortunately Cosmos DB SQL lets you nest ternary logic (as you may have done using the IF() function in Excel or the T-SQL IIF() functions).

SELECT   s.CountryName IN (“France“, “Germany“, “Spain“)

                       ? “EU“

                       : s.CountryName = “United Kingdom“

                            ? “Blighty“

                            : “Other“

         AS Region

FROM     s

Conclusion

Now that you have a grasp of the basics of Cosmos DB SQL, I hope that you will be tempted to consider using this powerful and scalable document database as a potentially game-changing extension to your relational database environment-and this particular approach to NoSQL as a valuable part of your skillset.

I am not denying that the SQL that is implemented on top of Cosmos DB is severely limited. Although you can often find workarounds to obviate the worst of its restrictions, you are essentially stuck with simple SQL queries that use SELECT, FROM. WHERE and ORDER BY. You will not find any GROUP BY or HAVING clause for instance, and queries are bound to a single collection. Its analytical capabilities are curtailed by the absence of windowing functions.

On the other hand, the sheer simplicity of the query language, as well as the ease with which you can adapt your current skills and learn to query JSON documents without having to learn a new framework and syntax are powerful points in its favour.

However, we have not finished with the guided tour of Cosmos DB SQL yet. A second article will explain some of the more advanced features of the language that can be used to cope with the more complex document structures that JSON allows. This article will also provide some hints and tips on working around some of the limitations inherent in querying JSON documents with SQL.

Let’s block ads! (Why?)

SQL – Simple Talk

Read More

Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines

March 1, 2019   Microsoft Dynamics CRM

In this blog series, we will explore building out DevOps processes and practices for Dynamics 365 Customer Engagement (CE) by utilizing YAML based Azure Pipelines. In this first blog, we will cover version controlling solutions and the basics of automating deployment.

What is DevOps?

DevOps is the union of people, process, and products to enable continuous delivery of value to our end users. The contraction of “Dev” and “Ops” refers to replacing siloed Development and Operations to create multidisciplinary teams that now work together with shared and efficient practices and tools. Essential DevOps practices include agile planning, continuous integration, continuous delivery, and monitoring of applications. (Sam Guckenheimer, What is DevOps?)

Getting started

If you already have a Dynamics 365 CE environment, Azure DevOps, you’re familiar with the basics, and have a solution ready to go skip to Moving solutions between environments. If you are new to all of this, please continue from here.

The most basic workflow of Dynamics 365 CE development, deployment, and functional testing consists primarily of manual processes. We modify, export, and import a solution(s) into a downstream environment, then manually test to ensure there are no issues with solution layering, integrations, etc. That gets old quick, what we really want is to automate as much as we can. The first step we can take is to implement the practice of version control for our solutions and automate solution imports to various environments.

What we will need

Assumptions

  • Some experience with customizing Dynamics 365 CE
  • A basic understanding of version control
    • There are a number of tools to make version control easier such as GitHub Desktop, Sourcetree, and GitKraken to name a few. For this series, it is expected that you know the basics or will learn Git for version control.

Version control for solutions

Solutions in Dynamics 365 CE are in essence a package containing any customization we’ve done to our environment that we can export from one environment then import into various environments. When exported from an environment, solutions are in the form of a zip file. When that zip file is unzipped, the output directory contains folders for plugins, web resources, and any workflows we have made as well as XML files defining the schema of any customization we have done. In the zipped format, our schema definition is contained in one massive file. Consider this zip file as a binary, or in layman’s terms, a tidy package with a fancy bow, i.e. It may look nice but it’s not easy to see what’s inside and it’s a poor format for version control.

Enter Solution Packager. Solution packager essentially takes our Dynamics 365 CE solution zip file and breaks it out into a logical folder structure by decomposing the contents. The resulting output shows a more granular view of our solution and is considerably more friendly for version control as you can see from the example screenshots below.

Example zip file directory structure

 Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines

Example extracted solution directory structure

 Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines

Note:

  • The extra granularity of an unpacked solution makes it ideal for team development. Developers can pack and import a solution from a repository into their various development environments, make changes, export, unpack, and commit the solution back to the repository. For more on the benefits of using solution packager and example scenarios check out the article Use source control with solution files on Microsoft Docs.
  • Solution Packager does not completely remove merge conflicts but does make it easier to manage them.

Download solution packager

To get started we will need to download a copy of the Dynamics 365 CE Core Tools Nuget package locally, this package includes Solution Packager.

Steps to download

  1. Open a PowerShell Terminal
  2. Navigate to the directory that you want the tools downloaded to.
    • Note that the code below will create the folder structure Tools\CoreTools in the current directory
  3. Copy & paste the code below into the terminal or new script window if using PowerShell ISE and run the script.
    $  sourceNugetExe = "https://dist.nuget.org/win-x86-commandline/latest/nuget.exe"
    $  targetNugetExe = ".\nuget.exe"
    Remove-Item .\Tools -Force -Recurse -ErrorAction Ignore
    Invoke-WebRequest $  sourceNugetExe -OutFile $  targetNugetExe
    Set-Alias nuget $  targetNugetExe -Scope Global -Verbose
    
    ##
    ##Download CoreTools
    ##
    ./nuget install  Microsoft.CrmSdk.CoreTools -O .\Tools
    md .\Tools\CoreTools
    $  coreToolsFolder = Get-ChildItem ./Tools | Where-Object {$  _.Name -match 'Microsoft.CrmSdk.CoreTools.'}
    move .\Tools\$  coreToolsFolder\content\bin\coretools\*.* .\Tools\CoreTools
    Remove-Item .\Tools\$  coreToolsFolder -Force -Recurse
    
    ##
    ##Remove NuGet.exe
    ##
    Remove-Item nuget.exe

*The code above was sourced from the Download tools from NuGet Microsoft Docs article.

Extract solution file

Now that we have Solution Packager downloaded we can work on adding our solution to version control. If you are following along with your own setup you will need to export a solution from your environment as both managed and unmanaged. It is recommended to use an unmanaged solution for development purposes and managed for all other environments. If you do not have a solution file handy grab a copy of our example unpacked solution from our GitHub repo in the Lesson-1 folder. Note that Azure DevOps build pipelines integrate with Azure Repos, GitHub, and GitHub Enterprise repository. In this example, I am using GitHub.

PowerShell command to extract Dynamics 365 CE solution:

The following PowerShell command allows us to extract both our managed and unmanaged solution files, this is accomplished by setting the packagetype to both and the zipfile value to the path to our unmanaged solution zip file. Solution packager will search the same directory for a solution file with the name containing the unmanaged solution name but ending in _managed.zip, for example, contoso.zip and contoso_managed.zip.

.\Tools\CoreTools\SolutionPackager.exe /action extract /packagetype both /zipfile <path to file>\contoso.zip /folder <path to output directory>

Once unpacked, submit the output to version control.

Moving solutions between environments using a YAML script

Now that we have an unpacked solution and it has been checked into version control we can work on automating deployment. I tend to follow the GitHub Flow development workflow that assumes anything in the master branch is always deployable. Following this pattern, we will build a simple YAML script that triggers on commits to the master branch and performs the steps outlined below. For a full explanation of YAML schema for Azure Pipelines check out aka.ms/yaml.

YAML script steps

Create a new build pipeline

First and foremost we will need a new build pipeline, so let’s head over to our Azure DevOps project repository to get this started.

  1. Navigate to your Azure DevOps project repository. For example, https://dev.azure.com/{username}/D365-CE-DevOps-Tutorial
  2. Click Pipelines, then click Builds.
  3. Click New, then click New Build Pipeline
  4. On the next screen, we will select the location of your unpacked solution: Azure Repos, GitHub, or GitHub Enterprise. Note that choosing GitHub requires that you authorize Azure DevOps to access the repository, the behavior otherwise is the same in our build pipelines.
  5. Select the repository containing the unpacked solution files.
  6. Configure pipeline using the Starter pipeline option.

*Note that choosing starter pipeline will name you file azure-pipelines.yml so you’ll want to remain the file later and update the path in your pipeline settings.

 Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines

Define name, trigger, and pool type

Now that a basic pipeline has been set up we can begin building out the steps needed to pack and deploy our solution file. For this example, we will be using the build pipelines only as YAML support for release pipelines is still under development at the time of writing.

We can start by deleting the contents of the starter template and adding our own definitions for the build name, trigger, and pool.

name: $  (BuildDefinitionName)-$  (Date:yyyyMMdd).$  (Rev:.r)

trigger:
- master

pool:
  vmImage: 'vs2017-win2016'

Explanation of schema:

  • Name - represents build number format. Default build numbers are whole numbers, we can do better than that.
  • Trigger - specific branch to trigger on, if no branch is provided, commits to any branch will trigger continuous integration.
  • Pool - essentially the type of OS and tools set you want to have your build run on. In this case, we are using Visual Studio 2017 on Windows Server 2016, but there are other available build agents as well.

Download and install Solution Packager

A build agent is provided to us as a blank workspace, so we will need to download and install the necessary tools to complete our build. We will first download and install Nuget so that we can install the Dynamics 365 CE Core Tools Nuget package. Solution Packager resides in the core tools package and is nested down a few levels. Rather than deal with a long string to point to SolutionPackager.exe, we will do some clean up to make it easier to reference the tool and to make this step easier to reuse in the future.

Append the following code the pipeline script, if you get a syntax error ensure Steps is not indented.

steps:
- script: md tools
  displayName: 'Create tools directory'

- powershell: |
    Invoke-WebRequest `
      -Uri https://dist.nuget.org/win-x86-commandline/latest/nuget.exe `
      -OutFile tools\nuget.exe
  displayName: 'Download nuget.exe'

- powershell: |
    tools\nuget.exe install Microsoft.CrmSdk.CoreTools -O tools
    md "tools\CoreTools"
    $  coreToolsFolder = Get-ChildItem tools | Where-Object {$  _.Name -match 'Microsoft.CrmSdk.CoreTools.'}
    move "tools\$  coreToolsFolder\content\bin\coretools\*.*" "tools\CoreTools"
    Remove-Item "tools\$  coreToolsFolder" -Force -Recurse
  displayName: 'Install CoreTools'

Note that script above is essentially the same as the script we used in the Download solution packager section.

Save and run to ensure your setup is correct and make adjustments as needed.

Pack Solution from repository

With Solution Packager installed we can turn our attention to packing up our extracted solution for importing into our target environment(s). To do that we will need to pack the solution back into its original zip file state using, this can be accomplished by reversing the extraction process we completed earlier in this blog.

- powershell: |
    Start-Process tools/CoreTools/SolutionPackager.exe `
    -ArgumentList `
      "/action: Pack", `
      "/zipfile: $  (Build.ArtifactStagingDirectory)\packedSolution$  env:SolutionName.zip", `
      "/folder: $  env:SolutionPath", `
      "/packagetype: Both" `
    -Wait `
    -NoNewWindow
  env:
    SolutionPath: $  (solution.path)
    SolutionName: $  (solution.name)
  displayName: 'Solution Packager: pack solution'
  • /action - available options are pack and extract
  • /zipfile - the location and desired name of packed solution zip file.
    • $ (Build.ArtifactStagingDirectory) is a predefined build variable that points to a directory that is purged with each new build so no need for cleanup in our script.
  • /folder - the location of our unpacked solution that will be packed.
  • /packagetype - Defines whether the solution should be packed as managed or unmanaged. Both will create both unmanaged and managed solution zip files.

In the code above you will have noticed that there is now an environment variable section, env, that contains two variables: SolutionPath and SolutionName. These variables allow us to define the path to our extracted solution and desired packed zip file name. We will define the variables in our pipeline settings variables tab and enable the variables for modification at queue time. Note that the solution name that you provide is a friendly name, the actual unique name and localized name(s) that will be used in Dynamics are stored in the solution.xml file of the packed and unpacked solution.

Steps to create pipeline variables

For our script to be able to access the variables defined in our script, we will need to manually create them in our pipeline settings using the following steps:

*Don’t forget to click Save and run before attempting to navigate away.

  1. Click the ellipsis (…) on the top right of the page and in the drop-down click Pipeline settings, this will take you to the visual pipeline designer.
  2. Click the Variables tab.
  3. Click + Add in the pipeline variables view to add a new variable.
  4. Perform step 3 twice and add the following variables
    • name: solution.name
      • value: <desired zip file name. In this example, contosoUniversity>
    • name: solution.path
      • value: <path to repository location of extracted solution. in this example, Lesson-1/ExtractedSolution/>
  5. Checkmark both as Settable at queue time
  6. Click the Save & queue drop down and click Save
  7. Enter a comment and click Save

*If you queue your build ensure the branch where your YAML file is saved is correct or you may get an error. Or navigate back to the pipeline YAML editor and run from there.

 Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines

Publish build artifacts

Next, we are going to publish our build artifacts even though release pipelines are not available for YAML builds at the time of writing this blog. In a future update, we will be moving our solution imports to a release pipeline and start breaking our script out into templates. For now, on build completion, you will be able to see and download the artifacts and the build will handle imports.

- task: PublishBuildArtifacts@1
  inputs:
    pathtoPublish: $  (Build.ArtifactStagingDirectory)
    artifactName: drop
  displayName: 'Publish build artifacts'

Save and run to ensure your setup is correct. again, ensure you don’t have extra spaces as the script is space sensitive. You can click the build queue notification message to see the build steps run in real-time and view the execution history. Once the build is complete, you should see a new button labeled Artifacts on the top left of the page. Click this drop-down to view and download your solution file artifact(s). Clicking Release will automatically add build artifacts from this build to your release pipeline, for this blog we will not be doing this because YAML for release pipelines is not out yet.

Import solution into target environment

Finally! Let’s deploy our solution to an environment.

The following snippet will download and install Microsoft.Xrm.Data.Powershell, then perform a synchronous import of our managed solution.

- powershell: Install-Module Microsoft.Xrm.Data.Powershell -Scope CurrentUser -Force
  displayName: 'Install Microsoft.Xrm.Data.PowerShell'

- powershell: |
    $  connection = Get-CrmConnection `
      -ConnectionString `
        ("AuthType = Office365;" + `
        "Username = $  env:ServiceAccountUpn;" + `
        "Password = $  env:ServiceAccountPassword;" + `
        "Url = https://$  env:EnvironmentName.crm.dynamics.com")

    Import-CrmSolution `
      -conn $  connection `
      -SolutionFilePath $  (Build.ArtifactStagingDirectory)\packedSolution$  ($  env:SolutionName)_managed.zip
  env:
    EnvironmentName: $  (environment.name)
    SolutionName: $  (solution.name)
    ServiceAccountUpn: $  (serviceAccount.upn)
    ServiceAccountPassword: $  (serviceAccount.password)
  displayName: 'Import solution'

Important - We have added some environment variables so we will need to edit our pipeline settings once more following the steps in the Steps to create pipeline variables section.

This time for step 4 we will add 3 new variables

  • name: environment.name - value: <Dynamics 365 CE org name e.g. contoso.crm.dynamics.com, name only>
  • name: serviceAccount.upn
  • name: serviceAccount.password
    • value: hopefully not hunter2
    • For password variable be sure to select the lock symbol to change the type of the field to secret.

*Mark variables that you want to modify at queue time to settable at queue time.

 Introduction to DevOps for Dynamics 365 Customer Engagement using YAML Based Azure Pipelines

That’s it, now you can click Save and run on the top left of the editor. Once the build has been kicked off click the build name in the notification banner to watch the build run. After the build has successfully completed your solution will be imported into the specified target environment and that’s that.

Stay tuned for more blogs that improve and build upon this example setup.

If you are interested in this topic and would like to do some further self-study I encourage you to check out the Solution Lifecycle Management: Dynamics 365 for Customer Engagement apps white paper.

*[CE]: Customer Engagement

References

Let’s block ads! (Why?)

Dynamics 365 Customer Engagement in the Field

Read More

Introduction to SQL Server Security — Part 3

February 26, 2019   BI News and Info

The series so far:

  1. Introduction to SQL Server Security — Part 1
  2. Introduction to SQL Server Security — Part 2
  3. Introduction to SQL Server Security — Part 3

Microsoft introduced contained databases in SQL Server 2012. A contained database is one that is isolated from other databases and from the SQL Server instance that hosts the database. The database maintains much of its own metadata and supports database-level authentication, eliminating the need for server-based logins. As a result, a contained database is more portable than a traditional, non-contained database. It can also simplify database development and administration, as well as make it easier to support Always On Availability Groups.

Controlling access to a contained database is similar to a non-contained database, except for a few important differences. In the first two articles in this series, I touched briefly upon the topic of contained databases when discussing SQL Server access control. In this article, I dig deeper into contained databases and offer several examples that show how to create contained database users, duplicate users across multiple contained databases, and unlink database users from their server-level logins.

Setting Up Your Environments

To try out the examples in this article, you need a test environment that includes a contained database. On my system, I used SQL Server Management Studio (SSMS) to create a simple database and populate it with data from the WideWorldImporters database, although you can use any data that fits your needs.

Before you can implement a contained database, you must enable the SQL Server instance to support this feature, if it’s not already enabled. To use T-SQL to enable contained databases, run the following EXECUTE statement:

EXEC sp_configure ‘contained database authentication’, 1;

GO

RECONFIGURE;

GO

The EXECUTE statement calls the sp_configure stored procedure to set the contained database authentication setting to 1 (on). You should then run the RECONFIGURE statement to implement the changes.

For the examples in this article, create the ImportSales1 contained database, using the following T-SQL script:

USE master;

GO

DROP DATABASE IF EXISTS ImportSales1;

GO

CREATE DATABASE ImportSales1

CONTAINMENT = PARTIAL;

GO

When you create a database, you can specify that it should be contained by including the CONTAINMENT clause in the CREATE DATABASE statement and set its value to PARTIAL. The default value is NONE, which disables the contained database feature. The PARTIAL value is used because SQL Server supports only partially contained databases, as opposed to fully contained databases. Currently, SQL Server does not support fully contained databases.

A partially contained database allows you to implement uncontained features that cross the database boundary. For example, you can create a database user that is linked to a SQL Server login in a partially contained database. Fully contained databases do not allow the use of uncontained features.

After you create the ImportSales1 database, you can add tables and then populate them, just like you can with a non-contained database. To support the examples in the rest of the article, use the following T-SQL script:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

USE ImportSales1;

GO

CREATE SCHEMA Sales;

GO

CREATE TABLE Sales.Customers(

  CustID INT NOT NULL PRIMARY KEY,

  Customer NVARCHAR(100) NOT NULL,

  Contact NVARCHAR(50) NOT NULL,

  Category NVARCHAR(50) NOT NULL);

GO

INSERT INTO Sales.Customers(CustID, Customer, Contact, Category)

SELECT CustomerID, CustomerName,

  PrimaryContact, CustomerCategoryName

FROM WideWorldImporters.Website.Customers

WHERE BuyingGroupName IS NOT NULL;

GO

The script creates the Sales schema, adds the Customers table to the schema, and then populates the table with data from the WideWorldImporters database. The SELECT statement’s WHERE clause limits the results to those rows with a BuyingGroupName value that is NOT NULL (402 rows on my system). If you create a different structure or use different data, be sure to modify the remaining examples as necessary.

Creating Database Users

In SQL Server, you can create users that are specific to a contained database and not linked to server-level logins. Contained users make it possible to maintain the separation between the contained database and the SQL Server instance, so it’s easier to move the database between instances.

SQL Server supports two types of contained users: SQL user with password and Windows user. The password-based user is a database user that is assigned a password for authenticating directly to the database. The user is not associated with any Windows accounts.

To create a password-based user, you must include a WITH PASSWORD clause in your CREATE USER statement. For example, the following CREATE USER statement defines a user named sqluser02 and assigns the password tempPW@56789 to the user:

USE ImportSales1;

GO

CREATE USER sqluser02

WITH PASSWORD = ‘tempPW@56789′;

GO

When a password-based user tries to access a contained database, the user account is authenticated at the database level, rather than the server level. In addition, all authorization granted through assigned permissions is limited to the database.

The second type of contained database user—Windows user—is based on a Windows account, either local or domain. The Windows computer or Active Directory service authenticates the user and passes an access token onto the database. As with password-based users, authorization also occurs within the database according to how permissions have been granted or denied.

When you create a Windows user, be sure that the Windows account is not already associated with a login. If you try to create a Windows user with the same name as a login, SQL Server will automatically associate that user with the login, which means that the user will not be contained.

In the following example, the CREATE USER statement defines a user based on the winuser02 local account, which I created on the win10b computer:

CREATE USER [win10b\winuser02];

GO

Whenever referencing a Windows account in this way, you must use the following format, including the brackets (unless enclosing the account in quotes):

[<domain_or_computer>\<windows_account>]

After you’ve created your contained users, you can grant, deny, or revoke permissions just like you can with any database users. For example, the following GRANT statement grants the SELECT permission on the Sales schema to both users:

GRANT SELECT ON SCHEMA::Sales TO sqluser02, [win10b\winuser02];

GO

You can also add contained users to fixed and user-defined database roles, and assign permissions to the user-defined roles. For more information about creating database users and granting them permissions, refer back to the second article in this series.

Creating Duplicate Database Users

When working with contained databases, you might find that some users need to be able to access multiple databases. For password-based users (SQL user with password), you should create the same user in each database, assigning the same password and security identifier (SID) to each user instance.

One way to get the SID is to retrieve it from the sys.database_principals system view after creating the first user, as shown in the following example:

USE ImportSales1;

GO

SELECT SID FROM sys.database_principals WHERE name = ‘sqluser02′;

The SELECT statement returns the SID value for the sqluser02 user in the ImportSales1 database. The returned value will be unique to that user and will be in a form similar to the following:

0x0105000000000009030000008F5AC110DFB07044AFDADA6962B63B03

You should use this value whenever you duplicate the user in other contained databases. To see how this works, you can create a database similar to the ImportSales1 database but instead name it ImportSales2, as in the following example:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

USE master;

GO

DROP DATABASE IF EXISTS ImportSales2;

GO

CREATE DATABASE ImportSales2

CONTAINMENT = PARTIAL;

GO

USE ImportSales2;

GO

CREATE SCHEMA Sales;

GO

CREATE TABLE Sales.Customers(

  CustID INT NOT NULL PRIMARY KEY,

  Customer NVARCHAR(100) NOT NULL,

  Contact NVARCHAR(50) NOT NULL,

  Category NVARCHAR(50) NOT NULL);

GO

INSERT INTO Sales.Customers(CustID, Customer, Contact, Category)

SELECT CustomerID, CustomerName,

  PrimaryContact, CustomerCategoryName

FROM WideWorldImporters.Website.Customers

WHERE BuyingGroupName IS NULL;

GO

The script creates the ImportSales2 database, adds the Sales schema to the database, adds the Customers table to the schema, and populates the table with 261 rows of data from the WideWorldImporters database. In this case, the WHERE clause filters for NULL values, rather than NOT NULL.

Next, create the sqluser02 user in the ImportSales2 database, only this time, include an SID clause that specifies the user’s SID from the ImportSales1 database, as shown in the following example:

USE ImportSales2;

GO

CREATE USER sqluser02

WITH PASSWORD = ‘tempPW@56789′,

SID = 0x0105000000000009030000008F5AC110DFB07044AFDADA6962B63B03;

GO

To create a duplicate Windows-based user, use the same CREATE USER statement you used in the ImportSales1 database:

CREATE USER [win10b\winuser02];

GO

You can also use the same GRANT statement to assign the SELECT permission to the Sales schema for both users:

GRANT SELECT ON SCHEMA::Sales TO sqluser02, [win10b\winuser02];

GO

That’s all there is to creating duplicate password-based and Windows-based users. You can use the same format for creating duplicate users in additional contained databases, depending on your data access requirements.

Running T-SQL Queries

To test the users you created in the contained databases, you can use an EXECUTE AS statement in SSMS to run queries within the execution context of a specific contained user. For example, the following T-SQL sets the execution context to the sqluser02 user, runs a SELECT statement against the Customers table, and then uses the REVERT statement to return to the original execution context:

EXECUTE AS USER = ‘sqluser02′;

SELECT * FROM Sales.Customers

REVERT;

GO

On my system, the SELECT statement returns 261 rows because the statement ran within the context of the last specified database, ImportSales2. However, the sqluser02 user exists in both databases, sharing the same name, password, and SID, so you should be able to query the Customers table in both databases, as in the following example:

EXECUTE AS USER = ‘sqluser02′;

SELECT * FROM ImportSales1.Sales.Customers

UNION ALL

SELECT * FROM ImportSales2.Sales.Customers;

REVERT;  

GO

Unfortunately, if you try to run the statement, you’ll receive an error similar to the following:

The server principal “S-1-9-3-281107087-1148235999-1775950511-54244962“

is not able to access the database “ImportSales1“ under

the current security context.

The problem is not with how you’ve set up the user accounts or query, but rather with how the TRUSTWORTHY database property is configured. The property determines whether the SQL Server instance trusts the database and the contents within it. Although this might seem to have little to do with contained databases, the TRUSTWORTHY property must be set to ON for the ImportSales2 database because you’re running the query within the context of that database but trying to access data in the ImportSales1 database.

By default, the TRUSTWORTHY property is set to OFF to reduce certain types of threats. You can find more information about the property in the SQL Server help topic TRUSTWORTHY Database Property.

Before setting the property, you must be sure you’re working in the correct execution context. If you’ve been following along with the examples, your session might still be operating within the context of the sqluser02 user. This is because the UNION ALL query in the last example failed, which means that the REVERT statement never ran. As a result, your current SQL Server session is still be running within the execution context of the sqluser02 user. To correct this situation, simply rerun the REVERT statement.

At any point, you can verify the current execution context by calling the CURRENT_USER function:

SELECT CURRENT_USER;

Once you’ve established that you’re working within the context of the correct user, run the following ALTER DATABASE statement to set the TRUSTWORTHY property to ON:

ALTER DATABASE ImportSales2 SET TRUSTWORTHY ON;

GO

Now when you run the following query, it should return the 663 rows from the two tables:

EXECUTE AS USER = ‘sqluser02′;

SELECT * FROM ImportSales1.Sales.Customers

UNION ALL

SELECT * FROM ImportSales2.Sales.Customers;

REVERT;

GO

You should also receive the same results if you run the query under the execution context of the win10b\winuser02 user, as shown in the following example:

EXECUTE AS USER = ‘win10b\winuser02′;

SELECT * FROM ImportSales1.Sales.Customers

UNION ALL

SELECT * FROM ImportSales2.Sales.Customers;

REVERT;

GO

I created and ran all the above examples in SSMS. If you try them out for yourselves, you’ll also likely use SSMS or SQL Server Data Tools (SSDT). In the real world, however, most connections will be through third-party scripts, utilities, or applications. In such cases, the connection string that establishes the connection to the contained database must specify that database as the initial catalog. Otherwise the connection will fail.

Unlinking Database Users from Their Server Logins

Because SQL Server contained databases are only partially contained, they can include users mapped to server logins. The users might have existed before changing the database to a contained state, or they might have been added after the fact. In either case, the database is less portable because of its login connections.

SQL Server provides the sp_migrate_user_to_contained system stored procedure for quickly unlinking database users from their associated SQL Server logins. To see how this works, start by creating the following user in the ImportSales1 database:

USE ImportSales1;

GO

CREATE USER sqluser03 FOR LOGIN sqluser01;

GRANT SELECT ON SCHEMA::Sales TO sqluser03;

GO

The script creates the sqluser03 user based on the sqluser01 login and grants to the user the SELECT permission on the Sales schema. (If the sqluser01 login doesn’t exist on your system, you can also use a different login or refer to the second article in this series for information about creating the sqluser01 login.)

After you create the database user, you can test that it has the expected access by running the following query:

EXECUTE AS USER = ‘sqluser03′;

SELECT * FROM ImportSales1.Sales.Customers;

REVERT;

GO

The query should return all the rows from the Customers table in the ImportSales1 database.

If you view the user’s properties in Object Explorer in SSMS, you’ll find that the General tab shows the associated login as sqluser01 and the user type as SQL user with login, as shown in Figure 1

word image 69 Introduction to SQL Server Security — Part 3

Figure 1. Database user based on a SQL Server login

To unlink this user from the SQL Server login, run the sp_migrate_user_to_contained stored procedure, specifying the database user that you want to migrate, as shown in the following example:

EXEC sp_migrate_user_to_contained  

@username = N‘sqluser03′,  

@rename = N‘keep_name’,  

@disablelogin = N‘do_not_disable_login’;

The sp_migrate_user_to_contained system stored procedure takes the following three parameters:

  • The @username parameter is the database user.
  • The @rename parameter determines whether to use the database user or the server login for the name. The keep_name value retains the database user name. The copy_login_name uses the login name.
  • The @disablelogin parameter determines whether to disable the login. In this case, the login will not be disabled. To disable the login, instead, specify the disable_login value.

After you run the EXECUTE statement, reopen the properties for the sqluser03 user. You’ll find that a login is no longer associated with the user and that the user type has been changed to SQL user with password, as shown in Figure 2.

word image 70 Introduction to SQL Server Security — Part 3

Figure 2. Password-based contained database user

When you unlink a database user from a login, SQL Server assign’s the login’s password to the user, as indicated in the figure. As a security best practice, you should reset the user’s password at this point. If you were to now rerun the following query, you should again receive the same rows from the ImportSales1 database:

EXECUTE AS USER = ‘sqluser03′;

SELECT * FROM ImportSales1.Sales.Customers;

REVERT;

GO

By unlinking the login from the database user, you can take full advantage of the portability inherent in contained databases. Be aware, however, that the sp_migrate_user_to_contained stored procedure works only for SQL Server logins and not Windows logins.

Securing SQL Server Contained Databases

Contained databases can make it easier to move a database from one SQL Server instance to another, without having to worry about duplicating login information between those instances. However, before implementing contained databases, you should be familiar with Microsoft’s security guidelines, described in the SQL Server help topic Security Best Practices with Contained Databases. The topic explains some of the subtler aspects of controlling access to contained databases, particularly when it comes to roles and permissions.

Aside from these guidelines, you’ll find that controlling access to a contained database works much like a non-contained database. You might need to duplicate users across multiple contained databases or unlink database users from their server logins, but these are relatively straightforward processes, much like controlling access in general. Once you understand the basics, you should have little trouble supporting more complex scenarios.

Let’s block ads! (Why?)

SQL – Simple Talk

Read More

An Introduction to Cloud-Native Applications

February 6, 2019   TIBCO Spotfire
View of storm seascape

This is part one in a three-part series on the evolution to cloud-native application development. Next week’s part two will cover cloud-native API management, and tune in February 19 for a special cloud-native announcement.

Applications drive successful digital platforms, and their development and management continue to evolve. While many businesses still operate substantial legacy tech on-premises, many are looking to the cloud for development. But what does it mean to develop a cloud-native app, and why should you care?

Rather than implying where an app resides, cloud-native refers to how it was created and deployed. Cloud-native apps are developed with tools that allow them to take full advantage of cloud benefits, meaning they can be built and changed more quickly, are more agile and scalable, and can be connected with other existing apps more easily.

Now that you understand cloud-native as a concept, let’s explore some of the best practices developers use when creating cloud-native apps, as well as why those elements are vital in driving innovation.

Microservices

Cloud-native apps are developed with loosely-coupled microservices, allowing each microservice to function only as needed, as well as be updated/altered as needed, rather than requiring the full application to run aggregately.

Scalability

Cloud-native apps are deployed on self-service, shared infrastructure, and are built to be elastic and scalable. They are able to scale up to utilize more cloud resources when required while taking up as little bandwidth as possible otherwise. This is beneficial because apps often share tenancy with others, and the ebb and flow of resources can be shared as necessary.

Containerization

Cloud-native apps are built to be a collection of independent, autonomous services packaged together in lightweight containers. These containers are built to be scalable, as well as fully independent from other another. Because the unit of scaling shifts to the containers, the resource utilization is optimized.

Tooling

Best-of-breed languages and frameworks are used in the development of cloud-native apps. In order to create the best app possible, developers will pick and choose which tools to implement into each microservice, which allows the completed application to have the most efficient functionality possible.

DevOps

Each microservice of a cloud-native app goes through an independent life cycle, managed via an agile DevOps process. For the app to function properly, multiple continuous integration/continuous delivery (CI/CD) routes will work together to deploy and manage the application.
There are myriad other features of cloud-native apps that allow them to be faster, more flexible, and more efficient than their legacy counterparts. To learn more about how TIBCO® can help you learn more about cloud-native technologies, check out TIBCO.com

Let’s block ads! (Why?)

The TIBCO Blog

Read More
« Older posts
  • Recent Posts

    • OUR MAGNIFICENT UNIVERSE
    • What to Avoid When Creating an Intranet
    • Is Your Business Ready for the New Generation of Analytics?
    • Contest for control over the semantic layer for analytics begins in earnest
    • Switch from Old Record View to Kanban Board View to Maximize Business Productivity within Dynamics 365 CRM / PowerApps
  • Categories

  • Archives

    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited