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 |
|
magarwal77
Starting Member
3 Posts |
 Posted - 2011-05-27 : 10:36:52
|
| Hello All, We are planning to have Replication for the SQL 2005 database. I believe its call Mirroring in SQL SERVER 2005. What i understand it transaction logs will be automatically pushed to Replication site and 2ndry database will be running in roll forwarding mode to bring the DB DR site in sync with Primary. I would like to understand the past one week history of transaction log generated on hourly basis. I know how to get this information in Oracle, bit confused if there is any way to get this information in SQL SERVER. Any assistance will be greatly appreciated. Regards,Mahi |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-27 : 16:32:47
|
| Oh my - you have confused at least separate topics into one. In SQL Server 2005 and above, we have Replication, Mirroring and log shipping. These are 3 separate and distinct topics...Replication is a process where you publish tables that will be replicated to another system. The publication can be either snapshot, transactional or merge replication. Once published, you can then have one or more systems subscribe to the publication. Generally, this is used to get a copy of specific objects from one system to another and available for use on the other system.Mirroring is a HA/DR strategy for creating another copy of the database on a separate server. Once the database is mirrored, transactions are sent from the principal database to the mirror database as those transactions occur on the principal. There are several ways of configuring this - which include the ability to automatically failover to the mirror if the principal is not available. The only impact on transaction logs with this is that the transaction log must be in full recovery model and the VLF (virtual log files) in the transaction log cannot be marked as reusable until the transaction has been committed to the mirror. When setup in high safety mode - mirroring will use a two-phase commit process where the transaction must be committed successfully on the mirror first, then it will be committed on the principal database.Log shipping is a process where the transaction log backups are sent to another server and applied (restored) to that instance on a scheduled basis (usually, this is done immediately but can be setup to lag behind).As for understanding the past one week of history - you cannot view the transaction logs the same way you can view the archive logs in Oracle. The transaction logs in SQL Server are not easily viewable and do not have the kind of information you would be looking for.Jeff |
 |
|
|
magarwal77
Starting Member
3 Posts |
Posted - 2011-05-28 : 08:51:43
|
| Hi Jeff, This is excellent and short explanation i ever had. Thanks for taking a time and efforts on this. So the solution what we are looking for is Mirroring HA/DR strategy by creating a copy of the database and then automatic applying transaction log files on to the DR site. Before we implement this we would like to know how much transaction logs are generated on hourly basis. so that we can pass this information to the network for appropriate bandwidth. As you mentioned that we can;t see the history of transaction log, i beleive there must be some other way to get this. I understand that we take the backup of transaction log after every 15 mins, that means if we record the size of transaction log backup, would it give us the size of the transaction log backup. This will be a manual process, i am looking for any other way to find this out. Once again your advice is very valuable for me. Thanks.Mahi |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-05-28 : 10:30:35
|
| Okay - let's clear up the difference between mirroring and log shipping. Log shipping sends (ships) the actual log backups to the other system and restores them on that system. Mirroring, on the other hand - only sends the transactions (not the backups) and those transactions are sent immediately.Depending on the mode of mirroring - then you will either have a two-phase or single-phase commit. If you set this up with high safety, then all transactions must commit on the mirror before being committed on the principal. If you set this up with high performance, then the commit is performed on the principal and then sent to the mirror.To identify how much data is going to be transferred, you can look at the size of the transaction logs. Make sure you review those sizes for at least a full 24 hour period - and over the weekend or month end processing to capture the largest set of data that will be transferred.With mirroring, you should be less concerned with how much data and how large the pipe is and more concerned with network latency. If there is any significant latency at all, you will not want to use high safety mode - because that will cause performance issues on the principal system as it waits for the transactions to commit on the mirror.If you have to move to high performance, then you still shouldn't be worried too much about the size of the pipe - unless you end up sending so much data that the mirroring process falls behind by a significant time. If that happens, and the principal goes down while that data is still being transferred - then you will have the potential of a lot of data loss.FWIW - I mirror one of my systems half way across the country in high performance mode across the WAN with no real issues. It does fall behind during my index rebuild processes - but during business hours those transactions are applied to the mirror with no noticable lag.Jeff |
|
|
|
magarwal77
Starting Member
3 Posts |
Posted - 2011-05-29 : 14:08:39
|
| Jeff, Thanks a lot for the information. This will surely help me a lot while configuring Mirroring in High Performance mode. It is more or less same as in Oracle with Dataguard Configuration in High Availability, Performance and Protection mode. Regards,Mahi |
|
|
|
|
|
|
|
|
Transaction log Growth