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:
>|||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:[vbcol=seagreen]
> 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...|||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/defaul...1&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:
>|||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/defaul...1&Product=sql2k
> --
> Andrew J. Kelly SQL MVP
>
> "Eric Sabine" <mopar41@.hyotyt_mail_nounderscores.com> wrote in message
> news:uwRugZshEHA.3932@.TK2MSFTNGP09.phx.gbl...
> load
> same
>|||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...
>
http://support.microsoft.com/defaul...1&Product=sql2k[vbcol=seagreen]|||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:
>
http://support.microsoft.com/defaul...1&Product=sql2k
>|||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...
> you
describing[vbcol=seagreen]
log[vbcol=seagreen]
> server.
> could
> odbc
>
http://support.microsoft.com/defaul...1&Product=sql2k
>