SQL Server – How to Replicate from Standby Mode Database

replicationrestoresql server

I have set up a pipeline which brings transactional logs each hour from a FTP site, and this newly created database is in Standby mode all the time because I need to read data during an office hour.

Initially, I was thinking about leaving the restore mode in NoRecovery because I learned that Standby mode takes more time and for performance reason. But then, I realized that I cannot read data during an office hour, so I decided to set up hourly transaction log update in Standby mode all the time.

So, I will have one database which I am trying to isolate it to just pull transactional logs hourly, and then I would like to have a copy of this database so that it would not interrupt any process (of hourly logging) while I am reading data for reporting purposes.

I am trying to see if there is a way to replicate this database (which is in standby mode) so that at least I have a backup database, and I could pull data from there for reporting purposes.

Or would it be better if the database that update logging files daily from FTP site be NoRecovery mode and there is a way to replicate this database somehow? I am just thinking about a better option to set up stable database architecure.

BTW, this particular SQL Server is in an Azure VM, and I am trying to bring in only necessary data into Azure SQL Server (Portal) eventually.

Best Answer

I am trying to see if there is a way to replicate this database (which is in standby mode) so that at least I have a backup database, and I could pull data from there for reporting purposes.

You can always restore two separate databases from the log backups, one in NoRecovery and one in Standby.

Related Solutions

Sql-server – Advice on transaction log based reporting server

Is there a better solution for what I am attempting to accomplish where the database wouldn't need to refuse connections, or could queue connections until the backup has been applied?

You need to write some manual code and change backups .Please see below article which we are also applying from Aaron bertrand

http://sqlperformance.com/2014/10/sql-performance/readable-secondaries-on-a-budget

Sql-server – Can SQL Server database be changed from NoRecovery to Standby

Yes, you can restore the logs by changing the settings for the Restore Job to be set to STANDBY, instead of NORECOVERY. This would provide a degree of Read Only access until the next restore is run.

If you are using the log shipping functions that come with SQL Server you use the settings shown by Ali Razeghi for using sp_change_log_shipping_secondary_database.

If you have rolled your own log shipping process (which many people have done) then you are already controlling the restore process yourself.

EDIT: Since you restore the secondary one time a day, the READ ONLY window will be most of a day. So that eases the scheduling issue.

Since the procedure sp_change_log_shipping_secondary_database is not working, either (1) you ran this on the primary instead of the secondary server, or (2) your organization made their own custom log shipping process.

EDIT: If you are in the (2) state, then please include the details of the secondary server's Log Shipping Restore Job.

However, a database in NORECOVERY can be updated to STANDBY like this:

RESTORE DATABASE [MyDatabase]
WITH STANDBY = N'D:\MSSQL\Backup\MyDatabase_RollbackUndoFile.bak'

Any time the Restore Job recovers a series of LOG files, it will disconnect any STANDBY users that are still connected.

Also see Kendra Little's post: Reporting From a Log Shipping Secondary in STANDBY mode Contained in here post is a mention of the problem that you have run into with restoring a 2008 database to a 2008 R2 Secondary.

You can’t use “STANDBY” Mode if your Secondary is a Higher Version

I was a little sad when I learned this one years ago. I had a SQL Server 2005 instance that was the log shipping primary. The log shipping secondary was made readable so that developers could check out production data if needed without having access to production.

This is because the database pages need to be upgraded to the 2008 R2 level in order to be readable by STANDBY, but that is a violation of keeping the Secondary exactly like the Primary.

Really the only route to getting STANDBY mode on your secondary is to upgrade your SQL Server 2008 to SQL Server 2008 R2 so as to match the secondary. (Or (if you want to modernize) to upgrade to a higher version for both primary and secondary.)

Related Question