September 26, 2013 at 10:48 am
I have a 600GB database that I need to copy up to date information to. I must have it in a ready only state while we migrate information from it. I cannot replicate because all the tables do not replicate and I need all of them. Log shipping was a bust, thinking of mirroring but I need it in a read only state. I read that I can put it in standby mode for read only but will I be able to access it for reading purposes? I get conflicting answers out on the web and want you guys to chime in.
Thanks in advance!
MCSE SQL Server 2012\2014\2016
September 26, 2013 at 11:39 am
Sounds like mirroring will work. The mirror would be used as the report server. It would stay in standby mode, but that does not mean you cannot query it. It just means that it's listening to the principal and the transaction log is just being executed against it as well.
September 26, 2013 at 12:29 pm
Mirroring is in a restoring state. You cannot access the secondary. You can make snapshots on it, but those are points in time.
Log shipping is the best option here, but it does cause issues with reporting when the next log has to be restored.
Replication might be your best option. Why couldn't you do this?
If you are 2012, you could use AlwaysOn to help.
September 26, 2013 at 1:17 pm
not 2012, I wish, always on would be great, I was thinking SS but I am on standard, BOO!
the database is a sharepoint content, unable to do replication. How do I get the log shipping to be in a standby state Steve?
Do I restore in norecov, add diff with no recov and logs with no recov, then do standby for applying tran logs?
MCSE SQL Server 2012\2014\2016
September 26, 2013 at 2:06 pm
Do I restore in norecov, add diff with no recov and logs with no recov, then do standby for applying tran logs
Thats correct, restore the full backup and any differentials with the norecovery option, and then the logs with the standby clause, the database will then be readable.
There is an option in the GUI to specify this.
---------------------------------------------------------------------
September 26, 2013 at 2:07 pm
george sibbald (9/26/2013)
Do I restore in norecov, add diff with no recov and logs with no recov, then do standby for applying tran logs
Thats correct, restore the full backup and any differentials with the norecovery option, and then the logs with the standby clause, the database will then be readable.
There is an option in the GUI to specify this.
do I put the logs I have to apply manually in standby or norecov? This is where it failed on me last time adn I called it a bust... HA!
MCSE SQL Server 2012\2014\2016
September 26, 2013 at 2:20 pm
doesn't matter, you can switch between modes.
---------------------------------------------------------------------
September 26, 2013 at 2:56 pm
so restore in no recov, add my diff in no recov, then add manual logs in no recov, then start log shipping with secondary alreay initl in standby mode and I am good? I need steps, it's my ocd'ness 😀
MCSE SQL Server 2012\2014\2016
September 26, 2013 at 3:41 pm
It's the Database!!! (9/26/2013)
so restore in no recov, add my diff in no recov, then add manual logs in no recov, then start log shipping with secondary alreay initl in standby mode and I am good? I need steps, it's my ocd'ness 😀
you can do all your manual restores (full, diff and log) in norecovery, leave db in norecovery, then when you start logshipping do it in standby mode.
Keep any manual logs away from the logshipping created logs. If possible start logshipping immediately after a diff backup, less likely to get logs out of sync.
---------------------------------------------------------------------
September 26, 2013 at 6:04 pm
Do what George wrote. Standby for logs, with the automated process.
Note that whenever logs get restored, users will be kicked out of the database, so plan your restores accordingly. If you have restores < every hour, likely log shipping will be more annoying than useful.
If you have shared storage you could also look at Scalable Shared Databases.
The other option is build custom replication, using SSIS to ETL data across on some schedule.
September 26, 2013 at 6:41 pm
That's what I needed! Thanks guys! 🙂
MCSE SQL Server 2012\2014\2016
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply