迁移要扩展的现有数据库Migrate existing databases to scale out

适用于:是Azure SQL 数据库 APPLIES TO: yesAzure SQL Database

使用工具(例如弹性数据库客户端库)轻松管理现有的横向扩展共享数据库。Easily manage your existing scaled-out sharded databases using tools (such as the Elastic Database client library). 先转换现有数据库集,再使用分片映射管理器First convert an existing set of databases to use the shard map manager.

概述Overview

迁移现有的分片数据库:To migrate an existing sharded database:

  1. 准备分片映射管理器数据库Prepare the shard map manager database.
  2. 创建分片映射。Create the shard map.
  3. 准备各个分片。Prepare the individual shards.
  4. 将映射添加到分片映射。Add mappings to the shard map.

可使用 .NET Framework 客户端库或者 Azure SQL 数据库 - 弹性数据库工具脚本中提供的 PowerShell 脚本来实现这些技巧。These techniques can be implemented using either the .NET Framework client library, or the PowerShell scripts found at Azure SQL Database - Elastic Database tools scripts. 以下示例使用 PowerShell 脚本。The examples here use the PowerShell scripts.

有关 ShardMapManager 的详细信息,请参阅分片映射管理For more information about the ShardMapManager, see Shard map management. 有关弹性数据库工具的概述,请参阅弹性数据库功能概述For an overview of the Elastic Database tools, see Elastic Database features overview.

准备分片映射管理器数据库Prepare the shard map manager database

分片映射管理器是一个特殊的数据库,其中包含用于管理已扩展数据库的数据。The shard map manager is a special database that contains the data to manage scaled-out databases. 可以使用现有数据库,或创建新的数据库。You can use an existing database, or create a new database. 用作分片映射管理器的数据库不应是与分片相同的数据库。A database acting as shard map manager should not be the same database as a shard. PowerShell 脚本不会创建该数据库。The PowerShell script does not create the database for you.

步骤 1:创建分片映射管理器Step 1: Create a shard map manager

# Create a shard map manager
New-ShardMapManager -UserName '<user_name>' -Password '<password>' -SqlServerName '<server_name>' -SqlDatabaseName '<smm_db_name>'
#<server_name> and <smm_db_name> are the server name and database name
# for the new or existing database that should be used for storing
# tenant-database mapping information.

检索分片映射管理器To retrieve the shard map manager

创建后,可以使用此 cmdlet 检索分片映射管理器。After creation, you can retrieve the shard map manager with this cmdlet. 每当需要使用 ShardMapManager 对象时,则需要执行此步骤。This step is needed every time you need to use the ShardMapManager object.

# Try to get a reference to the Shard Map Manager  
$ShardMapManager = Get-ShardMapManager -UserName '<user_name>' -Password '<password>' -SqlServerName '<server_name>' -SqlDatabaseName '<smm_db_name>'

步骤 2:创建分片映射Step 2: Create the shard map

必须选择要创建的分片映射类型。Select the type of shard map to create. 选择取决于数据库架构:The choice depends on the database architecture:

  1. 每个数据库一个租户(有关术语,请参阅词汇表。)Single tenant per database (For terms, see the glossary.)
  2. 每个数据库多个租户(两种类型):Multiple tenants per database (two types):
    1. 列表映射List mapping
    2. 范围映射Range mapping

对于单租户模型,创建“列表映射” 分片映射。For a single-tenant model, create a list mapping shard map. 单租户模型将每个租户分配给一个数据库。The single-tenant model assigns one database per tenant. 这是适用于 SaaS 开发人员的有效模型,因为它可以简化管理。This is an effective model for SaaS developers as it simplifies management.

列表映射

多租户模型将数个租户分配给单个数据库(可跨多个数据库分布租户组)。The multi-tenant model assigns several tenants to an individual database (and you can distribute groups of tenants across multiple databases). 如果希望每个租户具有较小的数据需求,请使用此模型。Use this model when you expect each tenant to have small data needs. 在此模型中,使用范围映射将一系列用户分配到数据库。In this model, assign a range of tenants to a database using range mapping.

范围映射

或可以使用列表映射来实现多租户数据库模型,以将多个租户分配给单个数据库。Or you can implement a multi-tenant database model using a list mapping to assign multiple tenants to an individual database. 例如,DB1 用于存储租户 ID 1 和 5 的相关信息,而 DB2 用于存储租户 7 和租户 10 的数据。For example, DB1 is used to store information about tenant ID 1 and 5, and DB2 stores data for tenant 7 and tenant 10.

单一数据库上的多个租户

根据你具体的选择,选择以下选项之一:Based on your choice, choose one of these options:

选项 1:为列表映射创建分片映射Option 1: Create a shard map for a list mapping

使用 ShardMapManager 对象创建分片映射。Create a shard map using the ShardMapManager object.

# $ShardMapManager is the shard map manager object
$ShardMap = New-ListShardMap -KeyType $([int]) -ListShardMapName 'ListShardMap' -ShardMapManager $ShardMapManager

选项 2:为范围映射创建分片映射Option 2: Create a shard map for a range mapping

若要使用此映射模式,租户 ID 值需是连续范围,并且可接受范围中有间距,方法为创建数据库时跳过范围。To utilize this mapping pattern, tenant ID values needs to be continuous ranges, and it is acceptable to have gap in the ranges by skipping the range when creating the databases.

# $ShardMapManager is the shard map manager object
# 'RangeShardMap' is the unique identifier for the range shard map.  
$ShardMap = New-RangeShardMap -KeyType $([int]) -RangeShardMapName 'RangeShardMap' -ShardMapManager $ShardMapManager

选项 3:单个数据库的列表映射Option 3: List mappings on an individual database

设置此模式也要求创建列表映射,如步骤 2,选项 1 中所示。Setting up this pattern also requires creation of a list map as shown in step 2, option 1.

步骤 3:准备各个分片Step 3: Prepare individual shards

将每个分片(数据库)添加到分片映射管理器。Add each shard (database) to the shard map manager. 此操作将准备用于存储映射信息的各个数据库。This prepares the individual databases for storing mapping information. 对每个分片执行此方法。Execute this method on each shard.

Add-Shard -ShardMap $ShardMap -SqlServerName '<shard_server_name>' -SqlDatabaseName '<shard_database_name>'
# The $ShardMap is the shard map created in step 2.

步骤 4:添加映射Step 4: Add mappings

添加映射的操作取决于所创建的分片映射种类。The addition of mappings depends on the kind of shard map you created. 如果创建的是列表映射,则添加列表映射。If you created a list map, you add list mappings. 如果创建的是范围映射,则添加范围映射。If you created a range map, you add range mappings.

选项 1:为列表映射映射数据Option 1: Map the data for a list mapping

通过为每个租户添加列表映射来映射数据。Map the data by adding a list mapping for each tenant.

# Create the mappings and associate it with the new shards
Add-ListMapping -KeyType $([int]) -ListPoint '<tenant_id>' -ListShardMap $ShardMap -SqlServerName '<shard_server_name>' -SqlDatabaseName '<shard_database_name>'

选项 2:为范围映射映射数据Option 2: Map the data for a range mapping

添加所有租户 ID 范围的范围映射 - 数据库关联:Add the range mappings for all the tenant ID range - database associations:

# Create the mappings and associate it with the new shards
Add-RangeMapping -KeyType $([int]) -RangeHigh '5' -RangeLow '1' -RangeShardMap $ShardMap -SqlServerName '<shard_server_name>' -SqlDatabaseName '<shard_database_name>'

步骤 4,选项 3:映射单个数据库上多个租户的数据Step 4 option 3: Map the data for multiple tenants on an individual database

对于每个租户,运行 Add-ListMapping(选项 1)。For each tenant, run the Add-ListMapping (option 1).

检查映射Checking the mappings

可以使用以下命令查询现有分片及其关联的映射的相关信息:Information about the existing shards and the mappings associated with them can be queried using following commands:

# List the shards and mappings
Get-Shards -ShardMap $ShardMap
Get-Mappings -ShardMap $ShardMap

摘要Summary

完成设置后,便可以开始使用弹性数据库客户端库。Once you have completed the setup, you can begin to use the Elastic Database client library. 还可以使用数据依赖型路由多分片查询You can also use data-dependent routing and multi-shard query.

后续步骤Next steps

Azure SQL 数据库 - 弹性数据库工具脚本获取 PowerShell 脚本。Get the PowerShell scripts from Azure SQL Database-Elastic Database tools scripts.

GitHub 上也提供了这些工具:Azure/elastic-db-toolsThe tools are also on GitHub: Azure/elastic-db-tools.

使用拆分/合并工具在多租户模型与单租户模型之间来回移动数据。Use the split-merge tool to move data to or from a multi-tenant model to a single tenant model. 请参阅拆分合并工具See Split merge tool.

问题和功能请求Questions and feature requests

如有问题,请使用 SQL 数据库论坛For questions, use the SQL Database forum.