In the business I work for we are discussion methods to reduce the read load on our primary database.
在我工作的业务中，我们讨论的方法是减少主数据库的读取负载。
One option that has been suggested is to have live one-way replication from our primary database to a slave database. Applications would then read from the slave database and write directly to the primary database. So...
建议的一个选项是从主数据库到从数据库进行实时单向复制。然后，应用程序将从从属数据库中读取并直接写入主数据库。所以...
- Application Reads From Slave
- 应用程序从Slave读取
- Application Writes to Primary
- 应用程序写入主要
- Primary Updates Slave Automatically
- 主要更新Slave自动
What are the major pros and cons for this method?
这种方法的主要优点和缺点是什么？
2 个解决方案
#1
2
A few cons:
一些缺点：
- 2 points of failure
- 2点失败
- Application logic will have to take into account the delay between writing something and then reading it, since it won't be available immediately from the secondary database
- 应用程序逻辑必须考虑写入内容然后读取内容之间的延迟，因为它不会立即从辅助数据库中获得
A strategy I have used is to send key reporting data to a secondary database nightly, de-normalizing it on the way, so that beefy queries can run on that database instead of locking up tables and stealing resources from the OLTP server. I'm not using any formal data warehousing or replication tools, rather I identify problem queries that are Ok without up-to-the-minute data and create data structures on the secondary server specifically for those queries.
我使用的策略是每晚将关键报告数据发送到辅助数据库，在途中对其进行反规范化，以便可以在该数据库上运行强大的查询，而不是锁定表并从OLTP服务器窃取资源。我没有使用任何正式的数据仓库或复制工具，而是在没有最新数据的情况下确定问题查询，并在辅助服务器上专门为这些查询创建数据结构。
There are definitely pros to the "replicate everything" approach:
“复制一切”方法肯定有利可图：
- You can run any ad-hoc query on the secondary, since it has all of your data
- 您可以在辅助节点上运行任何即席查询，因为它具有您的所有数据
- If your primary server dies, you can re-purpose the secondary quickly to take over
- 如果您的主服务器死机，您可以快速重新使用辅助服务器来接管
#2
1
We are using one-way replications, but not from the same application. Our applications are reading-writing to the master database, the data gets synchronized to the replca database, and the reporting tools are using this replica.
我们使用的是单向复制，但不是来自同一个应用程序。我们的应用程序正在读写主数据库，数据与replca数据库同步，报告工具正在使用此副本。
We don't want our application to read from a different database, so in this scenario I would suggest using file groups and partitioning on the master database. Using file groups (especially on different drives) and partitioning of files and indexes can help on performance a lot.
我们不希望我们的应用程序从不同的数据库中读取，因此在这种情况下，我建议在master数据库上使用文件组和分区。使用文件组（特别是在不同的驱动器上）以及文件和索引的分区可以大大提高性能。
#1
2
A few cons:
一些缺点：
- 2 points of failure
- 2点失败
- Application logic will have to take into account the delay between writing something and then reading it, since it won't be available immediately from the secondary database
- 应用程序逻辑必须考虑写入内容然后读取内容之间的延迟，因为它不会立即从辅助数据库中获得
A strategy I have used is to send key reporting data to a secondary database nightly, de-normalizing it on the way, so that beefy queries can run on that database instead of locking up tables and stealing resources from the OLTP server. I'm not using any formal data warehousing or replication tools, rather I identify problem queries that are Ok without up-to-the-minute data and create data structures on the secondary server specifically for those queries.
我使用的策略是每晚将关键报告数据发送到辅助数据库，在途中对其进行反规范化，以便可以在该数据库上运行强大的查询，而不是锁定表并从OLTP服务器窃取资源。我没有使用任何正式的数据仓库或复制工具，而是在没有最新数据的情况下确定问题查询，并在辅助服务器上专门为这些查询创建数据结构。
There are definitely pros to the "replicate everything" approach:
“复制一切”方法肯定有利可图：
- You can run any ad-hoc query on the secondary, since it has all of your data
- 您可以在辅助节点上运行任何即席查询，因为它具有您的所有数据
- If your primary server dies, you can re-purpose the secondary quickly to take over
- 如果您的主服务器死机，您可以快速重新使用辅助服务器来接管
#2
1
We are using one-way replications, but not from the same application. Our applications are reading-writing to the master database, the data gets synchronized to the replca database, and the reporting tools are using this replica.
我们使用的是单向复制，但不是来自同一个应用程序。我们的应用程序正在读写主数据库，数据与replca数据库同步，报告工具正在使用此副本。
We don't want our application to read from a different database, so in this scenario I would suggest using file groups and partitioning on the master database. Using file groups (especially on different drives) and partitioning of files and indexes can help on performance a lot.
我们不希望我们的应用程序从不同的数据库中读取，因此在这种情况下，我建议在master数据库上使用文件组和分区。使用文件组（特别是在不同的驱动器上）以及文件和索引的分区可以大大提高性能。