SQL Q y A: traslado, optimización del rendimiento, copias de seguridad y creación de reflejos de la base de datos

  • Artículo

Paul S. Randal

Día de traslado de la nueva matriz

P: Nuestra matriz redundante de discos independientes (RAID) actual se está llenando rápidamente, por lo que debemos trasladar algunas bases de datos de SQL Server 2005 a otro lado. La nueva matriz está lista y he preparado el traslado de las bases de datos. Pero he descubierto que una de las bases de datos es un editor de duplicación transaccional y sé que eso significa que no puedo moverla. ¿Qué debo hacer?

R: Hay buenas noticias. Sólo SQL Server 2000 (y sus versiones anteriores) tiene una limitación que restringe el traslado de la base de datos de una publicación sin reinicializar la duplicación transaccional o alterar directamente las distintas tablas del sistema.

En SQL Server 2005 y SQL Server 2008, existe un proceso documentado que permite trasladar una base de datos sin modificar la duplicación transaccional, siempre que la base de datos permanezca adjunta a la misma instancia de SQL Server. Deberá permanecer cierto tiempo inactivo, dado que no hay forma de trasladar un archivo de base de datos si está en línea. El procedimiento es el siguiente:

En primer lugar, desconecte la base de datos usando el código a continuación. Si hay usuarios conectados a la base de datos, primero tendrá que eliminarlos para que el proceso sea satisfactorio:

ALTER DATABASE MyDatabaseName SET OFFLINE;

Luego, copie los archivos de datos en la nueva ubicación. Use la función de copiar en lugar de trasladar para revertir rápidamente en caso de que algo salga mal (de lo contrario, deberá realizar una restauración). A continuación, permita que SQL Server conozca la nueva ubicación de cada archivo con el siguiente código:

ALTER DATABASE MyDatabaseName 
MODIFY FILE
 (NAME = N'LogicalFileName',
 FILENAME = N'pathname\filename');

Una vez copiados físicamente todos los archivos y actualizadas sus ubicaciones en SQL Server, vuelva a poner en línea la base de datos usando el siguiente código:

ALTER DATABASE MyDatabaseName SET ONLINE;

Cierre del bloqueo temporal de la página

P: Me cuesta comprender algunos conceptos de la optimización del rendimiento. He leído varias veces que debo evitar los problemas de “bloqueo temporal de las páginas”. No entiendo que significa “página” o “bloqueo temporal”, ni por qué el bloqueo temporal de una página puede ser un problema. ¿Me pueden explicar todo esto?

R: Todos los datos de una base de datos de SQL Server se almacenan en archivos de datos. Internamente, estos archivos se organizan en secuencias de fragmentos de 8 KB llamadas páginas. Una página es la unidad básica de almacenamiento y E/S que SQL Server puede administrar. Las páginas normalmente se encuentran en los archivos de datos del disco y necesitan el caché de SQL Server (conocido como grupo de búferes) para ser leídas antes de procesar una consulta.

SQL Server utiliza distintos tipos de páginas para almacenar distintos tipos de datos relacionales (como las filas de una tabla, las filas de un índice no agrupado o los datos de texto/LOB). También hay páginas que almacenan parte de las estructuras de datos internas que SQL Server necesita para organizar y acceder a las páginas que almacenan los datos relacionales.

Un bloqueo temporal es un ligero mecanismo interno que SQL Server usa para sincronizar el acceso a una página dentro del caché. Hay dos tipos de bloqueos temporales de páginas que debe tener en cuenta: bloqueos temporales de páginas regulares y bloqueos de E/S de páginas. Si un subproceso de SQL Server debe esperar para adquirir uno de estos bloqueos temporales, esto indica un problema de rendimiento.

Cuando SQL Server espera que se lea parte del archivo de datos del disco, puede generar una espera de bloqueo temporal de E/S de la página. Si el bloqueo temporal de E/S de una página tarda mucho tiempo, generalmente indica un problema de rendimiento en el subsistema del disco subyacente (es decir, está sobrecargado).

Cuando varios subprocesos de SQL Server intentan acceder a la misma página del archivo de datos de 8 KB de la memoria, y existe una contención de acceso a la página, puede generarse una espera de bloqueo temporal de la página. Las situaciones más comunes implican el uso intenso de pequeños objetos temporales en la base de datos tempdb.

Una explicación más exhaustiva de cómo controlar y mitigar las esperas de bloqueo temporal de páginas se encuentra fuera del alcance de esta columna, pero puede encontrar más información en:

Análisis de instantáneas de bases de datos

P: Recientemente he descubierto las instantáneas de bases de datos. Y estoy considerando utilizarlas como alternativa al modelo de recuperación completa y las copias de seguridad de los registros. Crearé instantáneas aproximadamente cada una hora y, si algo sale mal, podré retirar los datos dañados. Parece implicar menos molestias y una forma más rápida de restauración. ¿Hay algún problema en la implementación de este cambio?

R: Sí, las instantáneas de bases de datos no son sustitutos prácticos o viables de la estrategia integral de recuperación ante desastres. Una instantánea de base de datos no proporciona la misma capacidad que una copia de seguridad de un registro transaccional en términos de recuperación completa ante desastres. La instantánea de base de datos no contiene una copia de todas las páginas de la base de datos, sólo de las que han cambiado desde su creación. Esto quiere decir que, si la base de datos se daña de alguna forma, su instantánea será inútil sin la base de datos subyacente. Sólo se trata de una recopilación de páginas dispares de la base de datos y no puede usarse para recuperación.

Una instantánea de base de datos le permitirá retirar los datos accidentalmente eliminados de la base de datos, siempre que la base de datos en sí esté disponible. Si una tabla eliminada de la base de datos aún existe en la instantánea, puede usarla, por ejemplo, para recrear dicha tabla eliminada.

Habiendo dicho esto, no es una buena idea crear muchas instantáneas de una base de datos (como sustituto de una copia de seguridad de un registro transaccional una vez cada media hora) debido a los posibles problemas de rendimiento. Antes de intercambiar una página de base de datos (consulte la explicación en la respuesta de la sección “Cierre del bloqueo temporal de la página”), primero debe copiar sincrónicamente la página en todas las instantáneas de bases de datos existentes que no contienen una versión de dicha página. A medida que crea más instantáneas de bases de datos, son más las páginas que debe copiar, lo que reduce el rendimiento.

Otro motivo para no crear muchas instantáneas de bases de datos es que cada una contendrá copias previamente modificadas de las páginas de la base de datos. Cada una aumentará de tamaño a medida que se modifiquen más bases de datos. Esto puede generar problemas de espacio en el disco, así como problemas de rendimiento.

Las instantáneas de bases de datos no están diseñadas para sustituir las copias de seguridad frecuentes de los registros. Puede leer un estudio más profundo de las implicaciones de rendimiento de las instantáneas de bases de datos en el artículo técnico “Consideraciones de rendimiento de las instantáneas de bases de datos bajo cargas de trabajo de E/S intensivas”.

Además, si usa el modelo de recuperación completa y las copias de seguridad de registros transaccionales, obviamente le interesa poder recuperar hasta el punto del desastre y/o utilizar restauraciones en un momento dado. (Para obtener una explicación de esto, consulte los artículos de julio de 2009 y noviembre de 2009, “Descripción general de las copias de seguridad de SQL Server” y “SQL Server: recuperación ante desastres mediante copias de seguridad”, respectivamente).

Espejito, espejito

P: Me han pedido que configure la creación de reflejos de nuestra base de datos, pero me preocupa que la creación de reflejos de la base de datos no solucione nuestro problema. Hemos padecido problemas de corrupción en nuestra red de área de almacenamiento (SAN), por lo que planeamos crear reflejos de la base de datos para que nos protejan de la corrupción. ¿Pasan los datos corruptos automáticamente a la base de datos reflejada? ¿Cómo puede ayudarnos la creación de reflejos de la base de datos?

R: Éste es un problema que genera mucha confusión. Al parecer, cualquier tecnología que proporciona una copia redundante de una base de datos es vulnerable a la propagación de datos corruptos de la base de datos principal a la reflejada (para utilizar la terminología de la creación de reflejos de la base de datos); pero en realidad, esto no ocurre.

El quid de la cuestión es comprender cómo se mantiene la base de datos reflejada. Los datos corruptos naturalmente se propagarán a la base de datos reflejada si el mecanismo de sincronización subyacente copia páginas de bases de datos completas de la base de datos principal en la reflejada. Por consiguiente, se colocará una página corrupta de la base de datos principal en la reflejada.

Sin embargo, la creación de reflejos de la base de datos específicamente evita esto porque no copia páginas de la base de datos de una base de datos a otra. La creación de reflejos de bases de datos copia los registros transaccionales de la base de datos principal en la reflejada. Los registros transaccionales describen cambios físicos de las páginas de la base de datos, pero no contienen las páginas reales en sí. (Para obtener una explicación completa de los registros transaccionales, el registro y la recuperación, consulte el artículo de febrero de 2009: “Descripción general de registro y recuperación en SQL Server”).

Incluso si una página de la base de datos se corrompe a través del subsistema de E/S subyacente de la base de datos principal, no hay forma de que dicha corrupción se propague directamente a la base de datos reflejada. Lo peor que puede pasar es que SQL Server no detecte una página corrupta (porque no están habilitadas las comprobaciones de página) y se use el valor de una columna corrupta para calcular un valor almacenado en la base de datos. El resultado incorrecto se propagará a la base de datos reflejada, un efecto de corrupción de segundo orden. Como mencioné, si las comprobaciones de página están habilitadas, dicha corrupción pasará desapercibida cuando se lea la página del disco y la corrupción de segundo orden no tendrá lugar.

Este comportamiento también explica por qué la ejecución de una comprobación de coherencia en la base de datos principal no proporciona información sobre el estado de coherencia de la base de datos reflejada y viceversa. Son dos bases de datos independientes que se mantienen sincronizadas mediante el envío de descripciones de cambios físicos a la base de datos, no a las páginas reales de la base de datos.

Nota del editor: Gracias a Kimberly L. Tripp de SQLskills.com por proporcionar un artículo técnico a la columna de este mes.

Paul S. Randales director administrativo de SQLskills.com, director regional de Microsoft y MVP de SQL Server. Trabajó en el equipo de Motor de almacenamiento de SQL Server en Microsoft desde 1999 hasta 2007. Escribió DBCC CHECKDB/reparación para SQL Server 2005 y fue responsable del Motor principal de almacenamiento durante el desarrollo de SQL Server 2008. Randal es experto en recuperación ante desastres, alta disponibilidad y mantenimiento de bases de datos, y es moderador habitual en conferencias en todo el mundo. Mantiene un blog en SQLskills.com/blogs/paul y puede encontrarlo en Twitter en Twitter.com/PaulRandal.

Contenido relacionado