How To STIG SQL Server 2016

[UPDATE 4/16/18]: I have started a series of blog posts that will address, “How to STIG SQL Server 2016.” The first in this series can be found here.

I was recently asked about STIG’ing a database server running SQL Server 2016. I checked DISA’s website and, to my surprise, they have not yet released an official STIG checklist for SQL Server 2016. The latest edition they have a STIG for is SQL Server 2014.

In fact, if you go to their website’s “master list“, and scroll down to “Microsoft SQL Server 2016 FAQ“, the link will direct you to the following FAQ page (image below):

How To STIG SQL Server 2016

So there you have it. Until DISA releases their official SQL Server 2016 STIGs, you can use the current SQL Server 2014 STIGs to harden SQL Server 2016.

Fair enough.

[UPDATE 4/16/18]: I have started a series of blog posts that will address, “How to STIG SQL Server 2016.” The first in this series can be found here.

IEPTO2: Performance Tuning and Optimization – Part 2 (Competition)

Paul Randal, of SQLSkills, recently announced a chance to win a free seat in their 5-day IEPTO1 or IEPTO2 classes in October (2017). You can sign up for their SQL Insider’s newsletter here.

I was extremely fortunate to attend IEPTO1 back in Spring 2015 and blogged about it here.

This blog post is hopefully to win a free seat in IEPTO2.

Why I want to attend IEPTO2 taught by SQLSkills

After attending IEPTO1, I was confident enough to interview and get a job that treasured employees that seek knowledge in their field. At the recommendation of Tim Radney, of SQLSkills, I created this blog, purchased my very first heavy-duty laptop (Dell M6800, 32GB RAM, 1.5TB SSD, etc) to create a lab environment, and did all the IEPTO1 homework that Kimberly and Paul suggested we do.

I want to attend IEPTO2 because in my current job, I am the SQL Server Engineer solely in charge of deploying a high available solution, using Windows Server Failover Cluster and SQL Server, as part of the Joint Strike Fighter (F35) program. I want to dive deeper into SQL Server!

Why I’ll make the best use of the knowledge I’ll get from being in the class

Since everything is being built from scratch, the knowledge I would get from IEPTO2 would directly relate to my current job. Topics such as Module 2 (IO concepts), Module 3 (Storage) and Module 4 (SQLOS / CPU) would help me tremendously in the production build-out of the SQL Server environment. I could literally put to use what I learned as soon as I got back from attending IEPTO2. What perfect timing for a chance to win!

What my favorite performance tuning challenge is

My favorite performance tuning challenge is index tuning. When it comes to performance tuning indexes, I have to admit that I need more experience/knowledge. It’s still my favorite though because I know how HUGE of a difference having the right indexes make in performance! I remember a story Kimberly told (during IEPTO1) about a client that had the wrong indexes. and were having horrible performance. Once she tuned the indexes, the SQL Server was blazing fast! I love that type of stuff!

[UPDATE: 08/14/2017 – Congratulations to all the winners!]

Adding Scalability to MySQL for Benefits That Go Beyond Performance

(This is a guest blog post by Tony Branson (t | b). Tony is a Database Load Balancing Senior Analyst at ScaleArc.)

Are you struggling to keep your systems up and running as your online applications continue to grow popular? Is your service always available and equipped to meet the requirements of performance scalability? Does your platform ensure failure recovery without losing data? Whether you are a small startup or a globally renowned brand, customers expect that your systems remain available and accessible round the clock. When you store every single transaction for millions of users and manage more than a hundred thousand queries every second, your database should be designed with scalability in mind.

The widely adopted master/slave model does help enterprises to ensure availability and uninterrupted connectivity but when it comes to transactional support, this approach lacks performance scalability. There are several other reasons that make MySQL a preferred database management system like:

  • The flexibility of open source
  • Ease of Use
  • Solid data security layers
  • Cost-effectiveness
  • Compatibility with major operating systems

But when a MySQL master-slave cluster is used to achieve the goal of high-availability and scalability, the complex sharding strategy can prove to be a tricky task.

Dynamic load balancing technology lets you handle even high loads rapidly and reliably without needing any modifications to your database. It automatically manages your traffic while ensuring the highest performance for your applications. It ensures complete consistency between replicas, facilitates faster failover and permits the shutdown of nodes for maintenance without affecting the service. MySQL server load balancing lets you scale your application, supports heavy traffic and identifies unhealthy VM instances while adding healthy ones by routing the traffic to virtual machines that are in close proximity. You can use a load balancing solution for query routing and prevent service outages by directing report queries to their designated servers.

When Your MySQL Deployment Needs to Go Beyond A Single Instance

Database load balancing facilitates uninterrupted use of MySQL even as organizations continue to grow while simplifying the tasks of your IT support team. It eliminates the issues arising due to vendor lock-in and the hassles of having to switch between systems. Load balancing lets you capitalize on unlimited horizontal scalability especially if your business processes run on MySQL entirely. Database load balancing not only addresses the issues arising due to the paucity of technical resources but also facilitates scalable MySQL deployments both in cloud and on premise.

Saving Statistics Early On

Monitoring is essential but if your legacy monitoring system is sending false positives frequently, it can leave your system administrators numb. This makes it important to capture all the metrics to facilitate timely actions when problems crop up as workloads change.

Too Much Configuration Tweaking Can Degrade Performance

DBAs typically spend most of their time tweaking configurations but it rarely works to optimize server performance. Don’t rely on the defaults that are shipped with your MySQL as they are outdated and don’t fit your unique circumstances. Using the server tuning tools rarely makes sense as they often come coded with inaccurate advice that is seldom right.

A Quick Solution to Scaling MySQL

Modify your MySQL application to update the connections to one IP or port connections to another using a code update and then divert the “update” connections to the master database and the “read” connections to a virtual server. Use a load balancing solution that uses least connections across the slave servers so that all the connections are routed to a single IP. Sharding is another well-established and reliable approach to scaling a MySQL database as it is easy to manage and completely transparent.

While MySQL efficiently performs the replication work it fails to balance load and distribute the queries among multiple servers. It may or may not be able to offer a low-latency environment. A load balancing software integrates replication aware routing so you can specify a delayed threshold without making any changes to your application. This approach is sure to optimize database performance and increase availability.