SQL 2017 std维护计划和可用性组问题 [英] SQL 2017 std maintenance plan and Availability group question

问题描述

我有3 x 2017 SQL std许可证，并试图在我们的小型商业环境中拥有一些冗余。据我所知，我受限于std许可。但是有几个问题，我想做每小时完整的数据库AG数据库的数据库备份，如果失败了，这个工作怎么会
？辅助DB不可访问，对吗？第二个问题，我正在寻找在第三台服务器上进行报告的最佳方式，但我只能阅读主数据库。想象一下，我可以每小时进行一次完整备份，并恢复到第三个SQL
实例进行报告。如果失败到次要，这将再次如何工作？在此先感谢任何和所有帮助/建议。

I have 3 x 2017 SQL std licenses and trying to have some redundancy in our small business environment. I understand i'm limited with the std licensing. But have a few questions, I want to do hourly full DB back ups of the clustered AG database, how would this work if failed over? The secondary DB are not accessible, correct? Second question, I'm looking for the best way to do reporting on a 3rd server but being I can only read the primary DB. Figured I could do a full backup every hour and restore to 3rd SQL instance for reporting. Once again how would this work if failed over to secondary? Thanks in advance for any and all help/suggestions.

推荐答案

您需要对您的主要维护进行维护。您将需要检测哪个服务器是主服务器并在那里执行。

You will need to do your maintenance on your primary. You will need to detect which server is the primary and do it there.

您需要发出以下查询 

You will need to issue the below query 

声明@dbname sysname ='ADatabaseProtectedByAlwaysOn'

declare @dbname sysname='ADatabaseProtectedByAlwaysOn'

if sys.fn_hadr_is_primary_replica
（@dbname）= 1

如果这个解析为1，那么你就是主要的。然后，您可以进行备份。  

and if this resolves to 1 you are on your primary. You can then do your back up.  

您可以将此作为维护计划的第一个作业步骤，如果为0，则可以引发错误以使作业步骤失败并辞去工作。如果它确实解决为1，您可以继续并运行您的维护计划。 

you can make this the first job step of your maintenance plan and if it is 0 you can raise an error to fail the job step and quit the job. if it does resolve to 1 you can continue and run your maintenance plan. 

您将需要升级到企业版以获得可读的辅助或我们的其他技术，如复制或者日志传送。如果您使用日志传送，您可以使用with standby选项来拥有可读目标数据库，然后每小时
恢复日志。

You will need to upgrade to the Enterprise Edition to have a readable secondary or us another technology like replication or perhaps log shipping. If you use log shipping you can use the with standby option to have a readable destination database and then restore logs each hour.

您需要编写解决方案以实现目标。

You will need to code a solution to achieve your goals.

这篇关于SQL 2017 std维护计划和可用性组问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！