September 9, 2008 at 11:48 am
Hi All,
I need some help how can I start working on SQL Server 2005 clustering, I have 3 servers running with SQL databases, they are almost 15 databases...we are growing, and I need to set up those servers as a clusters in our DR site.
they 3 servers are windows 2003, with SQL Server 2005 EE.
Would you mind giving me some guide on this issue...I found many postings over here, but most of them starts, but has no end, and I remain with the same doubts....
Please could anybody help me?
Thank you.
September 9, 2008 at 12:43 pm
Are you sure you want clustering? You mentioned that the servers are in a DR site, and that usually means "disaster recovery" (at least to me). That's not what clustering is for.
Clustering is usually for high-transaction and/or huge databases.
If what you're looking for is disaster recovery, you either want mirroring, replication, or log shipping (or some combination of those), not clustering.
So, before we go any further, are you sure clustering is what you need to know about?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 10, 2008 at 7:59 am
Thank you for that, I was reading about mirroring for our DR site, but my manager is asking me for clustering....in this case, which will be the differences betwwen both, to show him the adventage and disadvantage, I have to support what I will be doing...and that is way I get a little confusing how clustering works?
Would you mind giving me some guide on this please?... I have looking for some information but the posting I found here didn't have any asnwer.
Please any help.
Thank you.
September 10, 2008 at 11:54 am
If you look at the data on failover clustering in Books Online/MSDN, you'll note that there is a long list of requirements. For one, the disk drives have to be shared. If your failover server is on a different network at a different location, I'm not sure how you would do that effectively.
Setting up clustering has special hardware and software requirements. There's a long list of these in BOL/MSDN.
Mirroring has much fewer requirements and is much easier to set up.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 10, 2008 at 1:43 pm
As said configuring and maintaining cluster is more complicated as compared to mirroring.
Benefits to Mirroring over Clustering
Cost (cheaper h/w, licenses)
No HCL requirements
Distance (you can have a server in another country)
Less complex than clustered so less expertise
Faster failover 2 seconds vs 10 seconds (or whatever)
Both are different altogether:
From googling:
If cost is not a factor, you may consider both because they protect
different aspects of your database systems (if there is a business
requirement). Clustering is mostly for protecting against system failure
(e.g. motherboard), and mirroring is mainly for storage hardware and certain database corruption as well as site failure. Though you can use mirroring for protecting against system failure, clustering gives you faster and safer recovery with lower overhead. Any time your failover involves data, it's always risky. Cluster failure doesn't involve data in the database.
HTH
MJ
September 10, 2008 at 2:59 pm
When a cluster fails over the servername/ip address remains the same and although any connections to the cluster will have to reconnect after the failover, they just connect as they would normally. With mirroring there has to be a DNS or application level switch to the second server in the event of a failover - this can be set up without requiring user intervention at the time of failover but needs to be thought about.
September 10, 2008 at 3:49 pm
To address the problem mentioned by DNA:
Client-side Redirect
In SQL Server 2005, if you connect to a database that is being mirrored with ADO.NET or the SQL Native Client, your application can take advantage of the drivers' ability to automatically redirect connections when a database mirroring failover occurs. You must specify the initial principal server and database in the connection string, and optionally the failover partner server.
There are many ways to write the connection string, but here is one example, specifying server A as the principal, server B as the mirror, and AdventureWorks as the database name:
"Data Source=A;Failover Partner=B;Initial Catalog=AdventureWorks;Integrated Security=True;"
The failover partner in the connection string is used as an alternate server name if the connection to the initial principal server fails. If the connection to the initial principal server succeeds, then the failover partner name will not be used, but the driver will store the failover partner name that it retrieves from the principal server on the client-side cache.
Read: http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx
MJ
September 10, 2008 at 7:08 pm
As a DBA you may have jobs setup to maintain DB's but the hardware cannot be ignored. In the event of a hardware failure Clustering comes to the rescue. Also the failover is fast without user intervention. Unless you have an OLTP system with millions of transactions going on (which would ask for a mirror of the critical databases with a hot standby) I would go for Clustering. But before going on for Clustering, study the complexities involved in it, its not so simple as defining end points and giving the locations for principal and mirror databases.
Hope this helps...Good Luck Junior 🙂
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
September 11, 2008 at 7:37 am
Junior, you really, really need to get a consultant in for a day or two to explain to you what types of HA are available in SQL Server and to review your needs and guide you to the most appropriate solution for your particular set of circumstances/needs. You could then bring said person back to assist in implementing the chosen plan. Given your lack of experience and training there is very little chance that you can do the right things for your company here. Not a personal condemnation there, just a (very experienced) observation. 🙂 Best of luck with your endeavour!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
September 11, 2008 at 11:39 am
TheSQLGuru does have a point. If you go with clustering, it's complex and easy to get wrong. Mirroring and log shipping are pretty easy to set up if you just follow the directions in Books Online/MSDN. Clustering, not so easy.
And the right way to learn about something complex isn't to try it out and hope it doesn't explode too badly.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply