Mes: agosto 2024

14
Ago
2024

Restauración a un punto en el tiempo

La restauración de una base de datos a un punto en el tiempo es una habilidad crítica para cualquier DBA. Esta técnica nos permite recuperar datos hasta un instante específico, minimizando así la pérdida de información tras un incidente. En este artículo, abordaremos el proceso de restauración en SQL Server y Azure SQL, dos plataformas muy similares pero en las que vamos a encontrar diferencias a la hora de proceder. Empezaremos con una breve introducción a la restauración a un punto en el tiempo y luego nos adentraremos en los detalles técnicos.

¿Qué es restaurar a un punto en el tiempo?

La restauración a un punto en el tiempo es una técnica avanzada que nos permite revertir una base de datos a un estado anterior específico. Esto es especialmente útil cuando ocurren errores de usuario, fallos en las aplicaciones o cualquier otro tipo de problema que afecte la integridad de los datos. Para SQL Server y Azure SQL, el proceso varía ligeramente, pero el principio básico es el mismo: utilizar copias de seguridad (backups) completas y de logs de transacciones (transaction logs) para reconstruir la base de datos hasta el momento deseado.

Restauración a un punto en el tiempo en SQL Server

Para realizar una restauración a un punto en el tiempo en SQL Server, necesitamos disponer de una copia de seguridad completa de la base de datos y todas las copias de seguridad del registro de transacciones hasta el punto en el tiempo que queremos restaurar. Es, por tanto, necesario tener la base de datos en un modo de recuperación FULL o Bulk-logged. 

Primero, asegurémonos de tener todas las copias de seguridad necesarias. La estrategia de copias de seguridad debería incluir backups completos y de logs de transacciones. Opcionalmente podremos disponer también de backups diferenciales. Los backups completos contienen toda la información de la base de datos, mientras que los diferenciales almacenan sólo los cambios desde el último backup completo. Los de log de transacciones, por su parte, registran todas las modificaciones realizadas en la base de datos desde el último backup de log. Una vez que confirmamos que disponemos de todas las copias de seguridad necesarias, procedemos a la restauración. 

Para empezar, restauramos la copia de seguridad completa usando el siguiente comando:

RESTORE DATABASE MiBaseDatos
FROM DISK = 'Ruta/De/Mi/BackupCompleto.bak'
WITH NORECOVERY;

El parámetro NORECOVERY es crucial, ya que permite aplicar backups adicionales antes de recuperar la base de datos. A continuación, restauramos cualquier backup diferencial (si existe):

RESTORE DATABASE MiBaseDatos
FROM DISK = 'Ruta/De/Mi/BackupDiferencial.bak'
WITH NORECOVERY;

Finalmente, aplicamos los backups de los registros de transacciones hasta el punto en el tiempo deseado:

RESTORE LOG MiBaseDatos
FROM DISK = 'Ruta/De/Mi/BackupLog1.trn'
WITH NORECOVERY;

RESTORE LOG MiBaseDatos
FROM DISK = 'Ruta/De/Mi/BackupLog2.trn'
WITH STOPAT = 'AAAA-MM-DD HH:MM:SS';

El parámetro STOPAT especifica el punto en el tiempo exacto al que queremos restaurar. Para completar el proceso, recuperamos la base de datos:

RESTORE DATABASE MiBaseDatos WITH RECOVERY;

Usar STANDBY para encontrar un momento dado desconocido

En ocasiones, necesitamos restaurar la base de datos a un momento específico pero desconocido, donde ocurrió un evento crítico. En estos casos, la opción STANDBY resulta muy útil. Esta opción nos permite restaurar la base de datos en un estado de solo lectura después de aplicar cada backup de registro de transacciones. De esta manera, podemos inspeccionar la base de datos en diferentes puntos del tiempo hasta encontrar el momento exacto que necesitamos.

Para usar STANDBY, primero restauramos la copia de seguridad completa y cualquier backup diferencial como antes. Luego, aplicamos los registros de transacciones de la siguiente manera:

RESTORE LOG MiBaseDatos
FROM DISK = 'Ruta/De/Mi/BackupLog1.trn'
WITH STANDBY = 'Ruta/De/Mi/UndoFile.bak';

RESTORE LOG MiBaseDatos
FROM DISK = 'Ruta/De/Mi/BackupLog2.trn'
WITH STANDBY = 'Ruta/De/Mi/UndoFile.bak';

El archivo UndoFile se utiliza para almacenar la información necesaria para revertir las transacciones no confirmadas, permitiendo que la base de datos se mantenga en un estado de solo lectura. Podemos repetir este proceso, aplicando registros de transacciones de uno en uno, verificando el estado de la base de datos después de cada restauración, hasta que encontremos el momento exacto que buscamos. Una vez que lo encontramos, aplicamos el resto de los registros de transacciones y finalizamos la restauración con WITH RECOVERY como hemos visto antes.

Restauración a un punto en el tiempo en Azure SQL

En Azure SQL, el proceso de restauración a un punto en el tiempo es más sencillo gracias a la infraestructura gestionada por Microsoft. Azure SQL realiza copias de seguridad automáticas y las almacena en un almacenamiento redundante.

Para restaurar una base de datos a un punto en el tiempo específico, usamos el portal de Azure o comandos de PowerShell/CLI. En el portal de Azure, navegamos a la base de datos que queremos restaurar, seleccionamos «Restore» y elegimos la opción «Point-in-time restore». Especificamos la fecha y hora a la que queremos restaurar y confirmamos la operación.

Mediante PowerShell, el comando sería similar a este:

Restore-AzSqlDatabase -FromPointInTimeBackup -ResourceGroupName "MiGrupoDeRecursos" -ServerName "MiServidor" -TargetDatabaseName "MiBaseDatosRestaurada" -PointInTime "AAAA-MM-DDTHH:MM:SSZ"

El parámetro -PointInTime debe especificar el punto en el tiempo exacto en formato ISO 8601. Este proceso crea una nueva base de datos a partir del punto en el tiempo especificado.

Conclusión

Restaurar una base de datos a un punto en el tiempo en SQL Server y Azure SQL es una técnica vital que todos los administradores de bases de datos debemos dominar. En SQL Server, la restauración implica una secuencia cuidadosa de operaciones de restauracion de backups completos, diferenciales y de logs de transacciones. En Azure SQL, el proceso está simplificado gracias a las funcionalidades gestionadas por la plataforma.

Dominar esta habilidad nos permite asegurar la continuidad del negocio y la integridad de los datos tras cualquier incidente. Además, nos da la tranquilidad de saber que podemos recuperar información valiosa hasta el instante preciso en que ocurrió el problema. Sin duda, una habilidad indispensable en el arsenal de cualquier DBA experto.

 Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
12
Ago
2024

Log de transacciones

Cuando trabajamos con SQL Server, uno de los elementos más críticos y, a menudo, menos comprendidos es el log de transacciones. Este componente de las bases de datos es esencial para asegurar la integridad y consistencia de nuestros datos. Gracias a este registro de transacciones, el motor de base de datos va a poder llevar a cabo una recuperación completa en caso de fallo. También se usa para la replicación de datos, una vez sincronizada una base de datos lo que se replica en tiempo real son las operaciones que registra el log. 

¿Qué es el Log de Transacciones?

El log de transacciones es una archivo de base de datos que se almacena con una estructura de datos secuencial y que registra todas las modificaciones hechas a la base de datos. Cada cambio, sea cual sea, desde una simple inserción hasta una transacción compleja, se documenta en el log antes de ser aplicado físicamente a la base de datos. Gracias a esta característica podemos estar seguros de que en caso de un fallo del sistema, vamos a poder recuperar la base de datos a su estado anterior sin pérdida de datos.

Entender cómo funcionan los logs de transacciones es, por tanto, clave para cualquier DBA ya que sin ellos no funcionan nuestras bases de datos. En este artículo hablamos un poco más sobre el tema. 

Por otro lado, los logs se organizan en una serie de segmentos denominados VLFs (Virtual Log Files). SQL Server gestiona estos VLFs automáticamente, pero es fundamental entender su funcionamiento para evitar problemas de rendimiento. Cuando el log de transacciones crece, se añaden más VLFs, y si no se gestionan adecuadamente, pueden llevar a una fragmentación del log, lo que afecta negativamente al rendimiento.También hablamos sobre ellos en el blog, puedes profundizar más sobre este tema aquí. 

Importancia del Modo de Recuperación

El modo de recuperación de la base de datos determina cómo se gestionan los logs de transacciones y, en última instancia, cómo se pueden recuperar los datos. SQL Server ofrece tres modos de recuperación: Simple, Completo y Bulk-Logged.

En el modo de recuperación Simple, el log de transacciones se trunca automáticamente después de cada checkpoint, limitando las opciones de recuperación a los backups completos. El modo de recuperación Full (completo), por otro lado, permite una recuperación total hasta el punto de fallo, siempre y cuando se mantenga un régimen adecuado de backups de logs de transacciones. Finalmente, el modo de recuperación Bulk-Logged ofrece un compromiso entre los dos anteriores. Este modo minimiza las escrituras en log durante operaciones masivas, como cargas de datos, a cambio de una menor granularidad en la recuperación.

Optimización y Mantenimiento del Log de Transacciones

Para asegurar un rendimiento óptimo del log de transacciones, es crucial seguir ciertas prácticas de mantenimiento y optimización. Primero, es esencial monitorizar el tamaño del log y evitar que crezca indefinidamente. Esto se logra configurando adecuadamente el tamaño inicial del log y sus incrementos automáticos, así como realizando backups regulares del log de transacciones en los modos de recuperación distintos a simple.

Otro aspecto vital es la gestión de los VLFs. Un número excesivo de VLFs puede degradar el rendimiento, por lo que es recomendable mantenerlos en un rango razonable. Esto se puede controlar ajustando los incrementos automáticos del tamaño del log, evitando incrementos pequeños que resulten en una gran cantidad de VLFs. 

Accediendo al Log de Transacciones

Acceder a los logs de transacciones nos puede parecer una tarea intimidante al principio, pero SQL Server nos proporciona varias herramientas para facilitar este proceso. La forma más común de acceso es a través de la función fn_dblog, que permite visualizar el contenido del log directamente desde SQL Server Management Studio (SSMS). Consultando esta función, obtendremos una lista detallada de todas las transacciones registradas. Esta operación, nos permite a los DBAs una visión sin filtros de cada operación, pero es importante manejar la cantidad de información con precaución, ya que puede ser abrumadora.

 SELECT * FROM fn_dblog(NULL, NULL)

 Interpretando los Datos del Log

Una vez que hemos accedido al log, el siguiente paso es interpretar los datos. Cada registro en el log contiene múltiples columnas que describen la transacción en detalle. Algunas de las columnas más relevantes incluyen:

  • Transaction ID: Identificador único de la transacción.
  • Operation: Tipo de operación realizada (inserción, eliminación, actualización, etc.).
  • Transaction Name: Nombre de la transacción, si se ha especificado.
  • Begin Time: Hora de inicio de la transacción.
  • End Time: Hora de finalización de la transacción.

Comprender estas columnas nos permite seguir el rastro de cada transacción y analizar su impacto en la base de datos. Por ejemplo, podemos identificar transacciones largas que podrían estar afectando el rendimiento o detectar operaciones no autorizadas.

Uso de DBCC LOG para el Análisis Avanzado

Para análisis más avanzados, la instrucción DBCC LOG ofrece una visión aún más profunda del log de transacciones. Ejecutando DBCC LOG (<nombre de la base de datos>, <opción de detalle>), podemos especificar el nivel de detalle que queremos obtener. Las opciones de detalle van desde 0 (menos detalle) hasta 4 (máximo detalle), permitiendo ajustar la cantidad de información según nuestras necesidades. Aunque es un procedimiento no documentado, esta herramienta es particularmente útil para investigar problemas específicos o realizar auditorías exhaustivas.

Recuperación de Datos y Auditoría de Seguridad

Uno de los usos más críticos de los logs de transacciones es la recuperación de datos. En situaciones donde se han producido errores o pérdidas de datos, los logs permiten revertir transacciones y restaurar la base de datos a un estado previo consistente. Para llevar a cabo esta tarea, podemos utilizar la instrucción RESTORE LOG junto con el archivo de log de transacciones correspondiente. Es vital tener un plan de backup y recuperación bien definido que incluya la utilización de estos logs.

En cuanto a la auditoría de seguridad, los logs de transacciones son una herramienta invaluable. Al monitorizar y analizar las transacciones registradas, podemos detectar actividades sospechosas o no autorizadas, lo que nos permite tomar medidas preventivas y correctivas a tiempo. Configurar alertas y auditorías basadas en el contenido de los logs nos proporciona una capa adicional de seguridad.

Conclusión

El log de transacciones de SQL Server es un componente fundamental que garantiza la integridad y recuperación de nuestros datos. Comprender su funcionamiento, mantenerlo optimizado y monitorizarlo continuamente nos permite asegurar un rendimiento óptimo y evitar sorpresas desagradables en nuestros entornos de producción más críticos. A través de una gestión cuidadosa y un enfoque proactivo, podemos maximizar la eficiencia de nuestras bases de datos y asegurar su disponibilidad y consistencia en todo momento.

 Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Publicado por Roberto Carrancio en Cloud, SQL Server, 1 comentario
09
Ago
2024

Mantenimiento imprescindible en SQL Server

El mantenimiento de bases de datos en SQL Server, como en todos los sistemas de bases de datos, es una actividad crítica para garantizar el rendimiento y la estabilidad de las aplicaciones que dependen de ellas. Un enfoque proactivo en la gestión de índices, estadísticas, integridad y copias de seguridad nos permite a los administradores evitar problemas potenciales antes de que se conviertan en fallos costosos. En este artículo, profundizaremos en cada uno de estos aspectos, proporcionando una visión detallada y práctica para mantener nuestras bases de datos en condiciones óptimas.

Mantenimiento de índices para mejorar el rendimiento

Los índices son componentes esenciales en nuestras bases de datos, ya que nos permiten un acceso rápido a los datos y mejoran significativamente el rendimiento de nuestras consultas. Prueba de ello es la cantidad de artículos que les hemos dedicado en el blog. Sin embargo, la eficiencia de los índices puede deteriorarse con el tiempo debido a la fragmentación, que ocurre cuando las páginas de datos se desorganizan. Este fenómeno afecta negativamente la rapidez de las búsquedas y actualizaciones y tenemos que prevenirlo y ponerle solución.

Para mantener un rendimiento óptimo, es crucial que monitoricemos y gestionemos los índices de forma periódica. No vamos a entrar en muchos más detalles pues ya le dedicamos un artículo completo a este tema. Simplemente vamos a ver que tenemos dos opciones de mantenimiento, la reorganización y la reconstrucción. La reorganización de índices es una técnica por la cual el motor de base de datos ajusta los índices sin necesidad de reconstruirlos completamente, lo que es en sí un proceso menos intensivo en recursos. Cuando la fragmentación es alta, la reorganización ya no es tan efectiva y debemos recurrir a la reconstrucción de índices, aunque sea un proceso más costoso en términos de tiempo y recursos. Deberemos planificar estas actividades de mantenimiento durante periodos sin uso o de baja actividad para minimizar el impacto a los usuarios.

Mantenimiento de estadísticas

Las estadísticas en SQL Server proporcionan al optimizador de consultas la información necesaria para decidir cuál es el mejor plan de ejecución posible para nuestra consulta, es decir, cómo ejecutar una consulta de la manera más eficiente posible. Sin datos actualizados, el optimizador puede hacer estimaciones inexactas, lo que lleva a un mal rendimiento.

La actualización regular de las estadísticas es una práctica recomendada para mantener el rendimiento del sistema. SQL Server ofrece opciones de actualización automática, pero en entornos con alta carga de trabajo o donde el rendimiento es crítico, puede ser más efectivo realizar actualizaciones manuales programadas. Esto asegura que las estadísticas reflejen con precisión la distribución actual de los datos, lo que es esencial para que el optimizador pueda generar planes de ejecución óptimos. Además no olvidéis que una actualización de estadísticas fuerza la recopilación de los planes de ejecución por lo que tenemos que buscar el equilibrio para mantenerlas actualizadas sin provocar un exceso de recompilaciones de los planes en caché.

Chequeo de integridad de las bases de datos

La integridad de los datos es un aspecto fundamental en la administración de bases de datos. Gracias a las herramientas de SQL Server, como DBCC CHECKDB, vamos a poder verificar la consistencia física de las bases de datos y de sus datos internos. Esta verificación es crucial para identificar y corregir errores en las estructuras de datos, que pueden surgir por diversas razones, desde fallos del hardware de almacenamiento hasta errores humanos o de consistencia en los datos.

Realizar verificaciones de integridad de manera regular ayuda a detectar problemas antes de que afecten la disponibilidad del sistema o provoquen pérdidas de datos. En caso de encontrarnos con corrupción, deberemos tomar acciones inmediatamente para reparar los daños antes de que puedan ir a más. Para esto intentaremos usar las herramientas disponibles como DBCC CHECKDB y, si no es posible restauraremos los datos desde una copia de seguridad anterior al incidente. 

Una vez resuelta la corrupción tendremos que buscar el origen del problema para evitar que se vuelva a repetir. Las causas más comunes de corrupción son fallos en el almacenamiento o interrupciones inesperadas del servicio (como fallos de alimentación). Es común encontrarse con problemas de corrupción también en instalaciones en sistemas operativos de escritorio que, para ahorrar energía, desconectan la alimentación de los discos duros. Esta configuración debe ser deshabilitada si se instala SQL Server en un sistema operativo de escritorio.

Copias de seguridad como medida de protección esencial

Las copias de seguridad deberían ser la piedra angular de cualquier estrategia de administración de bases de datos. Gracias a estas copias vamos a poder recuperar datos en caso de fallos o desastres, asegurando la continuidad del negocio. Es crucial diseñar un plan de copias de seguridad que considere la frecuencia de los respaldos, la retención y el almacenamiento seguro de los mismos.

Como ya vimos en el artículo que les dedicamos, existen diferentes tipos de copias de seguridad, como completas, diferenciales y de log de transacciones, cada una con su propia función y aplicabilidad. También es vital que realicemos pruebas de restauración periódicas para asegurarnos de que los procedimientos de recuperación funcionarán correctamente cuando sean necesarios. Además, es aconsejable que almacenemos las copias de seguridad en ubicaciones seguras y, si es posible, en lugares distintos geográficamente para protegernos contra desastres locales.

Soluciones de mantenimiento para bases de datos en SQL Server

Para que podamos gestionar eficazmente el mantenimiento de bases de datos en SQL Server podemos contar con herramientas y soluciones que automatizan y optimizan estas tareas. SQL Server ofrece varias herramientas nativas, mientras que otros desarrolladores han creado soluciones adicionales que pueden complementar o incluso superar las capacidades integradas. 

Herramientas nativas de SQL Server para el mantenimiento

SQL Server incluye varias características y herramientas integradas que facilitan la gestión del mantenimiento de bases de datos. Entre ellas, el Mantenimiento de Bases de Datos (Database Maintenance) y el Asistente para Mantenimiento (Maintenance Plan Wizard) son particularmente útiles para automatizar tareas como la reorganización y reconstrucción de índices, la actualización de estadísticas, la verificación de integridad y la realización de copias de seguridad.

Estas herramientas nos permiten a los DBAs configurar trabajos programados (jobs) de manera sencilla, utilizando una interfaz gráfica o scripts Transact-SQL. Sin embargo, aunque son bastante flexibles y suficientes para escenarios no muy complejos, es común encontrarnos con que estas herramientas pueden estar limitadas en cuanto a personalización y control exhaustivo sobre nuestras tareas de mantenimiento.

Soluciones avanzadas de Ola Hallengren

Una de las soluciones más reconocidas y ampliamente utilizadas en la comunidad SQL Server es el conjunto de scripts de mantenimiento desarrollado por Ola Hallengren. Estos scripts son muy configurables y nos proporcionan una solución completa para el mantenimiento de índices, copias de seguridad y verificación de integridad. De esta manera, nos permiten adaptar nuestras tareas de mantenimiento a las necesidades específicas de cada entorno.

Yo siempre recomiendo los scripts de Ola Hallengren ya que destacan por su eficiencia frente a las soluciones nativas. Además tienen una integración con el agente de SQL Server que nos facilita la programación y monitorización de las tareas de mantenimiento. Por último, pero no menos importante, estos scripts son gratuitos y se actualizan regularmente, lo que los convierte en una opción robusta y confiable.

Conclusión

Un mantenimiento adecuado y constante de las bases de datos en SQL Server es esencial para garantizar su rendimiento, disponibilidad y seguridad. La gestión eficiente de índices, la actualización estadísticas, las verificaciones regulares de integridad y una estrategia eficaz de copias de seguridad son pilares fundamentales en esta tarea. Adoptar un enfoque proactivo y planificado nos permite evitar problemas antes de que se conviertan en crisis, asegurando que nuestras bases de datos funcionen de manera óptima y que los datos estén siempre disponibles y protegidos.

 

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 0 comentarios
07
Ago
2024

Reducir bases de datos (shrink)

En nuestro pasado artículo hablamos del crecimiento de las bases de datos. Entre otras cosas, os expliqué las diferencias entre espacio utilizado y espacio total del fichero y a que eran debidas. También vimos los errores más comunes que nos pueden llevar a que nuestras bases de datos crezcan más de lo necesario. En el artículo de hoy vamos a ver las técnicas de reducción de espacio de una base de datos con la operación shrink. El objetivo es que entendamos cuándo y por qué usar esta herramienta y si tienes que usarla, al final del artículo te diré cómo.

Espacio ocupado y utilizado

Como explicamos en el pasado artículo, aunque muy por encima, el espacio que ocupan nuestras bases de datos SQL Server en disco no es el tamaño real que tienen ocupado los datos. Por temas de rendimiento, el espacio que se asigna al fichero es mayor, o debería serlo, al que realmente ocupan los datos. Esto es así porque los tiempos de las operaciones de escritura podemos dividirlos en dos, por un lado reservar el espacio necesario en disco para los datos y por último escribir los datos. Aunque con técnicas como la inicialización instantánea de ficheros podemos agilizar mucho el proceso de crecimiento de los ficheros, si el espacio ya está previamente reservado, es decir, ocupado por el fichero pero no utilizado, será mucho más rápido el proceso de escritura. 

Reducir una base de datos

Cuando hablamos de reducir una base de datos, lo único que vamos a poder hacer sin eliminar datos es liberar ese espacio que tenía el fichero reservado pero no en uso. Es decir, reducir el tamaño del fichero hasta como mínimo el tamaño máximo de los datos. En el pasado artículo ya hablamos de las técnicas para que los datos ocupen menos y esto nos puede servir para reducir su tamaño, sin embargo, ninguna de estas técnicas va a reducir por sí misma el tamaño de los ficheros, para eso tenemos que recurrir a una reducción de ficheros o de base de datos también conocida como shrink.

¿Qué es el shrink?

El shrink es un proceso mediante el cual se reduce el tamaño físico de un archivo de base de datos, ya sea de datos o de log. Este comando libera espacio no utilizado y lo devuelve al sistema operativo. Aunque a primera vista puede parecer una herramienta útil para gestionar el espacio en disco, es fundamental entender cómo funciona realmente y los efectos que puede tener en el rendimiento de la base de datos. Empecemos por el principio, podemos hacer un shrink de toda la base de datos o de solo unos ficheros (ya sean de datos o de log), esta última opción es la más recomendable ya que nos permite un mayor control sobre la operación. 

Tipos de Shrink

Por otro lado, existen tres tipos de shrink, debido a que solo se puede liberar el espacio libre al final del fichero nos encontramos con un shrink truncateonly donde solo se libera ese espacio libre al final del fichero pero el espacio que hay entre los datos (fragmentación) sigue ocupado, este tipo de shrink es el más rápido y menos invasivo. 

El tipo más efectivo para liberar espacio es cuando definimos un tamaño que será el tamaño total que ocupe el fichero tras el proceso, en este caso, para conseguir liberar todo el espacio, SQL Server moverá físicamente los datos dentro del fichero eliminando los espacios libres y dejándolos al final para así poder liberar todo ese espacio. Esta operación en ficheros de datos es complicada, no solo por lenta, sino porque desfragmenta los índices. Al mover los datos de sitio los índices ya no apuntan al sitio correcto y hay que reconstruirlos después o no serán usables. El problema con esto, es que al reconstruir los índices, SQL Server crea un nuevo índice y al terminar borra el antiguo y si, durante este proceso vuelve a consumir espacio del fichero de datos. En concreto el espacio que ocupa el índice. 

Por último, cuando tenemos varios ficheros de base de datos del mismo filegroup podemos hacer un shrink en uno de ellos que lo vacíe por completo moviendo los datos al resto de ficheros. Esto también nos va a generar los problemas de fragmentación comentados anteriormente.

Ventajas del Uso del Shrink

Una de las ventajas más obvias del shrink es la liberación de espacio en disco. Esto nos será especialmente útil en situaciones donde el almacenamiento sea limitado o costoso. Además, el shrink puede ser una solución rápida para situaciones de emergencia en las que se necesita liberar espacio inmediatamente. Podemos ejecutar un shrink de forma fácil, incluso podemos programarlo en jobs para que se ejecute automáticamente. Incluso podemos configurar nuestras bases de datos para que liberen el espacio libre por defecto de manera automática.

Otra ventaja es que el shrink puede ayudar en la gestión de archivos de log de transacciones, que a menudo crecen considerablemente en bases de datos con muchas transacciones. En este contexto, el shrink puede reducir el tamaño de estos archivos después de una copia de seguridad del log, evitando así que ocupen espacio innecesario. En este mismo contexto, podemos hacer un shrink sobre la base de datos tempdb cuando haya crecido más de la cuenta debido a una transacción grande.

Inconvenientes y Riesgos del Shrink

A pesar de sus ventajas aparentes, como ya hemos comentado, nos podemos encontrar con muchos inconvenientes si ejecutamos un shrink sin ser cuidadosos. Uno de los principales problemas es la fragmentación de los índices. El proceso de shrink puede reorganizar los datos de manera que los índices queden fragmentados, lo que puede degradar significativamente el rendimiento de las consultas. La fragmentación aumenta el tiempo de respuesta de las consultas y la carga sobre el sistema, lo cual es contraproducente en entornos de alta demanda.

Además, el shrink es un proceso que consume recursos, muchos recursos. Durante su ejecución, puede aumentar la carga de trabajo del servidor, afectando a otras operaciones. Tendremos que tener un especial cuidado en bases de datos grandes o en sistemas con recursos limitados. También es importante mencionar que el espacio liberado por el shrink no se puede recuperar sin expandir el archivo nuevamente, lo cual podría ser necesario si la base de datos vuelve a crecer rápidamente.

Prácticas Recomendadas para el Uso del Shrink

Dado que el shrink puede tener efectos adversos en el rendimiento, es crucial seguir ciertas prácticas recomendadas para minimizar sus desventajas. Primero, no debemos usar el shrink como una solución de gestión de espacio a largo plazo. Normalmente si ya hemos utilizado un espacio vamos a necesitarlo en un futuro. Por tanto, es mejor emplearlo en situaciones específicas, como después de la eliminación masiva de datos o en la gestión de archivos de log de transacciones. Como ya habrás adivinado, cuando hacemos un shrink del log de transacciones o de la tempdb no vamos a vernos afectados por la fragmentación de índices.

También es recomendable realizar el shrink en momentos de baja actividad para minimizar el impacto en el rendimiento del sistema. Posteriormente, deberemos realizar una reconstrucción de índices para solucionar la fragmentación causada por el proceso de shrink. Esto ayuda a restaurar el rendimiento óptimo de las consultas.

Por último, debemos tener una previsión del espacio que van a necesitar nuestros ficheros de bases de datos y reducirlos siempre dejando el suficiente espacio libre para unas escrituras óptimas.

Alternativas al Shrink

El shrink más eficiente es el que no se hace. En este sentido, existen alternativas que pueden ser más adecuadas en ciertas circunstancias. La gestión proactiva del espacio, como la eliminación de datos obsoletos o el archivo de datos antiguos, puede reducir la necesidad de realizar un shrink. Además, la implementación de prácticas de mantenimiento regulares, como el reindexado y la actualización de estadísticas, puede ayudar a mantener la base de datos en buen estado sin los efectos adversos del shrink.

Conclusión

El shrink en SQL Server es una herramienta que debe ser utilizada con precaución y entendimiento. Aunque puede ofrecer una solución rápida para liberar espacio en disco, sus efectos secundarios, como la fragmentación de índices y el consumo de recursos, deben ser cuidadosamente gestionados. Es fundamental considerar el shrink como una herramienta de última instancia y explorar alternativas y prácticas de mantenimiento regulares para mantener una base de datos saludable y eficiente. La clave está en el balance y en la planificación proactiva, asegurando que el rendimiento y la integridad de los datos no se vean comprometidos.

Si has llegado hasta aquí esperando ver cómo hacer un shrink te diré que este no era el objetivo de este artículo, tienes eso en un vídeo aquí. Hoy quería contarte todo lo que rodea a esta operación, para mi, es más importante saber cuándo y por que hacer un shrink que saber hacerlo.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo.

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 0 comentarios
05
Ago
2024

¿A qué se debe el crecimiento excesivo de bases de datos?

El crecimiento descontrolado de las bases de datos es una preocupación común para los administradores de sistemas y bases de datos. El aumento natural de los datos puede ser una explicación pero, en ocasiones, este crecimiento puede estar influenciado por errores de modelado, configuraciones inadecuadas y prácticas de gestión poco eficaces. En este artículo, abordaremos algunos errores comunes que contribuyen a este problema y veremos soluciones prácticas para tratar de evitarlo.

Crecimiento normal de los datos

Antes de entrar en detalles sobre cómo controlar el crecimiento de nuestros datos tenemos que dejar clara una cosa. Tu base de datos va a crecer, es el comportamiento común. El crecimiento natural y orgánico de las bases de datos es un fenómeno esperado en cualquier sistema en funcionamiento. A medida que se almacenan más datos, ya sea por la expansión de la empresa, el aumento de las operaciones o la recopilación de información de clientes y transacciones, es normal que las bases de datos crezcan. Este crecimiento, aunque esperado, debe ser gestionado adecuadamente para evitar problemas de rendimiento y costes innecesarios.

Crecimiento por errores de modelado de bases de datos

Una de las áreas fundamentales donde se pueden originar problemas de crecimiento en bases de datos es en el diseño y modelado de la propia base de datos. Errores como la falta de normalización adecuada o la sobrecarga de índices pueden llevar a un uso ineficiente del espacio y un crecimiento innecesario de la base de datos.

Falta de normalización adecuada

Una de las causas más frecuentes del crecimiento innecesario de bases de datos que he podido contemplar a lo largo de los años es una falta de normalización adecuada. La normalización ayuda a minimizar la redundancia de datos y garantiza la integridad referencial. Sin embargo, cuando no se aplica correctamente, puede resultar en la duplicación de datos a lo largo de varias tablas, inflando el tamaño total de la base de datos.

Para ponerle solución, es recomendable revisar el diseño de las bases de datos para asegurar que estén normalizadas correctamente. Aunque en algunas situaciones específicas pueda ser necesario que  desnormalicemos los datos para mejorar el rendimiento, sobre todo en entornos analíticos y de BI, esta decisión debemos tomarla con precaución y basándonos en un análisis sólido de las necesidades de la aplicación.

Sobrecarga de índices

Otro de los errores que más se cometen es el uso excesivo de índices. Es tan común ver bases de datos con índices innecesarios o duplicados como bases de datos sin índices. Este es otro problema que puede contribuir al crecimiento de las bases de datos ya que los índices nonclustered son objetos separados de la tabla que requieren su propio almacenamiento duplicando los datos en cada índice que aparezcan. Aunque los índices son fundamentales para acelerar las consultas, mantener demasiados puede llevar a un aumento innecesario en el tamaño de la base de datos, además de afectar el rendimiento durante las operaciones de inserción, actualización y eliminación.

La solución es sencilla, revisar los índices que no se utilizan y eliminarlos, pero a la vez requiere de conocimientos avanzados tanto de administración como del uso de la base de datos para no eliminar un índice que sí sea importante para el rendimiento. Otra buena práctica es usar índices filtrados cuando sea posible ya que pueden proporcionar beneficios de rendimiento específicos sin un aumento significativo en el espacio de almacenamiento.

Crecimiento por configuraciones inadecuadas en SQL Server

Además del diseño de la base de datos, la configuración del servidor SQL también puede influir significativamente en el tamaño y rendimiento de la base de datos. Configuraciones inadecuadas, como el modo de recuperación FULL sin una correcta gestión de los logs de transacciones, o una configuración incorrecta del tamaño del archivo, pueden provocar un crecimiento inesperado y descontrolado.

Modo de recuperación FULL y log de transacciones

El modo de recuperación FULL es esencial para una recuperación completa en caso de desastres, ya que registra todas las transacciones. Sin embargo, si no se gestionan adecuadamente, los logs de transacciones pueden crecer de forma incontrolada, ocupando mucho espacio en disco. Cuando configuras este modo de recuperación tienes que habilitar una buena política de backups con backup log frecuentes o vas a encontrarte con problemas de crecimiento del log descontrolado.

Como hemos dicho la solución es sencilla en este caso, basta con implementar una estrategia de backups regulares de los logs de transacciones. Esto no solo controla el tamaño del log, sino que también es crucial para la recuperación de datos. Es importante revisar la frecuencia de los backups y ajustar las políticas de recuperación según las necesidades de la organización. Si no es necesario este tipo de copias regulares deberías replantearte el modo de recuperación de tu base de datos.

Configuración inadecuada del tamaño del archivo

Es importante entender la diferencia entre el tamaño del fichero y el tamaño de los datos, ya que esta distinción puede proporcionar una visión más clara sobre cómo se está utilizando el espacio en la base de datos y qué medidas pueden ser necesarias para optimizar su rendimiento y almacenamiento. En SQL Server los archivos de base de datos se dimensionan más allá del tamaño real que tienen ocupado por los datos para que las futuras escrituras sean más rápidas. Este espacio libre en los ficheros es importante, por tanto, para el rendimiento pero hay que saber gestionarlo. Una configuración inicial incorrecta del tamaño de los archivos de datos y logs, junto con opciones de autogrowth mal configuradas, puede resultar en un crecimiento fragmentado y subóptimo de los archivos. Esto puede llevar a un uso ineficiente del espacio en disco y afectar negativamente el rendimiento. 

Para evitar estos problemas debemos configurar adecuadamente el tamaño inicial de los archivos de datos y logs basándonos en las previsiones de crecimiento de la base de datos. Sin embargo, las bases de datos cambian con el tiempo y deberemos ajustar los incrementos de autogrowth para minimizar la fragmentación y asegurar un uso eficiente del espacio en disco.

Crecimiento por errores de aplicación

Los errores en el diseño y configuración no son las únicas fuentes de problemas. Las aplicaciones que interactúan con la base de datos pueden causar un crecimiento descontrolado, especialmente si no gestionan adecuadamente las transacciones o si insertan datos de manera ineficiente. 

Errores de aplicación en la gestión de transacciones

Uno de los errores más comunes que puede llevar al crecimiento descontrolado de los logs de transacciones es el manejo inadecuado de las transacciones por parte de las aplicaciones. Esto incluye transacciones que permanecen abiertas por mucho tiempo o que no se cierran correctamente, lo cual puede causar un aumento innecesario en el tamaño del log.

Debemos revisar y optimizar el código de la aplicación para asegurarnos de que las transacciones se manejen de manera eficiente. Es crucial que las transacciones sean lo más cortas posibles y que se cierren correctamente para evitar el crecimiento innecesario del log. Prestaremos una atención especial a las transacciones de larga duración o aquellas que se producen con demasiada frecuencia.

Errores por insertar datos erróneamente

Otra causa de crecimiento desmedido es la inserción de datos redundantes debido a errores de lógica en la aplicación o a escrituras descontroladas en tablas de log de actividad de las aplicaciones o procesos. Esto puede suceder cuando las aplicaciones insertan datos duplicados debido a fallos en la validación o la falta de controles adecuados para evitar duplicados.

Para evitarlo, deberemos implementar mecanismos de validación en el nivel de la aplicación para prevenir la inserción de datos redundantes. Además, utilizar restricciones de unicidad y primary keys en la base de datos para garantizar la unicidad de los registros. Las tablas de auditoría o de log deberán limpiarse frecuentemente y revisar que la información registrada es útil. Por ejemplo, una buena práctica si un proceso tiene una lógica de reintentos es solo registrar una vez el error y poner una columna con un contador de repeticiones si el error es el mismo en cada reintento.

Evitar el crecimiento con mantenimiento

La gestión y mantenimiento regulares de nuestros datos es fundamental para controlar el tamaño de las bases de datos SQL Server. Esto incluye la limpieza de datos obsoletos y una monitorización constante del rendimiento y el uso del almacenamiento. Sin una gestión adecuada, es fácil que el crecimiento se descontrole, afectando el rendimiento y la eficiencia del sistema.

Limpieza de datos obsoletos

Las bases de datos tienden a acumular datos obsoletos o redundantes que ya no son útiles para la operación actual, contribuyendo al crecimiento desmedido. Esto es especialmente problemático en sistemas que carecen de políticas de limpieza o archivado de datos. Establecer procedimientos regulares de limpieza para eliminar registros obsoletos y/o implementar políticas de retención de datos y archivado que permitan gestionar los datos históricos de manera eficiente, liberará espacio en nuestras bases de datos más activas.

Monitorización y mantenimiento

Para terminar con este artículo, y aunque esto no es un error como tal no quería dejar de comentar que la falta de monitorización y mantenimiento adecuado puede permitir que problemas de crecimiento pasen desapercibidos. Esto incluye no solo el seguimiento del tamaño de los datos y logs, sino también la identificación de problemas de rendimiento que pueden indicar un uso ineficiente de recursos. Utilizar herramientas de monitorización propias o de terceros y establecer alertas ante crecimientos inusuales de bases de datos y logs puede evitarnos el problema de que el servidor termine quedándose sin espacio y deje de admitir nuevas transacciones. 

Conclusión

El crecimiento excesivo de las bases de datos SQL Server a menudo se debe a una combinación de errores de modelado, configuraciones inadecuadas y prácticas de gestión insuficientes. Abordar estos problemas con soluciones prácticas, como la normalización adecuada, la gestión eficiente de índices, y la implementación de políticas de mantenimiento y backups, puede ayudar a controlar el tamaño de la base de datos y asegurar un rendimiento óptimo. Con un enfoque proactivo en estas áreas, seremos capaces de manejar el crecimiento de las bases de datos de manera más efectiva y evitar problemas futuros relacionados con el almacenamiento y el rendimiento.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

No te vayas aun. Hemos creado una página donde estamos recopilando todos estos artículos que dan respuesta a estas preguntas frecuentes de SQL Server. Pásate por aquí a echar un vistazo. 

Publicado por Roberto Carrancio en Cloud, SQL Server, 1 comentario
02
Ago
2024

Mover BBDD de sistema

A lo largo de nuestra experiencia como administradores de bases de datos, en ocasiones nos vamos a  encontrar con la necesidad de mover las bases de datos de sistema en SQL Server. Ya sea por motivos de rendimiento, capacidad, o mantenimiento, saber realizar esta tarea de manera segura y eficiente es crucial para garantizar la integridad y disponibilidad del sistema tras el cambio. En este artículo quiero detallaros el proceso de reubicación de las bases de datos de sistema master, model, msdb, tempdb, distribution, y SSISDB, desde las consideraciones previas, pasando por los pasos detallados, hasta las mejores prácticas para minimizar riesgos.

Consideraciones Previas al Mover las Bases de Datos de Sistema

Estamos hablando de un gran cambio y tenemos que actuar como tal. Antes de proceder con el movimiento de las bases de datos de sistema, es fundamental entender que estos componentes son críticos para el funcionamiento de SQL Server, no es una intervención sin importancia. Un error en este proceso puede dejar el servidor de base de datos como un caro pisapapeles incapaz de arrancar o llevarnos a una pérdida de datos. Por lo tanto, debemos asegurarnos de tener una copia de seguridad completa y actualizada de todas las bases de datos de sistema y de usuario. Además, es recomendable realizar este tipo de operaciones en periodos sin actividad o durante ventanas de mantenimiento planificadas ya que van a requerir de paradas del servicio.

Requisitos Previos

Como acabamos de ver, un cambio de esta índole requiere de mucha preparación, a continuación os dejo una lista de pasos que para mi son imprescindibles antes de acometer este tipo de intervenciones:

  1. Notificar de la parada: Debemos asegurarnos de que todos los usuarios están informados de que el servicio de bases de datos no va a estar disponible durante el tiempo que dure la intervención y de que deben parar sus aplicaciones.
  2. Detener la monitorización: Es crucial detener la monitorización sobre el servidor durante la intervención para evitar la saturación por falsas alarmas. 
  3. Copia de Seguridad Completa: Como en toda intervención, debemos asegurarnos de disponer de una copia de seguridad completa tanto de los datos de usuario como de las bases de datos de sistema para poder volver atrás en caso de problemas.
  4. Plan de Recuperación: Muy ligado con el anterior punto, de nada nos sirve tener las copias sin un plan de recuperación bien documentado y probado en caso de que algo salga mal.
  5. Documentación del Sistema: Durante el proceso tan crítico que vamos a llevar a cabo no hay lugar para la duda, es mejor dedicar unos minutos antes de empezar a anotar la ubicación actual de los archivos de base de datos y logs de transacciones así como sus nombres que luego nos van a hacer falta.

Mover la Base de Datos master

La base de datos master es el corazón del sistema de SQL Server, ya que contiene información sobre la configuración del servidor, inicios de sesión, y otros metadatos críticos. Para mover esta base de datos, seguiremos estos pasos:

  1. Modificar la Ruta del Archivo de la Base de Datos: Utilizaremos el comando ALTER DATABASE para cambiar la ruta de los archivos de datos (MDF) y de registro (LDF).
    ALTER DATABASE master MODIFY FILE (NAME = master, FILENAME = 'NuevaRuta\master.mdf');
    ALTER DATABASE master MODIFY FILE (NAME = mastlog, FILENAME = 'NuevaRuta\mastlog.ldf');

    2. Detener el Servicio de SQL Server: Deteneremos el servicio de SQL Server desde el Administrador de Servicios o mediante una línea de comandos con el comando net

      net stop MSSQLSERVER.

      3. Mover los Archivos: C1opiaremos los archivos MDF y LDF a la nueva ubicación especificada.

      4. Modificar los Parámetros de Inicio del Servicio: Actualizaremos los parámetros de inicio del servicio SQL Server para reflejar la nueva ubicación de los archivos master y mastlog.

      5. Iniciar el Servicio de SQL Server: Finalmente, reiniciamos el servicio y verificaremos que SQL Server se inicia correctamente.

         

        Mover Otras Bases de Datos de Sistema

        Mover las base de datos Model y msdb

        El procedimiento para mover las bases de datos model y msdb es similar al de la base de datos master, pero con algunas diferencias clave ya que no será necesario configurar ningún parámetro del servicio. En ambos casos, usaremos ALTER DATABASE para modificar la ubicación de los archivos de datos y log, luego detendremos el servicio de SQL Server, moveremos los archivos manualmente y finalmente reiniciaremos el servicio.

        ALTER DATABASE model MODIFY FILE (NAME = modeldev, FILENAME = 'NuevaRuta\model.mdf');
        ALTER DATABASE model MODIFY FILE (NAME = modellog, FILENAME = 'NuevaRuta\modellog.ldf');
        ALTER DATABASE msdb MODIFY FILE (NAME = MSDBData, FILENAME = 'NuevaRuta\MSDBData.mdf');
        ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog, FILENAME = 'NuevaRuta\MSDBLog.ldf');

        Mover la tempdb

        La base de datos tempdb es una base de datos de sistema especial utilizada para operaciones temporales y almacenamiento de datos de trabajo. A diferencia de las otras bases de datos, tempdb se reconstruye cada vez que SQL Server se reinicia, lo que hace que su reubicación sea más simple. Aquí están los pasos:

        1. Modificar la Ruta de los Archivos: Utilizamos ALTER DATABASE para modificar la ruta de los archivos de datos y registro.
        ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'NuevaRuta\tempdb.mdf');
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'NuevaRuta\templog.ldf');

        2. Reiniciar el Servicio de SQL Server: Al reiniciar SQL Server, los archivos de tempdb serán recreados en la nueva ubicación.

        3. Borrar los antiguos archivos de tempdb de la ruta original.

          Mover las bases de datos Distribution y SSISDB

          Estas bases de datos son específicas para ciertas características como la replicación y la integración de servicios. Los pasos para mover estas bases de datos son generalmente similares a los descritos anteriormente, pero es crucial entender las dependencias y servicios asociados, ya que esto puede afectar la replicación y la ejecución de paquetes SSIS.

          Conclusión

          Mover las bases de datos de sistema en SQL Server es una tarea que requiere precaución y planificación. Asegurarse de tener copias de seguridad actualizadas, seguir los pasos cuidadosamente, y verificar cada cambio es esencial para el éxito de la operación. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

          Publicado por Roberto Carrancio en SQL Server, 0 comentarios