SQL Server with Mr. Denny


June 8, 2016  4:00 PM

Getting around annoying outbound firewall rules at venues when presenting.

Denny Cherry Denny Cherry Profile: Denny Cherry
Azure, Firewalls, SQL Server, Windows Azure

Presentations that are given are user groups, events, etc. often require demos. Sometimes those demos are to large or comple1714341218_b139dfb7c2_zx to run on a laptop. To get around this problem people will use Azure for their demos. Doing this, depending on the demo, requires using RDP to connect to the VM in Azure so that you can run your demo.

But what happens when the network administrator has decided that they need to secure the outbound network connections and make it to that they prevent network connections on random TCP ports? This blocks RDP from working.

When you spin up a VM in Azure it uses a random port number as the network port that RDP listens on to make it harder for hackers to find your RDP port. Random port numbers are also used because every VM within a single resource group shares a public IP Address, so in order you to be able to RDP to the machines, every machine will need to have RDP setup on a different port number.

We can use this ability to gain access to our Azure VMs and get around this annoying practice of having the output connections blocked.

Now I should be clear that this isn’t required all the time, only when the network is blocking the outbound connections.  Thankfully it doesn’t happen all the time, but it does happen. I’ve been at some corporate venues doing training for corporate clients where this has come up, as well as at some SQL Saturday events. Now there’s nothing that the event organizer can do to solve the issue, the network admins sadly aren’t going to change anything just for us. But thankfully they don’t need to, we can reconfigure our Azure VMs a little so that this isn’t an issue anymore.

Even the most locked down network is going to allow web traffic. That’s TCP ports 80 and 443. As long as that traffic is allowed, we’re good to go. What we’re going to do is change the random TCP port that the Azure firewall is using for RDP access and have it use port 443 instead.

To make this change, log into the Azure portal and open the properties of the VM that you want to work with. Select Endpoints from the VM’s settings. It’ll look something like this (the public port number will probably be different).

rdp1

Change the public port number from whatever it is to 443. Do not change the private port number. Then click save at the top. It’ll take a minute or two for the firewall to be reconfigured. Once it is, download the new RDP connection file by clicking the Connect button on the VM’s properties blade.

You should now be able to connect to your VM.

If you have multiple VMs in a single resource group you’ll only be able to set one of them to use port 443. So just use one VM and use it as a jump box to then access all the other VMs.

Denny

June 1, 2016  4:00 PM

Sometimes you just need some physical hardware

Denny Cherry Denny Cherry Profile: Denny Cherry

We’ve been doing some really cool things in Azure recently with some of our clients as well as hosting our own websites in Azure pretty successfully.  But we decided that sometimes just having some physical hardware comes in handy sometimes, especially when you want to build out weird or large environments to test things for customers.  And sometimes the cloud just isn’t the right place to try and do all that.

Well, thankfully Denny Cherry & Associates Consulting doesn’t have to just want to do this anymore.  We’ve just built out a brand new hosting environment in a CoLo here in downtown Los Angeles.  As lab environments go it’s a pretty nice config.  We’ve got 32 physical cores, 432 Gigs of RAM (which we’ll be upgrading later to 576) spread across 4 physical hosts, and ~20 TB of Tintri SAN space.

Given that testing out most workloads doesn’t take a whole lot of CPU power or RAM, we can easily run 100+ VMs (less if we need some actual resources).

We’re configured to be able to test show multi-subnet clustering just like in a multi-site environment and just about any on-premises configuration that a client wants to see.

This is just one of the benefits that we are bringing to our clients when we work with them.  If you want to be able to see how AGs work in an on-premises environment, or test out some weird replication configuration you don’t need to have the resources needed to spin all that up for that testing.  We’ve got the hardware and the software to help you do that testing.

I’m a big rebel, so I snuck a pic of the gear in the rack.  It’s amazing how much processing power you can shove into such a small space.  We’ve obviously got plenty of room to grow (if any other vendors want to see their storage in the pic, feel free to get in touch with us.  As our clients need more CPU power and RAM for testing out new configs for their systems we’ll just have to expand out the server farm.

It’s also amazing how big the boxes are to hold that little bit of hardware (scroll down for that pic).

Now you may be asking why don’t we just do all this in Azure?  And we could, but the reason we didn’t is pretty straight forward. Cost.  Building tons of VMs in Azure and leaving them running for a few weeks for customers can cost a decent amount pretty quickly, even with smaller VMs.  Here our cost is fixed.  As long as we don’t need another power circuit (we can probably triple the number of servers before that beco20160516_175039 - Smallmes an issue) the cost is fixed.  And if we need more power that’s not all that much per month to add on.

All and all, this will make a really nice resource for our customers to take advantage of, and give us a place to play with whatever we want without spending anything.

Denny

 


May 25, 2016  4:00 PM

How can you tell how many NUMA nodes your SQL Server has?

Denny Cherry Denny Cherry Profile: Denny Cherry
NUMA, SQL Server

Back in the good old days (the late 1990s) you didn’t need to worry about things like NUMA unless you were running the top 0.001% of workloads. Today however even a fairly small server can have multiple NUMA nodes. NUMA configurations aren’t just for physical SQL Servers either. Virtual Machines can have NUMA configurations as well (typically called vNUMA).

There’s a few different ways of figuring out how many NUMA nodes you have in your server. If you are logged onto the server via RDP or on the physical console you can find this just by opening task manager. Once task manager is open go to the Performance tab (if it isn’t visible select the “More Details” button at the bottom) and select the CPU graph. You should see a graph on the right. Right click on that graph and select “Change graph to”, then you should see a NUMA node option. If the NUMA node option is greyed out, then you have one NUMA node. If it isn’t greyed out then select the option and however many graphs you see is how many NUMA nodes you have. It should look something like this.

CPU

The second option is to use SQL Server Management Studio. Connect to the server using the object explorer. Right click on the instance in the object explorer and select the CPU tab. Expand the “ALL” option. However many NUMA nodes are shown is the number of NUMA nodes that you have as shown below.

SSMS

You can even expand each NUMA nodes to see which logical processors are in each NUMA node.

The next way to see what your NUMA configuration is, is to look at the ERRORLOG file. Every time SQL Server is started it will log the NUMA configuration towards the beginning of the ERRORLOG file. That line will look something like this example.

Server Node configuration: node 0: CPU mask: 0x00000000000000ff:0 Active CPU mask: 0x00000000000000ff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

For each NUMA node there is in the server there will be a line in the ERRORLOG file. The CPUs which are in the NUMA node are shown based on the values in the CPU mask and the Active CPU mask. In this case the server has a single NUMA node with 8 cores in the NUMA node. How do we get a value of 000000ff for 8? This is hex, so the first core is 1, the second core is 2, the third core if 4 and the fourth code is 8. Add those up and you get 15. Because we’re dealing with hex, we count 1 through 9, then to get beyond 9 we use letters. A is 10, B is 11, C is 12, D is 13, E if 14 and F is 15 (yes F is supposed to equal 16 but if we start counting at 1 and skip 0 it works, so work with me; all you need to remember is that F means 4 CPUs). In almost all deployments the number of CPUs will be some multiple of 4 so you’ll end up with either 0s or Fs. If there’s other values in there that might be ok, or it might not. More digging will be required.

You can also use T-SQL (or performance monitor) to see how many NUMA nodes there are. By looking at the sys.dm_os_performance_counters DMV you can see how many NUMA nodes SQL sees. The query below will give you the exact number. For every row it returns there will be one NUMA node.

select *

from sys.dm_os_performance_counters

where object_name = ‘SQLServer:Buffer Node’

and counter_name = ‘Page life expectancy’

How many NUMA nodes is too many? Like most questions, that’s a pretty loaded question. If you have more than 4 then odds are something is configured incorrectly. If you have more than 8 I can pretty much guarantee that something in configured incorrectly. If you have one or two, that’s probably OK. The general rule of thumb that I tell people is that if you have more than 2 it needs to be reviews because it’s probably wrong. The most that I’ve seen is 16 NUMA nodes on a box, and that starts going some REALLY strange things. But more on that on another day.

For now, let’s just be happy that we know how many NUMA nodes are too many, and how to figure out how many we have.

Denny


May 18, 2016  2:00 PM

Move on Brogrammers, your comments aren’t welcome anywhere.

Denny Cherry Denny Cherry Profile: Denny Cherry
Behavior, Communication, community, SQL Server

Recently Jen McCown received a speaker evaluation form from a SQL Saturday attendee.  Now normally this wouldn’t be a blog worthy event as this happens several times a year as there are easily 100 SQL Saturday’s a year..  The reason that this speaker evaluation form is blog worthy is due to the comment which was left.

The question on the form was “What could the speaker do differently to improve?”.  Acceptable answers typically are something like “speaker slower”, “more demos”, “fewer demos”, etc.  However on this specific evaluation form the attendee wrote “wear négligée”.  Jen was kind enough to post the evaluation form for us all to see.

SpeakerEvalIn case you weren’t aware, and apparently at least one person isn’t, this is completely unacceptable.  This would barely be acceptable if her husband, Sean, had written it (he didn’t, she checked), from a stranger it’s unwanted, immoral, and completely unacceptable in either a professional or social setting.

Clearly someone needs to be reminded how we act in civil society.  We don’t tell total strangers that we want to see them standing around in their underwear.  Just because you’re seen someone give a presentation you aren’t in a relationship with them.  You don’t get to post these sorts of things.

Speakers in our community are not your playthings to do with as you please.  They are giving their own time and money to put together presentations and attend the events which they give the sessions at.

If you think that these sort of comments are acceptable, let me tell you now once and for all, they aren’t.  Not to a male presenter and not to a female presenter.  All the presenter is looking for, after spending their time and money is a round of applause and if you liked the session a nice “thank you”.

If you want to write this sort of thing on a speaker evaluation form, stay home. Your attendance isn’t required.

Denny

 


May 13, 2016  10:00 AM

Recommended reading from mrdenny for May 13, 2016

Michael Tidmarsh Michael Tidmarsh Profile: Michael Tidmarsh
SQL Server

Recommended reading from mrdenny for May 13, 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.

Hopefully you find these articles as useful as I did.

Don’t forget to follow me on Twitter where my username is @mrdenny.

Denny


May 11, 2016  4:00 PM

Viewing task status for related objects in the Azure Portal

Denny Cherry Denny Cherry Profile: Denny Cherry
Azure, Windows Azure

When working in the Azure portal finding task information when performing tasks against an object that is related to an object can be tough.  Before I dive into the how, let me demystify what I’m talking about a little more, as that first statement is a little vague.  In the case of my example I want to see the status of building an Azure Internal Load Balancer which I was deleting using the Azure portal.

There is no section within the Azure portal that you can use to see Internal Load Balancers directly.  You can only see them from within the Virtual Machine.  But if you are deleting them from the portal then you might not be able to see the Internal Load Balancer anymore.

So to see what’s happening to need to know where to find this related information in the Azure Portal.

The trick to doing this is looking at the events which were done on the resource group within the Azure Portal.  Now the nice thing about these events is that it doesn’t matter if the events are triggered through the Azure Portal or Azure Powershell.

To get to these events, you can either click on Resource Groups from the main Azure panel (you may need to click browse if it isn’t shown as one of your favorites) like you see below.

panel

Or you can go through another object within the Resource Group such as a Virtual Machine.  Personally I usually end up clicking through from a VM, because I’m usually working on a load balancer which is done from the settings of the VM, so I already have that panel up.  You’ll find the Resource Group at the top of the main panel of the VMs just under the settings button as shown below.

 

panel1

Either way you get there, you’ll end up on the properties of the Resource Group.  With that panel open, scroll down on the panel and you should see a monitoring tile that looks something like this.

panel2

While that is a very pretty little tile it doesn’t give a whole lot of details.  But you can click on the tile and get a lot more useful information.  When you click on the tile the new blade that opens shows the same basic graph, but bigger.  If you scroll down in that blade you’ll see details about those events as shown below.

panel3

If you click on the text in the Operation column, another blade will open which gives you the details of that operation.

panel4

In the details blade you’ll see all the various steps which were required for this operation to happen.  If we click on one of those details, yet another blade opens.  If we scroll to the bottom of that blade we’ll see some useful text which looks mostly like English.

panel5

Now in my case it’s a pretty easy to read error message.  The Internal Load Balancer couldn’t be created because one already existed in the resource group, and you can only have one ILB per resource group.  Sometimes the error text isn’t displayed at all, or it isn’t the most clear information in the world.  It all depends on what you are trying to do and why it failed.

If you have commands which you run in PowerShell that throw a generic error message, you’ll probably get more details from in here that you were able to get in PowerShell as it depends where in the Azure stack the error occurred and if that component was configured to bubble the actual error message up or just log it and throw a generic error message to the calling application, your PowerShell window in this case.

In any case, this should help you find the information that you are looking for in Azure when things don’t quite go according to plan.

Denny


May 4, 2016  4:00 PM

When using Always Encrypted in SQL Server 2016, how do I as the DBA view the data?

Denny Cherry Denny Cherry Profile: Denny Cherry
Data Encryption, SQL Server

The short answer is, you don’t.

The entire point of Always Encrypted is that the DBA can’t view or modify the data using SQL Server Management Studio. The only way to view and change the data is by using the front end application that created the data. This means that if someone submits a ticket to your team to change some data, you won’t be able to. You’ll have to refer that ticket back to someone in the business to have them make the change using the application just like a normal user would as you can’t touch the data.

Denny


April 27, 2016  4:00 PM

Differences between data types between SQL Server and Oracle

Denny Cherry Denny Cherry Profile: Denny Cherry
Data Types, Oracle, SQL Server

Recently there has been some talk online about how SQL Server processes data type conversions compared to how Oracle’s method for type conversion. I figured that I would clear up some of the confusion that’s out there.

When concatenating a number (like an INT) to a string (like a VARCHAR) you get an error message in SQL Server, but no error message in Oracle. The reason for this difference is due to the data type precedence differences in the two platforms. In SQL Server the database engine is trying to convert the VARCHAR value to an INT value (in this example) because INT is higher on the data type conversion order. Oracle on the other hand when concatenating a number (NUMERIC) to a string (like VARCHAR2) does not through an error message because it is converting the number to a string, then concatenates the string values.

If you create a table with a numeric datatype, and use a string value in the WHERE clause both platforms will throw an error because the data types don’t match.

I’ve also seen questions about why SQL Server has so many numeric data types when Oracle only has one. Having the different data types in SQL Server helps to optimize the storage of numbers, and to make it easier to predict how must storage your table will need (I’m assuming no compression in either case). With Oracle it requires one bit per value of 16 (remember data is stored in binary, not as actual numbers) (assuming that the value isn’t mostly zeros such as 100000 or 1000000000 where each of those values only requires two bytes of storage). In SQL Server, the space needed depends on the size of the data type with TINYINT taking 1 byte, SMALLINT taking 2 bytes, etc.

With data compression the storage changes for both platforms allowing numbers to be stored using less space. However, Data Compression in SQL Server requires Enterprise Edition and Advanced Compression in Oracle requires Enterprise Edition as well as the Advanced Compression feature to be purchased.

Denny


April 20, 2016  4:00 PM

Does your User Group need a free WordPress hosting platform?

Denny Cherry Denny Cherry Profile: Denny Cherry
community, Community service, SQL Server, User groups

Is your user group currently hosting their website that isn’t giving you the features that you need?

Is your user group currently paying to much (or anything) for your website?

Would your user group to be able to be hosted for free, on a WordPress platform where you can have basically any theme, and any plugin available to you?

Have I got a deal for you.

Several people that I know run user groups, and they haven’t been happy with their hosting solutions for one reason or another. So I decided to do something about it. So I’ve put together the website SQLUGS.com (SQL User Groups) which will happily allow you to run your own website as something.sqlugs.com for free (we’ll be happy to host for other user groups as well, if you’d like to be hosted here as well).  This platform is a WordPress powered solution with each subdomain as a network website so you get all the power of our already configured MySQL cluster which we have build and the web tier is built on an Azure Web App so it’ll scale beautifully as load comes and goes throughout the day.

Currently we’ve got a few plugins and themes configured.  If you want different themes and plugins just let us know via the support page and we’ll get them added to the site (it’s a requirement of WordPress that only the site owner installs plugins and themes as it’s a shared codebase).  But we’ll be happy to install anything on the environment as long as it doesn’t adversely impact performance of the rest of the network.  As new plugins are installed you’ll have control of which ones you have enabled on your sites.  We have a few turned on which are mandatory, which are mostly security related or infrastructure related (backups, forcing SSL when you login, etc.).

If you would like to host your user groups website in our network, just fill out the request form and we’ll get you setup as quickly as we can (we don’t have auto signup enabled because of spammers, scammers, etc).

Does your user group have their own domain name currently that you want to use to use? No problem, we support that as well. It just takes a little more work to setup on our side. If you want SSL for your domain we can do that as well, but we’ll require that you provide the SSL Certificate (we provide one for your something.sqlugs.com site). As long as there’s no charge for us to host it in Azure, then there’s no charge to the user group (based on pricing changes for SSL last week SSL certs should be free unless the pricing changes again).

We hope that you’ll take us up on our free hosting for your user groups.

Denny


April 12, 2016  4:00 PM

And with that, SQL 2005 is out of support

Denny Cherry Denny Cherry Profile: Denny Cherry
performance tuning, SQL Server, SQL Server 2005

As of today (April 12, 2016) Microsoft no longer supports SQL Server 2005. If something goes wrong with your SQL 2005 database server at this point, there’s not going to be anyone at Microsoft who can help you.

However we will be happy to help you. Do we love working on 10+ year old versions, no not really. But we are realists. You have SQL 2005 systems which haven’t been upgraded because you haven’t had the time to upgrade them, or because your vendor application doesn’t support anything newer than SQL 2005.

These systems still need performance tuning.

These systems still need upgrading.

And unlike a lot of other consulting companies out there we will be happy to help you performance tune these systems, and we are also happy to help you upgrade your systems from SQL 2005 to newer versions. Just because Microsoft doesn’t support the older versions anymore doesn’t mean that your older systems have magically been upgraded or are no longer needed by your enterprise.

If you need someone to do some performance tuning on your SQL 2005 server, or you need some help upgrading your SQL 2005 systems to a newer and Microsoft supported version of SQL Server, drop us a note.

Denny


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: