SQL Server

OK, so if you haven’t heard of the dbatools.io project run by Chrissy LeMaire and company…you’ve likely been living under a rock. I strongly encourage you to check it out ASAP. What they’re doing will make your life as a DBA easier…immediately. Here’s an example…

One of the things I like to do as a DBA is backup my databases, restore them to another server and run CHECKDB on them. There are some cmdlets in the dbatools project, in particular the Snowball release, that really make this easy. In this post I’m going to outline a quick solution I had to throw together this week to help me achieve this goal. We’ve all likely written code to do this using any number of technologies and techniques…wait until you see how easy it is using the dbatools project.

Speaking at SQLSaturday Chicago!

  I’m proud to announce that I will be speaking at <a href="http://www.sqlsaturday.com/600/EventHome.aspx">SQL Saturday Chicago</a> on March 11th 2017! And wow, 600 SQLSaturdays! This one won’t let you down. <a href="http://www.sqlsaturday.com/600/Sessions/Schedule.aspx">Check out the amazing schedule</a>!

  If you don’t know what SQLSaturday is, it’s a whole day of free SQL Server training available to you at no cost!

  If you haven’t been to a SQLSaturday, what are you waiting for! <a href="https://www.sqlsaturday.com/600/registernow.aspx">Sign up now</a>!

  My presentation is **“<a href="http://www.sqlsaturday.com/600/Sessions/Details.aspx?sid=56730">Networking Internals for the SQL Server Professional</a>” **

  <a href="http://www.sqlsaturday.com/600/EventHome.aspx"><img loading="lazy" style="display: block; margin-left: auto; margin-right: auto;" title="NewImage.png" src="/images/2017/02/NewImage.png" alt="SQLSaturday Chicago #600 logo" width="116" height="57" border="0" /></a>

  Here’s the abstract for the talk

<blockquote>

    Once data leaves your SQL Server do you know what happens or is the world of networking a black box to you? Would you like to know how data is packaged up and transmitted to other systems and what to do when things go wrong?  Are  you tired of being frustrated with the network team? In this session we introduce how data moves between systems on networks and TCP/IP internals. We’ll discuss real world scenarios showing you how your network’s performance impacts the performance of your SQL Server and even your recovery objectives.

</blockquote>

I’m excited to announce that I have been named a Friend of Redgate for 2017. The program targets influential people in their respective technical communities such as SQL,.NET and ALM and enables us to participate in the conversation around product and community development.

As a multi-year awardee in the program I get to see first hand the continuing dedication Redgate has to the SQL community and to making great software. I met a ton of really cool, very dedicated people along the way. Thanks for the recognition and I look forward to another great year!

Proactive Reporting for SQL Server

If you’re a return reader of this blog you know I write often about monitoring and performance of Availability Groups. I’m a very big proponent of using monitoring techniques to ensure you’re meeting your service level agreements in terms of recovery time objective and recovery point objective. In my in person training sessions on “Performance Monitoring AlwaysOn Availability Groups”, I emphasize the need for knowing what your system’s baseline for healthy replication and knowing when your system deviates from that baseline. From a monitoring perspective, there are really two key concepts here I want to dig into…reactive monitoring and proactive monitoring.

When designing Availability Group systems one of the first pieces of information I ask clients for is how much transaction log their databases generate. *Roughly*, this is going to account for how much data needs to move between their Availability Group Replicas. With that number we can start working towards the infrastructure requirements for their Availability Group system. I do this because I want to ensure the network has a sufficient amount of bandwidth to move the transaction log generated between all the replicas. Basically are the pipes big enough to handle the generated workload. But bandwidth is only part of the story, we also need to ensure latency is low. Why, well we’re going to explore that together in this post!

Today, I’m proud to announce that I have been named a Microsoft MVP – Data Platform.  This is an exceptional honor and I’m humbled to be included in this group of exceptional data professionals. I really look forward to working with everyone in the MVP community and continuing to contribute to our unmatched SQL Community!

What is an MVP?

Speaking at SQLSaturday Nashville!

  I’m proud to announce that I will be speaking at <a href="http://www.sqlsaturday.com/581/EventHome.aspx">SQL Saturday Nashville</a> on January 14th 2017! This will be my first speaking event this year and I look forward to seeing you there!

  If you don’t know what SQLSaturday is, it’s a whole day of free SQL Server training available to you at no cost!

  If you haven’t been to a SQLSaturday, what are you waiting for! <a href="https://www.sqlsaturday.com/581/RegisterNow.aspx">Sign up now</a>!

  My presentation is **“<a href="http://www.sqlsaturday.com/581/Sessions/Details.aspx?sid=56663">Performance Monitoring AlwaysOn Availability Groups</a>”** (which is one of my favorite sessions)

  **This is an updated session including new Availability Group Monitoring Extended Events and SQL 2016!**

  <a href="http://www.sqlsaturday.com/581/EventHome.aspx"><img loading="lazy" style="display: block; margin-left: auto; margin-right: auto;" title="NewImage.png" src="/images/2016/12/NewImage.png" alt="SQLSaturday Nashville #581 logo" width="187" height="86" border="0" /></a>

  Here’s the abstract for the talk

<blockquote>

    Have you deployed Availability Groups in your data center? Are you monitoring your Availability Groups to ensure you can meet your recovery objectives? If you haven’t this is the session for you. We will discuss the importance of monitoring and trending Availability Group Replication, how AGs move data between replicas and the impact replication latency can have on the availability of your systems. We’ll also give you the tools and techniques to go back to the office and get started monitoring and trending right away!

</blockquote>

OK, so everyone wants to know how Microsoft did it…how they got SQL Server running on Linux. In this article, I’m going to try to figure out how.

Update: Since the publication of this post, Microsoft has published a blog post detailing the implementation here.

There’s a couple of approaches they could take…a direct port or some abstraction layer…A direct port would have been hard, basically any OS interaction would have had to been looked at and that would have been time consuming and risk prone. Who comes along to save the day? Abstraction. The word you hear about a million times when you take Operating Systems classes in undergrad and grad computer science courses.:)

The SQLMonitor team at Redgate has been releasing updates at a much more rapid rate…what’s this mean to you? More fixes and more features. In this latest release, they certainly added something special…Graphical Query Plans! Yes, right inside of SQLMonitor’s user interface. Why is this important?  Well for me, when I’m troubleshooting a performance issue…I usually start with identifying what system resource is being taxed and try to zoom in from there on the root cause. Now lets say the root cause is a poorly performing query, SQLMonitor let’s you find that query pretty easily, but stopped short when it came to diagnosing that actual performance issue in the query.

SQL 2014 Service Pack 2 was recently released by Microsoft and there is a ton of great new features and enhancements in this release.This isn’t just a collection of bug fixes…there’s some serious value in this Service Pack. Check out the full list here. One of the key things added in this Service Pack is an enhancement of the Extended Events for AlwaysOn Availability Group replication.

Why are the new Availability Group Extended Event interesting?

If you’ve used Availability Groups in production systems with high transaction volumes you know that replication latency can impact your availability. If you want to brush up on that check out our blog posts on AG Replication Latency, Monitoring for replication latency, and issues with the DMVs when monitoring. These new extended events add insight at nearly every point inside your Availability Group’s replication. More importantly they also include duration. So using these Extended Events we can pinpoint latency inside our Availability Group replication.