MSSQL Mirroring : Unable To Access Mirror After Succes Failover
Oct 16, 2007
Hello everybody,
I'am currently testing a MSSQL 2005 mirroring implementation with automatically failover.
1 principal server ====> Server A
1 mirror server ====> Server B
1 witness server ====> Server C
When server A fails Server B becomes Principal. ( which is the general idea ) This is working perfectly.
The problem is when i try to connect to the mirror Server B ( actually the new principal )
I use the .NET connection string with the failover parameter ( even without the parameter ) the connection fails.
( the general error : unable to connect, or connection property has not been initialized )
Data Source=ServerA; Failover Partner=ServerB;Initial Catalog=DBMTest;uid=dbuser;pwd=dbpassword;
This connection property works fine when server A (original principal) is online.
When i run the SQL profiler with the mirror db as profile, i dont see the traffic.
So you think the mirror server has a network problem or was not connfigured for remote access.
i thought that also, but when i create a 'stand alone - non mirrored' database the same application with the same connetion string ( well.....different db) works fine.
The problem must be in the whole mirroring implementation....... ( my idea )
Does anyone had the same problem, or better, doed anyone know how to solve it?
Hope to hear from you soon!
Thank you in advance!
Regards,
W.schmitt
The netherlands
View 6 Replies
ADVERTISEMENT
Jul 16, 2015
I have created a Endpoint with "Mirroring Server" on the mirroring instance. Due to the keyword server i am not able to drop the endpoint now.
Also am not able to configure a mirror in a windows workgroup also( the two systems principal and mirror are under same network ). I have followed many ways as per msdn articles and other blogs.
I am not able to connect to the mirror server.Â
I have tried giving the ip addr and the port no, also the computer name:port no, computername.local:port no etc. Both the systems are running on sql server 2008 only
I always get the error as Connection cannot be established to the destination.
(I have also enabled the ports in firewall by creating inbound and outbound rules)
The issue probably lies in NAMING server address i guess.
View 2 Replies
View Related
Jun 14, 2015
I am using SQl Server 2012 Database Mirroring with around 40 gb as mdf and 1 gb as ldf. Now my ldf size increased . How to reduce ldf size while mirror enabled with mirror server and witness server. Can shrink the ldf with mirror enables.
View 4 Replies
View Related
Nov 18, 2006
I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.
View 3 Replies
View Related
Aug 3, 2007
I'm trying setup a proof of concept for a client but I€™m having issues with mirroring and replication.
With the help of http://technet.microsoft.com/en-us/library/ms151799.aspx I€™ve done the following..
The setup is as follows, I have several instances of SQL Server
A+B are mirrored with each other, with A being the principle and B the mirror
C is the distributor
E is subscribes to A, with parameters for PublicationFailoverPartner set to B
The mirroring between A+B works fine, I can manually fail each and the databases keep in step.
Without testing the mirroring failover, the replication between A+C+E also works.
However when I test the mirror to failover between A+B no transactions are replicated though to E. The publication moves from A to B in the Server Management Studio.
The logs state that the log reader agent can not gain access to A, however nothing is recorded regarding any attempts to contact B. In the replication monitor the error message
"The concurrent snapshot for publication ABCXYZ is not available because it has not been fully generated of the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Spapshot Agent for the publica..." (cut off).
"the process could not execute 'sp_replcmds' on 'A'
I have checked that the SQL Server Agents are running on all agents,
I have checked that that server B is set at the PublisherFailoverPartner on server C agent configuration; I'm now at a lost as to what is wrong,
Any help or suggestions are greatly appreciated.
View 14 Replies
View Related
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
Aug 24, 2006
When performing a failover the DriverManager.getConnection method never returns from it's call. I used a modified version of the test program included in the sqljdbc help file (below). If the principle server is server2 and a failover is performed, the program works just fine. If the priciple server is server1 and a failover is performed, the hang occurs. The sql servers are not on a domain, are in the same workgroup, have certificates setup between them. Sqljdbc version used is 1.1.1501.101. I rolled back to version 1.1.1320.0 and the DriverManager.getConnection exceptioned. Not sure if this is a setup issue or a common problem. Anyone have any suggestions or seeing similar issues?
Thanks
- tc
Version 1.1.1501.101 - System hangs on DriverManager.getConnection method.
Version 1.1.1320.0 - Exception:
java.nio.channels.UnresolvedAddressException
at sun.nio.ch.Net.checkAddress(Net.java:30)
at sun.nio.ch.SocketChannelImpl.connect(SocketChannelImpl.java:451)
at java.nio.channels.SocketChannel.open(SocketChannel.java:146)
at com.microsoft.sqlserver.jdbc.DBComms.<init>(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithFailover(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:525)
at java.sql.DriverManager.getConnection(DriverManager.java:193)
at testconnection.Main.Execute(Main.java:115)
at testconnection.Main.main(Main.java:35)
/////////////////////////////////////////////////////////////////////////////
package testconnection;
import java.sql.*;
public class Main
{
/** Creates a new instance of Main */
public Main()
{
}
/**
* @param args the command line arguments
*/
public static void main(String[] args)
{
int i = 0;
while(true)
{
Execute(++i);
try
{
Thread.sleep(5000);
}
catch(Exception e)
{}
}
}
private static void Execute(int iteration)
{
// Create a variable for the connection string.
String connectionUrl = "jdbc:sqlserver://server1;" +
"databaseName=TestDb;integratedSecurity=true;" +
"failoverPartner=server2";
// Declare the JDBC objects.
Connection con = null;
Statement stmt = null;
try
{
System.out.println("(" + iteration + ") Connection Test");
// Establish the connection to the principal server.
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
System.out.println(" Get Connection.");
con = DriverManager.getConnection(connectionUrl);
System.out.println(" Connected to the principal server.");
// Create and execute an SQL statement that inserts some data.
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM TestTable");
System.out.println(" SUCCESS");
while(rs.next())
{
System.out.println(" Record: " + rs.getString("Field1"));
}
}
// Handle any errors that may have occurred.
catch (SQLException se)
{
try
{
// The connection to the principal server failed,
// try the mirror server which may now be the new
// principal server.
System.out.println(" Connection to principal server failed, " +
"trying the mirror server.");
con = DriverManager.getConnection(connectionUrl);
System.out.println(" Connected to the new principal server.");
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM TestTable");
System.out.println(" SUCCESS");
while(rs.next())
{
System.out.println(" Record: " + rs.getString("Field1"));
}
}
catch (Exception e)
{
System.out.println(" FAILED");
e.printStackTrace();
}
}
catch (Exception e)
{
e.printStackTrace();
}
// Close the JDBC objects.
finally
{
if (stmt != null) try { stmt.close(); } catch(Exception e) {}
if (con != null) try { con.close(); } catch(Exception e) {}
}
}
}
View 15 Replies
View Related
Jun 13, 2007
I have two questions here.
Question 1:
If I am using Database Mirroring for my production database in SQL Serever 2005, can I do mirroring on the mirror database?
Principal Database -- Mirror -- Mirror A -- Mirror -- Mirror B
Can this be done?
Question 2:
Can I do 2 mirroring on a single principal database? If can, which mirror database it will goes when the failed over happen? can the other mirror switch to mirror the failed over database?
Principal Database -- Mirror -- Mirror A
-- Mirror -- Mirror B
Please advise. Thanks
View 3 Replies
View Related
Oct 13, 2015
I have three win2k8 r2 servers. Â 2 are running SQL 2008 r2 and are mirrored. The 3rd server is a witness server. Every so often we get errors and failovers of the mirror. The communication errors are between the witness server and sql servers. No errors between SQL servers. There seem to be no network issues and happen randomly.Â
Event id 1474 and 1479.Â
The mirroring connection to "TCP://witnesssrv:5022" has timed out for database "DB" after 30 seconds without a response. Â Check the service and network connections.Â
Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://witnesssrv:5022'.
View 4 Replies
View Related
Dec 22, 2006
Hi, all.
here is the situation.
we designated one server as a mirrored server. this server will host all mirrored databases from different principles in different server using certificate.
1) Server A (Principle) Server B(Mirrored)
2) Server C (Principle) Server B(Mirrored)
I was able to set up mirroring between A and B. But unable to set up mirroring between C and B.
My question is "Can I grant connect on endpoint to two different host logins?"
For example, on the server B
I created two different logins for Server A and C in order to assign different certificates to two principle servers.
For Server A.
create login server_A_login with password = 'djkkajnxks';
go
create user server_A_user for login server_A_login
go
create certificate server_A_cert
authorization Server_A_user
from file = 'c:certificateserver_a_cert.cer'
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [server_a_login];
GO
For Server C.
create login server_C_login with password = 'djkkajnxks';
go
create user server_C_user for login server_c_login
go
create certificate server_c_cert
authorization Server_c_user
from file = 'c:certificateserver_c_cert.cer'
go
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [server_c_login];
GO
when I set up like this and try to start mirroring, I am getting error from server c saying, "the network tcp:serverb.corp.com can not be reached or does not exist"
But mirroring between server a and b is OK. I set up them first, then between c and b.
any ideas?
thanks,
View 2 Replies
View Related
Apr 14, 2015
I need to migrate my SQL server Mirror Database to a new server. my current setup is as below
1. server A principal (192.168.1.100)
2. server B Mirror   (192.168.1.200)
Now i have a new server (Server C) to replace server B as below
1. server A principal (192.168.1.100)
2. server C Mirror   (192.168.1.300)
My question is how to migrate mirror db to new server without any affect or downtime on principal server.
note: SQL SERVER 2008R2 EE(64BIT), Win2008R2 EE 64bit.
View 5 Replies
View Related
Apr 23, 2006
Hi,
I've a very basic doubt about database mirroring. I did setup a database mirroring session with the help of SQL Server Management Studio between the server A (db1) and B(db1). When is stop database mirroring by using the command button "Stop Mirroring" available in the mirroring page of SQL Server Management Studio , the mirror database{ B(db1) } goes to state "Restoring...". After stopping the mirroring i'm not able to access the mirror database.
Can you please tell me how to bring mirror database B(db1) to operation mode so that we can start working with that database?
Regards,
Gopi
View 1 Replies
View Related
Jun 1, 2015
I have configured active passive cluster in production environment. And we also have a dr which we have configured with asynchronous mirroring with no witness. Currently active node(node
a) is in sync with dr. When failover happens and the second node(node
b) becomes active, the mirror is broken and goes to disconnected mode.
But when we failback again to node a mirror is connected again and is in sync again. In our setup we have active passive cluster and a standalone server as dr.
View 11 Replies
View Related
Aug 24, 2015
When I try to setup mirror on a SQLserver dbase using windows Authenticated login I get error:-
Login failed for user 'SERVERBAdministrator'. (Microsoft SQL Server , Error: 4060).The login connect string however on selection of SERVERB from SERVERA has a greyed out option for using Windows Authenticated login which clearly shows Username: SERVERAAdministrator.It is odd that the error coming back is showing SERVERBAdministrator on the rejection. (somehow the machines got swapped over in the authentication) The strange thing is if you use the SQLserver studio on SERVERA to connect to SERVERB using the object browser and Windows authenticated login it all works fine without errors. clearly the Mirror software is using some other means/privs of login to establish the connection.
The two machines SERVERA and SERVERB are in the same Domain (DOM1) but they are not using a domain user account.They are using Administrator user on both independent machines. The SQLSERVER install specified both Windows and sqlserver logins. The mirror is to use Windows authenticated login.Is there some login priv we are missing The sqlserver security has allowed this type of login......
View 2 Replies
View Related
May 2, 2015
DB replication can set db recovery model to simple ,why db mirror can not  db recovery model to simple.
DB mirror must be set to full recovery model.
As far as I know, whatever db mirror  and db replication ,there is a log reader to read the log in the ldf file DB mirror and DB replication are almost the same principle to replicate the db to another db server.
View 7 Replies
View Related
Jan 4, 2008
Hi everybody,
I have a mirroring set up for around 30 databases in a 64 bit - 2 powerfull servers scenario - wih latest sp2 + hotfix. Mirroring works perfectly most of the times but when the principal is getting busy during the busy period of the day all of a sudden the processor on the mirror jumps to 100% and stays there for a long time - there are no live databases on the mirror server - all the databases are in mirrored mode. The principal server processor is less than 5% all the time. The network load between the principal and the mirror is also in normal limits. The principal server is dedicated to SQL but the mirror has other things too but the other processes use up maximum 15-20% of the CPU and when I stop SQL Server in one of those moments when the CPU jumps to 100% everything comes back to normal so it's clear that the issue is SQL on the mirror.
Any ideas would be greatly appreciated.
Thanks,
Stefan
View 4 Replies
View Related
Aug 18, 2015
We have a SQL server 2012 database with size 200mb and Transaction Log has gone up to 34GB.
We have SQL server Mirror is enabled and principal database is running with Full Recovery Model.
How can I truncate/Shrink Log files? Will it impact on existing mirroring setup? I am very much new to SQL server.
View 17 Replies
View Related
Nov 29, 2006
Hi guys , I would like to know what's the exactly results able to get in this following scenario.
Server A : Principal
Server M : Mirror
Server W : Witness
1) Firstly, disable server M, following by server W. So probably i will unable to connect to Server A eventhough it acts as a principal.
2) Next, if I enable either server M or server W , what will I able to get actually ? Is it the mirroring works like the article which written in Microsoft page ?(http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx#ETPAE)
At here, I get an error message and unable to continue the mirroring section. ( "Database EMS is enabled for Database Mirroring, but neither the partner nor witness server instances are available: the database cannot be opened." or "Database 'EMS' is being recovered. Waiting until recovery is finished." )
3) If I enable both server M and server W, the mirroring continue to work again.
Can I have any assistance on (2) issue? Thx for the assistance.
Best Regards,
Hans
View 3 Replies
View Related
Nov 8, 2006
I'm testing mirroring in a high availability mode and getting some odd results. If I kill the SQL Service (or reboot the machine) fail-over works quickly and correctly. If I unplug the Network cable though I will get some of the databases failed over and some of the databases will either be Mirroring / In recovery or Principal / In recovery and never go live. Right clicking on them and going to properties lists there error that the database is unable to communicate with the Partner or Witness. The databases that this will occur on seem to move around (i.e. it isn't always the same databases, but will often be many of the same databases). If I reboot the databases that will not fail over tend to change fairly dramatically. I've seen instances where only 3 databases would fail over (I'm testing 17 databases) and other instances where all but 3 databases would fail over. I've had exactly one test where all databases failed over correctly. Again if I reboot a principal server, fail over works every time.
Has anyone else experienced only partial failovers for network issues or have a work around?
I have temporarily created a script that will force the databases to be the principal, but it's not great for high availability (probably will work for disaster recovery though).
Thanks
Larry
View 1 Replies
View Related
Jul 7, 2006
To configure database mirroring
After connecting to the principal server instance, in Object Explorer, click the server name to expand the server tree.
Expand Databases, and select the database to be mirrored.
Right-click the database, select Tasks, and then click Mirror. This opens the Mirroring page of the Database Properties dialog box.
To begin configuring mirroring, click the Configure Security button to launch the Configure Database Mirroring Security Wizard.
but i do not see any "Mirror" Option in the "Tasks" menu.
Why ?
View 1 Replies
View Related
Jul 15, 2015
I want my database to be mirrored . is it possible to mirror it on the same machine.
I have tried to create a named instance but while trying to configure its not visible.?
Am using SQL SERVER 2008
View 6 Replies
View Related
Oct 27, 2015
I have a 2 node cluster having 4 cores each wherein having 3 instances of SQL 2008 R2 enterprise comprising of 60 databases, 20 on each instance. I need to setup mirroring for each of the databases to a secondary server having 4 cores and 3 instances.
What i understand is that in this case the mirror server will be providing max of 512 worker threads and the 60 mirror databases would consume 240 threads.
What all needs to be checked for looking into the feasibility of going ahead with a async mirror setup as mentioned above.
View 4 Replies
View Related
Mar 28, 2007
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
View 10 Replies
View Related
Nov 27, 2008
When Create Mirror Database Server, Where need to store the Transaction Log backup file?I took FULL Backup of my Primary Database, and I restored at my Mirror Server also. When I try to create a Mirror Database."The remote copy of database "<db_name>" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error:1412)".I am misplacing the Transaction Log backup file. Where I need to store that file?
View 10 Replies
View Related
Aug 26, 2015
I have the following mirroing setup for DR.
Prinical(ServerA) and Mirror(ServerB)
When we do a DR test we do manual failover from Principal(ServerA) to Mirror(ServerB)
Now
ServerB is Prinicpal
ServerA is Mirror
Applications do their testing against the new principal which is ServerB but dont want to take those changes or sync to ServerA(Mirror).
Is there any way to do this without breaking the mirror?
Finally It will go back to original setup after the test which Server A is Principal and Server B will be its mirror.
View 9 Replies
View Related
Feb 16, 2006
We are planning to update our two node, active/passive clustered system to SQL 2005 from SQL2K. Would we be better off to use database mirroring rather than clustering with failover? I believe mirroring takes 3 systems rather than two (one for each mirror and one for monitoring), but can the monitor be a rather low powered PC?
Our current cluster uses two 4-processor Dells and an external PowerVault RAID array with fiber channel connections. Each server has two NICs, one for general network and one for heartbeat and a fiber channel card to connect to the external PowerVault.
Here are my questions:
1 - If I understand it correctly, mirroring uses just separate servers without the need for the rather expensive and complicated shared fiber channel array that must go between them. Do I still need a shared array or can I just use internal RAID arrays in each box?
2 - Are there any advantages to keeping our current system? Our current system has worked well, but the failover takes a couple of minutes. Is mirroring faster?
3 - Is there still a virtual IP address and network name that everyone connects to?
4 - What are the drawbacks?
5 - Can I run non-mirrored DBs on the same servers or are they really locked together tightly?
6 - How much different in configuration can the two boxes be? With the cluster, I really need duplicates in every way.
7 - Do I need a separate license for the SQL Server on each box in the mirror or is one enough, since they work in lock step?
Thanks for any of your experiences on this.
View 7 Replies
View Related
Oct 24, 2015
missing witness server information and the fail-over is broken suddenly? 4:00am no maintenance job. I have one sql job on 10pm for backup on database transaction log only.
I can see the primary have problem then perform fail-over to mirror database, the auto fail-over was broken.
I re-build the sql mirror is OK , but i want to find the root cause.
Windows application event was full when there have many failed event, i have increase log size for application event. Â
View 7 Replies
View Related
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
Jun 30, 2015
we have to build high availability SQL 2012 cluster for VDI and we have two options. One option is to build a server cluster with combination of failover and mirroring and other option is to build failover cluster with AlwaysOn.We are not sure which option to chose. We have contacted Microsoft support to provide us some documents and instructions for failovermirroring combination but they have send us instructions for AlwaysOn option.
What would be best way to build high availability cluster for VDI? Also, since first option is very complicated.
View 5 Replies
View Related
Jun 14, 2007
Hello.
I am confuse and cant decide on how to setup high availability on our SQL 2005. Here's what on my mind and on resources list:
I plan to have mirroring on my SQL1 to SQL2 with the help of SQL3 as witness. So this would be automatic failover. My idea on mirroring is when SQL1 goes down, SQL3 would tell SQL2 to run and be the primary. It will automatically failover to SQL2. Right? My questions are:
1) How can I revert back to SQL1 once it is ready?
2) I read in one of the post that it is impossible to write in a mirrored DB, is this true? I mean, what's the use of failing over to the next node when it's not possible to write and update data/records?
3) If number 2 is false (i hope so), how would the data be synchronize from SQL2 back to SQL1. Those transaction that were made while SQL1 is down.
4) How about the connection string from the web applications? Would it be automatically point to SQL2? We have load balancing setup in place, would this help web application connection to automatically point to SQL2?
Another setup:
We have SAN in place (not yet used, but is planning to use for this SQL thing), EMC to be specific. My question would be:
1) For SAN setup, the data storage would be centralize. So would that mean that SQL1 and SQL2 services will use the same data and log file from the SAN storage?
2) How would you call this setup then? Can this be clustering type of high availability? Will clustering work under load balancing setup? I believe mirroring is not possible here? Right?
3) How can I setup my 3 SQL servers with the same theory in mind: when SQL1 goes down, SQL2 will take over. Data will be synchronize when SQL1 is up and running again. With automatic failover and reverting back to primary.
I read so much topics about this, but the more I research, the more I get confuse.
Any suggestions, comments, advice is greatly appreciated!
View 6 Replies
View Related
Dec 1, 2006
We have an application running with a principal, mirror, and witness. If we bring the principal server down, it looks like failover occurs successfully, but the application is not able to access the database. If we bring the server back (so it is now functioning as the mirror), the application is now able to connect to the database. Why isn€™t the application able to connect without the mirror up? Any suggestions?
View 3 Replies
View Related
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
May 9, 2007
I can establish the mirror with the principal and mirror but cannot add the witness (error 1456) all machines are SQL Server 2005 standard edition service pack 2 principal and mirror are windows server 2003 and witness is windows xp
View 20 Replies
View Related
