.NET Core应用多域AD及多租户AAD同步SQL数据库架构咨询

一、目录配置数据库架构设计 #

采用通用基表+专属子表的分层设计，兼顾多AD域、多租户AAD的共性配置与独特参数需求，便于扩展维护。

1. 通用目录配置基表（DirectoryConfigs） #

存储所有目录的共性配置，作为AD/AAD专属配置的父表：

CREATE TABLE DirectoryConfigs (
    DirectoryConfigId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    DirectoryType VARCHAR(10) NOT NULL CHECK (DirectoryType IN ('AD', 'AAD')), -- 区分AD/AAD目录类型
    DirectoryName NVARCHAR(100) NOT NULL, -- 目录名称（如"Corp主域"、"Contoso租户"）
    IsEnabled BIT NOT NULL DEFAULT 1, -- 是否启用同步任务
    SyncIntervalMinutes INT NOT NULL DEFAULT 60, -- 同步周期（分钟）
    LastSyncStartTime DATETIME2, -- 上次同步启动时间
    LastSyncEndTime DATETIME2, -- 上次同步结束时间
    LastSyncStatus VARCHAR(20) CHECK (LastSyncStatus IN ('Success', 'Failed', 'InProgress')), -- 上次同步状态
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE()
);

CREATE INDEX IX_DirectoryConfigs_DirectoryType_IsEnabled ON DirectoryConfigs(DirectoryType, IsEnabled);

2. AD专属配置表（ADDirectoryConfigs） #

存储多域/多林AD的连接与搜索参数，每条记录绑定一个DirectoryConfigs条目：

CREATE TABLE ADDirectoryConfigs (
    ADDirectoryConfigId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    DirectoryConfigId UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES DirectoryConfigs(DirectoryConfigId),
    DomainController NVARCHAR(200) NOT NULL, -- 域控制器地址（如"dc.corp.com"）
    Port INT NOT NULL DEFAULT 389, -- LDAP端口（636用于SSL加密）
    BindUsername NVARCHAR(200) NOT NULL, -- 绑定服务账号（如"corp\sync-service"）
    BindPassword NVARCHAR(200) NOT NULL, -- 绑定密码（建议加密存储）
    SearchRootDN NVARCHAR(500) NOT NULL, -- 搜索根DN（如"DC=corp,DC=com"）
    IsMultiForest BIT NOT NULL DEFAULT 0, -- 是否为多林环境
    ForestTrustEnabled BIT NOT NULL DEFAULT 0, -- 是否启用林信任
    CONSTRAINT UQ_ADDirectoryConfigs_DirectoryConfigId UNIQUE (DirectoryConfigId)
);

3. AAD专属配置表（AADDirectoryConfigs） #

存储多租户AAD的认证与API参数，每条记录绑定一个DirectoryConfigs条目：

CREATE TABLE AADDirectoryConfigs (
    AADDirectoryConfigId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(),
    DirectoryConfigId UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES DirectoryConfigs(DirectoryConfigId),
    TenantId VARCHAR(50) NOT NULL, -- AAD租户ID
    ClientId VARCHAR(100) NOT NULL, -- 应用注册客户端ID
    ClientSecret NVARCHAR(200) NOT NULL, -- 应用注册客户端密钥（建议加密存储）
    TenantDomain NVARCHAR(100) NOT NULL, -- 租户域名（如"contoso.onmicrosoft.com"）
    GraphApiVersion VARCHAR(20) NOT NULL DEFAULT 'v1.0', -- Microsoft Graph API版本
    CONSTRAINT UQ_AADDirectoryConfigs_DirectoryConfigId UNIQUE (DirectoryConfigId)
);

二、DirectoryObjects表设计 #

统一存储AD/AAD同步的用户、组、OU对象，核心通过目录标识绑定数据源，确保同步时精准匹配：

CREATE TABLE DirectoryObjects (
    ObjectId UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), -- 本地数据库唯一ID
    ExternalObjectId NVARCHAR(500) NOT NULL, -- 目录系统原生ID（AD: objectGUID/DN; AAD: objectId）
    DirectoryConfigId UNIQUEIDENTIFIER NOT NULL FOREIGN KEY REFERENCES DirectoryConfigs(DirectoryConfigId), -- 所属目录配置ID
    ObjectType VARCHAR(20) NOT NULL CHECK (ObjectType IN ('User', 'Group', 'OU')), -- 对象类型
    DomainOrTenant NVARCHAR(200), -- AD域名称/AAD租户域名（冗余字段，优化查询效率）
    DisplayName NVARCHAR(200) NOT NULL, -- 对象显示名称
    SamAccountName NVARCHAR(100), -- AD专属：Sam账号
    UserPrincipalName NVARCHAR(200), -- AD/AAD通用：UPN
    Description NVARCHAR(500), -- 对象描述
    IsDeleted BIT NOT NULL DEFAULT 0, -- 软删除标记（AD/AAD删除对象时标记）
    LastSyncTime DATETIME2 NOT NULL DEFAULT GETUTCDATE(), -- 上次同步时间
    ChangeType VARCHAR(10) CHECK (ChangeType IN ('Create', 'Update', 'Delete')), -- 上次同步变更类型
    ExtendedProperties NVARCHAR(MAX), -- 扩展属性（JSON格式，存储AD/AAD非核心字段）
    CreatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE()
);

-- 联合唯一约束：避免同一目录下重复同步相同对象
CREATE UNIQUE INDEX UQ_DirectoryObjects_DirectoryConfigId_ExternalObjectId ON DirectoryObjects(DirectoryConfigId, ExternalObjectId);
-- 索引：优化同步时的查询效率
CREATE INDEX IX_DirectoryObjects_DirectoryConfigId_ObjectType_IsDeleted ON DirectoryObjects(DirectoryConfigId, ObjectType, IsDeleted);
CREATE INDEX IX_DirectoryObjects_LastSyncTime ON DirectoryObjects(LastSyncTime);

关键字段说明 #

• DirectoryConfigId：核心关联字段，直接绑定到对应的AD/AAD配置，同步任务启动时可通过该字段筛选出当前目录的所有对象，确保数据源匹配。
• ExternalObjectId：存储目录系统原生ID，用于同步时比对对象是否存在、是否变更。
• IsDeleted：软删除标记，保留历史数据便于追溯，避免物理删除导致关联数据丢失。
• ExtendedProperties：用JSON存储AD/AAD非核心属性（如AD的userAccountControl、AAD的mail），避免表结构臃肿，支持灵活扩展。

三、同步逻辑核心建议 #

1. 目录匹配：每次同步任务启动时，先查询DirectoryConfigs中IsEnabled=1的条目，根据DirectoryType加载对应AD/AAD专属配置，再逐个执行同步。
2. 变更检测：
   • AD：利用uSNChanged属性或LDAP增量查询获取上次同步后的变更对象，避免全量同步。
   • AAD：使用Microsoft Graph Delta查询接口，高效获取增量变更。
3. 更新策略：
   • 新增对象：插入DirectoryObjects新记录，ChangeType设为Create。
   • 修改对象：更新现有记录属性，ChangeType设为Update，刷新LastSyncTime。
   • 删除对象：标记IsDeleted=1，ChangeType设为Delete，不物理删除记录。

内容的提问来源于stack exchange，提问作者Imran Yaseen