Step-by-step install checklist

Before you begin: verify BizTalk 2020 + target SQL Server version support in your environment (apply the BizTalk 2020 platform-alignment CU if you plan to use Windows Server 2022 / SQL Server 2022). Microsoft published the platform alignment CU for BizTalk 2020 that adds SQL Server 2022 support — confirm you have the right CU before production installs. (support.microsoft.com, Microsoft Learn)

A — Planning & prerequisites (do this first)

Select SQL edition
- For production high-throughput: Enterprise (HA features, performance). For small environments Standard may suffice.
OS / BizTalk CU compatibility
- Choose Windows Server and SQL versions compatible with BizTalk 2020 + CU. (If you need SQL Server 2022, ensure BizTalk CU that adds support is installed). (support.microsoft.com, Microsoft Learn)
Service accounts (domain accounts) — create low-privilege accounts for:
- SQL Server Database Engine service
- SQL Server Agent service
- BizTalk host/service accounts (BizTalk host instances, SSO, BTS Group)
- SSO Master Secret server account (and a secure place for the SSO backup passphrase!)
Network & security — ensure domain membership, DNS, time sync (NTP), required ports open, and firewall rules for SQL & BizTalk.
Backups / DR plan — predefine backup location, retention, and DR approach (FCI, AG, log shipping). Use automated, tested restore drills. (Microsoft Learn)

B — Storage layout & sizing (very important)

Design separate volumes (preferably on physically separate spindles / SAN LUNs or NVMe tiers):

C:\ — OS & SQL binaries (small)
D:\SQL_DATA\ — user database data files (BizTalk DBs) — on striped LUNs (RAID10 recommended in production)
E:\SQL_LOG\ — transaction logs (separate, low-latency disks)
F:\TEMPDB\ — tempdb data files (fastest storage possible, separate)
G:\SQL_BACKUP\ — backup destination (separate volume, ideally on different storage system or network share)

Notes: “Striped sets” = striping across disks/LUNs to improve throughput, but do not use RAID-0 without redundancy in production — prefer RAID10/mirrored stripes. Place TempDB on the fastest tier and log files on a dedicated, low-latency volume.

C — Install SQL Server (unclustered / single-instance using striped storage)

Provision Windows Server for SQL (patch OS).
Add SQL service domain accounts to appropriate groups; give the SQL service account the “Perform volume maintenance tasks” right if you want Instant File Initialization (recommended; see below). (Microsoft Learn)
Run SQL Server installer:
- During Server Configuration: check “Grant Perform Volume Maintenance Task privilege to SQL Server Database Engine Service” if desired.
- Set installation paths (BIN to C:\, DATA default paths to your designated DATA volume if you want).
- Use mixed or Windows auth as per policy (BizTalk commonly uses Windows accounts).
After install, set instance-level options (see scripts below): max server memory, MAXDOP, cost threshold for parallelism. (Microsoft Learn)

D — Install SQL Server (clustered — Failover Cluster Instance / FCI)

Create and validate the Windows Server Failover Cluster (WSFC) across the nodes (network, cluster quorum, shared storage prepared). (Microsoft Learn)
Prepare shared storage for SQL DATA/LOG (or use storage fabric that supports shared disks as required for FCI).
Run SQL Server Setup to create the FCI (install on the active node, then add nodes). Follow Microsoft FCI checklist (cluster validation, permissions, network). (Microsoft Learn)
Configure tempdb, file locations on shared storage (or on local SSD if using AGs for replica topology instead of FCI). For AGs consider separate replica disk/layout decisions. Always test failovers and BizTalk behavior. (Microsoft Learn)

E — Post-install SQL configuration (do this immediately after SQL is up)

Enable Instant File Initialization (if not enabled at install): grant SQL service account Perform volume maintenance tasks via Local Security Policy or GPO. Note: IFI applies only to data files (not log files). (Microsoft Learn, docs.aws.amazon.com)
TempDB sizing & files: create multiple equal-sized tempdb data files (start with up to 8 files or match logical CPUs up to a point — Microsoft guidance: default installer may create up to 8; start there and tune). Pre-size to avoid autogrowth. (Microsoft Learn)
Set max server memory so OS and BizTalk hosts have breathing room (don’t give SQL everything). See Microsoft guidance and use a conservative OS buffer (4GB or ~10% as starting rule, then tune). (Microsoft Learn)
Set MAXDOP following MS NUMA/core guidance (common starting value is 8 or lower; tune by CPU topology). (Microsoft Learn)
Autogrowth policy: set file growth in MB (not percent), pre-size DBs where possible, and disable auto-shrink.
SQL Agent & Maintenance: enable SQL Server Agent; deploy robust backup+index+integrity jobs (I strongly recommend using Ola Hallengren’s Maintenance Solution — it’s widely used and configurable). (ola.hallengren.com)

F — Install BizTalk Server 2020 and configure BizTalk databases

Install BizTalk prerequisites as per Microsoft’s BizTalk setup docs (IIS, AD, Visual C++ runtime, etc.). (Microsoft Learn)
Run BizTalk Server setup as Administrator on BizTalk servers. When running the BizTalk Configuration wizard: point the DB configuration to your SQL instance (use the FCI virtual name or AG listener as appropriate for clustered setups). (Microsoft Learn)
After configuration, verify BizTalk created the databases (MessageBox, Management, Tracking, DTA, SSO, BAM if installed). Confirm DBs recovery model = FULL for BizTalk DBs (so log shipping / backups are effective). (Microsoft Learn)

G — Validation & go-live checklist

SQL Server Agent job list: ensure BizTalk SQL Agent jobs exist and run (MessageBox maintenance, DTA purge & archive, tracking jobs). (Microsoft Learn)
Run a simple send/receive / orchestration test and monitor BizTalk MessageBox waits and HWA.
Monitor tempdb allocation contention, disk latency, and SQL wait stats for first 24–72 hours under load and tune (add tempdb files or change MAXDOP if needed). (Microsoft Learn)

Example SQL instance configuration scripts

Put these in a \scripts folder and run as sa / sysadmin. Edit all <PLACEHOLDERS> before running. Scripts are intentionally idempotent-friendly (where possible).

Script 1 — Instance settings: `Instance_Config.sql`

Sets max server memory, MAXDOP, cost threshold for parallelism.

-- Instance_Config.sql
-- Run on the SQL instance as sysadmin. Replace placeholders.

USE master;
GO

-- Allow advanced options to be changed
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO

-- 1) Set max server memory (MB)
-- Replace <MAX_MEM_MB> with the number of MB you want SQL to use, e.g. 122880 for 120 GB.
EXEC sp_configure 'max server memory (MB)', <MAX_MEM_MB>;
RECONFIGURE;
GO

-- 2) Set MAXDOP (recommendation: start with 8 or follow MS NUMA guidance)
-- Replace <MAXDOP> with e.g. 8
EXEC sp_configure 'max degree of parallelism', <MAXDOP>;
RECONFIGURE;
GO

-- 3) Optional: set Cost Threshold for Parallelism (default 5). Consider raising e.g. to 25-50 for OLTP.
-- Replace <COST_THRESHOLD> with e.g. 50
EXEC sp_configure 'cost threshold for parallelism', <COST_THRESHOLD>;
RECONFIGURE;
GO

-- 4) Verify current values
SELECT name, value, value_in_use
FROM sys.configurations
WHERE name IN ('max server memory (MB)', 'max degree of parallelism', 'cost threshold for parallelism');
GO

Notes & guidance: calculate <MAX_MEM_MB> by total server RAM minus OS + BizTalk host reservation. A common starting heuristic: reserve at least 4 GB or ~10% of RAM (whichever is larger) for OS & other processes, then give remaining to SQL — then tune in production. See Microsoft memory config guidance. (Microsoft Learn)

Script 2 — TempDB config: `Configure_TempDB.sql`

Create / modify tempdb to have 8 equal data files and one log file. Update file paths to your tempdb volume.

-- Configure_TempDB.sql
-- Run as sysadmin. Edit file paths and sizes.
USE master;
GO

-- Example values (edit these):
-- Number of data files you want: @numFiles (start with 4-8 depending on cores)
-- Initial size per file in MB: @fileSizeMB
DECLARE @numFiles INT = 8;         -- set to 4..8 (or start with number of logical CPUs up to 8)
DECLARE @fileSizeMB INT = 8192;    -- e.g. 8192 = 8 GB
DECLARE @filegrowthMB INT = 512;   -- autogrowth in MB
DECLARE @basePath NVARCHAR(260) = N'E:\MSSQL\TEMPDB\'; -- change to your tempdb folder

-- Adjust primary tempdb file (tempdev) to match desired size
ALTER DATABASE tempdb
 MODIFY FILE (NAME = tempdev, SIZE = @fileSizeMB + 'MB', FILEGROWTH = @filegrowthMB + 'MB');
GO

-- Add additional files if they do not exist
DECLARE @i INT = 2;
WHILE @i <= @numFiles
BEGIN
    DECLARE @logicalName NVARCHAR(50) = 'tempdev' + CONVERT(NVARCHAR(10), @i);
    DECLARE @fileName NVARCHAR(260) = @basePath + 'tempdb' + CONVERT(NVARCHAR(10), @i) + '.ndf';

    IF NOT EXISTS (SELECT 1 FROM sys.master_files mf WHERE mf.database_id = DB_ID('tempdb') AND mf.name = @logicalName)
    BEGIN
        DECLARE @sql NVARCHAR(MAX) = N'ALTER DATABASE tempdb ADD FILE (NAME = N''' + @logicalName + N''', FILENAME = N''' + @fileName + N''', SIZE = ' 
                                   + CONVERT(NVARCHAR(12), @fileSizeMB) + N'MB, FILEGROWTH = ' + CONVERT(NVARCHAR(12), @filegrowthMB) + N'MB);';
        EXEC(@sql);
    END

    SET @i = @i + 1;
END
GO

-- Show tempdb file layout
SELECT name, physical_name, size/128 AS SizeMB, max_size, growth
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
GO

-- NOTE: some changes (reducing file sizes) may require restart. Adding files is immediate.

Important tempdb guidance: start with multiple equal-sized files (Microsoft guidance: installer sets up to 8 by default; start with up to 8 and tune from there). Pre-size to avoid frequent auto-growth. Monitor allocation contention (GAM/SGAM latch waits) and increase files if needed. (Microsoft Learn)

Script 3 — Quick & safe backup job examples (recommend: use Ola Hallengren)

Rather than hand-rolling backup jobs, I strongly recommend deploying Ola Hallengren’s Maintenance Solution (it creates tested backup, integrity and index jobs). Download and install MaintenanceSolution.sql and then schedule its stored procedures for full/diff/log backups. (ola.hallengren.com)

If you still want minimal example T-SQL job steps for a transaction log backup for BizTalk DBs (illustrative only — replace share path and database names):

-- Example: run as SQL Agent job step (T-SQL)
DECLARE @db NVARCHAR(128);
DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases
WHERE name IN ('BizTalkMsgBoxDb', 'BizTalkDTADB', 'BizTalkMgmtDb', 'SSODB') -- add your BizTalk DB names
AND state_desc = 'ONLINE';

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @cmd NVARCHAR(4000) = N'BACKUP LOG [' + @db + '] TO DISK = N''\\backup-server\sql\'+ @db + '_LOG_' + CONVERT(varchar(8),GETDATE(),112) + '.trn'' WITH INIT, STATS = 10;';
    EXEC(@cmd);
    FETCH NEXT FROM db_cursor INTO @db;
END

CLOSE db_cursor;
DEALLOCATE db_cursor;

But: use Ola’s DatabaseBackup stored procedure instead — it’s robust, handles compression/retention, and is well tested. (ola.hallengren.com)

How to run these safely & next steps

Test in a lab first — especially clustering/FCI and BizTalk configuration with the chosen SQL layout. Confirm BizTalk CU and SQL version compatibility beforehand. (support.microsoft.com)
Run instance scripts as a SQL sysadmin during maintenance windows. For tempdb, run during low activity; adding files is online, but file size reductions sometimes require restart. (Microsoft Learn)
Install Ola Hallengren for backups/index maintenance — it simplifies and professionalizes backup/restore and index maintenance. (ola.hallengren.com)

Selected references (official reading)

BizTalk Server 2020 hardware & software requirements; install docs. (Microsoft Learn)
BizTalk 2020 platform alignment CU adding SQL Server 2022 / Windows Server 2022 support. (support.microsoft.com)
TempDB configuration & allocation contention guidance (Microsoft). (Microsoft Learn)
Instant File Initialization (how to enable). (Microsoft Learn)
MAXDOP & memory config guidance (Microsoft). (Microsoft Learn)
SQL Server Failover Cluster Instance install guidance (Microsoft). (Microsoft Learn)
BizTalk databases and maintenance checklist (Microsoft). (Microsoft Learn)
Ola Hallengren maintenance solution (recommended). (ola.hallengren.com)

BizTalk step by step install checklist

JR IT Services

BizTalk step by step install checklist

Step-by-step install checklist

A — Planning & prerequisites (do this first)

B — Storage layout & sizing (very important)

C — Install SQL Server (unclustered / single-instance using striped storage)

D — Install SQL Server (clustered — Failover Cluster Instance / FCI)

E — Post-install SQL configuration (do this immediately after SQL is up)

F — Install BizTalk Server 2020 and configure BizTalk databases

G — Validation & go-live checklist

Example SQL instance configuration scripts

Script 1 — Instance settings: `Instance_Config.sql`

Script 2 — TempDB config: `Configure_TempDB.sql`

Script 3 — Quick & safe backup job examples (recommend: use Ola Hallengren)

How to run these safely & next steps

Selected references (official reading)

Johannes Rest

Schreibe einen Kommentar Antwort abbrechen

Step-by-step install checklist

A — Planning & prerequisites (do this first)

B — Storage layout & sizing (very important)

C — Install SQL Server (unclustered / single-instance using striped storage)

D — Install SQL Server (clustered — Failover Cluster Instance / FCI)

E — Post-install SQL configuration (do this immediately after SQL is up)

F — Install BizTalk Server 2020 and configure BizTalk databases

G — Validation & go-live checklist

Example SQL instance configuration scripts

Script 1 — Instance settings: Instance_Config.sql

Script 2 — TempDB config: Configure_TempDB.sql

Script 3 — Quick & safe backup job examples (recommend: use Ola Hallengren)

How to run these safely & next steps

Selected references (official reading)

Johannes Rest

Beitragsnavigation

Schreibe einen Kommentar Antwort abbrechen

Script 1 — Instance settings: `Instance_Config.sql`

Script 2 — TempDB config: `Configure_TempDB.sql`