databaseadmonfundamentalitprosdcchapter6
TRANSCRIPT
![Page 1: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/1.jpg)
Julián Castiblanco P.http://julycastiblanco.blogspot.com/
MCT-MCITP-MCTS SQL Server 2008/2005
Líder ITPros-DC
SESIÓN 6- Security & Recovery
![Page 2: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/2.jpg)
CONTENIDO DEL CURSO
![Page 3: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/3.jpg)
CONTENIDO DEL CURSO
![Page 4: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/4.jpg)
Seguridad
![Page 5: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/5.jpg)
Server Role
SQL Server Login
Windows Group
Domain User Account
Local User Account
SQL Server
Database
Windows
Securables
Permissions
Principals
User
Database Role
Application Role
Qué es un Principal?
![Page 6: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/6.jpg)
• Resources that can be secured
• Securables are contained within scopes
Server
Database
Schema
Qué son los objetos asegurables?
![Page 7: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/7.jpg)
GRANT, REVOKE, DENY
• GRANT is used to assign a permission
• DENY is used to explicitly deny a permission
– Used where permissions inherited through group or role membership
– Should only be used in exceptional circumstances
• REVOKE removes either a GRANT or a DENY
Grant, Revoke, DENY?
![Page 8: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/8.jpg)
Aseguramiento y Recuperación
![Page 9: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/9.jpg)
Buffer Cache
Data pages are located in,
or read into, the buffer cache
and then modified2
Modification is recorded
in transaction log on disk3
Checkpoint writes
dirty pages
to database
4
Data modification is
sent by application1
Transaction logs provide a history of actions executed by a database management system to guarantee ACID properties
Cómo Funciona el log transaccional?
![Page 10: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/10.jpg)
Backup Types Description
Full All data files and active part of the transaction log
Differential The parts of the database that have changed since the last full database backup
Partial The primary filegroup, every read/write filegroup, and any specified read-only filegroups
Transaction Log Any database changes recorded in the log files
Tail-log Backup Log backup taken of the tail of the log just before a restore operation
File/File Group Specified files or filegroups
Copy Only The database or log (without affecting the backup sequence)
Tipos de Backups?
![Page 11: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/11.jpg)
Full Database Backups:
Backup all data and part of the log records
Can be used to restore the whole database
Permit recovery to backup times only
Sunday Monday Tuesday
Estrategia de Full Backup
![Page 12: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/12.jpg)
BACKUP DATABASEAdventureWorks2008R2 TO DISK ='L:\SQLBackups\AW.bak'
WITH INIT;
• Backup entire database
• Backup active portion of log file
Como se genera un Full Backup
![Page 13: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/13.jpg)
A Database and Transaction Log Backup Strategy:
Involves at least full and transaction log backups
Enables point in time recovery
Database can be fully restored in the case of data file loss
Sunday Monday
Estrategias de Log Back Up
![Page 14: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/14.jpg)
BACKUP LOG AdventureWorks2008R2 TO DISK = 'L:\SQLBackups\AW_Log.bak'WITH NOINIT;
• Backup the transaction log only
• Backs up log from the last successfully executed log backup to the current end of the log
• Truncates inactive log records unless options specified
Note: Database must be in full or bulk-logged recovery model
Creación de un Log Back Up
![Page 15: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/15.jpg)
A Differential Backup Strategy:
Involves performing full and differential database backups
Includes differential backups with only changed data
Is useful if only a subset of a database is modified more frequently than the rest of the database
Monday Tuesday
Estrategias de Backup Diferencial
![Page 16: Databaseadmonfundamentalitprosdcchapter6](https://reader034.vdocuments.us/reader034/viewer/2022052509/55aea3fe1a28ab9e058b4834/html5/thumbnails/16.jpg)
BACKUP DATABASE AdventureWorks2008R2 TO DISK = 'L:\SQLBackups\AW_Diff.bak'WITH DIFFERENTIAL, INIT;
• Backup the extents changed since the last full database backup
• Store active part of the transaction log to be able to recover the database
• Independent of other differential backups
Note: You cannot create a differential database backup if no full backup has ever been created
Creación de Backup Diferencial