TIL: Microsoft Azure Part 2

Last week I started a multi-part series on Today I Learned (TIL) about Microsoft Azure.  This is part two of what I am learning in Azure.

Today’s topic is simply about Tenants, Subscriptions, Subscription Roles, Resource Groups, and Tags.

It’s Always Good to Start with Pictures

Here is a glimpse of how these topics relate. I will define and explain each below.

What is a Tenant?

In simplest terms, a Tenant is container for multiple subscriptions. An example of two subscriptions would be Azure and Office 365. They would be owned by one account, an individual or a company. A very large enterprise may use multiple subscriptions to better manage billing between divisions.

What Are Azure Subscriptions?

Basically, it’s just an ownership account. Think of it as just creating a billing and usage management account, whether it is a personal subscription or an enterprise level. The account allows you to group and manage multiple subscriptions for billing and reporting.

A subscription can encompass a mix IaaS, PaaS and SaaS services.  All subscription management, reviewing billing reports, and creating new subscriptions can be done through http://account.windowsazure.com site, but you need to be an account administrator.

How Do I Get Subscriptions?

You can get them through a Trial, MSDN, Pay as you go using a credit card, Azure Resellers (called Cloud Solution Providers or CSPs) or Enterprise Agreements.

What are the Subscription Server Roles?

Microsoft offers roles based on “Least Privilege” within Azure at the subscription level. There are several roles that secure the access to your cloud environment. These three main accounts below are all very powerful accounts and should be limited to only a few.

The top role is the Account Administrator. Think of this account in terms of what Enterprise Administrator is in your on-premises Active Directory. The Account Administrator has full rights. They have access to the account’s full financials and billing information for all subscriptions within the account, they can also create, delete and modify subscriptions.

The next role is the Service Administrator. This role is like the Domain Admin. It’s one level down from the account administrator and has full rights to the services in the subscription. They can do everything an account administrator can do with few exceptions, such as viewing the billing details of the subscription.

There is also the role of a Subscription or Co-administrator. This role is like System Admin(SA) in SQL Server.  This role can create and delete resources within the subscription but has no control over billing or the ability to change the authentication source such as AD.

The three accounts above control the Role Based Access (RBAC) for the rest of the users accounts on a resource level. They can assign users or groups of users, the rights to manage only the resources they need for their particular roles. These are roles such as Owner, Contributor and Reader of a resource group.

What’s a Resource Group?

A resource group is a container that separates resources into groups. Things that can exist in this container are things like VMs, NICS, Storage, Web Apps, SQL and Virtual Networks (VNETS). The “objects” within a resource group can be created, updated, and deleted as a group. One easy example of a resource group can be a development environment, all parts associated to that environment are contained in that in resource group.

What is a Tag?

The next granular level of organizing are Tags. These allow for adding your own meta-data to objects in Azure. Think of these as labels or categories for reporting and organizing things like billing. For instance, if the resource groups within an ERP environment are tagged as “ERP”, then those resource groups would get categorized together for management purposes. If you’ve ever used extended properties in SQL Server this is the same basic concept. There are however limits to the amount of tags an individual resource can have, which is currently 15. Your Azure billing statement is grouped by tags, which makes this almost a mandatory feature.

Summary

In this part we covered Tenants, Subscriptions, Subscription Roles, Resource Groups, and Tags. Hopefully you got a basic understanding of each and how the relate to each other. Next, I will dive a little into the differences between Azure SQL Database and SQL Server on IaaS.

 

TIL: Microsoft Azure Part 1

I thought maybe it would be a good idea to start a multi-part series on Today I Learned (TIL) about Microsoft Azure. As part of my new job I am currently learning as much about Azure as possible. As I learn things, I will blog to share what I am learning. It will cover beginner level things initially and gradually progress to more advanced topics.

Today’s topic is simply…. What the heck is Azure, how do I get to it, and what is the difference between IaaS, PaaS, and SaaS?

What is Azure?

According to Microsoft. “Microsoft Azure is a growing collection of integrated cloud services that developers and IT professionals use to build, deploy, and manage applications through our global network of data centers. With Azure, you get the freedom to build and deploy wherever you want, using the tools, applications, and frameworks of your choice.”

How do I get started in Azure Portal?

MS has a great walk through you can do to get you started. There is a free 30-day trial you can utilize to play around with along with $200 in Azure credits. I highly recommend getting an account and clicking through everything just to get the feel of all the offerings it has.

http://account.windowsazure.com

What is the difference between IaaS, PaaS, and SaaS?

You may have heard or seen the acronyms IaaS, PaaS and SaaS. Well what are they? Let’s start with their definitions and then how it pertains to SQL Server.

What is IaaS? (HOSTING)

Infrastructure as a Service or IaaS – Microsoft provides infrastructure capabilities such as an operating system, storage and network connectivity in a cloud offering. Basically, it’s the same as you would have on Premises, Virtual Machines and all its requirements to run your applications. You are able to install software such as SQL Server (aka SQL Server in IaaS) and configure as needed. They host your applications and workloads just as you normally have used, only difference is that it is in the cloud (their data centers). This is very similar to the concept of using a co-location facility (CoLo) data center to store your servers, only with a lot more automation and features. One of the biggest benefits being that you do not have to maintain the underlying hardware or data center.

It’s like asking a Network\Storage administrator to setup a virtual machine for you and you can decide on all the requirements you want. Such as I need 5 drives with X amount of storage on certain types of disks, and this many CPUS.

What is PaaS? (BUILD)

Platform as a Service or PaaS – This is the next level they offer in which you do not have control over the infrastructure and don’t install the software. That is all chosen (standardized) for you based on your “tier” requirements and the platform you need, such as SQL Server (aka Azure SQL Database) or MySQL/Postgres. I will cover more on these services in a follow up post. .

I think of PaaS as when you ask a Network\Storage administrator to give you a box to install SQL on and they give you a Templated VM with all it parts configured including SQL Server already installed. MS offers many different PaaS services – including Cloud Services, Websites, Storage and Azure SQL Database.

What is SasS? (CONSUME)

Software as a Service or SaaS – This simply put are things like Office 365. It’s applications that are consumed in the cloud, no hardware or software is maintained by the company. You just pay for the service and log in to the software essentially.

Summary

So, in Part 1, we’ve covered the basics of what IaaS, PaaS and SaaS means and how they can be leveraged. Next I will cover subscriptions and roles.  As I learn things I will continue to drop little tidbits like this, look for them over the next few weeks.

Blogging at SQLPerformance.com

I am honored to be a Guest Blogger at SQLPerformance.com. As a member of the SentryOne Product Advisory Council (PAC) I will be writing occasionally for the site. You can catch my first blog post It’s Not You It’s Me (I/O Troubleshooting) through simple changes.

SQLPerformance.com is about providing innovative and practical solutions for improving SQL Server performance. Whether you are running a 3rd party application database where very little can be changed, or you are a DBA at a site where getting the application developers to change anything is next to impossible, they cover both the “how” and the “why.”

Time for a Change

I am ecstatic to say I have joined Denny Cherry and Associates Consulting.

Lone No More

I am happy, excited, and nostalgic to announce that I am hanging up my Lone DBA hat and becoming a consultant. Yep, you read that correctly, I’ve decided after 16 years that I am going to change things up a bit. I am switching gears and will be helping those who are Lone DBAs and others by lending them a hand with their work loads.

Don’t get me wrong, I absolutely love being a Lone DBA. So, I will continue to speak on the topic and mentor others in that boat, but it’s time to give myself a little more freedom. Over the past 16 years I have been on call 24/7, even working while in labor, on vacations, nights, and weekends. I really think now is the time to slow down just a bit. My normal speed is 150 miles an hour so down shifting to 100 will allow me to spend time on what is important to me, my family.

Why DCAC?

First and foremost, the people. I am looking forward to working with and learning from Denny (B|T), Joey (B|T), and Kerry (B|T). These guys are wicked smart and most importantly know the importance of the SQL Community. They have a wealth of knowledge to share and I cannot wait to tap into it. With DCAC, I will be getting exposure to so many new environments as they are a renowned global provider of IT consulting and work on the cutting edge. I absolutely love learning new things and can’t wait to dive into things like Azure, which they excel at.

DCAC will give me the flexibility to speak and blog more which I genuinely want to do. Getting out into the community is also a strong passion of mine and moving out of the Lone DBA role will give me greater ability to do so.

Thanks, DCAC for bringing me on board, can’t wait to get started.

About Denny Cherry & Associates Consulting

The vetted and certified experts at Denny Cherry and Associates Consulting assist companies with attaining IT goals such as HA, scalability, SQL Server virtualization, migration and acceleration reliably, while finding ways to save on costs. With clients ranging from Fortune 50 corporations to small businesses, their commitment to each is the same: to provide a deft, high-speed IT environment that leverages every aspect of their platform: from architecture, to infrastructure, to network.

DCAC was named by CIOReview’s 20 Most Promising Azure Solution Providers of 2016.

Among Giants

Since becoming a Database Administrator I’ve always looked at Microsoft MVP’s as the giants in our field.  I never once thought I could be among them. I am very humbled to be recognized as a Microsoft Data Platform MVP for 2017. Thank you to those that deemed me worthy enough to nominate me.

What is an MVP?

According to Microsoft, the MVP Award is an annual award that recognizes exceptional technology community leaders worldwide who actively share their high quality, real world expertise with users and Microsoft. Microsoft MVPs represent a highly select group of experts. MVPs share a deep commitment to community and a willingness to help others.

How did I get here?

There is no magic formula to becoming an MVP.  I blog, I tweet, I did a couple podcasts, I speak at SQL Saturdays, I run my local chapter, and I am a Regional Mentor, but that doesn’t mean you have to do the same.  The point is I try to give back to that community what they have given to me. That’s all it takes. I share what I know and just do my thing, somehow that worked and you can do it too.

What can you do to help others achieve this?

NOMINATE, NOMINATE, NOMINATE!  There are so many valuable members in the community that have not become an MVP simply because they have never been nominated. I’ve had some tell me that they thought I was already an MVP so never thought to do so. I think for a lot of us waiting in the wings for our chance this is the case. So take the time and nominate someone you deem worthy, whether you think they are already one or not.

Here is the link to do so.

https://www.mvp.microsoft.com/en-us/Nomination/NominateAnMvp

Thank You

While I said it above, thank you again to all of those who nominated and believed in me. I could not have done it without the support of the #SQLFamily all these years. I’m honored to be a Microsoft MVP.

Lone DBA Podcast

I recently had the pleasure of being a guest on a Podcast episode with the SQL Data Partners Carlos Chacon (B|T) and Steve Stedman (B|T).  If you haven’t had a chance to attend one of my sessions on Survival Tips for the Lone DBA, this is great insight into it. I share via questions and answers how it is to be a Lone DBA.

http://sqldatapartners.com/2017/03/28/episode-89-lone-dba/

Trouble Shooting Little Jewel

I didn’t know about this little gem, so I shared it with my team and they didn’t know about it either. Then I tweeted about it and found others who didn’t know about it, so I decided I should write a short little blog.

Denny Cherry (B|T) posted a blog on twitter telling everyone to blog as much as possible no matter how small the topic, so I figured why not do this one.

So What Is It?

Windows PSR “Problem Steps Recorder”.  It’s a nifty tool that helps you trouble shoot a computer problem by recording step by step what the user is doing.

How to:

  • Go to Start
  • Windows Accessories
  • Steps Recorder or Problem Steps Recorder depending on Windows version
  • Select Start Record (App will popup)

  • Now start recreating the steps
  • As you are walking through you can even choose to Add Comment to annotate your screen

Once you finish recreating the steps for troubleshooting just hit Stop Record and it will create a ZIP file.

Output looks like this (note it captured all 3 of my screens)

By default it will only capture 25 screen shots, if you need more you will have to increase that number in settings. You can also choose whether or not you want screenshots taken.

Go to the Drop down arrow by Help Menu

Choose Settings

That’s all there is to it. Quick and simple trouble shooting jewel I never knew about.

*Note to Denny regarding your blog post above it took a little more than 10 minutes to write but not much more.

Ooops! Was that me? (Blog Challenge)

We have all made mistakes in our careers, I thought I’d share one of mine as a quick tip to others so that you don’t make the same one.

Everyone has their SQL Alerts setup right? If not, I have included the script below and here is the MSDN link to find out more (https://msdn.microsoft.com/en-us/library/ms180982.aspx).

alert-list

For those who have setup their alerts, how many of you have remembered to set the DELAY BETWEEN RESPONSES setting?

alerts

When I worked at the Port of Virginia, I was a little less experienced in SQL and didn’t notice this lovely little option. I of course failed to set it. Can anyone guess what happened? YEP, we got low on resources in the wee hours of the morning and SQL kicked off an Error 017-Insufficient Resources. Thousands of emails were generated and caused the Exchange server to go down as well as some other issues that arose because of this. The worst part is that all the emails had to finish processing before we could delete them from the system. I think when all was said and done there was well over 250k messages it created.

So the morale of the story is, pay attention to this little tiny option when you set up your alerts your Exchange Admin will thank you for it.

Blog Challenge

oops

Do you have a “Oops was that me” story to tell? If so, share it using hash tag #sqlmistakes. Link back to this blog, so we can all learn from each other.  I can’t wait to hear your stories.

Create Alert Script

 

Run Book, Run!!!

run-bookHow many of you actually have a “Hit-by-the-Bus” handbook? What is that, you ask? It is a document that explains how to execute all your jobs and SSIS packages. In addition, I preference mine with all key elements someone might need, like where passwords are stored, architectures, backup times, where are backups stored, etc… then dig into the job steps. The purpose of this document is so that someone with some SQL skills could step in if needed. You never know when you will be hit by a bus or win the lottery and someone has to take over for you.

Important things to note:

  • Step by Step with Pictures
  • Diagrams – Pictures are worth a thousand words
  • Plain English— Do this, then this, because of this, and watch out for that
  • Jobs- Rerun information, what to do if fails, what not to rerun when
  • Make a HARD Copy

Here is an example:

SERVER NAME

JOBS

LoadEDIDataandValidate: Imports a file \\EDI_FTP\CUSTOM_HOLD_RELEASE\EDI35020110908.log of EDI records that were sent from Gentran to Server A and Server B. It then validates that Server A and Server B have posted those records to their systems. Alerts are sent when something does not post with 15 minutes or record is in QUEUE status on Server B for more than 60 minutes. Server A and Server B data are kept separate on purpose do not combine those tables. As of 3/9/2015 It also sends out a TXT message if count is >50 that have not been posted.

Schedule: Runs Daily every 15 minutes between 2:16 and 11:21 am. This corresponds with 15minutes after Gentran begins and ends its daily processing.

Steps: Executes SSIS Package EDI350Import.dtsx and executes 2 stored procedures; jobValidateEDIServerAEDI350ServerB and jobValidateEDI350

Rerun: Can be rerun any time. Right Click on Agent job and Choose Start Job at step… There is only one.

sample

Here are some other examples of rerun information (try to be a clear as possible:)

Rerun: Can be rerun prior to 4 pm. If run post 4pm you’ll have to manually change the date (@pdate) of the data being pulled. Always verify no partial data was brought into table before rerunning clear out any data loaded.

Rerun: Do not rerun. Load the data manually to Server X for any missing data and use date_billed as key field for data pull

Rerun: This job will fail if there is a duplicate XXX number. You’ll need to resolve the duplicate before you can successfully rerun. It can be rerun prior to 4 pm. If run post 4pm you’ll have to manually change the date (@pdate) of the data being pulled. Always verify no partial data was brought into table before rerunning clear out any data loaded.

Why Share My Knowledge?

Don’t try to build job security into what you do. I know many that worry about giving up the knowledge to others. Having the sole “how to” knowledge for some, gives them a sense of job security. While to a point that might be true, it also locks you in to your current position. Many that hoard their knowledge never advance because they find themselves invaluable in their current position. “We can’t move them because they are the only ones who know about such and such”. Why put yourself in that position? If you can’t ever be replaced, you also can’t move up.

As a lone dba, I find this run book to be vital. It allows me to direct someone to the book and I can walk them through running anything I need them to in my absence.  It allows me to take a vacation or a day off while giving others the tools to get things done.

Why is it important to have a hard copy?

I’ve found over the years having some tangible steps in hand to follow and make notes helps those who have to cover for me. It’s very easy for them to grab a book off my shelf and follow step 1, 2, and 3. It also gives them a place to take notes as they go through the steps that I can later use to modify documentation for better clarity.

If you don’t have a run book I highly suggest you take the time to make one. Now keep in mind a run book is only a helping guide. I automate as much error handling as possible and build in code to minimize the use of this of this book.  However, in my opinion it is invaluable.  The book can give you some space for someone else to cover for you and when that day comes when you win the lottery, you will have left everyone with great notes on how to run things.

Now, off to buy that lottery ticket. Wish me luck!

Hide and Group Columns in SSRS Using a Parameter

Ever had users come to you and request another version of a report just to add another field and group data differently? Today, was such the day for me. I really don’t like have multiple versions of the same report out there. So, I got a little fancy with the current version of the report and added a parameter then used expressions to group the data differently and hide columns. For those new to SSRS I’ve embedded some links to MSDN to help you along the way.

Current Report

The report gives summarized counts by invoice date.  It currently has a ROW group using date_invoiced and the detail row is hidden from user.

current-report

row-group-2

group-exp3

New Version

To complete the user request to have Item Codes and Descriptions added to the report I need to find a way to group the data by Item and show Item columns without disturbing the current report that is currently used by many consumers.

To Do:

  • Add Parameter
  • Set Available Values
  • Set Default Values
  • Add New Columns
  • Change Visibility
  • Change Grouping to group data using parameter

Step 1: Add Parameter

add-para-4

 Step 2: Set Available Values

add-values-5

Step 3: Set Default Values – I want to make sure my current users get their version of the report simply, so I set it to No (N).

add-default-6

Step 4: Next Add Columns.  I was lucky that the fields (Item Code, Item Desc) the user requested to be add was already part of the dataset used, so no additional coding was needed on the stored procedure.

add-fields-7

Step 5: Next change the Visibility attributes. You want to HIDE the column when the IncludeItemDetails parameter is NOT YES (Y). I did this for both item columns.

visibility-8

visibility-9

Step 6: Next I needed to change the grouping. The report is currently group by date_invoiced only. To make the data now total by Item I need to group it by Item only when the IncludeItemDetails parameter is Yes (Y). I did this using an IIF expression setting it to IF IncludeItemDetails=Y then group using field value else don’t (0). Again I did this for both fields.

grouping-10

expression-11

espression-12

You will see it’s relatively simple to do, and prevents a whole new report version from being created. For you beginners out there, it’s a very easy way to start to minimize the number of reports you have to maintain. Try it.