In this article, we'll share how SQL Server can use filegroups to implement the cold and hot data isolation backup solution.
In our example scenario, we'll assume that a company has a critical large database (over 10 TB) and faces the following scenarios:
As a Database Administrator, you need to optimize the design of the database and Backup Recovery System to make backup and restore more efficient. But the question is, how can you do this on a SQL Server?
This article will not focus on the details of filegroups. However, as it is the core technology involved, it is necessary to briefly introduce filegroups in SQL Server in terms of their advantages, creation, and usage.
SQL Server supports the storage of table and index data in Non-Primary filegroups, which provides the following benefits when a database has multiple filegroups:
We can create a filegroup directly when we create a database. The code is as follows:
USE master
GO
EXEC sys.xp_create_subdir 'C:\SQLServer\Data\'
EXEC sys.xp_create_subdir 'C:\SQLServer\Logs\'
CREATE DATABASE [TestFG]
ON PRIMARY
( NAME = N'TestFG', FILENAME = N'C:\SQLServer\Data\TestFG.mdf' , SIZE = 5MB ,FILEGROWTH = 50MB ),
FILEGROUP [FG2010]
( NAME = N'FG2010', FILENAME = N'C:\SQLServer\Data\FG2010.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ),
FILEGROUP [FG2011]
( NAME = N'FG2011', FILENAME = N'C:\SQLServer\Data\FG2011.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB ),
FILEGROUP [FG2012]
( NAME = N'FG2012', FILENAME = N'C:\SQLServer\Data\FG2012.ndf' , SIZE = 5MB ,FILEGROWTH = 50MB )
LOG ON
( NAME = N'TestFG_log', FILENAME = N'C:\SQLServer\Logs\TestFG_log.ldf' , SIZE = 5MB , FILEGROWTH = 50MB)
GOHow can we restore an accidentally deleted database in GitLab? How can we implement database backup, recovery, disaster tolerance, and high availability? If you are in the database industry, you might be more concerned about these issues recently. Late last year, incidents including Hearthstone data loss and MongoDB hacker extortion highlighted the news. Recently, another incident stole the limelight: data files in GitLab databases were accidentally deleted by the rm -rf command.
Database is a vital for any enterprise. Web SQL injections and accidental deletion of data not only impair businesses but may also lead to user information leaks. Many companies realize the importance of database security and have specialized database administrators (DBA) for data maintenance. The DBA family is like the border warriors in the IT world, and guarding data is one of the most important responsibilities of DBAs. However, despite the many effective measures taken by DBAs, accidents do happen. Sometimes too many operation interfaces and too frequent back-and-forth switches between services may lead to an increased rate of human error.
How can we ensure the safety of data files even after an accidental deletion?
In many traditional enterprises, the common means of implementing HA is to mount shared storage among multiple hosts. In fact, only one copy of data is in place, because the availability of storage is much higher than the server. The advantage of this HA approach lies in the simplicity of its structure and thereby easier implementation of data consistency.
The weakness of this HA approach is obvious: there is a single point of failure. Of course, you can use multiple storage devices or physical images to solve single point of failure issues. On the other hand, because there is only one copy of storage, or multiple copies through the image, data deletion is infectious. That is, you can delete either none or all data, which makes it hard to protect data in the case of an rm -rf operation in GitLab.
Beginning with v9.1, PostgreSQL supports the dual-replica synchronous stream replication mode and the multi-replica asynchronous stream replication mode. In v9.6, PostgreSQL adds the new multi-replica synchronization mode.
With this approach, even if the data files in the master database are deleted by an rm -rf operation, because the synchronization is based on REDO, the operating system command will not be infectious, and rm -rf misoperation damages can be kept under control.
In the DT era, enterprises are becoming increasingly dependent on data. This article summarizes the Best Practices for Enterprise Database Session at The Computing Conference 2018.
With the arrival of the Data Technology (DT) era, enterprises have become increasingly dependent on data. Data protection has become essential for enterprises, and only those who take preventive measures with sufficient preparations can survive in disasters. In the Best Practices for Enterprise Database Session at The Computing Conference 2018, topics related to disaster recovery attracted much attention. This article introduces the best practices of using Alibaba Cloud database cloud product portfolios to tailor the disaster recovery solutions conforming to the development status of enterprises.
Data is important resources for production of an enterprise. Once data is lost, the enterprise's customer information, technical documents, and financial accounts may get lost, which may hold back customer relation, transaction, and production. In general, data loss is classified into three levels:
To cope with economical loss caused by data loss, enterprises must take disaster recovery measures to protect data. The higher the enterprises' degree of informatization, the more important the disaster recovery measures are.
Disaster recovery involves two elements: disaster tolerance and backup.
MySQL is the one of the most widely used Open-source database management systems. It is secure, flexible and easy to implement on websites and software applications.
MySQL comprehensive set of features makes it a good choice for storing data even for big companies like Youtube, Google and Facebook. Alibaba Cloud has continuously contributed and supported the development of MySQL and they won the 2018 MySQL Community Corporate Contributor Awards.
There are two ways to provision a MySQL database on Alibaba Cloud. The first method involves creating an ApsaraDB instance. This is an on-demand hosted database services.
The other option requires setting up an ECS (Elastic Compute Service) and installing MySQL server on top of an operating system like Linux.
In this guide we will compare Alibaba Cloud ApsaraDB for RDS to a MySQL database server hosted on Alibaba Cloud Elastic Compute Service (ECS). This will help you evaluate the best and suitable MySQL service for deploying your application on the cloud.
ApsaraDB for RDS is a fully-managed database service that requires a few mouse clicks to provision. This service frees you from administrative tasks for setting up, patching, recovering, detecting and repairing MySQL databases.
ApsaraDB for MySQL is a ready-to-use service that can be setup in less than 5 minutes. After setup, you can connect to the database and start using it as a backend for your application or website. Normally, you will just require the internet address, port, username and password created on the service console to use the database.
A MySQL database deployed on an ECS instance requires knowledge in both system and database administration. Installing and implementing MySQL database in this way takes a steep learning curve. To use the database, you must provision an operating system like Ubuntu 16.04 on Alibaba Cloud and install the software using package management tools like APT.
If a problem occurs on the MySQL database hosted on the ECS instance, you will have to personally troubleshoot the error to restore the database service. Remember the error can range from operating system failures, hardware problems, and memory issues. So unless you have in-depth knowledge in both system and database administration, ApsaraDB is a safer choice.
A database holds valuable information that must be safeguarded at all times. ApsaraDB for RDS makes your life easier by integrating security in the database solution. One of the worst enemies of MySQL database is SQL injection – a technique used by hackers to steal information from your database through malicious codes.
Alibaba Cloud ApsaraDB can intercept such attacks. On top of this, the service is built to withstand brute-force attacks – a method that tries to gain access to a database system by guessing login credentials.
When it comes to backup ApsaraDB has advanced disaster capabilities that live-copies data using Object Storage Service (OSS) for easy recovery process.
On the other hand, if you provision a MySQL server on Alibaba Cloud ECS, you must install and maintain the appropriate backup tools and applications. There are different approaches that you can take to create backups. For instance, you can use automatic backup scripts or copy data on different servers and this can increase the overall cost of running the service.
To safeguard the data on the ECS instances, you must ensure that both the operating system and the database server are protected. This involves setting up firewalls, anti-brute force tools like Fail2Ban.
If you don't have knowledge in such technologies, ensuring the security of your MySQL server can be overwhelming. ApsaraDB takes the pain away and incorporates security features by default. Hence, you can concentrate on your core business functions while Alibaba Cloud safeguards the database for you.
After learning this MySQL series courses, the trainees will master the MySQL relevant basic concept, deployment procedure, HA solution, commonly used backup and recovery method and daily inspection skills, thus having a comprehensive understanding of MySQL database.
Database Backup (DBS) is a secure and cost-efficient solution for continuous data protection. The service offers strong protection for data stored in multiple environments, such as enterprise data centers, hybrid clouds, public clouds, and third-party cloud vendors. Through this course, you will not only learn about Alibaba Cloud DBS technical essentials and applicable scenarios, but also by watching product console demos, you will be familiar with DBS major functions and operation details.
The security of the cloud databases are critical, they are directly affecting the security and stable operation of the cloud based applications. Only by understanding the principles, methods, and operation methods of backup and recovery of commonly used cloud databases, the cloud database administrators can better protect them. Through this course, you can not only understand the backup and recovery principles, types and methods of databases on the cloud, but also understand the backup and recovery methods related to Alibaba Cloud RDS.
After learning this MySQL series courses, the trainees will master the MySQL relevant basic concept, deployment procedure, HA solution, commonly used backup and recovery method and daily inspection skills, thus having a comprehensive understanding of MySQL database.
Learn about cloud database security concepts, MySQL and SQL Server backup and recovery concepts, types, and methods.
DBS offers features such as full backup, incremental backup, and data recovery. You can manage and configure backup plans at any time.
Log on to the DBS console, go to the Backup Plans list, and click manage to enter the details page of a backup plan. The detailed procedure is as follows:
On the backup plan details page, click Configure backup source to change the database settings such as its location and IP address. The steps are the same as described in Configure a backup plan.
On the backup plan details page, click Configure backup object to change the database objects for backup. The steps are the same as described in Configure a backup plan.
On the backup plan details page, click Backup Schedule to change the time settings of the backup plan. The steps are the same as described in Configure a backup plan.
On the backup plan details page, click set lifecycle to change the lifecycle settings of the backup plan. The steps are the same as described in Configure a backup plan.
On the backup plan details page, click disable incremental backup to disable incremental backups. The service will only perform full backups and not provide continuous data protection. To enable incremental backups for continuous data protection, click Enable incremental backup.
You can use Hybrid Backup Recovery (HBR) to back up SQL Server databases deployed on Alibaba Cloud Elastic Compute Service (ECS) instances and restore these databases as required. This topic describes how to back up SQL Server databases.
Before you back up SQL Server databases, note the following points:
To create a backup plan, follow these steps:
Database Backup (DBS) is a secure and cost-efficient solution for continuous data protection. The service offers strong protection for data stored in multiple environments, such as enterprise data centers, hybrid clouds, public clouds, and third-party cloud vendors.
ApsaraDB for PolarDB is a cloud-native relational database compatible with MySQL, PostgreSQL, and Oracle. ApsaraDB for PolarDB provides the performance and availability of traditional enterprise databases and the flexibility and cost-effectiveness of open-source databases.
2,599 posts | 762 followers
FollowAlibaba Clouder - August 1, 2019
Alibaba Clouder - August 2, 2019
Alibaba Clouder - August 2, 2019
Alibaba Clouder - February 24, 2020
Clouders - January 12, 2022
francisndungu - August 2, 2018
2,599 posts | 762 followers
Follow
Data Transport
A secure solution to migrate TB-level or PB-level data to Alibaba Cloud.
Learn More
Security Solution
Alibaba Cloud is committed to safeguarding the cloud security for every business.
Learn MoreMore Posts by Alibaba Clouder