January 8, 2008 at 6:25 pm
Hello everyone.
I have been tasked with setting up a 3 node SQL 2005 cluster for our DBA's here. We are moving a lot of our Oracle stuff to SQL 2005, so I have a lot of things I am thinking about, such as planning, scalability, growth etc.
At this time, it will be a 3 node cluster, running dual CPU's and 8gigs memory in each box. The storage backend is going to be on a NetAPP filer over fibre channel.
I have been pouring of Microsofts website, and found a few articles on high availability, but nothing that is really definitive in my search on how to setup a proper SQL cluster.
I have several options in the cluster since we are going with Enterprise, such as database mirroring, log shipping and replication. We are having a meeting tomorrow to discuss a lot of questions including:
-how many DB's will be on the clustr?
-how many instances do we need running?
-what is the projected growth of logs and database files?
Those are a few.
In the meantime, does anyone have some articles or web sites that go into greater depth on setting up a SQL cluster? I have done several Exchange clusters, but this will be my first SQL cluster.
Any and all help is greatly appreciated.
Thanks.
JW
January 8, 2008 at 8:29 pm
I particularly like this article:
It details how to make sure you have redundant everything and explains how to seperate your cluster resources into the appropriate groups for maximum availability (and security).
It is based on 2000, but in terms of a cluster on Windows Server 2003, I don't think the setup should be very different. Well worth a read.
It's the same methodology I use when setting up a new SQL 2005 cluster, which is why I like it I guess 🙂
January 8, 2008 at 9:48 pm
and this little checklist I came across by accident moments ago is a nice summary:
http://www.sql-server-performance.com/articles/clustering/clustering_best_practices_p1.aspx
January 9, 2008 at 11:34 am
Thanks for the links. I really appreciate it.
I think we are going to do a full blown cluster for all SQL parts; database, reporting services (I know there is more, but can't recall off the top of my head.)
The servers I have in mind are dual socket XEON processors with 8gbs of memory. The connection to the backend storage piece will be over Fibre channel.
One of the things I was curious about is, do I need to be concerned with adding more horsepower? Perhaps more memory? more cpus? How do I determine what my needs are? I really don't know whether SQL prefers more CPU or more memory.
I'll look over those links.
Thanks.
Jason
January 9, 2008 at 12:14 pm
A few comments:
- since you're talking about involving 3 machines, I'm assuming you're talking 2 active and 1 passive, right? If so - you need to make sure that you scale each machine so that it's capable of running BOTH instances of SQL server. In that case - your dual-processor thought seems a little "underpowered". Figure out how much horsepower each instance might need (CPU+RAM), and combine them to come up with your hardware req for each node.
- alternatively - you might also consider an active/passive setup with log shipping or mirroring to the third machine. Since you've got all three - you might get some gains out of having both setups. (such as running SSRS on the "standalone" instance).
SQL Server will use pretty much any hardware you give to it, so if you're not sure, it's usually better to go higher, especially with regards to RAM (since it's so comparatively cheap these days). More processors will allow you to have more threads going concurrently, but also tend to have a direct correlation to licensing (per processor is a very common way to license, so that can get expensive quickly).
If you're going to go over 8GB - you will also want to leverage the 64-bit version instead of the 32-bit install.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 9, 2008 at 1:17 pm
With 3 nodes you are going for sql enterprise edition thus go for quad core processors as to save license cost, and quad cores allow more room for upgrade in future...
There are some very decent documents on microsoft site including testing it on virtual server if needed.
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
January 10, 2008 at 5:28 am
BOL indicates that SQL Server Reporting Services itself is not Microsoft cluster aware, so you cant install it as a cluster resource.
So what you have to do is create a cluster for the SQL Server instance, and then create the reporting services database on that instance. Then to make the actual SSRS websites have "failover", you will need to set up load balancing between two IIS servers.
This involves installing multiple instances of SSRS and then pointing them to the same ReportServer database. Instructions for how to do this can be found in BOL here: http://msdn2.microsoft.com/en-us/library/ms156453.aspx.
CodeMinkey
January 11, 2008 at 9:51 am
Appreciate all the feedback and recommendations. I still have a lot of research to do for this project.
Just some more info and questions:
I was going to use Windows Server 2003 Enterprise 64bit and SQL Server 2005 64 bit. The idea behind is that we currently have 8gigs of memory in each physical server, but we might up that to 16gigs in each server.
Any thoughts on that?
Also, is there any documents that have a step by step process for clustering SQL? I would just like to get a feel for it before i do it.
Reallly appreciate it.
Jason
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply