Database Failover

Feb 9, 2007

Our reports are connecting to a database snapshot for their data. Periodically this snapshot loads, which means for a minute or two the reports go down. There is however another snapshot available, and between the two one will always be up. Is there a way with Reporting Services to dynamically switch between the two, depending on which one is up? These snapshots are on a constant schedule, so it would also acceptible (but not as nice), to have the database connection automatically switch at certain times of the day.

These snapshots are on the same server, or I would try using a failover partner in the connection string. To the best of my knowledge, this only switches servers, not the database instances.





View 1 Replies


ADVERTISEMENT

Not All Database Failover To Mirrored Database Server When MSSQL Service Stopped

Sep 4, 2007

We have a Prinicipal, a Mirror and a Witness server. We have automatic failover configured between the Prinicipal and Mirrored server. When we stop MMSQL service on Prinicipal, not all the databases failover to the Mirrored instance.
Any suggestions would be welcomed as we have a tight deadline to get this in Production.

View 5 Replies View Related

SQL Server Admin 2014 :: AlwaysOn Failover Cluster Session Failover

Oct 29, 2015

1. Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.
2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.
3. after fail over done to 2nd secondary node what mode in production(readonly or read write).
4. how to rollback to production primary ,will change data in secondary will get updated in primary.

View 5 Replies View Related

No Failover And Client Crashes ODBC DSN Setting For Failover In Connection String

Mar 3, 2007

We have set up Mirroring with a witness server and everything works fine when we failover from the SQL Management console.

However, if we failover when our Maccola client is connected, the client blows up - clearly because it can no longer connect to the database.

The ODBC DSN used by the Maccola client shows a checkbox for the 'select a failover server' but the checkbox is grayed out.

Also the summary of settings for the DSN at the end of the wizard reveals that the failover to server (y/N) option is set to N.

The default setting for this DSN is 'populate the remaining values by querying the server' but it doesn't appear to be getting the settings for failover from the server or any other interactive DSN settings either. The server is clearly set for mirroring.

Another suspicious item is that the DSN cannot connect to the server with SA permissions, even though the server is set to mixed security and we use the correct authentication.

Is it possible that the client MACHINE is not authenticating with the domain or sql server properly. We are logged into the client with the domain account that is the SQL admin account on the sql server box.

We should be able to interact with the sql server settings through the ODBC DSN on the client shoulnd't we?

Are we missing a service pack on the client?

Thanks,

Kimball

View 1 Replies View Related

Recovery :: AlwaysOn Failover Cluster And Active Sessions Failover Scenario

Oct 29, 2015

1. In alwaysON fail over cluster, Once fail over to secondary replica, what will happen to connected session in primary node? can the session fail over to secondary seamlessly or need to re-login. what happen committed transactions which has not write to disk.

2. Assume I have always on cluster with three nodes, if primary fails, how second node make write/ read mode.

3. After fail over done to 2nd secondary node what mode in production(readonly or read write).

4. How to rollback to production primary ,will change data in secondary will get updated in primary.

View 3 Replies View Related

Cannot Login To Failover Database.

Nov 13, 2007

I setup a mirrored database with a witness server. I can login to the database just fine when I set it up.
The servers are in Active Directory but the account "rps" is an SQL account.

When I failover the database for testing, I cannot login to the mirrored database even though it is now the princpal database and it is syncronized.

Here are the errors I receive.

11/13/2007 15:05:13,Logon,Unknown,Login failed for user 'rps'. [CLIENT: 172.23.10.118],
11/13/2007 15:05:13,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 16.,


Any idea on how to fix this?

View 9 Replies View Related

Frequent Database Failover

Apr 28, 2008

Hi MSDN ppl,

I seek your expertise yet one other time.



Scenario:

We have 7 databases mirrored on two servers which are mirroring partners. 3 of the 7 databases are live on server1 and mirrored on server2; and the remaining 4 databases are live on server2 and mirrored on server1. The data is exposed through .NET Widows Application.



The configurations of the servers are as follows.



System: Microsoft Windows Server 2003 R2

Standard x64 Edition

Service Pack 2



Computer: Intel(R) Xeon (R) CPU

5130 @ 2.00 GHz

2.00 GHz, 32.0 GB of RAM



SQL Version: Microsoft SQL Server 2005 - 9.00.3175.00 (X64) Jun 14 2007 11:45:39

Copyright (c) 1988-2005 Microsoft Corporation Enterprise Evaluation Edition (64-bit)

on Windows NT 5.2 (Build 3790: Service Pack 2)



Problem:

The databases for no apparent reason keep randomly failing over to one server quite frequently. At least twice a day. There is no pattern associated for me to make out as to why this is happening.



My Questions:

1. Is it a good practice to divide the databases on each server, the way it is now? Or should all the databases be kept on one server and mirrored on other all the time?



2. From the above mentioned scenario, do you find the reason for database to 'failing over' so frequently? Could the Win Application which is used to expose the data be responsible for the failovers?



3. What steps can be taken to check for the reason which is causing the databases to failover? Alternatively and most importantly, how can I this problem of 'Databases Failing over randomly' be solved?



Thank you,

Little_Birdie


View 13 Replies View Related

Using Microsoft Clustering For Database Failover Support

Jun 9, 1999

I am looking in to using Microsoft Clustering supported in NT Server Enterprise to provide failover support for 2 database servers running SQL Server 7. I was wondering if anyone had any experience with it--good or bad. I read the white paper and it sounds good, but I'd like to get some real
world application experience.

Thanks for any info.

Bob

View 2 Replies View Related

Database Mirroring Vs SQL Server FailOver (Performance)

Mar 15, 2006

Hi there.

I´m running some tests in a database with Mirroring and without Mirroring. As expected there is a performance hit using Database mirroring.

The tests i´m running are just simple functions inserting and updating the database, and then counting the number of sucedeed inserts and updates in a time interval.

My question here is: What if i use Sql Server failover mechanism?

I know that failover time will increase and management is more complex, but what can i expect in terms of performance ?





View 8 Replies View Related

Code To Programmatically Failover A Principal Database.

Mar 12, 2008



Hi Guys,
I was wondering if there is a system procedure / T-SQL code to programmatically Failover a Database from Principal to mirror mode?

also,
We noticed that at times few of all the databases that were kept on 'Principal' mode get randomly 'Failover'. Does anyone have any idea?

If so, pls respond.

Thank you.


View 9 Replies View Related

Why SQL Server 2005 Database Mirror Can't Failover?

Jun 28, 2006



Hello All,

I've tried to replicated exactly what it says in the online documentation. At the end of the post are the SQL statements issued.

1. Enable encrypted outbound connections on the primary server
2. Enable encrypted outbound connections on the mirror server
3. Enable encrypted outbound connections on the witness server
4. Enable encrypted inbound connections on the primary server
5. Enable encrypted inbound connections on the mirror server
6. Enable encrypted inbound connections on the witness server
7. Set mirror''s partner to the primary
8. Set primary''s partner the mirror
9. Set principal€™s witness.
Every step run''s normally,but why can''t auto failover.

I used the following Script:

/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */
use master;
go
DROP USER PrincipalLogin1
DROP LOGIN PrincipalLogin1
DROP USER PrincipalLogin2
DROP LOGIN PrincipalLogin2
GO

Create login PrincipalLogin1
with password='PrincipalLogin'
go
Create user PrincipalLogin1
from login PrincipalLogin1
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1
go
---stop

create certificate MirrorCertForPartnerPub
authorization PrincipalLogin1
from file = 'd:BackupCertMirrorCertForPartner.cer';
go

Create login PrincipalLogin2
with password='PrincipalLogin'
go
Create user PrincipalLogin2
from login PrincipalLogin2
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2
go
create certificate WintnessCertForPartnerPub
authorization PrincipalLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO



ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.1.40.158:5022'
GO

ALTER DATABASE Mirrortest
SET witness = 'TCP://10.10.11.30:7000'
GO

select * from sys.certificates



/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */
--On Mirror
--Remove the database
--Connect to either partner.
--Issue the following Transact-SQL statement:

ALTER DATABASE Mirrortest SET PARTNER OFF

--Optionally, you can recover the former mirror database. On the server instance that was the mirror server, enter:

--RESTORE DATABASE Mirrortest WITH RECOVERY;

RESTORE DATABASE [Mirrortest] FROM
DISK = N'D:BackupCertMirrortest.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
select * from sys.endpoints

drop endpoint Mirror_Endpoint_Mirroring

select * from sys.certificates


Create certificate MirrorCertForPartner
with subject ='this is the certificate for mirror',
start_date='06/25/2006'

backup certificate MirrorCertForPartner
to file='d:ackupcertMirrorCertForPartner.cer'



CREATE ENDPOINT Mirror_Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (
Authentication=Certificate MirrorCertForPartner
,ROLE=ALL)
GO

/* -------- 3. ENABLE OUTBOUND CONNECTIONS ON THE WINTESS -------- */

--On Witness

USE [master]
GO


select * from sys.certificates

drop certificate CertForDBRole
GO


create master key
encryption by password='asdf'

Create certificate CertForDBRole
with subject='this is a certificate for pricipal role',
start_date='06/25/2006'

backup certificate CertForDBrole
to file='c:CertForDBRole.cer'


select * from sys.endpoints

drop endpoint Witness_Endpoint_Mirroring


CREATE ENDPOINT Witness_Endpoint_Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=7000)
FOR DATABASE_MIRRORING (
Authentication=Certificate CertForDBRole
,ROLE=ALL)
GO


/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */
use master;
go
DROP USER PrincipalLogin1
DROP LOGIN PrincipalLogin1
DROP USER PrincipalLogin2
DROP LOGIN PrincipalLogin2
GO

Create login PrincipalLogin1
with password='PrincipalLogin'
go
Create user PrincipalLogin1
from login PrincipalLogin1
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin1
go
---stop

create certificate MirrorCertForPartnerPub
authorization PrincipalLogin1
from file = 'd:BackupCertMirrorCertForPartner.cer';
go

Create login PrincipalLogin2
with password='PrincipalLogin'
go
Create user PrincipalLogin2
from login PrincipalLogin2
go

Grant connect on endpoint::Principal_Endpoint_Mirroring to PrincipalLogin2
go
create certificate WintnessCertForPartnerPub
authorization PrincipalLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO



ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.1.40.158:5022'
GO

ALTER DATABASE Mirrortest
SET witness = 'TCP://10.10.11.30:7000'
GO

select * from sys.certificates


/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */
use master;
go
DROP USER MirrorLogin1
DROP LOGIN MirrorLogin1
DROP USER MirrorLogin2
DROP LOGIN MirrorLogin2
GO

Create login MirrorLogin1
with password='MirrorLogin'
go
Create user MirrorLogin1
from login MirrorLogin1
go

Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin1
go

--stop
create certificate PrincipalCertForPartnerPub
authorization MirrorLogin1
from file = 'd:BackupCertPrincipalCertForPartner.cer';
go

Create login MirrorLogin2
with password='MirrorLogin'
go
Create user MirrorLogin2
from login MirrorLogin2
go
create certificate WitnessCertForPartnerPub
authorization MirrorLogin2
from file ='D:BackupCertCertForDBRole.cer'
GO

Grant connect on endpoint::Mirror_Endpoint_Mirroring to MirrorLogin2
go


ALTER DATABASE Mirrortest
SET PARTNER = 'TCP://10.10.116.42:5022'
GO

ALTER DATABASE Mirrortest
SET WITNESS = 'TCP://w7mis02:5024'
GO




/* -------- 6. ENABLE INBOUND CONNECTIONS ON THE WITNESS -------- */

use master;
go
DROP USER WitnessLogin1
drop login WitnessLogin1
DROP USER WitnessLogin2
drop login WitnessLogin2

Create login WitnessLogin1
with password='WitnessLogi*n@'
go
create user WitnessLogin1
from login WitnessLogin1
go

create certificate PrincipalCertForPartnerPub
authorization WitnessLogin1
from file = 'c:ackupcertPrincipalCertForPartner.cer';
go
--***************************************
Create login WitnessLogin2
with password='WitnessLogi*n@'
go
create user WitnessLogin2
from login WitnessLogin2
go

create certificate MirrorCertForPartnerPub
authorization WitnessLogin2
from file = 'c:ackupcertMirrorCertForPartner.cer';
go

Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin1
go
Grant connect on endpoint::Witness_Endpoint_Mirroring to WitnessLogin2
go

All runs normally,But how can't be auto failover?

View 5 Replies View Related

SQL 2012 :: Database Failover History In AlwaysOn Group

Jun 16, 2014

If there is a history kept somewhere of failover events of a database in an AO group? I have 2 replicas with automatic failover and I'm looking for a history of failovers.

View 0 Replies View Related

Getting E-mail Notification After Mirrored Database Automatic Failover

Jul 12, 2007

Hello,

I am a C++/C# developer my SQL skills are very limited.
I have a database set up for mirroring and I would like to get an e-mail notification whenever an Automatic Failover occurs. Can anyone show me how to do this using SQLServer 2005? Please provide T-SQL script sample if possible!



Thanks,



-Agustin

View 7 Replies View Related

Client Failover Takes 1 To 3 Minutes After Database Has Failed Over

May 12, 2008



We are having problems getting the ADO.NET client to failover in a timely manner in a web application. Here's what happens:

1) Database fails over in 3 to 5 seconds
2) Clients attempt to login to primary and receive SQL server not available errors
3) The clients slowly start to connect and succeed without errorson the secondary.
4) We are still see SQL Server login errors on the primary, even after the failover
5) When we failback to the primary, it all happens within a few seconds (no problems)

If we recycle IIS, this problem goes away

Here's what we have tried
1) Turning off connection pooling (has no effect). We also can see the connections get killed on the primary server
2) Applying this hotfix (http://support.microsoft.com/kb/944099)

And yes, we are running a script to sync up the SIDS so that doesn't appear to be a problem. What seems to be happening is the primary server is getting stuck in the ADO.NET application memory. The problem is we don't know how to fix it.

BTW, our hardware is SQL Server 2005, Windows Server 2003, .NET 2.0 (and .NET 3.5)

Let me know if you need additional information. Thanks in advance

View 2 Replies View Related

Mirroring :: Manual Failover Did Not Get Back Database On Secondary Server?

Jun 22, 2015

I have SQL Server 2008 R2 database mirroring setup with witness. Below database connection string is used by the application
 
jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER
 
During some maintenance activity at the OS side , I tried to manually failover database from Server_A to Server_B. It took long time for failover  to Server_B. While failing over the Mirrored_DB database goes in In Recovery status and application is not able to connect to database. I waited for long time more than 40 mins for database to come online on Server_B but it did not work.So I have recovered database on Server_B using RESTORE DATABASE Mirrored_DB WITH RECOVERY.
 
After database was recovered on Server_B application was not able to connect to database with error as "The database <Mirrored_DB> on server Server_BMSSQLSERVER is not configured for database mirroring".
 
My Question is

Why manual failover did not get back database on secondary server upwhy the original  connection string below is not able to connect to the database while database is online on Server_B  but without mirroring

 jdbc:sqlserver://<Server_A>MSSQLSERVER;databaseName=<Mirrored_DB>;port=1433;failoverPartner=<Server_B>MSSQLSERVER

View 3 Replies View Related

Will SQL Server 2005 Failover Clustering Still Work When The Database Level Is Set To 80?

Nov 2, 2006

We want to use sql 2005 failover clustering feature, so that upgrade sql 2000 is necessary. But some of the stored procedures built in 2000 are not working directly in 2005, set the compatibility level to 80 could make them work. In this case, can somebody here tell me if down grade the database level will affect failover clustering running properly? e.g. will data still be synchronized properly? Thanks in advance.

View 3 Replies View Related

SQL Server Admin 2014 :: AlwaysOn HA - Database Stuck In Initializing After Failover

Oct 23, 2015

We had to failover our primary db server for maintenance to our secondary replica. The primary was rebooted during maintenance. We failed back after the maintenance and one of the databases is not synchronizing.

I checked sys.dm_hadr_database_replica_states, and it is showing that it is INITIALIZING.

It has been in this state for more than 45 mins now. The last_sent_time, last_received_time, last_hardened_time and last-redone_time are all stuck with a time stamp 45 mins ago.

They haven't changed. How do i resume this database and bring it back in sync?

I tried suspending and resuming the data movement, but hasn't worked.

View 6 Replies View Related

SQL 2000 Database Response Hangs After Cluster Failover Only When Active Connections Were Present

Sep 26, 2007

I have a Microsoft Cluster running on Server 2003 Entrprise. SQL 2000 8.00.2039 (SP4). 5gb physical memory installed.

With the databases online we run a test failover from the Cluster administrator. It takes about 30-40 seconds and completes without generating any Server Event log errors nor SQL log errors. Everything looks good from an administrative stand point.

However, when we test with running a series of queries to the databases, then failover, we notice that it can take up to 3 or 4 minutes before some of the databases will respond. Connections are not refused, they just sit there.

How can we troubleshoot this or does anyone have a similiar experience with this scenario?

View 5 Replies View Related

SQL Server Failover:

Jan 22, 2007

 
I have to figure out a way to handle a failover in SQL Server 2005. I was reading a few articles about it.
1. Do I need to do anything in application layer to let a failover occur in SQL Server 2005
2. I have a web site that connects to a production server, the connection string is defined in web.config. If I implement Mirroring in SQL Server 2005, and when failover occurs, shouldn’t I change the connection string to point the application to the new server?

View 2 Replies View Related

Cluster Failover ?

Oct 2, 2007

Hi I just set up a new cluster and everything was working. Now when I try to test failovers like unplug the public network card etc...

The cluster group fails over correctly but my data and log array's dont failover.

So I unplug the network cable from node 1 (active) the cluster group with the IP, name, and Quorum disk fails over to node 2 but the other groups sql data and sql log stay on node1 and dont failover.

Any ideas?

TIA

View 14 Replies View Related

Failover Configuration

May 13, 2004

I have 4 servers, the first two are web app and load balanced the second two are to be configured for failover. Let us call them SvrA and SvrB. SvrA will have active SQL (2000EE) and passive Content (large directory of courses offered) SvrB will have passive SQL(2000EE) and active Content. Both SvrA and SvrB will be connected to a redundant SCSI 18 drive Drive bay (cheap SAN) which will be configured with a) mirrored OS drives, b) a RAID 5 partition for SQL and c) a RAID 5 partition for the content folders.

SvrA & SvrB will be quad processor machines. EE license for 4 processors as 2nd svr is failover for SQL.

My concern is the failover set up for the content portion, or is it the failure of the server that causes the failover? This is quite confusing to me.

Any thoughts would be most helpful.

View 2 Replies View Related

Automatic Failover

May 25, 2008

Hi there,
I am testing the db mirroring, making sure it will auto failover. I've stopped the SQL services on my principal and then I looked at the mirror db is says it's restoring. It stayed like that for 10 min before I enabled the mirroring again. Anyone knows why it's not failing over??????


Here's my setup: SQL 2005 Standard, Server 1 Principal, Server 2 Mirror & Witness.

View 4 Replies View Related

Cluster Failover

Mar 9, 2007

Hi all:

I was wondering what will happend to the transaction when the cluster failover from one node to another?

In my case the database is in node-001 and I need to restart the server on node-001. So I need to have the database failover to node-002, so I can work on node-001. During this process, will it effect to end-user?

Thanks
vua

View 5 Replies View Related

Log Shipping Failover

May 25, 2006

Is it possible to failover a single or a selected number of databasesonto a standby server? We're currently log shipping about 12 databasesto a standby and I was wondering if it was possible to randomlyfailover onto selected dbs.My understanding of log shipping is that when the primary server goesdown, the standby takes over. But, does that mean that all of the db'son the standby server now become the primary? Or, can failover occur onselected dbs?The reason I ask is because if one db on the primary, for example, wasto go down, I'd like to be able to bring the particular db on thestandby server online while I fix or restore what's on the primaryserver. Can it be done individually or is it an all or nothingscenario?Your personal advice or links is greatly appreciated.Thanks,Peps

View 1 Replies View Related

MS SQL Clustering And Failover

Jul 20, 2005

I have a 2 node Microsoft 2000 cluster with a shared storage device. Iwant to create automatic failover for MS SQL 2000 server. I can dothat wit the following options:1. Active/Pasive (one instance installed)2. Active/Active (More then one instance/virtual sql serversinstalled)I have a question about option 2.Is it possible to have more then one instance/virtual SQL server,service only one database for example loadbalancing. ?, or when youcreate multiple instances/virtual SQL servers, does that mean you havemultiple databases ? If not why does a Virtual SQL server has to betaken over by a cluster node that didnt fail. Then you can actuallyuse option 1. Please advise!The documentation is not very clear on this issues.Regards,Nico de Groot

View 1 Replies View Related

Recovery :: Not Able To Failover AG

Oct 15, 2015

Could not able to connect secondary replica below is the error message I am getting when I tried to failover. Cannot connect to VLDBATEAM.

The secondary replica that you selected to become the new primary replica does not belong to the specified availability group. A possible explanation is that the replica has not been joined the availability group.  (Microsoft.SqlServer.Management.HadrTasks)

View 6 Replies View Related

Failover When The Dbb Is Not Mirrored

Jan 2, 2008



Hi there!

I use different udls for a website which is partly asp pages and partly aspx pages.
Every database is mirrored, so each udl indicates a "failover partner". However we decided recently that one of the database would not be mirrored anymore. For this database we still use in the udl a "failover partner" to redirect the connection to the second database in case of a failover. For the asp pages the failover works fine even if the database is not mirrored, but it doesn't work for the aspx pages.

It would be great if anyone had an idea why the behaviour is not the same for the aspx pages, and if we can still use a "failover partner" in udls even if the database is not mirrored.. if not, what should we use to connect to another database?

Any help is welcome!

idel

View 3 Replies View Related

Testing Failover

Sep 13, 2006

How do I test failover to point to the mirrored database instead of the principal without having to stop the primary instance? I would like to just bring the mirrored database on the primary instance to an offline status and see that the same database on the mirrored instance getting activated as the primary. This does not happen if I try to put the database on the primary server "offline" as it complains that any database that is being mirrored cannot be taken offline.

View 1 Replies View Related

Failover Question...

Sep 5, 2007



We're planning on moving some applications to a SQL server. We're likely going to go with 64-bit for obvious reasons. Since these are critical apps, we plan on having a failover cluster set up. We're aware that all nodes in the cluster have to be 64-bit, if that's the way we decide to go.

The question is this: is there any "non-standard" way to have a 32-bit failover solution, perhaps via a 3rd party application? The only reason we're considering this is from a budget standpoint.

"Hurricane" Andrew
Milford, DE

View 5 Replies View Related

Web App Failover Problem

Jun 7, 2006

Hi,

I have successfully set-up a DB mirroring. When the pricipal goes down, the mirror picksup and the other way around too. So the mirroring part is working great. However, when I use my web applicaiton (dot net 2.0), it is not able to connect to Mirror server. I get the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)


My connection string in web app:

"Server=serverAAA;Failover Partner=serverBBB;Database=TestDB;User Id=testUser;Password=testPassword;"

I have checked the surface are config on "serverBBB" and it is set to use tcpip only for remote connections.

Any suggestions are higly appreciated. TIA.

View 9 Replies View Related

SQL Server Failover:

Jan 23, 2007


I have to figure out a way to handle a failover in SQL Server 2005. I was reading a few articles about it.
1. Do I need to do anything in application layer to let a failover occur in SQL Server 2005
2. I have a web site that connects to a production server, the connection string is defined in web.config. If I implement Mirroring in SQL Server 2005, and when failover occurs, shouldn€™t I change the connection string to point the application to the new server?

View 1 Replies View Related

Automatic Failover... Help

Jan 25, 2007

Hi there,

We've recently set up a Principle, Mirror and Witness configuration with the Mirror and Witness in a separate building to the Principle. All three are part of the same domain (DMZ) and are different servers, the buildings are connected via a fiber optic cable. All servers and SQL Server instances are logged in with the same domain admin account DMZesAdmin.

Mirroring is all set-up and the databases are synchronized. Every once in a while some (not all, normally 6 out of 15) databases will switch roles and become active on the mirror. The SQL Server mirroring monitor job then reports:

Date  25/01/2007 12:37:01
Log  Job History (Database Mirroring Monitor Job)

Step ID  1
Server  DMZSQL01
Job Name  Database Mirroring Monitor Job
Step Name  
Duration  00:00:02
Sql Severity  16
Sql Message ID  32038
Operator Emailed  
Operator Net sent  
Operator Paged  
Retries Attempted  0

Message
Executed as user: DMZesadmin. An internal error has occurred in the database mirroring monitor. [SQLSTATE 42000] (Error 32038).  The step failed.

I have no idea, what causes the failover, it could be a slow network or a bad set-up, can anyone give me some ideas of what to do to track down the problem or any experience of what could be causing this, it happens randomly every day or three. No warning and if I go to the mirror and failover back to the principle again then it's all just fine. However I don't want half my databases working on 1 server and half on the other.

Any ideas?

Thanks
Ed



 

UPDATE:

I've just been looking at the logs on my Mirror and at the same time it reports in this order

Error: 1479, Severity: 16, State: 1.

The mirroring connection to "TCP://DMZSQL01.dmz.local:5022" has timed out for database "WARCMedia" after 10 seconds without a response.  Check the service and network connections.

Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required.

Recovery is writing a checkpoint in database 'WARCMedia' (41). This is an informational message only. No user action is required.

The mirrored database "WARCMedia" is changing roles from "PRINCIPAL" to "MIRROR" due to Failover.

Database mirroring is inactive for database 'WARCMedia'. This is an informational message only. No user action is required.

...

 

This looks like a time out, is there any way to set the TimeOut threashold for Database mirroring or set retry intervals??

View 5 Replies View Related

Cluster Failover Testing

Feb 29, 2000

We need to start testing our Microsoft cluster servers.
Has anyone heard of any plan or products one can use to test
the failovers?
Please advise
Thanks
Susan

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved