August 24, 2010 at 3:30 am
Hello to all,
As mentionned in the subject, would you tell me what are the differencies (with details) between those technologies in term of performance, broadband and simplicity? Or it could be any documents/papers?
To make myself clear, for example, I found information telling that it is more complicated to setup and manage replication comparing to do the same thing for log-shopping. I would like to know on what criterion that statement is affirmed?
Thank you in advance for your help!
August 24, 2010 at 3:55 am
They are all different technologies that have overlaps, what exactly are you trying to achieve?
Also replication is no more difficult to setup/maintain than log-shipping infact the process is fairly similar
August 24, 2010 at 5:10 am
agnnga. (8/24/2010)
Hello to all,As mentionned in the subject, would you tell me what are the differencies (with details) between those technologies in term of performance, broadband and simplicity? Or it could be any documents/papers?
To make myself clear, for example, I found information telling that it is more complicated to setup and manage replication comparing to do the same thing for log-shopping. I would like to know on what criterion that statement is affirmed?
Thank you in advance for your help!
All of these technologies are detailed in BOL, check there for more info
steveb. (8/24/2010)
They are all different technologies that have overlaps, what exactly are you trying to achieve?Also replication is no more difficult to setup/maintain than log-shipping infact the process is fairly similar
Transactional replication involves setting up a distributor and creating publications and subscriptions, so is a lot more involved than log shipping!!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 24, 2010 at 5:15 am
maybe i am just jaded from setting up replication / log-shipping so many times. It seems like it is only a few more clicks of the mouse. But the planning / architecture is more complex..
August 24, 2010 at 6:31 am
More precisely, I am expecting to know which technology performing better and why? For example, it is said "Mirroring works faster than clustering" but there weren't any explanation. And how about the others?
@perry Whittle:
This must be one of my other stupid questions but, ... what is BOL and where can I find it, please? :blush:
Perry Whittle: Transactional replication involves setting up a distributor and creating publications and subscriptions, so is a lot more involved than log shipping!! ..
steveb. (8/24/2010)maybe i am just jaded from setting up replication / log-shipping so many times. It seems like it is only a few more clicks of the mouse. But the planning / architecture is more complex..
And about replication, I followed this tutorial and have never managed to make it work! "SQL Agent has never started" is what I got. Do you have any ideas?
http://www.asp-php.net/tutorial/sql-server/replication-sqlserver-2005.php?page=4 stuck from the end of step 3
and i also tried this : http://msdn.microsoft.com/en-us/library/aa337475.aspx
Thank you very much 🙂
August 24, 2010 at 6:33 am
have a look at your log reader agent job history, there should be an error in there. Most likely a permissions issue at a guess.
August 24, 2010 at 6:42 am
Strangely I logged in with the admin account.
But sure, you're righe, I'd better have a look at it again later (I removed it as it has never worked)
And how about the BOL, plz? ^^
August 24, 2010 at 7:04 am
agnnga (8/24/2010)
Hello to all,As mentionned in the subject, would you tell me what are the differencies (with details) between those technologies in term of performance, broadband and simplicity? Or it could be any documents/papers?
To make myself clear, for example, I found information telling that it is more complicated to setup and manage replication comparing to do the same thing for log-shopping. I would like to know on what criterion that statement is affirmed?
Thank you in advance for your help!
You really can't just compare these technologies as they have different niches that they support. While there is some overlap it isn't as "cut and dry" as that. So, probably best to read up on them. I will add a brief summary as follows;
Replication - Used for sharing data across multiple servers in a close to real time format and is typically used for a scale out scenario, environment consistency (prod / dev) and other scenarios. This is done on a per table (article) basis and requires three components, publisher, distributor and subscriber. Typically in very active environments they would be three distinct servers. This would not be a choice for high availability nor really for disaster recovery. Bandwidth required completely depends on how much data you are actually replicating.
Database Mirroring - Database mirroring is done at the database level and would typically be used for disaster recovery and consists of three components principal, witness and the mirror. The witness would be used to monitor the principal and if that goes offline it will bring up the mirror. Data can be sent in two methods, synchronously and asynchronously; the latter would allow for transactions to commit at the principal before they were committed at the mirror. Less consistent data recovery but allows for normal activity at the principal to continue pretty much normally. There are some costs associated with mirroring which you should read up on. http://technet.microsoft.com/en-us/library/cc917681.aspx There are some that use Database Mirroring as a High Availability solution but if there are any complexities in your environment I'm not sure that is the way to go. Additionally being that there is a cost with mirroring it seems to me that failover clustering is really the choice for HA and to leave mirroring as a DR solution.
Failover clustering is done at the instance level and is not a SQL Server technology but a windows technology and allows for true high availability. This requires minimally 2 servers that would share storage. The storage could be moved to either physical node so that in the event of a failure you have a quick transition to another set of hardware so that you can continue working. This technology is very solid at this point and SQL Server works really well in this solution for high availability.
As the other posters mentioned you really should read up on all three so that you can have a better understanding of what they do and where the strengths and weaknesses are. I do hope however that this helps a bit.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 24, 2010 at 2:34 pm
agnnga (8/24/2010)
it is said "Mirroring works faster than clustering" but there weren't any explanation.
This is not strictly true. Failover to the mirror can be slow, it depends on the time it takes the Redo actions to complete. Clustering, when implemented correctly can be seamless. One of my clusters had a mid afternoon failover recently and over 90% of the business had no idea there was an issue.
agnnga (8/24/2010)
@perry Whittle:This must be one of my other stupid questions but, ... what is BOL and where can I find it, please? :blush:
BOL means SQL Server books on line, it's generally installed with SQL server unless you deselect it!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 24, 2010 at 7:36 pm
The question you ask is what are these technologies.
These are solutions to the question: How to get the data that is in one server over to another?
Log shipping moves the data to another server by backing up the transaction logs from one server, copying the backup over to 1 or more servers and restoring it. This is accomplished typically with scripts and SQL Agent jobs. The currency of data is either minutes or hours old on the second server. The data being transferred is the entire database.
Clients connect to the first server will have to make manual adjustment to connect to the other server.
Transactional Replication is setup on a table by table basis. This allows you to transfer either 1 table or all tables in a database to 1 or more servers called subscribers. The currency of the data is as fast as the distributor can send it which can be in seconds. The data being transferred is on a table by table basis. Clients typically connect to either one or the other server.
Clustering is setup with more specialized hardware. This allows you to move all databases on a server to another server in the cluster including SQL Agent and all the jobs (which the other two do not) as well as any batch shares and drive letters. It is typically set up using SAN storage.
Clients connect to a Virtual named server so if one server goes down, the other server takes over the virtual name and clients are not the wiser.
Failover to another server for me has been less than 1 minute.
Database mirroring is in someways log shipping on steroids. Changes made to database are mirrored to another server. However, you can only mirror to 1 other server. When setup correctly, clients will connect to the one server and on failure, immediately reconnect to the other server.
August 25, 2010 at 2:10 am
Tim-153783 (8/24/2010)
When setup correctly, clients will connect to the one server and on failure, immediately reconnect to the other server.
Only if using ado.net or SQL native client can you use the connection string parameter "failover partner". You also have the latency of the Redo logs which can slow things down, failover wouldn't necessarily be immediate.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 26, 2010 at 5:47 pm
Hi,
As others have already mentioned, the technology you use is dependant on wht you are trying to achieve.
We use clustering for High Availability. Transactional replication to maintain an up to date reporting DB. Log shipping to maintain a 'warm' standby at our DR site.
I also looked at using mirroring instead of clustering for HA. Automatic failover using a witness was good and worked well with the OLTP DB but we still wanted to replicate to a reporting DB and the distribution DB can't be mirrored so it introduced a single point of failure.
Our current seup works quite well but it's all about using the right tools for the job.
Hope this helps.
Cheers
Andrew
August 26, 2010 at 5:55 pm
Perry, even using .net your failover might not be seamless if you are using connection pooling. Atleast that has been my experience.
August 27, 2010 at 2:15 am
Hello,
Thank you very much for your answers! 🙂
It's true that I have been reading only the principle of each technology and comparisons among them, like what Tim and David Benoit had mentionned . Sure I should be doing a lot more of deeper reading and analysis now! 😀
Otherwise, so as to make it clear, I have two different servers A and B.
A : - is getting information from 10 different local databases
- is updated each 5 minutes.
- has tables that don't have PK
B : - should be up-to-date at a minimum of time comparing to A
- can be requested by users.
- without data loss
- with high reliability
If I understood correctly what you've suggested, a technology that gives high performance is the one that satisties the maximum of criterions? And the throughput is dependant of the amount of data and the network itself?
Then, according to my analysis, log-shipping would be a good choice. What do you think?
I am looking forward to your suggestion.
Have a nice day!
August 27, 2010 at 4:11 pm
Andrew Fowler-367854 (8/26/2010)
Perry, even using .net your failover might not be seamless if you are using connection pooling. Atleast that has been my experience.
good point!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply