PASS Summit is just a month away. That means there’s only just under a month left to get signed up for Denny’s Azure precon. You might be wondering why you want to take a full day session on Azure infrastructure. And that’s an excellent question. The answer is quite simple. Azure is a massive platform, with a huge number of moving parts which are constantly changing. In this session, we aren’t going to be spending a lot of time on our good friend SQL Server (or SQL DB for that matter) we’ll be focusing on the rest of the Azure platform.
This includes components such as:
- Azure AD
- Subscriptions (Billing)
- VMs in General
- Resource Manager
- Classic Deployments (like 2 minutes tops)
- Open Source
- Scale Sets
- RBAC (now called IAM)
If you have services in Azure today, if you are going to be moving services to Microsoft Azure in the near future, or you just want to get a better understanding of how things work in the Azure cloud, this is the session for you. At this end of this session, you’ll walk away with a good understanding of how Azure components should be configured, and how your services should be configured within the Azure platform to get the best performance and availability from the platform and your solutions.
You may have seen Joey and I traveling around India in 2016. We were actually delivering content similar to this (this is much newer obviously as Azure has changed a LOT in 12 months) to various customers and partners. If you’re using other consultants to help you with your Azure implementation there’s a decent chance that they’ve taken this training.
Not registered, for PASS? That’s an easy problem to solve. Go register. Already registered for PASS and need to add the precon to your
Already registered for PASS and need to add the precon to your registration? No problem, you just need to email PASS to add it to your registration.
Either way, nice and easy. Using Azure, take the precon. You’ll thank me.
If you were planning on attending the PASS Summit Attendee Orientation webcast on September 14th and couldn’t make it for whatever reason, have no fear, it was recorded and the recording has been posted online. If you forgot to register (or if you did) just to back to the registration page and fill it out and the video will magically appear. It’s that easy. The video is about 45 minutes long give or take. We used a new Skype service to broadcast the video so there were some issues at the begining (which I cut from the video before I uploaded it) so you don’t have to watch nothing for 10 minutes.
If you’re just looking for the information on Speaker Idol and the Karaoke Party, that info is linked here. The tickets for the Karaoke party are going fast so get signed up for a wristband if you’re going to be in Seattle on Tuesday night.
It’s that time of year again. It’s time for me to announce the biggest party at the PASS Summit. This years SQL Karaoke party. This year we’ve got a
couple of GREAT sponsors, SentryOne and SIOS who will be joining us back at CowGirls Inc on 1st Ave in Seattle for an all out party to end all parties. We’ll be rocking like always on Tuesday night, but that means that it’ll be Halloween so feel free to come dressed up, or come dressed as you are.
Like always the party starts at 9:30pm and runs until last call at 1:30am.
The registration link is right here, so get registered. You only need either a cash bar ticket OR an open bar ticket, not both. The open bar will get you free beer, wine or well drinks. The cash bar means that you’ll have to buy your own drinks. The open bar tickets are limited and open they are sold out, they are sold out.
So get registered, claim your wristband and get party with us at our great venue. We’ll see you there.
In past years I’ve been really glad that I’m just the MC of the PASS summit speaker idol because of the strong group of speakers. This year I’m really damn glad that I’m just not a judge because of the amazing group of speakers who submitted to be in the speaker idol. Getting the group down to just the 12 was enough of a challenge for me, I do not envy the judges their job of getting down to the one winner.
Your Wednesday lineup for speaker idol is:
Your Thursday lineup for Speaker Idol is:
Your Friday lineup for Speaker Idol is:
Daniel de Sousa
And we’ll see what the Friday afternoon session looks like.
Check back here as we get closer to the PASS Summit to see who our judges are for the speaker idol (I have to see who’s available during the speaker idol sessions before I can select my judges).
To everyone who submitted, both those who were selected and those who were not selected, thank you for submitting. Without you submitting we can’t have this competition.
For those attending the PASS Summit, be sure to check the PASS Session schedule for the speaker idol time slots (usually early afternoon Wednesday and Thursday, then early’ish Friday then late Friday for the finals) and come see these great speakers present.
Not going to happen.
Microsoft’s currently policy is to not deprecate any additional features and to not remove any additional features. This doesn’t mean that they’ll enhance those features, but they won’t be removing them. So get over the fact that they are still there. If you don’t want to use them, don’t. But they aren’t costing you anything by existing.
I was working with a client recently and they had an interesting problem. Recently they moved their application from a physical machine to a VM. And when they did performance took a nose dive. We’ve all heard this story before. They’ve got a crack team at this client and had checked all the usual suspects including adding more RAM and more CPUs (even though CPU was low already).
When I started looking at the system I saw minimal IO, in fact basically no IO on the system. So we got a user on the phone, and I profiled based on their username. I saw a huge number of commands go streaming by, all with a runtime of 0 milliseconds. Nothing really worth noting here except that SQL was only getting 6-8 commands per second. The client application in this case is running on Citrix within the same VMware farm so the client and the database are physically very close to each other, so unless the application is doing a lot of work on each row it should be throwing more commands at SQL than that.
Now in a perfect world I’d have the vendor fix their horrible code so that it wasn’t doing the RBAR, but the vendor couldn’t be convinced that this was a problem because none of their other clients were having a problem. In-fact the other installations at this client weren’t have a problem. But they were all much smaller installations.
What we ended up doing was turning a few things off, both in Windows and in VMware.
In Windows we turned off Receive Side Scaling, the TCP chimney, network auto tuning and task offloading. The thought behind all these components is that they are trying to optimize the network, but doing that takes time. Granted that time is just nanoseconds, but when you’re trying to through thousands of RBAR queries against a server at a time, a few hundred nanoseconds each is going to add up to seconds. When we profiled the server and let it run, we saw more than 16,000 commands being sent to the database just to open a single window in the application.
The code that we used to disable all these features is all done using the netsh command, and is shown below.
netsh int tcp set global chimney=disabled
netsh interface tcp set global rss=disabled
netsh interface tcp set global autotuninglevel=disabled
netsh int ip set global taskoffload=disabled
The vendor also recommended turning off IPv6 but I wouldn’t recommended that, especially if you are in a clustered SQL environment as modern versions of Windows clustering require IPv6.
On the VMware side we also turned off Interrupt Coalescing on the virtual NICs. The goal of having this on, to quote Jim Hannon from House of Brick is “to benefit the entire cluster by reducing the CPU overhead of TCP traffic”. Now in this case we have CPU to burn, and a very latency sensitive application. So turning it off became an option. This is done by changing the VMware advanced setting ethernetX.coalescingScheme (if the setting isn’t there you have to add it). Then set it to disabled like so.
ethernetX.coalescingScheme = "disabled"
With all these changes in place we fired up the VM and got the application user back on the phone and had them click the button again. And we were back in business. Response time was right were we expected it to be, and were it had been in the physical world before the migration.
On Monday, October 30th I’ll be presenting an all day class on Azure Infrastructure at the PASS Summit. During this day long session which will kick off the PASS Summit, I’ll be covering everything that you need to know to setup a solid Azure infrastructure so that the infrastructure you setup for your company today will be able to scale and perform well for many years to come.
Much like designing a data center, designing a cloud hosting solution needs to be done carefully, and if done correctly it will scale beautifully and you’ll be able to most applications of almost any size into it. Conversely, a cloud infrastructure that isn’t designed well won’t be able to scale very well and moving some applications into it can be challenging.
Learning about a technology from someone with a track record of proven successes in the space is key to success. Our team at DCAC has proven success with migrating clients from on-premises to Azure, so much so that we’ve won awards for our work in Azure.
- Azure Storage
- Azure Networking
- VPN Connectivity
- Express Route
- Global Load Balancing
- Azure Data Center Security
- Virtual Machines
- Active Directory Design Considerations
- Azure Active Directory
- SQL DB or SQL Server
- Azure Compute Discussions
- Scale Sets
- Open Source in Azure
- Identity and Access Controls
- Extending Identity to 3rd Party Applications
- Monitoring Azure
Needless to say, this is a very comprehensive day long class which will cover a large portion of the Azure infrastructure as it exists today.
Coming to PASS and thinking about moving services into Azure? Or just want to learn more about Azure so that when you move services into Azure you’re ready with the knowledge you need to help your company succeed? This is the session for you. You can’t do better than the Gold Medal winner in Data Center Innovation for an Azure Migration to teach about Azure.
There’s a lot of misunderstandings about dynamic SQL out there. I have heard this from clients on occasion, and I see it from other consultants on their blogs. If done correctly, dynamic SQL is not difficult to use or troubleshoot. Let’s look though some of the complaints that I’ve seen.
If you’re using NVARCHAR strings, you need to prefix all of your string concatenations with N, or you could end up silently truncating your final product.
Yes, if you are using NVARCHAR you do need to prefix everything with N so that SQL Server knows that all your strings are Unicode. There is no risk of truncate your final product. If you forget to put the N in front of a string when concatenating strings together, the only problem you will have is that a question mark where will appear you should have a Unicode character. You won’t suddenly end up with a shorter string than you were expecting.
If you use CASE to branch concatenation paths, it can also silently truncate a string.
Yes, CASE can do this, if you don’t put an ELSE block in your CASE expression. If you don’t have the ELSE block in your CASE expression, then your code could return NULL from CASE. Unless you’re changed your CONCAT_NULL_YIELDS_NULL setting from the default, this will return a NULL value, which is the expected result. I am sorry, but you don’t get to complain that SQL Server is doing what you told it do. Adding an ELSE block that returns a blank string so that your statement looks like CASE WHEN … ELSE ” END isn’t all that hard.
Figuring out how many single quotes you need to properly quote things is awful, sometimes.
Generally speaking, once you are in a dynamic string instead of a single quote you just need two single quotes for each single quote that you want. Unless you are using dynamic SQL to generate dynamic SQL it shouldn’t be that hard. If you are using dynamic SQL to generate more dynamic SQL (which I’ve done) then you’ll want to stab yourself in the eye.
PRINT is limited to 8000 characters, RAISERROR even fewer.
Both of these statements are true. Keep in mind that PRINT shouldn’t be used to return information to a user; it just returns it to the messages tab in SSMS. In an application that output from PRINT isn’t displayed to the user at all (even though it is sent to the client, so it still has to travel over the network). If you need to get a warning or error to the user RAISERROR would be the way to do that, and 2048 characters should be enough space to do that in, as that’s a lot of text to send back to a user. Especially as most users won’t even read the error message. THROW, which was introduced in SQL 2012, has the same size limitation as RAISERROR.
If you’re using sp_executesql, it’s a real chore to get the variables as the query ran without additional logging.
Passing variables into sp_executesql is pretty straight forward. You simply declare than as an input parameter, which is the second parameter for sp_executesql. Then you pass them in as named parameters to sp_executesql and use then within the dynamic SQL as needed. The nice thing about dynamic SQL is that your can pass in parameters that you aren’t even using, so if you remove parts of the code that use the parameter you can still pass it in to the dynamic SQL and SQL won’t care. As for not being able to log what parameters are being passed in to the query, who cares? Logging the queries that are passed in introduces so many potential security issues not the least of which may include storing PII information in an unencrypted format that I’d never recommended doing this. Not to mention that this is adding additional overhead and risk to the system for not useful benefit. If you need to see how the query procedure dealt with the variables before running the dynamic SQL then
Concatenating non-string data requires CAST/CONVERT.
Yeah, this is a real pain in a strongly typed language. Just like in C, C++, C#, etc. Also stop stringing non-text fields together. You simply pass them into dynamic SQL as variables and handle like you would with normal variables. There are very few cases when I’d want to handle dates and/or numbers (as an example) in this way.
Sometimes surprise NULL concatenations leave you with empty strings.
Yeah, that happens when you concatenate NULL and something together in SQL Server. This is by design. If you don’t want this check for NULL using ISNULL, COLLASE, CASE or change the CONCAT_NULL_YIELDS_NULL setting.
In summary, I’d have to say that Dynamic SQL really isn’t all that hard to work with. If you remember the rules of strings and NULLs then working with dynamic SQL isn’t pretty straight forward. Dynamic SQL makes lots of sense to use in order to reduce the complexity of SQL Statements, and simple SQL statements are easier for SQL to execute.
Well it’s time for another great Data Platform Summit in Bangalore India. The team there has been kind enough to allow me to present a pre-con this year (third year in a row). This year I’ll be changing up my precon a little bit. Instead of talking about SQL Server, I’ll be focusing more on Azure and everything that the DBA that’s going to be moving into Azure needs to know about Azure (or at least as much as I can cram into a 7 hour session, I’ve got 4 days worth of material I can present but only 7 hours to present it).
During the session this year we’ll be learning all about the components that you need to get know about and setup to successfully run a high volume SQL Server in a VM in the Azure Cloud.
This session is really important to the Indian market specifically as Microsoft has just opened new Azure data centers in India in the last year, so more and more Indian companies should be expecting to move workloads to Azure. This session will give attendees the knowledge to become the experts within the organizations to be able to help their companies move into the Azure Cloud.
So what do you do now? You go signup for the all day PreCon session and then come to the session. Nice and easy.