El Data Warehousing en la actualidad

El Data Warehouse es un marco para gestionar mejor, entender y sacar provecho de los datos generados por el negocio. El almacén de datos tradicional tiraba de  los datos en un "depósito de la verdad", esquema impulsado para el análisis y la presentación de informes, y funcionó muy bien durante muchos años. Sin embargo, el mundo de los datos está evolucionando rápidamente en formas que están transformando la industria y las empresas están considerando nuevos enfoques de inteligencia empresarial (BI).

El data Warehouse tradicional está siendo presionado por el creciente peso del volumen explosivo de datos, la variedad de tipos de datos y la velocidad de procesamiento en tiempo real así también de cómo estos los datos se están utilizando para crecer y operar el negocio. 

El almacenamiento de datos ha alcanzado el punto de inflexión más importante desde su creación. La empresa moderna necesita una arquitectura lógica que pueda escalar sin problemas para satisfacer estas demandas de volumen con una potencia de procesamiento en tiempo real y capacidad de manejar cualquier tipo de datos para conectar rápidamente el negocio con información valiosa. Esto significa que el data warehouse tradicional tiene que evolucionar hacia un data warehouse moderno.

El  data warehouse tradicional fue diseñado para ser un repositorio central de todos los datos de una empresa. Los datos dispares de sistemas transaccionales como  ERP, CRM y aplicaciones de línea de negocio debían ser adaptadas para extraer los datos (ETL) e introducirlo en el almacén dentro de un esquema relacional global. La estructura de datos era predecible y el procesamiento y presentación de informes de calidad optimizada. Sin embargo para la preparación de consultas se necesitaba gran medida el apoyo de tecnologías de la información TI basadas en el procesamiento por lotes programados.

La llegada de  la Web 2.0 aumentó significativamente los datos relacionados con la empresa generados a través de comercio electrónico, registros web, marketing en buscadores, y otras fuentes. Las Empresas ampliaron las operaciones de ETL para compensar las nuevas fuentes de datos, en última instancia, también  expandieron el modelo.

Sin embargo, incluso con estas complejidades crecientes, el valor de negocio principal del almacén de datos tradicional era la capacidad de realizar un análisis histórico y la presentación de informes procedentes de una fuente confiable y completa de datos.

Planes de ejecución con SQL Server III, planes de ejecución gráficos

Los planes de ejecución nos ayudarán a escribir código SQL eficiente, solucionar problemas de SQL o monitorear e informar sobre nuestros sistemas.
Aquí se pueden consultar las dos partes previas:

Planes de ejecución con SQL Server I

Planes de ejecución con SQL Server II

Permisos requeridos para ver los planes de ejecución

Para generar planes de ejecución de consultas es necesario tener los permisos necesarios dentro de la base de datos. El administrador de base de datos tendrá generalmente todos los permisos pero para dar permisos a cualquier usuario se puede ejecutar.

GRANT SHOWPLAN TO [nombre de usuario]

Esta acción permitirá a un usuario ver los planes de ejecución para la base de datos.

Trabajar con los planes de ejecución gráficos

Comenzaremos con una de las consultas más simples posibles,

SELECT * FROM dbo.tbNombretabla

Ver el plan estimado

Vamos a ver el plan de ejecución estimado en forma gráfica que ha generado el optimizador de consultas.
Es posible averiguar la estimación del plan más óptimo calculada por el optimizador de una delas siguientes maneras, primero hay que seleccionar la consulta a analizar y después:

• Hacer clic en el icono plan de ejecución estimado en la barra de herramientas.

Administración de la seguridad con SQL Server: IV Permisos de acceso a objetos

Permisos de acceso a objetos

El último filtro en el proceso de seguridad es la posibilidad de habilitar permisos para manejar cada uno de los objetos de una base de datos de SQL Server. Una vez que un login id tiene acceso a una base de datos mediante un username, todavía puede definirse qué sentencias podrá ejecutar y sobre qué objetos de la base de datos.

Existen tres tipos de permisos: de objeto, de sentencia e implícitos

-Permisos de objeto permiten utilizar y modificar los objetos existentes. Por ejemplo, se podrá dar permiso para añadir o eliminar una tabla, o para realizar sobre ella una consulta.

- Permisos de sentencia permiten ejecutarlas, sin limitar  ese permiso de ejecución a un objeto en particular. Suelen referirse a sentencias de creación o eliminación de objetos.

- Permisos implícitos. Son los que se da a los miembros de las funciones del servidor preestablecidas (como la sysadmin), y a los propietarios de bases de datos. Este tipo de usuarios tienen permisos no modificables, que son los que entran en la categoría de implícitos.

Arquitectura de permisos

Quién tiene permisos y quién puede otorgarlos

Por defecto, tras la creación de la base de datos sólo el propietario de la base de datos (los miembros de db_owner y los agrupados bajo el usuario dbo) tienen permisos para crear objetos en la misma. Esta facultad no suele transmitirse a otros usuarios, pues se supone que éstos son los únicos con permisos para modificar su estructura.
Una vez se crea un objeto en la base de datos, tan sólo el creador del objeto y los administradores (sysadmin) tienen permisos para manipulado. Por defecto, el administrador posee todos los permisos para todos los objetos.
El comando DENY hace que un usuario no tenga permiso para un acceso a un objeto. El comando REVOKE sólo afecta al usuario o función en que se ejecute, permitiendo que otros comandos GRANT en otras entidades que incluyan al mismo usuario puedan deshacer su efecto.

La tabla sysprotects

Cada vez que se otorga un permiso, se añade una fila a la tabla de sistema sysprotects, presente en todas las bases de datos. Esta tabla contiene las siguientes informaciones:

Id: ID del objeto para el que se otorga permiso.
Uid: ID del usuario o función al que se otorga permiso.
Action: Permiso que se otorga.

Este último puede ser:

REFERENCES
SELECT
INSERT
DELETE
UPDATE
CREATE TABLE
CREATE DATABASE
CREATE VIEW
CREATE PROCEDURE
EXECUTE
BACKUP/RESTORE DATABASE
CREATE DEFAULT
BACKUP/RESTORE LOG
CREATE RULE

Protecttype: Tipo de protección ante la posibilidad de que el permiso pueda transmitirse.

Column: Indica las columnas a las que se aplica el permiso.

Grantor: Identificador del usuario que ejecutó la sentencia GRANT o DENY que motivó que se añadiese la fila de la tabla.

Permisos a objetos

Hay dos formas de dar permisos de acceso a datos, una de ellas es hacerlo a través de permitir, revocar o denegar el trabajo con cada uno de los objetos. Esto permite dar permisos a tablas, columnas de tabla, vistas, etc. Los permisos pueden darse a través del Administrador Corporativo a través de la ejecución de procedimientos almacenados del sistema. Es posible dar permisos para ejecutar las siguientes sentencias sobre cada uno de los objetos.

Permisos para tablas

SELECT
UPDATE
DELETE
INSERT
DRI (Declarative Referential Integrity)
DRI permite crear un objeto que tenga relaciones de integridad referencial con el objeto al que damos permisos.

Permisos para columnas

SELECT
UPDATE

Permisos para vistas

SELECT
UPDATE
DELETE
INSERT

Permisos para procedimientos almacenados

 EXECUTE

Dar permisos a objetos con el Administrador Corporativo

Para dar, denegar o retirar permisos a objetos desde el Administrador Corporativo. Situarse sobre la base de datos en la que se encuentra el objeto.
Situarse sobre el objeto, sea una tabla, una vista o un procedimiento almacenado. Sobre el objeto pulsar botón derecho del ratón lo que muestra el un menú, pulsar en propiedades, esto nos abre un cuadro de diálogo con varias  pestañas, entre ellas la de permisos la cual nos muestra los tipos de  permiso que tiene el objeto.

Planes de ejecución con SQL Server II

Planes estimado y real de ejecución

Como se mencionó en la primera parte de Planes de Ejecución con SQL Server, hay dos tipos distintos de plan de ejecución. En primer lugar, existe el plan que representa la salida del optimizador. Es el plan de ejecución estimado. Corresponde con una primera estimación del optimizador y no representan la ejecución real cuando se ejecute la consulta física.
El siguiente es el plan que representa la salida real de la ejecución de la consulta. Este tipo de plan es el plan de ejecución real y representa lo que realmente ocurrió cuando se ejecutó la consulta.

Administración de la seguridad con SQL Server: III Gestión de la seguridad

Gestión de la seguridad

Aquí se describen las técnicas y mecanismos para implementar los mecanismos de seguridad. 

Administración de la seguridad con SQL Server 1ª Parte

Administración de la seguridad con SQL Server 2ª Parte

Identificadores de inicio de sesión: Creación

Crear inicios de sesión, sólo será necesario si vamos a utilizar autenticación de SQL Server.  Cuando se añade el nuevo usuario, SQL Server escribe una nueva fila en la tabla syslogins de la base de datos master. Esta tarea puede llevarse a cabo utilizando comandos SQL o a través del Administrador Corporativo.

Creación de identificadores mediante el Administrador Corporativo

Para añadir un identificador de inicio de sesión desde el Administrador Corporativo, seleccionamos la carpeta seguridad y dentro de esta, inicios de sesión que deseemos crearlo, y pulsamos botón derecho del ratón y elegimos Nuevo inicio de sesión.

 

 A continuación, especificamos la información de autenticación del identificador a crear.

 

La primera página de este cuadro de diálogo multipágina nos permite rellenar los siguientes datos:
- Nombre del identificador.
- Tipo de autenticación: Debe marcarse autenticación de SQL Server.
- La contraseña para el identificador.
- La base de datos por defecto para el identificador, en la que se colocará por defecto al usuario.
- El idioma predeterminado para el inicio de sesión.

En la segunda página,  Roles del servidor permite especificar en qué funciones de servidor preestablecidas se incluirá el identificador de inicio de sesión que va a crearse. 

 

En la página Asignación de usuarios, podremos indicar a qué bases de datos tendrá acceso el identificador de inicio de sesión  y al usuario de bases de datos a la que se asociará.

 

La siguiente pestaña es la de elementos protegibles, aquí podemos dar o denegar 

Finalmente en la carpeta Estado podemos conceder o denegar permiso de conexión al motor de base de datos y habilitar o deshabilitar el inicio de sesión.
 

Creación de inicios de sesión a través de comandos

Podemos crear inicios de sesión ejecutando el procedimiento almacenado sp_addlogin.

sp_addlogin  identificador [, contraseña [, password [, base de datos por defecto, [, lenguaje [, id numérico [, opción de encriptación  ]]]]]]

Algunas consideraciones sobre este procedimiento almacenado.

- Puede omitirse la contraseña.
- Si no se especifica la base de datos por defecto, se considerará la master como tal.
- El identificador numérico tiene sentido cuando existe un usuario en la tabla sysusers de una base de datos cuyo id apunte a un identificador de inicio de sesión inexistente, desee crear un nuevo inicio de sesión con el id del usuario de base de datos en cuestión.
- El parámetro opción de encriptación permite especificar si la contraseña se almacena o no encriptada. Si se omite, la contraseña se encriptará. Si ponemos como parámetro skip_encription, la contraseña no se encriptará.

Identificadores de inicio de sesión: Modificación y eliminación

Mediante el Administrador Corporativo

Para modificar las propiedades de un identificador de inicio de sesión desde el Administrador Corporativo, seleccionamos el inicio de sesión y con el botón derecho del ratón elegimos propiedades.

Esto nos abre de nuevo las pantallas explicadas anteriormente con lo que podemos modificar los parámetros elegidos. Si elegimos eliminar eliminaremos el inicio de sesión.

Modificación y eliminación de inicios de sesión a través de comandos SQL

La información de configuración de un identificador de inicio de sesión puede modificarse con los siguientes procedimientos almacenados.
Base de datos por defecto: sp_defaultdb
Facilita al sistema el nombre de la base de datos a la que un identificador de inicio de sesión accederá al entrar en el sistema.
La sintaxis de uso es:

sp_defaultdb nombre de usuario, base de datos

Idioma por defecto: sp_defaultlanguage
Este procedimiento almacenado facilita al sistema el lenguaje con el que el identificador accederá al iniciar una sesión en el sistema. Su sintaxis es:

sp_defaultlanguage ‘nombre de usuario’ [, ‘lenguaje’]

Cambio de contraseña: sp_password
Este procedimiento almacenado permite cambiar la contraseña de acceso al servidor de un identificador de inicio de sesión.

La sintaxis de uso es:

sp_password ‘contraseña antigua’, ‘contraseña nueva’, [,’usuario’]

Para eliminar un identificador de acceso utilizaremos el procedimiento almacenado sp_droplogin. La ejecución del procedimiento eliminará la línea correspondiente al identificador, en la tabla syslogins. Sólo el administrador del sistema puede ejecutar este procedimiento almacenado.

sp_droplogin nombre de usuario

Usuarios de bases de datos: Creación de usuarios de bases de datos

Crear usuarios de bases de datos significa, en realidad, habilitar identificadores de inicio de sesión para su acceso a una o varias bases de datos.
En función del modo de autenticación del usuario, esto significa habilitar el acceso a una cuenta o grupo de Windows o a un identificador de acceso de SQL Server.

A través del Administrador Corporativo

Posicionados sobre la base de datos, abrimos la carpeta seguridad y debajo de esta aparece la de usuarios, sobre esta carpeta botón derecho del ratón Nuevo usuario.

Esto abre un cuadro de diálogo con varias pestañas. En la primera podremos especificar:
- Tipo de Usuario,  a escoger entre los que ya están dados de alta para el acceso al servidor.
- Nombre de usuario. El nombre que deseamos asignar al usuario en la base de datos.
- Nombre de inicio de sesión. Que debe elegir entre los que ya estén creados
- Esquema predeterminado. También se elegirá entre los ya creados.

 

En el resto de pestañas podemos elegir las funciones de la base de datos para las que se da acceso o a las que se desea que pertenezca.

Añadiendo usuarios mediante comandos SQL

El procedimiento almacenado de SQL Server  permite habilitar a un identificador de acceso o a una cuenta Windows para su acceso a una base de datos:

sp_grantdbaccess o sp_adduser.
sp_grantdbaccess id inicio sesión [,’nombre de usuario’]

id inicio sesión es un identificador de inicio de SQL Server o una cuenta de Windows. Si se omite el nombre de usuario, el usuario en la base de datos utilizará la misma denominación que el identificador de inicio de sesión.

Eliminación de usuarios mediante el Administrador Corporativo

Para eliminar un usuario en el Administrador Corporativo basta con hacer clic en el botón derecho del ratón situados encima del usuario y eliminar.

 

Eliminación de usuarios mediante comandos

Para ello se utiliza el método sp_revokedbaccess que elimina un usuario de una base de datos.

sp_revokedbaccess 'usuario'

No pueden eliminarse los usuarios dbo ni el information_schema ni el guest en la base  de datos master  ni tempdb.

Obteniendo información sobre usuarios

Es posible obtener información sobre los usuarios existentes en una base de datos mediante el procedimiento almacenado sp_helpuser.

sp_helpuser [ ‘nombre de usuario’ ]

Si se omite el nombre del usuario se obtendrá información sobre todos los usuarios de la base de datos.

Roles de base de datos (database roles)

Es posible agrupar a los usuarios para otorgarles conjuntamente permisos de acceso a base de datos utilizando los Roles de base de datos. Un rol de base de datos representa un tipo de usuarios, desde el punto de vista de las labores que pueden realizar, al que se asignarán los permisos necesarios para llevar a cabo esa tarea.
Este Procedimiento simplifica la gestión al administrador de los aspectos de seguridad, pues permite otorgar conjuntamente permisos a usuarios de perfil similar. Lo más lógico es crear primero los roles de base de datos, y añadir posteriormente los usuarios particulares que se adapten a ese grupo. Existen roles de bases de datos predefinidos.

Gestión de roles de bases de datos: Mediante el Administrador Corporativo

En el Administrador Corporativo, para crear un rol de base a datos de usuario deberemos acceder a la base de datos en cuestión y desplegando la carpeta Seguridad y debajo de ella nos colocamos en la carpeta Roles y con el botón derecho del ratón elegimos Nuevo, esto nos da la opción de elegir entre nuevo rol de base de datos o de aplicación. Elegimos rol de base de datos.

Esto nos abre un cuadro de diálogo con tres pestañas.

En el cuadro de diálogo podremos especificar
El nombre del rol, el propietario y los esquemas propiedad del rol.
En las otras pestañas al igual que hicimos anteriormente podemos elegir los elementos de la base de datos a los que se proporcionan permisos. Los esquemas a los que pertenecen, y el tipo de permiso que se proporciona.

Creación de Roles de bases de datos mediante comandos SQL

El procedimiento almacenado que permite añadir un nuevo rol una base de datos es sp_addrole.

sp_addrole 'nombre de rol’ [ 'propietario']

El parámetro propietario permite especificar el usuario de la base de datos propietaria del rol que podrá, por tanto, modificarla. Por defecto será dbo.

sp_addrole 'db_owner’

Adición de usuarios a un rol de servidor

El procedimiento almacenado que permite añadir usuarios a un rol de base de datos es sp_addrolemember:

sp_addrolemember 'nombre función'  [, id de inicio de sesión]

Un ejemplo de uso: sp_addrolemember 'Especialistas', 'Conchi'

Información sobre las funciones existentes

Es posible obtener información sobre los roles existentes mediante el procedimiento almacenado sp_helprole.

sp_helprole  [ nombre de función ]

Planes de ejecución con SQL Server I

Introducción

A la hora de gestionar las bases de datos el DBA  siempre se plantea el mismo tipo de preguntas:

• ¿Por qué es esta consulta está funcionando tan lentamente?

• Está SQL Server utilizando el índice que he creado?

• ¿Por qué SQL Server no utiliza el índice que he creado?

• ¿Por qué esta consulta se ejecuta más rápido que esta otra?

• Y así sucesivamente.

La respuesta correcta probablemente sea diferente en cada caso, pero siempre hay que hacerse la misma pregunta: ¿He consultado el plan de ejecución?
Los planes de ejecución muestran lo que está pasando detrás de cada actuación de SQL Server. Esto proporciona una gran cantidad de información acerca de cómo SQL Server está ejecutando las consultas, incluyendo los siguientes puntos.