March 6, 2007 at 2:57 am
Not sure this is the right forum for these questions.
I have been tasked into investigating a solution for failover and load balancing using Windows 2003 and SQL-Server 2005. There are 2 databases - one for reporting and another for data entry. The reporting database will be fed via replication data from the data entry database.
Am I correct in thinking that a SQL-Server 2005 Instance can only exist on a 2 node cluster (active/passive) ? If so, then this gives me my failover/high availablity solution but does not solve load balancing, and this is where my main question lies.
Is it right that to provide load balancing, in actual fact I will need to set up N 2 node clusters, log replicating ? The client software will need to be programmed in such a way to determine which cluster to use - hence balancing the load. Is there no active/active solution, or an automatic method to determine the load balancing ? I gather that the MS load balancing is only a randomize solution, no real balancing logic is involved.
I also will need to consider clustering the presentation layer (IIS), a Reporting layer (Reporting Services). I would assume that these are a little easy to configure.
Thanks in advance.
March 6, 2007 at 4:56 am
For the SQL cluster, configure two separate instances--one to run on each node. Mirror the data entry DB to the second instance, then schedule snapshots of the mirrored database (since it cannot be read directly). Use the snapshot to run reports.
Configuring SSRS behind a load-balancer (hardware or software) requires a bit of work--especially if you are unfamiliar with the workings of IIS or SSRS. Things to note:
- SSRS should be configured to use one of the SQL Cluster instances for its database
- Use Microsoft's documentation for a SSRS "scale-out" implementation
- Authentication can be a hassle--use Kerberos (see below) for pass-through (multi-hop) authentication, otherwise force IIS to only use NTLM
- Set the MachineKey to be the same for each node to prevent invalid viewstate errors
SSRS + Kerberos:
- Use its own Application Pool with a domain account for its identity
- Enable "account is trusted for delegation" option on the domain account
- Grant rights to this identity (aspnet_regiis -ga domain\identity)
- Enable "trust computer for delegation" on each computer object (SSRS, SQL nodes, etc.)
- Create host Service Principal Name for NLB: setspn -A host/my.report.server domain\identity
- Create MSSQLSvc SPNs for the SQL Instances (MSOLAPSvc.3 for SSAS)
- Use method #2 in KB896861 to prevent 401.1 errors
Don't forget about licensing: A load-balanced SSRS implementation requires SQL Enterprise Edition.
March 6, 2007 at 5:19 am
Thanks Ron, the Kerberos information is great.
I already have planned to replicate (it's not snapshot unfortunately has to be transaction as the data must be available immediately) from the data entry database to the reporting database.
If we ignore the data entry database for the moment, what is a good design for load balancing the reporting database ? Is it as I suspect that you have to create 2 sets of active/passive clusters, each with their own replicated reporting database, and the client end (or the load balancing mechanism) has to determine which server to go to. I guess if you follow my assumptions here, you could actually ignore the fact that there is clustering. Am I on the right tracks ?
March 6, 2007 at 7:27 am
I have not put IIS/SSRS on the SQL Cluster if that's what you are thinking. I used separate machines (VMWare in fact) behind a hardware load balancer. I have not done this, but you could use Windows NLB to create the Web Farm.
The two (or more) SSRS nodes point to the same configuration database on an instance in the SQL cluster. This will provide fail-over capability. There is no need to load balance the SSRS database itself.
If you can, go with x64 for SSRS. There is a difference in performance--particularly related to the time between each drop-down parameter.
March 6, 2007 at 7:52 am
I wasn't thinking of putting IIS/SSRS with SQL-Server - they will be on their own clusters.
I was more interested in what to do with SQL-Server. My understanding is that for SQL you can only have 2 servers per instance - active/passive. I think this means that you can not load balance an instance - it is restricted to run on a single node. Perhaps one way round this is to use more than one instance and then enable the client to chose which instance to use. I guess this is where my question lies - is there a configuration tool to decide this, or is this something I have to code for in the client.
March 6, 2007 at 10:05 am
In a 2-node SQL cluster, you can have multiple instances of SQL running. Ie:
- CLUSTER\INST1 (runs on NODE-A)
- Data entry DB
- Report Server DB
- CLUSTER\INST2 (runs on NODE-B)
- Mirrored copy of Data entry DB
There is no software to configure to load-balance an instance. It can only live on one node at a time. The clients connect using the virtual server name (CLUSTER\INSTn).
March 6, 2007 at 10:28 am
Ah OK. I thought that Node B was always passive, if you wanted to run another instance on another server you had to have another node (to achieve some sort of load balancing).
Following that thought, I assume that the IIS/SSRS will need to make a decision at runtime as to which instance they are to connect to. That would then load balance across 2 servers, although the choke point is then the shared storage. It may be more sensible to have 2 separate clusters so that they are completely physically separate.
My thought here is to have 2 instances to hold the reporting database. These instance will be replicated from the data entry database so will be identical. The IIS/SSRS will determine which "virtual server" to connect to at runtime, in theory balancing the load.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply