SQL Server Propiedades de Base de Datos – Opciones (3)

Introducción

Ya he indicado previamente sobre las propiedades de las bases de datos y he indicado las paginas General, Archivos, Grupos de Archivos y la primera y segunda parte de la pagina Opciones, en previas entregas, ahora continuare con las opciones de configuración de las bases de datos. 

Pagina Opciones

Ya se ha indicado previamente que es posible utilizar esta página para ver o modificar varias opciones de configuración para la base de datos seleccionada. Se han establecido las categorías de opciones:

• Automático

• Contención

• Cursor

• Configuraciones del ámbito de la base de datos

• FILESTREAM

En esta ocasión se hablará de las opciones de la categoría Varios. Sin embargo, es necesario tener en cuenta que puede usar las declaraciones de Transact-SQL ALTER DATABASE SET OPTIONS para modificar los valores si así se prefiere.

Varios

Esta sección mostrara varios valores de diversas opciones que afectan la forma en que la base de datos se puede comportar.

Permitir aislamiento de instantáneas – Se muestra si la opción esta habilitada, se debe recordar que cuando se encuentra habilitada, evita la mayoría de los bloqueos y bloqueos mediante el uso de versiones de filas. Cuando se modifican los datos, las versiones confirmadas de las filas afectadas se copian en tempdb y se proporcionan números de versión. Esta operación se denomina copiar al escribir y se utiliza para todas las inserciones, actualizaciones y eliminaciones que utilizan esta técnica. Cuando otra sesión lee los mismos datos, se devuelve la versión confirmada de los datos en el momento en que comenzó la transacción de lectura.

ANSI NULL predeterminado – Muestra si se permiten valores nulos para todos los tipos de datos o columnas definidos por el usuario que no estén explícitamente definidos como NOT NULL durante una instrucción CREATE TABLE o ALTER TABLE (el estado predeterminado). 

ANSI NULLS habilitado – Muestra si la opción esta habilitada, esto especifica el comportamiento de los operadores de comparación Igual (=) y No igual a (<>) cuando se utilizan con nulos,  cuando está habilitado, todas las comparaciones con un nulo se evalúan como DESCONOCIDO. Cuando está habilitado, las comparaciones de valores que no son UNICODE con un nulo se evalúan como Verdadero si ambos son NULL. 

ANSI Padding habilitado – Indica si la opción esta habilitada, lo que permite especificar si el relleno ANSI está activado o desactivado. 

ANSI Warnings habilitadas – Indica si la opción esta habilitada, lo que permite especificar el comportamiento estándar ISO para varias condiciones de error. Cuando está habilitado, se genera un mensaje de advertencia si aparecen valores nulos en funciones agregadas (como SUMA, PROMEDIO, MÁX, MÍN, DESVEST, DESVEST, VAR, VARP o CONTAR). En el caso contrario, no se emite ninguna advertencia. 

Aborto aritmético habilitado – Muestra si esta opción esta habilitada. Cuando está habilitada, un error de desbordamiento o división por cero hace que la consulta o el lote finalicen. Si el error ocurre en una transacción, la transacción se revierte. En caso contrario, se muestra un mensaje de advertencia, pero la consulta, el lote o la transacción continúa como si no hubiera ocurrido ningún error.

Concatenar Null Yields Null – Indica si esta opción esta habilitada, permitiendo especificar con ello el comportamiento cuando se concatenan nulos. Cuando el valor de la propiedad está habilitada, string + NULL devuelve NULL. En caso contrario, el resultado es una cadena.

Encadenamiento de propiedad entre bases de datos habilitado - Este valor de solo lectura indica si se ha habilitado el encadenamiento de propiedad entre bases de datos. Cuando está habilitado, la base de datos puede ser el origen o el destino de una cadena de propiedad entre bases de datos. 

Optimización de correlación de fechas habilitada – Muestra si la opción está habilitada, en caso afirmativo, Microsoft SQL Server mantiene estadísticas de correlación entre dos tablas cualesquiera en la base de datos que están vinculadas por una restricción FOREIGN KEY y tienen columnas de fecha y hora. En caso contrario, las estadísticas de correlación no se mantienen.

Durabilidad retardada – Muestra el valor de la opción, es una que se introdujo en la versión Microsoft SQL Server 2014. Esta opción simplemente permite que las transacciones de escritura continúen ejecutándose como si el registro se hubiera descargado en el disco; en realidad, las escrituras en disco se han agrupado y diferido, para ser manejadas en segundo plano. La transacción es optimista; asume que se producirá el vaciado del registro. Los valores posibles son; DISABLED, ALLOWED, FORCED.  El valor disabled, indica que esta opción no se utilizará. Establecer el valor en Allowed, significa que cualquier transacción individual puede usar la Durabilidad Retardada; El valor Forced significa que todas las transacciones pueden usar Durabilidad Retardada.

¿Está activada la instantánea de lectura confirmada? – Indica si la opción esta habilitada. El motor de Microsoft SQL Server utiliza el mecanismo que previene los cambios de lecturas sucias. De esta forma usa el control de versiones de filas en lugar de bloquear. Para hacerlo, se crea una instantánea coherente de transición de los datos (como estaba antes del inicio de la declaración) para cada declaración. Por lo tanto, al usar las instantáneas, no es necesario colocar bloqueos para leer los datos. El valor predeterminado de esta opción es deshabilitado.

Aborto redondeo numérico – Se indica si esta opción esta habilitada, lo que permite indicar cómo se maneja la base de datos los errores de redondeo. Cuando esta habilitada, se genera un error cuando se produce una pérdida de precisión en una expresión. En caso contrario, las pérdidas de precisión no generan mensajes de error y el resultado se redondea a la precisión de la columna o variable que almacena el resultado. 

Parametrización – Muestra el valor asociado a esta opción, los valores permitidos son SIMPLE y FORZADO. De forma predeterminada el valor es SIMPLE, indicando que las consultas se parametrizan según el comportamiento predeterminado de la base de datos. Cuando el valor es FORZADO, Se indica que Microsoft SQL Server parametriza todas las consultas en la base de datos.

Identificadores entre comillas habilitados – Indica si la opción esta habilitada, los que permite especificar si las palabras clave de Microsoft SQL Server se pueden utilizar como identificadores (un objeto o nombre de variable) si están entre comillas. 

Activadores recursivos habilitados – Indica si esta opción esta habilitada, lo cual permite especificar si otros disparadores pueden disparar disparadores. Cuando se establece en habilitado, esto permite la activación recursiva de disparadores. En caso contrario, solo se evita la recursividad directa. Para deshabilitar la recursividad indirecta, establezca la opción del servidor de disparadores anidados en 0 en las opciones de configuración de servidor. 

Digno de confianza – Muestra si la opción esta habilitada, esta opción de solo lectura indica que si Microsoft SQL Server permite el acceso a recursos fuera de la base de datos en un contexto de suplantación establecido dentro de la base de datos. Los contextos de suplantación se pueden establecer dentro de la base de datos utilizando la declaración de usuario EXECUTE AS o la cláusula EXECUTE AS en los módulos de la base de datos.  Para tener acceso, el propietario de la base de datos también necesita tener el permiso AUTHENTICATE SERVER a nivel de servidor. Esta propiedad también permite la creación y ejecución de ensamblados de acceso externo y no seguros dentro de la base de datos. Además de habilitar esta propiedad, el propietario de la base de datos debe contar con el permiso EXTERNAL ACCESS ASSEMBLY o UNSAFE ASSEMBLY en el nivel del servidor.  De forma predeterminada, todas las bases de datos de usuario y todas las bases de datos del sistema (con la excepción de MSDB) tienen esta propiedad deshabilitada. El valor no se puede cambiar para las bases de datos modelo y tempdb. Es importante indicar que TRUSTWORTHY se establece como deshabilitado siempre que se adjunta una base de datos al servidor. Se recomienda que para acceder a recursos fuera de la base de datos, un enfoque en un contexto de suplantación utilizando certificados y firmas en oposición a la opción Confiable

Formato de almacenamiento VarDecimal habilitado- Muestra el valor de esta opción que es de solo lectura a partir de Microsoft SQL Server 2008. Cuando esta habilitado, esta base de datos permite el formato de almacenamiento vardecimal. El formato de almacenamiento vardecimal no se puede deshabilitar mientras las tablas de la base de datos lo estén usando. A partir de Microsoft SQL Server 2008, todas las bases de datos están habilitadas para el formato de almacenamiento vardecimal. 

Conclusión

Ya se ha indicado que esta página de propiedades de la base de datos brinda información de las opciones de la base de datos. Si se requiere usar T-SQL para obtener la información que se muestra en la pagina indicada, para la categoría varios, es posible usar la siguiente consulta.

/*******************************************************************************
-- Script : Get Database Options Properties Part 3 
-- Author : Julio J Bueyes
-- julio.bueyes@outlook.com
--
-- Description : This script helps to get a detailed view of the database properties – Options page – part 3.
--
-- DISCLAIMER. This Code is provided for the purpose of illustration only and is not intended to be used in a production environment. 
--
-- THIS CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, 
-- INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
**********************************************************************************/
-- Set the Database Name required
USE model;
GO

--- Miscellaneous
SELECT CASE snapshot_isolation_state WHEN 1 THEN 'True' ELSE 'False' END AS [Allow Snapshot Isolation], 
    CASE is_ansi_null_default_on WHEN 1 THEN 'True' ELSE 'False' END AS [ANSI NULL Default], 
    CASE is_ansi_nulls_on WHEN 1 THEN 'True' ELSE 'False' END AS [ANSI NULLS Enabled], 
    CASE is_ansi_padding_on WHEN 1 THEN 'True' ELSE 'False' END AS [ANSI Padding Enabled], 
    CASE is_ansi_warnings_on WHEN 1 THEN 'True' ELSE 'False' END AS [ANSI Warning Enabled], 
    CASE is_arithabort_on WHEN 1 THEN 'True' ELSE 'False' END AS [Arithmetic Abort Enabled], 
    CASE is_concat_null_yields_null_on WHEN 1 THEN 'True' ELSE 'False' END AS [Concatenate Null Yields Null], 
    CASE is_db_chaining_on WHEN 1 THEN 'True' ELSE 'False' END AS [Cross-database Ownership Chaining Enabled],
    CASE is_date_correlation_on WHEN 1 THEN 'True' ELSE 'False' END AS [Date Correlation Optimization Enabled], 
    delayed_durability_desc AS [Delayed Durability], 
    CASE is_read_committed_snapshot_on WHEN 1 THEN 'True' ELSE 'False' END AS [Is Read Committed Snapshot On], 
    CASE is_numeric_roundabort_on WHEN 1 THEN 'True' ELSE 'False' END AS [Numeric Round-Abort], 
    CASE is_parameterization_forced WHEN 1 THEN 'Forced' ELSE 'Simple' END AS [Parameterization], 
    CASE is_quoted_identifier_on WHEN 1 THEN 'True' ELSE 'False' END AS [Quoted Identifiers Enabled], 
    CASE is_recursive_triggers_on WHEN 1 THEN 'True' ELSE 'False' END AS [Recursive Triggers Enabled], 
    CASE is_trustworthy_on WHEN 1 THEN 'True' ELSE 'False' END AS [Trustworthy]
FROM sys.databases
WHERE database_id = db_id()

Como se ha establecido previamente, pueden observarse de forma fácil obtener los datos directamente de la ventana de propiedades de la base de datos, en la página Opciones, no obstante, el script funciona para obtener los mismos valores proporcionados por la página.

SQL Server Propiedades de Base de Datos – Opciones (2)

Introducción

Ya he indicado previamente sobre las propiedades de las bases de datos y he indicado las paginas General, Archivos, Grupos de Archivos y la primera parte de la pagina Opciones, en previas entregas, ahora continuare con las opciones de configuración de las bases de datos. 

Página Opciones

Ya se ha indicado previamente que es posible utilizar esta página para ver o modificar varias opciones de configuración para la base de datos seleccionada. Sin embargo, es necesario tener en cuenta que puede usar las declaraciones de Transact-SQL ALTER DATABASE SET OPTIONS o ALTER DATABASE SCOPED CONFIGURATION, para modificar los valores si se prefiere.

En esta ocasión, se presentarán las opciones de las categorías Database Scope Configuration y FILESTREAM.

 

Configuraciones con ámbito de base de datos

A partir de la versión de Microsoft SQL Server 2016 y en las bases de datos definidas en Azure SQL Database, se encuentran estas propiedades de configuración que pueden ajustarse al nivel de la base de datos. Esto es, son configuraciones de nivel base de datos que afectan el comportamiento del código de aplicación en el nivel de base de datos. Anteriormente no teníamos estas opciones que eran aplicables para toda la instancia, no de forma individual de base de datos. A partir de Microsoft SQL Server 2016, si se requiere una variedad de necesidades de configuración en las bases de datos, ahora es fácil llevarlo a cabo. Se debe tener cuidado de realizar modificaciones a estos valores. 

Se debe tener en cuenta que se manejan opciones Para Secundarias, estas opciones son compatibles con el uso de los grupos de disponibilidad AlwaysOn ya que se pueden configurar diferentes opciones para las bases de datos primaria y secundaria.

Estimación de cardinalidad heredada – indica el modelo de estimación de cardinalidad usado por el optimizador de consultas independiente del nivel de compatibilidad de la base de datos. Esto es equivalente al uso del Trace Flag 9481.

Estimación de cardinalidad heredada para secundaria – indica el modelo de estimación de cardinalidad del optimizador de consultas para los secundarios, si los hay, independientemente del nivel de compatibilidad de la base de datos. 

MAX DOP – indica la configuración predeterminada de grado máximo de paralelismo para la base de datos, en el primario que debe usarse para las declaraciones, los ser valores permitidos son 0, 1, 2, etc.

MAX DOP para secundaria – indica la configuración predeterminada de grado máximo de paralelismo para la base de datos en los secundarios, si los hay, que se debe usar para las declaraciones, los valores permitidos son 0, 1, 2, etc.

Rastreo de parámetros – indica si el rastreo de parámetros en el primario esta habilitada. Es posible configurar el comportamiento para que el optimizador de consultas use o no el rastreo de parámetros. En versiones anteriores, era posible deshabilitar esta función usando el Trace Flag 4136, pero ahora es posible controlarlo a nivel de la base de datos.

Rastreo de parámetros para secundaria – indica si el rastreo de parámetros en los secundarios, si los hay, esta habilitada. Al igual que para el primario, anteriormente el equivalente de Trace Flag 4136.

Correcciones del optimizador de consultas – indica si están habilitadas las revisiones de optimización de consultas para la base de datos en el primario independientemente del nivel de Esto es equivalente al uso de Trace Flag 4199. 

Correcciones del optimizador de consultas para secundaria – indica si están habilitadas las revisiones de optimización de consultas en la base de datos, para las secundarias, si las hay, independientemente del nivel de compatibilidad. 

FILESTREAM

Esta sección se habilitará y mostrará información, si el uso de FILESTREAM está habilitado en el servidor.

Nombre de directorio de FILESTREAM – Indica el nombre del directorio para los datos de FILESTREAM asociados con la base de datos seleccionada.

Acceso sin transacciones de FILESTREAM – Indica una de las siguientes opciones para el acceso no transaccional a través del sistema de archivos a los datos de FILESTREAM almacenados en FileTables: OFF, READ_ONLY o FULL. Si FILESTREAM no está habilitado en el servidor, este valor se establece en OFF y está deshabilitado. 

Conclusión

Ya se ha indicado que esta página de propiedades de la base de datos brinda información de las opciones de la base de datos. Es importante recordar, que las opciones que en esta ocasión se presentan, tienen la particularidad siguiente; en el primer caso solo se encontraran las opciones cuando se tenga una base de datos en una instancia de Microsoft SQL Server 2016 con el SP 2 o superior, en el segundo caso, si el servidor de base de datos se ha configurado para uso de FILESTREAM. Si se requiere usar T-SQL para obtener la información que se muestra en la pagina indicada, es posible usar las siguientes consultas.

/*******************************************************************************
-- Script : Get Database Options Properties Part 2 
-- Author : Julio J Bueyes
-- julio.bueyes@outlook.com
--
-- Description : This script helps to get a detailed view of the database properties – Options page – part 2.
--
-- DISCLAIMER. This Code is provided for the purpose of illustration only and is not intended to be used in a production environment. 
--
-- THIS CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, 
-- INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
**********************************************************************************/
-- Set the Database Name required
USE model;
GO

--- Database Scoped Configurations
SELECT name, CASE configuration_id WHEN 1 THEN value ELSE CASE value WHEN 1 THEN 'ON' ELSE 'OFF' END END AS [value]
FROM sys.database_scoped_configurations
WHERE configuration_id IN (2,1,3,4)
UNION
SELECT name + '_FOR_SECONDARY', CASE configuration_id WHEN 1 THEN value_for_secondary ELSE CASE ISNULL(value_for_secondary,0) WHEN 0 THEN 'PRIMARY' ELSE value_for_secondary END END
FROM sys.database_scoped_configurations
WHERE configuration_id IN (2,1,3,4);

-- FILESTREAM
SELECT directory_name AS [FILESTREAM Directory Name], 
    non_transacted_access_desc AS [FILESTREAM Non-transacted Access]
FROM sys.database_filestream_options
WHERE database_id = DB_ID();

Como se ha establecido previamente, pueden observarse de forma fácil obtener los datos directamente de la ventana de propiedades de la base de datos, en la página Opciones, no obstante, el script funciona para obtener los mismos valores proporcionados por la página.

SQL Server Propiedades de Base de Datos – Opciones (1)

Introducción

Ya he indicado previamente sobre las propiedades de las bases de datos y he indicado las paginas General, Archivos y Grupos de Archivos en previas entregas, ahora iniciare con las distintas opciones de configuración de las bases de datos. 

Página Opciones

Es posible utilizar esta página para ver o modificar varias opciones de configuración para la base de datos seleccionada. Sin embargo, es necesario tener en cuenta que puede usar las declaraciones de Transact-SQL ALTER DATABASE SET OPTIONS o ALTER DATABASE SCOPED CONFIGURATION, para modificar los valores si se prefiere.

En esta página es posible obtener o modificar el valor de las opciones en las siguientes categorías:

• Automático
• Contención
• Cursor
• Configuraciones del alcance de la base de datos
• FILESTREAM
• Varios
• Recuperación
• Agente de servicios
• Estado

En esta entrega comentaré las tres primeras categorías y las demás se indicarán en subsecuentes entregas.

La pagina opciones se muestra a continuación:

 

En la parte superior aparecen los siguientes valores de configuración de la base de datos: 

Colación - Indica la intercalación actual de la base de datos seleccionando, es posible cambiar este valor seleccionándolo de la lista. Debe recordarse que una base de datos que una base de datos que se mueve de un servidor a otro, ésta conserva la intercalación donde fue creada la base de datos. Es importante indicar que Microsoft recomienda que las bases de datos alojadas en el servidor mantengan la intercalación igual a la que se maneja en la base de datos master.

Modelo de recuperación - Indica el modelo de recuperación actual de la base de datos, asimismo es posible establecer un modelo diferente, seleccionando de la lista alguno de los siguientes valores; Completo, Registro-masivo o Simple. Debe recordarse que Microsoft recomienda que las bases de datos de ambiente productivo deben mantener el modelo de recuperación Completo.

Nivel de compatibilidad - Indica la versión de Microsoft SQL Server que admite actualmente la base de datos.  Debe recordarse que cuando se actualiza una base de datos en Microsoft SQL Server, el nivel de compatibilidad de esa base de datos se conserva, si es posible, o se cambia al nivel mínimo compatible con el nuevo Microsoft SQL Server.

Tipo de contención – muestra el valor actual de la base de datos. Se puede especificar uno de los siguientes valores Ninguno o Parcial, este ultimo sirve para designar que se trata de una base de datos contenida. Esta opción apareció por primera vez en Microsoft SQL Server 2012, y establece que una base de datos puede manejar su permiso de acceso a los usuarios que están contenidos en la base de datos, pero que no cuentan con un permiso de acceso en el servidor. Se requiere que la propiedad del servidor Habilitar bases de datos contenidas se establezca en TRUE antes de que una base de datos pueda configurarse como contenida.

La parte inferior muestra una cuadricula con las categorías de las opciones, a continuación, se indican cada una de ellas.

Automático

Esta categoría de opciones es usada para establecer acciones automáticas que se llevan a cabo, por parte del servicio, en la base de datos. Los valores posibles de estas opciones son Falso o Verdadero.

Auto cerrado – Indica si la base de datos se apaga y libera recursos después de que el último usuario cierra su sesión. Recuerde que esta opción debe mantenerse en falso, en ambientes productivos, además de que esta opción se recomienda en valor verdadero para bases de datos en ambientes móviles.

Crear automáticamente estadísticas incrementales - Indica el uso de la opción incremental cuando se crean estadísticas por partición..

Crear estadísticas automáticamente - Indica si se crean automáticamente las estadísticas de optimización faltantes que requieren alguna consulta, por lo que para la optimización se crean automáticamente.

Contracción automática – Indica si los archivos de la base de datos están disponibles para la reducción periódica. Es recomendable que en ambiente productivo se mantenga esta opción en falso.

Estadísticas de actualización automática – Indica si la base de datos actualiza automáticamente las estadísticas de optimización desactualizadas que necesita una consulta para la optimización.

Actualización automática de estadísticas de forma asincrónica – Indica si las consultas que inician una actualización automática de estadísticas desactualizadas no esperan a que se actualicen las estadísticas antes de compilar. Las consultas posteriores utilizan las estadísticas actualizadas cuando están disponibles. Si se establecer esta opción en Verdadero no tiene efecto a menos que la opción Estadísticas de actualización automática también se establezcan en Verdadero.

Contención

En una base de datos contenida o independiente, algunas opciones que se configuran normalmente en el nivel del servidor se pueden configurar en el nivel de la base de datos.

LCID de idioma de texto completo predeterminado - Muestra el idioma predeterminado para las columnas indexadas de texto completo. El análisis lingüístico de datos indexados de texto completo depende del idioma de los datos. El valor predeterminado de esta opción es el idioma del servidor. 

Idioma predeterminado – Muestra el idioma predeterminado para todos los nuevos usuarios de bases de datos contenidas, a menos que se especifique lo contrario.

Activadores anidados habilitados – Muestra si los disparadores activan otros disparadores. Los disparadores se pueden anidar hasta un máximo de 32 niveles. 

Transformar palabras ruidosas – Muestra si se suprime un mensaje de error si las palabras irrelevantes, es decir, palabras vacías, hacen que una operación booleana en una consulta de texto completo devuelva cero filas. 

Límite de año de dos dígitos – Indica el número de año más alto que se puede ingresar como un año de dos dígitos. El año indicado y los 99 años anteriores se pueden ingresar como un año de dos dígitos. Todos los demás años deben ingresarse como un año de cuatro dígitos. Por ejemplo, la configuración predeterminada de 2049 indica que una fecha ingresada como '14/3/49' se interpretará como 14 de marzo de 2049 y una fecha ingresada como '14/3/50' se interpretará como 14 de marzo de 1950. Para obtener más información, consulte Configurar la opción de configuración del servidor de corte de año de dos dígitos.

Cursor

Esta categoría de opciones tiene el alcance del uso de cursores de Transact-SQL. Los valores posibles de estas opciones son Falso o Verdadero.

Cerrar el cursor al confirmar habilitado - Indica si los cursores se cierran después de que la transacción que abre el cursor se ha confirmado. Con valor Verdadero, todos los cursores que están abiertos cuando se confirma o deshace una transacción se cierran. En contraste, si es Falso, estos cursores permanecen abiertos cuando se confirma una transacción. 

Cursor predeterminado - Indica el comportamiento predeterminado del cursor. Con un valor Verdadero, las declaraciones del cursor son por defecto LOCAL. Con un valor de Falso, los cursores se establecen de forma predeterminada en GLOBAL.

Conclusión

Esta página de propiedades de la base de datos brinda información de las opciones de la base de datos. Si se requiere usar T-SQL para obtener la información que se muestra en la pagina indicada, es posible usar las siguientes consultas.

/*******************************************************************************
-- Script : Get Database Options Properties Part 1 
-- Author : Julio J Bueyes
-- julio.bueyes@outlook.com
--
-- Description : This script helps to get a detailed view of the database properties – Options page – part 1.
--
-- DISCLAIMER. This Code is provided for the purpose of illustration only and is not intended to be used in a production environment. 
--
-- THIS CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, 
-- INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
**********************************************************************************/
-- Set the Database Name required
USE model;
GO

-- Header Page
SELECT collation_name AS [Collation],
    recovery_model_desc AS [Recovery model],
    CASE compatibility_level WHEN 80 THEN 'SQL Server 2000 (80)'
                            WHEN 90 THEN 'SQL Server 2005 (90)'
                            WHEN 100 THEN 'SQL Server 2008 (100)'
                            WHEN 110 THEN 'SQL Server 2012 (110)'
                            WHEN 120 THEN 'SQL Server 2014 (120)'
                            WHEN 130 THEN 'SQL Server 2016 (130)'
                            WHEN 140 THEN 'SQL Server 2017 (140)'
                            WHEN 150 THEN 'SQL Server 2019 / Azure SQL Database (150)'
                            ELSE 'SQL Server ' END AS [Compatibility level],
    containment_desc AS [Containment type]
FROM sys.databases
WHERE database_id = DB_ID();

--- Automatic Options
SELECT CASE is_auto_close_on WHEN 1 THEN 'True' ELSE 'False' END AS [Auto Close], 
    CASE is_auto_create_stats_incremental_on WHEN 1 THEN 'True' ELSE 'False' END AS [Auto Close Incremental Statistics],
    CASE is_auto_create_stats_on WHEN 1 THEN 'True' ELSE 'False' END AS [Auto Create Statistics], 
    CASE is_auto_shrink_on WHEN 1 THEN 'True' ELSE 'False' END AS [Auto Shrink], 
    CASE is_auto_update_stats_on WHEN 1 THEN 'True' ELSE 'False' END AS [Auto Update Statistics], 
    CASE is_auto_update_stats_async_on WHEN 1 THEN 'True' ELSE 'False' END AS [Auto Update Statistics Asyncronously]
FROM sys.databases
WHERE database_id = DB_ID();

--- Containment Options
SELECT CASE ISNULL(default_fulltext_language_lcid,0) WHEN 0 THEN (SELECT value_in_use FROM sys.configurations WHERE configuration_id = 1126) ELSE default_language_lcid END AS [Default Fulltext Language LCID], 
        CASE ISNULL(default_language_name,0) WHEN 0 THEN (SELECT CASE value_in_use WHEN 0 THEN 'English' END FROM sys.configurations WHERE configuration_id = 124) ELSE default_language_name END AS [Default Language], 
        CASE ISNULL(is_nested_triggers_on,0) WHEN 0 THEN (SELECT CASE value_in_use WHEN 1 THEN 'True' ELSE 'False' END FROM sys.configurations WHERE configuration_id = 115) ELSE CASE is_nested_triggers_on WHEN 1 THEN 'True' ELSE 'False' END END AS [Nested Triggers Enabled], 
        CASE ISNULL(is_transform_noise_words_on,0) WHEN 0 THEN (SELECT CASE value_in_use WHEN 1 THEN 'True' ELSE 'False' END FROM sys.configurations WHERE configuration_id = 1555) ELSE CASE is_transform_noise_words_on WHEN 1 THEN 'True' ELSE 'False' END END AS [Transform Noise Words], 
        CASE ISNULL(two_digit_year_cutoff,0) WHEN 0 THEN (SELECT value_in_use FROM sys.configurations WHERE configuration_id = 1127) ELSE two_digit_year_cutoff END AS [Two Digit Year Cutoff]
FROM sys.databases db
WHERE database_id = DB_ID();

--- Cursor Options
SELECT CASE is_cursor_close_on_commit_on WHEN 1 THEN 'True' ELSE 'False' END AS [Close Cursor on Commit Enabled], 
    CASE is_local_cursor_default WHEN 1 THEN 'LOCAL' ELSE 'GLOBAL' END AS [Default Cursor]
FROM sys.databases
WHERE database_id = DB_ID();

Como puede observarse es muy fácil obtener los datos directamente de la ventana de propiedades de la base de datos, en la página Opciones, no obstante, el script funciona para obtener los mismos valores proporcionados por la página.

SQL Server Propiedades de Base de Datos – Archivos y Grupos de Archivos

Introducción

Continuando con las propiedades de las bases de datos a través de Microsoft SQL Server, en esta ocasión hablare de los archivos y grupos de archivos, que se presentaran en las paginas correspondientes. Antes de ver el contenido de estas, es conveniente llevar a cabo una recapitulación de estos conceptos. Si bien, en la ventana de propiedades se presentan en dos paginas, estas propiedades están relacionadas y por lo mismo es importante hablar de ello en una sola entrega.

Archivos

Cuando una base de datos es creada, se generan de forma automática dos archivos de sistema operativo: 

Archivo de datos – como su nombre indica contiene los datos y objetos (tablas, indices, vistas, procedimientos almacenados y otros)

Archivo de registro - contiene la información necesaria para recuperar todas las transacciones en la base de datos. 

Es preciso indicar que los archivos de datos se pueden agrupar en grupos de archivos con fines de asignación y administración.

Antes de hablar de los grupos de archivo, es importante indicar que dentro de los tipos de archivo que podemos encontrar están, además de los mencionados archivos de datos y archivos de registro de transacciones, los archivos dispersos (sparse) y Filestream. 

Es necesario indicar que los archivos de datos dentro de Microsoft SQL Server se pueden encontrar dos tipos:

Primario - Contiene información de inicio para la base de datos y apunta a los otros archivos de la base de datos. Solo se puede tener un archivo de datos principal. La extensión de nombre de archivo, dentro del sistema operativo, para estos archivos es .mdf, este es asignado por omisión.

Secundario – Estos son opcionales y definidos por el usuario. Son utilizados cuando los datos se pueden distribuir en varios discos colocando cada archivo en una unidad de disco diferente. La extensión de nombre de archivo, dentro del sistema operativo, para estos archivos .ndf.

El archivo de registro de transacciones contiene la información que se utiliza para recuperar la base de datos. Ya se ha indicado que debe haber al menos un archivo de registro para cada base de datos. La extensión de nombre de archivo, dentro del sistema operativo, para los registros de transacciones es .ldf.

Cuando se realiza una instantánea de base de datos se utiliza un archivo disperso que es un archivo esencialmente vacío que no contiene datos del usuario y aún no se le ha asignado espacio en disco para los datos del usuario. La forma de archivo para almacenar los datos de copia en escritura depende de si es utilizado por la instantánea creada por un usuario o si se utiliza internamente, como en el caso de la creada cuando se llevan a cabo las operaciones del comando DBCC CHECKDB.

FILESTREAM permite que las aplicaciones basadas en Microsoft SQL Server almacenen datos no estructurados, como documentos e imágenes, en el sistema de archivos.

De forma predeterminada, los registros de datos y transacciones se colocan en la misma unidad y ruta para manejar sistemas de un solo disco. Es posible que esta elección no sea óptima para entornos de producción. Le recomendamos que coloque los datos y los archivos de registro en discos separados.

Grupo de archivos

Cuando se menciona grupo de archivos se piensa en dos características:

• El grupo de archivos contiene el archivo de datos principal y los archivos secundarios que no se colocan en otros grupos de archivos.
• Se pueden crear grupos de archivos definidos por el usuario para agrupar archivos de datos con fines administrativos, de asignación de datos y de ubicación.

Cuando se crean objetos en la base de datos sin especificar a qué grupo de archivos pertenecen, se asignan al grupo de archivos predeterminado. En cualquier momento, se designa exactamente un grupo de archivos como grupo de archivos predeterminado, denominado PRIMARY. Los archivos del grupo de archivos predeterminado deben ser lo suficientemente grandes para contener cualquier objeto nuevo que no esté asignado a otros grupos de archivos.

Todos los archivos de datos se almacenan en los grupos de archivos que se enumeran en la siguiente tabla.

PRIMARY El grupo de archivos que contiene el archivo principal. Todas las tablas del sistema forman parte del grupo de archivos principal.

Datos optimizados para memoria Un grupo de archivos optimizado para memoria se basa en un grupo de archivos de flujo de archivos

FILESTREAM

Definido por el usuario Cualquier grupo de archivos que crea el usuario cuando el usuario crea por primera vez o posteriormente modifica la base de datos.

Página de archivos

Utilice esta página para crear una nueva base de datos o ver o modificar las propiedades de la base de datos seleccionada. Este tema se aplica a las propiedades de la base de datos (página de archivos) para las bases de datos existentes y a la nueva base de datos (página general).

La parte superior de esta pagina muestra la siguiente información:

Archivos de base de datos

Nombre de la base de datos - El nombre de la base de datos.

Propietario - El propietario de la base de datos. En caso de que se desee cambiar de propietario esto puede realizarse, escribiendo directamente el usuario u oprimiendo el botón de tres puntos, para que pueda ser seleccionado de la lista. Cuando se crean las bases de datos, el propietario es el que las creó de forma predeterminada. Esta propiedad otorga al creador permisos adicionales, y esto puede ser un problema en un entorno seguro bloqueado donde debemos respetar el principio de privilegio mínimo.

Utilice la indexación de texto completo - Esta casilla de verificación está marcada y deshabilitada porque la indexación de texto completo siempre está habilitada en Microsoft SQL Server 2019 (15.x).

La parte inferior de la pagina, muestra información de los archivos de base de datos, donde se puede ver, agregar, modificar o eliminar archivos para la base de datos, y una cuadricula que contiene las siguientes columnas o propiedades de los archivos:

Nombre lógico - Muestra el nombre interno del archivo, este puede ser el mismo en diferentes bases de datos, pero debe ser único entre los nombres de archivo lógico de la base de datos. Puede modificarse el nombre lógico en cualquier momento.

Tipo de archivo - Indica el tipo de archivo, puede ser Data, Log o Filestream Data. No puede modificarse en un archivo existente. Si se agrega un nuevo archivo, seleccione el tipo de archivo de la lista. Es importante seleccionar Filestream Data si está agregando archivos (contenedores) a un grupo de archivos optimizado para memoria. 

Para agregar archivos (contenedores) a un grupo de archivos de datos de Filestream, el uso de FILESTREAM debe estar habilitado. Puede habilitar el uso de FILESTREAM mediante el cuadro de diálogo Propiedades del servidor (página avanzada).

Grupo de archivos - Indica el grupo de archivos al que pertenece el archivo. Cuando se crea o adiciona un nuevo archivo, se debe seleccionar el grupo de archivos para el archivo de la lista. De forma predeterminada, el grupo de archivos es el denominado PRIMARY. 

Puede crearse un nuevo grupo de archivos seleccionando <nuevo grupo de archivos> e ingresando información sobre el grupo de archivos en el cuadro de diálogo Nuevo grupo de archivos. 

También se puede crear un nuevo grupo de archivos en la página Grupo de archivos. 

No puede modificar el grupo de archivos de un archivo existente. Al agregar archivos (contenedores) a un grupo de archivos optimizado para memoria, el campo Grupo de archivos se completará con el nombre del grupo de archivos optimizado para memoria de la base de datos.

Tamaño inicial - Se muestra el tamaño inicial del archivo en megabytes. Cuando se crea o adiciona un archivo, se mostrará, de forma predeterminada, el valor que se indica en la base de datos model. 

Éste campo no es válido para archivos FILESTREAM. Para archivos en grupos de archivos optimizados para memoria, este campo no se puede modificar.

Autocrecimiento - Indica la forma en que se llevará a cabo el crecimiento automático del archivo. Se debe recordar que controla cómo se expande el archivo cuando se alcanza su tamaño máximo de archivo. 

Pueden modificarse estos valores, para editar los valores de crecimiento automático, haga clic en el botón editar junto a las propiedades de crecimiento automático del archivo que desee y cambie los valores en el cuadro de diálogo Cambiar crecimiento automático. De forma predeterminada, estos son los valores de la base de datos model. 

Este campo no es válido para archivos FILESTREAM. Para archivos en grupos de archivos optimizados para memoria, este campo debe ser Ilimitado.

Ruta - Muestra la ruta del archivo seleccionado. Cuando se crea o adiciona un nuevo archivo, para especificar una ruta para un nuevo archivo, haga clic en el botón editar junto a la ruta del archivo y navegue hasta la carpeta de destino. No puede modificarse la ruta de un archivo existente, utilizando esta ventana. 

Para los archivos FILESTREAM, la ruta es una carpeta. El motor de base de datos de SQL Server creará los archivos subyacentes en esta carpeta.

Nombre del archivo - Muestra el nombre del archivo físico.

Este campo no es válido para archivos FILESTREAM, incluidos los archivos en grupos de archivos optimizados para memoria.

Finalmente se aprecian dos botones, para ser usados con la cuadricula anterior:

• Añadir - Agregue un nuevo archivo a la base de datos.
• Eliminar - Elimina el archivo seleccionado en la cuadricula de la base de datos. Es necesario indicar que no se puede eliminar un archivo a menos que esté vacío. El archivo de datos principal y el archivo de registro no se pueden eliminar de la base de datos.

Página Grupos de Archivos

Esta página es utilizada para ver los grupos de archivos que tiene una base de datos o para agregar un nuevo grupo de archivos a la base de datos seleccionada. Actualmente los tipos de grupos de archivos se separan en los siguientes: 

• Grupos de archivos de datos - contienen datos regulares y archivos de registro

• Datos de FILESTREAM - contienen archivos de datos de FILESTREAM

• Grupos de archivos con optimización de memoria.

Los archivos de datos de FILESTREAM almacenan información sobre cómo se almacenan los datos de objetos grandes binarios (BLOB) en el sistema de archivos cuando se usa el almacenamiento FILESTREAM. Las opciones de estos grupos de archivo son las mismas que para los tipos de grupos de archivos de datos.

Recuérdese que, si FILESTREAM no está habilitado, la sección correspondiente a Filestream no estará disponible, por lo que será posible habilitar el almacenamiento de FILESTREAM utilizando Propiedades del servidor (página avanzada).

Es importante indicar que se requieren grupos de archivos optimizados para memoria para que una base de datos contenga una o más tablas optimizadas para memoria. Las tablas de datos optimizadas para memoria surgen en la versión de Microsoft SQL Server 2014, anteriormente no se usaba esta opción. 

Opciones de grupo de archivos de datos y FILESTREAM

Nombre - Muestra el nombre del grupo de archivos.

Archivos - Muestra el recuento de archivos en el grupo de archivos.

Solo lectura - Indica que el grupo de archivos esta en un estado de solo lectura.

Defecto - Esta opción indica que este grupo de archivos se indicara como predeterminado. Puede tener un grupo de archivos predeterminado para las filas y un grupo de archivos predeterminado para los datos de FILESTREAM.

Opciones de grupos de archivos de datos optimizados para memoria

Nombre - Indica el nombre del grupo de archivos optimizado para memoria.

Archivos de FILESTREAM - Muestra el número de archivos (contenedores) en el grupo de archivos de datos optimizados para memoria. Puede agregar contenedores en la página Archivos.

Se muestran dos botones al final de cada cuadricula con la siguiente funcionalidad

• Añadir - Agrega una nueva fila en blanco a la cuadrícula correspondiente que enumera los grupos de archivos para la base de datos.
• Eliminar - Elimina la fila del grupo de archivos seleccionado de la cuadrícula correspondiente.

Conclusión

Como se ha visto, una base de datos, manejada por Microsoft SQL Server puede usar diversos archivos, agrupados en diversos tipos de archivo, lo que es importante recordar, es que solo puede contener un archivo primario, con la extensión .mdf y al menos un archivo de transacciones, con la extensión .ldf. El manejo de grandes objetos permitió la llegada de los archivos de FILESTREAM, y el manejo de las tablas optimizadas en memoria se hizo posible con la arquitectura de 64 bits, por lo que también estos grupos de archivos permiten el uso de este tipo de grupo de archivos de datos.

Si se requiere usar T-SQL para obtener la información que se muestra en las paginas indicadas, es posible usar las siguientes consultas.

Para la pagina de archivos y grupos de archivos se puede obtener:

/*******************************************************************************
-- Script : Get Database Files Properties 
-- Author : Julio J Bueyes
-- julio.bueyes@outlook.com
--
-- Description : This script helps to get a detailed view of the database properties – Files page.
--
-- DISCLAIMER. This Code is provided for the purpose of illustration only and is not intended to be used in a production environment. 
--
-- THIS CODE AND ANY RELATED INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, 
-- INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
**********************************************************************************/

-- Set the Database Name required

USE model;
GO

-- Files Page

SELECT db.name as [Database Name], sp.name as [Owner]
FROM sys.databases db 
    INNER JOIN sys.server_principals sp ON db.owner_sid = sp.sid

-- Get the database files information

SELECT df.name AS [Logical Name], df.type_desc AS [File Type], ds.name as [Filegroup], (size/1024) as [Size (MB)], 
    CASE df.is_percent_growth 
        WHEN 1 THEN 'By ' + CAST(df.growth/1024 as varchar) + ' %, ' + CAST(df.max_size/1024 as varchar) + ' MB'
        ELSE 'By ' + CAST(df.growth/1024 as varchar) + ' MB, ' + CAST(df.max_size/1024 AS varchar) + ' MB' END AS [Autogrowth / Maxsize],
    df.physical_name AS [Path and Name] 
FROM sys.database_files as df 
    LEFT OUTER JOIN sys.data_spaces as ds ON df.data_space_id = ds.data_space_id;

-- Filegroups Page

-- Get the database filegroups data information

SELECT ds.name, COUNT(df.FILE_ID) as [Files], fg.is_read_only as [Read-Only], ds.is_default as [Default], is_autogrow_all_files as [Autogrow All Files]
FROM sys.data_spaces ds 
    INNER JOIN sys.database_files df ON ds.data_space_id = df.data_space_id
    INNER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id
WHERE ds.type = 'FG' ----<--- Indicate ROWS_FILEGROUP 
GROUP BY ds.name, fg.is_read_only, ds.is_default, is_autogrow_all_files;

-- Get the database filegroups FILESTREAM information
SELECT ds.name, COUNT(df.FILE_ID) as [FILESTREAM Files], fg.is_read_only as [Read-Only], ds.is_default as [Default]
FROM sys.data_spaces ds 
    INNER JOIN sys.database_files df ON ds.data_space_id = df.data_space_id
    INNER JOIN sys.filegroups fg ON df.data_space_id = fg.data_space_id
WHERE ds.type = 'FD' ----<--- Indicate FILESTREAM 
GROUP BY ds.name, fg.is_read_only, ds.is_default;

-- Get the database filegroups for Memory Optimized Files information
SELECT ds.name, COUNT(df.FILE_ID) as [FILESTREAM Files]
FROM sys.data_spaces ds 
    INNER JOIN sys.database_files df ON ds.data_space_id = df.data_space_id
WHERE ds.type = 'FX' ----<--- Indicate MEMORY_OPTIMIZED_DATA_FILEGROUP 
GROUP BY ds.name;

Como puede observarse es muy fácil obtener los datos directamente de la ventana de propiedades de la base de datos, en las páginas Archivos y Grupos de Archivos, no obstante, el script funciona para obtener los mismos valores proporcionados por las páginas.