Administración de bases de datos con SQL Server

Copias de seguridad y restauración

Una copia de seguridad es un mecanismo de protección ante cualquier inconveniente (fallo de lectura de un disco, problema con el hardware del servidor, accesos indeseados al sistema), ya que puede ser necesario recuperar los datos en la última situación estable del servidor.

Tipos de copias de seguridad

Respaldo completo

Hace una copia de seguridad de todo. Almacena todos los objetos de la BD: tablas, procedimientos, funciones, vistas...
También crea una copia del registro de transacciones, para que la BD se pueda recuperar.
Para realizar una copia de seguridad completa, se necesita ejecutar el comando:

BACKUP DATABASE DBDemoATC
To DISK='f:\PowerSQL\DBDemoATC.BAK'
WITH FORMAT,
MEDIANAME = 'Native_SQLServerBackup',
NAME = 'Full-SQLShackDemoATC backup';

Para realizar la copia de seguridad en varios archivos:

BACKUP DATABASE SQLShackDemoATC TO
DISK = 'f:\PowerSQL\SQLShackDemoATC_1.BAK’,
DISK = 'f:\PowerSQL\SQLShackDemoATC_2.BAK’,
DISK = 'f:\PowerSQL\SQLShackDemoATC_3.BAK’,
DISK = 'f:\PowerSQL\SQLShackDemoATC_4.BAK'
WITH INIT, NAME = 'FULL SQLShackDemoATC backup’, STATS 5

Respaldo diferencial

Un respaldo diferencial copia solamente los cambios realizados desde la última copia de seguridad completa, como no copia todos los archivos, suele ejecutarse más rápido.
Las copias de seguridad diferenciales ahorran espacio de almacenamiento y el tiempo para realizar copias de seguridad, a medida que los datos cambian con el tiempo, el tamaño e la copia de seguridad diferencial también aumenta.
Para ejecutar un respaldo diferencial, se ejecuta:

BACKUP DATABASE DBDemoATC
To DISK='f:\PowerSQL\DBDemoATC.BAK'
WITH DIFFERENTIAL,
MEDIANAME = 'Native_SQLServerBackup',
NAME = 'Full-SQLShackDemoATC backup';

Respaldo del registro de transacciones

Realiza una copia de seguridad de los registros de transacciones, como su nombre lo indica, este tipo de copia solo es posible con modelos de recuperación de registro completo o masivo.
Esta copia contiene todos los registros de transacciones que no se incluyeron en la última copia de seguridad del registro de transacciones.
Para crear una copia de seguridad de este tipo, se ejecuta:

BACKUP LOG SQLShackDemoATC
To DISK='f:\PowerSQL\SQLShackDemoATC_Log.trn'
WITH
MEDIANAME = 'Native_SQLServerLogBackup',
NAME = 'Log-SQLShackDemoATC backup';

Copia de seguridad de la copia de seguridad de archivos

Las copias de seguridad de archivos de grupos de archivos de solo lectura se pueden combinar con copias de seguridad parciales. Las copias de seguridad parciales incluyen todos los grupos de archivos de lectura / escritura y, opcionalmente, uno o más grupos de archivos de solo lectura.
Para crear una copia de seguridad de archivo, se ejecuta:

CREATE DATABASE SQLShackFileBackup ON PRIMARY
( NAME = N'SQLShackFileBackup_1',
FILENAME = N'f:\PowerSQL\SQLShackFileBackup_1.mdf' ,
SIZE = 5000KB , FILEGROWTH = 1024KB ),
FILEGROUP [Secondary]
( NAME = N'SQLShackFileBackup_2',
FILENAME = N'f:\PowerSQL\SQLShackFileBackup_2.ndf' ,
SIZE = 5000KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'SQLShackFileBackup_Log',
FILENAME = N'f:\PowerSQL\SQLShackFileBackup_Log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)

Para ejecutar una copia de seguridad a nivel de archivo de los archivos, se ejecuta:

BACKUP DATABASE SQLShackFileBackup
FILE = 'SQLShackFileBackup_1',
FILE = 'SQLShackFileBackup_2'
TO DISK = 'f:\PowerSQL\SQLShackGroupfiles.bak';
GO

Copia de seguridad espejo

Son copias idénticas en la misma copia de seguridad, usualmente se almacenan en diferentes dispositivos de copia de seguridad, incrementando la fiabilidad y reduciendo la probabilidad de pérdida de datos.
Como desventaja se tiene que cada copia de seguridad necesita espacio adicional de disco.
Para crear una copia de seguridad de la BD de este tipo, se usa el comando:

BACKUP DATABASE AdventureWorks2014
TO DISK = 'C:\Mirror_01\AdventureWorks2014_Mirror_01.bak'
MIRROR TO DISK = 'D:\Mirror_02\AdventureWorks2014_Mirror_02.bak'
MIRROR TO DISK = 'E:\Mirror_03\AdventureWorks2014_Mirror_03.bak'
WITH FORMAT

Para hacer una copia de seguridad de los logs:

BACKUP LOG AdventureWorks2014
TO DISK = 'C:\Mirror_01\AdventureWorks2014_Mirror_01.bak'
MIRROR TO DISK = 'D:\Mirror_02\AdventureWorks2014_Mirror_02.bak'
MIRROR TO DISK = 'E:\Mirror_03\AdventureWorks2014_Mirror_03.bak

Automatización de tareas

Los Administradores de Bases de Datos (DBA) tienen que aprender a automatizar tareas repetitivas, de tal forma que dichas tareas sean ejecutadas de forma consistente.
Las ventajas de la automatización son:

• Reducir la carga de trabajo
• Reducir el riesgo de olvidar tareas
• Reducir los errores humanos
• Manejo de proactividad en la administración

Las herramientas nativas disponibles para realizar la automatización de tareas en SQL Server son el Asistente y Diseñador de Planes de Mantenimiento y el Servicio de Agentes para la creación de objetos (jobs, alertas, notificaciones y operadores).

Las operaciones de mantenimiento de rutina que se tienen que ejecutar en el servidor de BD son:

• Asegurar la integridad de las BD con el comando DBCC CHECKDB
• Actualización de estadísticas de los objetos, tablas e índices
• Reducción del tamaño de la BD
• Respaldos de BD
• Tareas de limpieza de datos

Administración de seguridad

Conceptos básicos de seguridad:

• Login de SQL Server
• Usuario de la BD
• Rol de la BD
• Rol de una aplicación

Inicios de sesión (Login)

Es la habilidad de usar una instancia del Servidor SQL, está asociado con un usuario de Windows o SQL. Son autenticados con SQL Server, por lo que son los accesos al servidor, pero no pueden acceder a las BD u otros objetos (para eso necesita usuarios).
Un inicio de sesión (o identidad) e también una cuenta que un usuario puede usar para acceder al servidor SQL. Los inicios de sesión se adjuntan a los usuarios mediante el identificador de seguridad (SID).
para crear un login, se ejecuta: CREATE LOGIN <loginname> WITH PASSWORD = '<Password>';
Si queremos crear un inicio de sesión con una contraseña que debe cambiarse, se ejecuta:

CREATE LOGIN <loginname> WITH PASSWORD = '<Password>'
MUST_CHANGE, CHECK_EXPIRATION = ON;

Para eliminar un inicio de sesión que se usa para conectarse a una instancia de SQL Server
DROP LOGIN login_name;
Para modificar un inicio de sesión, se puede usar ALTER LOGIN, para cambiar una contraseña, deshabilitar/habilitar/bloquear/renombrar un inicio de sesión, etc. Su sintaxis es:

ALTER LOGIN login_name
{ ENABLE | DISABLE
| WITH PASSWORD = 'password' | hashed_password HASHED
[ OLD_PASSWORD = 'old_password' ]
| MUST_CHANGE
| UNLOCK
| DEFAULT_DATABASE = database_name
| DEFAULT_LANGUAGE = language_name
| NAME = new_login_name
| CHECK_EXPIRATION = { ON | OFF }
| CHECK_POLICY = { ON | OFF }
| CREDENTIAL = credential_name
| NO CREDENTIAL
| ADD CREDENTIAL new_credential_name
| DROP CREDENTIAL credential_name };

Sus parámetros son:
login_name: El nombre de inicio de sesión asignado actualmente al inicio de sesión.

• ENABLE: Habilita el inicio de sesión.
• DISABLE: Desactiva el inicio de sesión.
• Password: La nueva contraseña para asignar al inicio de sesión que se autentica mediante la autenticación de SQL Server
• hashed_password: El valor hash de la contraseña que se asignará al inicio de sesión mediante la autenticación de SQL Server.
• old_password: La contraseña anterior que usa la autenticación de SQL Server.
• MUST_CHANGE: Se usa cuando desea forzar el cambio de contraseña la primera vez que se usa el inicio de sesión después de la instrucción ALTER LOGIN.
• UNLOCK: Desbloqueará un inicio de sesión que ha sido bloqueado.
• database_name: La base de datos predeterminada para asignar al inicio de sesión
• language_name: El idioma predeterminado para asignar al inicio de sesión.
• new_login_name: El nuevo nombre del inicio de sesión si está utilizando la instrucción ALTER LOGIN para cambiar el nombre de un inicio de sesión.
• CHECK_EXPIRATION: Determina si se aplica la política de caducidad de la contraseña. Debe especificar CHECK_EXPIRATION = ON cuando use la opción MUST_CHANGE.
• credential_name: El nombre de una credencial para asignar al inicio de sesión.
• NO CREDENTIAL: Elimina las credenciales asignadas del inicio de sesión.
• ADD CREDENTIAL: Agrega una credencial al inicio de sesión.
• DROP CREDENTIAL: Elimina una credencial del inicio de sesión

Ejemplos:
Para cambiar la contraseña: ALTER LOGIN techonthenet WITH PASSWORD = 'bestsite’;
Para cambiar la contraseña y forzar el cambio: `ALTER LOGIN techonthenet WITH PASSWORD = 'bestsite' MUST_CHANGE, CHECK_EXPIRATION = ON;
Para deshabilitar el inicio de sesión: ALTER LOGIN techonthenet DISABLE;
Para habilitar un inicio de sesión: ALTER LOGIN techonthenet ENABLE;

Usuarios de la BD (User)

Un usuario se refiere a una cuenta en la BD que se utiliza para acceder a la BD.
Es la identidad del inicio de sesión cuando está conectado a una BD. Puede usar el mismo nombre que el inicio de sesión pero no es necesario.
Los Logins y grants se le asignan a los usuarios, además de sus propios esquemas (schemas).
Los usuarios por defecto en una BD:

• dbo: Propietario (no puede borrarse de la BD)
• Guest: Permite a usuarios que no tienen cuenta en la BD, que accedan a ella, pero hay que hacerle permiso explícitamente.
• Information_schema: Permite ver los metadatos de SQL Server.
• sys: Permite consultar las tablas y vistas del sistema, procedimientos extendidos y otros objetos del catálogo del sistema.

Para ver los usuarios existentes de una BD, podemos ejecutar:
`USE master
GO
SELECT * FROM sys.database_principals`
Para crear un usuario se usa: Create user <username> for login <loginname>
Para eliminar un usuario ejecuta: DROP USER user_name; esta instrucción solo se ejecuta si el usuario no tiene ningun objeto en la BD.

Un usuario puede también tener permisos o privilegios, esto se refiere a las reglas que rigen los niveles de acceso que tienen los elementos, se puede otorgar, revocar y denegar permisos con el comando:
`Use <database name>
Grant <permission name> on <object name> to <username\principle>`
Por ejemplo:
`USE TestDB
GO
Grant select on TestTable to TestUser`

Roles

Los roles pueden existir a nivel de instancia o de BD.
A nivel de instancia, los logins pueden ser otorgados roles, llamados 'server roles' y no se pueden crear roles nuevos.
A nivel de BD, los usuarios de la BD pueden ser otorgados roles y sí se pueden crear roles nuevos.
Un rol de aplicación sirve para asignarle permisos a una aplicación, tiene una password y no contiene usuarios.
Un rol facilita la configuración y mantenimiento del modelo de seguridad, es un grupo de privilegios relacionados, que se pueden otorgar al usuario, para crear un rol, se ejecuta: CREATE ROLE <role name>
Algunos de los privilegios otorgados a los roles del sistema son:

• Connect: Crear tablas, vistas, sinónimos, secuencias, sesiones, etc.
• Resource: Crear procedimientos, secuencias, tablas, triggers, etc. El uso principal de este rol es restringir acceso a objetos de la BD.
• DBA: Tiene todos los privilegios del sistema.

Para conceder privilegios a un rol, se ejecuta: GRANT <permission name>, <permission name> TO <role name>;
Para darle un rol a un usuario: GRANT <role name> TO <username>, <username>;
Para eliminar un privilegio de un rol: REVOKE <permission name> FROM <role name>;
Para eliminar un rol: DROP ROLE <role name>