October 24, 2010 at 11:07 pm
Our accountants could benefit from having a copy of our ERP systems database for reporting. Based on the research I've done, I am considering setting up asynchronous mirroring and create snapshots from the mirror.
My question is, how to I automate the snapshots so that the accountants can always have a common database name to connect to without interruption? Obviously I can create a job that creates the new snapshot and drops the previous one but I am wondering what others do and if there are any elegant solutions that I am not aware of.
October 24, 2010 at 11:34 pm
There was a (sort of similar) discussion over at http://www.sqlservercentral.com/Forums/Topic528119-146-1.aspx
This link has information on how to use synonyms to enable connection to the snapshot objects - allowing the snapshots to be modified to point to the new snapshot objects whenever the snapshot is re-created without causing issues to users connecting to the snapshot objects:
We have had a similar implementation (asynchronous mirroring and snapshots on the mirror for reporting purposes) and it has worked fine. Keep in mind the performance impact of having too many snapshots (documented here - http://www.sqlservercentral.com/articles/Performance+Tuning/64080/).
Do your end-users require the whole DB for ad-hoc reporting? i.e. if they need only a subset of tables then mirroring might be a bit of an overkill (depending on your requirement you could also look at replication or using some sort of ETL to provide an ad-hoc reporting environment).
October 24, 2010 at 11:43 pm
tnk (10/24/2010)
Our accountants could benefit from having a copy of our ERP systems database for reporting. Based on the research I've done, I am considering setting up asynchronous mirroring and create snapshots from the mirror.
If you just need a reporting database, consider transactional replication.
Mirroring is a DR solution, not intended for creation of a reporting DB. Snapshots on mirroring are kinda a way to make a failover server useful until it's needed for failover.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 25, 2010 at 12:10 am
GilaMonster, you are so right. Unfortunately the vendor of the ERP system won't support any kind of replication because they've, "never tested it".
After reading that thread, I think I should consider log shipping. There again, I would agree, log shipping is intended for DR and not reporting but it seems like a viable option for my circumstances. I understand that the logs won't restore when one of the accountants are connected but I think this is a limitation we can live with. If it restores logs every hour, it should get us what we need.
Thanks guys, I appreciate your responses.
October 25, 2010 at 2:51 am
tnk (10/25/2010)
GilaMonster, you are so right. Unfortunately the vendor of the ERP system won't support any kind of replication because they've, "never tested it".
Twits.
Neither transactional nor snapshot replication makes any changes at all to the tables in the database.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply