Building Perfect SQL Server Northern New Jersey SQL Server Users Group

Slides on slideshare below:

 

The scripts are located here.

SQL Server Days Belgium

So Belgium is one of my favorite countries in the world—it has the best beer (sorry Germany, and America you are trying hard, but the Belgians have a history), it is the spiritual home of cyclocross (which is one of my favorite sports), and has the best formula one circuit in the world at Spa Fracorchamps (sorry again Germany, the Nordschliefe is amazing, but open wheel cars won’t race there anymore). One additional thing to love about Belgium is SQL Server Days—a great two event, featuring SQL Server experts from around the world, such as Ola Hallengren, Denny Cherry, Buck Woody, Stacia Misner and Grant Fritchey to name a few.

I will be delivering a preview of the three hour session I will be doing at PASS Summit on using Microsoft Azure as your second data center. I’ll be talking about all of the potential backup and disaster recovery options, and which can be right for your solution. Additionally, I will be taking part in the BI Power Hour to talk a little bit about the analytics of Belgian beer using Power BI. I hope to see you there!

 

Figure 1 Sven Nys Koksijde, Belgium

 

Figure 2 Spa Francorchamps Circuit, Francorchamps, Belgium

 

Figure 3 Westvletren Ale

How to Use a Local Drive for TempDB in a Multi-Instance Cluster

One of the cool new features that was introduced in SQL Server 2012 was official support for using a local drive for TempDB in a SQL Server Failover Cluster Instance. In prior versions, this was unofficially supported (it was a bit of hack), but it made sense—TempDB is ephemeral (it gets recreated every time SQL Server restarts) and the increased prevalence of PCI SSD cards we are a great fit for the random IO patterns of TempDB. The only caveat to this is that the drive letter for TempDB needed to exist on all nodes in the cluster (and this doesn’t get checked as part of the cluster setup process, so you’ll want to failover to all nodes as part of your testing).

Recently, I had a chance to test out this setup—but in a slightly more complicated environment. In a two-node cluster with a single instance this is really straightforward, simply create a drive T: (or the drive letter of your choice) on both nodes and you are done. In the case I was working on, I had a four node cluster with three instances of SQL Server—so I would need three TempDB volumes on each node for a total of twelve. I could just assign each volume its own drive letter, but one of the concerns in a multi-instance cluster is running out of drive letters. In order to get around the limitation of only 25 available drive letters in a cluster (remember Windows always needs C:\) is to use mount points.

So I applied the same rules to TempDB—I created 3 volumes on the local SSD on each node:

T:\Instance1\TempDB_SSD

T:\Instance2\TempDB_SSD

T:\Instance3\TempDB_SSD

After this was completed, I failed each instance onto each node in the cluster to ensure that everything worked as expected. This setup is pretty straightforward, but not well documented for this scenario. So, have fun and enjoy good TempDB performance (don’t forget to create at least four files, either!)

 

Azure Virtual Machines and SQL Server—Mind Your Endpoints

I am a big advocate of using Microsoft Azure VMs for lots of uses—many clients don’t have the wherewithal to manage a full scale data center operation, or in other cases they don’t have the budget for a second data center for disaster recovery. Azure is a great use case for those options, as well as quickly spinning up dev environments for testing new releases or doing a proof of concepts. In fact, I’m currently working on a PoC for a client and we are using Azure IaaS and Power BI for Office 365.

The biggest fear most people have around cloud computing is the security aspect—they don’t trust their data not to be in their data center. In general, I think Microsoft (and Amazon) have way better security than most data centers that I’ve ever set foot in, but whenever you are using a cloud provider, you have to have a good understanding of the nuances of their security model. One thing to note about Microsoft Azure is that all virtual machines get their own public IP address (personally, I feel like this a waste of a limited resource, as VMs that are within virtual networks generally have no need for a public facing IP address, but that’s a different blog post) and security is provided by creating endpoints (by default the SQL Server template opens PowerShell, RDP and 1433 for SQL Server). Access to these endpoints can be controlled by ACL—you can define a list of IP addresses (presumably the other machines in your network) that can talk to your VM over that endpoint. However, by default, your new VM is accessible on port 1433 to the entire internet.

I was troubleshooting connectivity from my SharePoint VM to my SQL Server VM this morning, and I went to the SQL Server log, and I found:

 

Figure 1 Log of Failed Logins

Those IP addresses aren’t on my virtual network, and they aren’t the public IPs of any of the servers in my network. Let’s use an IP lookup service to see where they are from:

 

Figure 2 Ip Address #1 Nanjing, China

 

Figure 3 IP Address #2 Walnut, CA

 

As Denny Cherry (b|t) mentions in Securing SQL Server having an SA account named SA and enabled is a definite security risk. Since SQL Server accounts won’t get locked out from failed password attempts these hackers know half of the battle, and they are hammering my VM trying to guess the SA password. Chred1433 seems like an interesting name for a user (or a hack attempt at SQL Server) and kisadmin shows up in this list of attacks on SQL Server.

Securing Your VM

So what does this mean for you? If you have VMs in Azure (or in your own data center—this is just general security best practices):

  • Never expose port 1433 to the internet. There are some scenarios where you have to, but I try to always work around this
  • Always disable your SA account—use domain groups for access to SQL Server
  • When launching a SQL Server VM in Microsoft Azure either disable the endpoint on 1433 or use ACLs to limit access to specific machines
  • Use Azure Virtual Networks and Gateways to connect securely to Azure Infrastructure—when you have a virtual network, you never have to use the public IP address, and all connections can take place over secure VPN connections

No one wants to have their data breached—so make sure to follow these steps!

June was a Good Month

The month of June and into July have been very good to me. Along with my great volunteers at PSSUS, Microsoft, and all of our wonderful sponsors we had a great SQL Saturday event on June 6-7. Allan Hirt, Stacia Misner and myself had great precons, and almost 60 hours of training was provided to our attendees.

A few days after that happened, I got an email that I’d been awaiting for a long time—Microsoft was awarding me as a SQL Server MVP. I can’t begin to describe how humbled and honored I was to receive this award. I don’t do all the things I do in the community because of recognition (I do it because I love my friends in the community and its fun), but it is really nice to get recognition for the work I’ve done. There are entirely too many people to thank, for their assistance and guidance with my career progression as a presenter and writer, but I thank you all for helping to get me where I am.


 

 

 

 

So that happened, and then I got to Germany for a bit of a vacation. For those of you who don’t know, I’m a pretty big fan of auto racing. Germany is home to one of the largest, fastest racetracks in the world, the Nürburgring Nordschleife. We were lucky enough to be there the week of the 24 Hours of the Nürburgring. We didn’t stay for the race, but went to a qualifying day. It is incredibly impressive to see people driving fast cars around the plunging, twisting circuit that Formula 1 abandoned in 1976 for safety reasons. The skill and bravery of the drivers was quite impressive. Also, the engineering skills of some of the race fans was quite good—there were many elaborate camping setups, a beer pulley, and the below trash can converted into a grill/keg holder/stereo/prep table.

 

 

 

 

 

 

 

 

 

 

The week after I returned from Germany, sessions were announced for the PASS Summit. I was awarded two sessions, including a three hour talk on Hybrid Disaster Recovery. I’m looking forward to seeing folks in Seattle in November—my birthday is during summit week, so let’s have a beverage!

Right after that, I got news that I would be speaking at both SQL Server Days in Belgium and Live 360! in Orlando this fall. So it will be a busy quarter.

PASS Summit 2014—My Sessions and Free the Comments

This week, I had the honor of being selected to speak at the 2014 PASS Summit in Seattle, WA this coming November. As always, the program committee did an excellent job of combing through a ton of submissions. I was graced with two sessions this year—one is called “Building Your Second Datacenter Disaster Recovery in Microsoft Azure”, and the other is “Building Perfect SQL Servers, Every Time” (I’m noticing a trend here about building stuff)

For the Azure session, I started last year (at spoke at last year’s summit on the topic) of using a hybrid model for building an Always On Availability Group. At the time, the process was complex and fairly complicated. Since then, Microsoft has done a lot of work (as have I, I just finished writing a white paper with Stacia Misner (b|t) on implementing Power BI in Hybrid IT) to make the process easier in simpler. In this three hour session attendees will learn not just about availability groups, but other DR options like log shipping, mirroring and replication, and how to implement them in both cloud-only and hybrid models. It should be an interesting session, with lots of opportunities for my demos to fail.

My other session is about things that need to be done after installing SQL Server-it’s something I’m passionate about. As a consultant, I get to see a lot of SQL Servers, and they aren’t always pretty. SQL Server has a lot of pretty bad defaults in place (max memory, max degree of parallelism, data file autogrowth sizes) and these can lead to poor server performance if left in place. In addition, you will learn about how you can fully automate all of these best practices, so you don’t have to click next and watch the green bar go across the screen. I’ll also talk about the lessons we learned in building a private cloud environment at Comcast.

For my final comment, there has been a lot of controversy around session and precon selection for this summit. I had several friends on the program committee, who I know put a lot of work into comments on each abstract reviewed. As a speaker who gets rejected sometimes (and who doesn’t) being able to read those comments (even on selected sessions) is a great resource for feedback and understanding about what to change. For whatever reason, PASS has decided to not supply speakers with this comments, which I feel is a big mistake and an insult to the program committees and speakers who put in a lot of work to write abstracts and comments. #freethecomments

SQL Saturday #294—It’s a Wrap! #sqlsat294

I just finished organizing my third SQL Saturday event in Philadelphia, this one being our biggest ever. I’d like to thank all of my volunteers for making this event happen and be successful again. This year, we were on either side of TechEd, so I feel like that helped us with speakers. Also, for the first time ever we decided to have a precon day with myself and Stacia Misner (b|t) talking about Big Data and Power BI, and Allan Hirt (b|t) doing a really well regarded lab session on Always On Availability Groups. These were well received—I was surprised at how late most of our attendees registered. The last week saw a significant increase in registrations with a couple of attendees registering the night before. I did offer speakers a healthy discount on pre-cons—we had a few speakers sign up for some extra learning.

We are fortunate enough to have a really fantastic facility at Microsoft and a great representative there. The venue is central, close to my house, and the layout allows good foot traffic for all of our sponsors. One of the issues we’ve had in the past, was having attendees not print out their SpeedPASSes, this year we had the budget to offer a Xbox One as a prize, and it seemed like we had about 85% of our attendees show up with their SpeedPASSes printed.

In closing, I would like to thank all of my sponsors, my volunteers (you were awesome), and my speakers. Putting these events on isn’t easy, and it takes a great team to do it all.

T-SQL Tuesday #055: SQL Server 2014, The Good, The Bad and The Ugly

T-SQL Tuesday is here (sorry for delay, work is busy, and I’m running a SQL Saturday this week). I will be hosting this month’s edition. Since SQL Server 2014 has been out for a couple of months now, I wanted to solicit opinions. What features are you really happy about? What features should be tossed/fixed immediately/taken out back and shot? Did SQL 2014 break your application, and do you know why?

 

 

 

 

  1. The post must go live on your blog between 00:00 GMT Tuesday, June 2, 2014 and 00:00 GMT Wednesday, June 11, 2014

2) Include the T-SQL Tuesday logo at the top of your post, and link your post back to this post, and the link must be anchored from the logo (found above) which must also appear at the top of the post

3) Leave a comment here (below) or I won’t be able to find your post.            

Is My Static IP Address in Windows Azure Really Set?

I’ve been working with Windows Azure VMs since they became available last year, and I’ve built out some pretty complex scenarios with them (hybrid clusters using AlwaysOn Availability Groups, for one). One the early limitations was that all of the VMs were all dynamic IP (DHCP) addresses—there were some workarounds to this, but with database servers and domain controllers this wasn’t the best option. Starting early in 2014, a new PowerShell command called “Set-AzureStaticVNetIP” appeared on GitHub, and slowly made its way into the public domain. There’s a great article on how to configure this using Windows Azure PowerShell (which you will need to install after creating your VMs) at Windows IT Pro.

Note: This can only be done on Azure VMs in a virtual network

I’m in the middle of creating some VMs for some work that I am doing, and I went through the above process to assign a static IP to my domain controller in Azure. Pro tip—don’t set your IP address from within an RDP session to that machine. You will get kicked out (hangs head in shame). Also, make note that your machine may reboot—it’s not mentioned in the article, and I’m not 100% sure if it related to my being in the RDP session, but be forewarned.

As I was promoting it to a domain controller, I noticed that Windows still thought it had a dynamic IP address. Which I thought was odd.

Figure 1 Server Manager Showing Dynamic IP

From there I checked the IPv4 properties (note—this server has been promoted to a domain controller, why it is using localhost (127.0.0.1) and the other domain controller for DNS)

Figure 2 IPv4 Properties of AzureVM with Static IP

Of course, the proof was in the pudding—I had rebooted this VM several times and it was still keeping the same IP address (10.0.1.4). In the traditional dynamic IP address model, each DHCP call would increment by 1—so by my third reboot I would expect to see 10.0.1.7. So I went to Powershell to check using the command “Get-AzureStaticVNetIP”:

 

Figure 3 Static IP Address Assigned to VM.

So even though in most places it doesn’t look like your VM has a static IP address, it has been reserved on the Azure side. I think this is likely being done at the hypervisor level somehow, and hasn’t been exposed to Windows yet, but that’s just speculation on my part.

 

 

How Do I Show Memory Usage From A Columnstore Index in SQL Server?

Columnstore indexes in SQL Server are a great feature from an analytic and data warehouse perspective—you can get great compression ratios and batch mode processing allows for great performance. One of the ways these performance gains happen is by columnstore indexes bypassing the 8k pages in the SQL Server bufferpool (mostly—I’ll touch on that in a bit). Columnstore indexes get their own area of memory—which uses large pages (which are stored adjacently) to support the underlying lobs that the columnstore index is stored in. So I was curious to see how much memory my columnstore index was actually using—before I knew better, I looked in the buffer cache, which returned:

This shows my index has a 106 pages in the buffer cache—which is interesting because columnstore indexes aren’t supposed to use that right? So I take a look at sys.dm_os_buffer_descriptors:

select
*
from
sys.dm_os_buffer_descriptors
where database_id=
6 and allocation_unit_id=72057594085113856;

 


 

So there are some pages in the buffer pool from the columnstore—I suspect these are pages from the columnstore dictionary, but I can’t say for certain. So how do we find our actual columnstore memory usage? After chasing around DMVs a bit, I centered on sys.dm_os_memory_cache_entries:

select
*
from
sys.dm_os_memory_cache_entries
where type=CACHESTORE_COLUMNSTOREOBJECTPOOL

 

Unfortunately, none of the columns in that DMV tie back directly to a specific object or partition id. Except for entry_data—which presents data in this wonderful format (and not it’s not XML, that would have been easier):

<object_type=’1′ db_id=’12’ hobt_id =’72057594042646528′ column_id=’30’ object_id =’0′ object_size=’22760’/>

I knew if I could get that hobt_id, I could show the memory utilization of the columnstore. So with much thanks to at Jeremiah Peschka (b|t) who helped me work on some of the myriad of string functions and concatenation that I had to do to make this work, I present columnstorebuffers.sql. This procedure will return the name of the source object (the table here) and the amount of memory it’s using in the columnstore object pool:

Note—you’ll have to run this procedure on a database level, even though dm_os_memory_cache_entries is global, the other views that join to bring back the name of the object are not.

Follow

Get every new post delivered to your Inbox.

Join 1,816 other followers

%d bloggers like this: