Help required for OLTP kind of processing

Question

Help required for OLTP kind of processing

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Syed Jahanzaib Bin hassan (4/23/2011)
overhead on the same database
Database snapshot fetch the data from the primary database,It will affect the performance of the database and overall performance of the server
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com

Please clarify. Are you saying that if you take a snapshot of the mirror database, the snapshot has to pull the data from the principal database?

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 2

No,

If you take a snapshot then snapshot do transaction of the same primary database

and database mirroring is an alternate of Cluster failover not for Replication check this by microsoft

http://msdn.microsoft.com/en-us/library/ms177412.aspx

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

Oliiii SSCertifiable Points: 5328 More actions · Answer 3

The snapshot will be an overhead of the mirror database yes, but the important thing is the performance of the principal. As long as the mirrored DB is not on a server so busy its no longer able to apply transactions from the principal, you'll be fine.

Being able to read the mirror is one of the greatest feature of the database mirroring and one of the best way to offload reporting without having to fiddle with log shipping.

The whole point of this is to offload some of the heavy lifting to another server. If you have to run read only operation on an important OLTP DB you might as well do it on another machine.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Syed Jahanzaib Bin hassan (4/24/2011)
No,
If you take a snapshot then snapshot do transaction of the same primary database

Okay, I'm sorry but this statement really doesn't make an sense to me. It is almost like you are saying that taking a snapshot on the mirror data base takes a snapshot on the principal database.

and database mirroring is an alternate of Cluster failover not for Replication check this by microsoft
http://msdn.microsoft.com/en-us/library/ms177412.aspx

Mirroring is not an alternative to cluster failover, although both are alternatives to be considered for providing high availability for critical databases. They work on totally different principles. Taking a snapshot of the mirror database to use for reporting purposes is a viable use of the mirror database.

Clustering uses shared resources and requires identical configurations on both cluster servers (or more if more than 2 nodes). Mirroring, however, shares nothing and both servers can be different regarding resources. At a previous employer I mirrored databases between blade/san systems and rack mounted/DASD systems.

What you have to take into account is the potential for a failover and what your contingency for that is regardinging reporting should that occur. One alternative here is to drop the snapshot database as part of the failover process thereby locking out reporting use.

You also need to remember that you have to license the mirror server if you plan on using it for reporting.

Syed Jahanzaib Bin hassan SSChampion Points: 11410 More actions · Answer 5

I didnt say like this as you get ,snaphot database work with database which one is snaped shot,A database snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A snapshot persists until it is explicitly dropped by the database owner

http://msdn.microsoft.com/en-us/library/ms175158.aspx

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com

My Blog
www.aureus-salah.com

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 6

Syed Jahanzaib Bin hassan (4/28/2011)

I didnt say like this as you get ,snaphot database work with database which one is snaped shot,A database snapshot is a read-only, static view of a database (the source database). Multiple snapshots can exist on a source database and always reside on the same server instance as the database. Each database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A snapshot persists until it is explicitly dropped by the database owner

http://msdn.microsoft.com/en-us/library/ms175158.aspx

Regards,

Syed Jahanzaib Bin Hassan

MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

My Blog

http://www.aureus-salah.com[/quote%5D

You are quoting a lot of items from BOL, which is technically copyright infringement. please don't overly quote things you didn't write.

The initial confusion is that you were using the work "primary" with snapshot, which is not correct.

The load for a mirror/snapshot setup is as follows:

principal - OLTP work, mirroring read and transaction send.

mirror - receive mirror transactions, and page reads from snapshot that are unchanged

snapshot - reporting query load. Pages changed since snapshot come from snapshot MDF, pages unchanged pulled from mirror.

You need to be careful in how you are giving the advice. A few of your statements are not clearly written and that is causing some confusion with others. Mirroring is an HA technology, but different from clustering.