SQL 复制替代方案？
嘿，我在 sql server 2005 中有一个大数据库，由于它太大，我需要将其复制到其他服务器。我设置了 sql 复制发布者和订阅者，但它的行为不稳定。 它不会为每个插入进行复制，也不会抛出任何异常。 另外，我担心这里的性能，所以我想知道是否还有其他我可以使用的替代方案...... 我不需要复制数据库中的每一行或所有表。
任何评论都将在这里受到赞赏！
谢谢！
更新： 我在这里试图实现的是数据库的负载平衡。我预计这里会有很多流量，所以我正在考虑使用复制的数据库向用户显示信息。意思是，插入、更新和删除将在主数据库中执行，而选择将在新数据库中执行。由于复制的数据库仅用于选择并向用户显示信息，因此我只需要那些对应用程序有效的行。另外，我正在考虑这里不要标准化表格，以避免使用联接并尽快检索信息。 我设置的复制是事务性的，实际上，我遇到的问题是，按照相同的过程插入一行，它一次复制结果，然后再次尝试，但没有复制。从那时起它就一直这样运作。
Hey, I have a big database in sql server 2005, and since it´s so big I need to replicate it to other sever. I set up sql replication publisher and subscriptor but it doesn´t behave in a stable way.
It´s not replicating for every insert and it´s not throwing any exceptions either.
Also, I´m concerned about performance here, so I was wondering if maybe there is some other alternative I could use...
I don´t need to replicate every row in the database, or all the tables either.
Any comment will be appreciated here!
Thanks!
UPDATE:
What I´m trying to achieve here is a load balance for the database. I expect a lot of traffic here, so I´m thinking using the replicated databse to show information to users. Meaning, inserts, updates and deletes will be performed in main databse, and selects will be performed in the new databse. Since the replicated databse will be just for selects, and show information to users, I just need those rows that are active for the app. Also, I´m thinking of having not normalized tables here, to avoid using joins and retrieve the information as fast as I can.
the replication I set up is transactional, and really, the problems I have is that following the same process to insert a row, it once replicate the result, and then tryed again and it didn´t. And it has been working like that since.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
听起来您想使用事务复制：
但您需要认识到，应用更新与在订阅数据库中显示更新之间可能存在微小的延迟。大多数应用程序都可以应对这种情况，但是您可能必须在应用程序中做更多的工作，以防止基于过时的数据执行工作（如果您考虑一下，您的应用程序可能应该已经在处理此类问题，因为用户通常在提交更改之前会查看屏幕（很多）分钟
从您的编辑中可以看出，您已经尝试过事务复制，并且遇到了问题，我通常建议多次练习设置复制，然后只是获得一般性的结果。在尝试使用真实数据库之前先感受一下它的工作原理。从几个表和几行数据开始设置复制（两个数据库可以位于同一服务器上）。工作（如您所指出的），您需要寻找事件和日志消息，以指示出了什么问题/停止工作
从您的描述来看，这确实听起来像事务复制就是您所需要的，所以我想说坚持下去，并且如果您发现发生特定错误，请搜索 serverfault，或在那里提出有关此类错误的新问题。
It sounds like you want to use Transactional Replication:
But you need to recognise that there may be small delays between updates being applied, and their being visible in the subscription database. Most applications can cope with this, but you may have to do more work in the application to prevent performing work based on stale data (if you think about it though, your application should probably already be dealing with these kind of problems, since users typically will look at a screen for (many) minutes before submitting changes.
Seen from your edit that you've already tried transactional replication, and encountered issues. I'd normally recommend practising setting up replication a number of times, and just getting a general feel for how it works before attempting it with the real database. Start with just a couple of tables, and few rows of data. Set up replication (both databases can be on the same server). Experiment with the various options. If replication stops working (as you indicated) you need to go hunting for events and log messages that indicate what has gone wrong/stopped working.
From your description, it really does sound like transactional replication is what you need, so I'd say persevere, and if you find specific errors occurring, search serverfault, or ask new questions there about such errors.
您可以集成服务来创建一个包，该包可以安排运行和更新您的第二个服务器数据库。
You could you integration services to create a package which can be scheduled to run and update your second server database.
我认为触发器是个好主意，但是我有同样的情况，但就我而言，我需要在所有不同的位置可以在其位置发布数据，并且所有位置都需要与所有其他位置数据同步！
在这种情况下，我已经实现了自己的逻辑，即使用服务器 ID 和 SQL 日志创建复制表。
在主服务器上运行的软件将所有日志复制到每个位置并执行它们。
这解决了我的问题，但它仍然需要一些更多的优化，例如在旅行期间压缩数据以提高复制速度，因为我每个数据库每周有近 100000 个条目，并且我有 4 个位置。
I think triggers would be a good idea, however i have the same situation but in my case i need at all the different locations can post data in their locations and all locations needs to be synced with all other locations data!
in that case i have implemented my own logic of creating a replication table with server ids and sql logs.
on software runs on main server which copy all logs to the each location and execute them.
this solves my problem however it still needs some more optimization like to compress data during travel to increase the replication speed, because i have almost 100000 entries per week in each database, and i have 4 locations.