Optimización de grupos de archivos de base de datos

La contención de entrada y salida de archivos (E/S) suele ser un factor de limitación, o cuello de botella, en un entorno de producción de BizTalk Server. BizTalk Server es una aplicación muy intensiva de base de datos y, a su vez, la base de datos de SQL Server usada por BizTalk Server es muy intensiva de E/S de archivos.

En este tema se describe cómo hacer un uso óptimo de la característica de archivos y grupos de archivos de SQL Server para minimizar la aparición de contención de E/S de archivos y mejorar el rendimiento general de una solución de BizTalk Server.

Información general

Cada solución de BizTalk Server finalmente encontrará contención de E/S de archivos a medida que aumenta el rendimiento. El subsistema de E/S o el motor de almacenamiento es un componente clave de cualquier base de datos relacional. Normalmente, una implementación correcta de la base de datos requiere un planeamiento cuidadoso en las primeras fases de un proyecto. Este planeamiento debe incluir la consideración de los siguientes problemas:

• Qué tipo de hardware de disco se va a usar, como dispositivos RAID (matriz redundante de discos independientes).

• Cómo distribuir datos en los discos usando archivos y grupos de archivos. Para obtener más información sobre el uso de archivos y grupos de archivos en SQL Server, vea Archivos de base de datos y grupos de archivos.

• Implementar el diseño óptimo del índice para mejorar el rendimiento al acceder a los datos. Para obtener más información sobre cómo diseñar índices, vea Diseño de índices.

• Cómo establecer parámetros de configuración de SQL Server para obtener un rendimiento óptimo. Para obtener más información sobre cómo establecer parámetros de configuración óptimos para SQL Server, vea Opciones de configuración del servidor.

  Uno de los principales objetivos de diseño de BizTalk Server es asegurarse de que un mensaje nunca se pierde. Para mitigar la posibilidad de pérdida de mensajes, los mensajes se escriben con frecuencia en la base de datos messageBox a medida que se procesa el mensaje. Cuando los mensajes son procesados por una orquestación, se escribe en la base de datos MessageBox en cada punto de persistencia de la orquestación. Estos puntos de persistencia hacen que MessageBox escriba el mensaje y el estado relacionado en el disco físico. Con mayores tasas de transferencia, esta persistencia puede dar lugar a una contención considerable de disco y puede convertirse en un cuello de botella.

  El uso óptimo de la característica de archivos y grupos de archivos en SQL Server ha demostrado ser eficaz para abordar los cuellos de botella de E/S de archivos y mejorar el rendimiento general en las soluciones de BizTalk Server. Esta optimización solo debe realizarse por un administrador de base de datos de SQL Server experimentado y solo después de que se haya realizado correctamente una copia de seguridad de todas las bases de datos de BizTalk Server. Esta optimización debe realizarse en todos los equipos con SQL Server en el entorno de BizTalk Server.

  Los archivos y grupos de archivos de SQL Server se pueden usar para mejorar el rendimiento de la base de datos, ya que esta funcionalidad permite crear una base de datos en varios discos, varios controladores de disco o sistemas RAID (matriz redundante de discos independientes). Por ejemplo, si el equipo tiene cuatro discos, puede crear una base de datos formada por tres archivos de datos y un archivo de registro, con un archivo en cada disco. A medida que se accede a los datos, cuatro cabezales de lectura y escritura pueden acceder simultáneamente a los datos. Esto acelera significativamente las operaciones de base de datos. Para obtener más información sobre la implementación de soluciones de hardware para discos de SQL Server, vea "Rendimiento de la base de datos" en la Documentación en línea de SQL Server en https://go.microsoft.com/fwlink/?LinkID=71419.

  Además, los archivos y los grupos de archivos permiten la selección de ubicación de datos, ya que las tablas se pueden crear en grupos de archivos específicos. Esto mejora el rendimiento, ya que todas las E/S de archivos de una tabla determinada se pueden dirigir a un disco específico. Por ejemplo, una tabla muy usada se puede colocar en un archivo de un grupo de archivos, ubicado en un disco, y las otras tablas con menos acceso a ellas se pueden ubicar en archivos diferentes de otro grupo de archivos, ubicados en un segundo disco.

  Los cuellos de botella en la E/S de archivos se describen detalladamente en Cuellos de botella en el nivel de base de datos. El indicador más común de que la E/S de archivo (E/S de disco) es un cuello de botella es el valor del contador "Disco físico:Longitud media de la cola de disco". Cuando el valor del contador "Disco físico: Longitud media de la cola de disco" es mayor que 3 para cualquier disco determinado en cualquiera de los servidores SQL, es probable que la E/S de archivos sea un cuello de botella.

  Si la aplicación de la optimización de archivos o grupos de archivos no resuelve un problema de cuello de botella de E/S de archivos, puede ser necesario aumentar el rendimiento del subsistema de disco agregando unidades físicas o SAN adicionales.

  En este tema se describe cómo aplicar manualmente optimizaciones de archivos y grupos de archivos, pero estas optimizaciones también se pueden automatizar mediante scripts. Se proporciona un script SQL de ejemplo al final de este tema. Es importante tener en cuenta que este script tendría que modificarse para dar cabida a la configuración de archivo, grupo de archivos y disco usada por las bases de datos de SQL Server para cualquier solución de BizTalk Server determinada.

Bases de datos creadas con una configuración predeterminada de BizTalk Server

En función de las características que se habilitan al configurar BizTalk Server, se pueden crear hasta 13 bases de datos diferentes en SQL Server y todas estas bases de datos se crean en el grupo de archivos predeterminado. El grupo de archivos predeterminado para SQL Server es el grupo de archivos PRINCIPAL a menos que se cambie el grupo de archivos predeterminado mediante el comando ALTER DATABASE. En la tabla siguiente se enumeran las bases de datos que se crean en SQL Server si todas las características están habilitadas al configurar BizTalk Server.

Bases de datos de BizTalk Server

Base de datos	Nombre de base de datos predeterminado	Descripción
Bases de datos de configuración	BizTalkMgmtDb	Almacén central de metadatos para todas las instancias de BizTalk Server en el grupo de BizTalk Server.
Base de datos MessageBox de BizTalk	BizTalkMsgBoxDb	Almacena predicados de suscripciones. Es una plataforma de host y mantiene las colas y las tablas de estado para cada host de BizTalk Server. La base de datos MessageBox también almacena los mensajes y las propiedades del mensaje.
Base de datos de seguimiento de BizTalk	BizTalkDTADb	Almacena los datos empresariales y de supervisión de salud que lleva a cabo el motor de seguimiento de BizTalk Server.
Base de datos de análisis de BAM	BAMAnalysis	Base de datos de SQL Server Analysis Services que mantiene los datos históricos agregados para actividades empresariales.
Base de datos de esquema estrella de BAM	BAMStarSchema	Transforma los datos recopilados de la supervisión de actividad empresarial para el procesamiento OLAP. Esta base de datos es necesaria cuando se usa la base de datos de análisis de BAM.
Base de datos de importación principal de BAM	BAMPrimaryImport	Almacena los eventos de Actividades empresariales y, a continuación, consulta el progreso y los datos después de las instancias de actividad. Esta base de datos también realiza agregaciones en tiempo real.
Base de datos de archivo de BAM	BAMArchive	Almacena predicados de suscripción. La base de datos de archivo de BAM minimiza la acumulación de datos de actividad empresarial en la base de datos de importación principal de BAM.
Base de datos de SSO	SSODB	Almacena de forma segura la información de configuración de las ubicaciones de recepción. Almacena información sobre las aplicaciones afiliadas de SSO, así como las credenciales de usuario cifradas a todas las aplicaciones afiliadas.
Base de datos del motor de reglas	BizTalkRuleEngineDb	Repositorio para: - Directivas, que son conjuntos de reglas relacionadas. - Vocabularios, que son colecciones de nombres específicos de dominio y fáciles de usar para las referencias de datos en reglas.
Base de datos de administración del servidor de análisis de rastreo	BizTalkAnalysisDb	Almacena cubos OLAP de supervisión empresarial y de salud.

Separación de archivos de datos y archivos de registro

Como se indicó anteriormente, una configuración predeterminada de BizTalk Server coloca la base de datos cuadro de mensajes en un único archivo del grupo de archivos predeterminado. De forma predeterminada, los registros de datos y transacciones de la base de datos messageBox se colocan en la misma unidad y ruta de acceso. Esto se hace para acomodar sistemas con un solo disco. Una única configuración de archivo, grupo de archivos o disco no es óptima en un entorno de producción. Para obtener un rendimiento óptimo, los archivos de datos y los archivos de registro deben colocarse en discos independientes.

Nota:

Los archivos de registro nunca forman parte de un grupo de archivos. El espacio de registro se administra independientemente del espacio de datos.

La regla 80/20 de distribución de bases de datos de BizTalk Server

La principal fuente de contención en la mayoría de las soluciones de BizTalk Server, ya sea debido a la contención de E/S de disco o a la contención de base de datos, es la base de datos MessageBox de BizTalk Server. Esto es cierto en escenarios de cuadro de mensajes únicos y múltiples. Es razonable asumir que hasta el 80% del valor de la distribución de bases de datos de BizTalk se derivará de optimizar los archivos de datos de MessageBox y el archivo de registros. El escenario de ejemplo que se detalla a continuación se centra en optimizar los archivos de datos de una base de datos messageBox. Estos pasos se pueden seguir para otras bases de datos según sea necesario, por ejemplo, si la solución requiere un seguimiento extenso, la base de datos de seguimiento también se puede optimizar.

Adición manual de archivos a la base de datos MessageBox, paso a paso

En esta sección se describen los pasos que se pueden seguir para agregar manualmente archivos a la base de datos messageBox. En este ejemplo se agregan tres grupos de archivos y, a continuación, se agrega un archivo a cada grupo de archivos para distribuir los archivos del Cuadro de mensajes en varios discos.

Nota:

Con fines de las pruebas de rendimiento realizadas para esta guía, los grupos de archivos se optimizaron mediante el uso de un script que se publicará como parte de la Guía de optimizaciones de rendimiento de BizTalk Server. Los pasos siguientes solo se proporcionan con fines de referencia.

Agregar manualmente archivos a la base de datos MessageBox en SQL Server

1. Abra SQL Server Management Studio para mostrar el cuadro de diálogo Conectar con el servidor .

   

2. En el campo Nombre del servidor del cuadro de diálogo Conectar con el servidor , escriba el nombre de la instancia de SQL Server que contiene las bases de datos cuadro de mensajes de BizTalk Server y, a continuación, haga clic en el botón Conectar para mostrar el cuadro de diálogo Microsoft SQL Server Management Studio .

   En el panel Explorador de objetos de SQL Server Management Studio, expanda Bases de datos para ver las bases de datos de esta instancia de SQL Server.

   de objetos

3. Haga clic con el botón derecho en la base de datos para la que agregar los archivos y, a continuación, haga clic en Propiedades para mostrar el cuadro de diálogo Propiedades de la base de datos para la base de datos.

   

4. En el cuadro de diálogo Propiedades de la base de datos , seleccione la página Grupos de archivos. Haga clic en el botón Agregar para crear grupos de archivos adicionales para las bases de datos BizTalkMsgBoxDb. En el ejemplo siguiente, se agregan tres grupos de archivos adicionales.

   

5. En el cuadro de diálogo Propiedades de la base de datos , seleccione la página Archivos .

   Haga clic en el botón Agregar para crear archivos adicionales para agregarlos a los grupos de archivos y, a continuación, haga clic en Aceptar. La base de datos MessageBox ahora se distribuye entre varios discos, lo que proporcionará una ventaja de rendimiento significativa sobre una sola configuración de disco.

   En el ejemplo siguiente, se crea un archivo para cada uno de los grupos de archivos que se crearon anteriormente y cada archivo se coloca en un disco independiente.

   

Script SQL de ejemplo para agregar grupos y archivos al MessageBox de la base de datos de BizTalk

El siguiente script SQL de ejemplo realiza las mismas tareas que se completaron manualmente en la sección anterior. Este script de ejemplo supone la existencia de unidades lógicas distintas G a J. El script crea grupos de archivos y archivos para cada grupo de archivos y coloca los archivos de registro en la unidad J.

Nota:

Dado que SQL Server escribe en sus archivos de registro secuencialmente, no hay ninguna ventaja de rendimiento realizada mediante la creación de varios archivos de registro para una base de datos de SQL Server.

-- Filegroup changes are made using the master database
USE [master]
GO

-- Script-wide declarations
DECLARE @CommandBuffer nvarchar(2048)
DECLARE @FG1_Path nvarchar(1024)
DECLARE @FG2_Path nvarchar(1024)
DECLARE @FG3_Path nvarchar(1024)
DECLARE @Log_Path nvarchar(1024)

-- Set the default path for all filegroups
SET @FG1_Path = N'G:\BizTalkMsgBoxDATA\'
SET @FG2_Path = N'H:\BizTalkMsgBoxDATA\'
SET @FG3_Path = N'I:\BizTalkMsgBoxDATA\'
SET @Log_Path = N'J:\BizTalkMsgBoxLog\'

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG1]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG1_Path +
N'BizTalkMsgBoxDb_FG1.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG1]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG2]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG2_Path +
N'BizTalkMsgBoxDb_FG2.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG2]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] ADD FILEGROUP [BTS_MsgBox_FG3]
SET @CommandBuffer = N'ALTER DATABASE [BizTalkMsgBoxDb] ADD FILE ( NAME = N''BizTalkMsgBoxDb_FG1'', FILENAME = N''' + @FG3_Path +
N'BizTalkMsgBoxDb_FG3.ndf'' , SIZE = 102400KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB ) TO FILEGROUP [BTS_MsgBox_FG3]'
EXECUTE (@CommandBuffer)

ALTER DATABASE [BizTalkMsgBoxDb] MODIFY FILE ( NAME = N'BizTalkMsgBoxDb_log', SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10240KB )

GO -- Completes the previous batch, as necessary

El siguiente script SQL de ejemplo podría usarse para establecer un grupo de archivos determinado como grupo de archivos predeterminado:

USE [BizTalkMsgBoxDb]
GO
declare @isdefault bit
SELECT @isdefault=convert(bit, (status & 0x10)) FROM sysfilegroups WHERE groupname=N'BTS_MsgBox_FG1'
if(@isdefault=0)
ALTER DATABASE [BizTalkMsgBoxDb] MODIFY FILEGROUP [BTS_MsgBox_FG1] DEFAULT
GO

La ventaja de crear scripts es que los scripts pueden realizar varias tareas rápidamente, se pueden reproducir con precisión y reducir la posibilidad de error humano. La desventaja del scripting es que la ejecución de un script escrito incorrectamente puede provocar problemas graves que podrían requerir que las bases de datos de BizTalk Server se vuelvan a configurar desde cero. Por lo tanto, es de suma importancia que los scripts SQL, como el script de ejemplo enumerado en este tema, se prueben exhaustivamente antes de ejecutarse en un entorno de producción.