SQL Server: Consolidation Tactics and Best Practices

This course will show how to effectively plan and implement a consolidation strategy, including methods of consolidating, and ways to migrate databases and instances. This course is applicable to all versions of SQL Server.

Course info

Rating

(20)

Level

Intermediate

Updated

Jul 20, 2016

Duration

2h 12m

Description

There are many reasons for consolidating SQL Server but license and hardware cost reduction have always been primary drivers for organizations to consolidate. In this course, SQL Server: Consolidation Tactics and Best Practices, you'll start with general considerations for consolidation that should be thought about and completed before any planning, as well as ways that to identify consolidation candidates' recommendations for which type of servers to consider consolidating first. You'll then learn about three consolidation methods: using a large single instance, using a multiple-instance server, and using virtualization. The benefits of each consolidation method are explained as well as the risks associated with them, plus some considerations and practical experiences. Finally, you'll cover just about every option for migrating databases and servers from one location to another. By the end of this course, you'll have the skills required to plan and execute a consolidation project or an upgrade of your SQL Server environment, and you'll be well-versed in all the tactics that help reduce your SQL Server licensing requirements.

About the author

Tim is a Principal Consultant with SQLskills. He has worked with SQL server for the past 15 years, in a variety of roles including DBA, Lead DBA, and multi-department manager, which have given him extensive experience planning and implementing large-scale environment changes and upgrades.

More from the author

Section Introduction Transcripts

IntroductionHi, this is Tim Radney from SQLskills. com, and I am recording this course for Pluralsight. This course is about SQL Server consolidation tactics and best practices, and this is the introduction module. In this module, I am going to explain what consolidation actually means for SQL Server. Although consolidation is a common industry buzzword, there is confusion about how this relates to SQL Server environments. I am going to talk about some of the benefits of consolidating SQL Server, such as reducing cost and simplifying management. I am going to discuss the types of consolidation that you can use, as well as go over some of the complications that can arise with consolidation if you do not plan accordingly. Lastly, this module will go over the entire course structure. What is SQL Server consolidation, and what does it actually mean for your environment? SQL Server consolidation can be as simple as combining multiple databases from multiple instances of SQL Server to fewer instances. This process is used in each consolidation method. Consolidating SQL Server could also be combining multiple instances of SQL Server to fewer physical servers. This is my least favorite method for a few reasons, which I will discuss in the multiple instances module. And the most common method of consolidating SQL Server is combining multiple physical servers to fewer physical servers through virtualization. You will learn why this is the most common and favorable consolidation method in the virtualization module.

General ConsiderationsHi, this is Tim Radney from SQLskills. com, and I am recording this course for Pluralsight. This course is about SQL Server consolidation tactics and best practices, and this is the General Considerations module. In this module, I am going to discuss several key things that you should take into account before starting any consolidation effort. Before starting any consolidation project, you must first have a goal and define a plan. These are the things you wish to accomplish with the project and how you're going to go about accomplishing those. You have to engage the right people in order for the project to be a success. There are few things worse than spending countless hours planning a project only for it to get squashed by some other department head. You have to know your environment in order to plan the proper consolidation project. If you don't know what all needs to be consolidated or what all would be impacted by moving a database, it makes it very difficult to have a successful project. As you start consolidating databases or instances of SQL Server, you need to understand the implications to security. You need to make sure that you are not creating new vulnerabilities when making these changes. When you start migrating SQL Server resources together, you have to take into consideration the type of hardware and the storage you plan to use, as well as the impact to maintenance processes. As you combine resources together, there is a risk that you can oversaturate some components and create resource bottlenecks. High availability might also be a consideration for you to meet certain SLAs as you consolidate higher priority databases. In some cases, the consolidation method that you choose can enable you to have an HA solution.

Large Single InstanceHi, this is Tim Radney from SQLskills. com, and I am recording this course for Pluralsight. This course is about SQL Server consolidation tactics and best practices, and this is the Large Single Instance module. In this module, I am going to discuss several key areas that you need to be aware of when considering consolidating to a large single instance of SQL Server. A large single instance can either be a big physical machine or a larger scale virtual machine. It is simply a multipurpose SQL Server that is hosting databases for multiple applications. I will first list out several benefits of using a large single instance for consolidation such as easier management, license reduction, server reduction, and performance. This consolidation method was a very common approach many years ago and is still widely used today. Next I will discuss many of the risks that are associated with using a large single instance such as shared resources, security, instance settings, updates, and many others. Some of these risks can be mitigated with other consolidation methods. Last, I will cover some general considerations and best practices to use or to think about if you choose to consolidate to a large single instance.

Multiple InstancesHi, this is Tim Radney from SQLskills. com, and I am recording this course for Pluralsight. This course is about SQL Server consolidation tactics and best practices, and this is the Multiple Instances module. In this module, I am going to discuss several key areas that you need to be aware of when considering consolidating to a multiple instance SQL Server. In most cases, a multiple instance SQL Server is either a big physical machine or a larger scale virtual machine. It is simply a multipurpose SQL Server that is hosting multiple instances of SQL Server for different applications. This is usually warranted either for instance isolation or due to applications supporting different versions of SQL Server. I will first list out several benefits of using a multiple instance SQL Server for consolidation, such as being able to run different versions of SQL Server, having different instance-level settings, application isolation, license reduction, being able to reduce OS and hardware cost, as well as having a tempdb per instance. Next I will discuss many of the risks that are associated with using a multiple instance SQL Server such as security, patching, updates, service level agreements, and others. Some of these risks can be mitigated with other consolidation methods. Last I cover some general considerations and best practices to use or to think about if you're planning to consolidate to a multiple instance server.

VirtualizationHi, this is Tim Radney from SQLskills. com, and I am recording this course for Pluralsight. This course is about SQL Server consolidation tactics and best practices, and this is the Virtualization module. In this module, I am going to discuss several key areas that you need to be aware of when considering consolidating using virtualization. Virtualization has become the go-to consolidation method. Companies all across the world have been rapidly adopting virtualization over the past decade. The top two hypervisors that I work with are VMware and Hyper-V. I have heard virtualization described as turning hardware into software. Through virtualization, we can take a big server and run a bunch of smaller servers on it by sharing or splitting out the resources of the main server. I will first list out several benefits of using virtualization for consolidating SQL Server, such as being able to run multiple operating systems on a physical host, having higher isolation levels, built-in high availability, centralized administration, being able to take snapshots, and having a higher utilization of physical resources. Next I will discuss some of the risks that are associated with using virtualization, things such as a host being oversubscribed, SQL Server sprawl, condensing your SQL Servers onto just a few hosts, as well as not having OS-level protection. Last, I cover some general considerations and best practices to use or to think about if you're planning to implement virtualization for your SQL Servers.

Migration StrategiesHi, this is Tim Radney from SQLskills. com, and I am recording this course for Pluralsight. This course is about SQL Server consolidation tactics and best practices, and this is the Migration Strategies module. When it comes to migrating data from one server to another, there are quite a few options. A very common option for moving a database from one database server to another is by detaching, copying, and attaching the database on the new server. This has some risk, which I will discuss later. In some situations, you can move an entire SAN LUN from one server to another. This is very common when migrating to a server with a newer OS for upgrades, as well as when replacing hardware. Database mirroring and log shipping are both excellent methods for staging databases on a different server. This is a common approach when you have proper time for planning and you have either a lot of databases or very large databases. Much like with database mirroring and log shipping, availability groups is another way to set up and establish a copy of your database on another server that can be kept in sync. When it comes to virtualizing environments, leveraging physical to virtual migrations, or P2V, can really help expedite the migration. There is also the ability to perform a virtual to virtual migration.