Like most speakers who give presentations using Microsoft Azure, I’ve managed to leave services running for days (or weeks) without realizing it. To help solve this I put together a run book that runs in our Azure account and which runs every night. It turns off VMs, scales SQL DBs down to the basic size, alerts you if there’s any SQL DWs or HDInsight clusters running (after they have been running for 3 days it’ll just delete them for you).
Since we found it so useful we went ahead and published it on GitHub so that we can share it with the community. You can review the code on GitHub here and deploy it to Azure using the big “Deploy to Azure” button available on the GitHub page.
Check the GitHub as we’ll be adding more resource types to be checked and shutdown as time goes on.
I normally announce my PASS Summit Karaoke Party turning my PASS Summit pre-conference webcast, but I’m not sure when I’ll have a chance to schedule the webcast, and I felt that my party announcement was to close to PASS for those that wanted to attend. So this year I’m doing a separate blog post in advance.
This year, I’m pleased to say that for the fifth year in a row (I think it’s the 5th year) the Karaoke party is on again. This years party is sponsored by Idera and SIOS (as well as of course Denny Cherry & Associates Consulting).
This year we’re going back to the bar we were at last year which would be “Amber” at 2214 1st Ave in Seattle. It’s about a 15 minute walk from the Seattle convention center (or so). We’ll have the great band from Rockaraoke once again rocking the house down. I’m told that they’ll be bringing an even bigger and better setup than before. Check out their website for the list of songs that they have in their playbook.
Just like in prior years the party starts at 9:30pm (the welcome reception ends at 9pm) and goes until 1:30am.
Tickets for the party are available through Eventbrite where you’ll see open bar tickets and cash bar tickets. If you want free drinks you’ll want to select the open bar tickets. If those run out (or you don’t want to drink) you’ll want the cash bar tickets. You do NOT need both an open bar ticket and a cash bar ticket, you only need one or the other.
If you haven’t picked up your wristband for the open bar by 11pm that night from the bouncer at the front door it may be given away to someone else.
I look forward to seeing everyone at PASS and as many as possible at the party. We’ll have the up stairs balcony open this year as well as the patio, so there’s plenty of room for everyone.
Join Joey D’Antoni and I on July 29th at 11am Pacific Time for a webcast where we’ll be talking about our first year of managing Microsoft SQL Server 2016 in production. You read that correctly, one of our clients was an official early adopter with Microsoft and they have been running SQL Server 2016 since August 2015.
We’re combining this rather open format webcast with a #datachat on twitter where we’ll be posting questions and answers as well as chatting on the webcast about our experiences with Microsoft SQL Server 2016.
We hope that you’ll join us on the webcast (registration is required). It should be a good time while being informative about some of the issues that we’ve run across.
If you have attended the PASS Summit for the last couple of years you probably heard about the PASS Summit Speaker Idol competition that has happened for the last couple of years. If you haven’t, you are now.
For those that haven’t been to the PASS Summit before its a great conference to learn a ton about Microsoft SQL Server from other people who use SQL Server on a daily basis as well as the people from Microsoft who make and support Microsoft SQL Server.
Becoming a speaker at the PASS Summit is always tough. There are hundreds of people who submit almost one thousand sessions for the conference, and there are only a little over one hundred session slots available. Needless to say the competition is tough, especially for someone who hasn’t every spoken at the PASS Summit so the speaker selection committee doesn’t know anything about your speaking style.
So a couple of years ago I had a crazy idea to steal the format and the idea for speaker idol from the TechEd conference and bring it to the PASS summit. And PASS took a chance and let me run with it.
So now in our third year, the PASS Summit Speaker Idol competition is back. We’ll be using the same basic rules as last year with some slight tweaks.
You must have spoken at one of the following events (in a format other than a lighting talk, where your session had at least 30 minutes of content delivered by you):
- SQL Saturday
- SQL Bits
- SQL Server Days
- Microsoft Ignite
- Dev Connections
- Other large event with a national or international reach
You have not spoken at the PASS Summit of PASS Business Conference for a breakout, spotlight or pre-con session. (If you gave a lightning talk and no other session you are eligible.)
You are not scheduled to speak at the 2016 PASS Summit.
You will be in Seattle during the PASS Summit (attending PASS isn’t needed, and if you aren’t attending PASS we will get you to the session room for Speaker Idol).
You are available to attend which ever speaker idol session(s) you are assigned to (including the final session on Friday afternoon).
You have a 5 minute technical presentation which you can give. (Time management is your responsibility.)
You sign up on the registration form before August 31th, 2016 at 11:59:59pm (23:59:59) Pacific time according to Denny Cherry’s Verizon Cell Phone.
You are not a member of the PASS Board of Directors or an employee of PASS.
You are not employed by a sponsor of Speaker Idol
If a contestant drops out of the contest before the PASS Summit starts the organizer (Denny Cherry) will reach out to the next person on his list that wasn’t accepted and offer them an position in the contest. People will be offered spots in the competition until someone accepts. The organizer will NOT announce how many people if any turned down a spot in the competition.
If a contestant drops out of the contest during the PASS Summit (for purposes of this clause “during the PASS Summit” is defined as from the time that Denny gets to Seattle, which will probably be Saturday or Sunday before the conference, until Friday afternoon) no changes to the lineup will be made.
If a contestant wins their round and can not continue to the finals for any reason, the runner up from their round will be declared the winner of their round and will be moved to the finals.
If a judge is not able to complete the competition they will not be replaced.
The wild card winner will be selected by the judges at the end of the Friday morning round from the runners up from each round. If a winner is not able to compete in the finals, and the runner up is already speaking in the finals, the judges may consider all contestants from that round as eligible for the wildcard slot.
Judges do not have to recuse themselves because a friend is in the competition. Judges are expected to be fair.
You are responsible for your own travel to the PASS Summit to compete in this competition.
You may present your session in any format that you’d like. You can view last years recordings via PASS TV.
While you can use any format that you’d like, it is recommended that you use a format which will show the judges how your presentation skills will translate from a 5 minute session to a 60 or 75 minute session.
Contestants are allowed to watch the other sessions during speaker idol and make changes to their presentation as they see fit.
In case you missed it above, the Sign Up Form is <- there.
Stay tuned for announcements on the judges and the contestants. (Judges won’t be announced until after the PASS schedule is finalized as I need to see who’s available for the sessions.)
You may have heard that there’s a bug in SQL Server 2016 related to the query store in non-Enterprise editions. This is a less than dangerous bug, in that in editions of SQL Server 2016 other than Enterprise Edition when the space allocated to the query store fills it stops collecting data. Nothing more dangerous than that.
What this tells me is that at some point during the development cycle Query Store the Query Store was going to be Enterprise Edition only.
But thankfully someone at Microsoft released this would be an awful thing and they introduced it into all the editions.
This also means that this should be pretty quick to fix in a later CU as it’ll just be a matter of moving the call to the function to clean that up from inside as if/then block to outside the if/then block.
Hopefully this will be that easy to fix, and a fix can get rolled out in one of the first couple of CUs. Until then, just setup a job to clean out the old data from the query store nightly. If you are hitting this bug, I’d also recommend increasing the size of your query store to 1 Gig (or 5 on a really busy system) to help avoid the bug and make sure that you have enough room for data in the query store between flushing the data.
Having too many NUMA nodes can lead to some really strange CPU behavior. What you’ll probably see is that the CPU workload of the machine isn’t evenly balanced across the CPUs. If for example you have 32 cores on the VM, and you have 16 NUMA nodes, with two cores per NUMA node you’ll probably see two cores (might be four, or six it depends on a lot of factors) running very hot compared to the other cores on the server.
At one client I was working with in 2015 they had a server which was configured very similarly to what I’ve described above. They had 32 cores, with 11 NUMA nodes. One NUMA node has 4 cores, the next 9 had three cores each, while the 11th NUMA node had one core in it.
When monitoring the performance on the server what we saw was that three cores were running at 100% (or close to it) while the other cores were running at about 4%.
The reason for this, is that SQL Server is NUMA aware, and it was trying to get the best performance possible out of the configuration that it had access to.
The solution in this case was to reconfigure the virtual machine so that it had only two vNUMA nodes. We did this by changing the number of virtual sockets to 2, and put 16 virtual cores on each virtual socket. Once this was done CPU load on the server was much more balanced across the cores. Now instead of 3 cores running at 100% 16 cores were handing the same workload (that workload was still isolated to a single NUMA node, but now the node had 16 cores in it) but the CPUs were bouncing around the 15-20% range. Suddenly the box was happier, and more importantly the application which the end users was using was behaving much better and queries were responding much faster as they no longer needed to wait for a CPU to become available.
Now you may be asking yourself how a system could end up with a screwy NUMA configuration like this. And the answer is pretty simple. The system grew over time, and more and more cores were added to the server little by little as the years went by. As the system needed more CPU resources more cores were added, but no attention was paid to how those cores were presented to Windows. So we ended up with Windows seeing 32 cores in a very strange configuration.
Because most SQL Servers are running inside VMs these days, we need to be careful how we configure those VMs to ensure that we get the proper configuration of the virtual machine. If we don’t have the correct configuration of the virtual hardware there’s little hope of getting good performance out of the VM.
Check your NUMA node configuration to make sure that it’s correct and if it isn’t correct it. Especially if the configuration is very odd looking. And if you aren’t sure, check with us, we’re happy to help.
I’m thrilled and honored to have been selected to speak at the PASS Summit again. This year I’ll be presenting a session titled Database Security in SQL Server 2016 and beyond. In this session we will review the new enhancements to SQL Server security available in SQL Server 2016 and Azure SQL DB, including Always Encrypted, Row-Level Security, and Dynamic Data Masking, as well as whatever else Microsoft has released since this abstract was written. We’ll look at how to set these features up, how to use them, and most importantly, when to use them.
You can also find me at our booth called “Consultants Corner”, which we will be sharing once again with the team from SQL HA. Be sure to come by our booth, get entered for our giveaway and talk to the two teams who are there in the booth about any problems that you may be having that you want to bounce off of someone.
I was playing around with Azure Active Directory a little while ago, and I saw that Azure Active Directory can be setup to configure your Gmail implementation. This will allow Azure AD to configure new users in Gmail automatically when they are configured. If you have Azure Active Directory configured to sync with your on-premises Active Directory that means that when you create a new Active Directory user on-premises that use will automatically get a Gmail account created.
The configuration was shockingly simple to setup as well. To configure Azure Active Directory log into the Azure classic portal and select the Active Directory option. Open your Active Directory and select the Applications tab. Click “Add” down at the bottom and search for “Google”. Find the Google Apps option from the list and add it. After adding it run through the few page wizard. There’s really nothing to the wizard.
The only catch is that you need to already have your Google Apps account created and you have to have an account in the Google Apps account which has admin rights. You’ll be prompted to log into Google then test the connection. Once that’s done you’ll be taken back to the Azure AD application list. At the end of the wizard you’ll be prompted to select if you want to configure all your users in Google Apps or not. If you select to sync your users to Google Apps you can log into Google’s Admin screen and you should see all your users with accounts created.
And that’s it. When there’s a new user added to Azure Active Directory the user will be automatically created in Google Apps (and Gmail) automatically
If you are running a PCI compliant system on SQL Server 2005 you are going to fail your next audit. One of the audit requirements is that the vendors must support the version of your software which you are running on. As Microsoft no longer offers support for SQL Server 2005 that’s going to cause you to fail your next PCI audit.
Thankfully there is a way around this problem, upgrade. In a perfect world you’ll upgrade to SQL Server 2014 or SQL Server 2016, but in reality anything newer than SQL 2005 will do. The newer the version you upgrade to, the longer you can wait before your next upgrade project needs to kick off.
So if you have any PCI systems (or really anything running SQL 2005, or older) now is the time to upgrade.
(And yes, Microsoft really will not support you on SQL 2005. I just recently told someone to try calling support, and they were told there was nothing Microsoft support could do to help them.)
Recommended reading from mrdenny for June 10, 2016.
This week I’ve found some great things for you to read. These are a few of my favorites that I’ve found this week.
- How many copies do I need?
- Amazon IoT Button Hacking: Part 1
- Checkpoint process for memory-optimized tables in SQL 2016 and implications on the log
- Power BI Enterprise Content Sharing
- SQL Server 2016 features: R services
Hopefully you find these articles as useful as I did. Don’t forget to follow me on Twitter where my username is @mrdenny.