在日志传送的辅助服务器上创建用户 [英] Creating Users on secondary server in log shipping

问题描述

我有一个生产服务器说 ServerA 我已经设置日志传送到 ServerB，它处于只读模式.此日志传送的目的是降低生产服务器上一些昂贵查询(痛苦报告)的负载.

I have a production Server say ServerA I have setup log shipping to ServerB which is left in read-only mode. The purpose of this log shipping is to lower the load on production server for some expensive queries (painful reports).

现在，如果我必须使用我们的域帐户创建一些登录名.我不能这样做，因为辅助数据库处于待机模式.

Now if I have to create some logins using our domain accounts. I cannot do this because the secondary database is in standby mode.

我认为如果我在主服务器上创建这些登录名，它将被复制到辅助服务器，然后日志会在那里恢复，但事实并非如此.

I thought if I create these logins on Primary server it will be copied over to secondary server then the logs are restored there but this isnt the case.

我在网上做了很多研究，找到了解决这个问题的方法.我为此找到了以下资源.我尝试了本文中建议的所有方法，但似乎都不起作用.

I have done a lot of research online finding a way around to this. I found the following resources for this. I tried every method suggested in this articles but none of them seems to work.

1) SQL Server 2008 R2 中的日志传送，用于在复制数据库上设置 BI

2) 如何在 SQL Server 实例之间传输登录名和密码

3) 使用数据库镜像和日志传送的孤立用户

有人遇到过同样的问题吗?你做了什么?有没有办法解决这个问题?任何建议请指点.

Has someone experienced the same issue? what did you do? Is there any way around for this issue? Any suggestions any pointer please.

推荐答案

阿里，

我当然很狡猾……

查看这些文章.

http://technet.microsoft.com/en-us/杂志/2006.05.sqlqa.aspxhttp:///blogs.msdn.com/b/reedme/archive/2009/04/24/log-shipping-database-snapshots-bummer-dude.aspx

数据库镜像是更好的解决方案，因为您可以创建快照并报告它.

Database mirroring is a better solution since you can create a snapshot and report off that.

但是，镜像和日志传送都使数据库处于只读状态.因此，您无法更改孤立用户.

However, both mirroring and log shipping have the database in read only state. Therefore, you can not change the orphaned users.

最好的方法是确保您在两台服务器上的登录名匹配.因此，不会出现孤儿.

The best way is to make sure your login's on both servers match. Therefore, orphans will not occur.

在你的情况下，你可能必须删除日志传送，在 DR 服务器上创建登录名，删除数据库，使用备份重新设置 DR 服务器并重新启动传送.

I your case, you might have to remove log shipping, create the login's on the DR server, drop the database, reseed the DR server with a backup and restart shipping.

在这方面，我不是凭经验说的，因为我一直使用 SAN 集群.请在较低的环境中对此进行测试以解决任何问题.

In this area, I am not speaking from experience since I always used clustering with a SAN. Please test this out in a lower environment to work on any gotchas.

我即将进行的项目将使用 Always On(1 个主节点，1 个辅助节点)= 同步镜像或异步日志传送.但是 Always On 允许只读辅助节点，这很好.

My upcoming project will be using Always On (with 1 primary, 1 secondary) = mirroring if synchronous or log shipping if asynchronous. But Always On allows for read only secondaries, which is nice.

请写回你是怎么做出来的.我很好奇.

Please write back on how you make out. I am curious.

照顾好我的朋友.

J

这篇关于在日志传送的辅助服务器上创建用户的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！