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.
Any suggestions/recommendations are greatly appreciated.
thanks a bunch!
For "emergency standby SQL Server" options, you way want to look at data
replicaiton tools like Double-Take:
http://www.nsisoftware.com/pro/doubletake/
It will replicate your database to another standby SQL Server. No need to
build a cluster, or try and share the same NAS between 2 SQL Servers.
Chris Skorlinski
Microsoft SQL Server Support
Please reply directly to the thread with any updates.
This posting is provided "as is" with no warranties and confers no rights.
Showing posts with label current. Show all posts
Showing posts with label current. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
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/default...&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/default...&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/default...&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/default...&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[vbcol=seagreen]
> 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
> server.
> could
> odbc
>
http://support.microsoft.com/default...&Product=sql2k
>
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/default...&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/default...&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/default...&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/default...&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[vbcol=seagreen]
> 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
> server.
> could
> odbc
>
http://support.microsoft.com/default...&Product=sql2k
>
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
>
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
>
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!
> >
> >
>
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!
> >
> >
>
Wednesday, March 21, 2012
listing of sp_who
> You can use sp_helptext to get the definition of any procedure in the
> current database, but in SQL 2005, the definitions of the system
> procedures are in a database you can't get to (mssqlsystemresource).
This is not true for any of the sp_ procedures I've bothered to try,
including sp_who. The following works on 9.00.2047 and 9.00.3027:
EXEC master.sys.sp_helptext 'sp_help';
EXEC master.sys.sp_helptext 'sp_who';
EXEC master.sys.sp_helptext 'sp_who2';
EXEC master.sys.sp_helptext 'sp_helpdb';
EXEC master.sys.sp_helptext 'sp_configure';
EXEC master.sys.sp_helptext 'sp_spaceused';
EXEC master.sys.sp_helptext 'sp_helptext';
EXEC master.sys.sp_helptext 'sp_changedbowner';
EXEC master.sys.sp_helptext 'sp_columns_90_rowset';
EXEC master.sys.sp_helptext 'sp_addlogin';
Thanks. After reading your other reply, I checked, and saw that this was
true. In an early beta of SQL2005 you couldn't use sp_helptext to get the
system procs; I've even got some old notes about that from one of the guys
on the dev team. So they changed this, probably after getting lots of
complaints!
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23llR$UpKHHA.3488@.TK2MSFTNGP02.phx.gbl...
> This is not true for any of the sp_ procedures I've bothered to try,
> including sp_who. The following works on 9.00.2047 and 9.00.3027:
>
> EXEC master.sys.sp_helptext 'sp_help';
> EXEC master.sys.sp_helptext 'sp_who';
> EXEC master.sys.sp_helptext 'sp_who2';
> EXEC master.sys.sp_helptext 'sp_helpdb';
> EXEC master.sys.sp_helptext 'sp_configure';
> EXEC master.sys.sp_helptext 'sp_spaceused';
> EXEC master.sys.sp_helptext 'sp_helptext';
> EXEC master.sys.sp_helptext 'sp_changedbowner';
> EXEC master.sys.sp_helptext 'sp_columns_90_rowset';
> EXEC master.sys.sp_helptext 'sp_addlogin';
>
>
|||> Thanks. After reading your other reply, I checked, and saw that this was
> true. In an early beta of SQL2005 you couldn't use sp_helptext to get the
> system procs; I've even got some old notes about that from one of the guys
> on the dev team. So they changed this, probably after getting lots of
> complaints!
Yes, and I remember after they fixed that, complaining that you couldn't do:
EXEC sp_helptext 'sys.sp_who'
But you could do:
EXEC sp_helptext 'sp_who'
That has changed, because now you can do the former (and in the case of
dynamic management objects, you can't do the latter).
A
> current database, but in SQL 2005, the definitions of the system
> procedures are in a database you can't get to (mssqlsystemresource).
This is not true for any of the sp_ procedures I've bothered to try,
including sp_who. The following works on 9.00.2047 and 9.00.3027:
EXEC master.sys.sp_helptext 'sp_help';
EXEC master.sys.sp_helptext 'sp_who';
EXEC master.sys.sp_helptext 'sp_who2';
EXEC master.sys.sp_helptext 'sp_helpdb';
EXEC master.sys.sp_helptext 'sp_configure';
EXEC master.sys.sp_helptext 'sp_spaceused';
EXEC master.sys.sp_helptext 'sp_helptext';
EXEC master.sys.sp_helptext 'sp_changedbowner';
EXEC master.sys.sp_helptext 'sp_columns_90_rowset';
EXEC master.sys.sp_helptext 'sp_addlogin';
Thanks. After reading your other reply, I checked, and saw that this was
true. In an early beta of SQL2005 you couldn't use sp_helptext to get the
system procs; I've even got some old notes about that from one of the guys
on the dev team. So they changed this, probably after getting lots of
complaints!
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23llR$UpKHHA.3488@.TK2MSFTNGP02.phx.gbl...
> This is not true for any of the sp_ procedures I've bothered to try,
> including sp_who. The following works on 9.00.2047 and 9.00.3027:
>
> EXEC master.sys.sp_helptext 'sp_help';
> EXEC master.sys.sp_helptext 'sp_who';
> EXEC master.sys.sp_helptext 'sp_who2';
> EXEC master.sys.sp_helptext 'sp_helpdb';
> EXEC master.sys.sp_helptext 'sp_configure';
> EXEC master.sys.sp_helptext 'sp_spaceused';
> EXEC master.sys.sp_helptext 'sp_helptext';
> EXEC master.sys.sp_helptext 'sp_changedbowner';
> EXEC master.sys.sp_helptext 'sp_columns_90_rowset';
> EXEC master.sys.sp_helptext 'sp_addlogin';
>
>
|||> Thanks. After reading your other reply, I checked, and saw that this was
> true. In an early beta of SQL2005 you couldn't use sp_helptext to get the
> system procs; I've even got some old notes about that from one of the guys
> on the dev team. So they changed this, probably after getting lots of
> complaints!
Yes, and I remember after they fixed that, complaining that you couldn't do:
EXEC sp_helptext 'sys.sp_who'
But you could do:
EXEC sp_helptext 'sp_who'
That has changed, because now you can do the former (and in the case of
dynamic management objects, you can't do the latter).
A
Labels:
current,
database,
definition,
definitions,
listing,
microsoft,
mysql,
oracle,
procedure,
server,
sp_helptext,
sp_who,
sql,
systemgt,
thegt
Monday, March 12, 2012
List of what the server is running
I am working with the web service here, is there any way to get a list back
of all the current reports being ran? thanks!On Mar 2, 11:25 am, "Smokey Grindel" <nos...@.nospam.com> wrote:
> I am working with the web service here, is there any way to get a list back
> of all the current reports being ran? thanks!
I don't have the link but if you check the microsoft report packs I
beleive there are some reports for this...
Cheers,
Ben|||Take a look at the ReportService2005 Web service... There is a job class
that I think does what you wish.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Smokey Grindel" wrote:
> I am working with the web service here, is there any way to get a list back
> of all the current reports being ran? thanks!
>
>|||Just want to verify a "job" is not just a scheduled one but any report or
task that the report server is currently running correct? thanks!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:DEF182B0-BEEE-4F7B-B597-580C91778982@.microsoft.com...
> Take a look at the ReportService2005 Web service... There is a job class
> that I think does what you wish.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Smokey Grindel" wrote:
>> I am working with the web service here, is there any way to get a list
>> back
>> of all the current reports being ran? thanks!
>>
of all the current reports being ran? thanks!On Mar 2, 11:25 am, "Smokey Grindel" <nos...@.nospam.com> wrote:
> I am working with the web service here, is there any way to get a list back
> of all the current reports being ran? thanks!
I don't have the link but if you check the microsoft report packs I
beleive there are some reports for this...
Cheers,
Ben|||Take a look at the ReportService2005 Web service... There is a job class
that I think does what you wish.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Smokey Grindel" wrote:
> I am working with the web service here, is there any way to get a list back
> of all the current reports being ran? thanks!
>
>|||Just want to verify a "job" is not just a scheduled one but any report or
task that the report server is currently running correct? thanks!
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:DEF182B0-BEEE-4F7B-B597-580C91778982@.microsoft.com...
> Take a look at the ReportService2005 Web service... There is a job class
> that I think does what you wish.
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> I support the Professional Association for SQL Server ( PASS) and it''s
> community of SQL Professionals.
>
> "Smokey Grindel" wrote:
>> I am working with the web service here, is there any way to get a list
>> back
>> of all the current reports being ran? thanks!
>>
Friday, March 9, 2012
List of past Dates
Hello,
I want to create a list op dates using the SELECT statement. The dates range
from the current date till the current date - 10 days
The resultset for today should be as follows.
20050221
20050220
20050219
20050218
20050217
20050216
20050215
20050214
20050213
20050212
20050211
How do I create this. The result must be used in an other view, so if
possible I want a view.
Thanks
BartDoes this work for you?
select getdate()
union all
select getdate()-1
union all
select getdate()-2
union all
select getdate()-3
union all
select getdate()-4
union all
select getdate()-5
union all
select getdate()-6
union all
select getdate()-7
union all
select getdate()-8
union all
select getdate()-9
Bojidar Alexandro
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23kUS6iAGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I want to create a list op dates using the SELECT statement. The dates
range
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>|||Hi,
Maybe this will help you.
Tomasz B.
use tempdb
go
create view ten_dates
As
select convert(varchar(8), getdate(), 112) d
union all
select convert(varchar(8), getdate()-1, 112)
union all
select convert(varchar(8), getdate()-2, 112)
union all
select convert(varchar(8), getdate()-3, 112)
union all
select convert(varchar(8), getdate()-4, 112)
union all
select convert(varchar(8), getdate()-5, 112)
union all
select convert(varchar(8), getdate()-6, 112)
union all
select convert(varchar(8), getdate()-7, 112)
union all
select convert(varchar(8), getdate()-8, 112)
union all
select convert(varchar(8), getdate()-9, 112)
union all
select convert(varchar(8), getdate()-10, 112)
"Bart Steur" wrote:
> Hello,
> I want to create a list op dates using the SELECT statement. The dates ran
ge
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>
>|||Bart
Look at the script written by Irzik Ben-Gan
CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
DECLARE @.rc AS INT
SET @.rc = 1
INSERT INTO @.Dates VALUES(@.from)
WHILE @.from + @.rc * 2 - 1 <= @.to
BEGIN
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
SET @.rc = @.rc * 2
END
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
WHERE dt + @.rc <= @.to
RETURN
END
GO
SELECT dt FROM fn_dates('20050211', '20050221')
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23kUS6iAGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I want to create a list op dates using the SELECT statement. The dates
range
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>|||SELECT dt
FROM Calendar
WHERE dt >= DATEDIFF(DAY,10,CURRENT_TIMESTAMP)
http://www.aspfaq.com/show.asp?id=2519
David Portas
SQL Server MVP
--|||Thanks Guys,
It worked.
Bart
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:F3CD7AEB-9F22-435F-A569-32CE2D005F45@.microsoft.com...
> SELECT dt
> FROM Calendar
> WHERE dt >= DATEDIFF(DAY,10,CURRENT_TIMESTAMP)
> http://www.aspfaq.com/show.asp?id=2519
> --
> David Portas
> SQL Server MVP
> --
>
I want to create a list op dates using the SELECT statement. The dates range
from the current date till the current date - 10 days
The resultset for today should be as follows.
20050221
20050220
20050219
20050218
20050217
20050216
20050215
20050214
20050213
20050212
20050211
How do I create this. The result must be used in an other view, so if
possible I want a view.
Thanks
BartDoes this work for you?
select getdate()
union all
select getdate()-1
union all
select getdate()-2
union all
select getdate()-3
union all
select getdate()-4
union all
select getdate()-5
union all
select getdate()-6
union all
select getdate()-7
union all
select getdate()-8
union all
select getdate()-9
Bojidar Alexandro
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23kUS6iAGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I want to create a list op dates using the SELECT statement. The dates
range
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>|||Hi,
Maybe this will help you.
Tomasz B.
use tempdb
go
create view ten_dates
As
select convert(varchar(8), getdate(), 112) d
union all
select convert(varchar(8), getdate()-1, 112)
union all
select convert(varchar(8), getdate()-2, 112)
union all
select convert(varchar(8), getdate()-3, 112)
union all
select convert(varchar(8), getdate()-4, 112)
union all
select convert(varchar(8), getdate()-5, 112)
union all
select convert(varchar(8), getdate()-6, 112)
union all
select convert(varchar(8), getdate()-7, 112)
union all
select convert(varchar(8), getdate()-8, 112)
union all
select convert(varchar(8), getdate()-9, 112)
union all
select convert(varchar(8), getdate()-10, 112)
"Bart Steur" wrote:
> Hello,
> I want to create a list op dates using the SELECT statement. The dates ran
ge
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>
>|||Bart
Look at the script written by Irzik Ben-Gan
CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
AS
BEGIN
DECLARE @.rc AS INT
SET @.rc = 1
INSERT INTO @.Dates VALUES(@.from)
WHILE @.from + @.rc * 2 - 1 <= @.to
BEGIN
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
SET @.rc = @.rc * 2
END
INSERT INTO @.Dates
SELECT dt + @.rc FROM @.Dates
WHERE dt + @.rc <= @.to
RETURN
END
GO
SELECT dt FROM fn_dates('20050211', '20050221')
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:%23kUS6iAGFHA.560@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I want to create a list op dates using the SELECT statement. The dates
range
> from the current date till the current date - 10 days
> The resultset for today should be as follows.
> 20050221
> 20050220
> 20050219
> 20050218
> 20050217
> 20050216
> 20050215
> 20050214
> 20050213
> 20050212
> 20050211
>
> How do I create this. The result must be used in an other view, so if
> possible I want a view.
> Thanks
> Bart
>|||SELECT dt
FROM Calendar
WHERE dt >= DATEDIFF(DAY,10,CURRENT_TIMESTAMP)
http://www.aspfaq.com/show.asp?id=2519
David Portas
SQL Server MVP
--|||Thanks Guys,
It worked.
Bart
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:F3CD7AEB-9F22-435F-A569-32CE2D005F45@.microsoft.com...
> SELECT dt
> FROM Calendar
> WHERE dt >= DATEDIFF(DAY,10,CURRENT_TIMESTAMP)
> http://www.aspfaq.com/show.asp?id=2519
> --
> David Portas
> SQL Server MVP
> --
>
Wednesday, March 7, 2012
List of current publications and subscribers
Is there a stored procedure or some other code ie sql-dmo or vb.net
that will list all the publications of a certain server and another to
list all the subscribtions either at a certian publication or at the
whole server?
Thanks
Shane Lim
Shane,
have a look at sp_helppublication and sp_helpsubscription
Rgds,
Paul Ibison (SQL Server MVP)
that will list all the publications of a certain server and another to
list all the subscribtions either at a certian publication or at the
whole server?
Thanks
Shane Lim
Shane,
have a look at sp_helppublication and sp_helpsubscription
Rgds,
Paul Ibison (SQL Server MVP)
Subscribe to:
Posts (Atom)