Can you read from a log shipping secondary database?
In many scenarios, you only want to grant read access to a user on the log shipping secondary database. You do not want that user to be able to access the primary. This is tricky. To read from a database, you need a login with an associated database user.
What happens when SQL Server is in read only?
Transactions which are in process on the primary or any uncommitted transactions cannot be read on the secondary database when the database is in a read-only state. As previously mentioned, we have SQL Server Log Shipping in place for an application.
Why do I need to use log shipping in SQL Server?
SQL Server Log Shipping allows you to automatically send transaction log backups from a primary database server instance to one or more secondary database server instances. In most cases, log shipping is setup so you have a warm standby server for disaster recovery.
How can I change the database from standby / single user?
Its a not a standby database it was just a read only database… We have a nightly process that restores log shipping files from a different server to this server and it disconnects all the users and then put the database in standby mode to restore the data from another server into this database.
Can a logshipping secondary be in standby mode?
As of SQL Server 2014, you have a new option because of a couple of new security features. You can do this: When you add a member to this role, they can read anything. I tested and this worked for me against a logshipping secondary in standby mode just fine.
How to disable the log shipping primary instance?
And a lot of things could go wrong over time as tables are added, modified, and dropped. Create the login on the log shipping primary instance with the associated database user, and disable the login on the log shipping primary instance.
Why do I need to use log shipping?
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.
Can you create user in log shipping secondary?
To grant reads to a log shipping secondary database, you can create all the logins you want– but the log shipping secondary database is read only, so you can’t create a user in it. Up through SQL Server 2012, here’s the two main options I’ve seen people use (there are a couple more in the comments):