10. ARQUITECTURA DE SQL SERVER

Los componentes de SQL se dividen en dos amplios grupos: el motor relacional y las utilidades externas.

SQL Server Database Engine

Es el núcleo del servidor, se encarga de la gestión del trabajo de las bases de datos. Incluye los siguientes componentes:

·        Algebrizer: comprueba los comandos SQL y los transforma para ser procesados internamente. Query optimizer: determina la forma óptima de procesar los comandos SQL.

·        Query Engine: ejecuta finalmente los comandos de acuerdo con el plan generado por el optimizador. Motor de almacenamiento: procesa las operaciones de E/S a disco.

·        Buffer Manager: optimiza el uso  dde memoria reucieno la dependencia de acceso a disco. Checkpoint: realiza operaciones de escritura de memoria a los ficheros.

·        Resource monitor: optimiza la caché de consultas con relación a la carga actual de memoria. Lock manager: gestiona los bloqueos sobre distintos objetos de la base de datos.

·        SQLOS: es una capa añadida que funciona como un pequeño sistema operativo capaz de gestionar recursos propios del servidor tajes como memoria, hilos, peticiones de E/S, etc.

Transact·SQL

Del mismo modo que la mayoría de los sistemas gestores, SQL Server implementa su propia versión de SQL que incorpora él estándar además de extensiones y mejoras incluidas por SQL Server. Todos los comandos enviados al servidor deben ser comandos T·SQL válidos distinguiendo entre los DML (comandos de modificación), DDL (comandos de definición) y DCL(comandos de control).

Policy based manamement

O PBM es el conjunto de declaraciones administrativas que afectan al servidor, a las bases de datos y a cualquier objeto de estas.

Es un componente que centraliza las tareas de administración.

NET Common Language Runtime

Es un componente que permite la integración de Visual Sturlio de Microsoft con SQL Server de forma que se pueda ampliar la funcionalidad de T-SQL tanto para crear prutinas almacenadas como tablas o bases de datos desde el p ropio Visual Studio.

Service Broker

Es un componente que permite balancear la carga de proceso de forma dinámica mediante el uso de buffering, colas e instancias para cada petición.

Replication services

Estos servicios sirven para poder hacer réplicas de datos en diferentes ubicaciones de manera dinámica así como poder realizar transacciones que impliquen datos distribuidos.

Búsquedas Full-text

Estas búsquedas, realizadas sobre todas las palabras de un texto se optimizan enormemente con índices full-text ya que en estos se index cada palabra del campo correpondiente.

Server Management Object (SMO)

Son un conjunto de objetos que porporcionan la configuración con el objeto de facilitar la programación y el desarrollode scripts en el servidor.

Servicios de SQL Server

Estos componentes son de tipo cliente y sirven para controlar o comunicarse con el servidor.

·        Agente SQL Server

Realiza trabajos automáticos del servidor. Base de datos de correo

·        Permite envío de correo

      Permite envío de correo vía smtp. Este correo puede provenir del  propio dservidor, código T-SQL, tareas programadas, alertas, etc.

·        Distributed Transaction Coordinator (DTC)

Proceso que gestiona transacciones que afectan a varios servidores.

Business lntelligence (BI)

Es el conjunto de herramientas que permiten la gestión de datos con el objeto de reaHzar análisis, informes, data mining y visualización. En este sentido incluye tres herramientas que pueden ser utilizadas con el -Busin.ess inteligence Development Studio (BIDS) una versión de Visual Sturuo para el soporte de BI compatible con Visual Studio.

• Integration Services (18) permite la obtención y transformación de datos de toda clase de fuentes para ser integrados con la herramienta extract-transform-loo.d (ETL) del servidor.

• Analysis Services para el análisis online de bases de datos multidimensionales mediante OLAP (On.line Analytical Processing) nsí como métodos de búsqueda de patrones y tendencias en datos mediante Data- Mining.

• Reporling Services (RS) para la visualziación vía web y obtención de informes de datos y su exportación a distintos formatos como pdfo Excel.

Interfaces de usuario (UI) y otras herramientas

• SQL Server Management Studio 
  Es la herramienta más potente y útil para el administrador y desaroolador de bases de datos. Porporciona una interfaz gráfica para la gestión de la mayoría de caracterfsticas de SQL Server tanto de administración como de desarrollo incluyendo el Solution Explorer para la gestión de proyectos.
•   SQL Server Configuration Manager
  Herramienta utilizada para iniciar y detener el servidor así como para establecer parámetros de configuración y conectividad
• SQL Profilertrrace
  Permite depurar aplicaciones y hacer seguimiento de ciertas trazas de eventos u operaciones sobre datos en tiempo real para facilitar la optimi zación del servidor,
• Performance Monitor
  Es una herramienta visual para monitorizar el estado de] servidor y todos sus procesos y operaciones en un momento dado.

Diccionario de datos en SQL Server

Como en la mayoría de gestores de datos, SQL Server incorpora un diccionario de datos en el que se registran toda la información relativa a los distintos objetos de las bases de datos y del servidor,

Esta puede ser consultado con los comandos T-SQL o a través del el Object Explorer de Managemen.t Studio. El diccionario se divide básicamente en dos partes.

·        Bases de datos del sistema

o   Master: con información de las bases de datos del servidor.

o   Msdb: mantiene un listado de actividades de backup y tareas ojobs para cada usuario.

o   Tempdb: espacio temporal para creación de tablas, bases, rutinas almacenadas o para espacio temporal en el procesamiento de consultas.

·        Vistas

o   Es donde se almacena la información realmente 'tuil del diccionario se compone de las siguientes vistas: Catalogo: contiene información de datos estáticos acerca de tablas, seguridad y configuración del servidor.

o   Dyn.amic management uiew (DMW): almacena información de estado del servidor en cuanto a recursos en uso y onexiones activas.

o   Funciones de sistema y  variables globales: información de estado del servidor, de las bases de datos y variables de configuración.

o   Information schema: con información sobre objetos de cada base de datos siguiendo el estándar ISO de SQL,

Ediciones de SQL Server

Existen di versas ediciones del Servidor SQL a continuación veremos un listado de las mi smas con una brve descripción. Finalmente mostraremos el proceso de instalación de la edición libre express.

• Enterprise CDeueloper) edition.: es la más avanzada e incluye todas las posibilidades del servidor. Estaá oritentada a grandes sistemas de muchas bases de datos. La versión developer es igual pero solo liceniada para pruebas y desarrollo.
• Standardedition: es la edición para sistemas medios y recomendable para probar antes de pasar a la Enterprise Edition,
• Workgroup edition: edición más ligera ya que no incluye el componente Integration Services. Está orientada para pequeños negocios.
• Edición Web : sirve para alojar aplicaciones de sitios web. En partículas la SQL Server Express Edition es una versión limitada a 4GB de espacio para bases de datos y no más de 25 usuarios. Recomendable para pequeñas aplicaciones .Net que utilicen SQL server como base de datos.
• Edición compacta o Compact Edition (CE): es un servidor compacto más limitado que el anterior y cuyo uso está orientado para aplicaciones de móviles.
• SQL Data Seruices (SDS): proporciona características de alta disponibilidad y es la base de datos de Microsoft Azure.

Cuentas de usuario y permisos

La arquitectura de seguridad de SQL server pude representarse mediante el siguiente esquema:

La autenticación en SQL server es similar a MySQL, una vez autenticado el usuario se le otorgan los permisos correspondientes. La información de login se almacena en la base de datos master.

Un usuario en general se identifica mediante tres métodos posibles:

• Como usuario de Windows con su login correspondiente.
• Como miembro de un grupo de usuarios Windows.
• Como usuario de SQL Server con el login específico del servidor.

Una vez autenticado el usuario tiene los permisos que se le hayan concedido via mediante grant I incluyendo los correspondientes a los roles que tenga asignados.

A nivel de sistema operativo debe notarse que aunque lo susurios no tienen porque interaccionar con el sistema de archivos (salvo a través del servidor) si debemos considerar que el proceso SQL Server tiene que hacerlo por lo cual req uiere una cuenta de tres posibles:

·        Local: para cuando no es necesario trabajar en red.

·        Local de sistema: es buena para un solo servidor peor falla cuando hay procesamiento distribuido en red.

·        Cuenta de dominio: es la opción recomendada ya que es una cuenta creada ad-hoc para el servidor a la que se pueden otorgar distintos privilegios.

Vamos a ver ahora las opciones más interesantes desde el punto de vista de T-SQL:

Gestion de cuentas

Eliminar una cuenta

Denegar una cuenta de Windows

Modificar cuentas

De manera similar a la creación de cuentas podemos modificarlas con alter login:

A nivel de base de datos,los usuarios son independientes de las cuentas, así podemos crear y eliminar usarios a partir de cuentas o logins para cada base de datos.

Crear usuarios

Borrar usuarios

Modificar usuarios

SQL Server utiliza el paradigma de roles para englobar un conjunto de permisos bajo un mismo nombre. Distinguimos roles de sistema y de bases de datos. Del primero existen algunos predefinidos, por ejemplo Dbcrealor que permite crear, modificar, eliminar y restaurm' cualquier base de datos o el rol Processadmin para crear y eliminar procesos del servidor. Aunque sin duda es el Sysadmin el usuario que tiene todo el control sobre todos los objetos del sistema.

En el caso de roles de bases de datos existe el db_owrter con todos los permisos sobre la base de datos o db_ ddladmin que puede ejecutar comandos ddi como u·eate. drop o alter.

Para añadir un usuario a un rol se usa un procedimiento alamcenado llamado sp_addsrutolemember..

Para eliminarlo sp_dropsruroiemember.

♠

Para crear/eliminar un rol usarnos create role.

♠

Para crear/eliminar un rol usarnos create role.

♠

Para eliminar un miembro de un rol:

♠

Gestion de permisos y privilegios

En SQL Server los permisos se otorgan sobre los distintos objetos usando los comandos DeL (GRANT, REVOKE y DENY) y procedimientos almacenados predefinidos.

Se distinguen permisos sobre objetos y en forma de sentencia.

Son los stalement permissions que incluyen alter table, alter datahase o create function y se otorgan con la siguiente sintaxis:

♠

Donde security account puede ser un rol, un usuario de SQL Server o un usuario o grupo de Windows.

Los pennisos posibles sobre objetos induyen seLect, insert, update, deleu, dri (para claves ajenas) y execute para ejecución de procedimientos almacenados.

Para conceder permisos se usa el comando grant:

♠

Del mismo modo para revocar permisos usamos:

Encriptación

SQL server incropora la funcionalidad de encriptación de datos usaodo cuatro métodos:

Frase de paso

Es como usar una contraseña pero con más restricciones.

En el ejemplo se usa la función EncryptByPassPhrase con la frase de paso seguida de los datos bancarios a encriptar para lo cual el campo número de cuenta debe ser de tipo uarbinary:

Para desencriptar la información puede usarse la función DecryptByPassPhrase.

Las frases de paso son texto claro y poco seguras así que es mejor usar encriptación simétrica con el comando CREATE SIMETRIC KEY, como en el ejemplo:

♠

Donde el algoritmo usado es típicamente triple_des o aes~50,  aunque hay algunos más.

Para  usar la clave debe ser abierta en primer lugar con el comando OPEN SIMETRIC KEY ccardkey DECRIPTION BY PASSWORD= P@s$wOrD              '                  '.

Después hacemos las operaciones normales como inserción de datos:

♠

Después para desencriptar usamos decriptbykey y finalmente la cerramos con:

♠