Database Mirrors over distance

Various posts have noted that mirroring over distance is not advisable or that either async connections should be used.

Are there any limits/recommendations i.e. if two datacenters are a couple of files part with 10GBs fibre links and <50ms response times would this be acceptable for high-availability mirroring?

Hi Andrew,

I think you have good network links.

The best thing to do is to test it in synchronous mode first. See what the performance is like.

If the performance is not acceptable, you can then switch the safety off.

Please note that Wtiness server should not be used when running with safety off option.

cheers

Jag

|||

Andrew,

Please include re-indexing and large bulk inserts in your testing as well.

cheers

Jag

|||

jag,

Thanks for the response, although I think I will need to look at another solution as mirroring has a very small limit on the number of Databases that can be mirrored (10 recommended, people maxing out at 60) and with a large SharePoint installation the number of content databases can exceed these limits relatively quickly.

Options: More SQL servers (x2 to mirror) or use Clustering in live datacenter with Log Shipping to redundant center.

Andrew

Database Mirroring. Asp application (IIS 6.0) does''t forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Client error '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

|||

Been banging my head against this for a few days, have applied Hotfix for Microsoft.Net Framework 2.0 (KB916002) see http://support.microsoft.com/kb/912151 this is meant to fix default timeout in dB driver - didn't help. Just tried your suggestion about connection pooling, added 'Pooling=False;' to connect string - didn't help.

Is it sensible to turn off connection pooling? my understanding was this provides a big boost to site performance so would MS make us sacrifice performance for resilience? Has anyone got this failover working?

sql

Database Mirroring. Asp application (IIS 6.0) does't forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Client error '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTER DATABASE MYDBNAME SET PARTNER FAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

Database Mirroring. Asp application (IIS 6.0) does't forward connections to mirror server

Hi!

I have setup a database mirroring session without witness - ServerA is the principal, ServerB is the mirror,. Each SQL Server instance is hosted on its own machine on sql2005 EE SP2. The mirroring is working correctly. If I submit to server ServerA command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER

, ServerB becomes the principal, it means that mirroring works correctly.

My issue is with the SQL Native Client and a front-end ASP application (actually IIS 6.0 site) that needs to make use of this database. I have setup my front-end application to use SQL Native Client and specified the failover server in connection string. Here is the connection string that I am using :

PROVIDER=SQLNCLI.1;Server=ServerA,1433;Failover Partner=ServerB,1433;Database=MYDBNAME;Network=dbmssocn;Integrated Security=SSPI;

Everything works perfectly on my front-end application when ServerA is the principal. If I execute on server ServerA command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER

, ServerB becomes the principal, and the failover occurs correctly on the database side. The problem is that my front-end application is not able to query the database on ServerB. The error appears:

Microsoft SQL Native Clienterror '80004005'

Cannot open database "MYDBNAME" requested by the login. The login failed.

This behavior my appication till I unload IIS 6.0 pool application. After that my front-end application becomes work correctly with ServerB.

When I swap server, I execute on server ServerB command:

ALTERDATABASE MYDBNAME SETPARTNERFAILOVER,

my IIS 6.0 application automaticly turn back to ServerA without any action on my side.

I am using SQL Native Client last version http://download.microsoft.com/download/2/7/c/27c60d49-6dbe-423e-9a9e-1c873f269484/sqlncli.msi (issued in February 2007). Has anyone experienced this issue? I'm thinking that it's a problem in the SQL Native client

Are you using connection pooling>?|||Yes, I think so. I use SQL Native Client settings by default. As I know SQL Native Client uses connection pooling by default.|||

We're having the same problem with this, but we're testing automatic failover with a witness. We're also using classic ASP, native client (Feb2007) and SQL2005 Std with sp2. For some reason, automatic failover doesn't allow the web server connections to failover to the partner server - although we confirmed that the databases DO failover and we are automatically synchronizing sql logins. Manual failover seems to work without problems (again this is with a witness).

Does anyone have any ideas on why this happens? We also tried disabling named pipes on the web server native client settings (as some article suggested), but without luck.

|||

There are different keywords for failover partner. As far as I know there are three keywords that all work in different instances and fail in others. In your situation I am not sure which you would have to use in the connection string.

FailoverPartner=ServerB

Failover_Partner=ServerB

Failover Partner=ServerB

I would just try them and do a manual failover of the database and see which one works and which one fails. I have searched all over trying to find out for sure which ones work where but I can't find a list anywhere.

|||

This also might help. Not sure how accurate the statement is. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=421936&SiteID=1

|||

As already stated, connection pooling will be your issue.

turn it off and try again...

The other thought is, once you have failed over go to lunch and see if your app works afterwards (as if it was connection pooling the connection should be closed by then and hence will start to work)

|||

Been banging my head against this for a few days, have applied Hotfix for Microsoft.Net Framework 2.0 (KB916002) see http://support.microsoft.com/kb/912151 this is meant to fix default timeout in dB driver - didn't help. Just tried your suggestion about connection pooling, added 'Pooling=False;' to connect string - didn't help.

Is it sensible to turn off connection pooling? my understanding was this provides a big boost to site performance so would MS make us sacrifice performance for resilience? Has anyone got this failover working?

Database Mirroring, client connections all ODBC so what methods are recommended?

As stated in the subject I have a situation where if database mirroring is employed for either manual or automatic failover, all the client (including web connections) connections use ODBC not ADO, or OLEDB etc... so what methods are recommended? Client side redirect is not available so I could not employe the "Data Source =A; Failover Partner=B..." option.

Right now the method employed (pre database mirroring and basically employing log shipping on SQL 2000) is to have a DNS alias for the ODBC connection so that if the server were to change in a failover situation the DNS record would have to be altered, so that all the client connections would not have to be reconfigured.

Regards,

Dominic Baines

I have one customer in production that uses the DNS name change method to switch over the clients after a failover, and so that method does have uses.

Alternatively, you could have the application handle the failover by issuing the redirect to another server.

Thanks,

Mark

|||

Sorry Mark but just not an option to recode app connection strings.

Several reasons:

1. Most apps are 3rd party and insist on ODBC only connections (I know old method but that is state of affairs).

2. Recoding ODBC connections means we would have to visit physically or by logon/registry script every client to reconfigure connection when failover occurs. Not practical and bound to miss some.

3. When fail back occurs same problem exists in reverse.

We simply have too many applications and over 50 DB's on 6 pairs of servers in the failover/failback situation (canot use clustering) and 100+ app servers and many many direct connect clients involved. And the desktop clients spread over 5 countries including several timezones and as most everyone else these days also India.

It looks like DNS alias is the option unless anyone has a better one? It was originally coded for older NT4 style domain then migrated to AD, which was interesting but that is a different story.

As this appears to be the case can the failover be used to trigger a script in SQL2005 (that will update the DNS (I can handle that script). How could you detect the failover has occured? Still a bit too new to SQL2005 to know the answer to this one.

(and...thanks for the message on the webcast, Kimberley has got in touch).

Regards,

Dom

|||

I understood that the SQL 2005 ODBC provider (SQL Native Client ODBC provider) was part of the SNAC stack and therefore would support failover. Of course the SQL 2005 SNAC bits and pieces would need to be installed on the clients/servers and the connection string(s)/DSNs modified to support a mirror, but that would be a one off operation, albeit maybe a large one.

As per BOL : "The SQL Native Client ODBC driver complies with the Microsoft Win32? ODBC 3.51 specification. The driver supports applications written with the ODBC 2.5 or earlier versions of the ODBC functions in the manner defined in the ODBC 3.51 specification."

Am I missing something?

|||

I have marked as answer as basically the solution does provide an answer that will probably assist some.

However, I will carry on using the DNS method for the moment.

There is no scope right now to upgrade the native ODBC drivers as I am 'stuck' with desktops with older drivers and reconfiguring the client driver or connections is not possible as it would also mean rolling out new desktop images.

Regards,

Dom

sql

Database Mirroring Question

I've read several instances that when implementing Database Mirroring, all
client connections are lost. A retry from client/s, applications, etc... will
reconnect to the Failover.
In order to compensate for the disconnect you should make use of TRY CATCH
logic within your code.
When going over that statement, TRY CATCH is not valid for disconnects so
how would you accomplish this feat?
What I mean is how could I allow my application to essentially reestablish
the coonection or look for the Failover from within my Connection String?
Many Thx,
Anthony E. Castro - MCDBA
Need to utilize FAILOVER PARTNER=<Mirror SQL server> in your connection string.
"acmcdba68" wrote:

> I've read several instances that when implementing Database Mirroring, all
> client connections are lost. A retry from client/s, applications, etc... will
> reconnect to the Failover.
> In order to compensate for the disconnect you should make use of TRY CATCH
> logic within your code.
> When going over that statement, TRY CATCH is not valid for disconnects so
> how would you accomplish this feat?
> What I mean is how could I allow my application to essentially reestablish
> the coonection or look for the Failover from within my Connection String?
> Many Thx,
> --
> Anthony E. Castro - MCDBA

Database Mirroring Connection Question

I've read several instances that when implementing Database Mirroring and a
fail-over
occurs, all client connections are lost. A retry from client/s,
applications, etc... will
reconnect to the Failover.
In order to compensate for the disconnect you should make use of TRY CATCH
logic within your code.
When going over that statement, TRY CATCH is not valid for disconnects so
how would you accomplish this feat?
What I mean is how could I allow my application to essentially reestablish
the coonection or look for the Failover from within my Connection String?
--
Anthony E. Castro - MCDBADoesn't the disconnect raise a SqlException that could be caught and you
resubmit the failed request? Have you tried this already?
In case you are using ADO.Net 2.0 or later, you have a 'failover
partner' parameter in the connection string that SqlClient will use to
connect to if there was loss of connectivity to the primary server.
So, all your application needs to do is resubmit the request - whichever
database - principal or the new principal - that SqlClient is able to
connect to will be used.
acmcdba68 wrote:
> I've read several instances that when implementing Database Mirroring and a
> fail-over
> occurs, all client connections are lost. A retry from client/s,
> applications, etc... will
> reconnect to the Failover.
> In order to compensate for the disconnect you should make use of TRY CATCH
> logic within your code.
> When going over that statement, TRY CATCH is not valid for disconnects so
> how would you accomplish this feat?
> What I mean is how could I allow my application to essentially reestablish
> the coonection or look for the Failover from within my Connection String?
>