Capitulo 9 - Lección 3: Seguimiento de cambios en los datos

Si usted trabajó en las versiones anteriores de SQL Server, es posible que han construido soluciones complejas que incluyen la adición de columnas de tipo timestamp, agregando factores desencadenantes, e incluso la configuración de la replicación para controlar los cambios en su base de datos. En SQL Server 2008, Microsoft introdujo el control de cambios y captura de datos modificados (CDC) para ayudar a responder preguntas sobre las variaciones de datos en una base de datos. Además, SQL Server 2008 Enterprise Edition incluye SQL Server Audit, que presta servicios de auditoría automática de una instancia de SQL Server. Además de ser capaz de auditoría de servidor y base de datos de eventos de nivel, de Auditoría de SQL Server proporciona una herramienta para registrar no sólo cuando se modifica una tabla (INSERT, UPDATE, DELETE), sino también cuando se leen los datos de una tabla (SELECT) .

Comparando el seguimiento de cambios para la captura de datos
Al aplicar un método de seguimiento de los cambios en su base de datos, tendrá que decidir entre el control de cambios y los métodos de los CDC. Cada uno de estos métodos permite determinar si ha ocurrido un cambio en los datos. En las secciones siguientes se comparan las ventajas de cada método y describir cómo funciona cada método.

Change Tracking - Seguimiento de Datos (Más información AQUI):
El seguimiento de cambios tiene las siguientes funciones:
  • Proporciona la funcionalidad con instrucciones DML.
  • Pueden responder a preguntas tales como:
    • ¿Qué filas de la tabla han cambiado?
    • ¿Qué columnas han cambiado?
    • ¿Una fila determinada sido actualizada?
    • ¿Hizo un INSERT, UPDATE, o DELETE?
  • Opera de forma sincrónica para proporcionar información de los cambios inmediatamente.
  • Proporciona una sobrecarga de almacenamiento inferiores a los CDC.
  • Proporciona un mecanismo de limpieza integrado.
  • Utiliza la confirmación de la transacción para determinar el orden de los cambios.
  • Trabaja sin que requiera cambios en el esquema de la tabla o desencadenantes adicionales.
  • Debe estar activado en el nivel de base de datos mediante ALTER DATABASE.
Change Data Capture - Captura de datos modificados
La captura de datos tiene las siguientes funciones:
  • Proporciona la funcionalidad con instrucciones DML.
  • Puede contestar las mismas preguntas que el control de cambios, así como los siguientes:
    • ¿Cuáles fueron los cambios intermedios realizados a los datos desde la última sincronización?
    • ¿Cuántas veces se ha actualizado una fila desde la última sincronización?
  • Utiliza las tablas de cambio para registrar las modificaciones de datos de columnas y los metadatos que se requiere para aplicar los cambios a un entorno de destino.La estructura de columnas de la tabla de cambio refleja la estructura de la tabla de origen.
  • Utiliza el registro de transacciones como entrada para la información agregada a las tablas de cambios.
  • Funciona de forma asincrónica y los cambios sólo están disponibles después de que la sentencia DML se ha completado.
  • Proporciona funciones con valores de tabla para permitir el acceso a los datos de las tablas de cambios.
  • Requiere ser habilitar en la base de datos usando sys.sp_cdc_enable_db.
  • Requiere SQL Server 2008 Enterprise, Developer o Evaluation Edition.
La configuración del seguimiento de cambios
Antes de configurar el seguimiento de cambios en las tablas, debe habilitar el seguimiento de cambios en la base de datos. Puede usar tanto SSMS o ALTER DATABASE para permitir el seguimiento de cambios en su base de datos.
Nivel de compatibilidad mínimo: Antes de configurar el seguimiento de cambios para una determinada base de datos, debe establecer el nivel de compatibilidad de base de datos para SQLServer 2005 (90) o más alta para esa base de datos.
Habilitando Change Tracking para una base de datos
Cuando se habilita el seguimiento de cambios de una base de datos, puede configurar las siguientes opciones:
  • Change Tracking: Se establece en True o False (por defecto False) para activar o desactivar el control de cambios.
  • Retention Period: Establece en un valor numérico que representa la cantidad mínima de tiempo que los cambios se mantienen. El período de retención por defecto es 2.
  • Retention Period Units: Establecer días (por defecto), horas o minutos.
  • Auto CleanUp: ON (por defecto) o OFF para activar o desactivar el proceso que elimina la información obsoleta del seguimiento de cambios.
Habilitar Clean-Up automático restablece valores predeterminados: Cada vez que el Auto Clean-Up opción está establecida en ON, el período de retención y unidades de retención de cada periodo se restablece al valor predeterminado de 2 y 2 días, respectivamente.
Para habilitar el seguimiento de cambios con SSMS, debe realizar los siguientes pasos:
  1. En el Explorador de objetos, expanda la carpeta Bases de datos, haga clic en la base de datos donde desea utilizar el control de cambios, y seleccione Propiedades.
  2. En el panel Seleccionar la pestaña Propiedades de la base, haga clic en Change Tracking.
  3. En la página de Change Tracking, configurar las opciones apropiadas y haga clic en Aceptar (Más información AQUI).
La figura siguiente muestra el cambio de configuración de seguimiento de la base de datos AdventureWorks2008. El período de retención se encuentra a siete días. Si la base de datos de Analysis Services de ensayo está sincronizada con la base de datos AdventureWorks2008 una vez cada tres días, el período de retención permite que la configuración de una sincronización pueda perderse y repararse antes de los datos se eliminan de las tablas de cambios. En este escenario, se requiere más espacio en las tablas de cambios que si se configura un período de retención más cortos.
image
Se puede habilitar el seguimiento de cambios ejecutando el comando ALTER DATABASE. El siguiente código incluye la sintaxis completa del comando ALTER DATABASE para las opciones relacionadas con el control de cambios:
image
Puede ejecutar el siguiente comando para habilitar el seguimiento de cambios en la base de datos AdventureWorksDW2008. Los cambios se mantienen durante al menos siete días y el proceso de limpieza automática elimina la información de seguimiento de cambios que se creó hace más de siete días:
ALTER DATABASE AdventureWorksDW2008
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON)

Habilitación de Change Tracking para una tabla
Una vez que el control de cambios ha sido habilitada en la base de datos, el comando ALTER TABLE se utiliza para habilitar el seguimiento de cambios en las tablas individuales que desea realizar un seguimiento. Sólo hay dos argumentos en el comando ALTER TABLE que afectan el control de cambios:
  • Change_Tracking: Setear en ENABLE (por defecto) o DISABLE para determinar el estado de seguimiento de cambios en la tabla.
  • Track_Columns_Updated: Establecido en ON o OFF (por defecto) para determinar si desea mantener una lista de las columnas que se actualizan cuando el comando UPDATE se ejecuta en la tabla.
Para configurar el seguimiento de cambios con SSMS, seleccione la página Change Tracking de la ventana Propiedades de la tabla para la tabla en la que desea seguir los cambios.
Puede ejecutar el siguiente código para permitir el control de cambios, incluyendo información sobre los cambios a las columnas individuales que se modifican en la tabla de DimEmployee en la base de datos AdventureWorksDW2008:
ALTER TABLE DimEmployee
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);
image

Deshabilitar Change Tracking
Debe deshabilitar el seguimiento de cambios en cada tabla utilizando cualquiera SSMS o ALTER TABLE antes de que pueda desactivar el control de cambios sobre la base de datos.
Para determinar las tablas en la base de datos AdventureWorksDW2008 que tienen seguimiento de cambios habilitado, se puede consultar la vista de catálogosys.change_tracking_tables como se muestra en la figura siguiente (Más información AQUI).
image
Para deshabilitar el seguimiento de cambios en la tabla DimEmployee, ejecute el siguiente código:
ALTER TABLE DimEmployee
DISABLE CHANGE_TRACKING;
Una vez que haya desactivado el seguimiento de cambios en todas las tablas en la base de datos, puede desactivar el seguimiento de cambios en la base de datos mediante el uso de SSMS o el comando ALTER DATABASE.
El siguiente comando deshabilita el seguimiento de cambios en la base de datosAdventureWorksDW2008:
ALTER DATABASE AdventureWorksDW2008
SET CHANGE_TRACKING = OFF

Entender los efectos adicionales de Change Tracking
Los siguientes comandos pueden comportarse de manera diferente cuando el seguimiento de cambios está habilitado:
  • TRUNCATE TABLE: Con éxito pero los registros borrados no se siguen y la versión válida mínima es actualizada, lo que requiere que las aplicaciones sean reiniciadas antes de poder reanudar la sincronización.
  • DROP INDEX o ALTER INDEX DISABLE: Falla sólo si la referencia al índice de que aplica la restricción es PRIMARY KEY.
  • DROP TABLE: Tiene éxito y elimina toda la información de seguimiento de cambios relativos a la tabla eliminada.
  • ALTER TABLE DROP CONSTRAINT: Fracasa si intenta quitar la restricción PRIMARY KEY. Debe deshabilitar el control de cambios antes de que pueda quitar la restricción de PRIMARY KEY en la Tabla.
  • ALTER TABLE DROP COLUMN: Tiene éxito, siempre y cuando la columna no forma parte de la restricción PRIMARY KEY. A menos que la aplicación está programada para manejar la columna eliminada, los datos de la columna eliminada aún podría ser devueltos como parte de la información de seguimiento de cambios.
  • ALTER TABLE ADD COLUMN: Tiene éxito y comienza a seguir los cambios que se realicen en la nueva columna.
  • ALTER TABLE ALTER COLUMN: Exitoso, pero los cambios de tipos de datos en las columnas de clave no primarias no son seguidos.
  • ALTER TABLE SWITCH: Falla si uno o ambas tablas tiene seguimiento de cambios habilitado.
Trabajar con Change Tracking
Cuando se están desarrollando aplicaciones para sincronizar los datos utilizando el control de cambios, puede utilizar varios comandos T-SQL y vistas de catálogo que se proporcionan en SQL Server 2008.
Las siguientes funciones permiten obtener información de los cambios y administrar el entorno de control de cambios:
  • CHANGETABLE: Se utiliza para devolver toda la información para cambiar una tabla mediante la opción de CHANGES , o cambiar la información de una fila específica mediante el uso de la opción VERSION. (Más información AQUI)
  • CHANGE_TRACKING_MIN_VALID_VERSION: Devuelve la versión mínima que sea válida para su uso en la obtención de control de cambios cuando se ejecuta la función CHANGETABLE. Debe especificar el ID de objeto de tabla para la tabla de seguimiento como un argumento para el comando. (Más información AQUI)
  • CHANGE_TRACKING_CURRENT_VERSION: Obtiene la versión que está asociada con la última transacción confirmada. Usted puede utilizar esta información de la versión cuando se ejecuta la función CHANGETABLE. (Más información AQUI)
  • CHANGE_TRACKING_IS_COLUMN_IN_MASK: Interpreta el valor SYS_CHANGE_COLUMNS devuelto por el  CHANGETABLE(CHANGES ...) La función permite a su aplicación determinar si la columna que figuran en el argumento está incluido en los valores devueltos para SYS_CHANGE_COLUMNS. (Más información AQUI)
  • WITH CHANGE_TRACKING_CONTEXT: Le permite configurar la información de contexto en una columna varbinary (128) con la información de seguimiento de cambios cuando se producen cambios.Esto le permite determinar qué aplicación se inició una actualización. (Más información AQUI)
CHANGETABLE: Funciones de salida y ejemplos (Más información AQUI)
La función CHANGETABLE (CHANGES ) muestra las siguientes columnas cuando se consulta:
  • SYS_CHANGE_VERSION: Muestra la versión del cambio más reciente en la fila.
  • SYS_CHANGE_CREATION_VERSION: Muestra el valor de la versión de la última operación INSERT.
  • SYS_CHANGE_OPERATION: muestra U para UPDATE, D para DELETE, o I para INSERT.
  • SYS_CHANGE_COLUMNS: Muestra las columnas que han cambiado desde la versión básica. Esta columna contiene NULL en las siguientes circunstancias:
    • El argumento Track_Columns_Updated está en OFF.
    • La operación es una inserción o borrado.
    • Todas las columnas que no son de clave principal se actualizaron en la misma operación.
  • SYS_CHANGE_CONTEXT: Muestra un contexto para la actualización si está definida. Configurar el contexto como parte de la INSERT, UPDATE, o DELETE con la cláusula WITH.
  • <primary key column value>: Muestra el valor de clave principal de la fila.
Para las consultas de ejemplo siguiente, el seguimiento de cambios se ha habilitado en la base de datos AdventureWorksDW2008 y la tabla DimEmployee en la base de datos AdventureWorksDW2008. Para empezar, el argumento Track_Columns_Updated está en OFF.
Una nueva fila se inserta en la tabla DimEmployee para un empleado llamado Jonathan Haas. Su clave de empleado es de 299. La figura siguiente muestra el cambio mediante el uso de la función CHANGETABLE. Observe que el SYS_CHANGE_VERSION es 1 y el SYS_CHANGE_OPERATIONque es para INSERT.
image

Para el ejemplo siguiente, dos registros se actualizan en la tabla DimEmployee.Jonathan Haas, del empleado 299, se convirtió en un vendedor (la columna SalesPersonFlag se establece en 1). Syed Abbas, empleado 294, ya no es un vendedor, por lo tanto, la columna SalesPersonFlag se establece en 0. La siguiente imagen muestra los resultados de la CHANGETABLE (CHANGES), función que muestra todas las actualizaciones de la primera captura de este punto. Observe que el SYS_CHANGE_VERSION se ha incrementado a 2 para el EmployeeKey 299, pero la operación sigue siendo I. La fila no ha sido sincronizado desde la inserción inicial, por lo que un INSERT todavía tiene que ocurrir cuando la tabla se sincroniza, pero los datos nuevos en SalesPersonFlag = 1 se incluye en la sincronización. Para los empleados 294 la SYS_CHANGE_VERSION está ajustado a 3 (de cada actualización de la tabla de incrementos), el SYS_CHANGE_OPERATION es de U de actualización, pero la SYS_CHANGE_CREATION_VERSION se establece en NULL, porque esta fila se inserto antes de seguimiento de cambios se ha habilitado.

image

La muestra final para este escenario incluye la supresión del nuevo usuario,Jonathan Haas. Observe en la imagen siguiente que para la fila con EmployeeKey 299 de la SYS_CHANGE_VERSION ha incrementado en 4 y el SYS_CHANGE_OPERATION es ahora D para borrar.

image

En el segundo conjunto de imágenes, un servidor limpio se usa y el seguimiento de cambios está habilitado en la base de datos, una vez más. Cuando el control de cambios está habilitado en la tabla DimEmployee, el argumento TRACK_COLUMNS_UPDATED está en ON. Una vez más, una fila por JonathanHaas, ahora con un EmployeeKey de 297, se inserta en la base de datos y la consulta en la figura siguiente se ejecuta. Tenga en cuenta que los resultados son los mismos que con la primera serie de consultas.

image

Una vez más, para la consulta siguiente, dos registros se actualizan en la tabla DimEmployee. Jonathan Haas, del empleado 299, se convirtió en un vendedor (la columna SalesPersonFlag se establece en 1). Syed Abbas, empleado 294, ya no es un vendedor, por lo tanto, la columna SalesPersonFlag se establece en 0. La figura siguiente muestra los resultados de la CHANGETABLE (CHANGES), la función que muestra todas las actualizaciones de la primera captura de este punto. Observe que el SYS_CHANGE_COLUMNS incluye un valor binario que representa la columna que se ha cambiado. Usted debe utilizar la función CHANGE_TRACKING_IS_COLUMN_IN_MASK cuando es necesario interpretar esta información.

image
Para el último ejemplo, que se muestra en la figura posterior, vemos una vez más que el argumento columnas TRACK_COLUMNS_UPDATED no tiene ningún efecto sobre la instrucción DELETE.
image

Gestionar Change Tracking
Las vistas de catálogo siguientes muestran el seguimiento de cambios de información de configuración:
  • sys.change_tracking_databases: Muestra la siguiente información: (Más información AQUI)
    • Database_id: Un campo entero que representa el identificador único de base de datos dentro de la instancia de SQL Server para bases de datos donde se ha habilitado el seguimiento de cambios.
    • Is_auto_cleanup_on: Un campo de bit con valor 0 para off y 1 para on.
    • Retention_period: Un campo entero que representa la cantidad mínima de tiempo que los cambios realizados se guardan antes de que se eliminen en el proceso de auto-limpieza.
    • Retention_period_units_desc: Un campo nvarchar especificando Minutos, Horas o Días (“Minutes”, “Hours”, o “Days”).
    • Retention_period_unit: Un campo tinyint pone a 1 para minutos, 2 para horas o 3 para días.
  • sys.change_tracking_tables: Muestra la siguiente información: (Más información AQUI)
    • Object_id: Un campo entero que representa el ID único de la tabla para las tablas de la base de datos que tienen un diario de cambios. Una tabla donde se encuentra el control de cambios desactivado todavía puede tener un diario de cambios.
    • Is_track_columns_updated_on: Un campo de bit con valor 0 para off y 1 para on.
    • Begin_version: Un campo bigint que contiene la versión de la base de datos cuando se inició el seguimiento de cambios de la tabla.
    • Cleanup_version: Un campo bigint que contiene el número de versión cuando, antes de esta versión, los datos pueden haber sido eliminados por el proceso de auto-limpieza.
    • Min_valid_version: Un campo bigint que contiene el número mínimo de versión válida de seguimiento de cambios para cada tabla.
Configurando CDC
Para permitir que los CDC en una base de datos, se ejecuta el procedimiento almacenado del sistema sys.sp_cdc_enable_db.
El siguiente comando permite a los CDC sobre la base de datos AdventureWorksDW2008:
USE AdventureWorksDW2008R2;
GO
EXECUTE sys.sp_cdc_enable_db;
GO
Cuando habilita CDC para una base, el sistema establece la columna is_cdc_enabled en la vista de catálogo sys.databases a 1 y objetos que requiere CDC para funcionar, tales como una(s) cuenta(s) de usuario(s), las tablas, trabajos, procedimientos almacenados y funciones.
Importante: No se puede permitir CDC sobre bases de datos del sistema.
Habilitación de CDC en las tablas (Más información AQUI)
Una vez que han permitido a CDC sobre base de datos, se puede activar para las tablas de la base de datos.
Al habilitar el CDC en una tabla mediante el procedimiento almacenado del sistema sys.sp_cdc_enable_table, se crea una instancia de captura de la tabla de origen.
El procedimiento almacenado de sistema sys.sp_cdc_enable_table incluye los siguientes argumentos:
  • source_schema: Define el esquema al que pertenece la tabla de origen.
  • source_name: Define el nombre de la tabla de origen. Este nombre debe existir en la base de datos actual y no puede existir en el esquema de los CDC.
  • role_name: Define el rol en la base de datos que se utiliza para proporcionar acceso a los datos capturados. Si el rol no existe, SQL Server intenta crearlo. Si el usuario ejecuta el comando no tiene permisos suficientes para crear un rol, toda la operación del procedimiento almacenado produce un error.

    Nota Importante: Cuando se ejecuta sys.sp_cdc_enable_table debe proporcionar la source_name source_schema, y los argumentos role_name. El argumento role_name se puede establecer en NULL.
  • capture_Instance: Define un nombre dado a la instancia que se utiliza para la denominación de la instancia de objetos específicos. Una tabla de origen puede tener un máximo de dos instancias de captura definidas. Si no se especifica capture_instance, el nombre por defecto es schemaname_sourcename. El nombre capture_instance no puede exceder de 100 caracteres.
  • supports_net_changes: Indica si la compatibilidad con la consulta de cambios de red se va a habilitar para esta instancia de captura. supports_net_changes es bit con un valor predeterminado de 1 si la tabla tiene una clave principal o un índice único identificado mediante el parámetro @index_name. De lo contrario, el parámetro tiene como valor predeterminado 0. Si es 0, sólo se generan las funciones de compatibilidad para consultar todos los cambios. Si es 1, se generan también las funciones necesarias para consultar los cambios de la red.
  • index_name: Define el nombre de un índice único válido en la tabla de origen.
  • captured_column_list: Identifica las columnas de origen que se va a capturar. Si este campo es nulo, todas las columnas se incluyen en la tabla de cambios.
  • filegroup_name: Define el nombre del grupo de archivos en el que se crea la tabla de cambios. Si este valor es NULL, la tabla de cambio se crea en el grupo de archivos predeterminado. Si filegroup_name se especifica, el nombre del grupo de archivos debe existir en la base de datos actual.
  • partition_switch: Establecer en TRUE o FALSE (por defecto) para indicar si el comando SWITCH PARTITION de ALTER DATABASE se puede ejecutar en la tabla de origen cuando CDC está activado.
Requisitos adicionales de columna
Las columnas capturadas tienen los siguientes requisitos:
  • Usted debe enumerar las columnas incluidas en el campo de clave principal o en el índice único especificado en el argumento index_name.
  • Usted debe utilizar comas para separar los nombres de columna.
  • Si los nombres de columna incluye una coma incorporado, o si usted quisiera agregar identificadores entre comillas a los nombres de columna, puede utilizar las comillas simples o corchetes en el nombre de columna.
  • Usted no puede listar las columnas que se definen con cualquiera de los nuevos tipos de datos introducidos en SQL Server 2008.
Imporante: ¿Cómo SWITCH PARTITION y CDC interactúan? Los cambios de datos que son causadas por un comando SWITCH PARTITION no se refleja en la tabla de cambios. Por ejemplo, si se cambian las filas de la tabla de origen, y en otra tabla, la operación de eliminación no es capturado.
Usted puede utilizar el siguiente ejemplo de código para permitir a los CDC en la tabla FactInternetSales en la base de datos AdventureWorksDW2008. Este ejemplo le permite consultar los cambios netos en los datos:
USE AdventureWorksDW2008R2;
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo'
, @source_name = N'FactInternetSales'
, @role_name = N'cdc_adminR2'
, @capture_instance = N'InternetSalesR2'
, @supports_net_changes = 1
GO
Puede utilizar la función cdc.fn_cdc_get_net_changes verificar la configuración CDC.
Descripción de los permisos CDC
Por defecto, las funciones sysadmin y db_owner tienen todos los permisos a los datos en las tablas de cambios. Cuando la opción role_name se establece en NULL, los CDC están habilitados sin necesidad de utilizar un ROL de entrada.
El ROL de entrada controla el acceso a los datos de la tabla de cambios. Cuando se configura el sistema de CDC de esta manera, cualquier usuario con el permiso SELECT en la tabla de origen se puede acceder a la información en la tabla de cambios.
Cuando se especifica un rol en el argumento role_name, se define un rol de entrada en la tabla de cambio, lo que obliga a todos los usuarios que necesitan acceso a los datos capturados a tener el permiso SELECT en las columnas capturadas en tabla, así como la pertenencia a la rol de acceso para la instancia de captura.

Consulta de los cambios
Puede utilizar cualquiera de las funciónes cdc.fn_cdc_get_all_changes_ <capture_instance> o  the cdc.fn_cdc_get_net_changes_<capture_instance> para consultar los datos en la tabla de cambios. Si habilita CDC y establecer el argumento supports_net_changes a 1, las dos funciones estaran disponibles para la instancia de CDC definidos. Si establece el argumento supports_net_changes a 0, o si no se incluye el argumento, sólo la funcion cdc.fn_cdc_get_all_changes_ <capture_instance> está disponible.
Con los cambios de red habilitados, si quieres escribir una consulta que devuelve una fila en el conjunto de resultados para cada fila modificada en Log Sequence Number (LSN) rango incluye sólo el contenido final, incluso si se han producido cambios intermedios, por lo que debe utilizar la función cdc.fn_cdc_get_net_changes_<capture_instance> .
La función cdc.fn_cdc_get_net_changes_ <capture_instance> incluye los siguientes argumentos (Más información AQUI):
  • from_lsn: Define el número LSN de partida para crear el rango dentro de las filas que deben ser devueltos. Todas las filas de la tabla de cambio con un valor LSN igual o mayor que el valor from_lsn se incluyen en el conjunto de resultados. Esto se conoce como establecimiento del límite inferior del intervalo de consulta.
  • to_lsn: Define el número LSN final a crear el rango de filas devueltas. Todas las filas de la tabla de cambio con un valor LSN menor o igual al valor from_lsn se incluyen en el conjunto de resultados. Esto se refiere como establecimiento del límite superior del intervalo de consulta.
  • row_filter: Controla las filas que se devuelven y lo que se muestra en las columnas de metadatos del conjunto de resultados. El argumento row_filter puede tener cualquiera de los siguientes valores:
    • All: Devuelve el número LSN de la transacción en la columna _$start_lsn , el valor de la operación que se realiza en la columna de la _$operation, y NULL en la columna _$update_mask.
    • All with mask: Devuelve el número LSN de la transacción en la columna_$start_lsn, y el valor de la operación realizada en la columna _$operation. Si la operación UPDATE devuelve un valor de 4, los bits de la columna _$update_mask asociadas con las columnas actualizadas se ponen a 1.
    • All with merge: Devuelve el número LSN de la transacción en la columna _ $ start_lsn, y devuelve un valor de 1 en la columna _ $ operation si la fila se ha eliminado y un valor de 5, si necesita un INSERT o UPDATE que se utilizarán para aplicar el cambio. La columna $ update_mask siempre tiene un valor de NULL.

      Uso de la opcion All with merge: Esta opción está diseñada para mejorar el rendimiento cuando no es necesario distinguir entre las instrucciones INSERT y UPDATE. Usted debe considerar el uso de esta opción si usted está usando la operación de mezcla disponibles en SQL Server 2008.
La tabla que es devuelta cuando se consulta la función cdc.fn_cdc_get_net_changes_ <capture_instance> incluye las columnas siguientes:
  • _start_lsn: Muestra el LSN asociado a la acción transacción de confirmación del cambio.
  • _$seqval: Muestra el valor de la secuencia para ordenar los cambios que se producen por fila dentro de una transacción. Todas las filas en una transacción tienen el mismo valor de _$start_lsn.
  • _$operation: Muestra un valor entero sobre la base de la operación realizada y el parámetro row_filter_option. Cuando se establece la row_filter_option a all o all with mask, esta columna muestra los siguientes valores:
    • 1: para las operaciones DELETE
    • 2: para las operaciones INSERT
    • 4: para operaciones UPDATE

      Cuando el parámetro row_filter_option se establece a all with merge, la operación _$operation muestra los siguientes valores:
    • 1: para las operaciones DELETE
    • 5: tanto para operaciones INSERT y UPDATE
  • _$update_mask: Muestra una máscara de bits que representan las columnas que están siendo capturados en la tabla de origen. Un 1 en la máscara representa una columna que se ha cambiado. Para las instrucciones INSERT y DELETE, todos los bits se ponen a 1.
  • <captured source table columns>: Devuelve cada columna de la tabla de origen que fue capturado.
La consulta siguiente se modifica filas de la tabla FactInternetSales. La figura posterior muestra el resultado de la funcion cdc.fn_cdc_get_net_changes_InternetSales una vez que la actualización se ha ejecutado:
USE AdventureWorksDW2008R2;
GO
DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10)
, @to_lsn binary(10);
-- Set the beginning of the time interval to yesterday to capture all changes.
SET @begin_time = GETDATE() -1;
-- DML statements to produce changes.
UPDATE dbo.FactInternetSales
SET OrderQuantity = 5
WHERE SalesOrderNumber = 'SO43697' AND SalesOrderLineNumber = 1;
UPDATE dbo.FactInternetSales
SET OrderQuantity = 2
WHERE SalesOrderNumber = 'SO43697' AND SalesOrderLineNumber = 1;
DELETE FROM dbo.FactInternetSales
WHERE SalesOrderNumber = 'SO43701'AND SalesOrderLineNumber = 1 ;
-- Set the end of the time interval after changes were completed.
SET @end_time = GETDATE();
-- Map the time interval to a change data capture query range.
SET @from_lsn = sys.fn_cdc_map_time_to_lsn(
'smallest greater than or equal', @begin_time);
SET @to_lsn = sys.fn_cdc_map_time_to_lsn(
'largest less than or equal', @end_time);
-- Return the net changes occurring within the query window.
SELECT * FROM cdc.fn_cdc_get_net_changes_InternetSales(
@from_lsn, @to_lsn, 'all');

Y los resultados:

image

Si desea escribir una consulta que devuelve filas de todos los cambios que se produjeron durante el período especificado, se debe utilizar la función cdc.fn_cdc_get_all_changes_capture_instance.
Si más de un cambio ha ocurrido en una determinada fila de la tabla de origen, varias filas son devueltas por esta función.
La función cdc.fn_cdc_get_all_changes_capture_instance incluye los siguientes
argumentos (Más información AQUI):
  • from_lsn: Define el número LSN de partida para crear el rango dentro de las filas que deben ser devueltos. Todas las filas de la tabla de cambio con un valor LSN igual o mayor que el valor from_lsn se incluyen en el conjunto de resultados. Esto se conoce como establecimiento del límite inferior del intervalo de consulta.
  • to_lsn: Define el número LSN final para crear el rango de filas devueltas. Todas las filas de la tabla de cambio con un valor LSN menor o igual al valor from_lsn se incluyen en el conjunto de resultados. Esto se conoce como establecimiento del límite superior del intervalo de consulta.
  • row_filter_option: El row_filter_option puede tener cualquiera de los siguientes valores:
    • All: Devuelve todos los cambios que se produjeron dentro del rango definido por las opciones from_lsn y to_lsn. Para las filas que se han modificado con la instrucción UPDATE, sólo la fila que contiene los nuevos valores es devuelta.
    • All update old: Devuelve todos los cambios que se produjeron dentro del rango definido por las opciones  from_lsn y to_lsn. Para las filas que se han modificado con la instrucción UPDATE, tanto en la fila que contiene los valores antes de la actualización y la fila que contiene los valores después de la actualización se devuelven.
La tabla que devuelve la función cdc.fn_cdc_get_all_changes_capture_instance tiene las mismas columnas como los enumerados anteriormente para la función de cdc.fn_cdc_get_net_changes_ <capture_instance>.
la siguiente figura se muestran los resultados de la función cdc.fn_cdc_get_all_changes_InternetSales con los cambios que se produjeron para un idéntico conjunto de resultados que muestra en la siguiente figura. Se pueden comparar estas dos tablas para ver las diferencias entre mostrar los cambios de red y todos los cambios.

image

Cuando usted está construyendo una aplicación para consultar una tabla de cambios, es posible que desee para definir las filas devueltas en función del tiempo cuando se produjeron los cambios más que en el LSN. Usted puede utilizar la funcion sys.fn_cdc_map_time_to_lsn para determinar el número LSN que se utilizan en la from_lsn y los argumentos de la  función dc.fn_cdc_get_all_changes_<capture_instance> o cdc.fn_cdc_get_net_changes_<capture_instance>.
Puede utilizar las siguientes funciones de los CDC al consultar los datos modificados:
  • sys.fn_cdc_has_column_changed: Devuelve un 1 o 0 para determinar si la columna identificada por la máscara suministra ha sido actualizada en cualquier fila de cambio asociada. La instancia de captura, nombre de la columna, y los valores de actualización se incluyen como máscara de entrada para esta función (Más información AQUI).
  • sys.fn_cdc_increment_lsn: Devuelve el LSN siguiente en la secuencia basada en un valor LSN de entrada. Si usted sabe el valor LSN del límite superior de la consulta anterior, se puede utilizar esta función para definir el nuevo límite inferior del rango de la nueva consulta. Puede usar esta lógica para no tener que conocer el momento específico que la sincronización paso por última vez. Usted sólo tendrá que mantener los valores LSN en el período de sincronización anterior. (Más información AQUI)
  • sys.fn_cdc_decrement_lsn: Devuelve el LSN anterior basado en un valor LSN de entrada. Usted puede utilizar esta función para determinar el límite superior de un intervalo de LSN definido en una consulta (Más información AQUI).
  • sys.fn_cdc_is_bit_set: Utiliza la posición ordinal en la máscara de bits (el valor de $ _update_mask) para una columna especificada, y la máscara de bits como entradas para devolver un bit que representa si la columna de entrada se ha actualizado. Usted puede utilizar esta función para agregar una columna a la salida que representa el estado de actualización de una columna de origen dado. (Más información AQUI).
  • sys.fn_cdc_get_column_ordinal: Utiliza la instancia de captura y la información de la columna nombre como entrada y devuelve un entero que representa la posición ordinal de la columna en la máscara de bits (Más información AQUI).
  • sys.fn_cdc_map_lsn_to_time: Utiliza un LSN como entrada y devuelve el valor de fecha y hora de la hora de confirmación de la columna tran_end_time en la tabla del sistema cdc.lsn_time_mapping (Más información AQUI).
  • sys.fn_cdc_get_max_lsn: Devuelve el LSN máximo que existe en la columna start_lsn de la tabla del sistema cdc.lsn_time_mapping. Este valor representa el último cambio confirmado propado a una tabla de cambios en la base de datos actual. Este valor no depende de la instancia de captura (Más información AQUI).
  • sys.fn_cdc_get_min_lsn: Devuelve el LSN máximo que existe en la columna start_lsn de la tabla del sistema cdc.lsn_time_mapping. Este valor puede cambiar cuando el proceso de limpieza se lleva a cabo. Puede utilizar esta función para determinar el extremo inferior para el valor de LSN para verificar que su rango se encuentra dentro de la línea de tiempo CDC (Más información AQUI).
Además de las funciones que le permiten consultar los datos de las tablas de cambios, SQL Server incluye los procedimientos almacenados que puede usar para administrar los CDC (Más información AQUI y AQUI).

SQL Server Audit (Más información AQUI)
Usted debe crear un objeto de auditoría de servidor SQL antes de definir las especificaciones de auditoría. Usted puede lograr esto mediante el uso de SSMS o con la declaración CREATE SERVER AUDIT.
En SSMS, se puede utilizar el Explorador de objetos y vaya a la carpeta en la carpeta de Auditorías de Seguridad. A continuación puede hacer clic en la carpeta y seleccione Nueva Auditoría para crear una nueva de SQL Server Audit, como vemos en la siguiente imagen.
image
Dentro de la definición de auditoría, puede configurar las siguientes opciones:
  • Audit Name: Define el nombre asignado al objeto de auditoría.
  • Queue Delay: Define la cantidad de tiempo transcurrido en milisegundos que puede pasar antes de que el servidor debierá procesar una acción de auditoría. El valor predeterminado es 1 segundo o 1.000 milisegundos.
  • Shut Down Server On Audit Log Failure: Determina si SQL Server se cierra cuando la auditoría produce un error. Usted puede activar esta opción si las políticas de su organización de auditoría o de los requisitos reglamentarios aplicables, tales como la Health Insurance Portability, la Accountability Act (HIPAA), el Payment Card Industry (PCI) act, y la Federal Information Security Management Act (FISMA), que requieren auditar cada caso sin fallos.
  • Audit Destination: Especifica donde la información de auditoría sera escrita. Las opciones incluyen un archivo, el registro de seguridad de Windows, o el registro de aplicación de Windows. Cuando se selecciona el destino del archivo de auditoría, también hay que configurar las siguientes opciones:
    • File Path: Especifica el directorio donde se guarda el archivo. El motor de base de datos genera automáticamente el nombre del archivo, que se basa en el nombre del objeto de auditoría y la auditoría de identificador único global (GUID).
    • Maximum Rollover: Define el máximo número de archivos que se pueden crear, excepto cuando la opción ilimitada es seleccionado. Cuando se habilita la opción ilimitada para este ajuste, no hay límite en el número de archivos creados.
    • Maximum Filesize: Especifica el tamaño máximo del archivo antes de crear uno nuevo. Es necesario especificar un valor entero y si ese entero debe ser interpretada como megabytes, gigabytes, o terabytes. Cuando se selecciona Ilimitado, el archivo aumenta hasta que la unidad de disco duro está lleno. Si el archivo se llena el disco duro por completo, se detiene el registro y el servidor se apaga si la opción Shut Down Server On Audit Log Failure se ha habilitado. El valor mínimo permitido para este campo es de 2 MB.
    • Reserve Disk Space: Especifica que se crea un archivo en el tamaño máximo del archivo inmediatamente. De lo contrario, el archivo crece a medida que los detalles de auditoría se agregan a él. No se puede elegir esta opción si la opción ilimitada es habilitado para el tamaño máximo de archivo.
El código siguiente muestra la sintaxis completa de la declaración CREATE SERVER AUDIT. Las opciones disponibles reflejan las opciones de configuración descrito anteriormente para crear un objeto nuevo de Auditoría con SSMS:
CREATE SERVER AUDIT audit_name
{
    TO { [ FILE (<file_options> [ , ...n ]) ] | APPLICATION_LOG | SECURITY_LOG }
    [ WITH ( <audit_options> [ , ...n ] ) ] 
}
[ ; ]
<file_options>::=
{
       FILEPATH ='os_file_path'
    [ , MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]
    [ , MAX_ROLLOVER_FILES = { integer | UNLIMITED } ]
    [ , RESERVE_DISK_SPACE = { ON | OFF } ] 
}
  
<audit_options>::=
{
    [  QUEUE_DELAY =integer ]
    [ , ON_FAILURE = { CONTINUE | SHUTDOWN } ]
    [ , AUDIT_GUID =uniqueidentifier ]
}

Una vez creado el objeto SQL Server Audit, que debe activarla. Usted puede lograr esto mediante el uso de SSMS o la sentencia ALTER SERVER AUDIT.

Creación de una especificación de auditoría de base de datos de nivel

Una vez creado el objeto SQL Server Audit, puede optar por crear especificaciones de auditoría a nivel de servidor o el nivel de base de datos. Para el acceso de seguimiento o modificaciones hechas a los datos, se crea una especificación de auditoría de base de datos.

Puede crear una especificación de auditoría de base de datos con la instrucción
CREATE DATABASE AUDIT SPECIFICATION o el Explorador de objetos en SSMS. Las Especificaciones de auditoría de base de datos se encuentran en la carpeta  por debajo de [instance name]\[Databases]\[database name]\Security folder.
Para definir una especificación de auditoría de base de datos, debe definir el nombre de la especificación de auditoría, el objeto de auditoría donde los eventos serán escritos, y cada acción de auditoría que se registra.
Hay una amplia variedad de tipos de acciones de auditoría que se puede configurar dentro de cada especificación de auditoría de base de datos. Por ejemplo, puede especificar INSERT, UPDATE, DELETE, o SELECT y definir sobre los objetos que desea realizar un seguimiento de estos comandos. También se puede especificar para qué usuarios y roles le gustaría que los sucesos se registraran.
Además de especificar los eventos individuales que seran registrados, puede especificar un grupo entero de eventos que se registran. El SCHEMA_OBJECT_ACCESS_GROUP, también equivalente a la clase Audit Schema Object Access event, devuelve la información cuando un permiso de objeto, como INSERT, UPDATE, DELETE, SELECT, EXECUTE, o REFERENCES se produce. La información proporcionada por esta clase de eventos notifica que la sintaxis de la consulta que se publicó pero no mantiene un antes y después de los valores de los datos actualizados o eliminados (Más información AQUI).

Performance y Auditoria: ¡Tienes que ser cuidadoso en la definición de auditoría para verificar los efectos del rendimiento de los registros. Hay una tendencia a tratar de registrar todos los eventos, que puede conducir a resultados extremadamente lenta. Considere cuidadosamente los objetos en que la auditoría es necesaria, así como los eventos que deben ser seguidos.

Antes de la especificación de auditoría de base de datos comience a recopilar información, que debe activarla con SSMS, como parte de la instrucción CREATE DATABASE especificación de auditoría, o con la especificación de auditoría ALTER DATABASE.
Resumen de la lección:
  • El seguimiento de cambios (Change tracking) debe estár habilitado primero en la base de datos y luego a nivel de tabla.
  • El seguimiento de cambios (Change tracking) se puede decir lo que los registros han sido modificados y le proporcionará el resultado final de los datos.
  • El seguimiento de cambios (Change tracking) requiere menos recursos del sistema de CDC.
  • CDC le puede decir lo que los registros han sido modificados y le proporcionará los datos finales, así como los estados intermedios de los datos.
  • SQL Server Audit le permite registrar el acceso a las tablas, vistas y otros objetos.

Capitulo 9 - Lección 2: Implementar Scripts mediante Windows PowerShell

Como desarrollador, puede usar PowerShell de SQL Server 2008 para automatizar el proceso de despliegue de sus aplicaciones. También puede utilizar PowerShell de SQL Server para automatizar la enumeración de los objetos de base de datos y las propiedades de los objeto. Además, puede invocar a través de Sqlcmd PowerShell de SQL Server, lo que le permite ejecutar cualquier válida comandos SQL desde dentro del entorno de Windows PowerShell.

¿Qué es Windows PowerShell? (Más información AQUI)
Windows PowerShell es un entorno de línea de comandos de shell y un lenguaje que le permiten automatizar las tareas administrativas y el desarrollo mediante la creación de scripts robustos. Como las funciones de Windows PowerShell existen en muchas aplicaciones de Microsoft, una vez que aprenda este lenguaje de programación común, se puede utilizar para administrar varios servidores y productos.
Al instalar SQL Server 2008, el programa de instalación instala Windows PowerShell 1.0 (si no está instalado), el proveedor de SQL Server PowerShell, un conjunto de cmdlets de PowerShell de SQL Server, y la utilidad sqlps para habilitar la funcionalidad de SQL Server en el entorno de PowerShell.
Con PowerShell de SQL Server, puede crear secuencias de comandos que puede ejecutar en la fecha prevista mediante trabajos del Agente SQL Server, mediante el uso de la opción Iniciar PowerShell en SSMS, o ejecutando un entorno de la aplicación SQL Server PowerShell tales como sqlps o una aplicación personalizada.
Soporte de Windows PowerShell: En SQL Server 2008, Windows PowerShell se limita a apoyar las SMO en relación con el motor de base de datos y de Service Broker. Un proveedor de Windows PowerShell para SQL Server Analysis Services (SSAS) está disponible en el sitio web de CodePlex en http://www.codeplex.com/powerSSAS
Precaución: Cuando se crea pasos de trabajo dentro de Windows PowerShell en el Agente SQL Server, cada paso inicia un proceso sqlps separado. Cada uno de estos procesos requiere aproximadamente 20 megabytes (MB) de memoria. Si ejecuta un gran número de pasos de trabajo de SQL Server PowerShell al mismo tiempo, usted debe probar los recursos y el impacto en el rendimiento.
Navegar por la jerarquía de SQL Server PowerShell (Más información AQUI)
SQL Server utiliza una jerarquía para representar cómo los objetos se relacionan entre sí dentro de un servidor.
Por ejemplo, existe una tabla en un esquema, que existe dentro de una base de datos, que existe dentro de una instancia de SQL Server, que reside en un servidor.SQL Server PowerShell utiliza una representación de la unidad y de la ruta de esta jerarquía. La estructura de la unidad y de la ruta utilizada por SQL Server PowerShell se construye utilizando una terminología similar y los comandos a los que se utiliza en un sistema de archivos.
El nodo raíz de SQL Server es el SQLSERVER: la unidad. Bajo el SQLSERVER:unidad, el proveedor de SQL Server PowerShell implementa las siguientes tres carpetas:
  • SQLSERVE R:\SQL: Contiene objetos de base de datos, como bases de datos, tablas, vistas y procedimientos almacenados.
  • SQLSERVE R:\SQLPolicy: Contiene objetos basados ​​en políticas de gestión, tales como las políticas y los aspectos.
  • SQLSERVE R:\SQLRegistration: Contiene objetos registrados servidor, tales como los grupos de servidores y servidores registrados
Dentro de cada motor y carpeta, se puede definir la ruta de acceso al objeto que desea crear, ver o administrar. Las carpetas, las subcarpetas y los objetos que se puede acceder son definidas por el modelo SMO incluye en SQL Server 2008 (Más información AQUI).
Cuando se utiliza el Explorador de objetos en el SSMS para iniciar una sesión de PowerShell de SQL Server, la ruta se establece en el objeto desde el que comenzó la sesión, como se muestra en las figuras siguientes:
image
image

La ruta SQL Server PowerShell comienza con la unidad, seguida de una de las tres carpetas compatibles. Para la carpeta de SQL, el nombre del servidor y el nombre de la instancia siguen a la carpeta.
Si usted se está refiriendo a la instancia predeterminada, debe especificar la palabra DEFAULT. Después de que el nombre de la instancia, la ruta alterna entre el tipo de objeto y el nombre del objeto al que se refieren
Por ejemplo, PS SQLSERVER:\SQL\MAGC\DEFAULT+sa\Databases\AdventureWorks2008\Tables\Person.Contact se refiere a la tabla de Contact en el esquema Person en la base de datos AdventureWorks2008 en la instancia predeterminada de un servidor llamado MAGC.


Usando SQL Server PowerShell para enumerar los objetos
Una vez que se establece la ruta al objeto correcto, usted puede enumerar los elementos secundarios, mover elementos, cambiar el nombre, y realizar muchas otras acciones también. Cmdlets de PowerShell puede ser referida con su nombre completo o con cualquiera de una serie de alias. Una lista de los cmdlets, sus funciones, y sus alias implementado en SQL Server 2008 son los siguientes:
  • Get-Location: Devuelve el nombre del nodo actual. Alias: gl, pwd
     image
  • Set-Location: Cambios en el nodo actual. Alias: sl, cd, chdir.
    image
  • Get-ChildItem: Las listas de los objetos almacenados en el nodo actual. Alias: GCI, dir, ls.
    image
  • Get-Item: Devuelve las propiedades del elemento actual. Alias: gu.
    Move-Item: Mueve un elemento. Alias: mi, move, mv.
  • Rename-Item: Renombra un objeto. Alias: rni, rn, ren,
    Antes:
    image
    Como cambie el nombre:
    image
    Después
     image
  • Remove-Item: Elimina un objeto. Alias: ri, del, rd, rm, rmdir.
El uso de alias: Aunque por lo general es más fácil de usar alias cuando se trabaja de forma interactiva, utilizando el nombre completo del cmdlet hará scripts almacenados más fáciles de leer y mantener.
Cuando se trabaja de forma interactiva en la sesión de PowerShell de SQL Server, puede utilizar los siguientes consejos:
  • Use las teclas de flecha arriba y abajo para desplazarse por los comandos que se han ejecutado anteriormente.
  • Utilice las teclas de flecha izquierda y derecha para desplazarse y editar un comando que ha sido devuelto con las teclas de flecha arriba y abajo, o uno que acaba de escribir.
  • Usar los alias para minimizar escribir.
  • Utilice la ruta completa o relativa en función de los caminos actual y el deseado,como se muestra en los siguientes ejemplos:
    • Si su ruta actual es SQLSERVER:\SQL\MAGC\DEFAULT\ Databases\AdventureWorks2008\Tablas\Person.Address y que desea mover a la subcarpeta Tablas directamente sobre la ruta actual, puede escribir cd .. para subir un nivel en el camino.
      image
    • Si su ruta actual es SQLSERVER:\SQL\MAGC\DEFAULT\ Databases\AdventureWorks2008\Tablas\Person.Address y se desea mover a la carpeta de Directiva de SQL (SQLPolicy), la opción más sencilla es probablemente escribir cd \SQLPolicy
      image
  • Use cls para limpiar la pantalla para que su conjunto de resultados más fáciles de leer.
  • Use el parámetro -force para ver los objetos del sistema, tales como el esquema de sistema y los objetos en él.
  • Utilice auto relleno al escribir una ruta parcial o nombre de cmdlet y pulsar la tecla Tab para recibir una lista de objetos cuyos nombres coincidan con lo que ha escrito.
Los cmdlets Adicionales de SQL Server PowerShell
Además de los cmdlets mencionados anteriormente, el proveedor de SQL Server PowerShell incluye otros elementos de construcción en cmdlets para proporcionar una mayor funcionalidad. Los siguientes cmdlets podría ser beneficioso para los desarrolladores:
  • Get-Help: Proporciona información de ayuda sobre cada cmdlet. El-Full parámetro proporciona la ayuda técnica completa, incluyendo las muestras. La imagen siguiente muestra los resultados de la pantalla de ayuda para el cmdlet Invoke-Sqlcmd.
    image 
  • Invoke-Sqlcmd: Ejecuta un script Transact-SQL (T-SQL) o XQuery. (En la imagen muestro la ejecución de la consulta “Select top 5 pc.FirstName, pc.LastName, pc.EmailAddress from Person.Contact pc” y vemos los resultados en pantalla)
    image
  • Encode-SqlName: Codifica un identificador de SQL Server (nombre del objeto) para reformatear los caracteres no admitidos por el lenguaje de PowerShell de SQL Server.
  • Decode-SqlName: Devuelve el identificador original de SQL cuando se les da un identificador codificado SQL
  • Convert-UrnToPath: Convierte un SMO Uniform Resource Name (URN) a una ruta utilizada por SQL Server PowerShell. Tanto la ruta como URN contienen la misma información, pero el formato es diferente. (Más información AQUI)
Uso de SQL Server PowerShell-Ejemplos
Los siguientes ejemplos se proporcionan para ayudar a que se familiarice con algunos de los cmdlets y las opciones disponibles cuando se utiliza SQL Server PowerShell de SQL Server 2008. 
Este primer ejemplo se utiliza la opción Get-Item y la evalúa y muestra el estado actual de la política de SQL Server modo de conexión en un servidor llamado MIAMI.Esta política es una de las mejores prácticas de políticas incluye con SQL Server 2008, que se puede importar en las políticas de su servidor. Si su servidor está configurado para sólo la autenticación integrada, el resultado devuelto es True. Si la configuración de seguridad permitirá ambas SQL y los inicios de sesión de Windows integrada, el resultado devuelto es falso, como se muestra en la siguiente figura.
image
La segunda muestra, en la figura se muestra la lista de subcarpetas disponibles en la instancia predeterminada en el servidor de MAGC en la carpeta SQL. Observe que el ejemplo se utiliza el alias de ls en lugar de detallar la completa cmdletGet-ChildItem.
image
El último ejemplo, en la figura muestra el uso del cmdlet Get-ChildItem para listar los parámetros definidos en el procedimiento almacenado HumanResources.uspUpdateEmployeeLogin en la base de datos AdventureWorks2008 en la instancia predeterminada del servidor de MAGC.
image


Resumen de la lección
  • SQL Server PowerShell es un shell de línea de comandos y el entorno de programación basado en Windows PowerShell.
  • SQL Server PowerShell utiliza una jerarquía para representar cómo los objetos están relacionados unos con otros.
  • Las tres carpetas que existen en el proveedor de PowerShell de SQL Server son SQLSERVER:\SQL, SQLSERVER:\SQLPolicy, y SQLSERVER\SQLRegistration.
  • Puede navegar por la jerarquía mediante el uso de cualquiera de los nombres de cmdlet o sus alias.

Capitulo 9 - Lección 1: Manejabilidad de las características de Microsoft SQL server

Como desarrollador de bases de datos, puede que tenga que extender sus soluciones de bases de datos más allá de simplemente devolver los datos de consulta para una aplicación front-end. Por ejemplo, es posible que necesite enviar informes por correo electrónico a los usuarios. Este capítulo trata sobre varias características de Microsoft SQL Server 2008 que le permiten difundir y gestionar los datos en su base de datos de manera eficiente. Características que se describen incluyen Database Mail, Windows PowerShell, y change tracking. SQL Server 2005 introduce Database Mail, y SQL Server 2008 continúa el apoyo para el correo de la base de datos que le permite enviar mensajes de correo electrónico desde y hacia el servidor que ejecuta SQL Server. En SQL Server 2008, Microsoft introdujo Windows PowerShell para administrar tecnologías compatibles con los objetos de administración SQL Server (SMO).

Lección 1: Integración de Database Mail
Con SQL Server 2005, Microsoft introdujo Database Mail para que el servicio SQL Server para enviar mensajes de correo electrónico. Mediante el uso de Database Mail , usted puede desarrollar aplicaciones de bases de datos que envían los resultados de la consulta y los archivos adjuntos a los usuarios. Analizaremos cómo utilizar el procedimiento almacenado de sistema sp_send_dbmail.

Vista general de Database Mail
Database Mail permite a los administradores y desarrolladores que envíen mensajes de correo electrónico generados por el servicio de SQL Server. En SQL Server 2005, Database Mail se introdujo para sustituir a SQLMail. SQLMail se incluye en SQL Server 2008 sólo por compatibilidad con versiones anteriores. Uno de los principales beneficios de Database Mail es que se comunica mediante el protocolo SMTP y no requiere una extensión compatible con MAPI (aplicaciones de correo electrónico), como Microsoft Office Outlook, en el servidor que ejecuta SQL Server. Además, el correo de bases de datos proporciona tolerancia a fallos mediante el soporte de varios servidores SMTP y SMTP múltiples con cuentas de usuario y perfiles.

Configuración de Database Mail (Más información AQUI)
Aunque la configuración de Database Mail es tradicionalmente una función de la función de administrador de base de datos, una breve reseña se ofrece aquí como información general. Usted puede utilizar SSMS para iniciar el Asistente para configuración de Database mail.
Nota: habilitando Database mail: Para reducir al mínimo los problemas de seguridad de su servidor, Database mail está desactivada por defecto. Si se define Database mail mediante el uso de “Database Mail Configuration Manager”, Database mail está habilitada como parte del proceso de configuración. Además, puede utilizar Surface Area Configuration Policy-Based Management  o el sistema de procedimiento almacenado sp_configure para habilitar Database mail en uno o más servidores.
Para configurar Database mail, debe realizar los siguientes pasos:
  1. Abra SSMS y conéctese a la instancia de SQL Server que desea configurar.
  2. En el Explorador de objetos, expanda la carpeta Administración, haga clic en Correo de bases de datos, y seleccione Configuración de Database Mail, como se muestra en la figura siguiente:

    image
  3. En la página Configuración de la base de datos mail de bienvenida del asistente, revise la información y haga clic en Siguiente.
  4. En la página Configuración de tareas de selección, seleccione la primer opción (Set up Database Mail by performing the Following Tasks) y haga clic en siguiente:
    image
    Nota: Una vez que Database Mail se ha configurado, el asistente de configuración se puede utilizar para administrar la configuración.
  5. Si un mensaje de advertencia SSMS se indica que la función de correo de base de datos no está disponible, haga clic en Sí para habilitar esta característica. Si Service Broker se ha inhabilitado en la base de datos msdb, debe detener el Agente SQL Server (si se está ejecutando) y habilitar Service Broker en la base de datos msdb antes de poder habilitar Database Mail
  6. En la página Nuevo perfil, escriba un nombre de perfil descriptivo y una descripción en los campos correspondientes.
  7. En la sección Cuentas de SMTP, haga clic en Agregar para crear una cuenta nueva base de datos de correo.
  8. En la ventana Nueva base de datos de cuentas de correo, añadir la información que corresponde a la cuenta de SMTP que el servicio de SQL Server va a utilizar, y haga clic en Aceptar. Siguiente figura muestra la entrada de la muestra. Reemplazar esta información con información de la cuenta válida para el servidor.
    image
  9. En la página de perfil nuevo, compruebe la información de configuración, a continuación, haga clic en Siguiente.
  10. En la página Administrar perfil de seguridad, configurar los perfiles adecuados públicos o privados, a continuación, haga clic en Siguiente.
  11. En la página Configurar parámetros del sistema, revisar los parámetros por defecto del sistema, realizar los cambios necesarios y haga clic en Siguiente.
  12. En la página Finalización del asistente, haga clic en Finalizar.
  13. Verificar que la configuración tubo éxito, a continuación, haga clic en Cerrar.
Envío de mensajes de Correo electrónico mediante Database Mail
Puede utilizar las características de Database Mail para integrar los mensajes de correo electrónico en sus aplicaciones.
Por ejemplo, puede agregar al sistema el procedimiento almacenado sp_send_dbmail al final del procedimiento almacenado que la aplicación utiliza al añadir un nuevo cliente a la base de datos de crédito para enviar un mensaje de correo electrónico al supervisor de crédito con la información agregada del nuevo cliente .
El procedimiento almacenado de sistema sp_send_dbmail se utiliza para enviar mensajes de correo electrónico desde el servicio de SQL Server a destinatarios de correo electrónico. Este comando tiene las opciones que le permiten definir el estándar de e-mail los campos del mensaje, tales como A:, CC:, CCO: y Asunto:, junto con más opciones específicas de SQL Server (por ejemplo, la consulta que se ejecuta) para proporcionar flexibilidad al crear aplicaciones de SQL Server que necesitan enviar mensajes de correo electrónico. El sistema de almacenado sp_send_dbmail procedimiento incluye los siguientes argumentos:
  • @profile_name: Especifica el nombre del perfil de correo desde el que se envió el mensaje. Si no hay ningún perfil de correo predeterminado existe, debe incluir @profile_name. Si no hay ninguno en la lista, el sistema en el primer intento utiliza el perfil privado predeterminado para el usuario actual.
    Si el usuario actual no tiene un perfil privado predeterminado, el sistema utiliza el perfil público predeterminado para la base de datos msdb.
  • @recipients: Especifica los destinatarios del mensaje de correo electrónico. La lista completa de direcciones de correo electrónico debe estar encerrado entre comillas simples, y puntos y comas deben separar las direcciones.
    Este campo es opcional, pero si los receptores @recipients, @copy_recipients, and @blind_copy_recipients son todos en blanco, falla el comando.
  • @copy_recipients: Especifica las direcciones de correo electrónico de los destinatarios que se incluirán en el campo CC. La lista completa de direcciones de correo electrónico debe estar encerrado entre comillas simples, y puntos y comas deben separar las direcciones
  • @blind_copy_recipients: Especifica las direcciones de correo electrónico de los destinatarios que se incluirán en el campo CCO. La lista completa de direcciones de correo electrónico debe estar encerrado entre comillas simples, y puntos y comas deben separar las direcciones.
  • @subject: Especifica el valor que se incluirán en el Asunto: “título del e-mail”. Si no se especifica nada, el titulo será "Mensaje de SQL Server”. El titulo debe ser encerrado entre comillas simples.
  • @body: Especifica el contenido del mensaje, debe ir entre comillas simples.
  • @body_format: Especifica si el cuerpo del mensaje es "TEXT" o "HTML". El tipo de formato predeterminado es TEXT.
  • @importance: Ajusta el nivel de importancia del mensaje en 'Low', 'Normal' o ''High'. Si no se especifica, la importancia por defecto es "Normal".
  • @sensitivity: Ajusta el nivel de sensibilidad de los mensajes en  'Normal', 'Personal', 'Private', o "Confidential". El valor predeterminado es "Normal".
  • @file_attachments: Especifica una lista de nombres de archivo separados por comas que desea adjuntar al mensaje. La lista completa se debe encerrar entre comillas simples.
  • @query: Define una consulta que el sistema ejecuta. Puede incluir los resultados de la consulta en el cuerpo del mensaje de correo electrónico o como archivo adjunto. La consulta completa se encuentra dentro de comillas simples.
  • @execute_query_database: Especifica el contexto de base de datos para la consulta. Este argumento se ignora si no se especifica @ query.
  • @attach_query_result_as_file: Especifica si el resultado de la consulta se incluyen como un archivo adjunto o en el cuerpo del mensaje de correo. Un valor de 1 especifica un archivo adjunto será utilizado. Un valor de 0 especifica el resultado de la consulta seguirá el contenido especificado en el argumento @body. Este argumento se ignora si no se especifica @ query. Si no incluye esta opción, los resultados de la consulta se devuelven en el cuerpo del mensaje de correo electrónico.
  • @query_attachment_filename: Especifica el nombre del archivo adjunto resultante de una consulta. Esta opción requiere comillas si el nombre del archivo incluye una extensión de nombre de archivo (como. Txt) u otros caracteres no permitidos. Si @attach_query_result_as_file se establece en 1 y este parámetro no se incluye, el correo electrónico establece el nombre de archivo predeterminado. El argumento @query_attachment_filename se ignora si el argumento @attach_query_result_as_file no se especifica o se establece en 0 o @query no está definido.
  • @query_result_header: Se establece en 1 o 0 para especificar si los encabezados de columna se incluyen en el conjunto de resultados. El valor predeterminado es 1, que especifica que se debe incluir encabezados de columna. Este argumento se ignora si no se especifica @ query.
  • @query_result_width: Especifica el número de caracteres que se incluirán en una sola línea en el formato del conjunto de resultados. El valor predeterminado es de 256 caracteres, pero se puede establecer esta opción para cualquier cosa entre 10 y 32767. Este argumento se ignora si no se especifica @query.
  • @query_result_separator: Especifica el carácter que se utiliza para separar las columnas en el conjunto de resultados. El valor predeterminado es un espacio.
  • @exclude_query_output: Se establece en 0 o 1 para especificar si los mensajes de consulta de error como el que se muestra en la Figura 9.3 se muestran en el monitor. Un valor de 0 incluye el mensaje de error de consulta en el monitor, y un valor de un informe en el monitor sólo que el comando se ha completado con éxito, incluso si la consulta en el procedimiento almacenado no.
    image
  • @append_query_error: Especifica si un mensaje de correo electrónico está siendo enviado, cuando se produce un error de consulta. La configuración por defecto de 0 significa que la base de datos de correo no envía el mensaje de correo electrónico cuando la consulta produce un error. Cuando el valor se establece en 1, Database Mail envía el mensaje de correo electrónico y añade el mensaje de error en el correo electrónico como se muestra en la Figura siguiente. Este ajuste también afecta al comportamiento del argumento @exclude_query_output.
    image
  • @query_no_truncate: Se establece en 0 o 1 para especificar si grandes columnas de longitud variable en el conjunto de resultados se truncan. El valor por defecto de 0 trunca las columnas a 256 caracteres. Si el valor se establece en 1, que no trunca grandes columnas de longitud variable, títulos de las columnas que no están incluidos, y se necesitan recursos adicionales para ejecutar la consulta.
  • @mailitem_id [ OUTPUT ]: Establece una variable de salida opcional para devolver el mailitem_id del mensaje.
La siguiente consulta envía un mensaje de correo electrónico a student@Adventure-Works.com que tiene el titulo de “Job Candidate Resumes”. El mensaje incluye un archivo adjunto llamado Candidate_resumes.txt. Este documento incluye los resultados de una consulta con las columnas JobCandidateID y Resume de la tabla HumanResources.JobCandidate en la base de datosAdventureWorks2008. Los datos XML recuperados de la columna  no se truncan.
El archivo de texto generado se muestra en la Figura posterior:
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'student@Adventure-Works.com',
@query =
'SELECT JobCandidateID
, Resume from HumanResources.JobCandidate' ,
@subject = 'Job Candidate Resumes',
@execute_query_database = 'AdventureWorks2008',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Candidate_resumes.txt',
@query_no_truncate = 1;
y el archivo adjunto que se genera es este:
image
Comandos en desuso: SQL Server 2008 incluye xp_sendmail, así como una serie de procedimientos adicionales obsoletos solo para apoyar SQLMail y compatibilidad hacia atrás. Usted no debe usar SQLMail en el desarrollo de nuevos porque el apoyo se quitará en una versión futura de SQL Server. Se debe implementar Database Mail en los nuevos proyectos. Además,usted debe comenzar a convertir el código existente en Database Mail.
Gestión de Database Mail (Más información AQUI)
Aunque por lo general la configuración de Database Mail cae en la función de administrador de bases de datos, puede utilizar el siguiente sistema de procedimientos almacenados para modificar o solucionar problemas de configuración que afectan a los mensajes que están enviando a sus aplicaciones:
  • sysmail_configure_sp: Configura los parámetros como el tamaño máximo de archivo permitido para los archivos adjuntos, las extensiones de archivos prohibidas que no pueden ser enviados como archivos adjuntos, y la configuración de reintentos.
  • sysmail_help_configure_sp: Muestra la configuración actual para el correo de la base de datos.
    EJ: image
  • sysmail_help_queue_sp: Muestra información sobre el estado y las colas de correo. Puede utilizar este procedimiento almacenado para solucionar los mensajes que no se recibieron.
  • sysmail_delete_mailitems_sp: Elimina mensajes de correo electrónico de forma permanente a partir de tablas de Database Mail en en la base de datos msdb. Estos mensajes se pueden eliminar en función de su estado o la fecha en que se envió el mensaje. Los archivos adjuntos de los  mensajes eliminados también se eliminarán, pero los sucesos registrados asociados deben ser eliminados de forma independiente mediante el uso del procedimiento sysmail_delete_log_sp.
  • sysmail_delete_log_sp: Borra las entradas de forma permanente de los registros de la base de datos de correo.
    Estas entradas se pueden eliminar en función de su estado o la fecha en que se envió el mensaje asociado. Los mensajes de correo electrónico asociados con las entradas del registro eliminado debe ser eliminado de forma independiente mediante el uso del procedimiento sysmail_delete_mailitems_sp.
  • sysmail_start_sp: Inicia la Database Mail a partir de los objetos asociados de service Broker.
  • sysmail_stop_sp: Detiene Database Mail al detener los objetos de servicio asociados Broker. Puede utilizar este procedimiento almacenado para solucionar problemas de Database Mail mediante la detención temporal de la tramitación de los mensajes en las colas de Database Mail. El procedimiento almacenado sp_send_dbmail sigue funcionando, mientras que los objetos de Service Broker están detenidos.
Resumen de la lección
  • Database Mail se introdujo en SQL Server 2005 y debe ser usado en lugar de SQL Mail.
  • Database Mail está desactivado por defecto para reducir al mínimo la superficie de el servidor.
  • Usted debe utilizar el procedimiento de sistema almacenado sp_send_dbmail para integrar la base de datos de correo con sus aplicaciones.
  • Una amplia variedad de argumentos le permite personalizar los mensajes de correo electrónico y archivos adjuntos enviados desde el servidor de bases de datos.