The growing importance and complexity of data migration, in an era of exploding data volumes and ever-changing business requirements, means that old approaches will no longer get the job done. We are in a world where everything needs to run instantly. Every Database Administrator or Developer would have definitely heard about database migrations with zero downtime and with zero data loss.
在数据量激增和业务需求不断变化的时代，数据迁移的重要性和复杂性日益增长，这意味着旧方法将无法完成工作。 我们处在一个一切都必须立即运行的世界中。 每个数据库管理员或开发人员肯定都会听说过零迁移，零数据丢失的数据库迁移。
目的 (Objective)
Database migrations could lead to downtime. How can we deal with database migrations when we don’t want our end-users to experience downtime but also want to keep data up-to-date, with no missing data?
数据库迁移可能导致停机。 当我们不希望最终用户经历停机时间，又希望保持数据最新且没有丢失数据时，我们该如何处理数据库迁移？
Let us consider a scenario where you’re performing a migration where backing up and restoring the database might take 30 minutes. That means either 30 minutes of downtime or potentially a 30-minute window of lost data. How can we avoid end user downtime and also avoid data loss?
让我们考虑一下您正在执行迁移的情况，其中备份和还原数据库可能需要30分钟。 这意味着30分钟的停机时间或30分钟的丢失数据窗口。 我们如何避免最终用户停机并避免数据丢失？
The traditional database migration approaches have several limitations and its near-impossible task to maintain an exact replica of the production without a downtime because transactions that occur during the migration process will be lost or if the database is taken offline during the migration process, you will have downtime
传统的数据库迁移方法有几个局限性，并且几乎不可能完成维护生产的精确副本而不造成停机的任务，因为在迁移过程中发生的事务将丢失，或者如果在迁移过程中数据库脱机，您将停工
In this article, we’ll show how we can backup a production database and then restore it to a new location, while not taking the production system offline nor losing data during our migration window.
在本文中，我们将展示如何备份生产数据库，然后将其还原到新位置，而又不会使生产系统脱机，也不会在迁移窗口期间丢失数据。
We’ll allow the client application and database to keep running through the entire process. Once we restore the database to production, we’ll capture all of the transactions that were made on the production system, during the migration, and replay them on production to ensure there is zero downtime.
我们将允许客户端应用程序和数据库在整个过程中继续运行。 将数据库还原到生产环境后，我们将捕获在迁移过程中在生产系统上进行的所有事务，并在生产过程中重播它们以确保零停机时间。
过程 (The process)
The backup and restore database technique is the core of SQL Server High-Availability technique and also a base for database migration. This process is adopted to reduce the downtime of database migration. As it’s an online process, we can initiate a backup while the users online. But even so, there is a period of time in between the initial backup and the final restore, where transactions will be lost. That is where a transaction log reader comes in handy.
备份和还原数据库技术是SQL Server高可用性技术的核心，也是数据库迁移的基础。 采用此过程可减少数据库迁移的停机时间。 由于这是一个在线过程，因此我们可以在用户在线时启动备份。 但是，即使这样，在初始备份和最终还原之间仍会存在一段时间，在此期间事务将丢失。 那就是事务日志读取器派上用场的地方。
Let us take a look at the 3rd party too, ApexSQL Log. ApexSQL Log helps you to read the SQL Server transaction log quickly and efficiently. We’ll use this to capture the missing transactions and replay them on the new server
让我们来看看第3方太， ApexSQL日志 。 ApexSQL日志可帮助您快速有效地读取SQL Server事务日志。 我们将使用它来捕获丢失的事务并在新服务器上重播它们
- Backup the source database 备份源数据库
Restore the new database with recovery on—the target database is online and it is ready for use
恢复到恢复状态即可还原新数据库-目标数据库已联机并且可以使用
Initiate the transaction log reader at the source database to read all of the transactions that were committed during our migration window. In doing so, you will generate the SQL script of the delta changes since the last FULL backup. The available options and configurations are easy to customize. The process of scripting the committed transactions (redo.sql) is very simple and the generated SQL can be applied to target at a later point in time to synchronize any differences that happened during the backup and restore window.
在源数据库中启动事务日志读取器，以读取在迁移窗口期间提交的所有事务。 这样，您将生成自上次FULL备份以来增量变化SQL脚本。 可用的选项和配置易于定制。 对提交的事务（redo.sql）编写脚本的过程非常简单，并且可以在以后的某个时间点将生成SQL应用于目标，以同步备份和还原窗口期间发生的任何差异。
- Applying transactions is an online operation. In real-time, you just need to change the network configuration (DNS entry to point to a server) and all done. Also, the residual transactions are applied to the new databases online. It means the data loss is “Zero” and migration can be implemented under the concept of “Zero downtime”. 申请交易是一项在线操作。 实时地，您只需要更改网络配置（指向服务器的DNS条目）即可。 此外，剩余的交易将在线应用于新数据库。 这意味着数据丢失为“零”，并且可以在“零停机时间”的概念下进行迁移。
演示版 (Demo)
Let us walk through the entire process using World-wide-importer database.
让我们使用World-wide-importer数据库浏览整个过程。
We will have assumed you’ve backed up your source database and restored it to a new location. When you have restored it, you should take a backup of the transaction log for the original database.
我们假定您已经备份了源数据库并将其还原到新位置。 还原后，应备份原始数据库的事务日志。
Note the time that you backed up the production database and also the time that you switched over to the new database. These dates and times will be used to create the window by which we capture transactions from the original production system
请注意备份生产数据库的时间，以及切换到新数据库的时间。 这些日期和时间将用于创建一个窗口，通过该窗口我们可以从原始生产系统捕获交易
- Open ApexSQL Log 打开ApexSQL日志
Connect to SQL Server instance and choose the WWI database
连接到SQL Server实例并选择WWI数据库
Select transaction log file sources
选择事务日志文件源
Select Custom and enter in the dates and times From when the original production system was backed up To when the new production system was put online.
选择自定义 ，并在日期和时间时， 从原生产系统备份到在新的生产系统放到网上输入。
Next, choose Undo/Redo to generate a redo.sql file
接下来，选择撤消/重做以生成redo.sql文件
To generate the SQL, Click Finish.
要生成SQL，请单击“完成”。
You can see a lot of statistical data about the DML operation that was performed since the last full backup. This represents data that would have been lost or activity that would have been prevented, during a downtime window
您可以看到许多有关自上次完整备份以来执行的DML操作的统计数据。 这表示在停机时间窗口期间可能丢失的数据或可以防止的活动
Now, let’s run this script(redo.sql) against our newly restored production database. To run, Click redo.sql. This will open up a query execution window. Click Connect and type in the target instance details. Then, click Execute to run the SQL script.
现在，让我们针对新恢复的生产数据库运行此脚本（redo.sql）。 要运行，请单击redo.sql。 这将打开一个查询执行窗口。 单击连接，然后输入目标实例的详细信息。 然后，单击执行以运行SQL脚本。
We’ve successfully moved our database without losing a single row of data!
我们已经成功移动了数据库，而没有丢失任何一行数据！
结语 (Wrap Up)
In this article, we reviewed how to address the gap in data inherent in many database migrations, due to the fact that new data/transactions that occur since the last backup but before the new system is turned online are lost. The “answer” is often to shut down the production system for a maintenance window and lock out users, but this results in system downtime. By keeping the system up, recording the transactions during the latency period and then replaying them on the new production server, you can both keep the system up 100% and avoid any data loss
在本文中，我们回顾了如何解决许多数据库迁移中固有的数据差异，因为自上次备份以来，在新系统上线之前发生的新数据/事务丢失了。 解决方案通常是关闭生产系统以维护窗口并锁定用户，但这会导致系统停机。 通过保持系统正常运行，在等待时间段内记录事务，然后在新的生产服务器上重播它们，可以使系统保持100％运行并避免任何数据丢失
目录 (Table of contents)
| SQL Server复制：组件和拓扑概述 |
| SQL复制：基本设置和配置 |
| 如何从SQL Server中的现有出版物中添加/删除文章 |
| 如何对两个大型SQL Server数据库中的数据进行快速估计比较，以查看它们是否相等 |
| SQL Server事务复制：如何使用SQL Server数据库备份重新初始化订阅 |
| 如何使用中央订阅服务器和多个发布者数据库设置自定义SQL Server事务复制模型 |
| 如何使用中央发布者和多个订阅者数据库设置自定义SQL Server事务复制 |
| 如何设置DDL和DML SQL Server数据库事务复制解决方案 |
| 如何在Linux上为数据库报告设置跨平台事务SQL Server复制 |
| SQL Server数据库迁移，数据丢失为零，停机时间为零 |
| 使用事务数据复制来重放和测试登台服务器上的生产负载 |
| 如何为报表服务器设置SQL Server数据库复制 |
| SQL Server事务复制：如何使用“仅复制支持” –TBA重新初始化订阅 |
| 使用PowerShell –TBASQL Server复制监视和设置警报 |
翻译自: https://www.sqlshack.com/sql-server-database-migrations-with-zero-data-loss-and-zero-downtime/
380
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
