0

I have a SQL 2008 server with a database replicated via log shipping to a reporting server. The reporting server database gets updated once a day. The reporting server database is left in Standby/Read-Only mode in between applying the transaction log. The questions I have are: 1- Can I change the status from Standby/Read-Only to Read Only = False, add new users to the database and change back to Standby/Read-Only without breaking log shipping? 2 How can I add a new user to the reporting server database if it is in Standby/Read-Only? Thank you

Improve this question

2 Answers 2

Reset to default
1

  1. No.

  2. Add a user with the same SID to the original database (the source database). (Edited to add link with detailed instructions.)

Improve this answer
5
  • I hate this config, it can cause issues in the log shipping function, log shipping alerts may trigger, sending an alert that the secondary server has fallen behind. Even more fun, at the time of role-switching, there may be transaction logs that have not been applied because reporting prevented it, which increases the role-switching time because these transaction logs are applied. TL;DR, if your doing this to save money make sure you have that money tucked away for recovery or really really know you can handle this. It takes the "right" database to do this with
    – Jim B
    May 20, 2016 at 21:33
  • I'm actually using log shipping with standby, but not for recovery. It's for a read-only copy of some data for administrative scripting purposes.
    – Katherine Villyard
    May 21, 2016 at 1:21
  • And I'd argue that if it's not for recovery, and it's just a read only copy,you probably don't need it at all, and your database is probably taking a bigger hit from log shipping, than if you just read the records you need. Obviously I don't know any details about your particular situation, and perhaps it makes sense, but in general, the log shipping benefits don't outweigh the risks or performance hit anymore.
    – Jim B
    May 21, 2016 at 4:00
  • @JimB It's a firewall issue. Also, I'm not using the built-in log shipping functionality but am doing it manually, so the main DB's contribution is only running t-logs. Encrypted t-logs go to a place where the secondary can see it, and are restored scriptomatically.
    – Katherine Villyard
    May 21, 2016 at 14:41
  • 1
    The stand by server is for reporting only. The third party vendor's software restricts me to this configuration. The vendor says that options other than log shipping will be available "soon". I have not been able to nail down what "soon" means though.
    – user142253
    Jun 2, 2016 at 16:14
1

If your domains do not have trust: There is a way to do this by using local BUILTIN groups, BUILTIN\Guests (for example). This group has same SID on all Windows servers.

  1. Add this group as a database principal on primary server (with DB permissions you want). You can also explicitly disable this login on server level to avoid unwanted logins on primary.

    CREATE USER [BUILTIN\Guests] FOR LOGIN [BUILTIN\Guests] WITH DEFAULT_SCHEMA=[dbo] ALTER ROLE [db_datareader] ADD MEMBER [BUILTIN\Guests]

  2. On Secondary - create the login, restore the DB in standby mode. Builtin\Guests will get automatically "mapped" to the server login.

  3. On Secondary - manage the security of the [BUILTIN\Guests] group in Local user manager (make sure this group does not have RDP permissions).

It is a "workaround" but let's you manage the security of the DB via AD.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .