March 17, 2008 at 5:20 pm
I have two SQL 2005 Standard database servers, one's the production box and the other is not in use. I'd like to use the secondary server mainly for running reports on but I'd also like to use it as a warm standby as the reports will be read only.
I have a couple of questions:
- what kind of downtime will the report users experience during the transaction log restore process? i.e. if I leave it running during the default 15 minute intervals, will that kill the session/connections to the database on the secondary/reporting server every 15 minutes when the transaction log is processed by the secondary server? Both the reports/main application are IIS/ASP apps running on the same box; I'm not sure how/if the app(s) handle database connection losses.
- are there any performance trade-offs with log shipping? From what I've read, there doesn't seem to be, but I'd like to know what others have experienced.
- I've also read that you can set up a 3rd server to monitor the log shipping: can this be a non-SQL server (i.e. a 2003 standard box on the same domain)?
TIA!
March 19, 2008 at 1:02 pm
The amount of time a transaction log takes to restore is based on the size of the transactions and hence the number of transactions your source system has processed since the last log was shipped. So, if you have one transaction every 15 minutes the down time will be miliseconds, but if you have a million transactions, it will take much more time.
If you configure the log shipping to kill connections, it will kill every connection every 15 minutes so it can apply the transaction logs. This means that if a report is in-process it will error.
Performance trade-offs are almost nothing. You are probably doing a transaction log backup anyway. If so, the performance hit on your production server is the file copy to the standby server which is usually minimal unless you get a really big transaction log now and then.
You can set up log shipping to be executed from a third server and therefore basically monitored from that server. It is just more hardware - all three servers must be licensed for SQL server.
Another - possibly more appealing - option in SQL 2005 is database mirroring. Mirroring does not support read only access of the warm-standby directly, but you can mirror to your standby server and then create a database snapshot of the standby database that you can report from (make sure you get the correct editions of SQL). The advantages to this approach really lie in the fact that mirroring is much more suited for failover than log shipping is.
March 22, 2008 at 9:31 am
For the reasons Michael so well explained I would caution against using the warm standby for reporting. If you do decide to do so, be prepared (and prepare your users) for potentially long and irregularly occurring down times, plus the potential for stale data if, for some reason log-shipping has failed for some time.
Restoring logs on the standby every 15 min. basically means you cannot use it for reporting. You will have to settle for scheduling log shipping once or 2x a day, while the rest of the time your standby is used by your users for reporting. The users will have to settle for somewhat stale data, since log-shipping will be less frequent. All in all, it may be a compromise you and your users may not be happy with, especially if you want to use your standby for disaster recovery as well.
The morale of the story is, you cannot use the same standby server both for disaster recovery and reporting, unless you are willing to live with at least a few hours of lost data should something happen to your prod. server.
Trust me, I'm there right now, and it's not pretty... 🙂
Explore the mirroring/snapshot option that Michael mentioned.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 24, 2008 at 3:32 pm
Thanks for the responses, guys.
AFAIK, mirroring requires SQL Enterprise Edition, whereas Log Shipping does not. Can anyone clarify this? I think they'd rather not have to pay for additional licensing for SQL if they don't have to.
One more requirement is that the reporting database be writable: since the reporting database and the application database are identical, and the application code normally records usage/session data, the application owners would/could not change that behaviour (its a 3rd-party closed source application), so I think this will also rule out snapshots as I'm assuming the snapshots would be read-only. It's not so much that the usage data needs to be preserved (it can/will be overwritten), it's just that the application owners don't want to change their application logic.
Another issue is that the primary database that's running the application will most likely require somewhat frequent schema changes -- will this break the mirror? I'm assuming that any schema changes would come across with the transaction log shipments, and that makes log shipping appealing.
I've been toying with a shipping-based "hybrid" solution:
SERVER1/AppDb log ships to SERVER2/AppDb every 30 minutes in standby/read-only mode. Once, ~12:15AM daily*, I run a job that does a backup/restore of SERVER2/AppDb to SERVER/ReportingDb, which would then be used for the reporting database. The log shipping jobs would not be impeded if the backup/restore job on SERVER2 completely successfully within the 30 minute window allotted, and if the log-shipping jobs were impeded (SERVER2/AppDb is offline whilest the backup job is running), my understanding is that the transaction logs would just queue up on disk until the log-shipping restore job runs again, 30 minutes later, without data loss.
*I'd like to get a feel for how long, on avg., the 12:00AM log-shipping job would take to run before I schedule the job. There's activity after normal business hours on the app-side, but at a much more infrequent rate.
Having day-old data would be an acceptable compromise and the warm standby would never be older than 30 minutes.
Thoughts?
March 24, 2008 at 6:17 pm
gravyface (3/24/2008)
Thanks for the responses, guys.AFAIK, mirroring requires SQL Enterprise Edition, whereas Log Shipping does not. Can anyone clarify this? I think they'd rather not have to pay for additional licensing for SQL if they don't have to.
Yes, I believe mirroring requires Enterprise Edition.
One more requirement is that the reporting database be writable: since the reporting database and the application database are identical, and the application code normally records usage/session data, the application owners would/could not change that behaviour (its a 3rd-party closed source application), so I think this will also rule out snapshots as I'm assuming the snapshots would be read-only. It's not so much that the usage data needs to be preserved (it can/will be overwritten), it's just that the application owners don't want to change their application logic.
I'm confused as to why your reporting db has to be read/write. A reporting db is by definition a read-only db. Your 3rd party app does not need to hook up with the reporting db, does it? (Unless the 3rd-party app has a reporting component that cannot somehow be split from the OLTP component.) If you could provide some more info here, it would help.
I need to think some more about the rest of your message.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 24, 2008 at 6:39 pm
The reporting and data entry components are all part of the same application and are not separate components. We're essentially duplicating the application: one instance (SERVER1/AppDb) will be for OLTP, the other instance will be for reporting (SERVER2/AppDb or if my "hybrid" solution works, SERVER2/ReportDb).
In an ideal world, this application would either a) not grind everything to a halt when reports are being executed b) be modular to allow for distributed/dedicated reporting, but it's not, and we have to make due with what we've got.
March 24, 2008 at 8:38 pm
SERVER1/AppDb log ships to SERVER2/AppDb every 30 minutes in standby/read-only mode. Once, ~12:15AM daily*, I run a job that does a backup/restore of SERVER2/AppDb to SERVER/ReportingDb, which would then be used for the reporting database. The log shipping jobs would not be impeded if the backup/restore job on SERVER2 completely successfully within the 30 minute window allotted, and if the log-shipping jobs were impeded (SERVER2/AppDb is offline whilest the backup job is running), my understanding is that the transaction logs would just queue up on disk until the log-shipping restore job runs again, 30 minutes later, without data loss.
I would suggest that you take a nightly FULL backup on SERVER1/AppDb, NOT SERVER2/AppDb. During the backup turn off your txn-log backups, say between 10 pm and 12 am. Restore the full backup on SERVER/ReportingDB. That way there will be no conflict to the log-shipping stream from SERVER1/AppDb to SERVER2/AppDb:
SERVER1/AppDb --->>> SERVER2/ReportingDB
full restore
SERVER1/AppDb --->>> SERVER2/AppDb
log-shipping
Better yet:
Take your full backups on SERVER1/AppDb and copy them to a central repository location, say Loc1.
Take your txn-log backups on SERVER1/AppDb and copy them regularly to a central repository location, say Loc2. Do your restore and log-shipping from Loc1 and Loc2, respectively. This will reduce load on your primary server, SERVER1.
SERVER1/AppDb ---> Loc1 ---> SERVER2/ReportingDB
full restore
SERVER1/AppDb ---> Loc2 ---> SERVER2/AppDb
log-shipping
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
March 24, 2008 at 10:46 pm
You can use mirroring in Standard edition but you are limited to synchronous ie the transactions are confirmed on the mirror before the primary - good for availability but it does incur a small overhead on each transaction. The mirror is unavailable and the snapshot is read-only, so if you truly need to write then the only out-of-the-box solution is replication.
One possibility that you might wish to explore is having the application request report data from the mirror - it can be transparent to the user and be handled in the app or (if it uses stored proc based reporting) handled on the database via linked server. Of course both options force you to keep two servers available instead of one.
March 25, 2008 at 5:39 pm
Better yet:
Take your full backups on SERVER1/AppDb and copy them to a central repository location, say Loc1.
Take your txn-log backups on SERVER1/AppDb and copy them regularly to a central repository location, say Loc2. Do your restore and log-shipping from Loc1 and Loc2, respectively. This will reduce load on your primary server, SERVER1.
SERVER1/AppDb ---> Loc1 ---> SERVER2/ReportingDB
full restore
SERVER1/AppDb ---> Loc2 ---> SERVER2/AppDb
log-shipping
Even better -- I think I was leaving too much to chance with the "failure" queueing, but this gives me more control/predictability. I think I'm going to run with this; I'll let you guys know how it pans out. Thanks.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply