May 5, 2009 at 4:48 pm
We started off using snapshot replication because the vendor didnt put any primary keys on their tables.:ermm:
Snapshot replication caused locking when it was being reinitialized every morning. (Who knew some of the users were insomniacs trying to work at 4:00 am) So the Users complained to the Business and the Business complained to the DBA who referred the Business to the Vendor, and the Vendor gave us primary keys. But only on some of the tables,not all, just the ones being used for reports.
So we implemented transaction replication and things were good...till one day the Business decided it was not satisfied with the selection of tables offered for reports and wanted DBA to add in another. But this table did not have a primary key! Thus the Business complained to Director, who complained to DBA who referred him to the vendor who said they would add a primary key.
But the Director was not satisfied. What will happen, he asked, when Business wants to add another table to replication that has not a primary key? What if Business has to wait 2 or 3 days for a primary key to be added by the Vendor? This is not satisfactory. This will not do.
And the Director sent for the humble DBA and demanded a solution.
So the DBA being a relative novice with SQL Server heard of this thing called Log Shipping, where transaction logs are backed up from one database, copied to another server and applied to a second database. And he also read that the second database could be open in Read Only mode.
And the DBA began to wonder.
And this is where the I, humble DBA am now. If I implement log shipping that would mean the production database will be available to User with no contention issues due to snapshots etc. And all the tables will be available on the reports database so Business is free to run ad hoc queries and reports until his little fingers turn purple and his nails fall out.
My question is, Will the reporting database be accessible and readable while the logs are being applied? As I think this will help me determine how often the logs will be applied and how current the data is.
Is anyone else using this as a replication solution for reporting/ad hoc queries?
Does anyone have a better idea? (Other than going with another vendor... as this is not an option.)
May 5, 2009 at 7:29 pm
And this is where the I, humble DBA am now. If I implement log shipping that would mean the production database will be available to User with no contention issues due to snapshots etc. And all the tables will be available on the reports database so Business is free to run ad hoc queries and reports until his little fingers turn purple and his nails fall out.
My question is, Will the reporting database be accessible and readable while the logs are being applied? As I think this will help me determine how often the logs will be applied and how current the data is.
Is anyone else using this as a replication solution for reporting/ad hoc queries?
Does anyone have a better idea? (Other than going with another vendor... as this is not an option.)
Log shipping is essentially an automated version of backup / copy / restore. Transaction logs at the master are backed up, copied to a location where they're accessible by the secondary server, and then applied to the secondary server. Applying the transaction logs at the secondary server is done through the RESTORE command, which means that while those logs are being restored, the database has to be in single-user mode.
Having said that, if you can get by with having the database being a day behind what's in production, you could set the recovery process to happen each night, after your users are out of the office. The logs would be restored, and then the next day your users can be running reports.
Hope that helps.
David
May 5, 2009 at 8:32 pm
Applying the transaction logs at the secondary server is done through the RESTORE command, which means that while those logs are being restored, the database has to be in single-user mode.
While tlogs are getting restored / applied, database is in restoring state and is not accessible.
May 6, 2009 at 11:26 am
Thanks for the replies!
I think I might be able to work with that.
May 6, 2009 at 6:08 pm
You have two options there either the users are kicked off while restoring the tlogs or the secondary server has to wait till all the users are disconnected.
Since you are saying its for reporting purposes I suppose you do not need immediate data. Log shipping might quite work well for your purpose.
Test it and show it to the director if he is satisfied with it, then everything is well and fine if not you could give us a shout we would be able to think any other alternative.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply