Learn how to avoid configuration issues from the hardware to the database that can limit the scalability of a SQL Server 2012/2014 workload, along with features and design methodologies for scaling up and scaling out.
When considering how to improve the scalability of a SQL Server workload, many people jump to the conclusion that scaling up or scaling out are the only answers. This course, SQL Server: Scaling SQL Server 2012 and 2014: Part 2, builds on the earlier "Scaling SQL Server 2012 - Part 1" course and will show you a huge number of things you can do to improve performance and scalability. First, you'll learn about configuration issues in hardware, storage, Windows, and SQL Server, and you'll also see demonstrations of some of the issues and tools to help you find the issues in the first place. Finally, you'll get an explanation of some of the techniques and features you can use to scale up or scale out, once you've configured the system correctly. By the end of this course, you'll be able to improve the performance and scalability of a SQL Server workload.
Glenn works as a Principal Consultant at SQLskills.com. He has been a SQL Server MVP since 2007, and he is also an Adjunct Faculty member at University College - University of Denver. He is the author of the book SQL Server Hardware (Redgate 2011), and he wrote chapters for both SQL Server MVP Deep Dives books.
Introduction This is Glenn Berry with SQLskills. com. I'm recording this course for Pluralsight, and this is SQL Server: Scaling SQL Server 2012 and 2014: Part 2, and this is the introductory module. Well this course as a whole is instance-level scalability with SQL Server, and there are several components to this scalability that we need to think about as we go through the course. First, you really want to know your workload that you're using with SQL Server. Is it more of an OLTP workload, or more of a reporting workload, or some sort of a mix workload. You also need to think about and understand, are you using any kind of DR or HA technologies that can affect your workload. The next part is knowing your infrastructure, knowing what kind of hardware you have, what kind of storage subsystem you have, are you using virtualization or not, and understanding how that affects SQL Server and how it's going to scale at the instance level. Next, you need to understand how SQL Server uses your infrastructure. So you're going to have your regular workload, for example, that, again, could be an OLTP workload or a reporting workload, plus you might have HA/DR workload on top of that, plus you're going to have things like maintenance workload, so running DBCC CHECKDB, doing index maintenance, things of that nature are also going to have an effect on your infrastructure, on your VMs, for example, on your physical hardware, and on your storage subsystem.
Database Configuration Settings Database Configuration Settings. This module is going to cover key database property settings that are really important for performance and scalability. We're also going to talk about things you need to do with database filegroup considerations, as far as having additional filegroups and additional data files. We're also going to talk about where to layout your files and how to think about that from a performance and scalability point of view. Then we'll talk about database file property settings and how they relate to performance and scalability.
Instance Configuration Settings This is Glenn Berry with SQL. skills. com. I'm recording this course for Pluralsight, and this is module 3, Instance Configuration Settings. What are we going to cover in this module? We'll be talking about operating system issues that you might run into with the SQL Server instance. We'll talk about operating system configuration settings that are related to SQL Server that you need to take care of, usually before you install SQL Server, but you can do it afterwards if you miss it. We're also going to talk about some instance-level configuration settings that are really important for performance and scalability. And then we'll talk about some best practices for performance and scalability that are related to the operating system and the instance-level configuration settings that you need to worry about.
Storage Subsystem Issues Storage Subsystem Issues. In this module we're going to talk about common storage subsystem issues that you typically run into with SQL Server. We're also going to talk about how you go about designing a storage subsystem for different kinds of SQL Server workloads. We'll discuss how to configure your storage for scalability. And then finally, I'll show you a couple of free disk benchmark tools you can use to actually test your storage subsystem.
Hardware Issues Hardware Issues. This module is going to talk about common hardware issues that you often encounter with SQL Server. We'll talk about how to properly configure hardware for the best performance and scalability, we'll discuss the effect of power management on your performance and scalability, and then we'll talk a lot about processor selection for SQL Server because it's really important both for licensing purposes and for your overall performance and scalability that you're going to get out of a particular machine.
Scaling up SQL Server Scaling Up SQL Server. What are we going to cover in this module? Well first I'm going to talk about the capacity and license limits for SQL Server and for Windows Server, and how they're different with different versions of SQL Server and Windows Server. Next we'll talk about planning and implementing a hardware migration, and there are several different ways of doing it, and I want to show you the best way to do it to avoid having downtime. And then finally, I'm going to talk about migrating to new hardware with minimal downtime, and some of the different ways that you can do that using the technologies that are built into SQL Server.
Scaling Out SQL Server Scaling Out SQL Server. So what are we going to cover in this module? Well, we'll talk about some of the things that are built into SQL Server, some of the features that help you scale out SQL Server using the features that are available right in the product itself. The first one is AlwaysOn availability group replicas. Then we'll talk about transactional replication and we'll talk about peer-to-peer replication. Next we'll talk about distributed partition views, and then we'll talk about a concept that's not actually a product feature called service-oriented database architecture, or SODA. And then finally we'll talk about data-dependent routing, or DDR. And these are all different ways that you can scale out SQL Server.
New Scalability Features in SQL Server 2014 New Scalability Features in SQL Server 2014. In this module we're going to talk about some of the new features that were added to SQL Server 2014 that can help performance and scalability. The first one is buffer pool extensions. Then we'll talk about resource governor enhancements and we'll talk about columnstore clustered indexes. Next we'll discuss delayed durability, and then finally we'll talk about the new cardinality estimator.