SQL Server

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.

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.

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.

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.

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.

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.

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:

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.
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.
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.
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.
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:

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:

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!

Jul

2024

Bases de datos de sistema en SQL Server

Todos los que en algún momento hemos decidido profundizar en SQL Server nos hemos llegado a sentir abrumados por la cantidad de componentes internos y su complejidad de funcionamiento que tiene este motor de base de datos. A esto hay que sumar, que muchos de estos objetos de sistema carecen de documentación oficial en muchas ocasiones lo que los hace aún más opacos para el DBA novel. Entre estos componentes, las bases de datos del sistema juegan un papel fundamental. Estas bases de datos no solo son esenciales para el funcionamiento del servidor, sino que cuanto más conoces sus componentes más te facilitan la administración, la seguridad y la optimización del rendimiento de las bases de datos de usuario. En este artículo, vamos a hablar justo de eso, de las bases de datos del sistema en SQL Server, desglosando su propósito y funciones clave.

¿Qué son las Bases de Datos del Sistema en SQL Server?

Las bases de datos del sistema en SQL Server son un conjunto de bases de datos predefinidas que almacenan información interna sobre la configuración del servidor, los metadatos de las bases de datos de usuario y otros aspectos críticos de la operación del sistema. Estas bases de datos son fundamentales para el funcionamiento del servidor y la gestión de los datos que este contiene.

SQL Server necesita varias bases de datos del sistema, entre las que vamos a encontrar a las conocidas master, msdb, model, tempdb pero también alguna oculta como Resource Database y otras que solo aparecen si usamos características concretas como la SSISDB o la base de datos distribution. Cada una de estas bases de datos tiene una función específica que desempeñar y, en conjunto, permiten el correcto funcionamiento del servidor.

Base de datos master: El Corazón del Sistema

La base de datos master es la más crítica de todas. Almacena información crucial sobre el servidor SQL en sí mismo, incluyendo la configuración del servidor, los detalles de las bases de datos de usuario, y la información de inicio de sesión y permisos. En resumen, master actúa como el catálogo central de SQL Server.

Cualquier pérdida o corrupción de la base de datos master puede tener consecuencias graves, haciendo esencial contar con copias de seguridad regulares y verificadas de esta base de datos. Además, la restauración de master es un proceso delicado que debe realizarse con precaución para evitar daños adicionales al sistema.

msdb: La Base de Datos de sistema de los Jobs

La base de datos msdb es otra pieza clave en SQL Server. Esta base de datos almacena información sobre trabajos de SQL Server Agent, operaciones de respaldo y restauración, alertas, y operadores. También almacena los paquetes de los planes de mantenimiento nativos de SQL Server. En resumen, msdb es la base de datos encargada de la automatización y planificación de tareas dentro del servidor.

Para los DBAs es importantísima pues conocer sus objetos internos nos abre la puerta hacia la automatización, permitiéndonos programar y supervisar tareas que faciliten la administración diaria del sistema. Esto incluye desde respaldos automáticos hasta el envío de alertas cuando se detectan problemas. No debemos tampoco descuidar su mantenimiento pues es propensa a crecer en exceso al almacenar todo el historial de copias de seguridad, restauración, jobs, etc…

model: La Bases de Datos de sistema modelo

La base de datos model es el modelo de referencia que usa SQL Server para la creación de nuevas bases de datos. Cada vez que creamos una nueva base de datos, el motor de SQL se basa en la estructura y las configuraciones definidas en model. Esto incluye configuraciones de tamaño inicial, collation, y otros aspectos fundamentales.

Como DBAs tenemos la opción de modificar la configuración de la base de datos model para establecer configuraciones predeterminadas para nuevas bases de datos. Esto, que puede parecer trivial, es muy importante ya que puede ahorrarnos tiempo y asegurar la consistencia en entornos donde se crean bases de datos con frecuencia.

tempdb: Espacio Temporal para la Ejecución de Consultas

La base de datos tempdb es de uso temporal y es donde SQL Server maneja operaciones de procesamiento intermedio, como ordenaciones y operaciones de hash que no caben en memoria. Además sirve de almacenamiento de tablas temporales. Debido a la naturaleza volátil de sus datos, la base de datos tempdb se recrea cada vez que el servidor SQL Server se reinicia.

La gestión efectiva de tempdb es crucial para el rendimiento general del sistema. Esto incluye la configuración adecuada de su tamaño, el número de archivos de datos, y la ubicación física de los archivos para evitar cuellos de botella en el I/O. Una de nuestras responsabilidades como DBAs es asegurarnos de que nunca se llene, pues en ese momento nuestra instancia dejará de admitir transacciones nuevas. En este post hablamos más sobre este tema.

Resource Database: De sistema pero escondida

La base de datos de recursos es una base de datos oculta que contiene todas las definiciones de sistema para objetos incluidos en SQL Server. Aunque no es directamente accesible, Resource Database juega un rol crucial en las actualizaciones y en la recuperación del sistema, al permitir la actualización de objetos del sistema sin afectar las bases de datos de usuario. Al no ser accesible no nos tenemos que preocupar por ella, de nada serviría.Aunque esta base de datos tiene su página dedicada de Microsoft, tampoco existe mucha documentación al respecto.

Bases de datos de sistema especiales

Como hemos comentado en la introducción, además de las bases de datos de sistema que podemos encontrar en todas las instalaciones de SQL Server, existen otras que siendo bases de datos de sistema solo se crean si activamos la característica que las requiere. Estas son la SSISDB y la distribution.

SSISDB: Base de datos interna de SSIS

La base de datos SSISDB, también conocida como la base de datos del Catálogo de SSIS, es crucial para la gestión de paquetes de SQL Server Integration Services (SSIS). Esta base de datos almacena los paquetes, configuraciones y datos de ejecución para las tareas de ETL (Extract, Transform, Load). Nos permite un control centralizado de los procesos de integración de datos, lo cual es esencial para mantener la consistencia y fiabilidad en los entornos de datos empresariales.

Para los DBAs, SSISDB ofrece herramientas avanzadas de gestión y monitorización, incluyendo la capacidad de gestionar versiones de paquetes, programar ejecuciones y revisar logs de errores de SSIS. Además, SSISDB es fundamental para garantizar la seguridad y la auditoría de los procesos de integración de datos. Si quieres profundizar en la administración de la base de datos SSISDB te recomiendo este artículo que publiqué hace tiempo.

distribution: Administración de la Replicación

La base de datos distribution es esencial para la característica de replicación en SQL Server. Esta base de datos se utiliza para almacenar metadatos y datos de cola que se requieren durante la replicación transaccional y de mezcla. Se crea en el servidor distribuidor y actúa como un intermediario entre el publicador y los suscriptores, ayudando a garantizar que los cambios en los datos se distribuyan de manera eficiente y coherente.

Para los DBAs, conocer internatemente la base de datos distribution nos va a ayudar con la monitorización y resolución de problemas de replicación. Una administración adecuada de esta base de datos es fundamental para evitar cuellos de botella y asegurar la sincronización continua de los datos entre las distintas instancias.

Conclusión

En resumen, las bases de datos del sistema en SQL Server son esenciales para el funcionamiento y la administración efectiva del servidor. Cada una cumple un rol específico, desde la administración centralizada y la configuración del servidor en master, hasta el manejo de automatización y tareas en msdb, la gestión de espacio temporal en tempdb, y la supervisión de procesos de integración y replicación en SSISDB y distribution.

Para los DBAs, comprender y manejar adecuadamente estas bases de datos es crucial. No solo nos permite mantener la estabilidad y el rendimiento del servidor, sino que también asegura que estemos preparados para recuperarnos rápidamente en caso de fallos o desastres. Por si esto fuera poco, conocer sus objetos (tablas, vistas, procedimientos y funciones) nos van a permitir automatizar gran parte de nuestro trabajo.

Mantenerse al día con las mejores prácticas para la gestión de estas bases de datos, así como entender sus interacciones y dependencias, es una tarea continua y vital en nuestro rol como administradores de bases de datos. Con el conocimiento adecuado, podemos no solo mantener nuestros sistemas funcionando sin problemas, sino también optimizar el rendimiento y la seguridad en nuestros entornos de SQL Server. 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!

Jul

2024

Logins y Users: Seguridad en SQL

Una de las principales labores que tenemos como administradores de bases de datos es la gestión de su seguridad y, para eso, es imprescindible la gestión de inicios de sesión (Logins) y usuarios (Users). Puede parecer que son lo mismo pero no lo son y, entender sus particularidades y diferencias va a ser clave para nuestra labor. Además, a estos conceptos tenemos que sumarle uno nuevo, los usuarios independientes, que se han implementado en las soluciones SQL en Azure y que harán que cambiemos nuestra forma de actuar en muchos casos.

Logins o Inicios de Sesión, primera capa de seguridad

Los inicios de sesión en SQL Server son entidades de seguridad a nivel de servidor que permite a los usuarios autenticarse y acceder al servidor SQL. Los inicios de sesión pueden ser de dos tipos: inicios de sesión basados en Windows y inicios de sesión de SQL Server.

Los logins de Windows aprovechan las cuentas de usuario o grupos de seguridad definidos en Active Directory o en el equipo local, permitiendo una autenticación integrada y centralizada. Por otro lado, los logins de SQL Server son gestionados directamente por SQL Server y, para crearlos, tendremos que proporcionar un nombre de inicio de sesión y una contraseña específicos para SQL Server.

Creación de logins

Para crear logins usaremos la sintaxis CREATE LOGIN de la siguiente manera:

a) Para un login de Windows usaremos:

CREATE LOGIN MiDominio\MiUsuarioDeWindows FROM WINDOWS

b) Para un login de SQL usaremos:

CREATE LOGIN MiLogin WITH PASSWORD=’MiContraseñ@SuperSegurísima1234’

Usuarios en SQL Server, segunda capa de seguridad

A diferencia de los inicios de sesión, los usuarios en SQL Server existen a nivel de base de datos. Mientras que el inicio de sesión proporciona acceso a la instancia del servidor, el usuario define qué recursos dentro de una base de datos específica puede acceder ese inicio de sesión.

Los usuarios se utilizan para gestionar permisos dentro de una base de datos, controlando el acceso a tablas, procedimientos almacenados y otros objetos. Cada usuario de base de datos puede estar asociado a un inicio de sesión o no, pero un inicio de sesión puede estar asociado a múltiples usuarios en diferentes bases de datos. Cuando un usuario no está asociado a un inicio de sesión decimos que está huérfano.

Creación de users

Crear un usuario en SQL Server se hace, posicionado en la base de datos, con la sintaxis CREATE USER y obligatoriamente implica especificar el inicio de sesión al que estará asociado. Por ejemplo:

USE MiBaseDeDatos
GO
CREATE USER MiUsuario FOR LOGIN MiLogin;

El Concepto de SID ¿Qué es y por qué es clave para la seguridad?

El SID (Security Identifier) es un identificador único que SQL Server asigna a cada inicio de sesión y usuario. Este identificador es crucial porque es lo que realmente utiliza SQL Server para vincular inicios de sesión con usuarios y para gestionar los permisos. Aunque los nombres de los inicios de sesión y usuarios pueden ser los mismos en diferentes bases de datos o instancias, el SID es lo que realmente distingue a cada entidad de seguridad.

Cuando estamos usando logins de windows siempre usaremos el SID que tenga ese usuario en el Directorio Activo (lo sé es lioso pero es que a nivel windows si se llama usuario y nosotros en SQL lo usamos como login).

Problemas conocidos con los SID

Como habrás imaginado, cada SID es único para cada inicio de sesión y, aunque en dos servidores tengamos un login con el mismo nombre, al mover las bases de datos de uno a otro no van a coincidir esos SID y se quedará huérfano el usuario en la base de datos. Lo explico más en detalle, los permisos a nivel de base de datos asignados a un usuario o a nivel de instancia a un login, están realmente vinculados a su SID, no a su nombre. Esto hace que, durante migraciones o en configuraciones de alta disponibilidad, como los Grupos de Disponibilidad (AG) en SQL Server, sea fundamental que los SIDs de los inicios de sesión y usuarios coincidan entre las diferentes instancias y bases de datos.

Si los SIDs no coinciden, los usuarios no podrán acceder a los recursos correspondientes porque SQL Server no los reconocerá correctamente. Por suerte eso se puede solucionar copiando el SID del login en uno de los servidores y recreando el login en el resto con ese mismo SID como te expliqué aquí en un artículo o aquí en video

Usuarios Independientes o Contenidos, simplificando la seguridad

Tanto en SQL Server a partir de SQL 2012 como en las soluciones SQL en Azure existe un modelo de entidades de seguridad que se basa en usuarios sin necesidad de login. A esto se le llama usuarios de base de datos independientes o contenidos. Estos usuarios no están vinculados a un inicio de sesión a nivel de servidor, sino que existen exclusivamente dentro de una base de datos.

Tiene ciertos inconvenientes como que en la conexión siempre se debe especificar a qué base de datos te quieres conectar y si quieres cambiar de base de datos tienes que cerrar tu conexión actual y volver a conectar. Sin embargo en Azure SQL Database, donde “no hay servidor” y esto no es un problema, los usuarios independientes cobran relevancia. Si nos olvidamos de esta limitación, si no es importante para nosotros, las ventajas son indiscutibles, cada base de datos puede tener su propio conjunto de usuarios y permisos, independiente de otros recursos lo que hace que las bases de datos pueden moverse entre servidores o instancias sin necesidad de volver a crear o ajustar inicios de sesión a nivel de servidor.

Ventajas en Entornos de Replicación y Alta Disponibilidad

Los usuarios independientes son particularmente ventajosos en entornos con replicación y alta disponibilidad (HA). En configuraciones tradicionales, cada vez que replicamos o movemos una base de datos a otra instancia, debemos asegurarnos de que los inicios de sesión y sus SIDs coincidan en todas las instancias involucradas. Esto puede ser complicado y propenso a errores.

En entornos de Grupos de Disponibilidad (AG), se requiere un cuidadoso manejo de los inicios de sesión para asegurar que los usuarios puedan acceder a las bases de datos replicadas en diferentes nodos del AG. Debemos crear cada inicio de sesión manualmente en cada réplica secundaria, asegurando que los SIDs coincidan. Con usuarios independientes, este problema se simplifica enormemente. Dado que estos usuarios existen únicamente dentro de la base de datos y no dependen de un inicio de sesión a nivel de servidor, las preocupaciones sobre la coincidencia de SIDs se eliminan. Esto hace que la administración de permisos y accesos en entornos distribuidos sea más sencilla y menos propensa a errores.

Comparación con el Paradigma Tradicional

Comparado con el enfoque tradicional de SQL Server, los usuarios independientes proporcionan una mayor flexibilidad y facilidad de gestión en entornos distribuidos. En cambio, la capacidad de gestionar usuarios directamente dentro de cada base de datos, reduce la complejidad administrativa.

Por ejemplo, un usuario independiente se crea así:

USE MiBaseDeDatos
CREATE USER MiUsuarioIndependiente WITH PASSWORD = 'ContraseñaSegura';

Mientras que según el enfoque tradicional sería:

USE master
CREATE LOGIN MiLogin WITH PASSWORD = 'ContraseñaSegura';
USE MiBaseDeDatos
CREATE USER MiUsuario FOR LOGIN MiLogin;

Seguridad y buenas prácticas

Para asegurar nuestras instancias de SQL Server y las bases de datos asociadas, debemos seguir varias buenas prácticas. Lo primero y fundamental es seguir el principio de privilegios mínimos otorgando solo los permisos necesarios a cada usuario. Otra recomendación es seguir una política de contraseñas que asegure contraseñas fuertes y políticas de expiración y complejidad. Por último, os recomendaría crear auditorías para rastrear el acceso y las acciones de los usuarios.

Seguridad en Azure

En Azure, además de las prácticas anteriores, debemos considerar las herramientas y servicios adicionales que ofrece la plataforma.La integración con Microsoft Entra (antes llamado Azure Active Directory) para una gestión centralizada de identidades será un gran aliado. Al usar Microsoft Entra podremos implementar requisitos de segundo factor de autenticación (MFA) como por ejemplo requerir un código temporal enviado por SMS, llamada o una aplicación de autenticación además de la contraseña del usuario. También disponemos de servicios como Azure Key Vault para la gestión de claves y certificados. Y por supuesto, las alertas y monitorización avanzada utilizando Azure Security Center, Azure Monitor y Azure Insights para detectar y responder a amenazas de seguridad.

Conclusión

La gestión de inicios de sesión y usuarios en SQL Server es un aspecto fundamental de la administración de bases de datos, que garantiza la seguridad y el control de acceso a nuestros datos. Mientras que los inicios de sesión proporcionan acceso a la instancia del servidor, los usuarios dentro de las bases de datos gestionan el acceso a los recursos específicos. Con la evolución hacia entornos cloud, los usuarios independientes en Azure SQL Database ofrecen una mayor flexibilidad y facilitan la administración en escenarios distribuidos. Aprovechar las ventajas de los usuarios independientes en entornos de replicación y HA nos permite reducir la complejidad y mejorar la eficiencia administrativa.

Comprender estas diferencias y aplicar las mejores prácticas de seguridad nos permite aprovechar al máximo nuestras bases de datos SQL Server, ya sea en implementaciones tradicionales o en la nube.

SQL Server

Mover BBDD de sistema

Consideraciones Previas al Mover las Bases de Datos de Sistema

Requisitos Previos

Mover la Base de Datos master

Mover Otras Bases de Datos de Sistema

Mover las base de datos Model y msdb

Mover la tempdb

Mover las bases de datos Distribution y SSISDB

Conclusión

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes