Bloggers Should Have Reach

One of my goals for 2018 is to expand the reach of my blog.

As a blogger starting out I had my own site I posted to, but my blogs only reached those who followed me on Twitter, Facebook, Google + and LinkedIn. My blog automatically posts to these three sites using a WordPress Jetpack Plugin called Publicize.

After a few months, I discovered that syndication could be setup on other sites such as SQL Server Central.  This allowed more people the opportunity to read my writing and expand my reach. Many sites, such as these will even send your blog out in newsletters like Database Weekly.

Starting last week, I wrote my first article on Simple Talk and I hope to have more in the future. This week some of you may have noticed my tweets regarding C# Corner. I want to thank to Mahesh Chand (T), Microsoft Regional Director and MVP for introducing me to it.

C# Corner, founded in 2000 is one of the largest software professionals (developers, IT pros, database) online community with 5.5 million monthly visitors and 2.4 million registered members. C# Corner contributors are industry influencers, MVPs, authors, and trainers from around the globe. C# Corner is always looking for industry experts for their opinions, contributions, and expertise to share with its millions of users. Since posting to their site, I have already had over 7,000 views and exposed to millions of readers worldwide.

Websites such as SQLServerCentral.com and C# Corner are just two examples of ways to share what you have written beyond your own website. There are thousands of sites and readers out there looking to learn what you have to share.

If you’re looking to expand the reach of your writing, I highly encourage you to check out this site.

AHHH I need a Blog Topic!!!

One of the hardest things you can do as a blogger is to come up with a post topic. Do you make it simple for newbies, technical, or something personal?  After figuring out a topic, now you have to write.  However, there is a difference in what you say and what will people actually want to read.

Blogging is not easy, but without it, all of our google searches to help solve problems would be much less fruitful.   It’s important to put your experience into written words to help others, and let’s be honest lots of us use out blog posts to remind ourselves of how we did the first time. So, I figured I’d take a minute and let you know what I do to come up with a topic. Maybe it could help others break into the blogosphere or fix their writer’s block.

First, I look at what I have done recently in my job.

Did I fix something?

Come across an error?

Did I find something I didn’t like?

Did I find something I really liked?

Did I just do something really interesting that I got excited about?

If those don’t help I move on to.

Is there something I wish I knew when I was starting out in SQL Server?

A tip?

An option I didn’t know?

A how to?

Nothing coming to mind to write about still? Then I move on to complete RANDOMNESS.  Yes, you read that correctly.   I will go into SQL Management Studio and randomly pick a check box or option and research, test and then proceed write about it.

Lastly, if I am still at a loss I’ll write something like this post.   Something I think might be useful to others that is just a stream of consciousness. It may not be ground breaking, but it might get someone else thinking or motivated. My point of this, is that not all blogs you create have to be ground breaking, technical, or even long in length. Just blog, say what you want to say.

It makes a difference in more ways then you know.

Thankful DBA

This week is Thanksgiving in the United States, so I thought it fitting to write a quick blog on what I am thankful for as a DBA. These are in no particular order and feel free to respond with something you are thankful for. I’d love to hear it.

  1. Glenn Berry’s Diagnostic Scripts- (B|T) Used these for years. Really a great set of scripts and explanations that we all should be grateful for.
  2. Ola Hallengren’s (BMaintenance scripts. Index Optimization, Backup, and Integrity Checks for all! They have become an industry standard and continue to get better and better.
  3. RCSI (Read Committed Snapshot Isolation) –My Readers can stop blocking Writers! Thanks to Kendra Little (B|T) for this great blog.
  4. SSMS Results to grid and copy with header- I do this a million and one times a day. Ctrl+Shift+C .
  5. Query Store – Having the plan run stats and being able to force a plan, LOVE IT! Thanks Conor Cunningham and Microsoft for that one.
  6. Availability Groups – Easy setup and trustworthy. And, well, I like the name better than Mirroring.
  7. DMV’s (Dynamic Management Views)- Show me the money! It has all the SQL Server Internals goodies, mine for the taking.
  8. Profiler– #ProfilerForLife nuff said, my most trusted friend.
  9. Columnstore Indexes – I feel the need, the need for speed! Who doesn’t like up to 10x Query Performance gains and 10x the data compression?
  10. Paul Randal’s Waits Library (B|T)– I can’t tell you how many times I’ve referred to this. So much useful information!
  11. Adam Machanic’s SP_whosisactive (B|T) – This is my GO TO, for seeing what’s actively going on, it’s the first thing I run.
  12. Sentry One Plan Explorer– Execution Plans on STEROIDS! Yes, please. Love the detail and ease of use.
  13. RedGate’s SQL Prompt- My coding is downright ugly. With a quick Ctrl+K, Ctrl+Y my code is sleek and readable. Not to mention I love the code snippets.
  14. Grant Fritchey’s (B|T) Execution Plans book- I can’t wait for 3rd Edition, someone took my very loved highlighted, tabbed, marked up copy. I need another!
  15. Power BI – It puts the slicing and dicing into the user’s hands, giving Management easy visualizations of their data for analysis. Less reports for me to write, yippie.  Thank you Microsoft.
  16. dbatools – Great Power Shell Modules for migrating databases. No more doing it the hard way.

Last and most importantly I am grateful for #SQLFamily, Bloggers, and Twitter. I learn from you every damn day!

Happy Thanksgiving!

~Monica

Do Not Pass GO!

What is the GO statement and why is it so important to use? When do I have to use it? When do I not use it? These are questions that have passed through my head from time to time while writing T-SQL within SQL Server.

First What Is It and When Should I Use It?  

The GO statement lets SSMS (the interface) know when it’s the end of the batch. It basically defines the scope of what you are trying to send to the Database Engine. The below example sends two separate statements. The first statement changes the database context to run the next statement under, followed by the execution of the SELECT running against the database Demo. Simple, yes.

Example

Gotcha’s

I’ve been caught out by this behavior in the past. Using GO in stored procedures can be tricky. There are times when you want to run a batch of statements together, but if you put a GO into the procedure and compile it you will notice that you lost any code that came after the GO. The GO signaled to that my ALTER or CREATE Procedure statement was done. It then ignored all the statement below it as part of the stored procedure.

Another Gotcha which can be both good and bad depending on your need. A Variable’s life span ends after each GO statement. If you declare a variable, run a statement to populate that variable and use that variable you can no longer use it once you send a GO.

Example

Cool things to do with GO

This is learned by chance just messing round. Did you know that if you put a number after GO it will run those statements that many times? This can be handy for generating a lot of load against a database for demos.

Don’t like the word go, change it. Yep you can change it to anything you want. Tool> Options> Query Execution

Change it to RUNNOW.

Let’s Try

HMMM Why didn’t that work… because I ran it in an existing Open Window (Session).  Let’s try that again.

TADA! Much better.

Now that you know what it does, feel free to advance to GO and collect your $200. Enjoy.

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.”

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

 

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.

 

 

T-SQL Tuesday #84 – Helping New Speakers

Ok everyone; here goes my first crack at replying to a T-SQL Tuesday. For those that don’t know what it is, it’s a Monthly blog topic hosted by a member of the SQL Community. It was started originally by Adam Machanic (t | b)

This month’s topic hosted by Andy Yun (t | b) is on Growing New Speakers, which I find to be a perfect topic for me to leap off from, since this was my first year speaking and blogging.

How did I get started?

I 100% blame Derik Hammer (t | b) whom at the time was running my local user group. After attending just one meeting I was “volun-told” I would be presenting in August. Yep my name was now on the speaking calendar and I hadn’t even thought of a topic, let alone ever contemplated speaking.

My First Steps to Presenting

After the shock wore off, I sat back and began to think of anything of value I could talk about. Since it would be my first time speaking I really wanted a topic I could talk about and not necessarily a technical talk. Thus my Lone DBA talk was born. Everyone has something of value in their career to talk about, for me this seemed logical.

Simple Steps to Get Started

Where to begin is always the hardest part after choosing a topic. This was my approach. Of course there is a lot more to it, but getting this far a huge step forward.

  • Jot down a list of things you want to talk about
  • Then put them in a logical order
  • Then write a sentence or two about each line item

Just taking the time to do this will get you going.

Don’t Be Nervous (HA! Yeah Right)

It’s very hard not to be nervous. The way I “try” to get around this is to strike up a conversation some attendees prior to the start of the session while you are standing up front.  I pretend after the session begins that I am still having that one on one conversation with them.  For me it creates a “friendly” atmosphere rather than one like a teacher\ student. Now my biggest problem is talking fast, I try REALLY hard not to but it’s bound to happen as I get excited about the topic. My point is nobody is perfect at speaking everyone will have their fault, don’t let it discourage you.

Lastly

Start with your user group, listen to feedback, have another review your slide deck, and most of all enjoy it. There is nothing like a “speaker high”. Being able to share your knowledge and influence just one person is very rewarding.