Cluster Failover

We have a 3rd party vendor application that runs on IIS and connects to a
back end SQL Cluster. Sometimes a user will try and run a commission report
and the SQL server takes a big hit on the CPU's (Dual 2.0 Ghz Xeon's) and I
will get a error in MOM 2005 that the "Server Performance Thresholds
SQLSERVR Process > 90% CPU for 15 minutes" Of course the end user will try
and run it two or three more times taking up more resources.
If I do a fail over to the other SQL server in the cluster the CPU's will
drop down to their normal ranges and everything works fine.
My question is when performing the "failover" that 10 - 15 seconds it takes
to do this what happens to any data that is trying to write to the database?
Is it lost? And can this cause corruption in the database itself?
Thanks!
Failovers don't cause corruption. Rather, any active transactions are
rolled back when the backup node takes over from the primary node.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:DF8080ED-0DF2-42C7-B1DA-4D9375D0B2EA@.microsoft.com...
We have a 3rd party vendor application that runs on IIS and connects to a
back end SQL Cluster. Sometimes a user will try and run a commission report
and the SQL server takes a big hit on the CPU's (Dual 2.0 Ghz Xeon's) and I
will get a error in MOM 2005 that the "Server Performance Thresholds
SQLSERVR Process > 90% CPU for 15 minutes" Of course the end user will try
and run it two or three more times taking up more resources.
If I do a fail over to the other SQL server in the cluster the CPU's will
drop down to their normal ranges and everything works fine.
My question is when performing the "failover" that 10 - 15 seconds it takes
to do this what happens to any data that is trying to write to the database?
Is it lost? And can this cause corruption in the database itself?
Thanks!
|||When you mean "rolled back" the transactions are basically held in cache
until the backup server has taken complete control? Is that correct?
One user received the following error during failover in their web session:
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your
network documentation. in Microsoft OLE DB Provider for SQL Server
Is this a concern or only the period before the failover is complete?
Thanks!
"Tom Moreau" wrote:

> Failovers don't cause corruption. Rather, any active transactions are
> rolled back when the backup node takes over from the primary node.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:DF8080ED-0DF2-42C7-B1DA-4D9375D0B2EA@.microsoft.com...
> We have a 3rd party vendor application that runs on IIS and connects to a
> back end SQL Cluster. Sometimes a user will try and run a commission report
> and the SQL server takes a big hit on the CPU's (Dual 2.0 Ghz Xeon's) and I
> will get a error in MOM 2005 that the "Server Performance Thresholds
> SQLSERVR Process > 90% CPU for 15 minutes" Of course the end user will try
> and run it two or three more times taking up more resources.
> If I do a fail over to the other SQL server in the cluster the CPU's will
> drop down to their normal ranges and everything works fine.
> My question is when performing the "failover" that 10 - 15 seconds it takes
> to do this what happens to any data that is trying to write to the database?
> Is it lost? And can this cause corruption in the database itself?
> Thanks!
>
>
|||Not exactly. Anything in cache evaporates. Transactions are written to the
transaction log. If a COMMIT record is not written, then when SQL Server
comes up, all of the work done thus far in that transaction is backed out.
This is true whether you are using a cluster or not.
The network errors go away once SQL Server has come back up again. Clean
apps will try to reconnect.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:49B44171-B185-463B-833E-E4190AD923A9@.microsoft.com...
When you mean "rolled back" the transactions are basically held in cache
until the backup server has taken complete control? Is that correct?
One user received the following error during failover in their web session:
[DBNETLIB][ConnectionRead (recv()).]General network error. Check your
network documentation. in Microsoft OLE DB Provider for SQL Server
Is this a concern or only the period before the failover is complete?
Thanks!
"Tom Moreau" wrote:

> Failovers don't cause corruption. Rather, any active transactions are
> rolled back when the backup node takes over from the primary node.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> ..
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:DF8080ED-0DF2-42C7-B1DA-4D9375D0B2EA@.microsoft.com...
> We have a 3rd party vendor application that runs on IIS and connects to a
> back end SQL Cluster. Sometimes a user will try and run a commission
> report
> and the SQL server takes a big hit on the CPU's (Dual 2.0 Ghz Xeon's) and
> I
> will get a error in MOM 2005 that the "Server Performance Thresholds
> SQLSERVR Process > 90% CPU for 15 minutes" Of course the end user will
> try
> and run it two or three more times taking up more resources.
> If I do a fail over to the other SQL server in the cluster the CPU's will
> drop down to their normal ranges and everything works fine.
> My question is when performing the "failover" that 10 - 15 seconds it
> takes
> to do this what happens to any data that is trying to write to the
> database?
> Is it lost? And can this cause corruption in the database itself?
> Thanks!
>
>

client/server ODBC problem

I have an application on:

W2K client
Sql 2000 + No SP
MS ODBC V3.8
C++

The application, running on the client, connects to SQL server for many simultaneous queries involving select, insert and update. Application creats a thread for each query. Each thread connects to the sql server via ODBC using sqlconnect.

However I find that the individual thread do not close properly and stay open even after sqldisconnect has been issued on the client. At one point I have over 1200 threads on client application trying simultaneously to connect to the server and the sqlserver is holding with 276 threads open.

I do not understand why the ODBC connection stays open even after sqldisconnect is issued. I have even tried disabling connection pooling, to no effect. The performance is very poor.

How can I issue a large number of queries to same sql server via ODBC while guaranteeing performance?Enable connection pooling... What you are fighting (the pooling) is almost certainly exactly what you need. It will drastically reduce the number of connections (spids) used by your application, as well as reducing the latency and memory usage of your client application.

-PatP|||Enable connection pooling... What you are fighting (the pooling) is almost certainly exactly what you need. It will drastically reduce the number of connections (spids) used by your application, as well as reducing the latency and memory usage of your client application.

-PatP
Hi Pat,
Have you come across this problem before ?
Are you aware of a solution that I can implement to get round this problem?
I would greatly appreciate your input.
Best Regards,

Frank.

Client components

I have an application that connects to a SQL 2000 database on another
machine.
We are upgrading this to work with SQL 2005 or SQL 2000.
The application uses SQLDMO objects and ADO objects (written in C++)
At the moment we include sqlredist.exe in the install package for the
client.
We have found that we also need to install the Backwards Compatability
components to let us use SQLDMO with SQL 2005.
What is the smallest (and what are they) set of components we need to
install on the client to have our application work with either SQL 2000 or
SQL 2005.
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netYou should be able to use just the installer for the
Backwards Compatibility Components - SQLServer2005_BC.msi
You can download it from:
http://www.microsoft.com/downloads/...&displaylang=en
-Sue
On Thu, 1 Jun 2006 21:04:58 -0700, "Michael Tissington"
<mtissington@.newsgroups.nospam> wrote:

>I have an application that connects to a SQL 2000 database on another
>machine.
>We are upgrading this to work with SQL 2005 or SQL 2000.
>The application uses SQLDMO objects and ADO objects (written in C++)
>At the moment we include sqlredist.exe in the install package for the
>client.
>We have found that we also need to install the Backwards Compatability
>components to let us use SQLDMO with SQL 2005.
>What is the smallest (and what are they) set of components we need to
>install on the client to have our application work with either SQL 2000 or
>SQL 2005.