Framework de administración declarativa en SQL Server 2008R2

Introducción

Hoy en día, los entornos de bases de datos son bastante complejos. Desafortunadamente, las nuevas tendencias en principio no simplifican el problema.

• La gran cantidad de nuevas características que se introdujeron en SQL Server 2005, aumentó radicalmente la complejidad del producto, haciendo que los administradores de bases de datos, tuvieran que conocer una gran cantidad de información para poder extraer el máximo partido al servidor de base de datos.
• Aumentan el número de consolidaciones de servidores y centros de datos.
• Proliferación de bases de datos remotas y embebidas, teniendo entonces una gran importancia la sincronización y seguimiento de cambios.

Afortunadamente, desde SQL Server 2008 aparecen un conjunto de nuevas características precisamente destinadas a simplificarnos la problemática de la administración de bases de datos y entornos de bases de datos:

• Framework de administración declarativa
• Resource Governor
• Auditoria
• Eventos Extendidos
• Colección de datos de rendimiento

En este artículo nos vamos a centrar en el Framework de Administración Declarativa.

¿Cómo puede ayudarnos el framework de administración declarativa?

El framework de administración declarativa, cuyas siglas son DMF, nos puede ayudar a manejar y resolver tres grandes tipos de situaciones:

• Gestión por intención
  Las capacidades de SQL Server van en aumento y esto trae consigo un aumento de la complejidad de administración. Un administrador de bases de datos necesita más conocimiento, puesto que aumenta la sintaxis T-SQL, la guía de administración de SQL Server que ha de conocerse,…
  El framework de administración declarativa soluciona esto permitiéndonos realizar una gestión por intención. Esto quiere decir que vamos a poder definirnos políticas que reaccionen a las intenciones de los usuarios y apliquen reacciones a las mismas, de forma que podamos definirnos unas pautas de buenas prácticas y que automáticamente el sistema obligue al usuario a seguirlas, o se nos notifique si alguien intenta realizar alguna acción que consideremos poco apropiada.
• Escalabilidad “Uno a muchos”
  El aumento del número de consolidación de servidores y los centros de datos traen consigo que el administrador de base de datos tenga cada vez más servidores que administrar.
  El framework de administración declarativa permite dar solución a este problema de complejidad de administración, permitiendo lo que se conoce como escalabilidad “uno a muchos”. Gracias a esto, se consigue que definamos grupos de servidores que se administren como si fuera uno solo, de forma que podamos aplicar políticas de comportamiento y configuración similar a un conjunto de servidores de forma simultánea.
• Control y monitorización de cambios
  La proliferación de bases de datos embebidas y remotas incrementa la necesidad de realizar un control y una monitorización de los cambios que dichos sistemas producen. Ese control requiere que un DBA deba estar al tanto de cualquier problema que pudiera ocurrir para poder repararlo.
  El framework de administración declarativa permite realizar una automatización del control y la monitorización de los cambios, de forma que podamos tanto prevenir los mismos, como notificar en el caso de que se produzcan.

El framework de administración declarativa, a grandes rasgos, se basa en los siguientes 3 conceptos:

• Administración de políticas
  Se trata de políticas diseñadas por los administradores que se encargan de definir comportamientos ante determinadas circunstancias.
• Administración explícita
  Podemos realizar administración que verifique políticas en servidores objetivo, pero también podemos forzar a que se cumplan dichas políticas en esos mismos servidores.
• Administración automática
  Podemos prevenir la violación de políticas, verificar los cambios de las políticas y verificar políticas mediante una serie de reglas.

Términos y conceptos del framework de administración declarativa

Antes de que veamos ejemplos prácticos donde aplicar soluciones mediante DMF, vamos a definir los conceptos fundamentales que se deben conocer a priori

• Objetivo
  Un objetivo es una entidad administrada por el framework. Todos los objetivos de la instancia de un servidor, conforman una jerarquía. Gracias a ello podemos realizar filtros de entidades de forma que podemos aplicar políticas a todas las tablas de un determinado esquema, por ejemplo.
  Ejemplos de objetivos pueden ser la instancia de SQL Server, la base de datos, tablas, índices,…
• Faceta
  Se trata de un conjunto de propiedades lógicas que modelan el comportamiento o características de ciertos tipos de objetivos. El número y características de dichas propiedades están incluidas en la faceta y solo pueden añadirse o eliminarse por el creador de la misma.
  Existen facetas que son dependientes de la versión del motor de base de datos; por ejemplo, facetas sobre Database Mail solo se podrán aplicar a sistemas SQL Server 2005 y posteriores.
  Un objetivo puede implementar una o muchas facetas y una faceta pueda implementarse en uno o muchos objetivos.
  Por defecto, SQL Server trae preconfiguradas todo un abanico de facetas, catalogadas según el comportamiento o características del objetivo. Es por ello que podemos encontrar facetas como “Database”, “Server Configuration”, “Resource Governor”, “Login”,… que contienen toda una serie de propiedades enfocadas a dicho objetivo. Podemos acceder a ellas bajo el nodo “Management/Policy Management/Facets”
  

  

• Condición
  Una expresión booleana que especifica el conjunto de valores permitidos por un objetivo en relación a una faceta.
  Una condición podría indicar por ejemplo que Database Mail ha de estar habilitado, o que el nombre de una vista debe empezar por “vW_”.
  Podemos acceder a ellas mediante el nodo “Management/Policy Management/Conditions”
  

  

• Política
  Es un conjunto formado por una condición y su comportamiento esperado; por ejemplo, el modo de ejecución, los filtros del objetivo y la periodicidad de comprobación.
  Una política solo puede contener una condición y su estado puede ser habilitado o deshabilitado.
  Podemos acceder a ellas mediante el nodo “Management/Policy Management/Policy”
  

  

• Categoría de política
  Se trata de una categoría definida por el usuario con el único fin de administrar políticas. Utilizando categorías, los usuarios pueden clasificar políticas. Una política solo puede pertenecer a una única categoría.
  Todas las bases de datos por defecto están subscritas a la categoría por defecto.
  

  

• Política efectiva
  Las políticas efectivas de un objetivo son aquellas políticas que gobiernan dicho objetivo. Una política es efectiva en relación a un objetivo solo si todas las siguientes condiciones se satisfacen:
  - La política está habilitada
  - El objetivo pertenece al conjunto de objetivos de la política
  - El objetivo o uno de sus ancestros están subscritos al grupo de políticas que contiene dicha política.

Modos de ejecución de políticas permitidos

Los modos de ejecución aplicables a políticas vienen determinados por las características de las facetas que se usan en la política. Los modos de ejecución permitidos son los siguientes:

• Bajo demanda
  Este modo evalúa la política directamente por especificación del usuario.
• Intentos de modificación – Prevención
  Este modo utiliza trigers DDL para prevenir las violaciones de políticas
• Intentos de modificación – Registro
  Este modo utiliza la notificación de eventos para evaluar una política cuando un cambio ocurre y loga una violación de política
• En planificación
  Este modo utiliza el agente de SQL Server para evaluar mediante un trabajo, una política de forma periódica.

Administración de políticas

Las políticas se crean y mantienen mediante el cliente de SQL Server Management Studio.
Mediante dicha herramienta, podemos:

1. Crear políticas utilizando el menú Archivo/Nuevo y salvarlas a disco
2. Importar políticas desde archivos.
3. Exportar políticas a archivos.
4. Seleccionar las propiedades que queremos configurar de una faceta
5. Definir una condición que especifique el estado de una faceta
6. Definir una política que contenga las condiciones, filtros de objetivos y el modo de ejecución
7. Comprobar qué instancia cumple la política.

Para aquellas políticas que se están violando, se nos avisa y se permite forzar su cumplimiento en el caso de ser posible.

NOTA: La administración de políticas solo es posible a partir de SSMS 2008. No es posible realizarlo desde el entorno de SSMA 2005

Almacenamiento de políticas

Las políticas se almacenan en la base de datos msdb por lo que después de realizar un cambio, es recomendable realizar una copia de seguridad de la base de datos msdb.

SQL Server 2008 incluye políticas de monitorización de instancias SQL Server. Por defecto dichas políticas no vienen instaladas en el motor de base de datos, pero se encuentran en la ruta de instalación, que por defecto es:

C:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033

NOTA: SQL Server 2008 R2 no trae dichos ejemplos de políticas

Existe un histórico de evaluación de políticas en la base de datos msdb, pero solo solo contempla aquellas políticas propias de la instancia a la que pertenece msdb. No se contemplan políticas aplicadas a otras instancias del motor ni aplicadas a Reporting Services ni a Analysis Services.

Además, en la base de datos msdb, existen toda una serie de catálogos y vistas del sistema con la configuración de DMF. Estos catálogos se encuentran con los nombres dbo.syspolicy_* y entre ellos podemos encontrar dbo.syspolicy_conditions, dbo.syspolicy_management_facets,… con información que puede resultarnos útil para consultar de forma relacional.

Importar y exportar políticas

Es posible exportar e importar políticas a ficheros externos. Si lo que queremos es importar una política, podemos utilizar SSMS y aplicando botón derecho sobre el nodo Management/Policy Management/Policies, clickear sobre “Import Policy…”

Lo que está esperando importar, es un fichero con la configuración de la política en XML, que previamente tendremos que haber exportado.

Para exportar una política a XML, podemos hacerlo fácilmente pulsando botón derecho sobre ella y seleccionando “Export Policy…”, indicando la ubicación donde deseamos exportar su configuración.

El hecho de poder importar y exportar políticas nos facilita el poder definir las mismas políticas sobre diferentes instancias y servidores de SQL Server.

Crear Alertas de notificación ante violación de políticas

Cuando una violación de política se produce, en cualquier modo de ejecución, se guarda un mensaje descriptivo en el log de eventos. Lo recomendable es que cuando esto suceda, además nos creemos una alerta que nos detecte el mensaje y realice una acción.

Las alertas han de detectar los siguientes números de mensaje:

Existen una serie de consideraciones a tener en cuenta cuando se configuran las alertas:

- Puesto que las alertas solo se lanzan para políticas habilitadas, las políticas ejecutadas bajo demanda, no lanzan alertas.
- Si vas a enviar correos, asegúrate de que tienes correctamente configurado Database Mail
- Las políticas ejecutadas bajo demanda se ejecutan en el contexto del usuario que la lanza. Este usuario ha de tener permisos ALTER TRACE o ser miembro del rol sysadmin para que se pueda escribir en el log de errores un posible error de ejecución.
- Las políticas ejecutadas automáticamente se ejecutan en el contexto del rol sysadmin por lo que no existe problema de permisos a la hora de escribir en el log de errores ni de lanzar alertas.

Ejemplos de soluciones mediante el framework de administración declarativa

Los siguientes escenarios son claros ejemplos de situaciones resolubles mediante el framework de administración declarativa:

- Una compañía prohíbe habilitar Database Mail o SQL Mail.
- La base de datos AdventureWorks, por convenio ha de cumplir que todos sus procedimientos almacenados comiencen con el prefijo “AW_”.
NOTA: No tienen por qué ser escenarios recomendables en entornos productivos, tómense a modo de ejemplo

Política a nivel de base de datosPolítica a nivel de base de datos

En el primer problema a modelar planteado, lo que haremos será crearnos una condición que valide que una cadena de texto comience por ‘AW_’

1. Crear una nueva condición.
2. Seleccionar la faceta “Multipart Name”
3. Indicar que la propiedad @name sea “like” ‘AW_%’

NOTA: Seleccionamos la faceta “Multipart Name” en lugar de “stored procedure”, puesto que podríamos reutilizar esta condición mas adelante para otros objetos, no solo para stored procedures

Crear una política que valide que los nombres de procedimientos almacenados cumplan la condición que queremos

1. Crear una nueva política
2. Seleccionar la condición que hemos creado (estará sobre la categoría “Multipart Name”)
3. Seleccionar que queremos que el objetivo sea Cualquier procedimiento almacenado
4. Indicar que queremos que sea sobre la BBDD AdventureWorks, para ello nos tendremos que crear una nueva condición pinchando sobre “New Condition”.



5. Indicar en la condición, que será la BBDD con nombre AdventureWorks (le pondremos el nombre aw, por ejemplo)



6. Indicar que el modo de ejecución va a ser “On change – Prevent”, para que no nos deje crear procedimientos almacenados sin cumplir la nomenclatura.



En este caso, una vez definida la política, nos avisará si la intentamos validar de qué procedimientos almacenados la infringen, y los nuevos procedimientos almacenados no nos los dejará crear, indicándonos el por qué.

De esta forma, lo que ocurriría si intentásemos ahora crear un procedimiento almacenado en la BBDD Adventureworks que no comenzara por AW_ obtendríamos la siguiente excepción:

Política a nivel de instancia

Para el primer escenario a modelar, lo que haremos será crear una nueva condición que chequee el valor de SQL Mail y Database Mail como “false”

1. Boton derecho sobre “Management/ Policy Management/ Conditions” y “New Condition…”
2. Seleccionar faceta “Surface Area Configuration”, que es donde se encuentran estas propiedades de configuración.

Seleccionar @SqlMailEnabled y @DatabaseMailEnabled y seleccionarlos como valor “false”

Posteriormente, crearemos una policy que haga uso de ella y la configuraremos para que revierta la configuración

1. Boton derecho sobre “Management/ Policy Management/ Policies” y “New Policy…”
2. Seleccionar la condición que hemos creado (recordemos que se encontrará bajo la faceta “Surface Area Configuration”
3. Configurar el modo de ejecución para que cuando se viole la política, se registre.
4. Es importante el nombre de la política, en este caso le hemos puesto “correo fuera”

En este caso no podemos elegir el modo de ejecución “On Change – Prevent” porque las facetas de Surface Area Configuration no lo permiten, pero podemos simularlo como veremos más adelante. Para ello, lo primero seleccionar “On Change – Log Only” de forma que envie un evento de violación de política cuando sea realizada.

Para conseguir el efecto que si alguien cambia alguna propiedad que viole la política, esta se vuelva a configurar en su estado correcto, crearemos una alerta. Dicha alerta nos servirá para que restaure el estado a habilitado/deshabilitado de la condición que haya sido violada (esto lo hacemos así porque no podemos prevenir cambios a nivel de instancia

1. Boton derecho sobre “SQL Server Agent/ Alerts” y “New Alert…”
2. Seleccionar que queremos capturar “SQL Server event alert”
3. Indicar que el Error Number es el 34053 (recordad el apartado de alertas de modificación)
4. Indicar que queremos que la alerta solo se lance cuando tengamos el texto “correo fuera” que es el nombre de la política (si le poneis otro nombre, actualizar este campo)



5. Luego en la pestaña “Response” indicamos si queremos notificar a alguien y lo más importante, el job que restaure el estado

El código del job “restablecer estado config correo” podria ser este:

NOTA: Obviamente hay que introducir más lógica para las casuísticas de qué característica ha sido la modificada, pero se deja a cuenta del lector

Lo que hemos logrado con esta alerta ha sido que cuando se viole la política, la alerta programada sobre ella dé lugar a que el procedimiento almacenado, restaure el valor de SQL Mail y Database Mail a deshabilitado. Es una forma eficaz de simular un modo de ejecución “On change – Prevent”, que esta faceta no permite seleccionar por no existir posibilidad de crearse internamente un trigger DDL. Obviamente en este caso no se nos previene el cambio como en el ejemplo anterior, pero pese a permitirse, el evento capturado por la alerta lo restaura a su estado original.

Conclusión

El framework de administración declarativa es una excelente inversión de conocimientos para un DBA, puesto que obtendrá beneficio directo desde el punto de vista de la manejabilidad y estabilidad del sistema, ya que podrá conseguir que el sistema se comporte bajo los parámetros y límites que el mismo establezca tanto para la instancia como para el parque de instancias de SQL Server 2008 R2.

Como se ha podido ver por los dos ejemplos propuestos, la sencillez de definición y las posibilidades de aplicación, la hacen una característica muy poderosa que debería tenerse presente desde el mismo momento que se trabaja con una instancia SQL Server 2008 R2 en producción.

Además, recordar que las posibilidades que nos ofrece el framework de administración declarativa no se quedan a nivel de un único servidor. Esto unido a la posibilidad de validaciones de múltiples servidores nos dan la posibilidad incluso de crearnos un conjunto de políticas distribuidas y validadas contra todo nuestro conjunto de servidores (incluso en versiones 2005 y 2000 en función de la política deseada).

Se anima al lector a que continúe el aprendizaje a través de la ayuda oficial de SQL Server 2008 R2: http://msdn.microsoft.com/es-es/library/bb510667(v=SQL.105).aspx