How to replicate from Standby mode database

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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.

Method 2

Any reason you can’t just use the native Replication features of SQL Server, such as Transactional Replication?

You can real-time synchronize between your two servers, and then reporting off the Subscriber server won’t affect performance of the Publisher server.

You can also look into Snapshot Replication if you want something less real-time and more scheduled on a recurring basis.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply