SQL Server Propiedades de Base de Datos – Opciones 4

Introducción

Ya se ha indicado previamente sobre las propiedades de las bases de datos y he indicado las paginas General, Archivos, Grupos de Archivos y las primeras tres partes de la pagina Opciones, en previas entregas, ahora continuare con la última parte de 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. Se han establecido las categorías de opciones:

• Automático

• Contención

• Cursor

• Configuraciones del ámbito de la base de datos

• FILESTREAM

• Varios

En esta ocasión se hablará de las opciones restantes. 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.

Recuperación

Esta categoría esta relacionada con la forma en que se lleva a cabo la recuperación de la base de datos en disco.

Verificar página – Aquí se indica la opción que se utiliza para descubrir y notificar transacciones incompletas de E / S causadas por errores de lectura / escritura de disco. Los valores permitidos son None (no se lleva a cabo alguna verificación), TornPageDetection ( se llevan a cabo las siguientes acciones, al escribir una página en disco, se toman los primeros 2 bits de cada sector de 512 bytes en cada página y se almacenan en el encabezado de la página, posteriormente, cuando la página se lee desde el disco, Microsoft SQL Server compara los bits del encabezado con los bits del sector, para asegurarse de que sigan siendo los mismos) y Checksum (al realizar la escritura en disco se crea un valor de suma de comprobación utilizando el contenido de toda la página y guarda ese valor en el encabezado, cuando se lleva a cabo la lectura de una página del disco, se crea de nuevo una suma de comprobación y se compara con la suma de comprobación guardada). Cabe mencionar que el valor recomendado por Microsoft es Checksum.

Tiempo de recuperación objetivo (segundos) – Presenta el valor del límite máximo de tiempo, expresado en segundos, para recuperar la base de datos especificada en caso de un bloqueo. 

Agente de servicio

Esta categoría aplica como se funciona el agente de servicio 

Agente habilitado – Indica si el agente de servicio se encuentra habilitado.

Prioridad de Honor del Servicio – Muestra el valor de Propiedad de Service Broker de solo lectura.

Identificador de corredor de servicios – Muestra el valor del identificador, este valor es de lectura.

Estado

Esta categoría muestra las opciones del estado operativo de la base de datos.

Base de datos de solo lectura - Indica si la base de datos es de solo lectura. Cuando es verdadero, los usuarios solo pueden leer datos en la base de datos, pero no pueden modificar los datos ni los objetos de la base de datos. Es importante indicar que la base de datos se puede eliminarse utilizando DROP DATABASE. 

Estado de la base de datos – Muestra el estado actual de la base de datos. Este valor no es editable. Por lo general, una base de datos mantiene el valor NORMAL, en caso de que este estado se presente con algún valor diferente deberá indagarse que ha pasado con la base de datos.

Cifrado habilitado – Muestra si la base de datos está habilitada para el cifrado de datos. Es necesario contar con una clave de cifrado. 

Acceso restringido - Muestra que usuarios pueden acceder a la base de datos. Los posibles valores son:  Múltiple ( con el estado normal de producción permite que varios usuarios accedan a la base de datos),  Único (usado principalmente para acciones de mantenimiento, solo un usuario puede acceder a la base de datos),  Restringido (solo los miembros de los roles db_owner, dbcreator o sysadmin pueden usar la base de datos).

Conclusión

A lo largo de 4 entregas se han mencionado los valores de las distintas opciones en las categorías indicadas, es necesario indicar que muchos de los valores de las opciones establecidos por defecto, definidos desde la base de datos de sistema denominada model, son los que se utilizan cuando se crea una base de datos, no obstante, cada base de datos puede utilizar y establecer valores diferentes entre las opciones.

Una de las opciones que se sugiere sea el mismo en todas las bases de datos del servidor es el de la del nivel de compatibilidad, ya que esto permite que la base de datos pueda efectivamente utilizar las facilidades de la versión de Microsoft SQL Server. Otro de las opciones que se sugiere que se mantenga como la del servidor es la intercalación (collation), dado que cuando es diferente del que se tiene en las bases de datos de sistema, puede provocar errores y bajo desempeño en el uso.

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 4 
-- Author : Julio J Bueyes
-- julio.bueyes@outlook.com
--
-- Description : This script helps to get a detailed view of the database properties – Options page – part 4.
--
-- 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
--- Recovery
SELECT page_verify_option_desc AS [Page Verify], 

    target_recovery_time_in_seconds AS [Target Recovery Time (Seconds)]
FROM sys.databases
WHERE database_id = db_id()
--- Service Broker
SELECT CASE is_broker_enabled WHEN 1 THEN 'True' ELSE 'False' END AS [Broker Enabled], 
    CASE is_honor_broker_priority_on WHEN 1 THEN 'True' ELSE 'False' END AS [Honor Broker Priority], 
    service_broker_guid AS [Service Broker Identifier]
FROM sys.databases
WHERE database_id = db_id()
-- State
SELECT CASE is_read_only WHEN 1 THEN 'True' ELSE 'False' END AS [Database Read Only], 
    CASE state_desc WHEN 'ONLINE' THEN 'NORMAL' ELSE state_desc END AS [Database State], 
    CASE is_encrypted WHEN 1 THEN 'True' ELSE 'False' END AS [Encryption Enabled],
    user_access_desc AS [Restrict Access]
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 (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 – General

Introducción

La principal razón de ser de Microsoft SQL Server es la de administrar las bases de datos de los usuarios, ya sea individuos o empresas, que requieren almacenar y manejar sus datos, la cual, se convertirá en la información esencial para los efectos requeridos.

Ya se ha indicado y expuesto las propiedades y configuración de los servicios de servidor de Microsoft SQL Server, en este post iniciaré con las propiedades de las bases de datos, un aspecto importante, que se manejará en estas entregas, es que la visión será la que se observe con el uso de SQL Server Management Studio, existirá una diferencia con la que se obtenga con otras herramientas, o con las características que se presentan en Azure.

Debe tenerse en cuenta que la ventana de Propiedades de Base de Datos presentara las siguientes páginas, las cuales se pueden ver en la parte izquierda de la ventana:

• General – Presenta las propiedades generales de la base de datos
• Archivos – Presenta las propiedades de los archivos conteniendo la base de datos
• Grupo de Archivos – Presenta las propiedades de los grupos de archivos que se han definido para los archivos que contienen la base de datos
• Opciones – Presenta los valores establecidos para distintas opciones de configuración de operación de la base de datos
• Seguimiento de Cambios – Presenta los valores establecidos para llevar a cabo el seguimiento de los cambios realizados en la base de datos.
• Permisos – Presenta los permisos otorgados a para la base de datos
• Propiedades Extendidas – Presenta las propiedades establecidas por los usuarios para la base de datos
• Reflejo – Presenta los valores establecidos para llevar a cabo las actividades de reflejo de las bases de datos
• Envío de registro – Presenta los valores establecidos para llevar a cabo las actividades de envío de registro de la base de datos
• Almacén de consultas – Presenta el comportamiento de las consultas llevadas a cabo en la base de datos.

Las últimas tres páginas de la lista anterior no están presentes en todas las bases de datos, las bases de datos de sistema no cuentan con ellas, dado que no puede establecerse una arquitectura de reflejo o de envío de registros. El uso de un almacén de consultas es una propiedad que fue aparece en Microsoft SQL Server 2016, por lo que esta propiedad no se encontrara en versiones previas.

Página General 

En esta ocasión, se presenta la pagina correspondiente a los aspectos generales de la base de datos, en esta página no es posible modificar los valores. Únicamente se presentarán los valores generales, como se observa a continuación:

Se puede apreciar que las propiedades mostradas están divididas en tres grupos, cabe mencionar que ningún valor de esta página puede ser modificado en ella, lo cual indica que los valores son de solo lectura. A continuación se explicarán cada una de las propiedades que se muestran en esta página.

Copias de seguridad

En esta sección se incluyen dos propiedades relacionadas a la base de datos, debe recordarse que una copia de seguridad es parte integral de la base de datos:

Propiedad	Descripción
Última copia de seguridad de la base de datos	En este espacio se muestra la fecha de la última copia de seguridad de la base de datos, obviamente una base de datos de reciente creación no mostrará algún dato en este espacio. Es importante indicar que es independiente del modelo de recuperación que se haya establecido a la base de datos.

Última copia de seguridad del registro de la base de datos

En este espacio se muestra la fecha en la que se realizó la última copia de seguridad del registro de transacciones de la base de datos. En este espacio se mostrará una fecha siempre que el modelo de recuperación sea completo o registro masivo, ya que el modelo de recuperación simple no permite realizar este tipo de copia de seguridad de registro de transacciones.

Base de Datos

Se refiere a propiedades directamente relacionadas a la base de datos:

Propiedad

Descripción

Nombre

En este espacio de muestra el nombre de la base de datos, este debe ser un nombre único dentro del servidor de Microsoft SQL Server. En muchas ocasiones se genera o define una base de datos se le asigna un nombre único, posteriormente se lleva a cabo una copia de seguridad y se restaura con un nombre diferente, de esta forma se tendrán dos bases de datos estructuralmente iguales con nombre diferente.

Estado

En este espacio se muestra el estado de la base de datos, es decir, el estado operacional de la base de datos, por defecto el estado es normal indicando que la base de datos está en linea, sin embargo, es posible obtener un estado diferente, dependiendo de las circunstancias operativas. Se indicarán los diferentes estados de la base de datos en la página Opciones.

Propietario

Muestra el nombre del propietario de la base de datos.  El propietario se puede cambiar en la página Archivos.

Fecha de Creación

Muestra la fecha y hora en que se creó la base de datos, en el servidor. Cabe mencionar que muchas veces una base de datos es creada en un servidor diferente y al realizar la restauración desde una copia de seguridad, la fecha de la restauración se convierte en la fecha de creación de la base de datos.

Tamaño

Muestra el tamaño actual de la base de datos en megabytes.

Espacio Disponible

Muestra la cantidad de espacio disponible en la base de datos en megabytes.

Número de Usuarios

Muestra el número de usuarios configurados para la base de datos.

En la imagen se pueden apreciar dos propiedades adicionales, que están relacionadas con el uso de objetos optimizados en memoria, estableciendo los valores corrrespondientes a la cantidad de memoria asignada para esos objetos y la cantidad de memoria usada por esos objetos. Cabe mencionar que a partir de la version de Microsoft SQL Server 2014 se puede hacer uso de objetos optimizados en memoria.

Mantenimiento

Relacionado con el servicio de la base de datos. 

Propiedad	Descripción
Intercalación	Muestra la intercalación utilizada por la base de datos.  El tipo de intercalación se puede cambiar en la página Opciones de esta misma ventana.

Conclusión

Esta página inicial de las propiedades de la base de datos brinda información general de la base de datos, en caso de que se requiera más información de la base de datos, se deben acceder a las páginas correspondientes. Una forma alternativa de obtener la información de esta página a través del uso de T-SQL es utilizando el siguiente script para obtener los datos referidos en la página General:

/*******************************************************************************

-- Script : Get Database Properties General

-- Author : Julio J Bueyes

-- julio.bueyes@outlook.com

--

-- Description : This script helps to get a summarized view of the database properties – General 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

-- Declare a table variable to temporary store values 

DECLARE @DBSize TABLE

(

DBName sysname,

dbSize nvarchar(20),

dbUnaloc nvarchar(20),

reserved nvarchar(20),

datasize nvarchar(20),

idxSize nvarchar(20),

unused nvarchar(20)

)

-- Get the Space Used for database into table variable

INSERT INTO @DBSize

exec sp_spaceused   @oneresultset=1;

-- Generate Common Table expressions to generate query 

WITH LastFullBackup (database_name, LastBackupDate)

AS (

SELECT database_name, Max(backup_start_date) 

FROM msdb..backupset

WHERE type = 'D'

GROUP BY database_name

)

,

LastLogBackup (database_name, LastLogDate)

AS

(

SELECT database_name, Max(backup_start_date)

FROM msdb..backupset

WHERE type = 'L'

GROUP BY database_name

)

,

TotUsers (database_name, totUsers)

AS

(

SELECT DB_NAME(), COUNT(1) 

FROM sys.database_principals

WHERE type in ('S','U')

)

SELECT b.LastBackupDate AS [Last Database Backup], 

l.LastLogDate AS [Last Database Log Backup], 

d.name, state_desc as [Status], sl.name AS [Owner], 

d.create_date AS [Date Created], ds.dbSize AS [Size], 

ds.dbUnaloc AS [Space Available], 

t.totUsers as [Number of Users], d.collation_name AS [Collation]

FROM sys.databases d

INNER JOIN sys.syslogins sl ON d.owner_sid = sl.sid

INNER JOIN @DBSize ds ON d.name = ds.DBName COLLATE DATABASE_DEFAULT

LEFT OUTER JOIN LastFullBackup b ON d.name = b.database_name

LEFT OUTER JOIN LastLogBackup l ON d.name = l.database_name

INNER JOIN TotUsers t ON d.name = t.database_name

WHERE d.name = DB_Name() COLLATE DATABASE_DEFAULT;

Como puede observarse es mas fácil obtener los datos directamente de la ventana de propiedades de la base de datos, en la página General, sin embargo, el script funciona para obtener los mismos valores proporcionados, solo que en esta ocasión en un solo renglón.

SQL Server Homologar intercalación en una base de datos

Introducción

Muchas veces cuando estamos desarrollando o bien administrando un servidor de base de datos se presenta la necesidad de adquirir una base de datos que fue generada en un servidor diferente, ya sea por la vía de una restauración o por agregar los archivo correspondientes en el directorio seleccionado, es común que esta base de datos adquirida se presenta con una intercalación diferente a la que se tiene definida en el servidor. 

Intercalación de la instancia de Microsoft SQL Server

Para obtener la intercalación que se tiene en la instancia de Microsoft SQL Server del servidor, se sabe que es la que está definida en la base de datos master, de tal forma que es posible obtener esta información utilizando la consulta siguiente:

SELECT collation_name FROM sys.databases where name = 'master'

Como una mejor práctica de operación, se sabe que es importante que la intercalación de las bases de datos sea la misma que la que se tiene en la configuración de la instancia de Microsoft SQL Server en el servidor para mejorar el rendimiento, ya que en caso contrario será necesario que en las consultas se haga referencia a la intercalación adecuada.

Cambiar la intercalación de la base de datos

El cambio de la intercalación de una base de datos es sencillo realizarlo con las sentencias de Microsoft SQL Server, de tal forma que para cambiar la intercalación utilizamos la siguiente sentencia:

ALTER DATABASE [DatabaseName] COLLATE [CollateName]

Si bien esta sentencia cambia la intercalación de la base de datos, no necesariamente cambia la intercalación de todas las columnas de las tablas de la base de datos. Entonces, qué hacer para ese caso, una solución es abrir la definición de cada tabla y cambiar manualmente la intercalación, o utilizar la sentencia ALTER TABLE para cambiar la intercalación de cada columna, esto puede ser una tarea muy lenta.

Que intercalación usan las columnas de las tablas de la base de datos

Cuando ocurre esta situación hace falta una herramienta o bien un script que nos ayude a cambiar la intercalación de todas las columnas de tipo char, varchar, nchar, nvarchar (en versiones previas de Microsoft SQL Server se utilizaban los tipos de datos ntext o text) que haya en las distintas tablas de la base de datos. Es entonces útil saber que toda la información de la base de datos se encuentra definida en las tablas del sistema, más específicamente la tabla INFORMATION_SCHEMA.COLUMNS contiene todas las columnas definidas de las tablas de la base de datos, por lo que la siguiente consulta nos arrojara la información de todas las columnas de tipo char, varchar, nchar y nvarchar (text y ntext en el caso de las versiones anteriores) de las tablas de la base de datos:

USE DatabaseName

SELECT Table_Schema+'.'+Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE ='BASE TABLE')

  AND (Data_Type = 'char' OR Data_Type = 'varchar' OR Data_Type = 'nchar' OR Data_Type ='nvarchar' OR Data_Type = 'text' OR Data_Type = 'ntext')

ORDER BY Table_Schema,Table_Name

Esto nos dará como resultado las tablas, con sus respectivos esquemas, en nombre de la columna, el tipo y la longitud de la columna, existe una consideración para los tipos de datos varchar(max) y nvarchar(max) que a partir de la versión 2005 se incluye para sustituir los tipos text y ntext, que el valor para la longitud aparece como -1.

Cambiar la intercalación de una columna de la tabla

Para cambiar la intercalación de una columna de los tipos de datos indicados, se debe utilizar el siguiente comando de T-SQL:

ALTER TABLE TableName ALTER COLUMN ColumnName DataType(MaxLength) COLLATE CollateName

Donde; 

• DataType es uno de los tipos char, varchar, nchar, nvarchar, ntext o text
• MaxLength es la longitud de la columna, no se especifica en el caso de text o ntext.
• En una base de datos con 50 tablas en la que participe, se localizaron aproximadamente 210 columnas que contenían los tipos de datos especificados anteriormente. Imagínense que se hiciera la siguiente instrucción por cada una de las columnas identificadas, ello tomaría demasiado tiempo en realizarlo, ademas de lo monótono que se convertiría esa tarea.

Script para realizar el cambio de intercalación en una base de datos

Es por ello que he desarrollado un script que utiliza un cursor para llevar a cabo el cambio en las columnas de todas las tablas por la intercalación especificada, como se muestra a continuación:

/*********************************************************************************************

** Author:  Julio J. Bueyes (jjbueyes@computer.org)

** Date:     Abril, 2009

** Purpose: Standardize the collation of a database and table columns with the collation defined on the server

** DISCLAIMER. Sample Code is provided for the purpose of illustration only and is not intended to be used in a production environment. 

THIS SAMPLE 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. 

**********************************************************************************************/

--- Process variables definition

DECLARE @TableName AS varchar(100)              -- Set the schema and table name 

DECLARE @ColumnName AS varchar(60)             -- Set the column name

DECLARE @DataType AS varchar(10)         -- Set the column data type

DECLARE @MaxLength AS varchar(5)         -- Set the column length

DECLARE @Command AS varchar(1000) -- Set the Sql Command to execute

DECLARE @UseDB  AS varchar(1000)         -- Set the database to use

--- Variables used as constants definition 

DECLARE @Collate AS varchar(50)                 -- Set Collation value

DECLARE @Database AS varchar(50) -- Set Database name

---**** Setting the constant values *****

--- Setting the server collation

SET @collate = (SELECT Collation_name FROM Sys.Databases WHERE Name = 'master')

PRINT 'Collate actually used in Server: ' + @collate;

---******* CHANGE THE DATABASE NAME before execute *******

--- Set the database name to be homologated 

SET @Database = 'DatabaseName';

--- Validate the Database Name

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = @Database)

PRINT 'Must specify a valid database name, look at CHANGE THE DATABASE NAME Section'

ELSE

BEGIN

PRINT 'Database to be standardized: ' + @Database;

--- Set the database collation getting from server 

SET @Command = 'ALTER DATABASE ' + @Database + ' COLLATE ' + @Collate;

EXEC (@Command)

--- **** Initiate process *****

--- Set the database to use

SET @Command = 'USE ' + @Database + CHAR(13);

PRINT @Command

EXEC (@Command);

--- Declaring a cursor with the information of the columns of all tables in the database

DECLARE CurTables CURSOR LOCAL FORWARD_ONLY READ_ONLY 

FOR 

SELECT Table_Schema+'.'+Table_Name, Column_Name, Data_Type, CHARACTER_MAXIMUM_LENGTH 

FROM INFORMATION_SCHEMA.COLUMNS 

WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE')

  AND (Data_Type = 'char' OR Data_Type = 'varchar' OR Data_Type = 'nchar' OR Data_Type = 'nvarchar' OR Data_Type = 'text' OR Data_Type = 'ntext')

ORDER BY Table_Schema,Table_Name;

-- Open the cursor and fetch the first record

OPEN CurTables

FETCH NEXT FROM CurTables INTO @TableName, @ColumnName, @DataType, @MaxLength

-- While there are records in the cursor change columns collation in the database's tables

WHILE @@FETCH_STATUS = 0

BEGIN

       -- Identify text or another data type

       IF @DataType = 'char' OR @DataType = 'varchar' OR @DataType = 'nchar' OR @DataType = 'nvarchar'

           SET @Command = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @DataType + '(' + CASE @MaxLength WHEN -1 THEN 'max' ELSE @MaxLength END + ') COLLATE ' + @Collate

       ELSE

           SET @Command = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @DataType + ' COLLATE ' + @Collate

       -- Log the command before execute

       PRINT @Command

       -- Execute the command

       EXEC (@Command)

       -- Fetch next record from cursor

       FETCH NEXT FROM CurTables INTO @TableName, @ColumnName, @DataType, @MaxLength

END

-- Close and deallocate cursor

CLOSE CurTables

DEALLOCATE CurTables

--- Finishing Process

PRINT 'The columns of the tables in the specified database have been approved with the server collation'; 

END

Conclusión

Como se ha podido observar, con el anterior script que estoy ofreciendo, se obtiene la intercalación del servidor y se aplica a la base de datos y a las columnas de tipo char, nchar, varchar, nvarchar (y en caso de versiones anteriores los tipos de datos text y ntext), con el objetivo de mejorar el desempeño de las consultas y evitar los usos de la especificación de la intercalación en las mismas.

Mucho he de agradecer sus comentarios sobre el uso de este script, con el fin de llevar a cabo las mejoras correspondientes y que pueda servir en futuras versiones de Microsoft SQL Server.