Esecuele Sin Fronteras

SQL Server, Reporting Services y Biztalk Server
  • Esecuele Sin Fronteras

    Configurar Database Mail cuando no usamos el puerto por defecto ni SQL Browser

    • 0 Comments

    Hace un tiempo me encontré un entorno en el cual la base de datos no estaba en el puerto por defecto por política de seguridad y el Browser de SQL no estaba activo por el mismo motivo.

    En este escenario la configuración de Database Mail va a realizarse sin problemas pero a la hora de arrancar  va a fallar ya que no pide número de puerto, simplemente se auto configura para usar el nombre de la instancia. Por tanto si ponemos una instancia por defecto va a intentar conectar al puerto 1433 y si es una instancia con nombra va a intentar contactar contra el Browser.

    image

    Como se puede ver no puedo configurar más que unas pocas opciones.

    Ahora bien después de usar el wizard para crear la instancia el servidor no funciona ya que Database Mail no puede contactar con la base de datos.

    La solución para este problema viene de la mano del siguiente articulo http://msdn.microsoft.com/en-us/library/bb326746.aspx. El articulo describe como configurar Database Mail para usar un servidor remoto, en nuestro caso queremos usar el local pero Database Mail no lo encuentra, aplicando los pasos del articulo podremos conectar Database Mail a nuestra instancia.

    1 – Asegurarnos de que tenemos Database Mail configurado en el servidor

    2 – Navegamos al directorio donde está el ejecutable de Database Mail, normalmente en el directorio Binn dentro del directorio de instalación de la instancia:

    En SQL 2005 C:\Program Files\Microsoft SQL Server\MSSQL90.X\MSSQL\Binn

    En SQL 2008 C:\Program Files\Microsoft SQL Server\MSSQL10.X\MSSQL\Binn

    En SQL 2008 R2 C:\Program Files\Microsoft SQL Server\MSSQL10.50.X\MSSQL\Binn

    3 – Creamos el siguiente archivo de configuración en el directorio:

    DatabaseMail90.exe.config para SQL 2005

    DatabaseMail.exe.config para SQL 2008  y 2008 R2

    4 – Escribimos lo siguiente en el archivo

    <configuration>
      <appSettings>
        <add key="DatabaseServerName" value ="LocalServerName" />
        <add key="DatabaseName" value ="msdb" />
      </appSettings>
    </configuration>

    Sustituimos LocalServerName por tcp:hostname,port  por ejemplo si nuestro servidor se llama Kaworu y esta escuchando en el puerto 34657 la configuración quedaria así:

    <configuration>
    <appSettings>
    <add key="DatabaseServerName" value ="tcp:kaworu,34657 " />
    <add key="DatabaseName" value ="msdb" />
    </appSettings>
    </configuration>

    5 – A continuación deberemos ejecutar el siguiente comando en Management Studio para que Database Mail use el archivo de configuración

    USE msdb;
    GO
    INSERT INTO [msdb].[dbo].[sysmail_configuration] ( [paramname] ,[paramvalue] ,[description] )
    VALUES ( N'ReadFromConfigurationFile' ,N'1' ,N'Send mail from mail server in configuration file' ); GO

    6 – Reiniciamos Database Mail ejecutando en Management Studio:

    USE msdb;
    GO
    EXEC dbo.sysmail_start_sp;
    GO

    De esta forma tendremos Database Mail funcionando correctamente en un entorno donde no usamos el puerto por defecto ni el Browser de SQL.

    Pablo Gavela López – Microsoft Customer Support Services

  • Esecuele Sin Fronteras

    No se Puede Realizar un SHRINK del Log de Transacciones porque está Marcado como “REPLICATION” (y la Replicación no ha Sido Configurada)

    • 0 Comments

    La pasada semana estuve trabajando en un caso con una base de datos de Microsoft Dynamics AX en la que no era posible reducir el tamaño (en Inglés, “shrink”) del log de transacciones. En el pasado encontré problemas similares con bases de datos de SQL Server 2005 que eran debidos a los llamados “ghost records” (en Castellano, “registros fantasma”); este hecho se encuentra discutido en el artículo Microsoft KB953991, pero en nuestro caso la base de datos era de SQL Server 2008.

    Hay varias razones por las que un log de transacciones puede no reducir su tamaño cuando se ejecuta una tarea de shrink, un listado de estas puede encontrase en este artículo de MSDN. Para encontrar la razón por la que el log de transacciones no puede ser truncado utilizamos la Vista Dinámica o DMV (del Inglés “Dynamic Management View”) sys.databases y buscando la información bajo la columna log_reuse_wait_desc. En la mayoría de las ocasones la descripción en esta columna será LOG_BACKUP, indicando que es necesario un backup del log de transacciones para poder relizar un truncado del mismo (y una posterior reducción del tamaño mediante SHRINK). En nuestro caso el resultado de esta consulta era REPLICATION, como se puede ver a continuación (en el ejemplo estoy utilizando la base de datos AdventureWorks):

    USE master;
    GO
    SELECT name, log_reuse_wait_desc, * FROM sys.databases
    WHERE name = 'AdventureWorks';

    untitled

    Tras discutir los detalles del caso con el cliente, encontramos que un proceso DBCC CHECKDB fue ejecutado en la base de datos varios días atrás utilizando la opción de recuperación ALLOW_REPAIR_DATA_LOSS. No estoy completamente seguro de que ambas acciones estén relacionadas pero en su momento me encontré con una situación similar en SQL Server 2005 en la que un log de transacciones de una base de datos aparecía marcaddo como REPLICTOIN después de ejecutar el comando DBCC CHECKDB con la misma opción de recupearción.

    En nuestro caso intentamos resestear el estado del log de transacciones utilizando el procedimiento almacenado de replicación sp_repldone, pero el procedimiento almancenado falló indicando que la base de datos no estaba configurada para replicación. Encontramos que la forma más rápida y sencilla de eliminar la marca “REPLICATION” del fichero de log de transacciones era configurar una replicación de tipo Snapshot (“Instantánea”) en la base de datos y, justo después, elminiar esta configuración del servidor.

    Para configurar una replicación de tipo Snapshot (“Instantánea”) utilizando SQL Server Management Studio puedes seguir los pasos descritos en este vídeo Tehcnet de 10 minutos de Ty Anderson (sólo en Inglés). La primera parte del vídeo muestra cómo configurar la instancia de SQL Server como su propio Publicador y Distribuidor mientras que la segunda parte mustra cómo configurar los Suscriptores. En nuestro caso no es necesario configurar ningún suscriptor cuando ejecutemos el Asistente de Configuración para la replicación Snaphsot, tan solo tenemos que tener en cuenta la siguiente información:

    • Seleccionamos la base de datos afectada como la base de datos para Replicación
    • No necesitmoa seleccionar todos los objetos para el proceso de Replicacion, podemos seleccionar una tabla cualquiera
    • Recordamos crear un Snapshot o Instantánea inicial cuando seamos preguntados por el Asistente
    • Podemos configurar la cuenta de servicio del Agent de SQL Server en las opcioens de seguridad ya que esta configuraión será sólo temporal

    Una vez el Asistente para la Replicación tipo Snaphot configure el entorno de replilcación correctamente, ejecutaremos la instrucción SELECT anterior; en este punto la descripción de la columna log_reuse_wait_desc nos deberá mostrar la etiqueta NOTHING o la etiqueta LOG_BACKUP:

    untitled2

    Si el estado de esta columna para el fichero de log de transacciones muestra todavía REPLICATION, ejecutaremos la siguiente instrucción:

    EXEC sp_repldone @xactid = NULL, @xact_sgno = NULL, @numtrans = 0, @time = 0, @reset = 1;

    A continuación necesitamos eliminar todos los objetos de replicación de la base de datos ya que no necesitamos mantenerlos. Inicialmente eliminamos la publicación (no necesitamos eliminar los suscriptores antes ya que no hemos configurado ninguno):

    USE AdventureWorks;
    GO
    EXEC sp_droppublication @publication = N'AW_Test_Publication'USE master
    GO
    EXEC sp_replicationdboption @dbname = N'AdventureWorks', @optname = N'publish', @value = N'false';

    En este ejemplo se puede ver que el nombre de nuestra Publicación es “AE_Test_Publication”. Este nombre será diferente en función de lo que hayamos escrito durante el asistente para configurar la Replicación. A continuación eliminamos el Distribuidor:

    USE master;
    GO
    exec sp_dropdistributor @no_checks = 1;

    Y finalmento nos aseguramos de que no queda ningún objeto de replicación en la bae de datos ejecutando el siguiente procedimiento almacenado:

    USE master;
    GO
    sp_removedbreplication 'AdventureWorks';

    Jorge Pérez Campo – Microsoft Customer Support Services

  • Esecuele Sin Fronteras

    Upgrade a SQL Server 2008 R2 en español en un Windows inglés.

    • 0 Comments

    Antes de empezar tenemos que aclarar que esta situación no está soportada. Las combinaciones soportadas para los distintos idiomas de Windows/SQL son las siguientes:

    - SQL Server Ingles puede instalarse sobre cualquier versión de Windows, sea cual sea su idioma.

    - SQL Server localizado para un idioma solo puede instalarse en un Windows del mismo idioma. También es posible instalarlo en un Windows ingles con el paquete “MUI” español instalado y activo para la cuenta que instala SQL.

    Para más información: http://technet.microsoft.com/en-us/library/ee210665.aspx

    Hace unos días me encontré con un caso en el que se pretendía actualizar una instancia de SQL Server 2008 a SQL Server 2008 R2 la cual finalizaba inesperadamente mientras se instalaban las “Support Files”

    image

    Error durante el upgrade de SQL Server 2008 a 2008 R2.

    Como podemos ver el “Locale ID” del paquete que queremos instalar es el 3082 (Español – Alfabetización Internacional) y la máquina local tiene el “locale” (LCID) en 1033 (Inglés – Estados Unidos)

    Como hemos comentado esta situación no está soportada pero nuestro objetivo es actualizar la instancia por tanto nos pusimos a analizar los logs de instalación de SQL Server. (Normalmente están localizados en “C:\Program Files\Microsoft SQL Server\{Version}\Setup Bootstrap\LOG” donde {version} es: 80 para SQL 2000, 90 para SQL 2005, 100 para SQL 2008 y 2008 R2

    En el archivo Summary del log nos encontramos:

    Exit code (Decimal):           -2068054013 (0x84BC0003)

    Exit facility code:            1212 (0x4BC)

    Exit error code:               3 (0x0003)

    Como podemos ver en: http://msdn.microsoft.com/en-us/library/ms681382(v=vs.85).aspx el error código 3 corresponde con: ERROR_FILE_NOT_FOUND

    Si vamos al final del log “Detailed_ComponentUpdate.txt” nos encontramos lo siguiente:

    2010-11-16 13:21:56 Slp: Target package: "D:\SQL Server 2008 EE\SQL_Svr_2008_R2\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi"

    2010-11-16 13:21:56 Slp: InstallPackage: MsiInstallProduct returned the result code 3.

    2010-11-16 13:21:56 Slp: Watson Bucket 1

    Como podemos ver el error nos da porque no puede encontrar D:\SQL Server 2008 EE\SQL_Svr_2008_R2\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi. El instalador prueba varias rutas antes de fallar así que ponemos una traza de procmon para ver que rutas intenta. El resultado de procmon es el siguiente.

    "12:03:43,4193775","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\x64\SqlSupport.msi","PATH NOT FOUND",""

    "12:03:43,4194404","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi","PATH NOT FOUND",""

    "12:03:43,7781168","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sql2008support\x64\SqlSupport.msi","PATH NOT FOUND",""

    "12:03:43,7781803","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sql2008support\SqlSupport.msi","PATH NOT FOUND",""

    "12:04:09,4823169","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\x64\SqlSupport.msi","PATH NOT FOUND",""

    "12:04:09,4824232","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi","PATH NOT FOUND",""

    "12:04:11,0054432","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sql2008support\x64\SqlSupport.msi","PATH NOT FOUND",""

    "12:04:11,0055509","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sql2008support\SqlSupport.msi","PATH NOT FOUND",""

    "12:04:18,7159743","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\x64\SqlSupport.msi","PATH NOT FOUND",""

    "12:04:22,8443950","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\x64\SqlSupport.msi","PATH NOT FOUND",""

    "12:04:22,8445045","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi","PATH NOT FOUND",""

    "12:04:22,9637093","setup100.exe","2680","CreateFile","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi","PATH NOT FOUND","Desired Access: Generic Read, Disposition: Open, Options: Synchronous IO Non-Alert, Non-Directory File, Attributes: N, ShareMode: Read, AllocationSize: n/a"

    "12:04:22,9919463","setup100.exe","2680","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi","PATH NOT FOUND",""

    "12:04:23,4191552","msiexec.exe","5508","QueryOpen","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi","PATH NOT FOUND",""

    "12:04:23,4192983","msiexec.exe","5508","CreateFile","D:\SW_SQLSrv2008R2Ent\1033_ENU_LP\x64\setup\sqlsupport_msi\SqlSupport.msi","PATH NOT FOUND","Desired Access:

    El instalador busca en varias rutas, pero siempre dentro de la rama 1033_ENU_LP que corresponde al idioma inglés.

    La primera impresión es que estábamos actualizando con el paquete equivocado y que la instalación original estaba en inglés. Cuando instalamos con el paquete ingles la instalación de las support files se hace correctamente pero al llegar a la comprobación de requisitos nos encontramos con el siguiente fallo que nos evita seguir la instalación.

    BlockCrossLanguageUpgrade – Failed

    ¡La instalación detecta que el idioma de SQL instalado y el del upgrade que queremos hacer son distintos!

    A continuación sacamos un listado de las instancias detectadas por el instalador:

      Sql Server 2008 X Database Engine Services 1033 Enterprise Edition 10.1.2531.0

      Sql Server 2008 X Database Engine Services 3082 Enterprise Edition 10.1.2531.0 

      Sql Server 2008 Full-Text Search 1033 Enterprise Edition 10.0.1600.22      

      Sql Server 2008 X Analysis Services 1033 Enterprise Edition 10.1.2531.0       

      Sql Server 2008 X Analysis Services 3082 Enterprise Edition 10.1.2531.0 

      Sql Server 2008 X Reporting Services 1033 Enterprise Edition 10.1.2531.0  

      Sql Server 2008 X Reporting Services 3082 Enterprise Edition 10.1.2531.0    

      Sql Server 2008 Management Tools - Basic 3082 Enterprise Edition 10.1.2531.0       

      Sql Server 2008 Management Tools – Complete 3082 Enterprise Edition 10.1.2531.0   

      Sql Server 2008 Client Tools Connectivity 3082 Enterprise Edition 10.1.2531.0    

      Sql Server 2008 Integration Services 3082 Enterprise Edition 10.1.2531.0 

    El instalador detecta que el motor de la base de datos, Analysis Services y Reporting Services de la instancia X están instalados en Español(3082) e Ingles(1033), algo que resulta bastante extraño.

    Cuando instalas SQL Server en un idioma que no coincide con el del sistema operativo es normal encontrar estas entradas en el log de SQL Server. Esto indica que la instalación es española y el sistema operativo es inglés.

    Para estar dentro de soporte sin reinstalar todo de nuevo la única solución es instalar los MUI(Multilingual User Interface) para el idioma en el que tenemos instalando SQL Server, podemos descargarlos desde aquí http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e9f6f200-cfaf-4516-8e96-e4d4750397ff&displaylang=en. Una vez que se instaló el MUI y se cambió la cuenta con la que se hace la instalación a español SQL se instaló sin ningún problema.

    Pablo Gavela López - Microsoft Customer Support Services

  • Esecuele Sin Fronteras

    Cómo sincronizar dos tablas usando SQL Server Integration Services (SSIS)-Parte II de II

    • 1 Comments

    Por favor, utiliza este link para acceder a la primera parte de este artículo.

    En esta segunda parte empezaremos con el mismo escenario donde una “tabla A” en una “base de datos A” tiene que ser sincronizada con una “tabla B” en una “base de datos B”. Durante la primera parte de este artículo discutimos cómo trabajar con los nuevos registros añadidos a la “tabla A” pero no explicamos cómo sincronizar los registros ya existentes que también eran actualizados en la “tabla A”.

    Dentro de SSIS podemos emplear diferentes métodos para recononcer aquellos registros que difieren entre la tabla origen y la tabla destino. Por ejemplo, podemos usar el operador EXCEPT para extraer esas diferencias y actualizar la tabla destino a continuación. En este caso vamos a usar la utilidad tablediff, que permite sincronizar ambas tablas rápidamente. Esta utilidad se emplea en la Replicación de SQL Server para recopilar información detallada acerca de las diferencias entre dos tablas.

    Lo mejor de tablediff es que no sólo permite comparar tablas sino que además permite generar un script que incorpora las diferencias de manera que las tablas pueden ser sincronizadas simplemente ejecutando el script. La limitacion de tablediff es que sólo funciona con servidores SQL Server así que si el objetivo es sincronizar dos tablas con algún otro motor de base de datos no se podrá emplear este método.

    En mi caso esto es lo que hice para sincronizar las dos tablas:

    1. Nos aseguramos de que la utilidad tablediff está intalada en el servidor de SQL Server. El ejecutable tablediff.exe se puede encontrar bajo el directorio C:\Program Files\Microsoft SQL Server\<version>\COM

    2. Añadimos tablediff.exe a la variable Path de Windows desde Computer Properties > Advanced System Settings > Advanced > Environment Variables > System Variables > PATH

    3. Nos aseguramos de que xp_cmdshell está habilitada ejecutando sp_configure desde SQL Server Management Studio (SSMS):

    Imagen1

    Por favor, lee detenidamente la documentación de xp_cmdshell y entiende las implicaciones de usar esta opción en el entorno. xp_cmdshell crea un proceso Windows con los mismos privilegios que los de la cuenta de servicio de SQL Server, lo que significa que los miembros del grupo sysadmins pueden acceder a funciones a las que no tendrían acceso sólo con su cuenta de Windows. Por defecto sólo los miembros del rol de servidor sysadmin en SQL Server están autorizados a ejecutar este procedimiento almacenado extendido. Si tu compañía no dispone de una política para asignar permisos a las cuentas de servicio de SQL Server donde se especifique claramente quién pertenece al este rol, evalua detenidamente la conveniencia de habilitar xp_cmdshell.

    4. Utilizando elmismo proyecto de SSIS que creamos durante la primera parte de este artículo, creamos dos tareas del tipo “Execute SQL Task”, bajo la sección de Control Flow en BIDS. La primera tarea, llamada “Execute tablediff” en el ejemplo, se encargará de ejecutar el comando tablediff.exe. Este es un ejemplo del código en mi caso:

    exec master..xp_cmdshell 'tablediff.exe -sourceserver SQL2008R2\KILIMANJARO64 -sourcedatabase SSISDBSource -sourcetable Customer -destinationserver SQL2008R2\KILIMANJARO64 -destinationdatabase SSISDBDest -destinationtable Customer -f C:\Temp\Diff'

    imagen2

    La parte importante en este paso es el modificador –f, que es quien se encarga de crear el script de T-SQL con los cambios que tienen que ser implmentados en la tabla de destino. Este es un ejemplo de este script generado automaticamente:

    imagen3

    La segunda tarea, llamada “Execute SQL Script” en el ejemplo, se encargará de ejecutar contra la base de datos el script generado en C:\Temp\Diff.sql, llevando a cabo las modificaciones requeridas desde la tabla origen en la tabla destino:

    imagen4

    5. Opcionalmente podemos combinar la tarea de “Data Flow” que creamos durante la primera parte de este artículo con estas dos tareas y disponer de un paquete de sincronización completo:

    imagen5

    Jorge Pérez Campo - Microsoft Customer Support Services

  • Esecuele Sin Fronteras

    Cómo sincronizar dos tablas usando SQL Server Integration Services (SSIS)-Parte I de II

    • 1 Comments

    Hay diferentes situaciones en las que un administrador de base de datos necesita mantener dos tablas sincronizadas. Una de estas situaciones se da cuando es preciso mantener una copia de una talba en un repositorio de Datawarehouse que es usado como solución de archivo o generación de informes.

    SQL Server proporciona un método robusto para mantener datos sincronizados en diferentes bases de datos usando Replicación, pero hay situacones en las que nuestra necesidad es sólo la de mantener sincronizadas un par de tablas y no deseamos tener que configurar una topología de replicación en la instancia.

    El siguiente artículo está divido en dos partes: la Parte I explica cómo actualizar una tabla destino con la información que es añadida en una tabla origen, mientaras que la Parte II explica cómo replicar o propagar cualquier cambio que suecede en la información ya existente de la tabla origen en la tabla destino.

    Este procedimiento se basa en el siguiente escenario: Una "tabla A" en la "base de datos A" replica periódicamente la nueva información añadida en una "tabla B" en la "base de datos B". La "tabla A" es actualizada periódicamente con nuevos registros y necesitamos copiar esa informaicón en la "tabla B". La implementación final tendrá el siguiente aspecto en SQL Server Business Intelligence Development Studio (BIDS):

     

    Veamos cómo funciona esto:

    1. "Source Table" es la "tabla A" en la "base de datos A" mientras que "Dest Table" es la tabla destino "B" en la "base de datos B". Empezamos creando dos conectores OLEDB diferentes en el componente de Data Flow en SSIS utilizando tanto la tabla origen como la tabla destino como orígenes de datos.

    2. Necesitamos realizar una operación de JOIN en los dos orígenes de datos anteriores para copiar la información que queremos copiar de una tabla en la otra. Para que este JOIN funcione correctamente los datos deben de estar ordenados; esto se encuentra descrito en el siguiente enlace de MSDN:

    En Integration Services, las transformaciones Mezclar y Combinación de mezcla requieren datos ordenados en sus entradas. Los datos de entrada deben estar ordenados físicamente, y se deben establecer opciones de ordenación en las salidas y en las columnas de salida del origen o en la transformación de nivel superior. Si las opciones de ordenación indican que los datos están ordenados, pero en realidad no lo están, los resultados de la operación de mezcla o combinación de mezcla son impredecibles.

    En el operador de "Merge Join" es donde separamos los datos que han sido añadidos en la tabla origen (los datos que necesitamos) de los que no han sido añadidos (los que no necesitamos) desde la última ejecución del paquete de SSIS. En nuestro caso en la talba origen (a la izquierda) se han includio todas las columnas que queremos mantener sincronizadas mientras que la tabla destino (a la derecha) contiene solo el registro que correponde a la Clave Primaria, la columna "No_" en este caso. Este sería la descripción de la tarea:

     

    Esta es la parte importante del proceso: el operador Left Outer Join recupera todos los registros en la tabla origen pero aquellos que no exiten en la tabla destino son recuperados como NULL en la columna "No_" usada en el Join (columna Join Key). Esto se encuentra también descrito en la documentación del producto:

    Para incluir todos los productos, independientemente de si se ha escrito una revisión para alguno de ellos, utilice una combinación externa izquierda ISO. Ésta es la consulta:

    LEFT OUTER JOIN incluye en el resultado todas las filas de la tabla Product, tanto si hay una coincidencia en la columna ProductID de la tabla ProductReview como si no la hay. Observe que en los resultados donde no hay un Id. de revisión de producto coincidente para un producto, la fila contiene un valor nulo en la columna ProductReviewID.

    3. A continuación necesitamos separar los datos que necesitamos de los que no han cambiado. Para ello usamos una tarea de "Conditional Split" que se encarga de salvar la información para aquellos registros donde el campo clave "No_" devuelve NULL; dicho de otra forma, se encarga de salvar la información sólo de los registros nuevos. Aquí se muestra una descripción de esta tarea en BIDS:

     

    4. Finalmente realizamos un INSERT en los datos resultantes del operador "Conditional Split" en la tabla destino, que es la misma que usamos también como origen al principio del todo.

    En este ejemplo la tarea de JOIN está configurada de tal modo que puede ser reutilizada tantas veces como sea necesario, los registros en la tabla destino no se duplicarán con la información de la tabla origen, sólo los nuevos registros serán incorporados en el destino.

    Jorge Pérez Campo - Microsoft Customer Support Services

  • Esecuele Sin Fronteras

    Cómo crear una cuenta de inicio de sesión en SQL Server para una cuenta de máquina

    • 0 Comments

    Hoy queremos traer un escenario relacionado con la administración de cuentas de inicio de sesión que, aunque en apariencia es sencillo, puede generar algo de confusión y hacernos perder más tiempo del deseado.

    NOTA: Queda fuera de este artículo las consideraciones generales relativas a las mejores prácticas de seguridad para SQL Server 2005, 2008 y 2008R2. Tan sólo dejar claro que, como norma general y dentro de lo posible, en lo que se refiere a la creación de cuentas de inicio se recomienda el uso de la autentificación integrada con Windows y que, dentro de esta opción, se empleen grupos usuarios de dominio de tipo local (domain local group / local groups) en lugar de cuentas individuales (domain account / local account). Para más detalles sobre estos temas recomendamos consultar los enlaces de referencia incluidos al final del artículo.

    Escenario inicial

    En determinadas circunstancias puede existir la necesidad de dar permisos de acceso a una cuenta de máquina (computer account) en una o más base de datos dentro de una instancia de SQL Server. Para ello es necesario crear una cuenta de inicio de sesión para la cuenta de máquina y luego crear las cuentas de usuario en cada una de las bases de datos necesarias con los permisos adecuados. También es verdad que lo habitual es dar acceso una cuenta de usuario de dominio o a un grupo de usuarios local de dominio, o incluso crear una cuenta propia de SQL Server.

    Un caso donde es necesario crear inicio de sesión para cuentas de máquina puede ser que un producto “cerrado” (como SCOM 2007 por ejemplo) requiera este tipo de configuración, o que un desarrollo a medida (como un desarrollo en ASP.NET por ejemplo) se ejecute bajo el contexto de la cuenta de sistema “NT AUTHORITY\System” de forma local o “NT AUTHORITY\NETWORK SERVICE” si se está accediendo de forma remota.


    Imagen 1: Es posible ver cuentas de inicio de sesión de máquina en el explorador de objetos de SQL Server. Estas cuentas se identifican fácilmente porque con un símbolo de dólar “$” al final del nombre.


    Normalmente sólo es necesario preocuparse de la creación de inicios de sesión para este tipo de cuentas cuando se está instalando un desarrollo a medida, ya que lo habitual es que la propia instalación de un producto cerrado se encargue de esta tarea. No obstante puede darse el caso en el que sea necesario mover una cuenta de máquina a una nueva instancia o crear una nueva cuenta en la misma, como por ejemplo migraciones o actualizaciones a versiones más recientes de la base de datos. Sea cual sea el motivo, si para el proceso se intenta utilizar la interfaz gráfica de SQL Server Management Studio, se puede comprobar que del todo imposible crear un inicio de sesión para una cuenta de máquina, ya que la propia ventana de selección de cuentas no permite incluirlas o enumerarlas.



    Imagen 2: La interfaz gráfica de SSMS no permite elegir el tipo de objeto (object type) máquina ya que no aparece tan siquiera listado.


    En cambio, en otros servicios, como la administración de cuentas de usuario del sistema operativo, es posible ver e incluir cuentas de máquina.


    Imagen 3: La consola de administración de usuarios sí permite elegir el tipo de objeto (object type) máquina.


    Si de todas formas se conoce y se intenta escribir el nombre completo de la cuenta de máquina, al chequearla o se intenta directamente aceptar en el cuadro de diálogo, el sistema mostrará un error indicando que no ha podido localizar el nombre especificado dentro de los objetos de tipo usuario, grupo o cuentas predefinidas.


    Imagen 4: Error al intentar introducir manualmente la cuenta de máquina.


    Resolución

    SQL Server Management Studio no permite la selección de cuentas de máquina desde la interfaz gráfica por características de diseño. La ventana de selección de cuentas del Directorio Activo pertenece a la librería común del sistema operativo, al igual que el cuadro de diálogo para abrir o guardar un fichero o el de seleccionar un directorio de destino. Cuando una aplicación desea utilizar esta ventana de selección de cuentas debe indicarle de antemano el tipo de objetos del Directorio Activo que desea que aparezcan listados y en este caso SSMS le solicita que muestre cuentas de usuario, grupos y cuentas predeterminadas. Esto no significa que en futuras revisiones no se cambie este comportamiento.

    En este caso la única forma posible de dar de alta una cuenta de máquina es a través de un comando Transact-SQL. Como recordatorio, la cuenta de máquina corresponde al nombre de la máquina (que puede obtenerse desde la línea de comandos con el comando HOSTNAME) precedido por el nombre del dominio donde se encuentra y seguido del símbolo de dólar ($). Es prácticamente seguro que esta opción no funcione para equipos fuera de dominio:

    De todas formas, la solución recomendada es, siempre que lo permita el producto o el desarrollo que utilice la cuenta, crear un grupo de usuarios local o local de dominio en la consola de administración de seguridad del sistema operativo y añadir la cuenta de máquina para luego crear una cuenta de inicio de sesión en SQL Server vinculada al grupo. También se recomienda como norma general evitar el uso de las cuentas predefinidas “NT AUTHORITY\System” o “NT AUTHORITY\NETWORK SERVICE” en los desarrollos que accedan a recursos tales como ficheros o bases de datos y que de forma alternativa se utilicen cuentas de usuario local o de dominio específicas para cada propósito. De esta manera se logra identificar qué servicio o aplicación en concreto está accediendo a cada recurso y se protege al sistema operativo de posibles fallos de seguridad.

    Referencias

    Cómo crear un inicio de sesión de SQL Server

    http://msdn.microsoft.com/es-es/library/aa337562.aspx

    CREATE LOGIN (Transact-SQL)

    http://msdn.microsoft.com/es-es/library/ms189751.aspx

    SQL Server 2005 Security Best Practices - Operational and Administrative Tasks

    http://sqlcat.com/whitepapers/archive/2007/12/16/sql-server-2005-security-best-practices-operational-and-administrative-tasks.aspx

    Directory Object Picker

    http://msdn.microsoft.com/en-us/library/ms675899(v=vs.85).aspx

     

    - Jesús Nacimiento Casanova, Microsoft Premier Field Engineer

  • Esecuele Sin Fronteras

    El equipo de soporte busca un experto en Analysis Services

    • 0 Comments

    Buenos días a todos

     

    Eres desarrollador? Conoces Analysis Services? Quieres dar soporte con nosotros? Compartir tu conocimiento en nuestro blog?

    Nuestro equipo de soporte quiere crecer y estamos buscando a gente motivada por la tecnología para dar soporte a nuestros clientes en España y el resto de Europa (siendo fundamental el inglés como idioma).

    Si estás interesado, háznoslo saber: https://careers.microsoft.com/JobDetails.aspx?ss=&pg=0&so=&rw=3&jid=28375&jlang=EN.

    Te estamos esperando!

     

    El equipo de Esecuelesinfronteras

     

     

  • Esecuele Sin Fronteras

    Error "no se puede abrir el archivo de datos <ruta_del_archivo>" accediendo a un fichero de texto en SSIS

    • 0 Comments

    La pasada semana me encontré con este error trabajando en un caso con uno de nuestros clientes. El error se producía en una instalación de SQL Server 2008 sobre Windows Server 2008, pero como podremos ver no es un error asociado a una versión concreta de Windows o SQL Server.

    En este caso el cliente estaba intentando ejecutar una tarea de Agente de SQL Server compuesta por 17 pasos distinto. Estos pasos eran de diferentes tipos: Ejecución de código Transact-SQL, scripts de ActiveX, ejecuciones de CmdExec, etc. El cliente estaba tratando de ejecutar estos pasos con una cuenta de Proxy, que es un medio para limitar el contexto de seguridad en el que un trabajo es ejecutado en SQL Server; aquí podemos encontar más información sobre en qué consiste una cuenta de Proxy en SQL Server:

    El Agente SQL Server permite al administrador de la base de datos ejecutar cada paso de trabajo en un contexto seguro que sólo tiene los permisos necesarios para realizar ese paso de trabajo, que está determinado por un servidor proxy del Agente SQL Server. Para establecer los permisos para un paso de trabajo concreto, cree un proxy que disponga de los permisos necesarios y, a continuación, asigne ese proxy al paso de trabajo. Se puede especificar un servidor proxy en más de un paso de trabajo. Para los pasos de trabajo que necesitan los mismos permisos se utiliza el mismo proxy.

    Ejecutando la tarea con la cuenta de Proxy se comprobaba que dos de los pasos fallaban con el siguiente mensaje de error:

    10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. Todos los derechos reservados. Iniciado: 15:27:31 Error: 2010-10-16 15:27:32.38 Código: 0xC020200E Fuente: Copiar Datos de AgentSource a AgentDestination: no se puede abrir el archivo de datos "V:\MySharedFolder\MyTextFile.txt". Error de error final: 2010-10-16 15:27:32.38 Código: 0xC004701A Fuente: Copiar Datos de AgentSource a AgentDestination.Pipeline de tarea flujo de datos: componente "FlatFileConnection" (46) falló la fase de pre-execute y devolvió el código de error 0xC020200E. Error de final DTExec: la ejecución del paquete devuelto DTSER_FAILURE (1). Iniciado: 15:27:31 finalizado de PM: 15:27:32 PM Elapsed: 0.687 segundos. Error en la ejecución del paquete. Error en el paso

    Si nuestra instalación de SQL Server es en idioma Inglés el mensaje mostrado será el siguiente:

    10.0.2531.0 for 64-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  15:27:31  Error: 2010-10-16 15:27:32.38     Code: 0xC020200E     Source: Copy Data from AgentSource to AgentDestination Task Flat File Source [1]     Description: Cannot open the datafile "V:\MySharedFolder\MyTextFile.txt".  End Error  Error: 2010-10-16 15:27:32.38     Code: 0xC004701A     Source: Copy Data from AgentSource to AgentDestination Task SSIS.Pipeline     Description: component "Flat File Source" (1) failed the pre-execute phase and returned error code 0xC020200E.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  15:27:31  Finished: 15:27:32  Elapsed:  0.687 seconds.  The package execution failed.  The step failed.

    En este caso el propietario de la tarea era la cuenta 'sa' de SQL Server de forma que nuestra primera prueba fue cambiar el propietario de la tarea a la cuenta de Proxy, pero esto no dió ningún resultado. Durante la resolución del problema trabajaba con el cliente a través de una conexión remota y observé que el disco V: al que hacía referencia el mensaje de error no existía en el servidor. El cliente me explicó que este disco era creado a través de una asignación de red ("mapeo") durante el primer paso de la tarea.

    La asignación de este disco V: tenía lugar en el contexto de seguridad de la cuenta de Proxy así que nuestra primera tarea era comprobar que esta cuenta tenía los permisos correctos para el procedimiento extendido del sistema xp_cmdshell, tal y como se describe in la siguiente página de MSDN:

    Cuando es llamada por un usuario que no pertenece a la función fija de servidor sysadmin, xp_cmdshell se conecta a Windows con el nombre de cuenta y la contraseña almacenados en la credencial con el nombre ##xp_cmdshell_proxy_account##. Si no existe esta credencial de proxy, xp_cmdshell registrará errores.

    Para crear la credencial de cuenta de proxy, debe ejecutar sp_xp_cmdshell_proxy_account. Como argumentos, este procedimiento almacenado utiliza un nombre de usuario y una contraseña de Windows. Por ejemplo, el siguiente comando crea una credencial de proxy para el usuario de dominio de Windows SHIPPING\KobeR que tiene la contraseña de Windows sdfh%dkc93vcMt0:

    EXEC sp_xp_cmdshell_proxy_account 'SHIPPING\KobeR','sdfh%dkc93vcMt0'

    En nuestro caso los permisos requeridos por la cuenta de Proxy estaban correctamente configurados así que intentamos realizar la asignación del disco de red fuera de la propia ejecución de la tarea, utilizando nuestar propia sesión en lugar de la de la sesión de la cuenta de Proxy, pero esto resultó en el mismo error. Tanto el cliente como yo seguíamos pensando que era precisamente esta asignación de red la que estaba causando el problema de modo que creamos una nueva tarea de Agente de SQL Server pero esta vez con un único paso para intentar aislar el problema; en nuestro caso el contenido del paso era un código similar al siguiente:

    Este prueba funcionó correctamente. La diferencia aquí era que la asignación de la unidad de red V: y la ejecución de la tarea de SSIS estaban teniendo lugar en un mismo paso mientras que en la tarea original la asignación se producía en el primer paso y la ejecución del paquete en un paso posterior. Como se explica en el artículo de KB180362, este era precisamente el problema en nuestro caso:

    Cuando el sistema establece una unidad redirigida, se almacena en cada usuario. Sólo el usuario sí puede manipular la unidad redirigida. El sistema realiza un seguimiento de unidades redirigidas basadas en identificador de seguridad de inicio de sesión del usuario (SID). El SID de inicio de sesión es un identificador único de sesión de inicio de sesión del usuario. Un único usuario puede tener varias sesiones de inicio de sesión simultáneos en el sistema

    Si un servicio está configurado para ejecutarse bajo una cuenta de usuario, el sistema se crear siempre una nueva sesión de inicio de sesión para el usuario y, a continuación, iniciar el servicio en ese nuevo inicio de sesión. Por lo tanto, el servicio no puede manipular las asignaciones de unidad que se establecen dentro del usuario de otras sesiones.

    Justo en el primer párrafo de este mismo artículo de KB se puede leer:

    Un servicio (o cualquier proceso que se ejecuta en un contexto de seguridad diferente) que el debe tener acceso a un recurso remoto debe utilizar el nombre UNC (convención de nomenclatura universal) para tener acceso al recurso. Los nombres UNC no sufren las limitaciones descritas en este artículo.

    En nuestro caso la solución fue tan sencilla como modifciar la asignación a la unidad V: por la ruta UNC (es decir, \\nombreservidor\nombrerecurso). Otra opción en este caso hubiese sido ejecutar la asignación de la unidad de red V: en el mismo paso de la tarea en la que se realizar la ejecución del paquete de SSIS.

    Jorge Pérez Campo - Microsoft Customer Support Services

     

     

  • Esecuele Sin Fronteras

    ¿Qué son los Ficheros de Tipo "sparse" y Qué Importancia Tienen para un DBA?

    • 0 Comments

    Durante la pasada semana trabajé junto con uno de mis compañeros del grupo de soporte a la Plataforma Windows en un problema acerca de la copia de seguridad en ficheros de datos de SQL Server. Mi compañero y yo aprendimos varias cosas en este caso y creo que merece la pena compartir esta información con vosotros.

    Nuestro cliente estaba utilizando Microsoft Data Protection Manager (DPM) para realizar copias de seguridad de los servidores Windows y las bases de datos de SQL Server y estaba encontrando errores precisamente al copiar los ficheros de base de datos. Durante nuestro trabajo en el caso el cliente encontró el siguiente artículo en un blog de MSDN que explicaba en detalle la causa del problema: "Did your backup program/utility leave your SQL Server running in a squirrely scenario?". Si nunca has oído hablar de los cheros de tipo sparse (traducido como "fichero disperso") y cómo estos afectan a SQL Server, te recomiendo que eches un vistazo a este post del blog del grupo de soporte para SQL Server.

    ¿Qué son los ficheros sparse?

    Los ficheros sparse son creados por SQL Server durante operacones como la creación de un snapshot o instantánea de una base de datos o durante la ejecución de un sentencia DBCC CHECKDB. En el caso de un snapshot el fichero sparse es borrado cuando el snapshot es borrado, mientras que en el caso de una operación de comprobación de consistencia de una base de datos con DBCC CHECKDB el fichero sparse es borrado automáticamente por SQL Server justo después de la ejecución. Puedes encontrar información completa sobre el papel de los ficheros "sparse" en los siguientes enlaces:

    Instantánea de base de datos interna

    DBCC CHECKDB utiliza una instantánea interna de la base de datos para la coherencia transaccional necesaria para realizar estas comprobaciones. Así se evitan problemas de bloqueo y simultaneidad cuando se ejecutan estos comandos. Para obtener más información, vea Descripción del tamaño de los archivos dispersos en instantáneas de bases de datos y la sección sobre el uso de la instantánea de base de datos interna DBCC en DBCC (Transact-SQL).

    Descripcion del tamaño de los archivos dispersos en instantáneas de bases de datos

    Los archivos dispersos son una característica del sistema de archivos NTFS. Inicialmente, un archivo disperso no incluye datos de usuario y no se le asigna espacio en disco para éstos. Para obtener información general sobre el uso de los archivos dispersos en instantáneas de bases de datos y el crecimiento de éstas, vea Funcionamiento de las instantáneas de la base de datos.

    Cuando se crea por primera vez, un archivo disperso ocupa poco espacio en disco. A medida que se escriben datos en él, NTFS le asigna espacio en disco de forma gradual. El tamaño de un archivo disperso puede aumentar en gran medida. Si una instantánea de base de datos se queda sin espacio, se marcará como sospechosa y se deberá quitar. Sin embargo, la base de datos de origen no se verá afectada y las acciones en ella continuarán normalmente.

    ¿Qué es lo que sucede?

    Los ficheros de tipo sparse son una funcionalidad ofrecida por el sistema de ficheros NTFS. Con determinadas versiones de NTFS.SYS la propiedad sparse de un fichero es incorrectamente transferida al fichero original, también llamado fichero "padre", del que el fichero de tipo sparse procede. Este sería un ejemplo de esta situación:

    1. Ejecutamos una sentencia DBCC CHECKDB on una base de datos SQL Server como parte de un proceso de mantenimiento. Esta acción se completa sin errores
    2. Realizamos una copia de seguriad de la base de datos con utilidades como DPM o alguna otra utilidad de backup
    3. En esta situación los ficheros originales de base de datos son marcados como de tipo sparse

    ¿Cuál es el problema?

    SQL Server no soporta operaciones de copia de seguridad o restauración en ficheros de tipo "sparse de forma que podemos ponernos en una situación de no-soporte sin ni siquiera ser conscientes de ello

    ¿Cómo puedo saber si estoy en esta situación?

    Ejecuta la siguiente consulta y comprueba si en la columna 'is_sparse' aparecen valores distintos de 0 (cero):

    use <DatabaseName>; SELECT is_sparse, * from sys.database_files

    El siguiente ejemplo muestra una base de datos con varios ficheros de este tipo:

     

    La herramienta de configuración de mejores prácticas para SQL Server SQL Server 2008 R2 Best Practices Analyzer incluye unan nueva relga para detectar cuándo una base de datos incluye ficheros de tipo "sparse". Se pueden encontrar más detalles en el artículo de la Knowledge Base KB2028447.

    ¿Qué puedo hacer para evitar este problema?

    Como primera medida debemos actualizar la versión de NTFS.SYS en los servidores Windows que alojan ficheros de base de datos. La última versión de NTFS.SYS evita que este problema ocurra. Lo siguiente es una lista de los hotfixs para NTFS.SYS que debemos tener en cuenta (estos enlaces hacen referencia a artículos de traducción automática):

    Para Windows Server 2003:

    No puede restaurar archivos de gran tamaño en el sistema de archivos NTFS cuando se eliminan todas las secuencias de datos que tienen atributos dispersos en la versión de 64 bits de Windows XP SP2 o en Windows Server 2003 Service Pack 2, http://support.microsoft.com/kb/973886/es-es

    Mensaje de error al ejecutar el comando "chkdsk" junto con el "/ v" cambiar en un equipo basado en Windows Server 2003: "Corregir archivo disperso segmento de registro, http://support.microsoft.com/default.aspx?scid=kb;es-es;932021

    Para Windows Server 2008:

    Informes del Administrador de disco incorrectas un tiempo después de que el sistema operativo se inicia en Windows Server 2008 o en Windows Vista de uso de disco, http://support.microsoft.com/kb/981891/es-es

    Para SQL Server 2005 y 2008:

    Ademas de las actualizaciones indicadas para Windows, debemos aseguarnos de que tenemos instalada la úlitma versión del Paquete de Servicio (Service Pack, SP) y Paquete Acumulativo (Cumulative Update, CU) tanto en SQL Server 2008 como 2005 para prevenir que este problema vuelva a suceder.

    ¿Cómo puedo solucionar el problema?

    Actualizar los servidores Windows y SQL Server con los últimos Paquetes de Servicio y hotfixes evitará que el problema vuelve a suceder pero no solucionará el problema si este ya existe. El post del grupo de producto mencionado inicialmente incluye el procedimiento para solucionar este problema tanto ten SQL Server 2005 como en SQL Server 2008.

    Otra opción para poser solucionar este problem es vaciar (en Inglés, empty) el contenido del fichero de base de datos a otro fichero dentro del mismo filegroup. Podemos realizar esta acción utilizando la opción EMPTYFILE del commando Transact-SQL SHRINKFILE, aquí se muestra un ejemplo:

    Este método no puede ser utilizado cuando el fichero con la propiedad sparse es el primer fichero del filegroup primario (PRIMARY) ya que este fichero contiene varios objetos del sistema que no puedes ser movidos.

    ¡Después de seguir estos pasos SQL Server todavía muestra los ficheros como "sparse"!

    Después de seguir estas indicaciones es posible que SQL Server siga mostrando los fichero como de tipo sparse a pesar de que la utilidad de Windows fsutil muestre los ficheros como no-sparse. Si este es el caso, debemos de asegurarnos de hacer un detach y a continuación un attach de estos ficheros; esto es necesario para que la información de la vista sys.database_files sea actualizada.

    Jorge Pérez Campo - Microsoft Customer Support Services

  • Esecuele Sin Fronteras

    Problemas de consumo de espacio en disco tras cambiar el modelo de recuperación de la base de datos MASTER

    • 0 Comments

    NOTA: No es nuestra intención en este artículo entrar en profundidad sobre los tres tipos de modelo de recuperación de una base de datos SQL Server o sobre el comportamiento del registro de transacciones. Para más detalles sobre estos temas recomendamos consultar los enlaces de referencia incluidos al final del texto.

    Escenario inicial

    Las versiones actualmente soportadas de SQL Server permiten la modificación del modelo de recuperación (Recovery Model) de todas sus bases de datos de sistema entre las tres opciones posibles: SIMPLE, BULK-LOGGED y FULL. Esto es está permitido por el sistema para todas las bases de datos de sistema, salvo la base de datos temporal tempdb la cual siempre está configurada con el modelo SIMPLE. De hecho, si intentamos cambiar tempdb de modelo usando la interfaz gráfica, obtendremos el siguiente mensaje error:

     

    Imagen 1: Error 5058 "Option 'RECOVERY' cannot be set in database 'tempdb'"

    Independientemente de si se trata de una base de datos de sistema o una base de datos de usuario, siempre que se modifique el modelo de recuperación de una base de datos cuya configuración inicial fuera SIMPLE a una configuración BULK-LOGGED o FULL, es necesario realizar una copia de seguridad completa (Full Backup) o diferencial (Differential Backup) para que esta modificación sea efectiva. En caso contrario la base de datos se seguirá comportándose como si estuviera configurada con el modelo SIMPLE. En cambio, la reconfiguración de un modelo FULL o BULK-LOGGED a SIMPLE es inmediata, sin necesidad de ninguna copia de seguridad.

    Imagen 2: Mensaje de error al intentar hacer una copia de seguridad del registro de transacciones de una base de datos cuyo modelo de recuperación es FULL o BULK-LOGGED pero que sigue comportándose como SIMPLE.

    Una vez efectiva la configuración del modelo de recuperación a FULL o BULK-LOGGED, es necesario realizar siempre una copia de seguridad periódica del registro de transacciones. Esta operación, aparte de permitir una restauración en un punto en el tiempo concreto, es necesaria para indicar al sistema que puede reutilizar espacio de los ficheros de la base de datos. Esa copia de seguridad, como se ha dicho, es necesaria aunque no suficiente, ya que pueden existir varios motivos que impidan la reutilización del espacio en los ficheros del registro de transacciones como transacciones abiertas durante mucho tiempo o datos pendientes de replicar a otros servidores.

    Independientemente del detalle sobre el comportamiento del registro de transacciones, es muy importante recordar tres cosas:

    • Sin una copia de seguridad del registro de transacciones, el espacio de los ficheros de registro de transacciones de la base de datos nunca es reutilizado. En el caso en el que la base de datos tenga permitido solicitar más espacio al sistema operativo para sus ficheros de registro de transacciones lo hará hasta el límite configurado o hasta consumir todo el espacio en el volumen donde se encuentra. En cualquiera de los casos, en el momento en el que no pueda obtener más espacio, la base de datos dejará de aceptar transacciones y por lo tanto dejará de funcionar.
    • Ningún otro tipo de copia de seguridad permiten la reutilización del espacio en los ficheros de registro de transacciones. Sólo la copia de seguridad del registro de transacciones o el cambio de modelo de recuperación a SIMPLE, con las implicaciones que esto último supone, pueden permitir la reutilización.
    • Este comportamiento es igual para todas las bases de datos que permiten cambio a modelo FULL o BULK-LOGGED, sean estas de sistema o de usuario.

    NOTA: En el apartado de referencias, al final del artículo, incluimos un enlace a nuestros libros en línea de ayuda para identificar los motivos por los que no se reutiliza el espacio en los ficheros del registro de transacciones.

    Por otro lado, otro aspecto importante es que:

    • Las versiones actualmente soportadas de SQL Server no permiten la copia de seguridad del registro de transacciones para la base de datos de sistema MASTER.

    Entonces ¿cuál es la consecuencia de la combinación de estos factores? Para conocerla, ejecutemos en una instancia de SQL Server de prueba (para este caso he utilizado SQL Server 2008 R2 y SQL Server 2005 Developer Edition) esta serie de comandos:

    USE [master]

    GO

    ALTER DATABASE [master] SET RECOVERY FULL

    GO

    BACKUP DATABASE [master] TO DISK = N'C:\Temp\master.bak'

    GO

    El resultado será aproximadamente el siguiente:

    Processed 384 pages for database 'master', file 'master' on file 1.

    Processed 3 pages for database 'master', file 'mastlog' on file 1.

    BACKUP DATABASE successfully processed 387 pages in 0.584 seconds (5.177 MB/sec).

     

    Después, para asegurarnos que el registro de transacciones contiene información nueva, ejecutamos otra copia completa de la base de datos:

    BACKUP DATABASE [master] TO DISK = N'C:\Temp\master.bak' WITH FORMAT

    GO

    El resultado será aproximadamente el siguiente:

    Processed 368 pages for database 'master', file 'master' on file 1.

    Processed 2 pages for database 'master', file 'mastlog' on file 1.

    BACKUP DATABASE successfully processed 370 pages in 0.650 seconds (4.663 MB/sec).

     

    Si ejecutamos la siguiente consulta y comprobamos que el modelo de recuperación de la base de datos MASTER (recovery_model_desc) es FULL y que el registro de transacciones (log_reuse_wait_desc) indica que la base de datos está esperando a una copia de seguridad del registro de transacciones: LOG_BACKUP.

    USE [master]

    GO

    SELECT [name], recovery_model, recovery_model_desc, log_reuse_wait, log_reuse_wait_desc

    FROM sys.databases

    WHERE name = N'master'

    GO

     

    Resultados:

     

    Imagen 3: Vista del estado de la base de datos del sistema MASTER en modelo de recuperación FULL

    De hecho, si, por ejemplo, ejecutamos repetidas veces la copia completa de la base de datos MASTER o realizamos alguna transacción en las tablas del sistema, se puede comprobar a través del explorador del sistema operativo como el fichero de registro de transacciones crece continuamente.

    Ahora si intentamos realizar una copia de seguridad del registro de transacciones de la base de datos MASTER a través de la consola de administración comprobamos que la opción está deshabilitada en la interfaz. De hecho, se puede comprobar que ni siquiera muestra la opción de una copia diferencial:

     

    Imagen 4: Ventana de copia de seguridad de la base de datos MASTER en la consola de administración.

    Si de todas formas intentamos hacer la misma operación utilizando Transact-SQL, recibiremos un mensaje de error indicando que esta acción no está permitida:

    USE [master]

    GO

    BACKUP LOG [master] TO DISK = N'C:\Temp\masterlog.trn'

    GO

     

    Resultado

     

    Imagen 5: Mensaje de error 4212 "Cannot back up the log of the master database".

    Como se ha comentado al principio del artículo, pese a que el mensaje anterior recomienda realizar una copia completa de la base de datos, esta operación no va conseguir que el sistema reutilice el espacio para el registro de transacciones ya que es absolutamente necesaria una copia de seguridad del registro. De hecho, cuantas más copias de seguridad completas se realicen, más espacio se consumirá en el registro de transacciones.

    El resultado es una base de datos MASTER cuyo registro de transacciones no para de crecer y a la que no se puede realizar una copia de seguridad del mismo.

    Resolución

    El motivo por el que no está permitida la copia de seguridad del registro de transacciones de la base de datos MASTER se debe al propio diseño de SQL Server: en un escenario de recuperación tras un desastre, a fin de mantener la estabilidad del servicio y la integridad de su información de sistema, no es posible que la base de datos MASTER se quede en ningún momento fuera de línea o no operativo.

    Si, por ejemplo, se intenta restaurar la base de datos MASTER y mantenerla fuera de línea o de sólo lectura recibiremos el siguiente error:

    USE [master]

    GO

    RESTORE DATABASE [master] FROM DISK=N'C:\temp\master.bak'

    WITH NORECOVERY

    GO

    Resultado:

     

    Imagen 6: Mensaje de error 3109 "Master can only be restored and fully recovered in a single step using a full database backup".

    De hecho si intentamos hacer una copia de seguridad diferencial de MASTER también recibiríamos un mensaje de error, en este caso más explícito que el de la copia de seguridad del registro de transacciones:

    USE [master]

    GO

    BACKUP DATABASE [master] TO DISK = N'C:\Temp\master.bak'

    WITH FORMAT, DIFFERENTIAL

    GO

    Resultado

     

    Imagen 7: Mensaje de error 3024 "You can only perform a full backup of the master database".

    Dado que el modelo de recuperación FULL es "de facto" incompatible con el funcionamiento de la base de datos MASTER, la única opción posible es cambiar y mantener el modelo de recuperación a SIMPLE, bien modificándola a través de la consola de administración o bien a través del siguiente comando:

    USE [master]

    GO

    ALTER DATABASE [master] SET RECOVERY SIMPLE

    GO

    Posteriormente podremos liberar espacio compactando el registro de transacciones bien a través de las tareas de la consola de administración o bien a través del siguiente comando de ejemplo:

    USE [master]

    GO

    DBCC SHRINKFILE (N'mastlog' , 0, TRUNCATEONLY)

    GO

    Para prevenir que en SQL Server 2000 se modifique el modelo recuperación de MASTER se puede desarrollar una tarea programada que evalúe y corrija la configuración. En SQL Server 2005 se puede además desarrollar un desencadenador a nivel de instancia (DDL Trigger) que evite el cambio de modelo. Ya en SQL Server 2008 y SQL Server 2008R2 es posible definir y exportar una política para que se evite esta modificación. La siguiente sección contiene información adicional respecto a estas opciones.

    Referencias

     

    Overview of the Recovery Models

    http://msdn.microsoft.com/en-us/library/ms189275(v=SQL.90).aspx

     

    How to stop the transaction log of a SQL Server database from growing unexpectedly

    http://support.microsoft.com/kb/873235

     

    Doctor, this SQL Server appears to be sick....

    http://blogs.msdn.com/b/psssql/archive/2009/11/24/doctor-this-sql-server-appears-to-be-sick.aspx

     

    Understanding DDL Triggers

    http://technet.microsoft.com/en-us/library/ms175941(SQL.90).aspx

    Administering Servers by Using Policy-Based Management

    http://msdn.microsoft.com/en-us/library/bb510667(v=SQL.100).aspx

     

    Enterprise Policy Management Framework with SQL Server 2008

    http://technet.microsoft.com/en-us/library/dd542632(SQL.100).aspx

     

Page 4 of 9 (81 items) «23456»