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!
>
>

CLR Procedure error with third party .dll

I have a CLR procedure with the below code calling a third party .dll in the system32 directory of the SQL server:

using System;

using System.Collections;

using System.Data;

using System.Data.SqlTypes;

using System.Runtime.InteropServices;

using System.Text;

using Microsoft.SqlServer.Server;

public class CorrectAddressProcedures

{

[DllImport(@."C:\WINDOWS\system32\CorrectA.dll", EntryPoint = "FindCityCounty")]

public static extern int FindCityCounty(StringBuilder zip, StringBuilder cityname, StringBuilder state, StringBuilder countyname, StringBuilder countynum);

public static String Space(int len)

{

StringBuilder str = new StringBuilder("");

str.Append(' ', len);

return str.ToString();

}

[Microsoft.SqlServer.Server.SqlProcedure]

public static void spFindCityCounty(SqlString _zip, out SqlString _cityname, out SqlString _state, out SqlString _countyname, out SqlString _countynum)

{

int rc;

StringBuilder zip = new StringBuilder(5);

StringBuilder cityname = new StringBuilder(28);

StringBuilder state = new StringBuilder(2);

StringBuilder countyname = new StringBuilder(25);

StringBuilder countynum = new StringBuilder(3);

try

{

cityname.Append(Space(28));

state.Append(Space(2));

countyname.Append(Space(25));

countynum.Append(Space(3));

zip.Append(_zip);

rc = FindCityCounty(zip, cityname, state, countyname, countynum);

_cityname = cityname.ToString();

_state = state.ToString();

_countyname = countyname.ToString();

_countynum = countynum.ToString();

}

catch (Exception ex)

{

throw (ex);

}

}

}

I am getting the error

Msg 6522, Level 16, State 1, Procedure spFindCityCounty, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'spFindCityCounty':

System.DllNotFoundException: Unable to load DLL 'C:\WINDOWS\system32\CorrectA.dll': Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)

System.DllNotFoundException:

at CorrectAddressProcedures.spFindCityCounty(SqlString _zip, SqlString& _cityname, SqlString& _state, SqlString& _countyname, SqlString& _countynum)

.

I have restarted the service with memory allocation settings -g512 and -g1024 but still receive the error. The SQL server is an Intel Xeon 2.8GHZ with 2GB of RAM. Any thoughts of how to solve this would be greatly appreciated.

thanks,
Whitney

I failed to mention above that the .dll in question is a rather large one. 154MB to be exact.|||

hi,

how much memory space have you reserved for your SQL 2005 server.

in sql200 Management Studio:

Server Properties\Memory.

Try to enlarge your maximum memory or reduce the minimum memory to zero.

Restart the SQL Server and try again.

regards,

Martin

|||Checking SSMS I'm assuming the defaults were used. The minimum is set to 0MB and maximum is 2147483647MB. I have a startup parameter of 512MB and have also tried 1024MB with no luck.|||

Our vendor sent us a smaller DLL today (98MB vs 154MB) and the CLR procedures now work without code changes. Does anyone know if there is an undocumented ceiling (say around 100MB) that DLLImport has within the SQL CLR?

While I'm happy my code works I would still like to know the deeper detail on why I was getting the error.

Thanks,
Whitney

Client side SQL manager tool?

I host my website with a 3rd party and need something to manage my SQL
database. Can someone please recommend a good client side tool for XP?
Thanks.MS has a free web-based SQL admin tool you might take a look at...
Duh! Now that I think about it, you can still use SQL Enterprise Manager ,
just register and connect to the IP and port number...
Also ask your hosting company, many provide tools for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Smith" <noemail@.please.com> wrote in message
news:lDHjd.39122$Om6.2911@.bignews5.bellsouth.net...
> I host my website with a 3rd party and need something to manage my SQL
> database. Can someone please recommend a good client side tool for XP?
> Thanks.
>|||Hi Bob,
Thanks MVP Wayne for his perfect suggestions and here is some links for
your reference
Download: SQL Server Web Data Administrator
http://www.microsoft.com/downloads/...A798-C57A-419E-
ACBC-2A332CB7F959&displaylang=en
The SQL Server Web Data Administrator enables you to easily manage your SQL
Server data, wherever you are. Using its built-in features, you can do the
following from Microsoft Internet Explorer or your favorite Web browser:
- Create and edit databases in SQL Server 2000 or Microsoft SQL Server
2000 Desktop Engine (MSDE 2000)
- Perform ad-hoc queries against databases and save them to your file
system
- Export and import database schema and data
- Manage users and roles
- View, create and edit stored procedures
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Bob,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Client side SQL manager tool?

I host my website with a 3rd party and need something to manage my SQL
database. Can someone please recommend a good client side tool for XP?
Thanks.
MS has a free web-based SQL admin tool you might take a look at...
Duh! Now that I think about it, you can still use SQL Enterprise Manager ,
just register and connect to the IP and port number...
Also ask your hosting company, many provide tools for you.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Smith" <noemail@.please.com> wrote in message
news:lDHjd.39122$Om6.2911@.bignews5.bellsouth.net.. .
> I host my website with a 3rd party and need something to manage my SQL
> database. Can someone please recommend a good client side tool for XP?
> Thanks.
>
|||Hi Bob,
Thanks MVP Wayne for his perfect suggestions and here is some links for
your reference
Download: SQL Server Web Data Administrator
http://www.microsoft.com/downloads/d...798-C57A-419E-
ACBC-2A332CB7F959&displaylang=en
The SQL Server Web Data Administrator enables you to easily manage your SQL
Server data, wherever you are. Using its built-in features, you can do the
following from Microsoft Internet Explorer or your favorite Web browser:
- Create and edit databases in SQL Server 2000 or Microsoft SQL Server
2000 Desktop Engine (MSDE 2000)
- Perform ad-hoc queries against databases and save them to your file
system
- Export and import database schema and data
- Manage users and roles
- View, create and edit stored procedures
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Bob,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Client side SQL manager tool?

I host my website with a 3rd party and need something to manage my SQL
database. Can someone please recommend a good client side tool for XP?
Thanks.MS has a free web-based SQL admin tool you might take a look at...
Duh! Now that I think about it, you can still use SQL Enterprise Manager ,
just register and connect to the IP and port number...
Also ask your hosting company, many provide tools for you.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bob Smith" <noemail@.please.com> wrote in message
news:lDHjd.39122$Om6.2911@.bignews5.bellsouth.net...
> I host my website with a 3rd party and need something to manage my SQL
> database. Can someone please recommend a good client side tool for XP?
> Thanks.
>|||Hi Bob,
Thanks MVP Wayne for his perfect suggestions and here is some links for
your reference:)
Download: SQL Server Web Data Administrator
http://www.microsoft.com/downloads/details.aspx?FamilyID=C039A798-C57A-419E-
ACBC-2A332CB7F959&displaylang=en
The SQL Server Web Data Administrator enables you to easily manage your SQL
Server data, wherever you are. Using its built-in features, you can do the
following from Microsoft Internet Explorer or your favorite Web browser:
- Create and edit databases in SQL Server 2000 or Microsoft SQL Server
2000 Desktop Engine (MSDE 2000)
- Perform ad-hoc queries against databases and save them to your file
system
- Export and import database schema and data
- Manage users and roles
- View, create and edit stored procedures
If you have any questions or concerns, don't hesitate to let me know. We
are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Bob,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!