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 limitador o un cuello de botella en un entorno de BizTalk Server de producción. BizTalk Server es una aplicación muy intensiva en bases de datos y, a su vez, la base de datos de SQL Server utilizada por BizTalk Server es muy intensiva en 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. Una implementación correcta de base de datos suele requerir un diseño cuidadoso en las primeras etapas de un proyecto. Este planeamiento o diseño debería tener en cuenta lo siguiente:

• El tipo de disco que se va a utilizar, por ejemplo, los dispositivos RAID (matriz redundante de discos independientes).

• Cómo apportion data on the disks using files and filegroups.How to apportion data on the disks using files and filegroups. 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 SQL Server parámetros de configuración para 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 nunca se pierde un mensaje. 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 una orquestación procesa los mensajes, el mensaje se escribe en la base de datos messageBox en cada punto de persistencia de la orquestación. Estos puntos de persistencia hacen que el cuadro de mensajes escriba el mensaje y el estado relacionado con el disco físico. Con un mayor rendimiento, 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 se ha mostrado para abordar eficazmente 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 mediante un administrador de bases de datos 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 SQL Server del entorno de BizTalk Server.

  SQL Server archivos y grupos de archivos 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 su equipo tiene cuatro discos, puede crear una base de datos que contenga tres archivos de datos y un archivo de registro, y mantener un archivo en cada disco. A medida que se accede a los datos, cuatro encabezados de lectura y escritura pueden acceder simultáneamente a los datos en paralelo. 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 los libros en pantalla de SQL Server en https://go.microsoft.com/fwlink/?LinkID=71419.

  Además, los archivos y 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 demás tablas con menos acceso en la base de datos se pueden ubicar en archivos diferentes en otro grupo de archivos, ubicado en un segundo disco.

  Los cuellos de botella de E/S de archivo se describen con considerable detalle en Cuellos de botella en el nivel de base de datos. El indicador más común de que E/S de archivos (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 cola de disco" es mayor que 3 para cualquier disco determinado en cualquiera de los servidores SQL Server, es probable que la E/S de archivo 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 que sea 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 crear 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 de BizTalk Server predeterminada

Dependiendo 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 PRIMARY, 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
Base de datos de configuración	BizTalkMgmtDb	Almacén central de metadatos para todas las instancias de BizTalk Server del grupo de BizTalk Server.
Base de datos de Cuadro de mensajes de BizTalk	BizTalkMsgBoxDb	Almacena predicados de suscripciones. Es una plataforma host y mantiene las colas y las tablas de estado para cada host de BizTalk Server. La base de datos de cuadro de mensajes también almacena los mensajes y sus propiedades.
Base de datos de seguimiento de BizTalk	BizTalkDTADb	Almacena los datos de supervisión empresarial y de estado de los que realiza el seguimiento el motor de seguimiento de BizTalk Server.
Base de datos de análisis de BAM	BAMAnalysis	SQL Server Analysis Services base de datos que mantiene los datos históricos agregados para actividades empresariales.
Base de datos de esquema de estrella de SAE	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 SAE	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 para las ubicaciones de recepción. Almacena información sobre las aplicaciones afiliadas de SSO, así como las credenciales de usuario cifradas en 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 fáciles de usar para las referencias de datos en reglas.
Base de datos de administración de Analysis Server de seguimiento	BizTalkAnalysisDb	Almacena cubos OLAP de supervisión empresarial y de estado.

Separación de archivos de datos y archivos de registro

Como se indicó anteriormente, una configuración de BizTalk Server predeterminada coloca la base de datos messageBox en un único archivo del grupo de archivos predeterminado. De forma predeterminada, los datos y los registros de transacciones de la base de datos messageBox se colocan en la misma unidad y ruta de acceso. Esto se hace para dar cabida a sistemas con un único 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 del registro se administra de forma independiente del espacio de datos.

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

El origen principal 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 la contención de bases de datos, es la base de datos BizTalk Server Cuadro de mensajes. Esto es cierto en escenarios de cuadro de mensajes únicos y múltiples. Es razonable suponer que hasta el 80 % del valor de la distribución de bases de datos de BizTalk se derivará de optimizar los archivos de datos del Cuadro de mensajes y el archivo de registro. El escenario de ejemplo que se detalla a continuación se centra en optimizar los archivos de datos de una base de datos de Cuadro de mensajes. A continuación, se pueden seguir estos pasos 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.

Agregar manualmente 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

Para los 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 al servidor.

   

2. En el campo Nombre del servidor del cuadro de diálogo Conectar al servidor, escriba el nombre de la instancia de SQL Server que contiene las bases de datos BizTalk Server Cuadro de mensajes 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.

   

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 de 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 de 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 que se agreguen 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 de archivos y archivos a la base de datos de Cuadro de mensajes de BizTalk

El siguiente script SQL de ejemplo realiza las mismas tareas que se completaron manualmente en la sección anterior. En este script de ejemplo se 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 se podría usar 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 la creación de 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 que se muestra en este tema, se prueben exhaustivamente antes de ejecutarse en un entorno de producción.