Choosing correct high availability setup

I have a client that wants me to start hosting their SQL and IIS for them.
They want to have the ability to have a "hot spare" in case the primary SQL
server fails. I have read and read until my eyes hurt about clustering and
mirroring and I am getting more confused on how to proceed.
My first question is, should I even be using IIS on the SQL box at all?
They have a web interface that gets it data from SQL.
My second question is, if I host their web site on a separate IIS box is
there any reason that I shouldn't go with database mirroring instead of any
other option?
Thanks for any help you can provide.
Marty
Never do both on the same box, read this -
http://msmvps.com/blogs/clusterhelp/archive/2006/02/17/84035.aspx.
Server Clustering with make the entire node HA, SQL mirroring will make that
database HA. Which does your customer require?
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:F03EC295-F9C8-4602-B787-BFBCC5841E48@.microsoft.com...
>I have a client that wants me to start hosting their SQL and IIS for them.
> They want to have the ability to have a "hot spare" in case the primary
> SQL
> server fails. I have read and read until my eyes hurt about clustering
> and
> mirroring and I am getting more confused on how to proceed.
> My first question is, should I even be using IIS on the SQL box at all?
> They have a web interface that gets it data from SQL.
> My second question is, if I host their web site on a separate IIS box is
> there any reason that I shouldn't go with database mirroring instead of
> any
> other option?
> Thanks for any help you can provide.
> Marty
|||Well that is what I had always thought, but you see more and more people
consolidating these functions.
Basically there will be an Access database that arrives via FTP at the web
server, the SQL server will pick it up from a shared drive and import it.
That will happen about every 15 minutes or so. The same web server will host
a site that the client can access to see real time data that it pulls from
the SQL server. The only snag is that if the primary SQL server goes down
for some reason, IIS will not know that it has to get that data from the
backup server if I am using only mirroring.
"Rodney R. Fournier [MVP]" wrote:

> Never do both on the same box, read this -
> http://msmvps.com/blogs/clusterhelp/archive/2006/02/17/84035.aspx.
> Server Clustering with make the entire node HA, SQL mirroring will make that
> database HA. Which does your customer require?
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:F03EC295-F9C8-4602-B787-BFBCC5841E48@.microsoft.com...
>
>
|||Got it, use NLB for the IIS servers, Server Clustering for the backend
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:ECAE918E-4B1E-4339-A4CA-7F5752DC6768@.microsoft.com...[vbcol=seagreen]
> Well that is what I had always thought, but you see more and more people
> consolidating these functions.
> Basically there will be an Access database that arrives via FTP at the web
> server, the SQL server will pick it up from a shared drive and import it.
> That will happen about every 15 minutes or so. The same web server will
> host
> a site that the client can access to see real time data that it pulls from
> the SQL server. The only snag is that if the primary SQL server goes down
> for some reason, IIS will not know that it has to get that data from the
> backup server if I am using only mirroring.
>
> "Rodney R. Fournier [MVP]" wrote:
|||NLB?
If I use clustering for the backend, will the web server be able to keep
serving the data from the SQL database to the web interface in the case of a
disaster? The web site will be looking for a specific instance of SQL
correct?
"Rodney R. Fournier [MVP]" wrote:

> Got it, use NLB for the IIS servers, Server Clustering for the backend
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:ECAE918E-4B1E-4339-A4CA-7F5752DC6768@.microsoft.com...
>
>
|||Yes NLB (Network Load Balancing), IIS is not made for Server Clustering, and
won't be supported/allowed with Windows Server 2008 Failover Clustering.
No matter how you do your SQL, IIS will need to know the instance name to
connect.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:1429702D-83EF-4E83-B2DD-0C127B41DAC8@.microsoft.com...[vbcol=seagreen]
> NLB?
> If I use clustering for the backend, will the web server be able to keep
> serving the data from the SQL database to the web interface in the case of
> a
> disaster? The web site will be looking for a specific instance of SQL
> correct?
> "Rodney R. Fournier [MVP]" wrote:
|||Okay I will go with NLB for IIS. As far as the clustering for SQL goes, I am
very new on this subject. If one SQL box in the cluster fails, will the
other SQL box in that cluster pick up where the first left off and assume the
failed box's identity as far as IP address, DNS name, etc...?
Thanks for all of your help Rodney. I am glad there is a forum where people
like me can get real world help from people like yourself. Pat yourself on
the back man!!!
"Rodney R. Fournier [MVP]" wrote:

> Yes NLB (Network Load Balancing), IIS is not made for Server Clustering, and
> won't be supported/allowed with Windows Server 2008 Failover Clustering.
> No matter how you do your SQL, IIS will need to know the instance name to
> connect.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:1429702D-83EF-4E83-B2DD-0C127B41DAC8@.microsoft.com...
>
>
|||It will failover to another node and continue on, but at a cost. SQL not
running on the node until the failover, so the databases goes through the
normal SQL startup, DB integrity check, roll back non-committed
transactions, roll forward committed ones, etc. Your application(s) have to
be cluster aware to handle the failover. Ping tests will fail during the
process, though maybe only 4-5 depending on the network, hardware, DB sizes,
etc.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:20D5F814-D38D-40B0-8BDE-2C50893F6509@.microsoft.com...[vbcol=seagreen]
> Okay I will go with NLB for IIS. As far as the clustering for SQL goes, I
> am
> very new on this subject. If one SQL box in the cluster fails, will the
> other SQL box in that cluster pick up where the first left off and assume
> the
> failed box's identity as far as IP address, DNS name, etc...?
> Thanks for all of your help Rodney. I am glad there is a forum where
> people
> like me can get real world help from people like yourself. Pat yourself
> on
> the back man!!!
> "Rodney R. Fournier [MVP]" wrote:
|||The only application that will depend on the SQL server would be IIS. How is
IIS going to react if one of the cluster nodes fail? I guess I would need to
point it to the alternate node's instance.
"Rodney R. Fournier [MVP]" wrote:

> It will failover to another node and continue on, but at a cost. SQL not
> running on the node until the failover, so the databases goes through the
> normal SQL startup, DB integrity check, roll back non-committed
> transactions, roll forward committed ones, etc. Your application(s) have to
> be cluster aware to handle the failover. Ping tests will fail during the
> process, though maybe only 4-5 depending on the network, hardware, DB sizes,
> etc.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
> message news:20D5F814-D38D-40B0-8BDE-2C50893F6509@.microsoft.com...
>
>
|||Correct as Geoff "the SQL God and good buddy of mine" already stated.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Marty Shifflett" <MartyShifflett@.discussions.microsoft.com> wrote in
message news:7871ABB2-D689-41A6-B9F7-30C5482A4EE3@.microsoft.com...[vbcol=seagreen]
> So the node that picks up truly is a "clone" of the failed one? Well then
> it
> sounds like I may be better off going with clustering than mirroring in my
> situation wouldn't you say?
> "Rodney R. Fournier [MVP]" wrote:

Checking a database for physical damage

Hi,
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?
If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegro ups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>
|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:[vbcol=seagreen]
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegro ups.com...
|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegro ups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>

Checking a database for physical damage

Hi,
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> > Hi,
> >
> > Today I had one disk of my server array crashed, it is on raid 5 with
> > hot spare disks.
> > SQL Server reported some errors on event logger but apparently the hot
> > spare disks were used correctly since it is working right now.
> >
> > So far is fine, but just in case I ran this command:
> > DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> >
> > And it showed no errors.
> > Are there any other commands that I can run to check for integrity in a
> > database in a case like this?
> >|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegroups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>> If the CHECKDB was clean, you are in good shape. You might also include
>> DBCC CHECKCATALOG as part of your scheduled integrity checks but it
>> unlikely
>> that a hardware error will introduce catalog problems.
>> You mention SQL Server errors but SQL Server should be oblivious to the
>> problem, assuming the controller gracefully handles such errors. If the
>> errors are due to the I/O subsystem become sluggish during the rebuild,
>> check to see if your array controller allows you to configure the rebuild
>> priority.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> <craigkenisston@.hotmail.com> wrote in message
>> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
>> > Hi,
>> >
>> > Today I had one disk of my server array crashed, it is on raid 5 with
>> > hot spare disks.
>> > SQL Server reported some errors on event logger but apparently the hot
>> > spare disks were used correctly since it is working right now.
>> >
>> > So far is fine, but just in case I ran this command:
>> > DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
>> >
>> > And it showed no errors.
>> > Are there any other commands that I can run to check for integrity in a
>> > database in a case like this?
>> >
>

Checking a database for physical damage

Hi,
Today I had one disk of my server array crashed, it is on raid 5 with
hot spare disks.
SQL Server reported some errors on event logger but apparently the hot
spare disks were used correctly since it is working right now.
So far is fine, but just in case I ran this command:
DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
And it showed no errors.
Are there any other commands that I can run to check for integrity in a
database in a case like this?If the CHECKDB was clean, you are in good shape. You might also include
DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlikely
that a hardware error will introduce catalog problems.
You mention SQL Server errors but SQL Server should be oblivious to the
problem, assuming the controller gracefully handles such errors. If the
errors are due to the I/O subsystem become sluggish during the rebuild,
check to see if your array controller allows you to configure the rebuild
priority.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...
> Hi,
> Today I had one disk of my server array crashed, it is on raid 5 with
> hot spare disks.
> SQL Server reported some errors on event logger but apparently the hot
> spare disks were used correctly since it is working right now.
> So far is fine, but just in case I ran this command:
> DBCC CHECKDB (MyProd) WITH ALL_ERRORMSGS
> And it showed no errors.
> Are there any other commands that I can run to check for integrity in a
> database in a case like this?
>|||Dan,
Thank for your comment. I did not expect to have SQL Server errors
either. But they were.
I guess as you suggest, the problem went during the rebuild, not
directly because the failure.
The errors I had were:
18052 :
Error: 3624, Severity: 20, State: 1.
And this:
17066 :
SQL Server Assertion: File:
< p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
I also had other errores, but the only message they have is about
telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
) cannot be found. "
Regards,
Dan Guzman wrote:[vbcol=seagreen]
> If the CHECKDB was clean, you are in good shape. You might also include
> DBCC CHECKCATALOG as part of your scheduled integrity checks but it unlike
ly
> that a hardware error will introduce catalog problems.
> You mention SQL Server errors but SQL Server should be oblivious to the
> problem, assuming the controller gracefully handles such errors. If the
> errors are due to the I/O subsystem become sluggish during the rebuild,
> check to see if your array controller allows you to configure the rebuild
> priority.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> <craigkenisston@.hotmail.com> wrote in message
> news:1165648156.056709.236200@.f1g2000cwa.googlegroups.com...|||The assertion error indicates garbage was returned to SQL Server. I find it
strange that the controller would return bad data during the rebuild. Even
during the rebuild, the controller ought to be able to reconstruct the
original data and return it to SQL, albeit more slowly than a healthy array.
Anyway, I'm glad your DBCCs are clean and you are out of the woods.
Hope this helps.
Dan Guzman
SQL Server MVP
<craigkenisston@.hotmail.com> wrote in message
news:1165681327.852727.312450@.f1g2000cwa.googlegroups.com...
> Dan,
> Thank for your comment. I did not expect to have SQL Server errors
> either. But they were.
> I guess as you suggest, the problem went during the rebuild, not
> directly because the failure.
> The errors I had were:
> 18052 :
> Error: 3624, Severity: 20, State: 1.
> And this:
> 17066 :
> SQL Server Assertion: File:
> < p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'.
>
> I also had other errores, but the only message they have is about
> telling "The description for Event ID ( XXXX ) in Source ( MSSQLSERVER
> ) cannot be found. "
>
> Regards,
>
> Dan Guzman wrote:
>