The Shortcut Guide to SQL Server Infrastructure Optimization

by Don Jones


Are you ready to create an optimized infrastructure for SQL Server? One that’s agile, ready to respond to changing business needs at a moment’s notice—without ridiculously high administrative overhead? The time has come. The tools, technologies, and techniques are all in place to create a highly-optimized, business-agile infrastructure that can power SQL Server for years to come. In The Shortcut Guide to SQL Server Infrastructure Optimization, you will learn about the pitfalls of an un-optimized SQL Server infrastructure, and learn about the ways in which your infrastructure can be upgraded and enhanced to be more nimble, more reliable, and even less expensive.


Chapter 1: Challenges of Today's Typical Infrastructure Model for SQL Server

SQL Server can be an organization’s greatest asset and—from a corporate management perspective—a complex challenge. The reason is that SQL Server (or any enterprise-class database system, for that matter) places a heavy demand on infrastructure resources such as servers, the network, and storage. Many organizations continually struggle to scale their SQL Server installations to meet business demand—including requirements for availability in the face of hardware failure—while at the same time bemoaning the “data center bloat” that seems to be the unavoidable companion of large SQL Server installations. A lot of that is simply the cost of SQL Server’s success in the enterprise: As companies find more uses for SQL Server, there are inevitably more SQL Server installations to deal with. Some of the challenge comes from what I’ll call traditional SQL Server architecture, which too often consists primarily of double-clicking Setup, or architecting SQL Server for performance without taking manageability into account.

In fact, SQL Server and "data center bloat" don’t necessarily go together any more than SQL Server and “difficult to manage” do. With the right tools and techniques, you can have a top-performing SQL Server infrastructure without having to cram your data centers with so much hardware that they’re all but overflowing. Some of these tools and techniques may not seem very obvious, which is perhaps why many SQL Server architects don’t discover them right away. They are, however, extremely effective.

It's all about infrastructure optimization, or letting your application get the very most from the infrastructure that it’s running on. Before I can begin to share some of these new techniques, however, I need to back up a bit and explain exactly why so many organizations aren’t really optimizing their SQL Server platform.

Chapter 2: Optimizing Your SQL Server infrastructure: Good Ideas, Bad Ideas

In the previous chapter, I discussed the impact of sprawl in a SQL Server infrastructure. Basically, my conclusion was that traditional SQL Server architectural techniques, along with a number of business factors (such as political factors) seem to make SQL Server sprawl inevitable. The result is a highly unoptimized infrastructure, where copious amounts of unused resources exist but which cannot be easily deployed to help fill any need. That is, if you take a look at your infrastructure as a whole, you probably have lots of unused storage, processor capacity, and so forth, but you’re not necessarily free to "move" unused resources from one area to cover a shortage in another. Your resources are inflexible, tied to individual servers, which are often handling a very specific workload.

This chapter looks a bit harder at why things turn out this way, from a SQL Server technical viewpoint, and explores some of the ways the industry has tried to solve the problem. Remember that the ultimate goal is to create an infrastructure that’s dynamic according to the Microsoft Infrastructure Optimization Model (IOM) - capable of changing smoothly and quickly to meet business needs, and in fact designed to accommodate rapid changes.

Chapter 3: The New Cluster: Technologies for SQL Server Infrastructure Optimization

The average mid-size company of 250 employees typically serves the same number of workstations with about 25 servers. Those nodes on the network are interconnected by about nine network devices, through firewalls, switches, and routers. For a network of that size, the average network device configuration contains about 300 lines per device. Multiplying those two numbers, you get the potential for more than 2700 individual configurations, just to connect a relatively small number of devices.

The big question is this: In a critical situation, could you rebuild those 2700 lines purely from memory? It’s the implementation of configuration management into your network environment that helps you answer that question in the affirmative.

Chapter 2 discussed performance management in relation to the FCAPS model of network management. The chapter discussed how managing performance in a network can be virtually impossible without a baseline to measure it by, and talked about how to use your NMS to measure the changes in performance from your baseline and how those changes in performance can trace back to configuration inconsistencies or other underlying problems. The chapter also brought forward some good technical and business metrics that illustrate network performance and validate it to your business leaders.

This chapter will move away from the P in FCAPS and focus on the C and the S—configuration management and security. This chapter will discuss how you can use a good NMS to consistently manage, store, and audit the configuration of devices on your network. We’ll explore the four steps in establishing and maintaining an environment configuration and relate those to the underlying financial reasons why configuration management has business relevance. The chapter will go over a set of features that an effective NMS should incorporate to assist with this task, and will finish up with a short discussion about how good device configuration dovetails into good device security.