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

 

Advertisements
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

Overview of File-group(s) and Piecemeal database restore operations in SQL Server

So far, we discussed many de-facto details about SQL Server database backup and restore. In this 15th article of the series, we are going to discuss, the file-group and piecemeal database backup and restore process.

Database “Backup and Restore” strategies are vital to every organization for smoother functioning of the business. Database design concepts are also important in defining the backup and restore strategy. A good database design structure and proper planning would give us an ample time to speed up the recovery process.

In this article, we will discuss the following topics:

  1. Introduction
  2. Explain file-group(s) level database backup and restore operations
  3. Discuss piecemeal database restore process
  4. Demo
  5. And more…

In some cases, taking full database backup is not a big deal, whereas, for VLDB databases or large OLTP databases, it may not be a feasible solution to initiate frequent full database backups in-and-out. In such scenarios, the file(s) and filegroup(s) backup and restore options play a vital role.

If you are operating VLDB database, in some cases, it becomes a daunting task to perform full database backup and restore as it may take several hours to complete the backup and restore operation.

Piecemeal restore helps with databases that contain multiple filegroups to be restored and recovered at multiple stages. This would give an option to customize the backup and restore (or recovery) solution.

Based on recommended practices and database design principles; if the database is designed to leverage data and segments to different file groups and store them on a different drive this provides a great advantage when doing backups of the database, and restoring the database in case of any database corruption or failure. Let’s say that one of the non-primary data files may become corrupt or otherwise it can go offline due to some hardware failure then there is no need to perform the full database restores, instead, only restore the filegroup that is needed. This operation will suffice or speed-up the entire restoration process.

Getting started

Let us jump into the demo to see how to perform the backup and restore operation.

In most of the cases, a single data file and log file works best for the database design requirement. If you’re planning to leverage data across multiple data files, create secondary file groups for the data and indexes, and make the secondary filegroup a default one for the storage. In this way, the primary-file will contain only the system objects. Then it’s possible that a single file group’s data file may become corrupted or otherwise go offline due to hardware failure or I/O subsystem failure. When this happens, there’s no need to perform a full database restore. After all, the rest of the file groups are all still safe and sound. By only restoring the file groups that need it, this way you can speed up the entire restoration process.

 

Further reading…

Filegroup and Piecemeal restore

 

 

Posted in SQL Server Backup | Tagged , , | Leave a comment

Overview of SQL Server 2017 Docker containers on Linux machine

SQL Server 2017 is the biggest release and most important releases in the Microsoft product history simply because of the freedom that it gives to install the product anywhere. The advent of the Docker containers and growing emphasis on open source solutions opened the door to greater technological innovation and advancement.

This article discusses the following topics:

  1. Introduction to Docker container
  2. Pre-requisite to build Docker container
  3. A walkthrough of the setup process to build a SQL Server instance running on the Linux CentOS and in a Docker container
  4. How to connect to the database engine
  5. How to connect to the database engine externally
  6. And more…

So let’s get started exploring the new-found Docker container.

Introduction

Docker provides an elegant platform for API packaging for container management. It eases out the complex design process of application packaging by incorporating the dependent components into the container image.

The support for Linux and Docker containers came with the release of SQL Server 2017, which opens up the various options for installing and working with SQL Server 2017 on those platforms. SQL Server 2017 is no longer a platform dependent of the database offering. Its footprint is widespread across other flavors of the operating system as well. This new capability allows developers and open source users to test the full-fledged SQL Server instance at a lower cost.

Prerequisites

  • Docker Engine 1.8+ on any supported Linux distros or have an up-to-date version of Docker for Mac or Windows
  • Min of 2 GB of dedicated disk space
  • Min of 2 GB of RAM dedicated to Docker
  • System requirements for SQL Server on Linux

In this quickstart, we will learn how to install the Docker on Linux distros and how to incorporate the SQL Server 2017 container image. In this case, Docker provides a platform to bundle only the required resources for SQL Server 2017 into a fully self-contained unit. In the case, the container will include only the pieces of the operating system that it required, including any drivers, system libraries, or other resources needed to make SQL Server instance fully functioning. This option keeps the size down since only the bare minimum components are included in the container.

This capability makes containers highly portable since they don’t have any external dependencies. It is possible to create a Docker image on one machine, then move or copy it to another and ensure that it’ll still work in the same way.

Microsoft offers a number of different container images that we can pull. They will be identified by tags during the installation process. In most cases, we’d specify a 2017-latest image to get the most current version of SQL Server 2017, but there is an option to pull an image with an earlier cumulative update as well. On this site, we can see the different tags that are available for the Docker images.

For the entire demo, I will be using the Docker Centos CE(Community Edition) container. For the production use, the documentation at this URL will help us to obtain the required licenses and images, if this applies to your situation.

further reading…

docker and sql server

 

Posted in docker | Tagged , , , , , | 2 Comments

SQL Server -Database Backup Report using PowerShell and T-SQL

In the previous articles, we discussed several ways of taking backup and testing the restore process to ensure the integrity of the backup file.

In this article, we’re going to discuss the importance of the database backup reporting. This kind of reporting will be designed to eliminate any unnecessary risk that may arise with the safety and security of information. This is the reason, the high-level report; Daily Health Check report will be generated and sent to the SME or to the DBA responsible for IT Infra management.

Database backups are a vital component for most of the database administrators regardless what backup tool is in place and how the data backup process is all about backing up the data to the disk, to the tape or to the cloud.

In general, administrators are very much concerned with getting the report daily and also any alerts as per the defined SLAs. Administrators rely on the backup report to understand and how the backups are doing and always wanted to safeguard the data

The other area, we will cover, is the online backup; it is increasingly becoming the default choice for many small businesses databases. A database backup report is an important document which reveals the specific piece of information about the status of the data. In general, the generation reports have become a standard practice for any business who values their data.

A backup report should be produced after each backup job has run. The report provides detailed information including what was backed up, what can be restored and information about the backup media. Some of the information provided is specific to each backup type.

The following are the three important parameters that need to be reviewed at regular intervals of time are:

  1. Backup failure jobs

    This is the most important metric to be measured and in most cases, it requires immediate attention and action. For test or dev environments, this could wait for being taking an action based on the SLA

  2. Capacity planning and forecasting

    The other report generally circulated every day is the disk space utilization report. Proactive monitoring of storage would prevent most of the backup failures and it helps to forecast the data growth. In turn, this may reduce many unforeseen disk space-related issues.

  3. Performance

    Backup performance is the important metric to measure the overall health of the system. It gives a heads-up for many hidden issues such as hardware resource problem, device driver issues, network throughput problem, software problem etc:-

 

Further reading…

Backup report

 

 

Posted in Uncategorized | Leave a comment

Getting Started with Mssql-cli tool

A recent announcement on the release of several SQL Server tools has raised expectations across various groups. Product requirements and business are almost always a trade-off, and striking the right balance in a product in terms of the toolset is a sign of a successful product. After testing the SQL Operations Studio, I feel that it’s a promising tool for many developers, administrators, and DevOps specialists. In my opinion, the mssql-cli tool adds another feature to SQL Server in order to make it a leading database product.

Microsoft announced mssql-cli, a SQL Server user-friendly, command line interactive tool hosted by the dbcli-org community on GitHub. It’s an interactive, cross-platform command line query tool. The public preview release of mssql-cli is available for testing. mssql-cli is based on Python and the command-line interface projects such as pgcli and mycli. Microsoft released this tool under the OSF (Open Source Foundation) BSD 3 license. We can find its source code on GitHub. Now, the tool and the code are available for public preview. The tool is officially supported on Windows, Linux, and MacOS, and is compatible with Python versions 2.7, 3.4, and higher.

Mssql-cli improves the interactive CLI experience for T-SQL and includes support for SQL Server, MySQL, and PostgreSQL. The SQL Tools Service is the micro-service that forms the backend and based on .NET SDK Framework.

Mssql-cli is a little ahead of sqlcmd in terms of functionality and enhancements. Some of these features are introduced below:

Features

  1. T-SQL IntelliSense
    • This provides an array of keyword references that can be easily accessible during the development process.
    • The parameter or metadata population is contextual.
    • It also provides a quick reference.
  2. Syntax highlighting
    • This feature helps in identifying the troubleshooting of typo or syntax errors.
    • It helps to complete keywords and snippets automatically, thus, improving efficiency.
    • Autosuggestion and auto-completion of tasks where needed are available.
  3. Query history
    • It provides a way to store the history.
    • This tool is no different than many other editors—use the up/down arrow keys to select the desired SQL statement.
    • The advantage of this tool is to automatically suggest commands from the history.
  1. Configuration
    • A configuration file is used to configure the initial settings and parameters.
    • By default, the configuration file is stored to the following path:
        1. Important file location paths on Linux
          1. Configuration file – ~/.config/mssqlcli/config
          2. Log file ~/.config/mssqlcli/config/mssqlcli.log
          3. History file – ~/.config/mssqlcli/config/history
        2. Important file location paths on Windows
          1. Configuration file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\
          2. Log file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\mssqlcli.log
          3. History file – %USERPROFILE%\AppData\Local\dbcli\mssqlcli\history\
  2. Multi-line queries

Linebreaking within a query is very similar to what we have in SQL. To provide another example, in many languages, even shell languages, we can break up a single long command or a chain of commands into multiple lines. In PowerShell, we break the line with a backtick (`). In BAT/CMD, we use ^ to indicate that we have not yet done to issue commands to the shell.

  1. Mssql-cli modes

mssql-cli works in two very popular modes right out of the box: VI and EMACS. Those who are familiar with UNIX would instantly recognize these tools—text editors, to be precise. Most VI (and VIM—or VI Improved) users talk about “thinking in VI(M)”, which essentially means that the editor is so friendly and minimal that typing on it is almost the second nature to most VI users. If you’re one of them, you should feel right at home using this tool in the VI mode.

If you’re one of the EMACS users, you can enable or even customize this mode on mssql-cli. EMACS has a library of the LISP code that enhances EMACS and extends its capabilities.

Further reading mssql-cli

 

Posted in SQL tools | Tagged | Leave a comment

SQL Database Corruption: Causes, Prevention, Fixing Techniques

“I had a Database File and I want to attach this MDF file to SQL Server 2014. However, whenever I try to attach the file, I am receiving following error message :
The header for file ….\MSSQL\DATA\xxxx_data.mdf’ is not a valid database file header. The FILESIZE property is incorrect. (SQL Server Error 5172)”.

After surfing a lot, I found that MDF file has corruption in its header. I tried expert solution given in various forum websites but failed to fix header corruption, I am still getting the same error message. I have no idea how to deal with SQL database corruption. Can anybody suggest me any solution to fix this error? Thank you in advance!”

SQL Server stores its physical database in MDF file and the first page of this file, comprises the information of MDF file header. The header page keeps the information about the entire database like file signature, size etc. While attaching the MDF file in SQL Server, sometimes users encounter 5172 error code. This error generally occurs when the MDF file becomes corrupted or damaged. This error mismatches the information of the header and takes database into the inaccessible state. Thus, in this problem tackling blog, we are going to describe some easy and cost-efficient ways of dealing with SQL database corruption.

Before moving on to the Solution to fix database corruption, let us discuss what steps should not be taken if you are facing SQL database corruption.

What not to do?

Do not Reboot Server: Rebooting the server only can help to fix the minor issues from the OS end. If the issues are from server’s end then, reboot is not an appropriate solution. Rebooting system will put the database in offline mode and will detect it as a SUSPECT. This will make the situation worse.

Do not shutdown SQL Server: After identifying the master database corruption in SQL Server, every user tries to shut down the SQL Server. Yet, this is not a proper solution as the database become inaccessible via this.

Do not Detach/Re-attach Database: If corruption is present in the database, then detaching and re-attaching the database will make the recovery process much harder.

Do not Upgrade SQL Server: Upgrading SQL Server to fix the SQL database corruption is not an accurate method. Because upgrading the SQL Server version can create a new and major hurdle.

Do not Run Repair Command Instantly: The DBCC CHECKDB command only should be run when all other methods get failed. In many cases, this command takes users to permanent data loss condition. Thus, before executing this command, make sure you have correct knowledge of its syntax.

Solution to Fix SQL Database Corruption

Keeping regular backups and utilizing them later to recover the database in case of corruption or other disasters is the best option to deal with SQL database corruption. Nevertheless, it is not always a possible solution to restore all the data as there is always a missing portion of information between the last backup and time of disaster.

With SQL database repair program, it is possible to recover the entire data to the time of disaster or failure. The software is capable to repair corrupt MDF and NDF Server database. It has the capability to recover deleted SQL Server database table’s data.

preview sql data

The Final Note

There could be various factors that are responsible for the corrupt database like sudden shutdown, hardware failure, virus attack etc. In this write-up, we have covered various causes responsible for the corruption, What steps should not be taken when your database is in SUSPECT mode. The blog covers a quick solution to repair corrupt SQL database file.

Posted in SQL | Leave a comment

Backup and Restore Automation using SQLCMD and SQL Server agent

Database administrators are often requested to refresh a database, mostly to create a live copy of the production database to the test or development environment. This is done so that the development or the test environment closely resembles the production environment; this way, we prevent many undesirable issues when running the application in the production environment.

Many developers, at times, are required to write and debug code on the production copy of the database, so it makes more sense to refresh the database on regular basis.

Let’s build the process to automate the database refresh activity using a simple backup-and-restore method and scheduling it using SQL Server Agent.

  • Restore the database to the same server with a different name

 

  • Restore the database to a different server using sqlcmd and Robocopy utility

  • Schedule the job

 

Let’s take a closer look at the process to see how this works.

Continue reading Backup and Restore Automation using SQLCMD and SQL Server agent

 

Posted in SQL Server Backup | Tagged , , | Leave a comment