August 16, 2007 at 12:29 pm
Howdy,
One of the fundamental requirements in my workplace is being able to provide select people access to a database that they can perform direct SQL queries on (hopefully well constructed ones) without performing these queries directly on an application database. I'm also looking to put the application database into a mirroring type setup under SQL 2005.
If I was to have the App DB Mirror creating a snapshot database (re-created periodically), would I then be able to create another database/system that mirrors from the snapshop database. What would be the pros and cons? Is there a better way to ensure that end users can access the application data close to real-time AND be able to access it all of the time, without touching the application database(s) directly?
August 17, 2007 at 6:48 am
Be careful of your terms. A database snapshot and a snapshot replication, and a mirror are all specific terms and your post is a bit confusing because of this. I think you are saying is that you want to make a periodic copy of your database, and then a copy of this database made active so users can query it.
Why the two copies?
Database mirroring give you a database you cannot access without doing a failover. It is for redundancy, not for kapping n active copy of your data that you can query. If you are looking for redundancy of your production system, this can be a good solution.
As far as a copy of the database that users can query without impacting the production system, you may want to consider using replication or log shipping. If the number of transactions on your database are not unreasonably high, transactional replication will give you a near real-time copy for users to query. Log shipping would give you more latency, but could be used as part of a failver solution as well.
August 17, 2007 at 5:01 pm
A mirrored db can be used as a reporting db when used with database snapshots. This may accomplish what you need.
August 18, 2007 at 6:30 pm
My understanding is that there have been inherent issues with transactional replication (at least with SQL 2k) where the data is transferred to the replication database and no error is reported, but upon closer inspection and comparison to the principal database there is data missing. This was one of the pluses with Synchronous Mirroring where ongoing data isn't replicated until there is confirmation from the replication server that the transaction has been committed. Can someone confirm that data loss with transactional replication has been an issue (under 2k), and comment on their experience with 2k5?
Cheers.
August 20, 2007 at 5:25 am
I have used transactional replication pretty extensively and have had very few instances of any data loss. I have most often found problems in the way replication was configured.
August 20, 2007 at 2:43 pm
Thanks for your response Michael. Any pointers on common mistakes / things to avoid when configuring replication?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply