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 2: Keeping Your SQL Server Environment Healthy

In the first chapter, we saw that SQL Server is a complex software suite consisting of many services that work together. Based on the deployment scenario, we can have even more instances of SQL Server services running on the same host. SQL Server Database Engine is usually a key service in the Enterprise environment because many other applications and tools depend on SQL Server for their primary data storage. It's a crucial task to keep our SQL Server environment healthy, not only with proper maintenance and monitoring but also with proper post-installation configuration. Installation, as we have seen in the first chapter, is not just about configuring many of the settings and keeping the default values. A default configuration might not be ideal for your production environment, and it's important to understand the benefits of any modifications to the default values. The goal of this chapter is to provide the basic post...

Understanding SQL Server patching

Once you install SQL Server, you need to watch for future updates released by Microsoft. You can confirm which updates were installed to your server by checking the build number of the SQL Server deployment. You can find the build number in SQL Server Management Studio (SSMS), as you can see in the following screenshot, or via the SELECT @@VERSION command:

Fig. 2.1 – SQL Server Object Explorer (build version)

The build version of the currently installed SQL Server is 15.0.2070.41. We can parse this version into the following portions:

15–indicates we work with SQL Server 2019
2070.41—4517790 Servicing Update (GDR1) for SQL Server 2019 Release to Manufacturing (RTM)
Note
You can find a nice list of updates for all SQL Server versions at http://sqlserverbuilds.blogspot.com/ where you can identify the correct build of your SQL Server.

Based on the data...

Configuring SQL Server environment

Once you have installed your SQL Server and performed the patching to the current patch level required, you need to configure basic settings on the SQL Server and also on the Windows Server itself. There are several settings on the Windows Server that have an impact on your SQL Server's performance and security, and these need to be updated before you put the server into production. The following are the basic options that you need to configure on the operating system:

Configuring security rights for your SQL Server account
Configuring power settings
Configuring firewall rules

Configuring security rights for your SQL Server account

During the installation of the SQL Server, you're choosing an account that will be used to run all SQL Server services. There are quite a few considerations for a proper choice but, in this chapter, we'll focus more on the follow-up configuration. Such an account...

Creating a performance baseline

Baseline refers to the normal or typical state of your SQL Server and environment performance. This baseline is very important to you for numerous reasons, and these are as follows:

When you start troubleshooting the server, you need to know how your server will behave toward something odd.
You can proactively tune the system if you find a weak spot in the baseline.
When you plan to upgrade your server, you know how the load was increasing over time, so you can plan properly.

As a matter of fact, you won't have just one single baseline, but you can create multiple baselines depending on the variable workload. In such a case, you will have a baseline for the following:

Business hours
Peak usage
End of week/month/quarter due to closures; reporting
Weekend

Creating a performance baseline and capturing performance information for your server is, hence, a very crucial task and should be deployed to each...

Summary

In this chapter, we have seen how to build a healthy SQL Server environment and how to configure not only the SQL Server but also Windows Server for a stable and secure SQL Server workload. Keeping your server up to date, secure, and monitored is extremely crucial for the stability of applications and your ability to perform any troubleshooting at the SQL Server level.

In the next chapter, we will build on our healthy SQL Server and introduce backup and recovery procedures so that you can understand how to keep your server safe and how to recover from failures.

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