Friday, April 7, 2017

SQL Server backup process

Similar to Hyper-V VM backup, DPM leverages VSS technology to take application-consistent snapshots of SQL Server. The VSS file specification provides a list of all the .mdf, .ndf, and .ldf files that are associated for a given database that is enabled for backup. DPM maintains a bitmap file filter that tracks the blocks of SQL Server database files that are changed. For each synchronization event, SQL Server is quiesced, a volume snapshot is taken, and a stable point in time of .mdf, .ndf, and .ldf files are copied to the replica server. With DPM file filters and the change bitmap, only delta changes between the current synchronization and previous synchronization are copied to the DPM replica. With express full technology, customers can essentially do a full back up every day efficiently, both in terms of data transfer as well as storage on the replica server.

In addition, to express full backup, DPM ships a transaction log to the DPM replica storage, thereby minimizing data loss up to 15 minutes. While express full backup is efficient in terms of data transfer and storage on the replica, it is expensive on the disk IOPS since storage snapshots are maintained on the production server while the backup data is being copied. Transaction log backup, alternatively, is lightweight and enables up to a 15-minute recovery point objective (RPO).

Self-service recovery of SQL Server databases
DPM can be configured to enable self-service recovery for a group of users. The first step is to configure a DPM role using the Configure Self Service Recovery option in the Protection view in the DPM console. The list of users that are allowed to perform self-service recovery are added to the role from Active Directory. The specific set of databases that are allowed for self-service recovery are added to the DPM role. The target recovery SQL Server instances are added to the DPM role.

When this option is configured, the end users can install the Self-Service Recovery Tool (SSRT) from System Center 2012 on their client computers and perform SQL Server database restore without the intervention of the backup administrator.

Source of Information : Microsoft System Center

No comments: