Clustering, mirroring or replication?
-
Hi,
I find it difficult to figure out how design my redundant databases. I have two servers, both are running some custom applications and one instance of SQL Server 2005 each. On the client, I can select which server to work with. If one server fails the client will fail over manually or automatically to the other server. Therefore, I need to keep the data on the two servers synchronized. I tried merge replication, but there was a latency of about 2-5 seconds, which I cannot afford. If one server goes down it could leave the other one with a different set of data which will make the client behave differently after the failover.
I also looked at database mirroring. I haven't tried it yet but as far as I understood, it uses the instance on the second server as a standby instance. I guess there is some latency here as well? Also, I dont want the applications on the server to use the instance on the other server. All applications on the server must use only the local instance.
I'm thinking of clustering, but I have no experience in that.
My goal is to have two SQL Server 2005 running completely synchronized. What is the best solution for that? Actually, I'm not interested in failover on the SQL Server level since that is taken care of by other applications, I'm only interested in making sure that the data are exactly the same on both servers at any given time.
-
Hi,
replication is not a viable solution for redundancy in this case.
Mirroring is an option. It is easier and cheaper to set up than clustering and can offer complete redundancy of data.
When you set up mirroring you can set the transaction safety to FULL (high-protection mode). This ensures that transactions on the principal server are only commited when the same transaction has been commited on the secondary server - thus ensuring that both databases have the same data. You pay a performance price for this though given that each transaction has to wait for confirmation from the secondary server.
Clustering, as you pointed out, is the other option. This is more expensive and not as easy to set up. You need very specific hardware that appears on the Microsoft hardware compatibility list for clusters. You'd set things up in an Active-Passive configuration and one server is offline. If the primary server goes offline the secondary server takes control of the disks and no one knows any different. In this instance there is no flow of data between the two servers. The two servers share disks and only one server has control of them at any one time.
The subject is a lot more detailed than I have chosen to go into so I would recommend you read up in BOL on these subjects first.
Hope that helps,
-
Cluster could be the answer to your problems.
It’s not hard to implement MS SQL Server cluster solution you just have to pay attention to all the steps. It’s not rocket science.
When I do my first cluster it took me the all night to mount a MS SQL Server 7 cluster solution, but now it take a couple of hours only depending on the number of the named instances.
In MS SQL Server 2005 you may use the mirroring solution that is another way to do the cluster.
Feel free to contact me,
PaL
email:pal_soft@hotmail.com
www: will be available soon
-
the question should be mirroring or replication - clustering does not provide any redundacy other than for server hardware ( excluding storage ) the disk storage is still a single point of failure. Don't get the technologies confused.
You can't just replicate a database, your database has to be replication friendly!! Mirroring has several variations most are not comaptible with clustered hardware btw.
There will always be some latency.
Mirroring provides single database failover ( e.g. one database can failover but the others keep running ) - it has to be to another server you can't fail over to a failed server so for any DR situation you need at least duplicated hardware - and preferably in a geographically seperated location. SQL 2005 peer to peer replication has some nice features.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thanks for the feedback!
I have already been reading a lot on mirroring and I'm just about to test it to find out some more. It looks like I could use mirroring with High Availabilty, but I still haven't found any information about how mirroring behaves if you disconnect and reconnects one of the servers. Will the active server update the reconnected server with all changes or only new ones? I will find out soon
I also read:
"Despite the close coordination between principal and mirror when safety is FULL, database mirroring is not a distributed transaction and does not use a two-phase commit."
...
"Most importantly, unlike a distributed transaction, failures to commit on the mirror will not cause a transaction rollback on the principal."
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
Of course this is good in most cases. If one server crash I dont want the other one to stop, but if for some reason a transaction failes to commit on the mirror (timeout, bug...) the two servers will not be synchronized any more.
Well, there is work to be done
I'll have a look at peer to peer replication as well. -
There's never really one whole answer, you have to make decisions always. Only a two phase commit will guarantee the transaction, however, this is only one part of "protection" for although this would give you uptime without data loss it would not protect you against accidental or malicious data loss. Likewise with peer to peer. It's all about choices and how much money you can afford!! or how much data you can afford to lose.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hi,
Thanks for all the feedback! I have been struggling with setting up the redundant network so I haven't had time to test mirroring. Well, I started testing it but got all sorts of error messages, so I'll continue after the weekend. I had a redundant network with two network adapters on different subnets (default configuration for another application) but I was not able to set up replication in SQL Server to use the two networks with some sort of failover, so now I teamed the NICs on each computer instead.
I hope to get back with some results next week. Have a great weekend!
-
Mirroring requires sql server 2005 service pack I to be used in production. You mentioned the comprehensive article at the link below.
" no more than 2-5 seconds latency " I must be living in a dream world because I would think five minutes lost data would be fantastic in a failover situation.
We are considering mirroring as either a replacement or addition to log shipping we currently have in our sql 2005 enterprise cluster environment. The cluster only solves the problem of the sql server hardware failing -- it will fail over to the other node of the cluster. Our data files are on a SAN which is in effect a "single point of failure" but as expensive as those things are they should almost never fail completely.
Log shipping seems to have one advantage in that you can have the trans log backups restore after a delay time, which in the event of a major incorrect data update or deletion, would allow you to shut down the restore job and use the standby prior to it receiving the unwanted transactions.
http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
-
I would suggest mirroring as an addition, not replacement, to log shipping. The two technologies are best suited for different purposes.
Mirroring gives you very fast failover, automatic if you want it, manual if you don't. However, mirroring can only be accomplished at a single (presumably remote location), so your backup redundancy is limited to one backup. Also, mirroring simply copies everything that happens to the principal database. So there's no "roll-back" ability here to obtain the database in an earlier state.
Log shipping can be used in addition to mirroring to maintain several geographically seperated backup copies of the database. Also, log shipping gives you the opportunity to maintain "archive" copies of the database. These can be useful to recover the database to a point in time before the error or accident occurred.
In our situation, we are getting recovery in 3 seconds upon an automatic failover event.
HTH
Elliott
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply