Replication With Mirror Failover Issues
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
ADVERTISEMENT
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
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
View Related
Jun 13, 2007
Hi i have tried a search on this topic but didn't find much.
I have my fiest sql db live on my we server and all works fine, more luck than skill i think.
But now i wory about backing up my data i have tried various tools backing up the db every 5 minutes and then sending it too another server via ftp this tool works fine but when we have a few people on and the db is accessed frequently it can't back it up i assume this is to do with the backup tool needing an exclusive lock on it.
What is the difference between Replication and data mirror both of these seem to be talked about alot and is there a simple guide somewhere on how to set these up.
Or if i am coming at this from totally the wrong angle then please let me know
Thanxs
View 12 Replies
View Related
Dec 5, 2007
Hi ALL,
I have a problem with merge replication and mirror. I have
Principal server: B is subscriber of A.
Mirror server: D is subscriber of C.
F is distributor and also witness.
D is mirror of B.
C is mirror of A.
I also set parameter PublisherFailoverPartner for Merge agent and log reader agent.
1. Merge replication between A and B is ok
2. Mirror between D and B is ok
Mirror between C and A is ok
3. When i failove, merge replication between D and C does not work. The message is shown
The merge process could not connect to the Publisher 'SGIMIS-2:Mis'. Check to ensure that the server is running. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199368)
Get help: http://help/MSSQL_REPL-2147199368
Another merge agent for the subscription(s) is running or the server is working on a previous request by the same agent. (Source: MSSQLServer, Error number: 21036)
Get help: http://help/21036
Thanks for your supports. You can email me via buihuynhmai@yahoo.com directly.
View 3 Replies
View Related
Aug 28, 2007
All,
We have SQL 2005 db mirror configured with a witness server for high availability. Node 1 is the principal and Node 2 is the mirror. A nightly job creates a snapshot on Node 2. The snapshot is used for previous day reporting queries. We have now been asked to present another copy of the database for near-time reporting. I thought about possibly adding a peer-to-peer replication as part of my environment but was hoping to see what everyone else out there is doing.
Regards,
Ian
View 1 Replies
View Related
Jun 5, 2008
Dear All,
we have one production server and continuously getting the data from application.
we have replication server with transactional replication for the main database.
so, for taking the data, we are using prod server, and for fetching the data, we are using replication server.
now what i'm expecting from you is, if something happend to the main server, there is no cluster except backup. what is the best plan for this?
and one more thing, if replication server fails, then the application will not fetch the data then what is the plan for this?
please give me a good plan for this kind of strategy.
View 5 Replies
View Related
May 11, 2006
Hi All,I would like to set up transactional replication with failover cluster.Did anybody tried this? Does it work together? Is there anything Ishould be coutios when I set up these together? or Is there anyglitches?If somebody helps me I will really appreciate itThanks
View 1 Replies
View Related
Jun 26, 2015
what type of replication is best and easiest to startup on the DR site in event of a failover for an SQL server with very high transaction occurring on the database.I normally handle Oracle database and we use SAN to SAN replication to the DR site, all w have to do is mount the replicated disks at the DR site and Startup the database.How do i achieve this on SQL database including having the using logging details work at the DR site.
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
Sep 29, 2015
if for any reason AG fails over to async node, how replication behaves? As data will not be in sync with previous primary replica, how replication will work? I think that we have to reset replication from scratch as there's a high chance subscribers might be more updated than current primary replica as failover to this node causes data loss. How to keep replication in sync without resetting up? Can we achieve this?
View 5 Replies
View Related
Oct 17, 2007
I'm getting the following error when launching the database mirroring monitor and attempt to connect to my database.
TITLE: Connect to Server
------------------------------
Cannot connect to dborat01.hs.pitt.edu.
------------------------------
ADDITIONAL INFORMATION:
SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'xxxxxx'. (Replication.Utilities)
------------------------------
BUTTONS:
OK
------------------------------
View 3 Replies
View Related
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
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
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
Jan 31, 2008
After adding the Witness Server to the Mirror session, the Witness Connection state between the Mirror and Witness Connection is Disconnected and the state between Principal and Witness Connection is Connected.
The procedures defined in Books Online was used to setup Database Mirroring...when the Witness server was added to the Mirror session, only the alter database T-SQL statement was executed on the Principal server.
ALTER DATABASE <db_name> SET WITNESS = 'TCP://<servername>:<port>'
After executing the above statement, a few seconds later the state between Principal and Witness Connection changed to Connected and the state between Mirror and Witness Connection remains Disconnected.
The Mirror session is not using Certificates, every server is on the same domain, using the same domain login account, and all servers have SP2 installed running Enterprise Edition.
Any idea's why the state between Mirror and Witness Connection remains Disconnected?
Thanks,
View 9 Replies
View Related
Dec 27, 2006
I was trying to test mirroring and now would like to delete the mirror database but it says I need to remove database mirroring first. I deleted the endpoint and cannot figure out how to remove the mirroring. Can someone please help.
View 1 Replies
View Related
Sep 24, 2015
I'm looking at a setup where they have server1 and server2 in a mirroring relationship with automatic failover.Â
Server1 is the principalThey are using transactional replication to replicate asingle databse to server3 is AWS.Distribution database is on Server1All Agents (log reader, snapshot, distributor) run on Server1Server2 has not been set up for replication...My understanding is that in this set up you would normallly place the distribution database on a separate server and enable publication on the mirror, Server2.
What happens if they failover? Replication would stop, and presumably records added while the mirror is the active database would not be marked for replication?How would they recover? Failback and reinintialize
View 3 Replies
View Related
Mar 15, 2006
We have a new server that is 64-bit and I have been approached about using the old 32-bit SQL Server (after a complete format and reinstall) for a mirrored server.
Either that, or buy a new Server with less power that would be used in times of need.
View 4 Replies
View Related
Dec 7, 2007
I have two sql 2005 servers. I want to do mirroring. My question is does mirroring supports store procedures, ssis packages, user log in ids also? I know mirroring supports database level. In this case, mirroring should supports sp and indexes also. Am I right?
View 3 Replies
View Related
Jun 21, 2007
My client wants to provide a redundant server at a remote location. They want to have db access in a worst case "building burns down" scenario. Can I use mirroring to a remote location? Do I want to use mirroring to a remote location? What happens to my mirror set when some knucklehead with a backhoe digs up my high speed cable?
Am I better off, in this case, sticking with log shipping and manually bringing up the stand by server?
View 1 Replies
View Related
Dec 19, 2006
Hi all,
I feel happy to post my first thread at dbforums.com, and I think it's the community of DB professionals.
my question is:
I have two servers in my organization, one of them is running an SQL server database. Is there any way to make the second server a mirror of the first one and to make it runs automatically as the main server when the first server is down?
Thanks for replying soon.
Haytham
View 4 Replies
View Related
Aug 6, 2007
Hi folks,
I am creating an external database for a legacy application and one of the requirements is that every table has to be declared twice in the database:
TABLE_READ and TABLE_WRITE.
Now the idea is that as soon as a change is made to TABLE_WRITE, these
changes should be reflected in TABLE_READ.
Now there are dozens of these tables, and I dont want to have to go
through and write AFTER UPDATE/INSTERT/DELETE triggers for all of
them. Is there an easier way?
- can you use replication within the same database instance (ie, replicating tables instead of instances)
- some kind of table mirroring within a single database?
- is there a good solution using tsql/stored procedures?
Thanks in advance,
Alex
View 2 Replies
View Related
Feb 20, 2007
Hello All!
I have a very simple question.
Im studying for 40-431 and just finished reading about Log Shipping.
It says in the book that when using Log Shipping you cannot run any other log backups because that would affect the LSNs. Can one do log backups with mirroring - principle and mirror?
Because mirroring uses log to keep the mirror up-to-date and synchs on LSN.
Thank you
View 1 Replies
View Related
Jun 21, 2007
Hi,
I've got a problem with the DB mirroring in SQL 2005.
Configuration:
Server 1 Name: ABC001 NIC#1: IP 10.1.1.1 no DNS Server, no Gateway NIC#2: IP 192.1.1.11
Server 2 Name: ABC002 NIC#1: IP 10.1.1.2 no DNS Server, no Gateway NIC#2: IP 192.1.1.12
NIC#1 is an cross-over between the 2 server, NIC#2 is related to the local network.
I cannot setup the mirror with the built-in tool nor by hand.
With the tool I receive the message:
"
One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click Start Mirroring again.
The syntax for a fully-qualified TCP address is:
TCP://<computer_name>.<domain_segment>[.<domain_segment>]:<port>
"
By hand with (Endpoints are ready, added by the tool):
-- Specify the partner from the principal server
ALTER DATABASE [MyDB] SET PARTNER =
N'TCP://ABC002:5022';
also
ALTER DATABASE [MyDB] SET PARTNER =
N'TCP://10.1.1.1:5022';
makes a problem like "other server cannot be reached"
I can ping the other server and use windows sharing. I have create an host entry for ABC002 to use the NIC#1 alltime.
Has anyone an idea how to resolve this?
Thanks!
View 1 Replies
View Related
Feb 21, 2007
Hi,
Trying to build an replication and subscription beteween db in SQL 2000. The db holds our business system (Dynamics NAV, earlier Navision) and several of the tables include $, spaces and even :
We can take some of this away when creating a new db or make scripts for some actions but I wonder if the mirroring in 2005 could handle this?
Thanks,
Mikael
View 3 Replies
View Related
Jan 26, 2007
Im pretty new to DBA world
We have a SQL2005 Standard setup with mirror and witness
I create a Database in the Principle, create a SQLLogon account and give it permission to the database. All works.
I then fail the databse over to SQL2 and the database is there, it has the SQLAccount I create at the database level, but a logon does not work. I notice there is not login account at the database level and If I attempt to create one, I am told there is one already. I try to assign permission to that account for the database and it again replys that there is already on.
Is this refered to as an orphaned logon?
I was a post on Moving logins from on server to another, is that what I must do?
THank you
View 7 Replies
View Related
May 30, 2007
I have a question and excuse me if I look dumb ... but I can't MIRROR MASTER or other dBs. If I lose the server then (primary) ... will the db still be open? do I not require some of these non-mirrorable database to run?
View 3 Replies
View Related
Jul 11, 2007
Hello,
I'm trying to setup database mirroring between 3 servers and the details are as follows:
1. All 3 machines reside in the same domain.
2. Server1 consists of SQL 2005 Ent Edition without SSAS, Server 2 consists of SQL 2005 Ent Ed without SSAS & SSRS, Server 3 consists of SQL 2005 Express Ed.
3. All the 3 machines are having their individual service domain accounts.
4. Server 1 consists of 5 databases which needs to be mirrored to Server 2. I took full backups and tlog backups and restored the full backups in NO Recovery mode on Server 2 and then applied the tlog backups to sync them with Server 1.
5. I created the logins on Server 1 (Server 2 & 3), Server 2 (Server 1 & 3) and Server 3 (Server 1 & 2) and gave "sa" permissions respectively.
6. I kept the first database on Server 1 to full recovery mode, created an endpoint with the default port and granted connection to Server 2 & 3 logins.
7. I created an endpoint on Server 2 with the default port number and granted connection to Server 1 & 3 logins.
8. I created an endpoint on Server 3 with the default port number and granted connection to Server 1 & 2 logins.
9. On Server 2 I'm executing this statement
ALTER DATABASE <dbname>
SET PARTNER = 'TCP://<Server Network Address of Server 1>:<Port No>'
10. On Server 1 I'm executing this statement
ALTER DATABASE <dbname>
SET PARTNER = 'TCP://<Server Network Address of Server 2>:<Port No>'
ALTER DATABASE <dbname>
SET WITNESS = 'TCP://<Server Network Address of Server 3>:<Port No>'
On Step 9 & 10, I'm getting an error message "Msg 1416, Severity 16, State 2 Database <dbname> is not configured for mirroring" in the query window but when I see the error logs individually on Server 1 & 2 I'm seeing "Error: 1443, Severity: 16, State: 2. Database mirroring has been terminated for database 'dbname'. This is an informational message only. No user action is required."
I would appreciate if any one can look into the problem I'm facing. I've googled and could not come up with a solution.
Thanks
View 13 Replies
View Related
Mar 26, 2007
Hope this is the right thread,
When you backup with Mirror?
Is that concurrent to those devices and if so, what happens if one fails does the entire backup fail?
Cheers,
Casey
View 1 Replies
View Related
May 8, 2006
When witness and mirror were down, mirroring was removed at primary. Now when I try to re-establish mirroring I get the following error
Msg 1456, Level 16, State 3, Line 1
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://witness:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
On the witness server the following sql
select * from sys.database_mirroring_witnesses
returns 1 row with the previous mirroring information.
How do I remove old information from witness server? Thanks.
View 16 Replies
View Related
Oct 24, 2007
I've setup sql mirroring which works fine. my problem is that when the mirror takes over from the partner that means the IP address of the SQL server has changed which is coded into the web.config file.
How can I get my web.conig file to recognise 2 sql servers that are being mirrored so it can see whichever one is the principal.
Currently, when the mirror takes over my web application does not work as the IP address of the sql server has changed.
Does anyone know the fix for this?
thnaks
View 1 Replies
View Related
