What’s new in SQL Server 2017

SQL Server 2017 is considered a major release in the history of the SQL Server life cycle for various reasons. From my personal point of view, SQL Server 2017 is indeed an interesting release. After writing a lot about it and testing various features of SQL Server 2017, I’d like to walk you through some of its interesting features.

SQL Server 2017 …

  1. is now on the platform of our choice; SQL Server 2017 on several Linux distros, that can run on Docker containers, and SQL VM in Azure, along with the good ol’ SQL Server on Windows.
  2. expands its reach to support Graph database technology
  3. includes automatic database tuning
  4. supports Machine Learning by adopting Python
  5. contains new dynamic management views and functions
  6. has new string functions
  7. And more…

Continue reading ..SQL Server 2017 Top Features

 

Advertisements
Posted in SQL Server 2017 | Tagged , , | Leave a comment

SQL Server – Stairway to Database Backup-and -Restore, (and Recovery)- Complete Guide

It’s a time to turn the corner from “DBA” to “DBA specialist”. This in-depth guide explores the importance of database backup-and-restore features and skills you’ll need to build good backup-and-restore strategies using the available tools and techniques. Along the way, you’ll pick up some interesting insights and most of the concepts of database backup and restore procedures.

As you move through the list of 20+ articles, you’ll see most of the features that include various database Backup-and-Restore concepts, T-SQL programming techniques, PowerShell Scripting, implementation of backup solutions to Docker containers, data management using SQL Ops Studio, handling bacpac and dacpac files, ingress and egress data from Cloud and more. The articles are organized to make specific topics easy to find so that you can jump start depending on your skill level.

  1. Article 1: An overview of the process of SQL Server backup-and-restore
  2. Article 2: Understanding the SQL Server Data Management Life Cycle
  3. Article 3: Understanding SQL Server database recovery models
  4. Article 4: Understanding SQL Server Backup Types
  5. Article 5: Backup and Restore (or Recovery) strategies for SQL Server database
  6. Article 6: Discussing Backup and Restore Automation using SQLCMD and SQL Server agent
  7. Article 7: Understanding Database snapshots vs Database backups in SQL Server
  8. Article 8: SqlPackage.exe – Automate SQL Server Database Restoration using bacpac with PowerShell or Batch techniques
  9. Article 9: Smart database backups in SQL Server 2017
  10. Article 10: How to perform a page level restore in SQL Server
  11. Article 11: Backup Linux SQL Server databases using PowerShell and Windows task scheduler
  12. Article 12: SQL Server Database backup and restore operations using the Cloud
  13. Article 13: Tail-Log Backup and Restore in SQL Server
  14. Article 14: SQL Server Database Backup and Restore reports
  15. Article 15: Database Filegroup(s) and Piecemeal restores in SQL Server
  16. Article 16: Backup and Restore of a SQL Server database with Memory-Optimized objects
  17. Article 17: Backup and Restore using SQL Server Docker Containers
  18. Article 18: Backup and Restore operations with SQL Server Docker containers using SQL Ops Studio
  19. Article 19: Interview questions on SQL Server database backups, restores and recovery – Part I
  20. Article 20: Interview questions on SQL Server database backups, restores and recovery – Part II
  21. Article 21: Interview questions on SQL Server database backups, restores and recovery – Part III
  22. Article 22: Planning a SQL Server Backup and Restore strategy in a multi-server environment using PowerShell and T-SQL
  23. Article 23: Understanding Database Backup Encryption in SQL Server

Continue reading….

https://www.sqlshack.com/database-backup-and-restore-process-in-sql-server-series-intro/

 

Posted in Backup and Restore | Tagged , | Leave a comment

Know more about SQL Server MSDB log store for database backups, restores, and recovery: Q& A – Part 3

In this article, we’ll see the how the backup-and-restore meta-data tables store the information in the MSDB database.

  1. How do you delete six months old data to reduce the size of the backup and restore history tables?
  2. How do you get the Backup History for a specific database including the size, location, and LSN?
  3. How do you create and restore a marked transaction?
  4. How do you find the RESTORE HISTORY of the database?
  5. How do you list the last 30 days restore history at the instance level?
  6. How do you measure the database backup or database restore operation progress?
  7. How do you measure the database growth using backup size?
  8. How do you define or estimate the storage required for database backup?
  9. How do you get most recent database backup time for each database?
  10. How do you get recent database backup time for each database using PowerShell?
  11. How do you get recent database backup time for each database across multiple servers using PowerShell?
  12. How do you find the backup history with duration and compressed backup size columns?

Continue reading…

https://www.sqlshack.com/interview-questions-on-sql-server-database-backups-restores-and-recovery-part-iii/

 

Posted in Backup and Restore | Tagged , | Leave a comment

Know more about SQL Server database backups, restores, and recovery: Q& A – Part 2

In this article, you’ll see the Q&A about the database restore and recovery internals. To learn a lot about SQL Server database backup-and-restore (recovery) internals, you can refer to the following full list of topics.

  1. Define database restore
  2. Define the database recovery process
  3. Check the state of the backup file
  4. Check the number of file(s) in a backup file
  5. Identify the database version from a backup file
  6. Check the backup software tools used for backup
  7. Perform simple and multiple ways to restore a database
  8. Explain WITH OVERWRITE option
  9. Explain WITH NORECOVERY option
  10. Restore differential or t-log backup file
  11. Understand Other restore types STANDBY/READONLY
  12. Explain WITH REPLACE option
  13. Explain WITH MOVE option
  14. Restore the database using a Split files
  15. Detail Piecemeal restore process
  16. Explain Point-in-time recovery
  17. Describe the Page-Level-Restore process
  18. Explain Recovery-Only database restore
  19. Explain WITH STOPAT option
  20. Generate restore data commands using dynamic T-SQL

Continue reading…

https://www.sqlshack.com/interview-questions-on-sql-server-database-backups-restores-and-recovery-part-ii/

Posted in Backup and Restore | Tagged , | Leave a comment

Know more about SQL Server database backups, restores, and recovery: Q& A – Part 1

Let’s deep dive and review each of the database backup command topics to get a better understanding of what it is all about. In this article, we will find an answer for FAQs about the SQL Server database backup. We will learn more about database backup.

In this article, we’ll discuss following Q&A:

  1. What are database backups?
  2. What are the factors to consider while planning for backup, restore, and recovery strategy?
  3. What are the different stages of data life-cycle management?
  4. How to do plan or setup SQL Server Backup and Restore strategy in a multi-server environment using native tools?
  5. How does the database recovery model impact database backups?
  6. Explain the different types of database backup in SQL Server?/a>
  7. How can I verify that backups are occurring on a daily basis?
  8. How can I verify that backups are occurring on a daily basis?
  9. What are the system tables that store backup and restore related information?
  10. What are the new enhancements added for Backup and Restore SQL Server 2017?
  11. What is a smart T-Log backup in SQL Server 2017?
  12. Assume that the database recovery model is full. The full database backup runs every week at 9 PM. Sunday, differential backup runs daily at 9 PM. Monday to Saturday, and hourly transaction log backups. Now, database crashed on Friday 5:10 PM. How to do a point-in-time recovery of the database?
  13. How to do database refresh automation?
  14. Explain the process of database backup and restore operations using the Cloud?
  15. In a given situation, assume that the system has 3 drives with 30 GB of free space on each drive. Now, how can you perform a database backup for 80 GB database?. Is it possible?
  16. Explain piecemeal restore operation?
  17. What are database recovery phases and how it is different for in-memory optimized objects?
  18. How to perform database backup and restore operation on SQL Server Docker containers?
  19. What is the native toolset that is available to perform database backup and restore operation?
  20. What are the top 10 trace flags can be used with database backup?

Continue reading…

https://www.sqlshack.com/interview-questions-on-sql-server-database-backups-restores-and-recovery-part-i/

 

 

Posted in Backup and Restore | Tagged , | Leave a comment

Backup and Restore operations with SQL Server 2017 Docker containers using SQL Operations Studio

In this 18th article of the series, we’ll discuss the concepts of database backup-and-restore operations with SQL Server 2017 Docker containers using SQL Ops Studio (SOS). Before proceeding, you need to have Docker engine installed and SQL Ops studio configured on your host machine.

This article covers the following topics:

  1. Overview of SQL Operations Studio (SOS)
  2. How to use SQL Ops Studio integrated terminal
  3. Definition of Docker containers
  4. Step by step instructions to initiate backup-and-restore of SQL Server 2017 Docker containers using the SQL Ops Studio interface
  5. And more…

Continue reading…

Backup-and-Restore SQL Server 2017 Docker containers using SQL Operations Studio (SOS)

Posted in Backup and Restore, docker, SQL 2017 | Tagged , , , | Leave a comment

Understanding Data Sharing in SQL Server Docker Containers

This article talks about shared volumes and the preferred mechanism for data persistence using the Docker containers.

  1. This article covers the following topics:
  2. Introduction of the Docker volumes.
  3. Managing volume using Docker CLI commands or Docker API.
  4. Sharing data between containers and between containers and hosts
  5. Sharing database files across containers
  6. And more…

Introduction

Containers are independent of the storage engine and volumes. Let’s discuss briefly on how Docker accomplishes the idea of images and containers. How does the layer organized in storage and keeping them isolated, letting them be stacked on top of each other as layers?

We are in the era of rapid development and faster deployment; we also know how to do a release faster. Now, we will see how to build the application and port it as a micro-services using containers with data persistence.

Before jumping to the demo, let’s understand the available Docker solutions. Let’s talk about sharing data between containers and between containers and hosts.

Docker offers the solution of data externalization and this feature is called volumes. The volumes are like a shared folder or the directory structures of the file system. They’re virtual discs that you can store data in and share them between the containers, and between containers and the host or both. So they have two main varieties of volumes available within Docker.

We’ve got the persistent ones, where we can place the data there, and it will be available to the host. And when the container goes away, the data will be still available. And, another one is ephemeral volumes. They exist as long as the container is using them. But when no container is using them, they evaporate. So they’re ephemeral. They’ll stick around as long as they’re being used but they’re not permanent. These are not part of images. No part of volumes will be included when you download an image and no part of volumes is going to be involved if you upload an image. They’re for your local data, local to this host. So first let’s talk about sharing data between the host and a container.

The underlying file systems manage and determine which bits on the drive are part of which file and it’s up to the kernel with the file-systems to keep track of every file. Now on top of all that, you can take programs, and programs can pretend to be file systems. We call this FUSE file system. For a detailed example, you can refer here.

Continue reading

data sharing in SQL Server docker containers

 

Posted in docker | Leave a comment

SQL Server : Understanding Backup and Restore operations using Docker Containers

In this article, we will discuss the concepts of database backup-and-restore operations on SQL Server Docker containers. This is certainly the era of containers and it made the buzz in the business, so let us understand the importance of backup-and-restore the databases on the Docker containers.

Docker containers are systems that are small, light-weight, isolated, and segmented subsystems built on the Linux or Windows host system. It’s just running an application on the host machine.

This article discusses the following topics:

  • Pre-requisites
  • Initiating a database backup and copy the backup file across the containers
  • Restoring a database
  • Pulling the latest SQL Server 2017 container images from the docker registry
  • Running the downloaded Docker container image using docker run command
  • Detailing the steps to create the database in the container
  • Explaining the data persistence concepts
  • And more…

Continue reading…

Docker containers

 

 

 

Posted in docker, SQL Server 2017 | Tagged , | Leave a comment

SQL Server In-Memory Optimized database backup and restore in action

A SQL Server backup and restore strategy is an essential process to safeguard and protect critical data. The vital role of any DBA is to minimize the risk of data loss and preserve data modifications at regular intervals of time. A well-planned and well-tested backup-and-restore strategy always help to protect the organization with no data loss, or at least a minimum, from the many varieties of system and database failure. As such, it is recommended to understand the importance and implication of the backup-and-restore strategy.

In this article the following topics are covered:

  1. High-level components and concepts of Memory-optimized objects
  2. Introduction to Backup and Restore Strategies
  3. Background of In-Memory OLTP engine
  4. Explain database recovery phases
  5. Transaction logging
  6. Backup and restore of the memory-optimized database
  7. Piecemeal restore of the memory-optimized database
  8. And more…

Introduction

One should understand the basic business requirements, of an organization, and system internals so that the good backup-and-restore strategy can be defined. OLTP workloads define the state of the data. Data-manipulation and data-transformation operations define the memory and disk requirements for the operation to complete. A good knowledge about the systems and local applications is always a key factor for successful backups. Improper management of backups may lead to a greater risk to database availability.

In-Memory OLTP engine and storage engines both use the same transaction log and Write-Ahead Logging (WAL) logging mechanism; however, the log record format and algorithms used for In-Memory OLTP logging mechanism is entirely different. It generates log records only at the time of a commit transaction. There is no concept of tracking uncommitted changes and there’s no need to write any undo records to the transaction log. In-Memory OLTP generates logs based on the transaction write-set. All the modifications are combined to form one or very few log records.

The SQL Server engine handles the backup-and-recovery of durable memory-optimized databases in a different way than the traditional database backups. As we know, any data held in RAM is volatile and it’s lost when the server reboots or server crashes. However, the In-Memory architecture is engineered to create a backup of the data on the localized disk. As it’s a memory-centric, this adds a new step in the database recovery phase.  Memory management is a very important step. If it’s not managed, we’ll end up with out-of-memory issues.

Database Recovery Phases

When SQL Server instances restart, each database goes through different recovery stages.

The different phases are:

  1. Analysis
  2. Redo
  3. Undo

Analysis: In this phase, the transaction log is analyzed to track the information about the last checkpoint and create the Dirty Page Table (DPT); this captures all the dirty-page details. In In-Memory OLTP engine, the analysis phase identifies the checkpoint inventory and prepares the system table with all the log entries and also its processes the associated file allocation log records

Redo: This is the roll-forward phase. When this phase completes, the database comes online.

Here are the points to ponder:

  1. For disk-based tables, the database is moved to the current point-in-time and acquires locks taken by uncommitted transactions.
  2. For memory-optimized tables, data from the data and delta file pairs are loaded into the memory and then update the data with the active transaction-log based on the last durable checkpoint. During this phase, disk and memory-optimized based object recovery run concurrently.
  3. In SQL Server 2017, the redo phase of the memory-optimized tables (the associated data and delta files) is done in parallel. This results in faster times for the database recovery process.
  4. When the above operations are completed for both disk-based and memory-optimized tables, the database becomes online and available for access.

Undo: This is the rollback phase. It holds the list of the active transaction from the analysis phase basically undoing the transactions. This phase is not needed for memory-optimized tables since In-Memory OLTP doesn’t record any uncommitted transactions for memory-optimized tables.

Checkpoint

A checkpoint is a background process continuously scans the transaction log records and then writes the data and delta files on disk. Writing to the data and delta files is done in an append-only manner by appending newly created rows to the end of the current data file and appending the deleted rows to the corresponding delta file.

Data and Delta Files

For persistence, the data for memory-optimized tables are stored in one or more checkpoint file pairs (CFP). The data file(s) store inserted rows and delta file(s) stores the references of deleted rows. The update operation is an insert followed by delete rows. Over time, based on the merge policy, the background checkpoint process merges the CFP’s into a new CFP and older CFP’s go through the removal process with a process of garbage collection.

 

Continue reading…

In-Memory Optimized database backup and restore in action

 

Posted in Backup and Restore | Tagged , , , | Leave a comment

Introduction to Docker and Docker Toolbox

The article gives some knowledge about getting started and utilizing Docker containers. The focus is all about understanding  Docker and its basics. Once you start grasping the basics and learn to use them, you will easily understand how to use Docker with other products.

In this article, we are going to review the following points:

  1. Introduction to Docker
  2. Pre-requisites to be done for the installation
  3. Installation of Docker toolbox on Windows
  4. Basics on getting started with Docker
  5. The main docker commands
  6. Methods to build Ubuntu images from the docker hub repository
  7. And more…

Introduction

Docker is a portable, open platform, lightweight and simple to configure, build and split an operating system into small containers that can be used to deploy applications in isolated and secure containers.

These containers are designed to be portable so they can be shipped from one place to anotherю Thus, Docker is a tool that passes these containers to and from your systems. A container is a self-contained sealed unit of the required software. It has the combination of everything needed to run that code, as well as contains all the dependencies that your system needs bundled up in that container. Moreover, it even includes an operating system to run your code. So, it takes all the services that make up an operating system such as network configuration, storage, libraries, code, and IPC (Inter Process Communication) protocols.

Docker is built into a client-server application architecture. The Docker client initiates a request to a Docker daemon (Server). Both Docker client and Docker server can be run on the same or remote system.

Docker has a program, which builds containers from a code. It takes your code along with its dependencies, bundles it up, and seals into a container.

The tool is a good choice for DevOps pipeline to speed up the consistent delivery of applications. It offers a platform to streamline the development lifecycle by providing only required applications and services. Containers are great to be used for continuous integration and continuous delivery (CI/CD) workflows.

Install Docker Toolbox on Windows

For Windows 7 (or higher) operating systems, Docker provides Docker Toolbox, an installer with a platform to configure and launch a Docker environment. Additionally, it can set up and start a Docker environment on older Mac and Windows systems. Docker Toolbox installs a program known as Docker that helps manage Docker Virtual Machines and others components, including the VirtualBox, Docker Machine programs etc. Docker Toolbox also installs Docker Machine, which has several useful commands for managing the VMs (Virtual Machines).

The components of Docker Toolbox are as follows:

  1. Docker Machine
  2. Docker Engine
  3. Docker Compose
  4. Kinematic
  5. Docker Quickstart Terminal App
  6. Oracle VirtualBox

 

Further reading….

Docker

 

Posted in docker | Tagged , , | Leave a comment