在两个服务器实例之间复制数据 [英] Copy data between two server instances

问题描述

我想要类似的东西:

insert into server2.database1.table1 select * from server1.database1.table1

两个表完全一样.

如何在两个服务器实例之间复制数据?

推荐答案

SQL - Linked Server

如果两台服务器都是 SQL Server，您可以设置链接服务器 - 为了安全起见，我建议使用 SQL 帐户.

If both servers are SQL Server, you can set up Linked servers - I would suggest using an SQL account for security there.

然后你就可以简单地执行

Then you can simply perform

insert into server2.database1.dbo.table1 
select * from server1.database1.dbo.table1 where col1 = 'X'

如果在连接到 server1 的 SQL Management Studio 中运行查询，并且当前数据库设置为 database1，则不需要前缀

If you run the query in SQL Management studio connected to server1, and current database set to database1, you won't need the prefix

server1.database1.dbo.

此外，链接服务器将配置在 server1 上，以连接到 server2(而不是相反).

Also, the linked server would be configured on server1, to connect to server2 (rather than the other way around).

如果您有正确的 OLE DB 驱动程序，此方法也可以在不同类型的 RDBMS(即非 SQL Server 的 RDBMS)之间工作.

If you have the correct OLE DB drivers, this method can also work between different types of RDBMS (ie. non-SQL Server ones).

打开查询

注意:注意不要过分依赖链接服务器，尤其是过滤和跨服务器连接，因为它们需要在应用任何条件之前将数据完整读取到原始 RDBMS.链接服务器可能会引起许多并发症，因此请在开始之前仔细阅读，因为即使是版本差异也可能会导致头痛.

Note: Beware not to rely on linked servers too much especially for filtering, and for joins across servers, as they require data to be read in full to the originating RDBMS before any conditions can be applied. Many complications can arise from Linked Servers, so read up before you embark, as even version differences might cause headaches.

我建议您对 SQL Server 使用 OPENQUERY 命令来绕过这些限制.这是一个示例，但您应该通过进一步研究找到针对您需求的帮助:

I recommend you use the OPENQUERY command for SQL Servers to get around such limitations. Here's an example, but you should find help specific to your needs through further research:

insert into server2.database1.dbo.table1 
select * from OPENQUERY(server1, 'select * from database1.dbo.table1 where col1 = ''X''');

以上代码效率更高，在将数据泵入之前过滤源服务器上的数据(并使用可用索引)，从而节省源服务器和目标服务器的带宽/时间/资源.

The above code is more efficient, filtering the data on the source server (and using available indexes), before pumping the data through, saving bandwidth/time/resources of both the source and destination servers.

(还要注意双引号 ''，是产生单引号的转义序列.)

(Also note the double quote '', is an escape sequence to produce a single quote.)

SQL - 暂时在同一台服务器上

将启用(注意下划线):

Would enable (note the underscore):

insert into server2_database1.dbo.table1 
select * from database1.dbo.table1

仍在 SQL 查询域内.如果您可以将 server2 上的数据库临时移动到 server1，那么您将不需要链接服务器.在服务器 1 上共同定位时，似乎需要重命名数据库.实现这种共存可以使用多种方法，我建议在继续之前缩小数据库文件:

Still within the SQL query domain. If you can temporarily move the database on server2 to server1, then you won't need the linked server. A rename of the database would appear to be required while co-locating on server1. Achieving such co-location could use various methods, I suggest shrinking database files before proceeding with either:

1. 备份/恢复 - 在 server2 上备份，在 server1 上恢复(使用不同的名称)- 如上所述执行插入，但没有 server1 或 server2 前缀.然后反向 - 在 server1 上备份，在 server2 上恢复/
2. 分离/附加 - 重命名数据库，在 server2 上分离，(压缩)，将文件复制到服务器 1，(解压缩)，在 server1 上附加，执行插入.然后反转...

1. Backup/Restore - Backup on server2, Restore on server1 (with different name) - perform insert as described above, but without the server1 or server2 prefixes. Then reverse - backup on server1, restore on server2/
2. Detach/Attach - Rename database, Detach on server2, (compress), copy files to server 1, (decompress), attach on server1, perform insert. Then reverse...

无论哪种情况，SQL Server 版本都可能成为障碍.如果 server1 的 SQL 版本较低，则备份和分离/附加方法都可能失败.这可以通过将 server1 数据库移动到 server2 来解决，这可能更合适，也可能不合适.

In either case, SQL Server version could be a barrier. If server1 is of a lower SQL version, then both backup and detach/attach methods will likely fail. This can be worked around by moving the server1 database to server2, which may or may not be more suitible.

其他方法

可能是合适的非 SQL/TSQL 方法，但对前面提到的方法没有有利的环境因素.如果您有正确的访问权限(OLE DB 驱动程序等)，此方法也可以在不同类型的 RDBMS(即非 SQL Server 的)和数据源(例如 XML、平面文件、Excel 电子表格)之间工作...)

May be suitable, non-SQL/TSQL method failing favorable environmental factors for previously mentioned methods. And if you have the correct access (OLE DB Drivers, etc..), this method can also work between different types of RDBMS (ie. non-SQL Server ones), and data-sources (such as XML, flatfiles, Excel Spreadsheets...)

• SSIS 明确使用 Business Development Management Studio - 直接数据泵或使用分隔文件中间.
• SSIS 隐式通过 SQL Management Studio，右键单击 server1 上的 database1 > 任务 > 导出，然后完成向导.可以直接在 server2 上工作，也可以使用平面文件中介.
• .Net Programming 与 SqlBulkInsert(我相信 SSIS 数据泵使用这样的对象)，如果您感兴趣，我可以详细介绍此内容.立>

• SSIS Explicitly with Business Development Management Studio - direct datapump or using delimited file intermeditary.
• SSIS Implicitly through SQL Management Studio, by right clicking the database1 on server1 > Tasks > Export, then completing the wizard. May work direct to server2, or using a flat-file intermeditary.
• .Net Programming with SqlBulkInsert (I believe the SSIS datapump uses such an object), I can go into more detail about this, if it interests you.

例如.SQLBulkInsert (psedo-C# 代码)

Eg. of SQLBulkInsert (psedo-C# code)

SqlConnection c = new SqlConnection("connectionStringForServer1Database1Here");
SqlConnection c2 = new SqlConnection("connectionStringForServer2Database1Here");
c.Open();
SqlCommand cm = new SqlCommand(c);
cm.CommandText = "select * from table1;";
using (SqlDataReader reader = cm.ExecuteReader())
{
    using (SqlBulkInsert bc = new SqlBulkInsert(c))
    {
         c2.Open();
         bc.DestinationTable = "table1";
         bc.WriteToServer(reader);
    }
}

很酷吧?如果速度/效率是一个问题 - 基于 SqlBulkInsert 的方法(例如 SSIS)是最好的.

Pretty cool huh? If speed/efficiency is a concern - SqlBulkInsert based approaches (Such as SSIS) are the best.

更新 - 修改目标表

如果您需要更新目标表，我建议您:

If you need to update the destination table, I recommend that you:

1. 写入目标数据库上的临时表(临时表或在处理前后截断的正确表)，后者更可取.如果您没有 CREATE TABLE 权限，前者可能是您唯一的选择.您可以使用上述任一选项执行传输.
2. 根据您的要求从临时表到目标表运行 MERGE INTO 命令.这可以非常有效地根据需要插入、更新和删除.

可以通过滑动窗口(自上次检查以来的更改)来增强整个过程，仅将源中最近更改的行应用于目标，这会使过程复杂化，因此您至少应该先完成更简单的过程.完成一个滑动窗口版本后，您可以定期运行完整更新版本，以确保滑动窗口没有错误.

Such a whole process could be enhanced with a sliding window (changes since last checked), only taking recently changed rows in the source an applying to the destination, this complicates the process, so you should at least accomplish the simpler one first. After completing a sliding window version, you could run the full-update one periodically to ensure there are no errors in the sliding window.

这篇关于在两个服务器实例之间复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！