A New Adventure at SQL Sentry

For the last 8 years, consulting roles have offered me a chance to learn and grow and expand my skills. Learning all of the time is really pivotal to my sense of happiness and satisfaction. In particular, BlueGranite has been an absolutely fantastic employer. They have a ton of really sharp people who are smart, motivated, and fun to be around. The leadership team truly values their technical staff, and they are trustworthy. I will miss a ton of people at BlueGranite (especially a few...you know who you are), but it's time for a new adventure.

As of October 3rd I'll be joining the SQL Sentry team as a BI Architect. I'll be the second BI-type of person to join their staff. The first is Jim Benton, who I worked with at a previous employer. In fact, this job change is all Jim's fault - he spoke so incredibly highly of SQL Sentry that I just had to look into this new role they created.

So what will I be doing? Since I don't yet have clarity on what's proprietary information and what's not, I'll just say that I'll be helping with BI and analytics for their product line, as well as internal analytics projects. Some of the projects in the pipeline are going to be seriously awesome.

If you're not familiar with SQL Sentry, they are a software development company. SQL Sentry creates monitoring and performance optimization software for SQL Server, Windows, and some Azure components which are related to SQL Server. They have a variety of paid offerings in their suite of tools, but you can get started for free with a fantastic tool called Plan Explorer

As I write this I'm enjoying a week of leisurely life in between jobs. Well, leisure mixed in with a little closet cleaning and weed pulling that is. I look forward to posting all sorts of new info soon, and it'll be really interesting to see how my technical focus shifts over time.

Overview of Azure Automation

We're currently setting up Azure Automation as one component of an overall solution which uses PowerShell for managing members in an Office 365 unified group (per my recent post on the issue). I'll post those details as soon as it's completed and tested. In the meantime, this post is an introduction to what Azure Automation (AA) is all about.

Azure Automation is a cloud service in Microsoft Azure which let you schedule execution of PowerShell cmdlets and PowerShell workflows. Azure Automation uses the concept of runbooks to execute a set of repeatable, repetitive tasks via PowerShell. Consistency in execution, reduction of errors, and of course saving time, are all key objectives - which makes DBAs and system admins happy, eh?

Examples of How You Could Use Azure Automation

  • Shut down a virtual machine in a development environment on a schedule to avoid charges when it's not being used
  • Pause Azure SQL Data Warehouse on a schedule to avoid compute charges during the time it's not serving queries or processing data loads
  • Check size of an Azure resource to determine if it's close to reaching its threshold for scaling up
  • Scale Azure resources up or down on a predefined schedule
  • Deployment of resources from Dev to Test environment (i.e., in conjunction with ARM deployments)
  • Deployment of test scenarios, particularly if an environment needs to be deleted and recreated numerous times as part of the testing cycle
  • Scheduling of routine processes such as backups, file copy operations, or custom scripts
  • Auditing and logging of all resources in a resource group (optionally with Log Analytics in Operations Management Suite)
  • Kick off a task in response to an Azure alert (requires a webhook-enabled runbook which can respond automatically to alerts)
  • Execute a task on an on-premises server (requires the Hybrid Runbook Worker feature)
  • Deployment of patches for Azure virtual machines

Obviously the above list isn't all-inclusive; there are so many ways in which automation techniques can make your life easier.

The "Lingo" in Azure Automation

  • Automation Account: The container for your runbooks and assets; associated with a Run As account and certificate
  • Runbook: Contains the PowerShell scripts which perform the operation(s)
  • Jobs: History of when runbooks were executed
  • Assets: Objects which are reusable across runbooks, including:
    • Schedules: Schedule to execute a runbook hourly, daily, weekly, or monthly
    • Modules: aka Integration Modules - PowerShell modules uploaded to AA or imported from the Gallery, each of which contains encapsulated functionality
    • Variables: Values for variables which can be called from runbooks
    • Credentials: Encrypted user name and password for authenticating to an external source
    • Certificates: Encrypted .cer or .pfx file for authenticating to an external source
    • Connections: Encrypted info re: connecting to an external source
  • Hybrid Runbook Worker: Allows you to run AA runbooks on your on-premises (non-cloud) resources
  • Webhook: Allows you to execute the runbook from an HTTP request; this allows for a runbook to be dependent upon something else that has occurred

Getting Started With Azure Automation

  • The first thing to do is create an Azure Automation acccount.
  • When you set up an Azure Automation account, you get 4 tutorial runbooks created automatically (though they aren't scheduled). After you've perused and learned from them, you probably want to delete them. 
  • There is a Runbook Gallery which contains a ton of runbooks created by Microsoft or the developer community.
  • Microsoft Press has a free e-book. This e-book was published mid-2015 so some things have definitely changed since it was published - for instance, only PowerShell workflows were supported at the time that e-book was written.

Tips, Suggestions, and Comments About Azure Automation

  • When you create an Azure Automation account, give some thought to the scope of how you intend to use it. Depending on how you have chosen to set up resource groups within your subscription (a *very* important decision!), you might want to align your automation account in a similar way. Do keep in mind that runbooks can't be shared across different automation accounts, so that might lend itself to the automation account being more broad so as not to duplicate scripts. Conversely, owner/contributor/reader/operator roles are all at the automation account level which might justify being more granular.
  • Schedules can be set to run as frequently as once an hour (at the time of this writing, fall 2016).
  • Look into using Assets for sharing connections, credentials, and variable values across runbooks (within the scope of one automation account).
  • Runbooks can be nested; that is, one runbook can call another runbook. This allows for reuse of scripts or other useful patterns. (The runbook being called needs to be in a published status for this to work.)
  • There are 3 statuses: draft, edit, and published. If you have "undeployed" changes in draft or edit status, a published version of your runbook will keep running in its published state.
  • If it's being edited, a runbook's status will be in edit mode. If you wish to make changes to a runbook that's in edit status (i.e., being edited by another contributor), you should wait until it's back safely in published status. Otherwise, two authors at one time will absolutely, positivelyget in each other's way.
  • Design any runbooks, or checkpoint durations, to be less than 3 hours. Currently (fall 2016), any Azure Automation job running 3 hours will be temporarily unloaded. This means it will be restarted from the beginning or the most recent checkpoint (if applicable). (This limit used to be 30 minutes but was increased to 3 hours in 2015.)
  • There are 4 types of runbooks:  PowerShell, PowerShell Workflow, Graphical PowerShell, and Graphical PowerShell Workflow. They can't currently be converted from one type to another. 
  • Azure Automation can be integrated with GitHub for source control. Visual Studio Online (TFS) is coming soon.
  • Every single Azure service isn't supported by Azure Automation, so be sure you verify for your scenario.
  • Runbooks can be created directly in the Azure portal if you like, or they can be imported (.ps1 or .graphrunbook file types). An imported file needs to be <1MB.
  • Be sure to read the chapter called Best Practices in Using Azure Automation as part of the e-book. It's got lots of good tips. In the first edition, it's chapter 6 which starts on page 83.

Desired State Configuration

The Desired State Configuration (DSC) is a specific type of Azure Automation. It utilizes PowerShell in a declarative way to enforce configuration settings or validate a specific state. DSC monitors computers in your environment, which are referred to as target "nodes." The nodes can be in Azure on on-premises, physical or virtual.

Examples of using Desired State Configuration include:

  • Validation that machines comply with corporate security standards
  • Auditing of server state, registry settings, environment variables, etc
  • Validation of server roles and features enabled
  • Management of files, folders, settings, software installations, etc

Finding More Information

Azure Documentation

You Might Also Like...

Setting Up a PC for Cortana Intelligence Suite Development

How to Create a Demo/Test Environment for Azure Data Catalog

Managing Members in a Power BI Group Workspace

As discussed in my post about Sharing and Security in Power BI, a group workspace in Power BI is our best option currently for organizing content by subject area, and/or by security boundaries. 

Members in a group workspace may be allowed to edit content, or restricted to view content (this applies to *all* members in the group rather than individually). This is easy enough to work around by setting members to view only, and allowing only administrators of the group to edit content. For groups, such as "Sales Staff" for instance, where there could be dozens or even hundreds of salespersons, this type of setup would allow all of the salespersons view only access with a very small number of people allowed to edit the content.

Dozens or hundreds of users in a group is what is prompting me to write this post. Manually managing the members within the Power BI workspace is just fine for groups with a very small number of members - for instance, your team of 8 people can be managed easily. However, there are concerns with managing members of a large group for the following reasons:

  • Manual Maintenance. The additional administrative effort of managing a high number of users is a concern. 
  • Risk of Error. Let's say there is an Active Directory (A/D) group that already exists with all salespersons add to the group. System admins are quite accustomed to centrally managing user permissions via A/D groups. Errors and inconsistencies will undoubtedly result when changes in A/D are coordinated with other applications, but not replicated to the Power BI Group.Depending on how sensitive the data is, your auditors will also be unhappy.

To avoid the above two main concerns, I came up with an idea. It didn't work unfortunately, but I'm sharing what I learned with you anyway to save you some time. 

The Idea For a Way to "Sync" Members from Active Directory to a Power BI Group 

Essentially, the idea was that if synchronization (or federation) from on-premises Active Directory is already taking place to Azure & Office 365, that we could use the sync'd O365 group (security group, distribution group, or mail-enabled security group) as the only member of the O365 unified group that controls the Power BI workspace.

There are multiple types of groups in Office 365. The names shown in the screenshots below are just because I did some various experimentation with Security, Distribution, and Mail-Enabled Security Groups. In the screenshots below:

      MCTesting_DistributionGroup: An O365 Distribution Group (the one to be sync'd from A/D)

      MCTesting_AlignWithDistributionGroup: An O365 Unified Group (created in Power BI)

Unsuccessful Attempt 1: "Invitation Will Be Sent" Message

When I tried to add the O365 distribution group as a member directly in the Power BI group workspace, I got a message "Invitation will be sent" rather than the normal dropdown to set admin or member. I saw this message recently as well when I typed an individual person's e-mail address incorrectly. At this point, I believe the "Invitation Will Be Sent" message is really indicating it's an invalid e-mail address so I take it to mean the Power BI group doesn't actually recognize usage of the O365 distribution group. (A bit of a guess there on my part - either way, it didn't work.)

Unsuccessful Attempt 2: Using the O365 Interface

In order to get around the first issue, I tried instead use the Office 365 group interface:

The good news is the e-mail address for the group was accepted in this interface. However, my happiness didn't last long because what it does is take that group and expand it out to the individual members (Meagan agreed to be my guinea pig in the test group):

Just for grins, I decided to add one more member to the O365 group. Sure enough, it doesn't flow through to the O365 Unified Group for use in Power BI. I didn't expect it to, just wanted to double check.

Ideally we would want the Power BI Group (i.e., the Office 365 Unified Group) to be maintenance-free in terms of membership - once the original A/D group members are updated, we want them to flow through to Office 365 and thus Power BI.

Alternative Solution: PowerShell

A colleague and I are in the midst of testing out usage of PowerShell to automate synchronization of members extracted from Active Directory into the respective Office 365 Unified Group for use in Power BI. There are some ExchangeOnline cmdlets that could be utilized for this purpose. Specifically, the "Add-UnifiedGroupLinks" cmdlet adds one or more members to a unified group. Coming soon: a separate post with details on how this went. 

Although this post doesn't offer an absolutely definitive solution (yet), its purpose is to share what I've observed so far with respect to easing the effort of maintaining a large number of users in Power BI groups. At some point I'm certain our options will evolve, so be sure to validate in your environment.

You Might Also Like...

Groups in Power BI - How Sharing and Security Works

Overview of Power BI V2 Features End-to-End

Direct Connect Options in Power BI for Live Querying of a Data Source

Tips for Using Azure Data Catalog

It seems there's a lot of interest in Azure Data Catalog from the customers that I work with. Since I've been discussing it a lot recently during projects, I thought I'd share a few thoughts and suggestions.

Register only Production data sources. Typically you won't want to register Development or UAT data sources. That could lead to users seeing what appears to be duplicates, and it also could lead to inconsistency in metadata between sources if someone adds an annotation to, say, a table in UAT but not in Production. 

 
 

Register only data sources that users interact with. Usually the first priority is to register data sources that the users see-for instance, the reporting database or DW that you want users to go to rather than the original source data. Depending on how you want to use the data catalog, you might also want to register the original source. In that case you probably want to hide it from business users so it's not confusing. Which leads me to the next tip...

Use security capabilities to hide unnecessary sources. The Standard (paid) version will allow you to have some sources registered but only discoverable by certain users & hidden from other users (i.e., asset level authorization). This is great for sensitive data like HR. It's also useful for situations when, say, IT wants to document certain data sources that business users don't access directly.

Use the business glossary to ensure consistency of tags. The business glossary is a capability of the Standard (paid) version and is absolutely worth looking into. By creating standardized tags in the business glossary, you'll minimize issues with tag inconsistency that would be annoying. For example, the business glossary would contain just one of these variations: "Sales & Marketing", "Sales and Marketing", or "Sales + Marketing".

Check the sources in the "Create Manual Entry" area of Publish if you're not seeing what you're looking for. There's a few more options available in Manual Entry than the click-once application.

Use the pinning & save search functionality to save time. For data sources you refer to often, you can pin the asset or save search criteria. This will display them on the home page at AzureDataCatalog.com so they're quicker to access the next time.

Use the Preview & Profile when registering data when possible. The preview of data (i.e., first X rows) and profile of data (ex: a particular column has 720 unique values that range from A110 to M270) are both extremely useful when evaluating if a data source contains what the user really wants. So, unless the data is highly sensitive, go ahead and use this functionality whenever you can.

 
 

Be a little careful with friendly names for tables. When you set a friendly name, that becomes the primary thing a user sees. If it's very different from the original name, it could be more confusing than helpful because users will be working with the primary name over in tools such as Power BI.

 
 

Define use of "expert" so expectations are clear. A subject matter expert can be assigned to data sources and/or individual objects. In some organizations it might imply owner of the data; however, in the Standard (paid) version there is a separate option to take over ownership. Usually the expert(s) assigned indicates who knows the most about the data & who should be contacted with questions. 

Be prepared for this to potentially be a culture change. First, it's a culture change for DBAs who are responsible for securing data. The data catalog may absolutely expose the existence of a data source that a user didn't know about--however, remember that it only exposes metadata and the original data security is still in force. The other culture change affects the subject matter experts who know the data inside and out. These folks may not be used to documenting and sharing what they know about the data. 

You Might Also Like...

Overview of Azure Data Catalog in the Cortana Intelligence Suite <--Check the "Things to Know about Azure Data Catalog" towards the bottom this post for more tips

How to Create a Demo/Test Environment for Azure Data Catalog