如何将 SQLite 数据库迁移到 Azure SQL 数据库无服务器How to migrate your SQLite database to Azure SQL Database serverless

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

许多人在 SQLite 中首次体验了数据库和 SQL 编程。For many people, SQLite provides their first experience of databases and SQL programming. SQLite 包含在许多操作系统和热门应用程序中,是世界上最广泛部署和使用的数据库引擎之一。It's inclusion in many operating systems and popular applications makes SQLite one the most widely deployed and used database engines in the world. 此外,它可能是许多人首次使用的数据库引擎,因此往往成为项目或应用程序的核心组成部分。And because it is likely the first database engine many people use, it can often end up as a central part of projects or applications. 当项目或应用程序超过最初的 SQLite 实施规模时,开发人员可能需要将其数据迁移到可靠的集中式数据存储。In such cases where the project or application outgrows the initial SQLite implementation, developers may need to migrate their data to a reliable, centralized data store.

Azure SQL 数据库无服务器计算层是适用于单一数据库的计算层,可根据工作负载需求自动缩放,并根据每秒使用的计算资源量计费。Azure SQL Database Serverless is a compute tier for single databases that automatically scales compute based on workload demand, and bills for the amount of compute used per second. 此外,当仅对存储计费时,无服务器计算层将在非活动期间自动暂停数据库;当活动返回时,它将自动恢复数据库。The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns.

完成以下步骤后,数据库将会迁移到 Azure SQL 数据库无服务器,使你能够将数据库提供给云中的其他用户或应用程序使用,而且只需为使用的资源付费,只需对应用程序代码进行极少量的更改。Once you have followed the below steps, your database will be migrated into Azure SQL Database Serverless, enabling you to make your database available to other users or applications in the cloud and only pay for what you use, with minimal application code changes.

先决条件Prerequisites

  • 一个 Azure 订阅An Azure Subscription
  • 要迁移的 SQLite2 或 SQLite3 数据库SQLite2 or SQLite3 database that you wish to migrate
  • 一个 Windows 环境A Windows environment
    • 如果没有本地 Windows 环境,可以使用 Azure 中的 Windows VM 进行迁移。If you do not have a local Windows environment, you can use a Windows VM in Azure for the migration. 使用 Azure 文件存储和存储资源管理器在 VM 上移动 SQLite 数据库文件并使其可用。Move and make your SQLite database file available on the VM using Azure Files and Storage Explorer.

步骤Steps

  1. 在“无服务器”计算层中预配新的 Azure SQL 数据库。Provision a new Azure SQL Database in the Serverless compute tier.

    Azure 门户的屏幕截图,其中显示了 Azure SQL 数据库无服务器的预配示例

  2. 确保 SQLite 数据库文件在 Windows 环境中可用。Ensure you have your SQLite database file available in your Windows environment. 如果没有 SQLite ODBC 驱动程序,请安装该驱动程序 (有许多可用的开源驱动程序,例如 http://www.ch-werner.de/sqliteodbc/) 。Install a SQLite ODBC Driver if you do not already have one (there are many available in Open Source, for example, http://www.ch-werner.de/sqliteodbc/).

  3. 为数据库创建系统 DSN。Create a System DSN for the database. 确保使用与系统体系结构匹配的数据源管理器应用程序(32 位或 64 位)。Ensure you use the Data Source Administrator application that matches your system architecture (32-bit vs 64-bit). 可以在系统设置中查找正在运行的版本。You can find which version you are running in your system settings.

    • 在环境中打开 ODBC 数据源管理器。Open ODBC Data Source Administrator in your environment.
    • 依次单击“系统 DSN”选项卡、“添加”Click the system DSN tab and click "Add"
    • 选择已安装的 SQLite ODBC 连接器,并为连接指定一个有意义的名称,例如 sqlitemigrationsourceSelect the SQLite ODBC connector you installed and give the connection a meaningful name, for example, sqlitemigrationsource
    • 在 .db 文件中设置数据库名称Set the database name to the .db file
    • 保存并退出Save and exit
  4. 下载并安装自承载集成运行时。Download and install the self-hosted integration runtime. 为此,最简单的方法是根据文档中的详述使用“快速安装”选项。The easiest way to do this is the Express install option, as detailed in the documentation. 如果选择手动安装,则需要为应用程序提供身份验证密钥,可在数据工厂实例中通过以下操作找到该密钥:If you opt for a manual install, you will need to provide the application with an authentication key, which can be located in your Data Factory instance by:

    • 启动 ADF(在 Azure 门户上的服务中选择“创作和监视”)Starting up ADF (Author and Monitor from the service in the Azure portal)
    • 单击左侧的“创作”选项卡(蓝色铅笔图标)Click the "Author" tab (Blue pencil) on the left
    • 单击“连接”(左下角)并选择“集成运行时”Click Connections (bottom left), then Integration runtimes
    • 添加新的自承载集成运行时,为其命名,然后选择“选项 2”。Add new Self-Hosted Integration Runtime, give it a name, select Option 2.
  5. 为数据工厂中的源 SQLite 数据库创建新的链接服务。Create a new linked service for the source SQLite database in your Data Factory.

    显示 Azure 数据工厂中的空“链接服务”边栏选项卡的屏幕截图

  6. 在“连接”中的“链接服务”下,单击“新建” 。In Connections, under Linked Service, click New.

  7. 搜索并选择“ODBC”连接器Search for and select the "ODBC" connector

    显示 Azure 数据工厂中“链接服务”边栏选项卡上的“ODBC”连接器徽标的屏幕截图

  8. 为该链接服务指定一个有意义的名称,例如“sqlite_odbc”。Give the linked service a meaningful name, for example, "sqlite_odbc". 从“通过集成运行时进行连接”下拉列表中选择你的集成运行时。Select your integration runtime from the "Connect via integration runtime" dropdown. 在连接字符串中输入以下内容(请将 Initial Catalog 变量替换为 .db 文件的文件路径,并将 DSN 替换为系统 DSN 连接的名称):Enter the below into the connection string, replacing the Initial Catalog variable with the filepath for the .db file, and the DSN with the name of the system DSN connection:

    Connection string: Provider=MSDASQL.1;Persist Security Info=False;Mode=ReadWrite;Initial Catalog=C:\sqlitemigrationsource.db;DSN=sqlitemigrationsource
    
  9. 将身份验证类型设置为“匿名”Set the authentication type to Anonymous

  10. 测试连接Test the connection

    显示 Azure 数据工厂中的成功连接的屏幕截图

  11. 为无服务器 SQL 目标创建另一个链接服务。Create another linked service for your Serverless SQL target. 使用链接服务向导选择数据库,并提供 SQL 身份验证凭据。Select the database using the linked service wizard, and provide the SQL authentication credentials.

    显示 Azure 数据工厂中选择的 Azure SQL 数据库的屏幕截图

  12. 从 SQLite 数据库提取 CREATE TABLE 语句。Extract the CREATE TABLE statements from your SQLite database. 为此,可对数据库文件执行以下 Python 脚本。You can do this by executing the below Python script on your database file.

    #!/usr/bin/python
    import sqlite3
    conn = sqlite3.connect("sqlitemigrationsource.db")
    c = conn.cursor()
    
    print("Starting extract job..")
    with open('CreateTables.sql', 'w') as f:
        for tabledetails in c.execute("SELECT * FROM sqlite_master WHERE type='table'"):
            print("Extracting CREATE statement for " + (str(tabledetails[1])))
            print(tabledetails)
            f.write(str(tabledetails[4].replace('\n','') + ';\n'))
    c.close()
    
  13. 通过从 CreateTables.sql 文件中复制 CREATE TABLE 语句并在 Azure 门户上的查询编辑器中运行 SQL 语句,在无服务器 SQL 目标环境中创建登陆表。Create the landing tables in your Serverless SQL target environment by copying the CREATE table statements from the CreateTables.sql file and running the SQL statements in the Query Editor in the Azure portal.

  14. 返回到数据工厂的主屏幕,然后单击“复制数据”来运行作业创建向导中的每个步骤。Return to the home screen of your Data Factory and click "Copy Data" to run through the job creation wizard.

    显示 Azure 数据工厂中的“复制数据向导”徽标的屏幕截图

  15. 使用复选框选择源 SQLite 数据库中的所有表,并将其映射到 Azure SQL 中的目标表。Select all tables from the source SQLite database using the check boxes, and map them to the target tables in Azure SQL. 作业完成运行后,即已成功将数据从 SQLite 迁移到 Azure SQL!Once the job has run, you have successfully migrated your data from SQLite to Azure SQL!

后续步骤Next steps