Backup and restoration of SQL Server databases are configured / operated with the Web Console.
Configurations for Backup and Restoration of SQL Server Databases
When the Backup Wizard for SQL Server is used, the SQL Server database backup settings and creation backup execution scripts, both of which require complex procedures, can be simplified.
Operation of Backup and Restoration of SQL Server Databases
Registering the backup scripts created by the Backup Wizard for SQL Server to the Scheduler can automatically perform backup operation of SQL Server databases.
When the Restore Wizard for SQL Server is used, SQL Server database restoration, which requires complex procedures, can be easily carried out.
From configuration of backups to the execution flow is shown below.
Figure 10.11 Flow from Configuration of Backups to Execution
Note: Refer to "Chapter 11 Scheduler" for how to register this to the scheduler of ETERNUS SF.
Refer to "10.5.5 Restoration Using Restore Wizard for SQL Server" for the flow of the restoration task.
The Backup Wizard for SQL Server simplifies the backup operation for SQL Server databases.
When the Backup Wizard for SQL Server is used, the SQL Server database backup settings and operations, both of which require complex procedures, can be simplified by users even without specialist knowledge.
In the Backup Wizard for SQL Server, the following functions are provided:
Backup settings
Configure backup of its source and destination.
Creating Backup Execution Script
Full Backup Execution Script
The script that will execute a full backup of the user database.
Log backup execution script
The script that will perform a backup of the transaction log of the user database.
Configuration Removal Script
Script that clears the backup configured by the wizard.
Refer to "10.5.4.1 Creating Backup Execution Script" for details of each script.
When the Restore Wizard for SQL Server is used, SQL Server database restoration, which requires complex procedures, can be easily carried out by users even without specialized knowledge.
The Restore Wizard for SQL Server will support the restoration of databases backed up with the complete backup execution script or log backup execution script created with the Backup Wizard for SQL Server.
The system configuration recommended by this feature is indicated below:
Recommended Configuration 1
Database Servers(cluster configurations)
A Management Server-cum-Backup server(Non-cluster Configuration)
Recommended Configuration 2
Database Servers(cluster configurations)
A Management Server-cum-Backup server (Cluster Configuration)
Figure 10.12 Recommended Configuration 1 (Example)
Point
Though Storage Cruiser's agent does not support a cluster operation, install Storage Cruiser's agent in each physical node.
Figure 10.13 Recommended Configuration 2(Example)
Point
Though Storage Cruiser's agent does not support a cluster operation, install Storage Cruiser's agent in each physical node.
The servers that configure the SQL Server backup / restoration operation are explained below.
Server Type | Server Roles | Required/Optional | ETERNUS SF and Installed Products of SQL Server |
|---|---|---|---|
Active Directory Server | Active Directory is used for user authorization. In order to prevent a single failure of a domain controller from affecting the operation of the domain, a redundant setup with a plurality of units is recommended. | Optional (*1) This is necessary when using Active Directory for user authorization. | - |
Database Server | The server on which the database of the SQL Server to be backed up operates. In addition, the server to which the ETERNUS Disk storage system storing the database of SQL Server is connected. | Required |
|
Backup Server | The server which will serve as the destination for the backup of the database of SQL Server. A backup volume must be connected. | Required |
|
Management Server | The Management Server for performing an online backup of the database of SQL Server. The Management Server manages the Managed Server (Database server and Backup server). Only 1 unit is installed within the system. | Required | ETERNUS SF Manager(AdvancedCopy Manager's manager) |
*1: In the case that the Management Server or the Managed Server is of a cluster configuration, an Active Directory server is required.
*2: In the case that this server already exists together with the Management Server, it will not be necessary to install the AdvancedCopy Manager's agent.
Server Type | Active Directory Server | Database Server | Backup Server | Management Server |
|---|---|---|---|---|
Active Directory server(*) | - | N/A | N/A | A |
Database Server | N/A | - | A | A |
Backup Server | N/A | A | - | A |
Management Server | A | A | A | - |
A: available; N/A: not available
*: The AdvancedCopy Manager's agent cannot be installed on a Windows server that serves the role of the Active Directory domain service.
Note
It is necessary for the ETERNUS SF Manager (AdvancedCopy Manager's manager), the AdvancedCopy Manager's agent, and the Storage Cruiser's agent to be of version 16.1 or later.
In the case that Active Directory is used for user authorization, it is necessary to configure each server to an Active Directory environment.
Virtual Environment
The range of support of virtualized software (host / guest) is indicated below in the following table.
Platform | Managed Server | Management Server | |
|---|---|---|---|
VMware | Host | N/A | N/A |
Guest (*1) | A | A | |
Hyper-V | Host | A | A |
Guest | N/A | A (*2) | |
KVM | Host | - | A |
Guest | - | A | |
A=Available
N/A=Not Available
-: Not for Backup Wizard for SQL Server and Restore Wizard for SQL Server
*1: VMware Tools must be installed.
*2: The Manager supports Windows Server 2008 R2, Windows Server 2012 and Windows Server 2012 R2.
Note
Virtualization of all servers is supported, with the exception of Active Directory.
The range of support of the virtualized environment is in accordance with the range of support of the AdvancedCopy Manager and the range of support of the Storage Cruiser.
In the case that the Management Server exists together with the database server or the backup server, it is necessary for the OS of the Management Server to be Windows.
The cluster configuration supported by the database server and the backup server is indicated below. Refer to "10.4.2.5 Operation by Cluster" for details.
1: 1 Standby Operation
N: 1 Standby Operation
Mutual Standby Operation
Cascade Operation
For details of the cluster operation, refer to "14.1.5 Notes on Cluster Operation" and "14.1.6 Notes on Backup in Clustered System".
Backup Function
The database type supported with the Backup Wizard for SQL Server is as follows:
Database Type | Backup Type | |
|---|---|---|
Full Backup | Log Backup | |
System database | N/A | N/A |
User database | A | A |
A=Available
N/A=Not Available (This is not supported with the wizard. Backups are performed by directly operating the SQL Server.)
Full Backup
Backups will be made for data files and transaction logs necessary for recovery in units of databases. With this, it will be possible to perform recovery to the point when full backup has been completed.
Log Backup
The transaction log will be backed up. By performing log backup, it will be possible to perform recovery to a point in time immediately prior to the failure. In addition, when log backup is performed, the transaction logs of completed transactions will be deleted (disposal of logs).This will preventing the log file from becoming excessively large. The backup destination is the file system region (NTFS or ReFS(see Note)) on the Managed Server (Database server).
Note: The ReFS (Resilient File System) is a file system of Windows 2012.
Point
The only type of snapshot replication supported by this feature is the QuickOPC Type Replication.
A backup image is shown in the following chart:
Figure 10.14 Backup Destination
The recovery models of the SQL Server supported with this feature are as follows. For details of recovery models, refer to the SQL Server document.
Recovery Models | Backup | |
|---|---|---|
Full Backup | Log Backup | |
Full | A | A |
Bulk-Logged | A | A |
Simple | A | N/A |
(A=Available, N/A=Not Available)
Restore Functions
The recovery models of the SQL Server supported with this feature are as follows. For details of recovery models, refer to the SQL Server document.
Recovery Models | Restore | |
|---|---|---|
Latest Point | Backup Point | |
Full | A | A |
Bulk-Logged | A(*) | A |
Simple | N/A | A |
(A=Available, N/A=Not Available)
*: In the case that bulk operations (bulk import and creating of indexes) are performed after the latest full backup, it might not be possible to perform restoration to the latest point (recovery to immediately prior to the occurrence of the failure).
Refer to "Backup with the Bulk Log Recovery Model" in the "SQL Server Books Online" for more details.
A diagram illustrating restoration to the latest point and restoration to a backup point is shown below:
Figure 10.15 Restoring to Latest Point
Figure 10.16 Restoring to Point When Backup Was Taken
Point
Backup / restoration can be performed only in cases when the AlwaysOn availability group of the instance of the SQL Server database is not effective.
The only type of snapshot replication supported by this feature is the QuickOPC Type Replication.
Volume Configurations
The configurations of supported volumes are as follows:
Placement of the Data Files and the Transaction Log Files on Separate Volumes (recommended configuration)
Figure 10.17 Placement of the Data Files and the Transaction Log Files on Separate Volumes (recommended configuration)
1 database placed on 1 volume
Figure 10.18 1 database placed on 1 volume
1 database placed on a plurality of volumes
Figure 10.19 1 database dispersed and placed on a plurality of volumes
A plurality of databases placed on 1 volume
Figure 10.20 A plurality of databases placed on 1 volume
Note
It is required that all databases are databases under the same instance. In addition, the system database cannot be included in any of the databases (the tempdb is allowed).
When performing restore in this configuration, it is required to select all the databases in the volume.
An N number of databases placed on an M number of volumes
Figure 10.21 An N number of databases dispersed and placed on an M number of volumes
Note
It is required that all databases are databases under the same instance.In addition, the system database cannot be included in any of the databases (the tempdb is allowed).
When performing restore in this configuration, it is required to select all the databases in the volume.
Note
Do not store any files other than target database files in the volumes on which the database files are placed.
Locate the following of the database to be backed up so that the file path length may become 254 bytes or less.
Primary data file
Secondary data file
Transaction log file
Place the database files and the transaction log files on separate volumes (recommended).
If a full text catalogue is used, it is necessary to create on any of the database volumes.
If the full text catalogue is created on a non-database volume, the full text catalogue will not be backed up and will need to be reconstructed after restore is executed.
It is required that the volume to be backed up and the backup destination volume is configured of the same size on the same ETERNUS Disk storage system (it is necessary for these sizes to be identical to the number of bytes). Furthermore, the volumes that may be selected as the backup destination are Standard, TPV (Thin Provisioning Volume), FTV (Flexible Tier Volume), and WSV (Wide Striping Volume).
Confirm that the backup destination volume is not being used for other purposes.
The format of the disk partitions shall be MBR or GPT and it is necessary for the partitions formats of the volume to be backed up and the backup destination volume to be the same.
It is required that the partition on which the database of the SQL Server is placed is formatted in advance with the NTFS or ReFS format and that a drive letter or a mounting point folder path is assigned.
It is required to assign a drive letter or mounting point folder path to the partition of the backup destination volume.
The size of the partition must be the same number of bytes as that of a partition on which SQL Server database as backup source is located.
It is required that a drive letter or a folder is assigned to the mounting point of the LUN.
The supported assignment of drives and folder paths are listed below in the following table.
Usefulness | Assignment Destination | Supported Y/N |
|---|---|---|
System Drive | drive letter | N/A |
Folder Path | A | |
Other than System Drive | drive letter | A |
Folder Path | A |
(A=Available, N/A=Not Available)
A database constructed on a low partition will not be supported. It is required that the database is constructed on the file system.
Only 1 partition / LUN will be supported.
Spaces and the characters ! " % ' ( ) - ^ \ @ [ ] : ; , . = ~ | ` { } * + < > ? / & are not supported for instance names.
The characters " \ : | * < > ? / ' are not supported for database name.
The characters " \ : | * < > ? / are not supported for file names of the database.
