Iniciación a SQL Serverpara DBA’s Oracle

Juan C. Erazo M.ApplyTech – CO

Skype: juanc.erazo

1

Contenido General

• Arquitectura de la BD• Administración Básica• Administración del Servidor• Laboratorio• Administrar Objetos Esquema• Seguridad de la BD• Transporte de Datos• Afinamiento del Desempeño• Escalabilidad y Alta Disponibilidad• Monitoreo

2

Arquitectura de la Base de Datos

3

Arquitectura de la BD

• Esquema y Estructura de Datos (Objetos)• Arquitectura de Almacenamiento

– Bloques de Datos, Extents, y Segmentos– Asignación de Almacenamiento– Tablespaces y Datafiles– SQL Server Data Files– Mapeo de Tablespaces y Filegroups– Instancias

• Modelo de Logging• Diccionario de Datos

4

Esquema y Estructura de Datos

• Esquema – colección de objetos pertenecientes a un usuario de bd• Esquemas en SQL Server proveen separación lógica de objetos,

similar al Schema Oracle.

Oracle SQL Server

Table Table

Index Index

View View

Synonym Synonym

Sequence Identity Columns

Procedure Stored Procedure

Function Function

Package N/A

Queue in Streams Advanced Queuing Service Broker Queue

Object Type Type

XML DB XML Schema Collection5

Arquitectura de Almacenamiento

• Incluye estructuras físicas y lógicas.• Físicas: data files, log files y bloques del SO.• Lógicas: subdivisiones de data files usadas para administrar el

espacio de almacenamiento.

Data File Data File Data File Data File Data File Data File

Temporary Tablespace Groups

Tablespace

ExtentExtent

FilegroupTablespace

Segment Segment

ExtentExtent

Filegroup

Heap/Index Heap/Index

ExtentExtent Extent

BlocksBlocksBlocks Blocks Pages Pages Pages

6

Bloques de Datos, Extents, y Segmentos

Estructura Oracle SQL Server 2008+

Unidad mínima de almacenamiento

lógicoBloque Página

Tamaño de bloque Variable 8 KB fijo

Asignación de almacenamiento

En múltiples bloques; son los “extents”

En múltiples páginas; son los “extents”

Tamaño de Extent Variable 64 KB fijo

Segmento

Cualquier estructura lógica a la que se

asigna almacenamiento

No hay una estructura equivalente

7

Asignación de Almacenamiento

8

Tablespaces y Data Files

• Oracle y SQL Server almacenan los datos en archivos.

• La estructura más grande de almacenamiento lógico en Oracle es un tablespace.

• La estructura más grande de almacenamiento lógico en SQL Server es un filegroup.

• Tablespaces/filegroups son usados para agrupar objetos de aplicación.

• Tablespaces/filegroups optimizan la administración de los Data Files.

9

SQL Server Data Files3 tipos de archivos soportados:• Primary Data Files. Cada BD

tiene uno. Extensión mdf. Apunta a los otros archivos y es similar al control file de Oracle pero también puede contener datos de usuario.

• Secondary Data Files. Definidos por el usuario y almacenan los datos de usuario. Son opcionales.

• Log Data Files. Tienen toda la información de log que se utiliza para recuperar la BD. Debe existir al menos uno por cada BD.

10

Mapeo de Tablespaces y Filegroups

System Tablespace

SysAux Tablespace

Temporary Tablespace

BigFile Tablespace

User Data Tablespace

User Index Tablespace

Undo Tablespace

Redo Log Files

Master DB

Resource DB

TempDB

Model DB

MSDB

User DB

Data FG

Index FG

Log File(s)

Oracle DB Instance SQL Server Instance

Data file

Data fileData file

Data fileData file

Tablespace Group

Data fileData file

Data fileData file

Data fileData file

Data fileData file

Data fileData file

Redo LogRedo Log

Redo Log

Log fileData file

Log fileData file

Log fileData file

Log fileData file

User Database

Data fileData file

Data fileData file

Log file

Log fileData file

11

Instancias

12

Modelo de Logging

• Oracle usa redo logs online para registrar los cambios hechos a la BD por transacciones, y segmentos undo para capturar las imágenes “previas” de los datos.

• SQL Server implementa ambas funcionalidades usando transaction logs. Cada registro de transacción contiene la imagen “undo” y “redo” de la transacción.

13

Diccionario de Datos• En Oracle el DD se almacena bajo el esquema SYS en

el tablespace SYSTEM.• En SQL Server el DD consiste de:

– Vista Catálogo — la mejor forma de acceder la metadata del sistema.

– Vistas de Compatibilidad con versiones anteriores — todas las tablas del sistema de versiones anteriores se suministran como vistas de compatibilidad.

– Vistas de Administración Dinámica — para ver el estado actual del sistema. Proveen “fotos” en tiempo real de las estructuras de memoria que indican el estado del servidor.

– Vistas del INFORMATION_SCHEMA — Es el método estándar SQL-99 para ver la metadata del sistema.

– BD RESOURCE contiene la metadata para los procedimientos almacenados del sistema. 14

Administración Básica

15

Contenido

• Planeación y pasos de preparación para un ambiente de SQL Server.

• Creación y configuración de una instancia de SQL Server y sus BD.

• Características de una BD.• Tareas básicas de mantenimiento.

16

Instalación de SQL Server

• Planeación— edición del SW, ubicación de los archivos, convenciones de nombres, opciones de instalación.

• Adquisición de recursos — HW, SO, Utilidades.• Instalar SW y componentes.• Configuración de la red — selección de protocolos,

balanceo de carga, etc., usando el ConfigurationManager.

• Conectividad de clientes y configuración — selección de protocolos, detalles del servidor, etc., usando el Configuration Manager.

17

Sistema de BD

• Crear instancias.• Configuración – los prámetros pueden

establecerse usando sp_configure o el Management Studio.

• Los parámetros de configuración pueden ser básicos o avanzados – algunos se auto-configuran y pueden ser establecidos manualmente.

18

Bases de Datos

• Para nombrarlas se deben usar las mismas reglas que para los identificadores de objetos.

• Definir el propietario de la BD/Esquema.

19

Configuración de la BD

• Las opciones de configuración de la BD se establecen usando ALTER DATABASE y sp_dboptions.

• Pueden clasificarse como:– Opciones “Auto” – auto_close, auto_shrink, …– Opciones de Cursor – restricciones de

direccionamiento y alcance.– Opciones de Recuperación – full | bulk_logged |

simple.– Opciones SQL – ansi_padding, …– Opciones de Estado – offline | online, single user, …

20

Mantenimiento de la BD

• Información de la BD puede obtenerse usando sp_helpdb.• Las operaciones de mantenimiento y su equivalente en Oracle son:

– Reducir (Shrinking) la base de datos – equivalente a reducir (shrinking) todos los archivos en un tablespace

– Cambiar el filegrouppor defecto – igual a cambiar el tablespace por defecto pero se establece para toda la base de datos y no para cada usuario que hace login.

– Asociar o des-asociar DB (Attaching & detaching) – similar a transportar tablespaces

– Cambiar el nombre de la BD – similar en funcionamiento a renombrar tablespaces

– Cambiar el dueño de la BD – no existe en Oracle

21

Procedimientos Almacenados del Sistema

• Equivalen a los paquetes propios del sistema Oracle. Algunas categorías incluyen:

• Active Directory • Catalog • Cursor• Database Engine• Database Mail

and SQL Mail• Distributed Queries• Full-Text Search

• Log Shipping• Automation• Notification Services• Replication• Security• SQL Server Profiler• SQL Server Agent• Web Task• XML• General Extended

22

Administración del Servidor

23

Contenido

• Recursos a nivel de instancia y de servidor como memoria y procesos.

• Recursos a nivel de base de datos cooestructuras de almacenamiento físico y lógico.

• Sesiones de usuario y su actividad.• Estructuras de concurrencia

24

Administración de la Memoria

• La memoria se gestiona a:– Nivel de servidor – asegura la distribución apropiada de la memoria a lo

largo de toda la BD, el sistema operativo y las aplicaciones.– Nivel de instancia – tanto Oracle como SQL server permiten gestionar

los requerimientos de memoria directamente por el DBA o automáticamente.

– El tamaño y el desempeño de los componentes de la memoria se pueden consultar en Oracle a través del OEM o de las vistas dinámicas. En SQL Server el desempeño puede verse a través de los objetos de gestión dinámica, contadores relacionados con la memoria en el Performance Monitor, el SQL Profiler, y otras sentencias de DBCC(DataBase Console Commands).

• SQLServer:Buffer Manager, SQLServer:Cache Manager, SQLServer:Memory Manager

25

Administración de Procesos

• Los procesos de background de Oracle pueden ser configurados para asegurar un desempeño óptimo, mientras que en SQL Server no son configurables (excepto checkpoint).

• Los procesos compartidos de Oracle son configurados usando parámetros como DISPATCHER, SHARED_SERVERS, MAX_SHARED_SERVERS, mientras en SQL Server los workerthreads pueden ser controlados usando la opción de configuración max worker threads.

• En Oracle el número y el desempeño de los procesos puede ser observado usando las vistas de desempeño dinámicas y el OEM. En SQL Server el desempeño puede ser visto a través de los objetos de gestión dinámica, los contadores relacionados a los procesos (Page Faults/sec, Thread Count/sec.) usando el Performance Monitor, SQL Profiler.

26

Administración del Almacenamiento

• Tareas de administración de data files: – Expandir el espacio y de los data files y del transaction log.– Reducir (Shrink) el espacio de los data files y del transaction log.– Adicionar y remover data files y transaction log.

• Los transaction logs de SQL Server combinan las funciones de los redo log de Oracle y los segmentos undo.

• Oracle tiene múltiples redo logs de tamaño fijo que se rotan en forma cíclica con cada checkpoint. Los transaction logs de SQL tienen tamaño variable y se liberan después de un checkpoint o un backup del log.

• La gestión de los tablespaces incluye la creación, eliminación, renombrado y cambio de estado.

• Debido a que SQL Server usa extents de tamaño fijo, su administración no involucra las mismas tareas que se tienen en Oracle bajo ciertas condiciones.

27

Administración de Sesiones y Transacciones

• En Oracle las tablas y vistas del diccionario de datos se usan para relacionar sesiones, transacciones y recursos.

• En SQL Server, las vistas, procedimientos almacenados y utilidades pueden ser usados para proveer datos equivalentes.

28

Bloqueos y Latches (cerrojos)

• Los latches son mecanismos que permiten a las BD evitar accesos simultáneos a estructuras de memoria.

• Monitorear esperas relacionadas con bloqueos usando las vistas del diccionario de datos y los procedimientos almacenados.

• Monitorear bloqueos en con el umbral de procesos bloqueados

• Visualizar los interbloqueos usando el Deadlock GraphProfiler.

29

Laboratorio

30

Contenido

• Instalación de SQL Server• Propiedades de las BD• Propiedades de Almacenamiento• Management Studio

31

Administrar Objetos Esquema

32

Contenido

• Guías para nombrar identificadores en definición de objetos esquema.

• Almacenamiento y estructura de objetos esquema.

• Implementar integridad de datos usando constraints.

• Implementar reglas de negocio a nivel de base de datos usando triggers y secuencias.

33

Identificadores y Nombrado

• Nombres de objetos y columnas no son sensibles a mayúsculas/minúsculas en Oracle, pero son almacenados en el DD en mayúsculas a menos que sean forzados usando delimitadores.

• En SQL Server los nombres de objetos y columnas son almacenados en el catálogo el sistema en la misma forma comoseespecificaron en el comando de creación.

• Los nombres deben ser únicos dentro del mismo esquema. 34

Tablas

• Existe una sintaxis de definición genérica de tablas tanto para Oracle como para SQL Server.

• Existen varios tipos de tablas en ambos motores (temporales, particionadas, variables).

• Lo mismo ocurre con las columnas.

35

Constraints

• Los tipos de constraints son:– NOT NULL– CHECK– UNIQUE– PRIMARY KEY– FOREIGN KEY

• Pueden definirse junto con la definición de las columnas o por fuera junto con la definición de la tabla.

• Los contraints de llave primaria son forzados usando índices únicos y constraints NOT NULL.

36

Triggers

• Los triggers INSTEAD OF de SQL Server son similares a los triggers BEFORE en Oracle.

• Los triggers en SQL Server son a nivel de sentencia.

• Oracle usa estructuras de pseudo-filas:new and :old.

• SQL Server usa pseudo-tablas DELETED and INSERTED.

37

Índices

• Existen índices únicos, no-únicos, no-clustered, B-tree al igual que en Oracle.

• O índices clustered en SQL Server son similares a los IOTs (index-organizedtables) de Oracle.

• Pueden ser creados, eliminados, reconstruidos, reorganizados, puestos offline u online.

• También permite indexar tipos de datos XML y datos espaciales.

38

Vistas

• En Oracle las vistas pueden crearse aun sin permisos o ausencia del objeto base usando la cláusula FORCE.

• En SQL Server la definicion de la vista puede ser almacenada en forma “encriptada” dentro del catálogo del sistema.

• En Oracle las vistas pueden ser definidas con constraints y pueden ser solo de lectura.

39

Seguridad de la Base de Datos

40

Cotenido

• Gestionar el acceso a la BD a través de cuentas de usuario.

• Controlar el acceso a los datos a través de privilegios y roles.

• Gestionar el acceso al servidor usando cuentas de login.

41

Gestión de Usuarios

• En Oracle, un usuario hace parte de todo el sistema de BD. SQL Server usa cuentas de login para acceder a ainstancia y cuentas de usuario para acceder a las BD individuales.

• Los nombres de usuario de Orale y los logins de SQL Server pueen ser auntenticados a nivel de sistema operativo o de la BD. Adicionalmente en SQL pueden también autenticarse en el dominio de red.

• En SQL Server, una cuenta de usuario debe ser creada en cada BD que una cuenta login necesite acceder y puede llamarse diferente que la cuenta login.

42

Gestión de Privilegios

• Oracle y SQL Server controlan el acceso y la actividad dentro de la BD usando privilegios del sistema y de objetos.

• ALTER DATABASE y GRANT son ejemplos de privilegios del sistema mientras que los privilegios a nivel de objeto se manejan con SELECT, INSERT, UPDATE, DELETE.

• Oracle y SQL Server usan la sentencia GRANT para otorgar privilegios y REVOKE para removerlos. Adicionalmente SQL Server tiene la sentencia DENY para suspender privilegios.

• Para listar los permisos sobre objetos se pueden usa la vistas sys.database_permissions y la función fn_builtin_permissions.

43