You're reading from SQL Server 2019 Administrator's Guide - Second Edition

Product typeBook

Published inSep 2020

Reading LevelBeginner

PublisherPackt

ISBN-139781789954326

Edition2nd Edition

Languages

SQL

Concepts

Database Administration

Authors (2):

Marek Chmel

Vladimír Mužný

View More author details

Chapter 7: Planning Migration and Upgrade

Although this book is about SQL Server 2019, you will not always be working with the latest versions and editions of SQL Server. You may be surprised how many diverse versions of SQL Server are still in production. Quite frequently, you'll face a task of upgrading and migrating the server configuration and content to the new server running the latest version of SQL Server. Each new version of SQL Server brings out many new features that are not available in older versions, and those features may be very useful for your environment to bring better performance, stability, and many other factors to your application. In this chapter, we'll explore the upgrade options for SQL Server and what you have to actually think about during the planning phase of the upgrade. This information will help you in upgrading your SQL Server systems to the latest version in a real environment.

Note

Migration of SQL Server is also usually bound...

The importance of keeping up with latest version

An important reason to upgrade is to continue to obtain the latest support for SQL Server. Once the mainstream support for SQL Server ends, there will be no more service packs or cumulative updates bringing fixes and updates to your current version of SQL Server.

As you can see from the following table, just three SQL Server versions are supported as of now (summer 2020) and those are SQL Server 2016, SQL Server 2017, and the current SQL Server 2019:

Fig. 7.1 – SQL version support end dates

Although many of the older versions are not supported anymore, this does not mean they are not used in production environments. The preceding table does not list all the SQL Server versions. There are even older systems that can be used today, but those fell out of support a long time ago. Also, note that SQL Server 2008 and SQL Server 2008 R2 share an end date for mainstream support, although...

Planning the upgrade

Upgrading a complex infrastructure is not an easy task and should not be executed without any preparation. Careful planning of the required steps will help you eliminate possible issues to the minimum, and the upgrade will run smoothly. Throughout the versions of SQL Server there have been many changes, so it's worth exploring your options when planning the new installation, also from the perspective of the available edition.

While upgrading SQL Server from older versions, you also have to understand changes in the licensing for SQL Server, whereby SQL Server versions 2008 R2 and older used the per-processor licensing model or used a client access license (CAL) licensing model. Starting with SQL Server 2012, Microsoft has moved to core-based licensing, where you need to have a license for each central processing unit (CPU) core used on your operating system.

Note

SQL Server licensing is a very complex topic that goes beyond the scope of...

Performing the upgrade

Once you have finished the planning phase and all the preparations are done, it's time to perform the upgrade/migration of your SQL Server environment. For the scenario used in this chapter, we'll upgrade an existing SQL Server 2016 version to the latest version. One thing you also need to carefully consider is the edition upgrade path that you need to follow.

Note

We will be upgrading to the Enterprise Edition, and the edition will stay the same after the upgrade. You can, however, upgrade between editions. You can find the whole edition and version upgrade matrix on Microsoft's website at https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades where you can see which versions and editions can be upgraded.

In the next steps, we will consider the in-place upgrade of the SQL Server environment first. Once we have started the SQL Server setup, the setup program...

Migrating from other platforms

SQL Server migration projects don't necessarily only include SQL Server as a primary data source, but there are many other platforms where you may choose to upgrade to SQL Server. You can use SQL Server Migration Assistant (SSMA), which is available for several Database Management System (DBMS) systems, as follows:

SSMA for Access
SSMA for DB2
SSMA for Oracle
SSMA for SAP Adaptive Server Enterprise (SAP ASE)
SSMA for MySQL

SSMA is able to create a project for a number of target versions of SQL Server, where SQL Server 2017 is included with both platforms—Linux and Windows operating systems. The other targets available are listed as follows:

SQL Server 2008/2008 R2
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017 Windows/Linux
Azure SQL Database
Azure SQL Data Warehouse

If you would like to use the migration tool, you need...

Summary

Upgrade and migration are important phases in the SQL Server operation life cycle. Each path has its own advantages and disadvantages. As we went through the chapter, you saw that careful planning of such a task is crucial, since there are numerous considerations for the new platform.

In-place upgrades offer you the option to utilize the current hardware of the virtual environment, allowing you to quickly upgrade your SQL Server to new versions. However, this option has more complex rollback if your upgrade fails. With side-by-side migration, you can really use the benefits of the new hardware platform, modern CPUs, and the latest operating system, which will allow you to build the SQL Server again, and then you just need to migrate the data between the old and new SQL Server. There are again numerous options for you, the most common being backup and attach/detach methods. There are many tools that can be used to plan the upgrade—especially SQL Server Data Migration...

The rest of the chapter is locked

You have been reading a chapter from

SQL Server 2019 Administrator's Guide - Second Edition

Published in: Sep 2020Publisher: PacktISBN-13: 9781789954326

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

undefined

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime

Authors (2)

Marek Chmel

Marek Chmel is a senior CSA at Microsoft, specializing in data and AI. He is a speaker and trainer with more than 15 years' experience. He has been a Data Platform MVP since 2012. He has earned numerous certifications, including Azure Architect, Data Engineer and Scientist Associate, Certified Ethical Hacker, and several eLearnSecurity certifications. Marek earned his master's degree in business and informatics from Nottingham Trent University. He started his career as a trainer for Microsoft Server courses and later worked as SharePoint team lead and principal database administrator. He has authored two books, Hands-On Data Science with SQL Server 2017 and SQL Server 2017 Administrator's Guide.
Read more about Marek Chmel

Vladimír Mužný

Vladimír Mužný has been a freelance developer and consultant since 1997. He has been a Data Platform MVP since 2017, and he has earned certifications such as MCSE: Data Management and Analytics and MCT. His first steps with SQL Server were done on version 6.5, and from that time on, he has worked with all following versions of SQL Server. Now Vladimir teaches Microsoft database courses, participates in SQL Server adoption at various companies, and collaborates on projects for production tracking and migrations.
Read more about Vladimír Mužný

Other recommended products

Related to this chapter

Professional SQL Server High Availability and Disaster Recovery

The Professional SQL Server High Availability and Disaster Recovery book explains the high availability and the disaster recovery technologies, their technical implementation, and different topologies that you can use when creating a highly available infrastructure with hybrid topologies.

BookJan 2019564 pages

SQL Server on Azure Virtual Machines

This book will teach you how to harness the capabilities of SQL Server and the cloud database by taking you through the steps to deploy the server in Azure virtual machine effectively.

BookJun 2020200 pages

SQL Server on Linux

Microsoft's launch of SQL Server on Linux has made SQL Server a truly versatile platform across different operating systems and data-types, both on-premise and on-cloud. You will start by understanding how SQL Server can be installed on supported and unsupported Linux distributions. With the help of this book you will be able to setup SQL Server on Linux and understand how SQL Server can be installed and implemented on this open source platform.

BookAug 2017222 pages

Learn T-SQL Querying

T-SQL is an extension of the SQL language which allows you to tackle advanced querying and query-tuning challenges in SQL Server and Azure SQL Database. This book will be a perfect reference for you to write more efficient T-SQL code to perform simple-to-advanced tasks for data management and data analysis.

BookMay 2019484 pages

Introducing Microsoft SQL Server 2019

Introducing Microsoft SQL Server 2019 takes you through what’s new in SQL Server 2019 and why it matters. After reading this book, you’ll be well placed to explore exactly how you can make MIcrosoft SQL Server 2019 work best for you.

BookApr 2020488 pages

Hands-On Data Science with SQL Server 2017

Learn how to utilize Microsoft SQL Server with NoSQL concepts for data science challenges. This book will help enhance your knowledge beyond data querying & processing tasks by implementing a data science pipeline. We will implement data science tasks and show how to use them on a day-to-day basis for efficient smart predictive models.

BookNov 2018506 pages

SQL Server 2017 Developer's Guide

This book is your guide to exploring the various developer capabilities offered by SQL Server 2017. Model your data and the complex relationships within it, and integrate SQL Server with R and Python for efficient analytics. The book also covers the performance and troubleshooting aspects to help you develop efficient database applications.

BookMar 2018816 pages

SQL Server 2017 Machine Learning Services with R

With integrated R Services within SQL Server 2017, developers and data scientists can now benefit from the integrated, effective, efficient and more streamlined analytics environment. In this book, you will understand how to leverage the capabilities of R Services in SQL Server 2017. This short yet effective guide will help you get familiar with SQL Server 2017 R Services, and will show how to implement efficient data science models using it.

BookFeb 2018338 pages

Professional Azure SQL Managed Database Administration

Whether it is learning different techniques to monitor and tune an Azure SQL database or improving performance using in-memory technology, this book will enable you to make the most out of Azure SQL database features and functionality for data management solutions.

BookMar 2021724 pages

SQL Server 2016 Developer's Guide

This book is designed to get you up to speed with SQL Server 2016, covering the essential concepts and techniques. By the end of this book, you’ll be able to design efficient, high-performance database applications confidently.

BookMar 2017616 pages

SQL Server 2017 Integration Services Cookbook

SQL Server Integration Services is a tool that facilitates data extraction, consolidation, and loading options (ETL), SQL Server coding enhancements, data warehousing, and customizations. With the help of this book, you’ll gain complete hands-on experience of SSIS 2017’s new features, and design and development improvements including SCD, Profiling, Tuning, and Customizations.

BookJun 2017558 pages

ETL with Azure Cookbook

This book will take you through hand-on recipes for extracting, transforming, and loading data using big data tools and Azure services such as Data Factory and Azure Databricks. You will learn how to interact effectively with Azure services, along with covering automation with BIML and data profiling in Azure.

BookSep 2020446 pages

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

BookAug 2023230 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages4

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages1

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

BookAug 2023684 pages

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages5

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages2

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

BookOct 2023636 pages5

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

BookAug 2023420 pages5

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

BookDec 2023240 pages

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

BookNov 2023664 pages