Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
methodology
Starting Member
31 Posts |
 Posted - 2007-04-13 : 10:42:07
|
| HiSo imagine the scenario - sync SQL 2005 mirroring and the mirror server goes offline. accordingly the transaction log of uncommitted stuff grows on the primary.To what extent does this effect the efficency of running queries against the primary? Any query will need to check data in both the hardened database on disk and the trans log to make sure that it retrieves the right info, correct? surely uncommited trans may affect the committed data and so both sources need to be chekced? logically something like this MUST happen? and the bigger the trans log grows ie the longer the mirror remains offline the more inefficient the primary would become at servicing client query requests?still trying to dig my way out of being stuck with sync miroring on sql 2005 standard. if the efficiency of answering queries takes a nose dive in this scenario, then im stuffed.THanksAlastairMethodology"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips. |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-04-16 : 18:33:23
|
| Your understanding of the transaction log is not correct.Queries never look at data in the transaction log. In most cases (unless you use the read uncommitted isolation level) data is locked when modified by a transaction and not unlocked until the transaction is committed. All queries are answered by reading data from the database into memory (the buffer cache) and then accessing the data (if it is not locked) in the buffer cache. So data that is already in the buffer cache is not read from disk at all. Data is modified in the buffer cache too, then when a transaction is committed other connections get to read the modified data right out of the buffer cache (even though it has not yet been saved to the database disk - the modification has however been recorded in the transaction log on disk). A background process (the checkpoint process) then later reads the transaction log to update the database.So while sync mirroring may affect performance because transactions must be added on two servers instead of one, it's not reading the transaction log when you run a query that has anything to do with it, because that doesn't happen. The concern if you lose the mirror is not a performance one, rather it is that your transaction log cannot be truncated on the principal (because it contains transactions that have not yet been applied to the mirror), which means that it could grow very large and fill your disk.There is an excellent article that describes all of the performance characteristics of database mirroring herehttp://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/dbm_best_pract.mspx |
 |
|
|
SatyaSKJ
Starting Member
46 Posts |
Posted - 2007-04-17 : 09:16:52
|
| In the High Availability operating mode, if the principal fails, the mirror server will automatically become a new principal and recover its database. Using the new ADO.NET or SQL Native Access Client drivers, applications can also perform an automatic failover from the client servers as well. In any case, comparing log shipping to database mirroring should make it clear that it is important to keep database and transaction log backups of the principal database. Applying these log backups to a log shipping server can supplement your database mirroring configuration.Satya SKJSQL Server MVPhttp://www.sqlserver-qa.net |
|
|
|
methodology
Starting Member
31 Posts |
Posted - 2007-04-18 : 05:13:58
|
| But surely then locked data in the cache is potentially WRONG as it may be pending updating by trans log data?"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-04-18 : 11:03:41
|
quote:
Originally posted by methodology
But surely then locked data in the cache is potentially WRONG as it may be pending updating by trans log data?"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.
No, the data is modified in the buffer cache immediately, the transactions in the log are used to modify the data in the database, not the buffer cache. |
|
|
|
|
|
|
|
|
Sync Mirroring - uncomm trans & dbase efficiency