Question About Scalability of Reporting Services

We use SQL Server 2000 Reporting Services to represent reports for our company.

Our boss complained to me about the poor performance of Reporting Services when he viewed reports. He suspected that RS Server is a single-thread server and can not be scaled up by adding more CPUs or memories to increase the reports process speed.

Is my boss correct?

And how can we increase the performance of Reporting Servces?

Traditional scale up can resolve the issue?

Appreciate your precious reply.

Your boss is not correct :)

We will scale up with more CPUs. Most of Reporting Services is managed code, so we are subject to whatever memory constraints are in place by the CLR. So on 32-bit machines we will use up to 2GB of memory, and we will use more on 64-bit platforms.

You can check the execution log to see which reports in particular are taking a long time to execute and what phases of those executions are long running:

http://msdn2.microsoft.com/en-us/library/ms159110.aspx

See Tudor's blog entry on performance here as well:

http://blogs.msdn.com/tudortr/archive/2004/06/28/167969.aspx

It has good tips for optimizing a poorly performing report.

|||

Excellent!

Thanks alot!

Question about Parameter

Hi,
I need to design a Sales Order (SO) report for my company. In the SO
report, I need to print out the company name, company address, Telephone no.
... etc in report header. For this design, it serve 5 sub-company under my
company group. Therefore, I store the company data in a table - name
CompanyInfo. Everytime I need to select correct company name to print it out.
But I know that it cannot print "Field" in report header in RS. I try to
put it into parameter and print it out. It OK but all of the parameter will
be show to user. For this point, I don't want our user to see such
information.
Any idea for me?
Thank You!
TonyYou can have multiple datasets in a report. If you put a list at the top of
the report and your query has only one record, then the data will look for
all intents and purposes as the report header. Then following that have your
table or matrix or whatever. I do this all the time and it looks exactly the
same.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> Hi,
> I need to design a Sales Order (SO) report for my company. In the SO
> report, I need to print out the company name, company address, Telephone
> no.
> ... etc in report header. For this design, it serve 5 sub-company under
> my
> company group. Therefore, I store the company data in a table - name
> CompanyInfo. Everytime I need to select correct company name to print it
> out.
> But I know that it cannot print "Field" in report header in RS. I try to
> put it into parameter and print it out. It OK but all of the parameter
> will
> be show to user. For this point, I don't want our user to see such
> information.
> Any idea for me?
> Thank You!
> Tony
>|||Hi Bruce,
Thank you for your reply!
I already try this way and it look OK. But all parameters will be shown at
the top of the report! It looks no good! Can I hidden the parameter? It is
because there are 6 parameters for such report header.
Thank You!
Tony
"Bruce L-C [MVP]" wrote:
> You can have multiple datasets in a report. If you put a list at the top of
> the report and your query has only one record, then the data will look for
> all intents and purposes as the report header. Then following that have your
> table or matrix or whatever. I do this all the time and it looks exactly the
> same.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> > Hi,
> >
> > I need to design a Sales Order (SO) report for my company. In the SO
> > report, I need to print out the company name, company address, Telephone
> > no.
> > ... etc in report header. For this design, it serve 5 sub-company under
> > my
> > company group. Therefore, I store the company data in a table - name
> > CompanyInfo. Everytime I need to select correct company name to print it
> > out.
> >
> > But I know that it cannot print "Field" in report header in RS. I try to
> > put it into parameter and print it out. It OK but all of the parameter
> > will
> > be show to user. For this point, I don't want our user to see such
> > information.
> >
> > Any idea for me?
> > Thank You!
> >
> > Tony
> >
> >
>
>|||You can have hidden parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:7FABFC55-8149-4484-9C80-AD70FC318BCF@.microsoft.com...
> Hi Bruce,
> Thank you for your reply!
> I already try this way and it look OK. But all parameters will be shown
> at
> the top of the report! It looks no good! Can I hidden the parameter? It
> is
> because there are 6 parameters for such report header.
> Thank You!
> Tony
>
> "Bruce L-C [MVP]" wrote:
>> You can have multiple datasets in a report. If you put a list at the top
>> of
>> the report and your query has only one record, then the data will look
>> for
>> all intents and purposes as the report header. Then following that have
>> your
>> table or matrix or whatever. I do this all the time and it looks exactly
>> the
>> same.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
>> > Hi,
>> >
>> > I need to design a Sales Order (SO) report for my company. In the SO
>> > report, I need to print out the company name, company address,
>> > Telephone
>> > no.
>> > ... etc in report header. For this design, it serve 5 sub-company
>> > under
>> > my
>> > company group. Therefore, I store the company data in a table - name
>> > CompanyInfo. Everytime I need to select correct company name to print
>> > it
>> > out.
>> >
>> > But I know that it cannot print "Field" in report header in RS. I try
>> > to
>> > put it into parameter and print it out. It OK but all of the parameter
>> > will
>> > be show to user. For this point, I don't want our user to see such
>> > information.
>> >
>> > Any idea for me?
>> > Thank You!
>> >
>> > Tony
>> >
>> >
>>|||Thank You! I try it!
"Bruce L-C [MVP]" wrote:
> You can have hidden parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:7FABFC55-8149-4484-9C80-AD70FC318BCF@.microsoft.com...
> > Hi Bruce,
> >
> > Thank you for your reply!
> >
> > I already try this way and it look OK. But all parameters will be shown
> > at
> > the top of the report! It looks no good! Can I hidden the parameter? It
> > is
> > because there are 6 parameters for such report header.
> >
> > Thank You!
> > Tony
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You can have multiple datasets in a report. If you put a list at the top
> >> of
> >> the report and your query has only one record, then the data will look
> >> for
> >> all intents and purposes as the report header. Then following that have
> >> your
> >> table or matrix or whatever. I do this all the time and it looks exactly
> >> the
> >> same.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> >> > Hi,
> >> >
> >> > I need to design a Sales Order (SO) report for my company. In the SO
> >> > report, I need to print out the company name, company address,
> >> > Telephone
> >> > no.
> >> > ... etc in report header. For this design, it serve 5 sub-company
> >> > under
> >> > my
> >> > company group. Therefore, I store the company data in a table - name
> >> > CompanyInfo. Everytime I need to select correct company name to print
> >> > it
> >> > out.
> >> >
> >> > But I know that it cannot print "Field" in report header in RS. I try
> >> > to
> >> > put it into parameter and print it out. It OK but all of the parameter
> >> > will
> >> > be show to user. For this point, I don't want our user to see such
> >> > information.
> >> >
> >> > Any idea for me?
> >> > Thank You!
> >> >
> >> > Tony
> >> >
> >> >
> >>
> >>
> >>
>
>

Question about Database Mirroring and Possibly NLB

OK since my company really would not prefer to spend the money for Windows 2003 Enterprise and MS SQL 2005 Enterprise I was thinking about this idea...

Server1 - W2K3 Std - SQL2005 Std - Primary
Server2 - W2K3 Std - SQL2005 Std - Mirror
Server3 - W2K3 Std - SQL2005 Std - Witness
Server4 - W2K3 Std - SQL2005 Std - Processing

OK So we have Server1 & Server2 doing the Database Mirroring. Then I have Server3 as the Witness to say who is the Primary and so forth. Now Server4 I thinking to us to create jobs that will run on a Schedule to pull data from our Ticketing system and import it into Server1 & Server2 (Yes this is a Datawarehouse).. So what I am wondering... We really wish to not have to change our connections to the DB with the Failover connection string and just use a common name. Since we are not usng Enterprise I can not do the Failover Clustering... So what I was wondering is if we could use the MS NLB. So for example with Server1 being primary and Server2 being Mirror... Server1 dies or goes down for maintance or whatever... Then Server3 will notice that and say OK Server2 you are now primary but also have Server3 go into the MS NLB and say Server3 Online and take all connections. Then when Server1 comes back on-line it is set as the Mirror system and is Offline in the NLB... Is this possible? If yes can someone either A... Give me directions on how to do that... or B shot me some URL's where I can read up on this stuff?

Thanks for all your help anyone is able to provide. It is greatly appricated.

Billy S.

1. You can build a 2 node cluster with the Standard edition

2. You don't need to deploy Standard edition for the witness, it can be Express Edition if you choose

3. Your imports would go to Server1. You can not write to a mirror.

4. Yes, you can combine this with NLB if you really want to.

|||

1. How do you setup a Cluster in Windows 2003 Server Standard? To my understanding it wont allow you to create a cluster on the Standard version... Can you give me a URL for that?

2. Yeah I understand that... but was noticing you cant really schedule any jobs via Express edition... Unless I am confused about that also.

3. Wouldn't my imports go to the Virtual name being which ever SQL Server is the Primary? Cause say Server1 dies and Server2 becomes primary... Wouldn't I want the updates to then to go Server2?

4. Do you happen to know where some documentation is on this concept?

Thanks again for all the help.

Billy S.

|||

What I believe Michael was referring to is SQL Server Standard Edition will allow you to create a 2 node cluster. I believe that you will still need to purchase Windows 2003 Server Enterprise Edition to cluster the two servers together at the OS level.

There are 3rd party products that allow you to do failover clustering similar to Microsoft Clustering that allow Windows 2003 Server Standard edition as the OS.

Hope this helps clarify things.

Drew

|||

What about on the Witness when it goes to Set a server to be the primary... can it also execute another command that will send a command to either a HW NLB or the MS NLB to set which ever server Online and the other server offline?

I would try and do it now but I need to wait some time first and get our VM server in before I can test anything... But manager is wanting me to say yes or no on this method and exactly what versions we will need to purchase for both the OS & SQL parts... So... Is there some place I can read about what the Witness can do when it does do a failover and set say Server2 to be primary and Server1 as mirror... like say run server2prim.bat... Just trying to get this figured out the best I can while they still have the purse open :)

|||

Actually, if you're operating in full sync mode (full safety), mirroring should do everything you need.

DBM can handle the redirection when there is a principal transition. You use a little different connection string which names both principal and partner. On connection, the app will automatically try the partner if the principal does not respond.

|||Yes... We can use the Failover Partner in the ODBC type connections... However some of the apps I design/manage/run use Perl and I do a direct connection not looking at ODBC to a single server... So... How would I do that without using the Failover peice in the connection string and not use the Microsoft Clustering Service? That is why I was looking at NLB...You have 2 machines in a NLB setup.. 1 system is set "Online" and the Secondary (Mirror Server) is set "Offline" now is there anyways on the Witness server to tell it when Primary server dies and it not available after you tell the Mirror server to become the primary run this command or batch job which will send a request to the NLB device or app saying Server1 is "Offline" and Server2 is "Online"... Does this make any sense? Or is what I am wanting to do just not doable period?|||

1. Windows Server 2003 STANDARD edition will do a 2 node cluster. That is very clearly documented in the feature set and there are tens of thousands of clusters out there running under Windows Server 2003 Standard edition. SQL Server 2005 Standard edition will also support a 2 node cluster.

2. If you are putting a load balancer in front of this, there is NOTHING that you need to do. The load balancer gives you a virtual IP for everything you put behind the LB. You hit the virtual IP exposed by the load balancer. The load balancer takes care of routing the traffic to the proper machine on the back end. Your applications are oblivious to any of this since they connect to the VIP and NOT the actual machines behind the load balancer. You don't point anything at all at the witness. The sole purpose of the witness is to enable automatic failover. How does traffic get re-routed by the load balancer? Exactly the same way that a load balancer routes all traffic. It detects that a resource is unavailable and then it automatically routes traffic to the resources that are available. By putting a witness in here, YOU are not telling the mirroring session to failover, the software does this automatically. If you don't put a witness in there to have automatic failover, then putting all of this behind a load balancer would be completely pointless in the first place.

3. You can NOT combine Windows Load Balancing and Clustering. They are incompatible.

|||

1) Michael, I know you as an extremely knowledgeable person, so I'm finding a hard time pointing out, that you seems to be off track. You cannot cluster on Windows Standard edition: http://www.microsoft.com/technet/windowsserver/evaluate/features/compare.mspx#64Clustering

Or at least, can you point me to a place in which Microsoft states that you can cluster on Windows 2003 Standard Edition?

Yours

Kim

|||

Michael,

I like the Idea of using NLB with 2005 Database mirroring since our DB's guy's will not have to rewrite all of their existing code, however I'm concerned about what would happen in the event of the SQL service stopping. I understand the witness will auto-magically failover the SQL resources but how do you instruct NLB to look for that same condition and redirect traffic accordingly?

Rob

|||That is done in the NLB configuration. You setup NLB with health checks that handle the traffic direction. Simply put, when the code in the NLB configuration can not connect to the SQL Server, it will direct connections to the alternate IP address that it is configured with. It behaves no differently than if you had a web server farm or app server farm behind NLB.

Question about Database Mirroring and Possibly NLB

OK since my company really would not prefer to spend the money for Windows 2003 Enterprise and MS SQL 2005 Enterprise I was thinking about this idea...

Server1 - W2K3 Std - SQL2005 Std - Primary
Server2 - W2K3 Std - SQL2005 Std - Mirror
Server3 - W2K3 Std - SQL2005 Std - Witness
Server4 - W2K3 Std - SQL2005 Std - Processing

OK So we have Server1 & Server2 doing the Database Mirroring. Then I have Server3 as the Witness to say who is the Primary and so forth. Now Server4 I thinking to us to create jobs that will run on a Schedule to pull data from our Ticketing system and import it into Server1 & Server2 (Yes this is a Datawarehouse).. So what I am wondering... We really wish to not have to change our connections to the DB with the Failover connection string and just use a common name. Since we are not usng Enterprise I can not do the Failover Clustering... So what I was wondering is if we could use the MS NLB. So for example with Server1 being primary and Server2 being Mirror... Server1 dies or goes down for maintance or whatever... Then Server3 will notice that and say OK Server2 you are now primary but also have Server3 go into the MS NLB and say Server3 Online and take all connections. Then when Server1 comes back on-line it is set as the Mirror system and is Offline in the NLB... Is this possible? If yes can someone either A... Give me directions on how to do that... or B shot me some URL's where I can read up on this stuff?

Thanks for all your help anyone is able to provide. It is greatly appricated.

Billy S.

1. You can build a 2 node cluster with the Standard edition

2. You don't need to deploy Standard edition for the witness, it can be Express Edition if you choose

3. Your imports would go to Server1. You can not write to a mirror.

4. Yes, you can combine this with NLB if you really want to.

|||

1. How do you setup a Cluster in Windows 2003 Server Standard? To my understanding it wont allow you to create a cluster on the Standard version... Can you give me a URL for that?

2. Yeah I understand that... but was noticing you cant really schedule any jobs via Express edition... Unless I am confused about that also.

3. Wouldn't my imports go to the Virtual name being which ever SQL Server is the Primary? Cause say Server1 dies and Server2 becomes primary... Wouldn't I want the updates to then to go Server2?

4. Do you happen to know where some documentation is on this concept?

Thanks again for all the help.

Billy S.

|||

What I believe Michael was referring to is SQL Server Standard Edition will allow you to create a 2 node cluster. I believe that you will still need to purchase Windows 2003 Server Enterprise Edition to cluster the two servers together at the OS level.

There are 3rd party products that allow you to do failover clustering similar to Microsoft Clustering that allow Windows 2003 Server Standard edition as the OS.

Hope this helps clarify things.

Drew

|||

What about on the Witness when it goes to Set a server to be the primary... can it also execute another command that will send a command to either a HW NLB or the MS NLB to set which ever server Online and the other server offline?

I would try and do it now but I need to wait some time first and get our VM server in before I can test anything... But manager is wanting me to say yes or no on this method and exactly what versions we will need to purchase for both the OS & SQL parts... So... Is there some place I can read about what the Witness can do when it does do a failover and set say Server2 to be primary and Server1 as mirror... like say run server2prim.bat... Just trying to get this figured out the best I can while they still have the purse open :)

|||

Actually, if you're operating in full sync mode (full safety), mirroring should do everything you need.

DBM can handle the redirection when there is a principal transition. You use a little different connection string which names both principal and partner. On connection, the app will automatically try the partner if the principal does not respond.

|||Yes... We can use the Failover Partner in the ODBC type connections... However some of the apps I design/manage/run use Perl and I do a direct connection not looking at ODBC to a single server... So... How would I do that without using the Failover peice in the connection string and not use the Microsoft Clustering Service? That is why I was looking at NLB...You have 2 machines in a NLB setup.. 1 system is set "Online" and the Secondary (Mirror Server) is set "Offline" now is there anyways on the Witness server to tell it when Primary server dies and it not available after you tell the Mirror server to become the primary run this command or batch job which will send a request to the NLB device or app saying Server1 is "Offline" and Server2 is "Online"... Does this make any sense? Or is what I am wanting to do just not doable period?|||

1. Windows Server 2003 STANDARD edition will do a 2 node cluster. That is very clearly documented in the feature set and there are tens of thousands of clusters out there running under Windows Server 2003 Standard edition. SQL Server 2005 Standard edition will also support a 2 node cluster.

2. If you are putting a load balancer in front of this, there is NOTHING that you need to do. The load balancer gives you a virtual IP for everything you put behind the LB. You hit the virtual IP exposed by the load balancer. The load balancer takes care of routing the traffic to the proper machine on the back end. Your applications are oblivious to any of this since they connect to the VIP and NOT the actual machines behind the load balancer. You don't point anything at all at the witness. The sole purpose of the witness is to enable automatic failover. How does traffic get re-routed by the load balancer? Exactly the same way that a load balancer routes all traffic. It detects that a resource is unavailable and then it automatically routes traffic to the resources that are available. By putting a witness in here, YOU are not telling the mirroring session to failover, the software does this automatically. If you don't put a witness in there to have automatic failover, then putting all of this behind a load balancer would be completely pointless in the first place.

3. You can NOT combine Windows Load Balancing and Clustering. They are incompatible.

|||

1) Michael, I know you as an extremely knowledgeable person, so I'm finding a hard time pointing out, that you seems to be off track. You cannot cluster on Windows Standard edition: http://www.microsoft.com/technet/windowsserver/evaluate/features/compare.mspx#64Clustering

Or at least, can you point me to a place in which Microsoft states that you can cluster on Windows 2003 Standard Edition?

Yours

Kim

|||

Michael,

I like the Idea of using NLB with 2005 Database mirroring since our DB's guy's will not have to rewrite all of their existing code, however I'm concerned about what would happen in the event of the SQL service stopping. I understand the witness will auto-magically failover the SQL resources but how do you instruct NLB to look for that same condition and redirect traffic accordingly?

Rob

|||That is done in the NLB configuration. You setup NLB with health checks that handle the traffic direction. Simply put, when the code in the NLB configuration can not connect to the SQL Server, it will direct connections to the alternate IP address that it is configured with. It behaves no differently than if you had a web server farm or app server farm behind NLB.

Question about Database Mirroring and Possibly NLB

OK since my company really would not prefer to spend the money for Windows 2003 Enterprise and MS SQL 2005 Enterprise I was thinking about this idea...

Server1 - W2K3 Std - SQL2005 Std - Primary
Server2 - W2K3 Std - SQL2005 Std - Mirror
Server3 - W2K3 Std - SQL2005 Std - Witness
Server4 - W2K3 Std - SQL2005 Std - Processing

OK So we have Server1 & Server2 doing the Database Mirroring. Then I have Server3 as the Witness to say who is the Primary and so forth. Now Server4 I thinking to us to create jobs that will run on a Schedule to pull data from our Ticketing system and import it into Server1 & Server2 (Yes this is a Datawarehouse).. So what I am wondering... We really wish to not have to change our connections to the DB with the Failover connection string and just use a common name. Since we are not usng Enterprise I can not do the Failover Clustering... So what I was wondering is if we could use the MS NLB. So for example with Server1 being primary and Server2 being Mirror... Server1 dies or goes down for maintance or whatever... Then Server3 will notice that and say OK Server2 you are now primary but also have Server3 go into the MS NLB and say Server3 Online and take all connections. Then when Server1 comes back on-line it is set as the Mirror system and is Offline in the NLB... Is this possible? If yes can someone either A... Give me directions on how to do that... or B shot me some URL's where I can read up on this stuff?

Thanks for all your help anyone is able to provide. It is greatly appricated.

Billy S.

1. You can build a 2 node cluster with the Standard edition

2. You don't need to deploy Standard edition for the witness, it can be Express Edition if you choose

3. Your imports would go to Server1. You can not write to a mirror.

4. Yes, you can combine this with NLB if you really want to.

|||

1. How do you setup a Cluster in Windows 2003 Server Standard? To my understanding it wont allow you to create a cluster on the Standard version... Can you give me a URL for that?

2. Yeah I understand that... but was noticing you cant really schedule any jobs via Express edition... Unless I am confused about that also.

3. Wouldn't my imports go to the Virtual name being which ever SQL Server is the Primary? Cause say Server1 dies and Server2 becomes primary... Wouldn't I want the updates to then to go Server2?

4. Do you happen to know where some documentation is on this concept?

Thanks again for all the help.

Billy S.

|||

What I believe Michael was referring to is SQL Server Standard Edition will allow you to create a 2 node cluster. I believe that you will still need to purchase Windows 2003 Server Enterprise Edition to cluster the two servers together at the OS level.

There are 3rd party products that allow you to do failover clustering similar to Microsoft Clustering that allow Windows 2003 Server Standard edition as the OS.

Hope this helps clarify things.

Drew

|||

What about on the Witness when it goes to Set a server to be the primary... can it also execute another command that will send a command to either a HW NLB or the MS NLB to set which ever server Online and the other server offline?

I would try and do it now but I need to wait some time first and get our VM server in before I can test anything... But manager is wanting me to say yes or no on this method and exactly what versions we will need to purchase for both the OS & SQL parts... So... Is there some place I can read about what the Witness can do when it does do a failover and set say Server2 to be primary and Server1 as mirror... like say run server2prim.bat... Just trying to get this figured out the best I can while they still have the purse open :)

|||

Actually, if you're operating in full sync mode (full safety), mirroring should do everything you need.

DBM can handle the redirection when there is a principal transition. You use a little different connection string which names both principal and partner. On connection, the app will automatically try the partner if the principal does not respond.

|||Yes... We can use the Failover Partner in the ODBC type connections... However some of the apps I design/manage/run use Perl and I do a direct connection not looking at ODBC to a single server... So... How would I do that without using the Failover peice in the connection string and not use the Microsoft Clustering Service? That is why I was looking at NLB...You have 2 machines in a NLB setup.. 1 system is set "Online" and the Secondary (Mirror Server) is set "Offline" now is there anyways on the Witness server to tell it when Primary server dies and it not available after you tell the Mirror server to become the primary run this command or batch job which will send a request to the NLB device or app saying Server1 is "Offline" and Server2 is "Online"... Does this make any sense? Or is what I am wanting to do just not doable period?|||

1. Windows Server 2003 STANDARD edition will do a 2 node cluster. That is very clearly documented in the feature set and there are tens of thousands of clusters out there running under Windows Server 2003 Standard edition. SQL Server 2005 Standard edition will also support a 2 node cluster.

2. If you are putting a load balancer in front of this, there is NOTHING that you need to do. The load balancer gives you a virtual IP for everything you put behind the LB. You hit the virtual IP exposed by the load balancer. The load balancer takes care of routing the traffic to the proper machine on the back end. Your applications are oblivious to any of this since they connect to the VIP and NOT the actual machines behind the load balancer. You don't point anything at all at the witness. The sole purpose of the witness is to enable automatic failover. How does traffic get re-routed by the load balancer? Exactly the same way that a load balancer routes all traffic. It detects that a resource is unavailable and then it automatically routes traffic to the resources that are available. By putting a witness in here, YOU are not telling the mirroring session to failover, the software does this automatically. If you don't put a witness in there to have automatic failover, then putting all of this behind a load balancer would be completely pointless in the first place.

3. You can NOT combine Windows Load Balancing and Clustering. They are incompatible.

|||

1) Michael, I know you as an extremely knowledgeable person, so I'm finding a hard time pointing out, that you seems to be off track. You cannot cluster on Windows Standard edition: http://www.microsoft.com/technet/windowsserver/evaluate/features/compare.mspx#64Clustering

Or at least, can you point me to a place in which Microsoft states that you can cluster on Windows 2003 Standard Edition?

Yours

Kim

|||

Michael,

I like the Idea of using NLB with 2005 Database mirroring since our DB's guy's will not have to rewrite all of their existing code, however I'm concerned about what would happen in the event of the SQL service stopping. I understand the witness will auto-magically failover the SQL resources but how do you instruct NLB to look for that same condition and redirect traffic accordingly?

Rob

|||That is done in the NLB configuration. You setup NLB with health checks that handle the traffic direction. Simply put, when the code in the NLB configuration can not connect to the SQL Server, it will direct connections to the alternate IP address that it is configured with. It behaves no differently than if you had a web server farm or app server farm behind NLB.