Preguntas y respuestas sobre SQLParticiones y comprobaciones de coherencia, entre otros temas

Paul S. Randal

P Accidentalmente, adjunté una base de datos de producción a un servidor SQL Server® 2005 y ahora estoy intentando adjuntarlo al servidor correcto, que usa SQL Server 2000. Lo he intentado simplemente separando la base de datos y adjuntándola al servidor SQL Server 2000, y también lo he probado con una copia de seguridad y una restauración, pero ninguno de estos dos métodos ha funcionado. ¿Por qué no funciona en mi servidor SQL Server 2000? Ésta es la única copia de la base de datos que tengo.

R La primera cosa que hay que tener en cuenta es la importancia de disponer de copias de seguridad. Los administradores de bases de datos suelen tener en mente la necesidad de realizar copias de seguridad para recuperar datos dañados o que han sufrido cualquier otro desastre. Pero usted ha dado con un problema menos evidente: algo ha fallado durante una actualización, aunque en este caso fuese una actualización accidental. La verdad es que siempre se debe tener una copia de seguridad completa y reciente de la base de datos por si a ésta le sucediera algo.

Una actualización, sea intencionada o accidental, es una operación en un solo sentido y es muy difícil invertir sus efectos. Cuando se actualiza entre versiones de SQL Server, se llevan a cabo una serie de pasos de actualización en la base de datos. Cada paso suele implicar algunos cambios físicos en la base de datos y el número de versión de la base de datos aumenta.

Por ejemplo, uno de los cambios más importantes que se realizan al actualizar una base de datos de SQL Server 2000 a SQL Server 2005 es el cambio de la estructura de catálogos del sistema de una base de datos (que se suelen denominar tablas del sistema o metadatos de la base de datos), que contienen distintos metadatos sobre tablas, índices, columnas, asignaciones y otros detalles con respecto a la estructura relacional y física de la base de datos.

A medida que se van llevando a cabo pasos de la actualización, el número de versión de la base de datos va aumentando. Por ejemplo, las bases de datos de SQL Server 7.0 tienen el número de versión 515, las bases de datos de SQL Server 2000 tienen el número de versión 539 y las bases de datos de SQL Server 2005 tienen el número de versión 611 (o 612 si la característica vardecimal está habilitada). De esta forma, SQL Server puede saber cuál fue el último paso de actualización realizado en la base de datos.

Las versiones de SQL Server no pueden leer bases de datos actualizadas a versiones más recientes de SQL Server (por ejemplo, SQL Server 2000 no puede leer una base de datos que se ha actualizado a SQL Server 2005). Esto ocurre porque las versiones anteriores no tienen el código necesario para interpretar el diseño de la base de datos ni las estructuras actualizadas. Y he aquí el problema con su base de datos, que se ha actualizado a SQL Server 2005 y por este motivo no se puede volver a adjuntar a SQL Server 2000.

Puesto que no dispone de una copia de seguridad de toda la base de datos, su única opción es exportar todos los datos de la base de datos actualizada y transferirlos manualmente a una nueva base de datos de SQL Server 2000. Siempre y cuando no haya usado ninguna característica nueva de SQL Server 2005, podrá crear la estructura de la base de datos, crear la base de datos en SQL Server 2000 y, finalmente, exportar o importar los datos.

Para crear la base de datos en SQL Server 2005, puede usar el Explorador de objetos de SQL Server Management Studio (haga clic con el botón secundario en la base de datos, seleccione Tareas y, a continuación, la opción de Generar secuencias de comandos). El asistente ofrece unas explicaciones muy claras y generará un script para crear todos los objetos, los índices, las restricciones, los desencadenadores, etc.

P Recientemente, hemos rediseñado nuestro esquema para usar particiones de tabla en la tabla principal, ya que hemos oído que de esta forma se mejora el rendimiento. La base de datos se almacena en una sola unidad de disco de 120 GB y la tabla se encuentra en un solo grupo de archivos. Las ventanas no se desplazan, sino que cada semana se van agregando particiones nuevas y todos los datos deben estar disponibles en línea. La mayoría de las consultas procesan datos de una sola semana y algunas buscan en los datos del último año. Parece más sencillo realizar esta operación en un solo grupo de archivos, ¿estoy en lo cierto o se me escapa algo?

R Aunque parezca más sencillo usar un solo grupo de archivos, si lo hace, es poco probable que pueda ver las ventajas de usar particiones. Los principales usos de las particiones son para un mantenimiento más eficaz de las bases de datos y para aumentar la disponibilidad de los datos en caso de un desastre, además se puede crear un esquema que optimice el rendimiento.

El ejemplo arquetípico de partición es para una tabla de ventas, con 12 particiones, cada una de las cuales representa los datos de las ventas de cada mes del año anterior. Al acabar el mes, la partición más antigua queda fuera (se archiva o se elimina) y entra una nueva partición. Éste es el entorno de ventanas que se desplazan al que hacía referencia. La partición del mes en curso es de lectura y escritura y los meses anteriores son de sólo lectura. Cada partición se almacena en un grupo de archivos independiente. Este esquema permite beneficiarse de todas las ventajas de las particiones, pero no es el mejor en todas las circunstancias.

Mi esposa, Kimberly, propuso un cambio en el esquema descrito más arriba que permite una indexación más eficaz. Supongamos que divide la tabla de ventas en dos tablas: una que contenga sólo la partición de lectura y escritura y otra que contenga las 11 particiones de sólo lectura, con una vista de particiones de ambas tablas.

Al hacerlo así, la tabla de lectura y escritura tiene un número inferior de índices, mientras que la tabla de sólo lectura puede tener más índices para permitir consultas de informes. Además, las operaciones de lenguaje de manipulación de datos (DML) en los datos de lectura y escritura son considerablemente más eficaces porque no tienen que mantener tantos índices no agrupados.

Así, las consultas de los datos de lectura y escritura no tendrán que procesar también los datos de sólo lectura. La eliminación de particiones en planes de consulta no es perfecta en SQL Server 2005, especialmente en casos con predicados complejos, pero ha mejorado mucho en SQL Server 2008. Para obtener más información, consulte el mensaje de Kimberly sobre este tema en el blog sqlskills.com/blogs/kimberly/default,month,2007-10.aspx#a71c70243-3d57-4940-9af7-a802b73f2f93.

Para mostrarle a lo que me refiero, le describiré algunas de las características que se habilitan al crear particiones de varios grupos de archivos.

Disponibilidad parcial de la base de datos Esta es la capacidad de una base de datos de estar en línea y ser accesible durante una situación de recuperación ante desastres siempre que el grupo de archivos principal esté en línea. Si sólo tiene un grupo de archivos, toda la base de datos estará inactiva mientras se realiza la restauración. Si los datos se encuentran en diversos grupos de archivos, sólo los grupos de archivos dañados estarán fuera de línea durante la restauración y su aplicación podrá seguir funcionando.

Restauración por etapas Este esquema es parecido a la disponibilidad parcial de la base de datos. Con un solo grupo de archivos, la unidad de restauración es una sola página o la base de datos completa. Con varios grupos de archivos, se puede restaurar sólo un grupo de archivos, con lo que la base de datos puede estar disponible parcialmente.

Mantenimiento de bases de datos con particiones Con cualquiera de los esquemas de particiones descritos más arriba se puede realizar la eliminación de fragmentación de índices por partición, incluso cuando todas las particiones se encuentran en un solo grupo de archivos. Pero con un solo grupo de archivos, se pierde la capacidad de realizar comprobaciones de coherencia por grupo de archivos, lo que puede reducir drásticamente la cantidad de datos que la comprobación de coherencia de la base de datos (DBCC) necesita para procesarse (y, a su vez, la cantidad de CPU y recursos de E/S que se usan).

En pocas palabras, aunque se pueden tener varias particiones dentro del mismo grupo de archivos, son muchas las ventajas de tener una correlación 1-1 entre particiones y grupos de archivos.

P Hace poco tiempo tuvimos un buen susto con uno de nuestros servidores de base de datos más moderno: una placa de memoria averiada estaba dañando datos. Lo descubrimos cuando empezaron a aparecer datos aleatorios en nuestra aplicación. Ejecutamos DBCC CHECKDB y encontramos todo tipo de daños. Por desgracia, también ocurrió en nuestras copias de seguridad, así que tuvimos que eliminar manualmente los datos incorrectos.

Para abreviar, reemplazamos el hardware averiado y activamos las sumas de comprobación de página. Nos gustaría realizar comprobaciones periódicas de coherencia pero no disponemos de una ventana de mantenimiento grande y nuestra base de datos de 2,4 TB tarda mucho tiempo en realizar comprobaciones. ¿Qué podemos hacer?

R La pregunta sobre cómo realizar comprobaciones de coherencia y otras operaciones de mantenimiento en una base de datos muy grande cada vez es más común. Muchos administradores de bases de datos dejan de realizarlas cuando se dan cuenta de que DBCC CHECKDB tarda más tiempo en ejecutarse del permitido por su ventana de mantenimiento. En algunos casos, la base de datos se usa las 24 horas del día, 7 días de la semana y no hay tiempo válido para renunciar a la CPU y la carga de E/S que requiere DBCC CHECKDB durante un período de tiempo prolongado.

Aparte de dejar de lado y no ejecutar ninguna comprobación de coherencia (que yo no recomiendo en absoluto), existen cuatro métodos que puede emplear. He ayudado personalmente a clientes a usar estos cuatro métodos.

Uso de la opción WITH PHYSICAL_ONLY de DBCC CHECKDB Un DBCC CHECKDB normal realiza gran cantidad de comprobaciones lógicas de coherencia y usa mucha CPU (ya que básicamente es una operación enlazada a la CPU). Al usar la opción WITH PHYSICAL_ONLY, las comprobaciones se limitan a comprobaciones muy rápidas de la coherencia del mapa de bits de la asignación DBCC CHECKALLOC y, a continuación, se leen y realizan auditorías de todas las páginas asignadas de la base de datos, con lo que se fuerza la comprobación de todas las sumas de comprobación de página presentes en las páginas por probar. De esta forma, DBCC CHECKDB se convierte en una operación ligada a acciones de E/S mucho más rápida (en realidad, a veces es incluso una vez o más rápido que un DBCC CHECKDB completo, que se puede traducir en un ahorro de tiempo muy considerable).

División de la carga de trabajo de la comprobación de coherencia Esta operación implica fragmentar las tablas de la base de datos en grupos del mismo tamaño (el método más sencillo es hacerlo por número de páginas) y, a continuación, mediante los comandos de DBCC CHECKTABLE, comprobar la coherencia de todas las tablas de un solo grupo cada noche. Así, por ejemplo, si comprobamos siete grupos, uno cada día, con un DBCC CHECKALLOC y un DBCC CHECKCATALOG todas las semanas, se obtiene el equivalente a un DBCC CHECKDB, aunque éste se realizaría en el transcurso de una semana.

Uso de particiones de tablas con varios grupos de archivos Las tablas más grandes de bases de datos muy grandes se pueden dividir en varios grupos de archivos. Un esquema de comprobación de coherencia de ejemplo puede ser ejecutar un DBCC CHECKFILEGROUP diario en el grupo de archivos que incluye la partición de lectura y escritura y también un DBCC CHECKFILEGROUPs semanal en los grupos de archivos que incluyen las particiones de sólo lectura. La lógica de este método es que se realiza una copia de seguridad exhaustiva de los datos de sólo lectura y que éstos no se usan en el procesamiento diario. Como resultado, no es necesario realizar comprobaciones de coherencia tan a menudo porque los daños no son tan críticos en estos datos.

Traslado de las comprobaciones de coherencia a otro servidor Esta opción implica restaurar las copias de seguridad completas y periódicas de la base de datos en otro servidor y ejecutar el DBCC CHECKDB en dicho servidor. Obviamente, esto descarga por completo la carga de trabajo que implican las comprobaciones de coherencia del servidor de producción. Sin embargo, el inconveniente es que si se encuentran datos dañados, se debe llevar a cabo una comprobación de coherencia en el servidor de producción; aunque esto rara vez ocurre.

Como se puede ver, los administradores de bases de datos tienen muchas opciones para realizar comprobaciones de coherencia en una base de datos muy grande, con lo que se reduce la carga de recursos adicional necesaria al realizar un DBCC CHECKDB completo. Como ya he mencionado anteriormente, he ayudado personalmente a clientes a usar estos cuatro métodos. Creo que estas opciones le serán de utilidad.

Sugerencia: Uso de desencadenadores para implementar lógica en los servidores

En algunos casos, es necesario implementar la lógica del servidor con desencadenadores, pero debe tener en cuenta ciertos inconvenientes. A continuación enumeramos algunos aspectos importantes que debe tener presente.

• Los desencadenadores son activados por instrucciones, no por filas. Una vez dicho esto, asegúrese de que incluye lógica adicional en la lógica de su desencadenador para controlar la situación en que varias filas o ninguna fila se ven afectadas por la instrucción (los desencadenadores son activados por instrucciones, aunque no haya ninguna fila afectada). Los datos afectados se encuentran en tablas virtuales de las instrucciones del lenguaje de manipulación de datos (DML). Estas tablas se pueden unir, lo que le permitirá trabajar con los datos.
• Los desencadenadores se ejecutan de forma sincrónica en su transacción. Recuerde esto cada vez que desee llamar a una aplicación externa u obtiene acceso a un recurso externo en el que no puede estar seguro de que recibirá una respuesta rápida o en un tiempo razonable. Por ejemplo, si activa una instrucción de actualización para una tabla y se activa un desencadenador en esa acción, la transacción (la parte implícita de la instrucción de actualización) no terminará hasta que no se haya completado toda la lógica del desencadenador. Si la aplicación o el proceso externos devuelven un código de error, SQL Server puede que cancele la transacción y revierta su transacción (según el control de errores y el código de error implementados). Así pues, si debe realizar alguna acción externa en un desencadenador y no es crítica para la transacción (o no debe ejecutarse dentro del mismo ámbito), la debe pasar a otro proceso y recopilar los datos de manera asincrónica. En SQL Server 2005 se introdujo SQL Server Service Broker, que puede realizar estas operaciones de forma asincrónica.
• Los errores provocados por una instrucción en un desencadenador son muy difíciles de encontrar. Si hay varias tablas incluidas en la transacción, recuerde examinar los desencadenadores si se produce un error e implemente el control de errores apropiado. Si cambia el esquema de su base de datos, asegúrese de realizar también un seguimiento de la lógica del desencadenador, ya que, de lo contrario, un desencadenador pequeño puede tener un importante impacto en el rendimiento y la estabilidad generales. La mayoría de las implicaciones que provienen de cambios en el esquema pueden comprobarse mediante Visual Studio® for Database Professionals, realizando comprobaciones automáticas del esquema a la vez que se edita el proyecto y realizando análisis de código estático con el fin de encontrar incoherencias en los tipos de datos.

—Jens K. Suessmeyer, consultor de bases de datos en Microsoft

Paul S. Randal es el director general de SQLskills.com y uno de los profesionales más valorados (MVP) de SQL Server. Ha trabajado en el equipo de Motor de almacenamiento de SQL Server en Microsoft de 1999 a 2007. Paul creó DBCC CHECKDB/reparación para SQL Server 2005 y fue responsable del motor de almacenamiento de núcleo durante el desarrollo de SQL Server 2008. Paul, que es un experto en recuperación ante desastres, en la alta disponibilidad y en el mantenimiento de bases de datos, participa frecuentemente en conferencias. Puede consultar su blog en SQLskills.com/blogs/paul.

© 2008 Microsoft Corporation y CMP Media, LLC. Reservados todos los derechos. Queda prohibida la reproducción parcial o total sin previa autorización.