Load Balancing SSAS Servers

Hello,

We are trying to load balance a pair of SQL Server 2005 Analysis Services query servers using hardware load balancing (Foundry switches) but we don't seem to be able to get it to work.

The main problem as far as I can tell is that we can't get the SSAS instances to listen on the IP address of the loopback adapter which is configured on both servers.

The netstat -ano command shows that the SSAS instances are only listening on the IP address of the physical NIC in each machine.

We are using a custom port and I can see where this custom port is defined in the msmdsrv.ini file but I can't see anywhere where you can tell SSAS the IP addresses on which to listen.

Should SSAS listen on all available IP addresses when it starts up ? We have re-booted both boxes but this hasn't made any difference ?

Can anyone shed some light on this please ?

Regards,

Paul Lynch

Hi Paul, I have not done anything like this before, so this is a bit of a long shot. I was wondering if the security setting that you need to change in order to use application names might need to be changed. see http://support.microsoft.com/kb/888526 - this article is for AS2000, but I think the same approach still applies for AS2k5.

I think by default it checks that the name/ip address match each other, so this may help.

|||

Hi Darren,

Thanks for your reply.

We actually found the problem - the network guy had forgotten to configure something called Direct Server Return (DSR) on the Foundry so the traffic wasn't being forwarded properly to the SSAS boxes. When I looked into it again I realised that the SSAS process was actually listening on the loopback adapter address as well as the IP address of the physical NIC so the problem wasn't what I originally thought it was.

We are now able to connect to the VIP address on the port specified in SSAS and it appears to connect OK (I can connect from the front-end web server using SQL Management Studio so I'm assuming its working)

However, the KB article you posted has got me thinking that maybe we need to configure an application name for it to work - I'll need to speak to the DBA's because I'm out of my depth with this already - I'm just an IIS guy ;-)

Thanks again.

Paul

Load balancing SQL Server

I have 3 SQL Servers that are all subscribers and the only apps that need to
connect to it do read only queries . Id like the apps to load balance across
these 3 servers. Is there any article on how to set this up using Windows
Load balancing ?
Are there any other tools out there ?
Thanks
Load balancing is typically done on the application level, i.e. load balance
your web servers. Have a SQL Server hanging off each web server.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ONjJGSKnFHA.320@.TK2MSFTNGP09.phx.gbl...
> I have 3 SQL Servers that are all subscribers and the only apps that need
to
> connect to it do read only queries . Id like the apps to load balance
across
> these 3 servers. Is there any article on how to set this up using Windows
> Load balancing ?
> Are there any other tools out there ?
> Thanks
>
|||You can use Windows Network Load balancing or any external load balancer.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ONjJGSKnFHA.320@.TK2MSFTNGP09.phx.gbl...
>I have 3 SQL Servers that are all subscribers and the only apps that need
>to
> connect to it do read only queries . Id like the apps to load balance
> across
> these 3 servers. Is there any article on how to set this up using Windows
> Load balancing ?
> Are there any other tools out there ?
> Thanks
>
|||Do you know where I can find documentation on WLB with SQL Server ?
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eYvuUkOnFHA.1204@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> You can use Windows Network Load balancing or any external load balancer.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ONjJGSKnFHA.320@.TK2MSFTNGP09.phx.gbl...
Windows
>
|||Windows Help. Index search "Network Load Balancing". External vendors
include Cisco, F5, and probably others.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e7RCjkPnFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Do you know where I can find documentation on WLB with SQL Server ?
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eYvuUkOnFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Windows
>

Load balancing SQL Server

I have 3 SQL Servers that are all subscribers and the only apps that need to
connect to it do read only queries . Id like the apps to load balance across
these 3 servers. Is there any article on how to set this up using Windows
Load balancing ?
Are there any other tools out there ?
ThanksLoad balancing is typically done on the application level, i.e. load balance
your web servers. Have a SQL Server hanging off each web server.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ONjJGSKnFHA.320@.TK2MSFTNGP09.phx.gbl...
> I have 3 SQL Servers that are all subscribers and the only apps that need
to
> connect to it do read only queries . Id like the apps to load balance
across
> these 3 servers. Is there any article on how to set this up using Windows
> Load balancing ?
> Are there any other tools out there ?
> Thanks
>|||You can use Windows Network Load balancing or any external load balancer.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ONjJGSKnFHA.320@.TK2MSFTNGP09.phx.gbl...
>I have 3 SQL Servers that are all subscribers and the only apps that need
>to
> connect to it do read only queries . Id like the apps to load balance
> across
> these 3 servers. Is there any article on how to set this up using Windows
> Load balancing ?
> Are there any other tools out there ?
> Thanks
>|||Do you know where I can find documentation on WLB with SQL Server ?
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eYvuUkOnFHA.1204@.TK2MSFTNGP12.phx.gbl...
> You can use Windows Network Load balancing or any external load balancer.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ONjJGSKnFHA.320@.TK2MSFTNGP09.phx.gbl...
Windows[vbcol=seagreen]
>|||Windows Help. Index search "Network Load Balancing". External vendors
include Cisco, F5, and probably others.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e7RCjkPnFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Do you know where I can find documentation on WLB with SQL Server ?
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eYvuUkOnFHA.1204@.TK2MSFTNGP12.phx.gbl...
> Windows
>sql

Load balancing SQL Server

I have 3 SQL Servers that are all subscribers and the only apps that need to
connect to it do read only queries . Id like the apps to load balance across
these 3 servers. Is there any article on how to set this up using Windows
Load balancing ?
Are there any other tools out there ?
ThanksLoad balancing is typically done on the application level, i.e. load balance
your web servers. Have a SQL Server hanging off each web server.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ONjJGSKnFHA.320@.TK2MSFTNGP09.phx.gbl...
> I have 3 SQL Servers that are all subscribers and the only apps that need
to
> connect to it do read only queries . Id like the apps to load balance
across
> these 3 servers. Is there any article on how to set this up using Windows
> Load balancing ?
> Are there any other tools out there ?
> Thanks
>|||You can use Windows Network Load balancing or any external load balancer.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ONjJGSKnFHA.320@.TK2MSFTNGP09.phx.gbl...
>I have 3 SQL Servers that are all subscribers and the only apps that need
>to
> connect to it do read only queries . Id like the apps to load balance
> across
> these 3 servers. Is there any article on how to set this up using Windows
> Load balancing ?
> Are there any other tools out there ?
> Thanks
>|||Do you know where I can find documentation on WLB with SQL Server ?
"Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
news:eYvuUkOnFHA.1204@.TK2MSFTNGP12.phx.gbl...
> You can use Windows Network Load balancing or any external load balancer.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:ONjJGSKnFHA.320@.TK2MSFTNGP09.phx.gbl...
> >I have 3 SQL Servers that are all subscribers and the only apps that need
> >to
> > connect to it do read only queries . Id like the apps to load balance
> > across
> > these 3 servers. Is there any article on how to set this up using
Windows
> > Load balancing ?
> >
> > Are there any other tools out there ?
> >
> > Thanks
> >
> >
>|||Windows Help. Index search "Network Load Balancing". External vendors
include Cisco, F5, and probably others.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e7RCjkPnFHA.1416@.TK2MSFTNGP09.phx.gbl...
> Do you know where I can find documentation on WLB with SQL Server ?
> "Geoff N. Hiten" <sqlcraftsman@.gmail.com> wrote in message
> news:eYvuUkOnFHA.1204@.TK2MSFTNGP12.phx.gbl...
>> You can use Windows Network Load balancing or any external load balancer.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "Hassan" <fatima_ja@.hotmail.com> wrote in message
>> news:ONjJGSKnFHA.320@.TK2MSFTNGP09.phx.gbl...
>> >I have 3 SQL Servers that are all subscribers and the only apps that
>> >need
>> >to
>> > connect to it do read only queries . Id like the apps to load balance
>> > across
>> > these 3 servers. Is there any article on how to set this up using
> Windows
>> > Load balancing ?
>> >
>> > Are there any other tools out there ?
>> >
>> > Thanks
>> >
>> >
>>
>

load balancing question

I need to load balance / mirror 3 things.
1. sql server. Can i do this using sql technology ?
i.e i get my app to connect to 99.99.99.99:1344 and the server connect me
to one but then mirrors data on next sql server too ?
2. IIS6. I can use windows load balancing for this but one IIS6 machine need
to be the front end i believe. Better to use hardware infront of server i
guess ?
3. specific traffic on single IP ... i guess i need hardware to do this.
Any help much appreicated.
ScottAnswers inline.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scott" <s@.yahoo.co.uk> wrote in message
news:eLJdGQytHHA.3400@.TK2MSFTNGP03.phx.gbl...
>I need to load balance / mirror 3 things.
> 1. sql server. Can i do this using sql technology ?
> i.e i get my app to connect to 99.99.99.99:1344 and the server connect me
> to one but then mirrors data on next sql server too ?
>
Yes, with peer to peer replication or database mirroring DML occuring on one
node could be replicated/mirrored to all nodes in the replication/mirroring
topology. However with mirroring all nodes except the principal will be in a
state of recovering (in other words inaccessible), and with both replication
or mirroring, there is no client redirect. So if the app is connecting to
the SQL Server on 99.99.99.99:1344, you will have to do something
programmatically to have it connect to the other servers, and bring them
online (if you are using mirroring). So mirroring is not the best way to do
this and not the appropritate technology. Peer to peer replication can work,
but the latencies and the possibility or conflicts also do not make it an
appropriate technology.

>
> 2. IIS6. I can use windows load balancing for this but one IIS6 machine
> need to be the front end i believe. Better to use hardware infront of
> server i guess ?
With NLB one IIS server will redirect requests to other IIS servers which
form your NLB topology. Each of these IIS servers could be directed to a
different SQL Server, however this moves the problem back to how you are
going to consolidate DML to be consistent across all SQL Servers. If a
client connetct to IIS Server1, and does some work on SQL Server 1, what
happens when it reconnects to IIS 2? Is it ok that its data is not on SQL
Server 2 which hangs off IIS2? You need to work these issues out before
deploying such a topology. Of course all IIS Servers in your NLB topology
could connect to a single SQL Server which might lead to concurrency issues.
>
> 3. specific traffic on single IP ... i guess i need hardware to do this.
You need to look at hardware like Cisco's Local Director for things like
this.
>
> Any help much appreicated.
> Scott
>|||great help, thank you very much.
scott

load balancing question

I need to load balance / mirror 3 things.
1. sql server. Can i do this using sql technology ?
i.e i get my app to connect to 99.99.99.99:1344 and the server connect me
to one but then mirrors data on next sql server too ?
2. IIS6. I can use windows load balancing for this but one IIS6 machine need
to be the front end i believe. Better to use hardware infront of server i
guess ?
3. specific traffic on single IP ... i guess i need hardware to do this.
Any help much appreicated.
ScottAnswers inline.
--
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scott" <s@.yahoo.co.uk> wrote in message
news:eLJdGQytHHA.3400@.TK2MSFTNGP03.phx.gbl...
>I need to load balance / mirror 3 things.
> 1. sql server. Can i do this using sql technology ?
> i.e i get my app to connect to 99.99.99.99:1344 and the server connect me
> to one but then mirrors data on next sql server too ?
>
Yes, with peer to peer replication or database mirroring DML occuring on one
node could be replicated/mirrored to all nodes in the replication/mirroring
topology. However with mirroring all nodes except the principal will be in a
state of recovering (in other words inaccessible), and with both replication
or mirroring, there is no client redirect. So if the app is connecting to
the SQL Server on 99.99.99.99:1344, you will have to do something
programmatically to have it connect to the other servers, and bring them
online (if you are using mirroring). So mirroring is not the best way to do
this and not the appropritate technology. Peer to peer replication can work,
but the latencies and the possibility or conflicts also do not make it an
appropriate technology.
>
> 2. IIS6. I can use windows load balancing for this but one IIS6 machine
> need to be the front end i believe. Better to use hardware infront of
> server i guess ?
With NLB one IIS server will redirect requests to other IIS servers which
form your NLB topology. Each of these IIS servers could be directed to a
different SQL Server, however this moves the problem back to how you are
going to consolidate DML to be consistent across all SQL Servers. If a
client connetct to IIS Server1, and does some work on SQL Server 1, what
happens when it reconnects to IIS 2? Is it ok that its data is not on SQL
Server 2 which hangs off IIS2? You need to work these issues out before
deploying such a topology. Of course all IIS Servers in your NLB topology
could connect to a single SQL Server which might lead to concurrency issues.
>
> 3. specific traffic on single IP ... i guess i need hardware to do this.
You need to look at hardware like Cisco's Local Director for things like
this.
>
> Any help much appreicated.
> Scott
>|||great help, thank you very much.
scottsql

Load balancing for read-only databases

We planning to setup a database and use the Windows 2003 NLB features to
balance query load between two database-nodes. The database is accessed for
read-only queries from a webapplication.
To update the database contents, a seperate application will insert new
content into one of the two databases. This database acts as the publisher
for the other database, the subscriber. That way, both databases will be able
to handle the queries. We will not use any shared disk technology.
Questions are:
1. are there any caveats in this setup?
2. Will NLB effectively be able to distribute the (select-)query-load
amongst the two servers? Provided we disable connectionpooling, as it reads
somewhere in the docs for Commerce Server 2000.
3. if one the servers is switched off, will the web application still be
able to perform queries on the remaining server? Will there be any delay?
4. are both nodes still available seperately to host other databases that
are not load-balanced?
5. does this setup allow for scale out, by adding an additional
database-node to act as another subscriber?
6. do you know of any documentation that describes this type of setup?See inline
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Sander" <Sander@.discussions.microsoft.com> wrote in message
news:EB91D60B-B298-4176-B99E-7403FFD772B5@.microsoft.com...
> We planning to setup a database and use the Windows 2003 NLB features to
> balance query load between two database-nodes. The database is accessed
for
> read-only queries from a webapplication.
> To update the database contents, a seperate application will insert new
> content into one of the two databases. This database acts as the publisher
> for the other database, the subscriber. That way, both databases will be
able
> to handle the queries. We will not use any shared disk technology.
> Questions are:
> 1. are there any caveats in this setup?
Ensure your business needs regarding latency and currency are met .. ( One
will be slightly behind the other.)
> 2. Will NLB effectively be able to distribute the (select-)query-load
> amongst the two servers? Provided we disable connectionpooling, as it
reads
> somewhere in the docs for Commerce Server 2000.
I do not think there should be a problem.
> 3. if one the servers is switched off, will the web application still be
> able to perform queries on the remaining server? Will there be any delay?
If one server is switched off, all of the connections will be broken, the
app will have to reconnect and get routed to the other server.. You can make
this invisible to the end users if you wish ( but you do this in the
application..) For instance, if you are using query analyzer, and the you
are looking at the results of a query... the DB server goes down and comes
back up... Then you send another query, Query Analyzer will automatically
re-connect and send your query, instead of simply reporting that you no
longer have a connection... That is a nice way to go...
> 4. are both nodes still available seperately to host other databases that
> are not load-balanced?
Although this is not my strength, I believe the entire Server is load
balanced , or at least at the port level, which would include all of SQL
Server... If you wish some databases to exist on one server which are not
LBs, you might have to do something special ,(although I do not know
at - another port?... another instance on a different port?)
> 5. does this setup allow for scale out, by adding an additional
> database-node to act as another subscriber?
What many people do is to use log shipping for this... It is better than
replication for this use in my opinion because replication does NOT
replicate system tables, (permissions, etc), and log shipping does... Log
shipping can ship to several destinations, allowing a nice scale out
solution which should be fairly easy to implement...
> 6. do you know of any documentation that describes this type of setup?
Log Shipping is doc's in books on line... You will have to learn about LB
from other docs(not in SQL)

Load Balancing

Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
Floris
SQL Server does not support load balancing of the databases with NLB.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.itsummitseries.com/experts/fournier.htm - Cluster Summit
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message
news:OyIKCVSjFHA.1148@.TK2MSFTNGP12.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available
> with Windows Server 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our
> sql servers?
> And i will create a replication on the background between the 2 sql
> servers so they will both serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>
sql

Load Balancing

Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
Floris
Load balancing when you own data (like a DBMS does) and you also want to modify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk, where you till only
have one copy of data or you do shared nothing where you only have a set of data on one location).
First determine what modifications are to be performed. Easiest is of you can separate read-only
applications (reports etc). If so, you can replicate (using built-in transactional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge replication. However, if you
modify the same data at the same time on two nodes, one will win and the other modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different types of replications)
as well as "federated servers" (also known as distributed partitioned views).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sql servers?
> And i will create a replication on the background between the 2 sql servers so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>
|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>

Load Balancing

Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
FlorisLoad balancing when you own data (like a DBMS does) and you also want to modify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk, where you till only
have one copy of data or you do shared nothing where you only have a set of data on one location).
First determine what modifications are to be performed. Easiest is of you can separate read-only
applications (reports etc). If so, you can replicate (using built-in transactional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge replication. However, if you
modify the same data at the same time on two nodes, one will win and the other modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different types of replications)
as well as "federated servers" (also known as distributed partitioned views).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sql servers?
> And i will create a replication on the background between the 2 sql servers so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> In windows 2003 server you have NLB (Network Load Balancing).
>> Can i use this to load balance my traffic to our sql server?
>> I know you also have "Microsoft Cluster Service" but that's only
>> available with Windows Server 2003 Enterprise Edition.
>> So is it possible to load balance my traffic from our webservers to our
>> sql servers?
>> And i will create a replication on the background between the 2 sql
>> servers so they will both serve the same data.
>> I hope someone has an answer for me.
>> Thanks in advance.
>> Floris
>sql

Load Balancing

Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
FlorisLoad balancing when you own data (like a DBMS does) and you also want to mod
ify that data is far
from trivial.
I don't think any system does that transparently (you either use shared disk
, where you till only
have one copy of data or you do shared nothing where you only have a set of
data on one location).
First determine what modifications are to be performed. Easiest is of you ca
n separate read-only
applications (reports etc). If so, you can replicate (using built-in transac
tional replication) from
the server where you do modifications to the read-only server.
If you cannot separate read-only applications, you can look into merge repli
cation. However, if you
modify the same data at the same time on two nodes, one will win and the oth
er modification will be
lost. Unacceptable in many scenarios.
I suggest you read up in Books Online about Replication (there are different
types of replications)
as well as "federated servers" (also known as distributed partitioned views)
.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx
.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server
> 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our sq
l servers?
> And i will create a replication on the background between the 2 sql server
s so they will both
> serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>|||Thanks!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uMficaSjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> Load balancing when you own data (like a DBMS does) and you also want to
> modify that data is far from trivial.
> I don't think any system does that transparently (you either use shared
> disk, where you till only have one copy of data or you do shared nothing
> where you only have a set of data on one location).
> First determine what modifications are to be performed. Easiest is of you
> can separate read-only applications (reports etc). If so, you can
> replicate (using built-in transactional replication) from the server where
> you do modifications to the read-only server.
> If you cannot separate read-only applications, you can look into merge
> replication. However, if you modify the same data at the same time on two
> nodes, one will win and the other modification will be lost. Unacceptable
> in many scenarios.
> I suggest you read up in Books Online about Replication (there are
> different types of replications) as well as "federated servers" (also
> known as distributed partitioned views).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Floris" <meNOSPAM@.here_my_place.nl> wrote in message
> news:eEpmKVSjFHA.2472@.TK2MSFTNGP15.phx.gbl...
>

Load Balancing

Hi,
In windows 2003 server you have NLB (Network Load Balancing).
Can i use this to load balance my traffic to our sql server?
I know you also have "Microsoft Cluster Service" but that's only available
with Windows Server 2003 Enterprise Edition.
So is it possible to load balance my traffic from our webservers to our sql
servers?
And i will create a replication on the background between the 2 sql servers
so they will both serve the same data.
I hope someone has an answer for me.
Thanks in advance.
Floris
No, SQL Server is not Load Balance aware. Even with clustering does not
provide Load Balancing.
You could have load balance your web servers, and have individual web
servers accessing different dedicated SQL Servers.
SQL Server 2005 does provide Peer to Peer replication which will keep a
group of SQL Servers aggregated for scale out read and write performance in
sync. But it does not provide load balancing, but a piece of the
functionality required by it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Floris" <meNOSPAM@.here_my_place.nl> wrote in message
news:OG3UGVSjFHA.2904@.tk2msftngp13.phx.gbl...
> Hi,
> In windows 2003 server you have NLB (Network Load Balancing).
> Can i use this to load balance my traffic to our sql server?
> I know you also have "Microsoft Cluster Service" but that's only available
> with Windows Server 2003 Enterprise Edition.
> So is it possible to load balance my traffic from our webservers to our
sql
> servers?
> And i will create a replication on the background between the 2 sql
servers
> so they will both serve the same data.
> I hope someone has an answer for me.
> Thanks in advance.
> Floris
>

Load Balance Clustering on 2005?

My company is planning on using a datacenter for our customers. These are manufacturing plants from which we collect batch and trend data (currently stored in SQL Server)

If we move to a centralized database model we may need Load Balancing. I read that SQL 2005 will accommodate this so I called MS Support and no one could confirm this.

My dilemma is that I need to report upon what our options are for the datacenter. So I need to know if load balancing will be an offering of SQL 2005.

SQL Server 2005 provides failover clustering, not load-balancing clustering.

Network Load-balancing in Windows works well with multiple read-only servers each containing the same data, but this is not provided directly by SQL Server.

SQL Server 2005 has several features that provide increased scalability (table partitioning, SQL Server Service Broker, XML indexes, query notifications, CLR stored procedures, and others), enhancing the scale-up abilities and further reducing the need for such load-balancing capabilities, but no, load-balancing is not provided.

Don|||Thanks for the clarification.|||SQL不支持负载均衡的设置,仅支持故障转移.如果想实现的话,需要其他的软硬件和其他的配置可以实现一定程度上的负载均衡.如果想提高I/O性能可以试一下SAN存储网络,如果实现CUP等的性能可能需要实现分区或者镜像或者复制等技术,使数据在其他服务器上保持一致的副本,然后在WIN上实现负载均衡.或者还有其他的办法.目前我知道的就|||Don Vilen - professor at CSU Chico circa 1982 - that Don Vilen?

|||

Hi Dave -

Yep, that one Was it really that long ago?

Don (DonV)

Load Balance Clustering on 2005?

My company is planning on using a datacenter for our customers. These are manufacturing plants from which we collect batch and trend data (currently stored in SQL Server)

If we move to a centralized database model we may need Load Balancing. I read that SQL 2005 will accommodate this so I called MS Support and no one could confirm this.

My dilemma is that I need to report upon what our options are for the datacenter. So I need to know if load balancing will be an offering of SQL 2005.

SQL Server 2005 provides failover clustering, not load-balancing clustering.

Network Load-balancing in Windows works well with multiple read-only servers each containing the same data, but this is not provided directly by SQL Server.

SQL Server 2005 has several features that provide increased scalability (table partitioning, SQL Server Service Broker, XML indexes, query notifications, CLR stored procedures, and others), enhancing the scale-up abilities and further reducing the need for such load-balancing capabilities, but no, load-balancing is not provided.

Don|||Thanks for the clarification.|||SQL不支持负载均衡的设置,仅支持故障转移.如果想实现的话,需要其他的软硬件和其他的配置可以实现一定程度上的负载均衡.如果想提高I/O性能可以试一下SAN存储网络,如果实现CUP等的性能可能需要实现分区或者镜像或者复制等技术,使数据在其他服务器上保持一致的副本,然后在WIN上实现负载均衡.或者还有其他的办法.目前我知道的就|||Don Vilen - professor at CSU Chico circa 1982 - that Don Vilen?|||

Hi Dave -

Yep, that one Was it really that long ago?

Don (DonV)

sql

Load Balance Clustering on 2005?

My company is planning on using a datacenter for our customers. These are manufacturing plants from which we collect batch and trend data (currently stored in SQL Server)

If we move to a centralized database model we may need Load Balancing. I read that SQL 2005 will accommodate this so I called MS Support and no one could confirm this.

My dilemma is that I need to report upon what our options are for the datacenter. So I need to know if load balancing will be an offering of SQL 2005.

SQL Server 2005 provides failover clustering, not load-balancing clustering.

Network Load-balancing in Windows works well with multiple read-only servers each containing the same data, but this is not provided directly by SQL Server.

SQL Server 2005 has several features that provide increased scalability (table partitioning, SQL Server Service Broker, XML indexes, query notifications, CLR stored procedures, and others), enhancing the scale-up abilities and further reducing the need for such load-balancing capabilities, but no, load-balancing is not provided.

Don|||Thanks for the clarification.|||SQL不支持负载均衡的设置,仅支持故障转移.如果想实现的话,需要其他的软硬件和其他的配置可以实现一定程度上的负载均衡.如果想提高I/O性能可以试一下SAN存储网络,如果实现CUP等的性能可能需要实现分区或者镜像或者复制等技术,使数据在其他服务器上保持一致的副本,然后在WIN上实现负载均衡.或者还有其他的办法.目前我知道的就|||Don Vilen - professor at CSU Chico circa 1982 - that Don Vilen?

|||

Hi Dave -

Yep, that one Was it really that long ago?

Don (DonV)