SQL 2000 - Reporting Services - High Availability, Scaleability
-
I am in the process of defining a clustering environment that uses MS SQL2000 and the Reporting Servces component and would like some feedback or recommendations on what I think I should be looking at.
Initially I have decided to provide High Avaialability by hosting the SQL2000 DB ontop of Windows2003 Advanced server running the MSDTC services. The clustered environment has SAN as the storage medium so I will be using this for the data and log partitions, but all other partitions will be on a local mirrored disk (including the temp).
Should I have a separate disk for the OS to that which I have the DB and is there a contention issue if the OS swap is on the same partition (physical disk set)as the tempdb area?
The SAN area will be shared between the 2 cluster nodes.
I have separated the IIS component onto a "web farm" type environment where i can scale out if necessary. The IIS servers would be communicating on a virtual IP on the cluster which talks to the active database component.
Is this solution the norm for a HA and scaleable solution for reporting services database ? Are there any links to references that would put me on a better direction than what I am thinking. Any help will be appreciated.
There are several sites which reference clusters , but not many that work with SQL 2000 and reporting services with the separation if on different servers in a scaleable solution.
** What you see, Depends on what you Thought, Before, You looked! **
-
I think you should have a read up on clustering !! Clusters use shared disks ( in your scenario a SAN ) you really don't want to put tempdb on the local disks. Binaries and o/s have to exist locally, are you planning an active/active or active/passive. swap files belong to the node - sql exists on a virtual node.
A cluster only provides hardware failover for the server, I wouldn't really describe a cluster as being High Availability , part of a solution maybe, what happens if your SAN fails? or if data is damaged?
Web servers are best placed sperately in a load balanced farm which either connect directly or via a middle tier to the sql virtual server. Technet contains info on clustering sql server.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Thank for your reply - It has raised more questions so here goes.
Can you explain a bit better why you recommend that the tempdb reside on the shared resources of the cluster. It is my understanding that the tempdb is only a working area which is totally destroyed(volitile) during any restart or stoping the server and does not contain data that needs to be available for any other purpose.
I probably did not explain clear enough about the location of storage, so I will attempt better here:
I understand that the OS should reside on a separate local mirrored pair.
I understand that in an ideal world the OS swap should reside on its own set of spindles (separate to the OS)
I understand that tempdb should be associated with a single node and separated from other data or log spaces used for normal database procedures. I was not aware it was recommended to be on a shared resource.
I want to know that if I had a mirrored local pair that this pair could host the OS swap and the tempdb partition without adverse effect? i.e. specifically..... does the tempdb utilize the OS swap or is the tempdb totally managed by the sql server engine and little use of the OS swap is actioned when tempdb functions are being utilized?
In regards to active/active or active/passive - My application that used the database is NOT "cluster aware" and relies heavily on DTS processes to import data to tables. The reporting services is the process that provides users with graphical data and reports. I understand that in an active/active scenario one node primararly will perform READS , while the other will concentrate on READ/WRITE transactions - Can Reporting services be configured to load balance against an active/active backend database and if so can someone please help me understand how and links if possible to materials which would help. I understand that in an active/active solution that I would be required to purchase 2 SQL server licences as opposed to 1 if running in active/passive.
In your message above you stated that the proposed solution was not High Available - do you have an example of how I would configure the environment to be Highly Available. My goal is to allow failure in multiple areas and yet keep the system available to the end users. I know I can perform Log shipping and SRDF type scenarios, but that is more of a DR environment (which I already have in place) so the HA part is what I realy want to concenttrate on.
** What you see, Depends on what you Thought, Before, You looked! **
-
I have done more research on the location of the Tempdb and it appears there are several recommendations for the area to be located on the shared resources, but none with a clear reason for doing such ,other than it will be less hassle when the system fails over.
Does anyone have any good links about how and recommendations to make a solution Highly available - Although my applications may be different to others , the general solution I would have thought be fairly commom place.
The most I am able to find on active/active vrs active/passive are all cursorary descriptions generally no longer than a paragraph of the high level description. Does anyone know of lower level descriptions (and with diagrams ... OK that is asking too much I know)
and links that would allow me to get a better undersatnding of the key benefits and primararly the critical concerns I should be aware of for either solution ?** What you see, Depends on what you Thought, Before, You looked! **
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply