0

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.

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
1
  • I ended up just replicating two StandBy modes (just for back up), not restoring in NoRecovery because I found no point of having NoRecovery since I would have to read data. I created a separate question here since my issue is not resolved. dba.stackexchange.com/questions/292194/…
    – Java
    May 25, 2021 at 0:32
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.

Improve this answer
2
  • I think Snapshot Replication is what I am looking into. But it appears that Publisher database cannot be Standby mode. It has to be Recovery mode, right?
    – Java
    May 25, 2021 at 17:36
  • @Java Correct that Standby mode doesn't work with replication. I believe you'll need to bring it out of Standby mode by executing a command such as RESTORE DATABASE YourDatabaseName WITH RECOVERY; first. From your second post, I understand now that your first database is being log shipped. I don't believe you'll be able to use Standby mode so long as you're using replication.
    – J.D.
    May 25, 2021 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.