August 1, 2011 at 8:34 am
I'm looking to set up a HA solution between databases that are about 1000 miles apart and also the network is less then reliable. Reading through articles mentions that log shipping might be the better option if network latency is an issue.
I have also read that log shipping is to be deprecated in the future. If this is true then why set up a log shipping solution now in SQL Server 2008R2 if future versions of SQL Server will not have option of log shipping. Any advise or solutions would be greatly appreciated as I'm somewhat confused.
thanks,
Jonathan
August 1, 2011 at 6:24 pm
If you don't have good network latency and 1000 miles distance between servers then Data Mirroring is not good option for you. I am sure what your environment need for set-up of HA but you can consider Replication also as one of your option if you don't want to proceed with Log Shipping.
Not sure about Log shipping will be deprecated in very near future... 🙂
August 2, 2011 at 3:37 am
A)Database Mirroring
B)Log-shipping
A1)Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance. Database mirroring can operate synchronously or asynchronously.
B1)Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s). Log shipping supports an unlimited number of secondary’s for each primary database.
A2)Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror.
B2)Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule
A3)Database mirroring supports only one mirror for each principal database. That means DB mirroring is at database level
B3)Log-shipping can work on database and server level. You can configure multiple databases in logshipping
A4)Data Transfer: Individual T-Log records are transferred using TCP endpoints
Transactional Consistency: Only committed transactions are transferred
Server Limitation: Can be applied to only one mirror server
Failover: Automatic
Failover Duration: Failover is fast, sometimes < 3 seconds but not more than 10 seconds
Role Change: Role change is fully automatic
Client Re-direction: Fully automatic as it uses .NET 2.0/.Net 3.0
B4)With Log Shipping:
Data Transfer: T-Logs are backed up and transferred to secondary server
Transactional Consistency: All committed and un-committed are transferred
Server Limitation: Can be applied to multiple stand-by servers
Failover: Manual
Failover Duration: Can take more than 30 mins
Role Change: Role change is manual
Client Re-direction: Manual changes required
A5)Support only full recovery model
B5)Supports full and bulk-logged recovery model
A6)Mirror database is always in recovery mode. To read it you have use database snapshot.
B6)You can use the stand-by option to read the database on standby server
A7)Auto Page Recovery introduced with SQL SERVER 2008 so it will recover the damaged pages.
B7)Not supported
August 3, 2011 at 4:52 am
Jonathan Marshall (8/1/2011)
I'm looking to set up a HA solution between databases that are about 1000 miles apart and also the network is less then reliable. Reading through articles mentions that log shipping might be the better option if network latency is an issue.I have also read that log shipping is to be deprecated in the future. If this is true then why set up a log shipping solution now in SQL Server 2008R2 if future versions of SQL Server will not have option of log shipping. Any advise or solutions would be greatly appreciated as I'm somewhat confused.
thanks,
Jonathan
1) If you have network latency/bandwidth issues then you are gonna have trouble with ANY form of HA.
2) Using back up compression like Hyperbac from Red-Gate will help with the bandwidth needs for log shipping.
3) You can roll your own log shipping very easily if it does get deprecated in the future. I did this on a client box with 7400 databases on one server and it worked fine.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 3, 2011 at 5:58 am
Just because the networks have high latency doesn't rule out mirroring, you can adjust the failover timeout quite easily. You will need some trial and error to find the ideal timeout value.
Do you require the replicated database to be readable?
I currently replicate an 80gb SQL Server 2000 database from the UK to USA, a distance of over 3000 miles, the database also had to be available for query access and runs on a virtual SQL server. I used log shipping to achieve this.
I found the weak point was getting the initial database backup (compressed to 7gb with Litespeed) to the virtual SQL server in the USA. It traversed many firewalls and took ages. However, once it place it runs without problems
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 3, 2011 at 6:35 am
TheSQLGuru (8/3/2011)
Jonathan Marshall (8/1/2011)
I'm looking to set up a HA solution between databases that are about 1000 miles apart and also the network is less then reliable. Reading through articles mentions that log shipping might be the better option if network latency is an issue.I have also read that log shipping is to be deprecated in the future. If this is true then why set up a log shipping solution now in SQL Server 2008R2 if future versions of SQL Server will not have option of log shipping. Any advise or solutions would be greatly appreciated as I'm somewhat confused.
thanks,
Jonathan
1) If you have network latency/bandwidth issues then you are gonna have trouble with ANY form of HA.
2) Using back up compression like Hyperbac from Red-Gate will help with the bandwidth needs for log shipping.
3) You can roll your own log shipping very easily if it does get deprecated in the future. I did this on a client box with 7400 databases on one server and it worked fine.
7400 databases on one server? WOW. I never heard about so many placed on one. If it is possible for you to reveal,i would be interested to know the total size of all the data and RAM used here.
August 3, 2011 at 8:46 am
This is invaluable information guys much appreciated.
The databases on the secondary server to not have to be in a read state but requirements could change of course.
So changing the timeout with Database Mirroring is definitely something I will try.
Also I see that you can combine log-shipping along with Database Mirroring.
Any viewpoints on this method or any form of replication?
Jonathan
August 3, 2011 at 11:58 am
Jonathan Marshall (8/3/2011)
The databases on the secondary server to not have to be in a read state but requirements could change of course.
🙂 tell me about it
Jonathan Marshall (8/3/2011)
So changing the timeout with Database Mirroring is definitely something I will try.
Yes, use the following on the principal
Alter database Mydb set partner timeout 30
Jonathan Marshall (8/3/2011)
Also I see that you can combine log-shipping along with Database Mirroring.
Yes but not certain this is suitable for you
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 4, 2011 at 6:41 am
FYI -
I found this reference on page 2333 (i.e., on the CD-ROM) for "Microsoft SQL Server 2008 R2 Unleased," copyright 2011, SAMS Publishing:
"Also, you need to remember that log shipping is on the way out in future Microsoft releases, so don't plan too much new usage of that feature."
From page 2341:
"Remember, log shipping is destined to be deprecated by Microsoft (unofficially announced)."
These quotes sound odd to me. I agree with one of the earlier posters. It's not that hard to "roll your own" log shipping if it gets deprecated.
August 4, 2011 at 7:49 am
chandan_jha18 (8/3/2011)
TheSQLGuru (8/3/2011)
Jonathan Marshall (8/1/2011)
I'm looking to set up a HA solution between databases that are about 1000 miles apart and also the network is less then reliable. Reading through articles mentions that log shipping might be the better option if network latency is an issue.I have also read that log shipping is to be deprecated in the future. If this is true then why set up a log shipping solution now in SQL Server 2008R2 if future versions of SQL Server will not have option of log shipping. Any advise or solutions would be greatly appreciated as I'm somewhat confused.
thanks,
Jonathan
1) If you have network latency/bandwidth issues then you are gonna have trouble with ANY form of HA.
2) Using back up compression like Hyperbac from Red-Gate will help with the bandwidth needs for log shipping.
3) You can roll your own log shipping very easily if it does get deprecated in the future. I did this on a client box with 7400 databases on one server and it worked fine.
7400 databases on one server? WOW. I never heard about so many placed on one. If it is possible for you to reveal,i would be interested to know the total size of all the data and RAM used here.
Total size was only about 125GB and usually no more than about 1200-1500 databases were 'active' at any one time. It was a payroll processing company, and each of their clients had their own database, primarily for isolation purposes. The server was pretty low-end with dual quad core CPUs and 32GB RAM with 24 disks service up IO. Even Microsoft PSS and CAT team members were stunned when I told them about this baby I had. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply