| Groups | Blog | Home |
sql server replication : Replication vs mirroring
as a database for reporting purposes. At face value, mirroring would seem the better option. However, it would appear that the users aren't able to run queries against the mirrored database. See statement below from the MSDN article on mirroring. "Because the mirror database is in a recovering state, it cannot be accessed directly. You can create database snapshots on the mirror to indirectly read the mirror database at a point in time. (See 'Database Mirroring and Database Snapshots' later in this paper.)" Snapshots have the following drawbacks: (1) The information help in the accessible database may be old. (2) The additional resources to create the snapshot. (3) Require enterprise edition (or developer edition) of SQL Server In addition, I though I read somewhere about database mirroring not being supported by Microsoft. Therefore, an I correct in saying that a mirrored database is not really an appropriate technology to use for near real-time reporting and replication is a better option.
Thanks Hilary
According to Table 2: Database Mirroring and SQL Server 2005 Editions in the article http://www.microsoft.com/technet/prodtechnol/sql/2005/ dbmirror.mspx, database snapshots are not available in the Standard edition. However, you have indicated that database snapshots are available in the Standard Edition. Which is correct? [quoted text, click to view] On Feb 6, 10:52 pm, "Hilary Cotter" <hilary.cot...@gmail.com> wrote: > Answers inline. > > -- > Hilary Cotter > > Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com > > "PromisedOyster" <PromisedOys...@hotmail.com> wrote in message > > news:1170764959.666164.300590@h3g2000cwc.googlegroups.com... > > > Our clients would like an automated fallover database server as well > > as a database for reporting purposes. > > > At face value, mirroring would seem the better option. However, it > > would appear that the users aren't able to run queries against the > > mirrored database. See statement below from the MSDN article on > > mirroring. > > Mirroring isn't as scalable as replication. With high safety on all dml on > the principal is slower. With high performance there is no performance hit, > but in both modes it is only scalable up to 10 or so databases per instance. > > > > > "Because the mirror database is in a recovering state, it cannot be > > accessed directly. You can create database snapshots on the mirror to > > indirectly read the mirror database at a point in time. (See 'Database > > Mirroring and Database Snapshots' later in this paper.)" > > > Snapshots have the following drawbacks: > > > (1) The information help in the accessible database may be old. > > (2) The additional resources to create the snapshot. > > (3) Require enterprise edition (or developer edition) of SQL Server > > Plus it eats up disk space even when there are no transactions going to the > snapshot. It is also available in Standard and Enterprise editions. > > > > > In addition, I though I read somewhere about database mirroring not > > being supported by Microsoft. > > It is supported since SP1. There was a trace flag which enabled it in the > RTM version. > > > Therefore, an I correct in saying that a mirrored database is not > > really an appropriate technology to use for near real-time reporting > > and replication is a better option. > > Yes, replication is ideal for near real-time reporting. I should point out > that replication does not do automatic failover. Clustering and data base > mirroring do, but you aren't really able to offload reporting with these > technologies.
Answers inline.
-- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "PromisedOyster" <PromisedOyster@hotmail.com> wrote in message news:1170764959.666164.300590@h3g2000cwc.googlegroups.com... > Our clients would like an automated fallover database server as well > as a database for reporting purposes. > > At face value, mirroring would seem the better option. However, it > would appear that the users aren't able to run queries against the > mirrored database. See statement below from the MSDN article on > mirroring. Mirroring isn't as scalable as replication. With high safety on all dml on the principal is slower. With high performance there is no performance hit, but in both modes it is only scalable up to 10 or so databases per instance. [quoted text, click to view] > > "Because the mirror database is in a recovering state, it cannot be > accessed directly. You can create database snapshots on the mirror to > indirectly read the mirror database at a point in time. (See 'Database > Mirroring and Database Snapshots' later in this paper.)" > > Snapshots have the following drawbacks: > > (1) The information help in the accessible database may be old. > (2) The additional resources to create the snapshot. > (3) Require enterprise edition (or developer edition) of SQL Server Plus it eats up disk space even when there are no transactions going to the snapshot. It is also available in Standard and Enterprise editions. [quoted text, click to view] > > In addition, I though I read somewhere about database mirroring not > being supported by Microsoft. > It is supported since SP1. There was a trace flag which enabled it in the RTM version. [quoted text, click to view] > Therefore, an I correct in saying that a mirrored database is not > really an appropriate technology to use for near real-time reporting > and replication is a better option. > Yes, replication is ideal for near real-time reporting. I should point out that replication does not do automatic failover. Clustering and data base mirroring do, but you aren't really able to offload reporting with these technologies.
Sorry PromisedOyster, I meant database mirroring is available in standard
and enterprise editions. http://msdn2.microsoft.com/en-us/library/ms143761.aspx My mistake. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "PromisedOyster" <PromisedOyster@hotmail.com> wrote in message news:1170767529.854834.79340@m58g2000cwm.googlegroups.com... > Thanks Hilary > > According to Table 2: Database Mirroring and SQL Server 2005 Editions > in the article http://www.microsoft.com/technet/prodtechnol/sql/2005/ > dbmirror.mspx, database snapshots are not available in the Standard > edition. > > However, you have indicated that database snapshots are available in > the Standard Edition. > > > Which is correct? > > > On Feb 6, 10:52 pm, "Hilary Cotter" <hilary.cot...@gmail.com> wrote: >> Answers inline. >> >> -- >> Hilary Cotter >> >> Looking for a SQL Server replication >> book?http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL >> FTShttp://www.indexserverfaq.com >> >> "PromisedOyster" <PromisedOys...@hotmail.com> wrote in message >> >> news:1170764959.666164.300590@h3g2000cwc.googlegroups.com... >> >> > Our clients would like an automated fallover database server as well >> > as a database for reporting purposes. >> >> > At face value, mirroring would seem the better option. However, it >> > would appear that the users aren't able to run queries against the >> > mirrored database. See statement below from the MSDN article on >> > mirroring. >> >> Mirroring isn't as scalable as replication. With high safety on all dml >> on >> the principal is slower. With high performance there is no performance >> hit, >> but in both modes it is only scalable up to 10 or so databases per >> instance. >> >> >> >> > "Because the mirror database is in a recovering state, it cannot be >> > accessed directly. You can create database snapshots on the mirror to >> > indirectly read the mirror database at a point in time. (See 'Database >> > Mirroring and Database Snapshots' later in this paper.)" >> >> > Snapshots have the following drawbacks: >> >> > (1) The information help in the accessible database may be old. >> > (2) The additional resources to create the snapshot. >> > (3) Require enterprise edition (or developer edition) of SQL Server >> >> Plus it eats up disk space even when there are no transactions going to >> the >> snapshot. It is also available in Standard and Enterprise editions. >> >> >> >> > In addition, I though I read somewhere about database mirroring not >> > being supported by Microsoft. >> >> It is supported since SP1. There was a trace flag which enabled it in the >> RTM version. >> >> > Therefore, an I correct in saying that a mirrored database is not >> > really an appropriate technology to use for near real-time reporting >> > and replication is a better option. >> >> Yes, replication is ideal for near real-time reporting. I should point >> out >> that replication does not do automatic failover. Clustering and data base >> mirroring do, but you aren't really able to offload reporting with these >> technologies. > >
You're correct that you can't run queries against the mirror, but you can
against the database snapshot of the mirror. Admittedly this has high latency, so you could perhaps really compare replication and log shipping as a better set of competing technologies: http://www.replicationanswers.com/Standby.asp Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |