SQL Server 2005 Clustering & Replication | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SQL Server 2005 Clustering & Replication

Just curious about subject above, we would like to have a sql server 2005 into os windows 2003. We are developing a new application. The goal is 1- We want to develop the sql clustering over windows 2003 enterprise edition. 2- Surely the active/active clustering – Is it possible for me to do clustering over windows 2003 ent. Edition? – What is the best way in this case to have clustering or replication ? – Why to chose 2nd question ? – Why? What? And HELP ME ASAP…………. Thank you.. Raydha.

quote:Originally posted by raydha Just curious about subject above, we would like to have a sql server 2005 into os windows 2003. We are developing a new application. The goal is 1- We want to develop the sql clustering over windows 2003 enterprise edition. 2- Surely the active/active clustering
Note that active/active has nothing to do with data sharing. Microsoft clustering is a failover cluster.
quote:
– Is it possible for me to do clustering over windows 2003 ent. Edition?
Yes
quote:
– What is the best way in this case to have clustering or replication ?
It’s not a question about one or the other. You can have both clustering and replication. They both solve different problems.
quote:
– Why to chose 2nd question ?
I think you might be assuming that Microsoft SQL clustering is about data sharing. It is not. In an active/active cluster you have one or more seperate instances with their own data running on either of the nodes.
quote:
– Why? What? And HELP ME ASAP………….
Here are some links that might be useful:
http://www.sql-server-performance.com/clustering_resources.asp
http://www.sql-server-performance.com/articles_clustering_log_shipping.asp
thanks Argyle, maybe that answers, answered the question. but now im more confusing on SQL Clustering and Windows 2003 Ent. Clustering. 1- both can cluster[SQL & Windows 2003 Ent.], but what the differ and can i both configured to cluster?
2- if im doing windows 2003 clustering, should i do so to sql server 2005? why? is it both same ? arghhh
3- load balancing already configure in router, and here what im thinking.
– router (load balancing)
– windows 2003 (clustering)
– sql 2005 (mirroring)
is it here the best configuration??? since we need the high perfomance app. and ability to handle greater load, we have provide the best hardware and now for the software.
can someone help me out here?? thanks to all…
Why not take advantage of database mirroring whereby the high availability is not compromised. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
What do you want to balance? Load on mssql server? If so, you can’t do it unlesss database is read only (AFAIK). Each data modification has to be recorded in each mirror database. It can be done by writting data in each database in the same transaction or writting into one primary db and replicating to others. I don’t know if there is load balancing solution supporting either solution. However, I’ve heard (never did it myself) for load balanced dw implementations. The solution was that during ETL process only primary server was available to the application (active). After mirror server was synchronized, both server were accessable, since all clients had just read-only access. I don’t know if it is your case and I don’t guarantee that the story mentioned is true. It would be nice if someone who implemented something similar shares his experience.
Hi Satya – all of the Microsoft literature that I have come across states that database mirroring is not yet ready for a production environment and should be sometime in the first half of this year. Do you agree with that or know why it isn’t ready for production environments? One article I read mentioned that it was because MS doesn’t have enough production implementations to prove its readiness….
quote:Originally posted by satya Why not take advantage of database mirroring whereby the high availability is not compromised. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Yes it is disabled by default, but still you can take advantage and test it before deploying on the production system. http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx fyi.
you can use SQL Express as a witness server and you need only license for the primary server and if you use secondary server for reporting purpose then license is required here, otherwise it is not required. For witness server no need to pay as it is only a SQL instance sitting like duck. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Some quick thoughts… You can cluster SQL 2005 Standard Edition up to 2 nodes. Microsoft SQL Server does not load balance. You can do things in your app to balance the load, but nothing within SQL is load sharing (as is possible with Oracle RAC) You configure a Windows cluster and then can put cluster aware applications on it, be it SQL or a custom app. The Windows cluster simply allows a virtual OS to bounce between servers. With Windows and SQL clusting, you can withstand server hardware and OS failures. You cannot withstand failures at the shared storage level or SQL data level. SQL 2005 mirroring is a primary/standby setup, with only one database able to take updates. As Satya states, you can enable mirroring, but don’t expect Microsoft to give you any support for it just yet. With Mirroring, you can withstand failures like data drive disk failure or corruption. With Mirroring, you are syncronizing everything in the database. With Replication, you can replicate just portions of a DB. Many DBAs use it for reporting on only specific tables. If you go with SQL 2005 Enterprise Edition, you could also look at database snapshots for reporting or recovery from accidential deletes, etc. One of the biggest questions for each is WHY do you need it? I’m sure MS would love you to use SQL 2005 Enterprise Edition clustering and mirroring, but make sure the technology matches up with your requirements…
]]>