如何创建SQL Server数据库架构：你需要知道的一切

什么是SQL Server数据库架构？

SQL Server数据库架构是一个命名的逻辑容器，用于将数据库中的相关对象（如表、视图、存储过程等）分组在一起。它既定义了数据的结构，也定义了访问控制的边界。

在SQL Server 2005之前，对象直接与创建它们的用户绑定。从那时起，架构已独立于用户，这意味着DBA可以转移对象所有权或重新调整访问权限，而无需触及应用程序代码。

SQL Server中的内置架构

每个SQL Server数据库都附带一组预定义的架构：

• dbo：用户创建对象的默认架构。如果你创建表时未指定架构，它将位于此处。这对于小型项目没问题，但在大型系统中过度依赖dbo通常意味着设计不佳。
• sys：保留给系统目录视图和内部函数使用。绝不要在此创建对象。
• INFORMATION_SCHEMA：一种符合标准的方式，用于查询数据库结构的元数据，如表名和列定义。它在不同的SQL平台上都能一致地工作。
• guest：与guest用户帐户关联。在大多数生产环境中，应锁定此架构，以阻止没有显式数据库帐户的用户进行访问。

如何在SQL Server中创建架构（两种方法）

你可以使用T-SQL或SQL Server Management Studio（SSMS）图形界面在SQL Server中创建架构。SSMS适用于一次性任务，但大多数DBA更倾向于使用T-SQL——它是可脚本化、可重复的，并且易于在开发、测试和生产环境之间进行版本控制。

方法1：使用T-SQL

以下示例涵盖了四种最常见的架构操作：创建架构、向架构添加对象、在架构之间移动对象以及列出所有现有架构。

1. 基本语法

使用CREATE SCHEMA语句来定义一个新架构。你可以选择指定一个所有者——通常是一个数据库角色或用户。

-- 使用默认所有权创建架构
CREATE SCHEMA Sales;
GO

-- 使用特定所有者创建架构
CREATE SCHEMA Production AUTHORIZATION dbo;
GO 

2. 在架构内部创建表

一旦架构存在，在创建对象时使用架构名.对象名的格式。如果省略架构前缀，SQL Server会将对象放置在你的默认架构中——通常是dbo。

CREATE TABLE Sales.Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATETIME,
    CustomerID INT
);
GO 

3. 在架构之间移动对象

如果对象创建在了错误的架构中，请使用ALTER SCHEMA ... TRANSFER来移动它——无需删除并重新创建。

-- 将表从dbo移动到Sales
ALTER SCHEMA Sales TRANSFER dbo.OldOrders;
GO

4. 列出所有架构

要查看当前数据库中定义的每个架构，请查询sys.schemas目录视图：

SELECT name AS SchemaName, schema_id, principal_id AS OwnerID
FROM sys.schemas; 

方法2：使用SSMS

按照以下步骤通过SSMS界面创建架构：

1. 打开SSMS并连接到你的SQL Server实例。
2. 在对象资源管理器中，展开目标数据库。
3. 展开安全性文件夹。
4. 右键单击架构，然后选择新建架构…。
5. 在常规页面中，在架构名称字段中输入名称（例如，Finance）。
6. 在架构所有者字段中，输入用户或角色名称，或单击搜索以浏览可用选项。
7. （可选）切换到权限页面，以在架构级别授予特定权限——如SELECT、INSERT或UPDATE——给用户或角色。
8. 单击确定保存。

真实世界的架构设计示例

在实践中，将每个表都放入dbo很快就会变得难以管理。企业系统通常会将其组织结构或数据生命周期映射到架构设计中——使数据库更易于导航，并且安全性更容易实施。

以下是四种常见模式：

1. ERP系统

大型ERP系统跨越多个业务功能。架构可以将这些功能清晰地分离：

• production.WorkOrders — 跟踪制造阶段
• procurement.Vendors — 管理供应商关系
• finance.GeneralLedger — 存储敏感的财务数据

这样你就可以授予财务团队对finance架构的完全访问权限，同时完全阻止他们访问production数据。

2. B2B CRM

CRM数据库通常混合了具有非常不同敏感级别和访问模式的数据：

• crm.Leads — 高流量、快速变化的销售活动
• contract.Agreements — 需要更严格访问控制的法律文档
• support.Tickets — 客户服务日志

将合同与销售线索分开，可以使高安全性数据在逻辑上与不断变化的表隔离。

3. 多租户SaaS

一些SaaS平台使用每个租户一个架构的模式，即每个客户拥有自己的架构：

• tenant_acme.Users
• tenant_globex.Users

这提供了强大的数据隔离。当客户离开时，DBA可以删除他们的架构，而不会影响其他任何人的数据。

4. 数据仓库（奖牌架构）

在分析流水线中，架构代表数据就绪的阶段：

• bronze.RawIngestion — 从源头到达的原始未过滤数据
• silver.CleanedData — 去重并格式化后的数据
• gold.Reporting — 准备好供Power BI或Tableau等BI工具使用的聚合表

这确保分析师只从gold层查询干净、可靠的数据——而不会意外地从原始摄入层拉取数据。

SQL架构最佳实践

创建架构很简单。设计一个能够随着数据库增长而保持稳健的架构则需要更多思考。这些实践反映了经验丰富的DBA在生产环境中实际遵循的做法。

1. 使用业务领域命名，而非技术类型

根据业务功能来命名架构——Sales、Inventory、HR——而不是像Tables或StoredProcs这样的对象类型。这能使数据库结构与业务实际运作方式保持一致，并使开发人员更容易定位相关对象，而无需翻遍所有内容。

2. 始终使用两部分命名

始终使用架构前缀来引用对象：Sales.Orders，而不仅仅是Orders。这很重要有两个原因：

• 性能： 如果没有架构前缀，SQL Server会先检查用户的默认架构，然后再回退到dbo，这会给每次查询执行增加不必要的开销。
• 准确性： 如果多个架构中包含同名的表，省略前缀可能会导致完全查询到错误的表。

3. 打破依赖dbo的习惯

dbo很方便，但它不应该是每个对象的默认归属。在较大的系统中，应将其保留给共享的配置表或跨功能工具。对于其他所有内容，请使用专用架构——这是充分利用架构级安全性和逻辑分组的唯一途径。

4. 每个部门或应用程序区域一个架构

对于大型系统，为每个团队或应用程序模块分配一个架构。这使得权限管理变得简单直接：你可以授予市场开发团队对Marketing架构的完全所有权，而不会暴露Payroll中的任何内容。这是在复杂数据库中实施最小权限原则的一种实用方法。

使用i2Stream管理和保护你的SQL Server架构

设计良好的架构只是解决方案的一部分。随着数据库的增长，数据丢失、损坏或计划外停机的风险也在增加，尤其是在迁移、升级或跨平台过渡期间。对于运行SQL Server的企业环境，这意味着需要有一个复制和连续性层，与你的架构设计协同工作。

i2Stream是一款企业级数据库复制解决方案，为同构和异构数据库环境提供实时数据同步、灾难恢复和迁移支持。它是为无法承受停机的生产系统而构建的。

i2Stream的关键特性

• 实时数据同步： i2Stream使用基于日志的捕获技术，以毫秒级延迟复制数据变更——且不会影响源数据库的性能。它同时支持DML和DDL同步，因此架构级别的变更也会与数据变更一起被捕获。
• 无代理设计： 无需在生产系统上安装任何软件。i2Stream在连接时不会侵入你的现有环境，这意味着在复制过程中对你的SQL Server实例零影响。
• 跨平台兼容性： i2Stream支持在Oracle、SQL Server、MySQL、PostgreSQL、DB2以及40多种其他数据库环境之间进行复制，包括Apache Kafka和Hive等大数据平台。这使其成为异构迁移或多平台架构的实用选择。
• 无中断迁移： 整个迁移过程在运行中的生产系统上执行。在验证期间，旧环境和新环境可以并行运行，消除了平台过渡或版本升级期间的停机风险。
• 数据完整性保证： 内置的MD5校验和比对以及事务级一致性检查，可验证复制的数据与源数据匹配。冲突会被自动检测和解决。

对于在多个环境中管理复杂SQL Server架构的团队来说，i2Stream消除了随着增长而来的运维风险。你的架构设计定义了数据的组织方式，而i2Stream则确保该结构及其数据在所需之处保持一致、受保护且可用。

常见问题解答

问1：我可以删除一个包含对象的架构吗？

不可以。如果你尝试删除一个仍然包含对象的架构，SQL Server将返回错误。你需要先移动或删除该架构内的所有对象，然后才能删除它。使用ALTER SCHEMA ... TRANSFER来重新定位对象，或使用DROP TABLE来删除它们，然后运行DROP SCHEMA schema_name。

问2：如何查看SQL Server中的所有架构？

查询sys.schemas目录视图：

SELECT name AS SchemaName, schema_id, principal_id AS OwnerID
FROM sys.schemas; 

在SSMS中，你也可以在对象资源管理器中导航到你的数据库，展开安全性文件夹，然后打开架构节点以查看完整列表。

问3：如何在SQL Server中创建数据库架构？

在T-SQL中使用CREATE SCHEMA语句：

CREATE SCHEMA Sales;
GO 

或者在SSMS中，右键单击安全性下的架构，然后选择新建架构…。请参阅上面“如何创建架构”部分的完整步骤说明。

问4：如何在SQL Server中重命名架构？

SQL Server不支持直接重命名架构。标准的解决方法是：使用所需名称创建一个新架构，使用ALTER SCHEMA ... TRANSFER转移所有对象，更新视图、存储过程和应用程序代码中的所有引用，然后删除旧架构。

结论

SQL Server架构不仅仅是一个组织工具；它是安全、可维护和可扩展数据库的基础。从一开始就正确设计，可以在后续节省大量精力。

关键要点：使用业务领域名称，始终使用架构前缀引用对象，避免过度使用dbo，并使架构结构与你的团队和应用程序实际工作方式保持一致。

对于生产环境，仅靠架构设计是不够的。将其与像英方软件的i2Stream这样可靠的复制解决方案相结合，可以确保随着数据库的发展——无论你是在迁移平台、跨区域扩展，还是并行管理多个环境——你的数据始终保持一致且受到保护。