List availables tables: balancing

Showing posts with label balancing. Show all posts

Friday, March 30, 2012

Load balancing.

Hi!
I have two sql servers. One raid rack in the middle for them both.
I installe two cluster groups, with two sql installation.
Now I can put a db on one server and it can fail over to the other one and
the other way around. Fine.
But how can I set it up so the same db is located one both cluster groups
(on the raid and shared by both cluster groups) and use some kind of load
balacing system to harvest the power of both servers for one db.
The only ways I have found so far is DTC and replikation. DTC no slack in
time for having the db exactly the same on both servers, but needs knowledge
and rewrite of code to use DTC transactions. Replikation, slack in time in
update between the cluster groups.
Is there another way to do this? We want to use the power of both servers in
a normal situation, have the fail over if one goes down, only one copy of
the db. No slack in time between the both servers.
regards
cj
SQL 2000 Clustering is not capable of load balancing a database.
Cheers,
Rod
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
http://msmvps.com/clustering/archive.../20/58233.aspx NYC Clustering
class
"Carl-Johan Wik" <cjwik@.hotmail.com> wrote in message
news:%23X%2334HRpFHA.708@.TK2MSFTNGP09.phx.gbl...
> Hi!
> I have two sql servers. One raid rack in the middle for them both.
> I installe two cluster groups, with two sql installation.
> Now I can put a db on one server and it can fail over to the other one and
> the other way around. Fine.
> But how can I set it up so the same db is located one both cluster groups
> (on the raid and shared by both cluster groups) and use some kind of load
> balacing system to harvest the power of both servers for one db.
> The only ways I have found so far is DTC and replikation. DTC no slack in
> time for having the db exactly the same on both servers, but needs
> knowledge and rewrite of code to use DTC transactions. Replikation, slack
> in time in update between the cluster groups.
> Is there another way to do this? We want to use the power of both servers
> in a normal situation, have the fail over if one goes down, only one copy
> of the db. No slack in time between the both servers.
> regards
> cj
>

load balancing with standard edition

Load balancing with Sql Server 2005 in Active/Active mode

This is in regards to the MS SQL

Server 2005 Cluster testing.

I have the set-up in

Active/Passive configuration.

We decided upon moving to the Active/Active

configuration, did some research on the internet for the additional features

which we get with this configuration and what could be the benefits of this over

the Active/Passive configuration. We have a query regarding the Active/Active

configuration. Please find below the details on the

same:

The Current Setup in Active/Passive

configuration has the following details:

There are two nodes “node 1” and

“node 2” setup on Virtual Server 2005.

A cluster has been set up using

“node 1” and “node 2”.

A SQL server default instance is

installed on “node 1”. While SQL server 2005 was installed, it created a

resource SQL server IP.

To connect to this SQL server

instance, our application uses SQL server IP created in step 3. (Please note

that this is the only IP through which we are able to connect to SQL cluster).

The fail-over features are working fine in this configuration. If “node 1” goes

down then the SQL server instance runs in “node 2”. This is taken care of by

cluster and is transparent to our application.

However, there is no provision for

load balancing as only one node is active at a

time.

After some research, we came across

Active/Active configuration which is supposed to support load

balancing.

We understand that in this

configuration, Step 1and 2 are similar to the Active/Passive configuration. The

only difference is in step 3 where an instance of SQL server is installed on

each node, thus providing two active nodes at a time. The failover works just

like in Active/Passive configuration.

As per the above information, the

Active/Active configuration seems to be similar to two SQL server instances

running independently.There will be two seperate databases and on failure of one instance other instance wont be able to cater to the requests designed for first instance, Thus providing no extra benefits from

cluster.

We require the information on how to

take benefits of the load balancing features in this configuration.

SQL Server 2005 doesn't support load-balancing in the way that you are describing, the main problem being that only one instance of SQL Server can access a database's data and log files at a time.

Have a look at these links for more info as to how load balancing can be achieved:

http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1127807,00.html

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1133488,00.html

Chris

|||As mentioned, MS SQL Server does not provide a way to do "active/active" cluster.

What you have installed would be a "high availablity" cluster, ie if one goes down, the other picks up.

If you need load balancing, you install 2 unique SQL installations and run "transactional replication" between them to make the databases the same. Then use a 3rd load balancing device to balance between them, problaby using "sticky sessions".

If you need both, high availabilty and load balancing, you install TWO 2 node clusters (4 servers + load balancing device) with transactional replication between cluster 1 and cluster 2.

I always recommend getting bigger/faster/better hardware and using 1 cluster of 2 servers.

Load balancing with SQl server 2005 cluster in active/active configuration

This is in regards to the MS SQL

Server 2005 Cluster testing.

I have the set-up in

Active/Passive configuration.

We decided upon moving to the Active/Active

configuration, did some research on the internet for the additional features

which we get with this configuration and what could be the benefits of this over

the Active/Passive configuration. We have a query regarding the Active/Active

configuration. Please find below the details on the

same:

The Current Setup in Active/Passive

configuration has the following details:

There are two nodes “node 1” and

“node 2” setup on Virtual Server 2005.

A cluster has been set up using

“node 1” and “node 2”.

A SQL server default instance is

installed on “node 1”. While SQL server 2005 was installed, it created a

resource SQL server IP.

To connect to this SQL server

instance, our application uses SQL server IP created in step 3. (Please note

that this is the only IP through which we are able to connect to SQL cluster).

The fail-over features are working fine in this configuration. If “node 1” goes

down then the SQL server instance runs in “node 2”. This is taken care of by

cluster and is transparent to our application.

However, there is no provision for

load balancing as only one node is active at a

time.

After some research, we came across

Active/Active configuration which is supposed to support load

balancing.

We understand that in this

configuration, Step 1and 2 are similar to the Active/Passive configuration. The

only difference is in step 3 where an instance of SQL server is installed on

each node, thus providing two active nodes at a time. The failover works just

like in Active/Passive configuration.

per the above information, the Active/Active configuration seems to be

similar to two SQL server instances running independently.There will be

two seperate databases and on failure of one instance other instance

wont be able to cater to the requests designed for first instance, Thus

providing no extra benefits from cluster.

We require the information on how to

take benefits of the load balancing features in this configuration.

Hi geetu...

MSCS (Microsoft Cluster Services) is not a load-balancing product, it is simply a high-availability solution, period...load balancing does not come in to play at all with clustering a Sql database...the term Active/Active seems to imply that this would be the case, which is why we typically try to refer to them as Multi-instance clusters now instead of the Active/Active label. Active/Active in the MSCS world basically means that you have 2 independent Sql Server instances running on 2 cluster nodes - these instances are independent of each other in all respects, obviously unless you link them in some manner with custom business logic, replication, etc. Think of them for all intents and purposes as 2 seperate instances running on seperate servers at all times (as that's what they really are)...the only difference being that in case of a physical node failure (or service failure on a node), the instance will be moved to and hosted on a second physical server.

I'd be curious to know what research you came across that implied load-balancing as a feature with MSCS so we can try and get it corrected, or possibly clarify what position the author was taking.

To support load-balancing, or scaling out in a Sql Server environment you have a couple of different options, depending on your edition, environment, version of Sql, etc. Take a look at the following articles for a start:

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

http://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx

HTH

|||

Hi Geetu,

Basically, Active/Active Cluster is two Active/Passive Clusters.

As mentioned by Chad, they are completely independent of each other.

HTH

Jag

|||

I hope you got more information from Chad's reply here and as suggested you might need another solution, cluster will not provide the load balancing.

These 2 links shoudl give you information in this regard:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog04.mspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0513.mspx

http://www.sql-server-performance.com/dk_massive_scalability.asp

|||

It's pretty clear what geetu is trying to get at. Here is your answer geetu:

1. Instead of having the 2nd node idle until something happens to node1, you can split the databases between the 2 nodes.

2. While this is not the definition of classic Load Balancing, it does help balance the load between the two nodes while they are both up.

We have several customers who are configured like this and it works well.

The downside? The “size” of each of the 2 servers in terms of CPU, memory etc must be enough to handle all the databases that are normally served from the 2 nodes in case one node fails.

While this is the same requirement in the active/passive configuration, there is a danger that over time you will keeps adding load to both nodes independently and at time of failure, the failover node will die as well because of a sudden overwhelming load.

Sometimes when smarty MSFT employees respond, they should be more respectful to the person who is posting a question, instead of being arrogant.

sql

Load balancing with SQl server 2005 cluster in active/active configuration

This is in regards to the MS SQL Server 2005 Cluster testing.

I have the set-up in Active/Passive configuration.

We decided upon moving to the Active/Active configuration, did some research on the internet for the additional features which we get with this configuration and what could be the benefits of this over the Active/Passive configuration. We have a query regarding the Active/Active configuration. Please find below the details on the same:

The Current Setup in Active/Passive configuration has the following details:

1) There are two nodes “node 1” and “node 2” setup on Virtual Server 2005.

2) A cluster has been set up using “node 1” and “node 2”.

3) A SQL server default instance is installed on “node 1”. While SQL server 2005 was installed, it created a resource SQL server IP.

To connect to this SQL server instance, our application uses SQL server IP created in step 3. (Please note that this is the only IP through which we are able to connect to SQL cluster). The fail-over features are working fine in this configuration. If “node 1” goes down then the SQL server instance runs in “node 2”. This is taken care of by cluster and is transparent to our application.

However, there is no provision for load balancing as only one node is active at a time.

After some research, we came across Active/Active configuration which is supposed to support load balancing.

We understand that in this configuration, Step 1and 2 are similar to the Active/Passive configuration. The only difference is in step 3 where an instance of SQL server is installed on each node, thus providing two active nodes at a time. The failover works just like in Active/Passive configuration.

As per the above information, the Active/Active configuration seems to be similar to two SQL server instances running independently.There will be two seperate databases and on failure of one instance other instance wont be able to cater to the requests designed for first instance, Thus providing no extra benefits from cluster.

We require the information on how to take benefits of the load balancing features in this configuration.

Hi geetu...

I'd be curious to know what research you came across that implied load-balancing as a feature with MSCS so we can try and get it corrected, or possibly clarify what position the author was taking.

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

http://www.microsoft.com/technet/prodtechnol/sql/2005/scddrtng.mspx

HTH

|||

Hi Geetu,

Basically, Active/Active Cluster is two Active/Passive Clusters.

As mentioned by Chad, they are completely independent of each other.

HTH

Jag

|||

I hope you got more information from Chad's reply here and as suggested you might need another solution, cluster will not provide the load balancing.

These 2 links shoudl give you information in this regard:

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/hasog04.mspx

http://www.microsoft.com/technet/community/chats/trans/sql/sql0513.mspx

http://www.sql-server-performance.com/dk_massive_scalability.asp

|||

It's pretty clear what geetu is trying to get at. Here is your answer geetu:

1. Instead of having the 2nd node idle until something happens to node1, you can split the databases between the 2 nodes.

2. While this is not the definition of classic Load Balancing, it does help balance the load between the two nodes while they are both up.

We have several customers who are configured like this and it works well.

The downside? The “size” of each of the 2 servers in terms of CPU, memory etc must be enough to handle all the databases that are normally served from the 2 nodes in case one node fails.

Sometimes when smarty MSFT employees respond, they should be more respectful to the person who is posting a question, instead of being arrogant.

Load Balancing using Service Broker

I am trying to achieve load balancing through service broker.

I have a 3 server setup:

server1:

Source Database where the requests are sent. This has two routes created on this to server 2 and 3 with the service broker instance id specified. The service broker matches both these routes to the destination databases on Server 2 & 3 and send the requests in a round robin fashion by itself.

server2 & 3: These servers have the destination databases. The databases are exactly same with same messages, contracts, queues, services, and servicing programs. These don't send the responce back and the source is also not expecting for any response from the target databases.

When I tested this scenario it was behaving in a different manner. If there are 20 requests, in a normal case 10 requests should go to either of the destiantion servers, but I found that sometimes it was 11-9 or 12-8 only once did it do a 10-10.

Also I want to know is it best practice to rely on the inbuilt feature of the service broker in matching the routes and sending the messages rather than doing it ourselves. I which case one has to write the logic for sending the message to appropriate target server based on the status of the destination queue.

I also want to know which would be the best technique to use to bring the two destination databases in a concurrent state.

thanks

ashok

The routes chosen are not round-robin, are random (based on a hash of conversation_id and conversation_id is a random guid). You will not get exactly 10-10, but on the long run the average should be 50%-50%.

The best practice is to let the routes do the load balancing. These assumes the services on server2&3 offer identical functionality. When you prefer a certain service, you enter into the realms of data depending routing, see http://rushi.desai.name/Blog/tabid/54/EntryID/38/Default.aspx and http://rushi.desai.name/Blog/tabid/54/EntryID/39/Default.aspx for a discussion of this topic. SSB does not offer data depending routing out of the box. My recommendation is to keep things simple and rely on the routes to do a 'dumb' load balancing.

About bringing the two back-end services in a concurrent state, the topic is a bit more complicated. Do you want all the data to be identical between the two? What you should have in mind is how to 'taxonomize' your data, see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/dataoutsideinside.asp and other papers by Pat Helland. Lookup data can be stored on one central server and replicated to the N instances of the service (you have to plan for eventual growth by scale out of the service). Business transactions data can be kept partitioned on the server 2&3..&N instances. Some ETL process runs on each of these partitions and populates a central analisys server for reporting and analysis. Each service database in the server2&3...&N group can be individually mirrored for high availability and disaster recoverability.

HTH,
Remus

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

Wednesday, March 28, 2012

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 question

Hi all, in our current environment we have 1 server running sql. After
making a review of our disaster recovery strategy and readiness we have
decided to bring in a 2nd server to function as a standby failover. Due to
the price limitations with SQL2KEE we have decided to stick with Standard
Edition. Here is how we intend to set it up and wanted to see whether this
was feasible and were there any downsides to this architecture.
1. 2 Servers running Windows Server 2003 Standard in a NLB Cluster.
2. Both Servers will have SQL2K Standard Installed
3. Database files (mdf & ldf) will reside on a Dell PowerVault NAS Server
4. Databases on each server configured to point to same file on NAS Server.
Assuming this is possible, if 1 server were to go down, the 2nd would
pickup. Now we would be in a ditch if the NAS Server were to go down but
with the Raid 5 measures in place we should be OK.
Any suggestions/recommendations are greatly appreciated.
thanks a bunch!I'm not sure if that would work. The NAS device is a server by itself. How
will SQL Server connect to the mdf/ldf files? Through a shared folder to
another server? I don't think that works. Even if it did, without direct
attach, i.e., fiber/SAN, I assume you'd see some corruption due to network
latency.
FWIW, NLB for Win Server 2003 was horrible for my company WRT IIS. It still
sent traffic to a server that was down because as far as it was concerned,
the load on the downed server was lower than the server that was up.
Incindentally, I've seen SQL 2k EE on ebay for what we paid for Standard
edition.
"Assuming this is possible, if 1 server were to go down, the 2nd would
pickup."
That's cluster-failover, not network load balancing.
Maybe look at a product called Britestore from Computer Associates. I'm not
an expert on what it can do but I believe it's a lower cost alternative to
clustering.
hth
Eric
RP wrote:
> Hi all, in our current environment we have 1 server running sql. After
> making a review of our disaster recovery strategy and readiness we
> have decided to bring in a 2nd server to function as a standby
> failover. Due to the price limitations with SQL2KEE we have decided
> to stick with Standard Edition. Here is how we intend to set it up
> and wanted to see whether this was feasible and were there any
> downsides to this architecture.
> 1. 2 Servers running Windows Server 2003 Standard in a NLB Cluster.
> 2. Both Servers will have SQL2K Standard Installed
> 3. Database files (mdf & ldf) will reside on a Dell PowerVault NAS
> Server
> 4. Databases on each server configured to point to same file on NAS
> Server.
> Assuming this is possible, if 1 server were to go down, the 2nd would
> pickup. Now we would be in a ditch if the NAS Server were to go down
> but with the Raid 5 measures in place we should be OK.
> Any suggestions/recommendations are greatly appreciated.
> thanks a bunch!|||Eric, thanks for your response. When you say you had a bad experience with
NLB could you please elaborate? We are about to implement an NLB solution
and I would like to know what to expect here and whether it is the right
solution. Could it be the rules on NLB Manager were misconfigured and that
is why it still sent traffic to the server that was down? Also was the
server totally down or was it up but just IIS was down?
thanks!
"Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in message
news:ebnwwjrhEHA.1888@.TK2MSFTNGP10.phx.gbl...
> I'm not sure if that would work. The NAS device is a server by itself.
How
> will SQL Server connect to the mdf/ldf files? Through a shared folder to
> another server? I don't think that works. Even if it did, without direct
> attach, i.e., fiber/SAN, I assume you'd see some corruption due to network
> latency.
> FWIW, NLB for Win Server 2003 was horrible for my company WRT IIS. It
still
> sent traffic to a server that was down because as far as it was concerned,
> the load on the downed server was lower than the server that was up.
> Incindentally, I've seen SQL 2k EE on ebay for what we paid for Standard
> edition.
> "Assuming this is possible, if 1 server were to go down, the 2nd would
> pickup."
> That's cluster-failover, not network load balancing.
> Maybe look at a product called Britestore from Computer Associates. I'm
not
> an expert on what it can do but I believe it's a lower cost alternative to
> clustering.
> hth
> Eric
>
> RP wrote:
> > Hi all, in our current environment we have 1 server running sql. After
> > making a review of our disaster recovery strategy and readiness we
> > have decided to bring in a 2nd server to function as a standby
> > failover. Due to the price limitations with SQL2KEE we have decided
> > to stick with Standard Edition. Here is how we intend to set it up
> > and wanted to see whether this was feasible and were there any
> > downsides to this architecture.
> >
> > 1. 2 Servers running Windows Server 2003 Standard in a NLB Cluster.
> > 2. Both Servers will have SQL2K Standard Installed
> > 3. Database files (mdf & ldf) will reside on a Dell PowerVault NAS
> > Server
> > 4. Databases on each server configured to point to same file on NAS
> > Server.
> >
> > Assuming this is possible, if 1 server were to go down, the 2nd would
> > pickup. Now we would be in a ditch if the NAS Server were to go down
> > but with the Raid 5 measures in place we should be OK.
> >
> > Any suggestions/recommendations are greatly appreciated.
> >
> > thanks a bunch!
>|||It wasn't my department; all I know was NLB the problem, sorry I can't
explain further. But I think it was IIS that was down, not the entire
server, so the server itself still was saying "yes send traffic to this
IP/Port".
But still, you're thinking about load balancing somethign that can't be load
balanced. You can't have 2 sql server instances communicating with the same
MDF/NDF/LDFs at the same time.
RP wrote:
> Eric, thanks for your response. When you say you had a bad experience
> with NLB could you please elaborate? We are about to implement an NLB
> solution and I would like to know what to expect here and whether it
> is the right solution. Could it be the rules on NLB Manager were
> misconfigured and that is why it still sent traffic to the server
> that was down? Also was the server totally down or was it up but just
> IIS was down?
> thanks!
> "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in message
> news:ebnwwjrhEHA.1888@.TK2MSFTNGP10.phx.gbl...
>> I'm not sure if that would work. The NAS device is a server by
>> itself. How will SQL Server connect to the mdf/ldf files? Through a
>> shared folder to another server? I don't think that works. Even if
>> it did, without direct attach, i.e., fiber/SAN, I assume you'd see
>> some corruption due to network latency.
>> FWIW, NLB for Win Server 2003 was horrible for my company WRT IIS.
>> It still sent traffic to a server that was down because as far as it
>> was concerned, the load on the downed server was lower than the
>> server that was up.
>> Incindentally, I've seen SQL 2k EE on ebay for what we paid for
>> Standard edition.
>> "Assuming this is possible, if 1 server were to go down, the 2nd
>> would pickup."
>> That's cluster-failover, not network load balancing.
>> Maybe look at a product called Britestore from Computer Associates.
>> I'm not an expert on what it can do but I believe it's a lower cost
>> alternative to clustering.
>> hth
>> Eric
>>
>> RP wrote:
>> Hi all, in our current environment we have 1 server running sql.
>> After making a review of our disaster recovery strategy and
>> readiness we have decided to bring in a 2nd server to function as a
>> standby failover. Due to the price limitations with SQL2KEE we have
>> decided to stick with Standard Edition. Here is how we intend to
>> set it up and wanted to see whether this was feasible and were
>> there any downsides to this architecture.
>> 1. 2 Servers running Windows Server 2003 Standard in a NLB Cluster.
>> 2. Both Servers will have SQL2K Standard Installed
>> 3. Database files (mdf & ldf) will reside on a Dell PowerVault NAS
>> Server
>> 4. Databases on each server configured to point to same file on NAS
>> Server.
>> Assuming this is possible, if 1 server were to go down, the 2nd
>> would pickup. Now we would be in a ditch if the NAS Server were to
>> go down but with the Raid 5 measures in place we should be OK.
>> Any suggestions/recommendations are greatly appreciated.
>> thanks a bunch!|||As Eric states you can not use NLB to load share with SQL Server unless the
two DB's are essentially read only. YOu can have a standby server but you
don't need NLB for that you can simply change the way the clients are
pointing and redirect them to the other server when the first is down. You
should also reconsider using NAS for your SQL Server files as it is
basically unsupported except for one specific condition and the DELL
solution is not it. See here for more info:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261&Product=sql2k
--
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in message
news:uwRugZshEHA.3932@.TK2MSFTNGP09.phx.gbl...
> It wasn't my department; all I know was NLB the problem, sorry I can't
> explain further. But I think it was IIS that was down, not the entire
> server, so the server itself still was saying "yes send traffic to this
> IP/Port".
> But still, you're thinking about load balancing somethign that can't be
load
> balanced. You can't have 2 sql server instances communicating with the
same
> MDF/NDF/LDFs at the same time.
>
> RP wrote:
> > Eric, thanks for your response. When you say you had a bad experience
> > with NLB could you please elaborate? We are about to implement an NLB
> > solution and I would like to know what to expect here and whether it
> > is the right solution. Could it be the rules on NLB Manager were
> > misconfigured and that is why it still sent traffic to the server
> > that was down? Also was the server totally down or was it up but just
> > IIS was down?
> >
> > thanks!
> >
> > "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in message
> > news:ebnwwjrhEHA.1888@.TK2MSFTNGP10.phx.gbl...
> >> I'm not sure if that would work. The NAS device is a server by
> >> itself. How will SQL Server connect to the mdf/ldf files? Through a
> >> shared folder to another server? I don't think that works. Even if
> >> it did, without direct attach, i.e., fiber/SAN, I assume you'd see
> >> some corruption due to network latency.
> >>
> >> FWIW, NLB for Win Server 2003 was horrible for my company WRT IIS.
> >> It still sent traffic to a server that was down because as far as it
> >> was concerned, the load on the downed server was lower than the
> >> server that was up.
> >>
> >> Incindentally, I've seen SQL 2k EE on ebay for what we paid for
> >> Standard edition.
> >>
> >> "Assuming this is possible, if 1 server were to go down, the 2nd
> >> would pickup."
> >> That's cluster-failover, not network load balancing.
> >>
> >> Maybe look at a product called Britestore from Computer Associates.
> >> I'm not an expert on what it can do but I believe it's a lower cost
> >> alternative to clustering.
> >>
> >> hth
> >> Eric
> >>
> >>
> >> RP wrote:
> >> Hi all, in our current environment we have 1 server running sql.
> >> After making a review of our disaster recovery strategy and
> >> readiness we have decided to bring in a 2nd server to function as a
> >> standby failover. Due to the price limitations with SQL2KEE we have
> >> decided to stick with Standard Edition. Here is how we intend to
> >> set it up and wanted to see whether this was feasible and were
> >> there any downsides to this architecture.
> >>
> >> 1. 2 Servers running Windows Server 2003 Standard in a NLB Cluster.
> >> 2. Both Servers will have SQL2K Standard Installed
> >> 3. Database files (mdf & ldf) will reside on a Dell PowerVault NAS
> >> Server
> >> 4. Databases on each server configured to point to same file on NAS
> >> Server.
> >>
> >> Assuming this is possible, if 1 server were to go down, the 2nd
> >> would pickup. Now we would be in a ditch if the NAS Server were to
> >> go down but with the Raid 5 measures in place we should be OK.
> >>
> >> Any suggestions/recommendations are greatly appreciated.
> >>
> >> thanks a bunch!
>|||Andrew, thanks for the MSKB. So I guess that option is ruled out. Well I can
have a standby server, but manual switching of configuration files is
something I dont want to do. I would rather have the standby server pickup
automatically and then I can work on the primary server to bring it back up.
What other options do I have here without having to go to SQLEE & Windows
Advanced Server Clustering. Are there any cost-effective database
replication software out there that can keep the the standby server in sync
with the primary?
thanks!
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23xD5AhshEHA.596@.TK2MSFTNGP11.phx.gbl...
> As Eric states you can not use NLB to load share with SQL Server unless
the
> two DB's are essentially read only. YOu can have a standby server but you
> don't need NLB for that you can simply change the way the clients are
> pointing and redirect them to the other server when the first is down.
You
> should also reconsider using NAS for your SQL Server files as it is
> basically unsupported except for one specific condition and the DELL
> solution is not it. See here for more info:
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261&Product=sql2k
> --
> Andrew J. Kelly SQL MVP
>
> "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in message
> news:uwRugZshEHA.3932@.TK2MSFTNGP09.phx.gbl...
> > It wasn't my department; all I know was NLB the problem, sorry I can't
> > explain further. But I think it was IIS that was down, not the entire
> > server, so the server itself still was saying "yes send traffic to this
> > IP/Port".
> >
> > But still, you're thinking about load balancing somethign that can't be
> load
> > balanced. You can't have 2 sql server instances communicating with the
> same
> > MDF/NDF/LDFs at the same time.
> >
> >
> >
> > RP wrote:
> > > Eric, thanks for your response. When you say you had a bad experience
> > > with NLB could you please elaborate? We are about to implement an NLB
> > > solution and I would like to know what to expect here and whether it
> > > is the right solution. Could it be the rules on NLB Manager were
> > > misconfigured and that is why it still sent traffic to the server
> > > that was down? Also was the server totally down or was it up but just
> > > IIS was down?
> > >
> > > thanks!
> > >
> > > "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in message
> > > news:ebnwwjrhEHA.1888@.TK2MSFTNGP10.phx.gbl...
> > >> I'm not sure if that would work. The NAS device is a server by
> > >> itself. How will SQL Server connect to the mdf/ldf files? Through a
> > >> shared folder to another server? I don't think that works. Even if
> > >> it did, without direct attach, i.e., fiber/SAN, I assume you'd see
> > >> some corruption due to network latency.
> > >>
> > >> FWIW, NLB for Win Server 2003 was horrible for my company WRT IIS.
> > >> It still sent traffic to a server that was down because as far as it
> > >> was concerned, the load on the downed server was lower than the
> > >> server that was up.
> > >>
> > >> Incindentally, I've seen SQL 2k EE on ebay for what we paid for
> > >> Standard edition.
> > >>
> > >> "Assuming this is possible, if 1 server were to go down, the 2nd
> > >> would pickup."
> > >> That's cluster-failover, not network load balancing.
> > >>
> > >> Maybe look at a product called Britestore from Computer Associates.
> > >> I'm not an expert on what it can do but I believe it's a lower cost
> > >> alternative to clustering.
> > >>
> > >> hth
> > >> Eric
> > >>
> > >>
> > >> RP wrote:
> > >> Hi all, in our current environment we have 1 server running sql.
> > >> After making a review of our disaster recovery strategy and
> > >> readiness we have decided to bring in a 2nd server to function as a
> > >> standby failover. Due to the price limitations with SQL2KEE we have
> > >> decided to stick with Standard Edition. Here is how we intend to
> > >> set it up and wanted to see whether this was feasible and were
> > >> there any downsides to this architecture.
> > >>
> > >> 1. 2 Servers running Windows Server 2003 Standard in a NLB Cluster.
> > >> 2. Both Servers will have SQL2K Standard Installed
> > >> 3. Database files (mdf & ldf) will reside on a Dell PowerVault NAS
> > >> Server
> > >> 4. Databases on each server configured to point to same file on NAS
> > >> Server.
> > >>
> > >> Assuming this is possible, if 1 server were to go down, the 2nd
> > >> would pickup. Now we would be in a ditch if the NAS Server were to
> > >> go down but with the Raid 5 measures in place we should be OK.
> > >>
> > >> Any suggestions/recommendations are greatly appreciated.
> > >>
> > >> thanks a bunch!
> >
> >
>|||Log Shipping. With SQL 2k EE (which you already said you don't want it) you
get log shipping. But I have seen articles and web sites often describing
how to set up a poor man's log shipping.
Search MSDN, Google, and some of the popular SQL Server web sites for log
shipping on SQL Server Standard Edition.
But you still have the issue of clients now need to point to the new server.
Depending on what front-end application you use and how it's set up, it
could be as easy as changing a server-based config/ini/xml file or it could
be as painful as sneakering (walking to each pc and updating a file or odbc
connection.)
hth
Eric
RP wrote:
> Andrew, thanks for the MSKB. So I guess that option is ruled out.
> Well I can have a standby server, but manual switching of
> configuration files is something I dont want to do. I would rather
> have the standby server pickup automatically and then I can work on
> the primary server to bring it back up. What other options do I have
> here without having to go to SQLEE & Windows Advanced Server
> Clustering. Are there any cost-effective database replication
> software out there that can keep the the standby server in sync with
> the primary?
> thanks!
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23xD5AhshEHA.596@.TK2MSFTNGP11.phx.gbl...
>> As Eric states you can not use NLB to load share with SQL Server
>> unless the two DB's are essentially read only. YOu can have a
>> standby server but you don't need NLB for that you can simply change
>> the way the clients are pointing and redirect them to the other
>> server when the first is down. You should also reconsider using NAS
>> for your SQL Server files as it is basically unsupported except for
>> one specific condition and the DELL solution is not it. See here
>> for more info:
>>
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261&Product=sql2k
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in
>> message news:uwRugZshEHA.3932@.TK2MSFTNGP09.phx.gbl...
>> It wasn't my department; all I know was NLB the problem, sorry I
>> can't explain further. But I think it was IIS that was down, not
>> the entire server, so the server itself still was saying "yes send
>> traffic to this IP/Port".
>> But still, you're thinking about load balancing somethign that
>> can't be load balanced. You can't have 2 sql server instances
>> communicating with the same MDF/NDF/LDFs at the same time.
>>
>> RP wrote:
>> Eric, thanks for your response. When you say you had a bad
>> experience with NLB could you please elaborate? We are about to
>> implement an NLB solution and I would like to know what to expect
>> here and whether it is the right solution. Could it be the rules
>> on NLB Manager were misconfigured and that is why it still sent
>> traffic to the server that was down? Also was the server totally
>> down or was it up but just IIS was down?
>> thanks!
>> "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in
>> message news:ebnwwjrhEHA.1888@.TK2MSFTNGP10.phx.gbl...
>> I'm not sure if that would work. The NAS device is a server by
>> itself. How will SQL Server connect to the mdf/ldf files?
>> Through a shared folder to another server? I don't think that
>> works. Even if it did, without direct attach, i.e., fiber/SAN, I
>> assume you'd see some corruption due to network latency.
>> FWIW, NLB for Win Server 2003 was horrible for my company WRT IIS.
>> It still sent traffic to a server that was down because as far as
>> it was concerned, the load on the downed server was lower than the
>> server that was up.
>> Incindentally, I've seen SQL 2k EE on ebay for what we paid for
>> Standard edition.
>> "Assuming this is possible, if 1 server were to go down, the 2nd
>> would pickup."
>> That's cluster-failover, not network load balancing.
>> Maybe look at a product called Britestore from Computer
>> Associates. I'm not an expert on what it can do but I believe
>> it's a lower cost alternative to clustering.
>> hth
>> Eric
>>
>> RP wrote:
>> Hi all, in our current environment we have 1 server running sql.
>> After making a review of our disaster recovery strategy and
>> readiness we have decided to bring in a 2nd server to function
>> as a standby failover. Due to the price limitations with SQL2KEE
>> we have decided to stick with Standard Edition. Here is how we
>> intend to set it up and wanted to see whether this was feasible
>> and were there any downsides to this architecture.
>> 1. 2 Servers running Windows Server 2003 Standard in a NLB
>> Cluster.
>> 2. Both Servers will have SQL2K Standard Installed
>> 3. Database files (mdf & ldf) will reside on a Dell PowerVault
>> NAS Server
>> 4. Databases on each server configured to point to same file on
>> NAS Server.
>> Assuming this is possible, if 1 server were to go down, the 2nd
>> would pickup. Now we would be in a ditch if the NAS Server were
>> to go down but with the Raid 5 measures in place we should be OK.
>> Any suggestions/recommendations are greatly appreciated.
>> thanks a bunch!|||Eric, I have tried the "poor mans" log shipping method. It works fine for
smaller databases. It never did work on one of my larger databases which was
1.5GB+. I kept getting timeout errors which I could never figure out why. I
guess some sort of database replication software would do the trick. The key
is frequency of the replication. Does anyone offer near-instant frequencies?
If so, I guess in theory if my primary went down all I would have to do is
switch the config files to the backup server and all the data would be there
ready to go...
thanks!
"Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in message
news:%23JVwD1shEHA.1184@.TK2MSFTNGP12.phx.gbl...
> Log Shipping. With SQL 2k EE (which you already said you don't want it)
you
> get log shipping. But I have seen articles and web sites often describing
> how to set up a poor man's log shipping.
> Search MSDN, Google, and some of the popular SQL Server web sites for log
> shipping on SQL Server Standard Edition.
> But you still have the issue of clients now need to point to the new
server.
> Depending on what front-end application you use and how it's set up, it
> could be as easy as changing a server-based config/ini/xml file or it
could
> be as painful as sneakering (walking to each pc and updating a file or
odbc
> connection.)
> hth
> Eric
>
> RP wrote:
> > Andrew, thanks for the MSKB. So I guess that option is ruled out.
> > Well I can have a standby server, but manual switching of
> > configuration files is something I dont want to do. I would rather
> > have the standby server pickup automatically and then I can work on
> > the primary server to bring it back up. What other options do I have
> > here without having to go to SQLEE & Windows Advanced Server
> > Clustering. Are there any cost-effective database replication
> > software out there that can keep the the standby server in sync with
> > the primary?
> >
> > thanks!
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%23xD5AhshEHA.596@.TK2MSFTNGP11.phx.gbl...
> >> As Eric states you can not use NLB to load share with SQL Server
> >> unless the two DB's are essentially read only. YOu can have a
> >> standby server but you don't need NLB for that you can simply change
> >> the way the clients are pointing and redirect them to the other
> >> server when the first is down. You should also reconsider using NAS
> >> for your SQL Server files as it is basically unsupported except for
> >> one specific condition and the DELL solution is not it. See here
> >> for more info:
> >>
> >>
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261&Product=sql2k
> >>
> >> --
> >> Andrew J. Kelly SQL MVP
> >>
> >>
> >> "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in
> >> message news:uwRugZshEHA.3932@.TK2MSFTNGP09.phx.gbl...
> >> It wasn't my department; all I know was NLB the problem, sorry I
> >> can't explain further. But I think it was IIS that was down, not
> >> the entire server, so the server itself still was saying "yes send
> >> traffic to this IP/Port".
> >>
> >> But still, you're thinking about load balancing somethign that
> >> can't be load balanced. You can't have 2 sql server instances
> >> communicating with the same MDF/NDF/LDFs at the same time.
> >>
> >>
> >>
> >> RP wrote:
> >> Eric, thanks for your response. When you say you had a bad
> >> experience with NLB could you please elaborate? We are about to
> >> implement an NLB solution and I would like to know what to expect
> >> here and whether it is the right solution. Could it be the rules
> >> on NLB Manager were misconfigured and that is why it still sent
> >> traffic to the server that was down? Also was the server totally
> >> down or was it up but just IIS was down?
> >>
> >> thanks!
> >>
> >> "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in
> >> message news:ebnwwjrhEHA.1888@.TK2MSFTNGP10.phx.gbl...
> >> I'm not sure if that would work. The NAS device is a server by
> >> itself. How will SQL Server connect to the mdf/ldf files?
> >> Through a shared folder to another server? I don't think that
> >> works. Even if it did, without direct attach, i.e., fiber/SAN, I
> >> assume you'd see some corruption due to network latency.
> >>
> >> FWIW, NLB for Win Server 2003 was horrible for my company WRT IIS.
> >> It still sent traffic to a server that was down because as far as
> >> it was concerned, the load on the downed server was lower than the
> >> server that was up.
> >>
> >> Incindentally, I've seen SQL 2k EE on ebay for what we paid for
> >> Standard edition.
> >>
> >> "Assuming this is possible, if 1 server were to go down, the 2nd
> >> would pickup."
> >> That's cluster-failover, not network load balancing.
> >>
> >> Maybe look at a product called Britestore from Computer
> >> Associates. I'm not an expert on what it can do but I believe
> >> it's a lower cost alternative to clustering.
> >>
> >> hth
> >> Eric
> >>
> >>
> >> RP wrote:
> >> Hi all, in our current environment we have 1 server running sql.
> >> After making a review of our disaster recovery strategy and
> >> readiness we have decided to bring in a 2nd server to function
> >> as a standby failover. Due to the price limitations with SQL2KEE
> >> we have decided to stick with Standard Edition. Here is how we
> >> intend to set it up and wanted to see whether this was feasible
> >> and were there any downsides to this architecture.
> >>
> >> 1. 2 Servers running Windows Server 2003 Standard in a NLB
> >> Cluster.
> >> 2. Both Servers will have SQL2K Standard Installed
> >> 3. Database files (mdf & ldf) will reside on a Dell PowerVault
> >> NAS Server
> >> 4. Databases on each server configured to point to same file on
> >> NAS Server.
> >>
> >> Assuming this is possible, if 1 server were to go down, the 2nd
> >> would pickup. Now we would be in a ditch if the NAS Server were
> >> to go down but with the Raid 5 measures in place we should be OK.
> >>
> >> Any suggestions/recommendations are greatly appreciated.
> >>
> >> thanks a bunch!
>|||There are plenty of bits of code out there (check google) for home grown log
shipping and there was also code in the SQL2000 resource kit as well. If
you want unattended and virtually instantaneous fail over you need
clustering. Log shipping can get you close if you create a good enough
script or use the Log shipping wizard in Enterprise edition. But you can't
have your cake and eat it too in this case. If you don't want to spend the
money to do it correctly you have to live with what options exist without
these features. 1.5GB is pretty small and should work fine for both
replication and log shipping. I would recommend log shipping over
replication for a warm stand by solution.
--
Andrew J. Kelly SQL MVP
"RP" <rp@.nospam.com> wrote in message
news:eQa2ZathEHA.2540@.TK2MSFTNGP10.phx.gbl...
> Eric, I have tried the "poor mans" log shipping method. It works fine for
> smaller databases. It never did work on one of my larger databases which
was
> 1.5GB+. I kept getting timeout errors which I could never figure out why.
I
> guess some sort of database replication software would do the trick. The
key
> is frequency of the replication. Does anyone offer near-instant
frequencies?
> If so, I guess in theory if my primary went down all I would have to do is
> switch the config files to the backup server and all the data would be
there
> ready to go...
> thanks!
> "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in message
> news:%23JVwD1shEHA.1184@.TK2MSFTNGP12.phx.gbl...
> > Log Shipping. With SQL 2k EE (which you already said you don't want it)
> you
> > get log shipping. But I have seen articles and web sites often
describing
> > how to set up a poor man's log shipping.
> >
> > Search MSDN, Google, and some of the popular SQL Server web sites for
log
> > shipping on SQL Server Standard Edition.
> >
> > But you still have the issue of clients now need to point to the new
> server.
> > Depending on what front-end application you use and how it's set up, it
> > could be as easy as changing a server-based config/ini/xml file or it
> could
> > be as painful as sneakering (walking to each pc and updating a file or
> odbc
> > connection.)
> >
> > hth
> > Eric
> >
> >
> > RP wrote:
> > > Andrew, thanks for the MSKB. So I guess that option is ruled out.
> > > Well I can have a standby server, but manual switching of
> > > configuration files is something I dont want to do. I would rather
> > > have the standby server pickup automatically and then I can work on
> > > the primary server to bring it back up. What other options do I have
> > > here without having to go to SQLEE & Windows Advanced Server
> > > Clustering. Are there any cost-effective database replication
> > > software out there that can keep the the standby server in sync with
> > > the primary?
> > >
> > > thanks!
> > >
> > > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > > news:%23xD5AhshEHA.596@.TK2MSFTNGP11.phx.gbl...
> > >> As Eric states you can not use NLB to load share with SQL Server
> > >> unless the two DB's are essentially read only. YOu can have a
> > >> standby server but you don't need NLB for that you can simply change
> > >> the way the clients are pointing and redirect them to the other
> > >> server when the first is down. You should also reconsider using NAS
> > >> for your SQL Server files as it is basically unsupported except for
> > >> one specific condition and the DELL solution is not it. See here
> > >> for more info:
> > >>
> > >>
> > >
> >
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261&Product=sql2k
> > >>
> > >> --
> > >> Andrew J. Kelly SQL MVP
> > >>
> > >>
> > >> "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in
> > >> message news:uwRugZshEHA.3932@.TK2MSFTNGP09.phx.gbl...
> > >> It wasn't my department; all I know was NLB the problem, sorry I
> > >> can't explain further. But I think it was IIS that was down, not
> > >> the entire server, so the server itself still was saying "yes send
> > >> traffic to this IP/Port".
> > >>
> > >> But still, you're thinking about load balancing somethign that
> > >> can't be load balanced. You can't have 2 sql server instances
> > >> communicating with the same MDF/NDF/LDFs at the same time.
> > >>
> > >>
> > >>
> > >> RP wrote:
> > >> Eric, thanks for your response. When you say you had a bad
> > >> experience with NLB could you please elaborate? We are about to
> > >> implement an NLB solution and I would like to know what to expect
> > >> here and whether it is the right solution. Could it be the rules
> > >> on NLB Manager were misconfigured and that is why it still sent
> > >> traffic to the server that was down? Also was the server totally
> > >> down or was it up but just IIS was down?
> > >>
> > >> thanks!
> > >>
> > >> "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in
> > >> message news:ebnwwjrhEHA.1888@.TK2MSFTNGP10.phx.gbl...
> > >> I'm not sure if that would work. The NAS device is a server by
> > >> itself. How will SQL Server connect to the mdf/ldf files?
> > >> Through a shared folder to another server? I don't think that
> > >> works. Even if it did, without direct attach, i.e., fiber/SAN, I
> > >> assume you'd see some corruption due to network latency.
> > >>
> > >> FWIW, NLB for Win Server 2003 was horrible for my company WRT IIS.
> > >> It still sent traffic to a server that was down because as far as
> > >> it was concerned, the load on the downed server was lower than the
> > >> server that was up.
> > >>
> > >> Incindentally, I've seen SQL 2k EE on ebay for what we paid for
> > >> Standard edition.
> > >>
> > >> "Assuming this is possible, if 1 server were to go down, the 2nd
> > >> would pickup."
> > >> That's cluster-failover, not network load balancing.
> > >>
> > >> Maybe look at a product called Britestore from Computer
> > >> Associates. I'm not an expert on what it can do but I believe
> > >> it's a lower cost alternative to clustering.
> > >>
> > >> hth
> > >> Eric
> > >>
> > >>
> > >> RP wrote:
> > >> Hi all, in our current environment we have 1 server running sql.
> > >> After making a review of our disaster recovery strategy and
> > >> readiness we have decided to bring in a 2nd server to function
> > >> as a standby failover. Due to the price limitations with SQL2KEE
> > >> we have decided to stick with Standard Edition. Here is how we
> > >> intend to set it up and wanted to see whether this was feasible
> > >> and were there any downsides to this architecture.
> > >>
> > >> 1. 2 Servers running Windows Server 2003 Standard in a NLB
> > >> Cluster.
> > >> 2. Both Servers will have SQL2K Standard Installed
> > >> 3. Database files (mdf & ldf) will reside on a Dell PowerVault
> > >> NAS Server
> > >> 4. Databases on each server configured to point to same file on
> > >> NAS Server.
> > >>
> > >> Assuming this is possible, if 1 server were to go down, the 2nd
> > >> would pickup. Now we would be in a ditch if the NAS Server were
> > >> to go down but with the Raid 5 measures in place we should be OK.
> > >>
> > >> Any suggestions/recommendations are greatly appreciated.
> > >>
> > >> thanks a bunch!
> >
> >
>

Friday, March 30, 2012

Load balancing.

load balancing with standard edition

Load balancing with Sql Server 2005 in Active/Active mode

Load balancing with SQl server 2005 cluster in active/active configuration

Load balancing with SQl server 2005 cluster in active/active configuration

Load Balancing using Service Broker

Load Balancing SSAS Servers

Load balancing SQL Server

Load balancing SQL Server

Wednesday, March 28, 2012

Load balancing SQL Server

load balancing question

load balancing question

load balancing question

load balancing question

load balancing question

List availables tables

Blog Archive

About Me