Ottimizzazione dei filegroup di database
La contesa di input/output dei file (I/O) è spesso un fattore di limitazione o un collo di bottiglia in un ambiente di produzione BizTalk Server. BizTalk Server è un'applicazione a elevato utilizzo di database e, a sua volta, il database SQL Server usato da BizTalk Server è molto intensivo per le operazioni di I/O dei file.
In questo argomento viene descritto come usare in modo ottimale la funzionalità file e filegroup di SQL Server per ridurre al minimo l'occorrenza di conflitti di I/O dei file e migliorare le prestazioni complessive di una soluzione BizTalk Server.
Panoramica
Ogni soluzione BizTalk Server incontrerà alla fine una contesa di I/O dei file man mano che la velocità effettiva viene aumentata. Il sottosistema di I/O o il motore di archiviazione è un componente chiave di qualsiasi database relazionale. Per la corretta implementazione di un database è in genere necessaria una pianificazione accurata nelle fasi iniziali del progetto. Nella pianificazione è necessario prendere in considerazione gli aspetti seguenti:
Scelta del tipo di dischi da utilizzare, ad esempio dispositivi RAID (Redundant Array of Independent Disks).
Come suddividere i dati sui dischi usando file e filegroup. Per altre informazioni sull'uso di file e filegroup in SQL Server, vedere File di database e filegroup.
Implementazione della progettazione ottimale dell'indice per migliorare le prestazioni durante l'accesso ai dati. Per altre informazioni sulla progettazione di indici, vedere Progettazione di indici.
Come impostare SQL Server parametri di configurazione per ottenere prestazioni ottimali. Per altre informazioni sull'impostazione di parametri di configurazione ottimali per SQL Server, vedere Opzioni di configurazione del server.
Uno degli obiettivi principali della progettazione di BizTalk Server è garantire che un messaggio non venga mai perso. Per ridurre la possibilità di perdita di messaggi, i messaggi vengono spesso scritti nel database MessageBox durante l'elaborazione del messaggio. Quando i messaggi vengono elaborati da un'orchestrazione, il messaggio viene scritto nel database MessageBox in ogni punto di persistenza nell'orchestrazione. Questi punti di persistenza fanno sì che MessageBox scriva il messaggio e lo stato correlato su disco fisico. Con velocità effettiva più elevate, questa persistenza può comportare una notevole contesa del disco e può potenzialmente diventare un collo di bottiglia.
L'uso ottimale delle funzionalità file e filegroup in SQL Server è stato illustrato per risolvere in modo efficace i colli di bottiglia di I/O dei file e migliorare le prestazioni complessive nelle soluzioni BizTalk Server. Questa ottimizzazione deve essere eseguita solo da un SQL Server amministratore del database esperto e solo dopo il backup corretto di tutti i database BizTalk Server. Questa ottimizzazione deve essere eseguita su tutti i computer SQL Server nell'ambiente BizTalk Server.
SQL Server file e filegroup possono essere utilizzati per migliorare le prestazioni del database perché questa funzionalità consente la creazione di un database su più dischi, più controller del disco o sistemi RAID (matrice ridondante di dischi indipendenti). Se, ad esempio, nel computer sono disponibili quattro dischi, è possibile creare un database che include tre file di dati e un file di log, con un file su ogni disco. Quando si accede ai dati, quattro teste di lettura/scrittura possono accedere simultaneamente ai dati in parallelo. Ciò accelera notevolmente le operazioni del database. Per altre informazioni sull'implementazione di soluzioni hardware per i dischi SQL Server, vedere "Prestazioni del database" nella documentazione online di SQL Server all'indirizzo https://go.microsoft.com/fwlink/?LinkID=71419.
Inoltre, i file e i filegroup consentono il posizionamento dei dati, perché le tabelle possono essere create in filegroup specifici. Ciò migliora le prestazioni, perché tutte le operazioni di I/O dei file per una determinata tabella possono essere indirizzate a un disco specifico. Ad esempio, una tabella usata di frequente può essere inserita in un filegroup in un filegroup, che si trova su un disco e le altre tabelle meno accessibili nel database possono trovarsi in file diversi in un altro filegroup, che si trova su un secondo disco.
I colli di bottiglia di I/O dei file sono descritti in dettaglio in Colli di bottiglia nel livello di database. L'indicatore più comune che l'I/O del file (I/O del disco) è un collo di bottiglia è il valore del contatore "Physical Disk:Average Disk Queue Length". Quando il valore del contatore "Physical Disk:Average Disk Queue Length" è maggiore di circa 3 per qualsiasi disco specificato in uno dei server SQL, è probabile che l'I/O del file sia un collo di bottiglia.
Se l'applicazione dell'ottimizzazione di file o filegroup non risolve un problema di collo di bottiglia di I/O di file, potrebbe essere necessario aumentare la velocità effettiva del sottosistema del disco aggiungendo unità fisiche o SAN aggiuntive.
Questo argomento descrive come applicare manualmente le ottimizzazioni di file e filegroup, ma queste ottimizzazioni possono anche essere scriptate. Alla fine di questo argomento viene fornito uno script SQL di esempio. È importante notare che questo script deve essere modificato per contenere il file, il filegroup e la configurazione del disco usati dai database SQL Server per qualsiasi soluzione di BizTalk Server specificata.
Database creati con una configurazione di BizTalk Server predefinita
A seconda delle funzionalità abilitate durante la configurazione di BizTalk Server, è possibile creare fino a 13 database diversi in SQL Server e tutti questi database vengono creati nel gruppo di file predefinito. Il filegroup predefinito per SQL Server è il filegroup PRIMARY, a meno che il filegroup predefinito non venga modificato tramite il comando ALTER DATABASE. La tabella seguente elenca i database creati in SQL Server se tutte le funzionalità sono abilitate durante la configurazione di BizTalk Server.
Database di BizTalk Server
| Database | Nome database predefinito | Descrizione |
|---|---|---|
| Database di configurazione | BizTalkMgmtDb | Archivio meta-informazioni centrale per tutte le istanze di BizTalk Server nel gruppo di BizTalk Server. |
| Database MessageBox BizTalk | BizTalkMsgBoxDb | Archivia i predicati delle sottoscrizioni. Si tratta di una piattaforma host e mantiene le code e le tabelle di stato per ogni host BizTalk Server. Nel database MessageBox vengono inoltre memorizzati i messaggi e le proprietà dei messaggi. |
| Database di rilevamento BizTalk | BizTalkDTADb | Archivia i dati di monitoraggio dell'integrità e aziendali rilevati dal motore di rilevamento BizTalk Server. |
| Database di analisi BAM | BAMAnalysis | SQL Server Analysis Services database che mantiene i dati cronologici aggregati per le attività aziendali. |
| Database con schema a stella BAM | BAMStarSchema | Trasforma i dati raccolti dal monitoraggio delle attività aziendali per l'elaborazione OLAP. Questo database è necessario quando si usa il database di analisi BAM. |
| Database di importazione primaria BAM | BAMPrimaryImport | Archivia gli eventi dalle attività aziendali e quindi esegue query sullo stato di avanzamento e i dati dopo le istanze dell'attività. Questo database esegue anche aggregazioni in tempo reale. |
| Database di archiviazione BAM | BAMArchive | Archivia i predicati di sottoscrizione. Il database di archiviazione BAM riduce al minimo l'accumulo dei dati delle attività aziendali nel database di importazione primaria BAM. |
| database SSO | SSODB | Archivia in modo sicuro le informazioni di configurazione per le posizioni di ricezione. Archivia le informazioni per le applicazioni affiliate SSO, nonché le credenziali utente crittografate per tutte le applicazioni affiliate. |
| database Motore regole di business | BizTalkRuleEngineDb | Repository per: - Criteri, che sono set di regole correlate. - Vocabolari, ovvero raccolte di nomi descrittivi e specifici del dominio per i riferimenti ai dati nelle regole. |
| Monitoraggio del database di amministrazione di Analysis Server | BizTalkAnalysisDb | Archivia cubi OLAP per il monitoraggio dell'integrità e dell'azienda. |
Separazione dei file di dati e dei file di log
Come indicato in precedenza, una configurazione di BizTalk Server predefinita inserisce il database MessageBox in un singolo file nel filegroup predefinito. Per impostazione predefinita, i dati e i log delle transazioni per il database MessageBox vengono inseriti nella stessa unità e percorso. Questa operazione viene eseguita per supportare i sistemi con un singolo disco. Una singola configurazione file/filegroup/disco non è ottimale in un ambiente di produzione. Per ottenere prestazioni ottimali, i file di dati e i file di log devono essere posizionati su dischi separati.
Nota
I file di log non vengono mai inclusi in un filegroup. Lo spazio del log viene gestito separatamente rispetto allo spazio dei dati.
Regola 80/20 per la distribuzione di database BizTalk Server
L'origine principale della contesa nella maggior parte delle soluzioni BizTalk Server, a causa di conflitti di I/O su disco o conflitti di database, è il database messageBox BizTalk Server. Questo vale sia negli scenari single che multi-MessageBox. È ragionevole presupporre che l'80% del valore di distribuzione dei database BizTalk sia derivato dall'ottimizzazione dei file di dati e del file di log messageBox. Lo scenario di esempio seguente è incentrato sull'ottimizzazione dei file di dati per un database MessageBox. Questi passaggi possono quindi essere seguiti per altri database in base alle esigenze, ad esempio se la soluzione richiede un rilevamento completo, il database di rilevamento può essere ottimizzato anche.
Aggiunta manuale di file al database MessageBox, passaggio successivo
Questa sezione descrive i passaggi che possono essere seguiti per aggiungere manualmente i file al database MessageBox. In questo esempio vengono aggiunti tre filegroup e quindi un file viene aggiunto a ogni filegroup per distribuire i file per MessageBox in più dischi.
Nota
Ai fini dei test delle prestazioni eseguiti per questa guida, i filegroup sono stati ottimizzati tramite l'uso di uno script che verrà pubblicato come parte della guida alle ottimizzazioni delle prestazioni di BizTalk Server. I passaggi seguenti vengono forniti solo a scopo di riferimento.
Aggiungere manualmente file al database MessageBox in SQL Server
Aprire SQL Server Management Studio per visualizzare la finestra di dialogo Connetti al server.
Nel campo Nome server della finestra di dialogo Connetti al server immettere il nome dell'istanza di SQL Server che ospita i database messageBox BizTalk Server e quindi fare clic sul pulsante Connetti per visualizzare la finestra di dialogo Microsoft SQL Server Management Studio.
Nel riquadro Esplora oggetti di SQL Server Management Studio espandere Database per visualizzare i database per questa istanza di SQL Server.
Fare clic con il pulsante destro del mouse sul database per cui aggiungere i file e quindi scegliere Proprietà per visualizzare la finestra di dialogo Proprietà database per il database.
Nella finestra di dialogo Proprietà database selezionare la pagina Filegroup. Fare clic sul pulsante Aggiungi per creare filegroup aggiuntivi per i database BizTalkMsgBoxDb. Nell'esempio seguente vengono aggiunti tre filegroup aggiuntivi.
Nella finestra di dialogo Proprietà database selezionare la pagina File .
Fare clic sul pulsante Aggiungi per creare file aggiuntivi da aggiungere ai filegroup e quindi fare clic su OK. Il database MessageBox è ora distribuito in più dischi, che offre un vantaggio significativo sulle prestazioni rispetto a una singola configurazione del disco.
Nell'esempio seguente viene creato un file per ogni filegroup creato in precedenza e ogni file viene inserito in un disco separato.
Script SQL di esempio per l'aggiunta di filegroup e file al database BizTalk MessageBox
Lo script SQL di esempio seguente esegue le stesse attività completate manualmente nella sezione precedente. Questo script di esempio presuppone l'esistenza di unità logiche distinte G tramite J. Lo script crea filegroup e file per ogni filegroup e inserisce i file di log nell'unità J.
Nota
Poiché SQL Server scrive nei file di log in sequenza, non esiste alcun vantaggio sulle prestazioni realizzato creando più file di log per un database 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
Lo script SQL di esempio seguente può essere usato per impostare un filegroup specifico come filegroup predefinito:
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
Il vantaggio per lo scripting è che gli script possono eseguire più attività rapidamente, possono essere riprodotti in modo preciso e ridurre la possibilità di errore umano. Lo svantaggio dello scripting è che l'esecuzione di uno script scritto in modo errato può causare problemi gravi che potrebbero richiedere la riconfigurazione dei database di BizTalk Server da zero. Pertanto, è di massima importanza che gli script SQL, ad esempio lo script di esempio elencato in questo argomento, vengano testati accuratamente prima di essere eseguiti in un ambiente di produzione.