Error:1418 During Mirroring

Nov 8, 2006

I am trying to do mirroring using SQL Server Ent. 2005 SP1 on two machines. I was able to create end points on both the machines at the port no.s 5091 and 5092 respectively. I have all the security setting settings for both the users. I am getting error no. 1418 while starting mirroring sessions.
Can anybody tell me how to troubleshoot this error?

View 3 Replies


ADVERTISEMENT

Still Need Help With Mirroring (Error 1418)

Mar 17, 2006

Hello again,

This is further to my previous post, which has had over thirty viewing but as yet no replies. Is there really no solution to this problem?

I have now given up trying to use the wizard to get mirroring running, as although I'm not entirely sure, some stuff I've read implies that the wizard only works using Windows authentication. Maybe someone can confirm this. Either way, the wizard doesn't work for me (see my previous post).

So instead of using the wizard I've now tried to set up mirroring manually using SQL statements. Following are the steps I've taken. 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 (EXPLOSION)

There might be more stuff to do after this, but here is where it breaks down. Again, the error is the same as before when using the wizard:

Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://10.152.58.243:7024" can not be reached or does not exist. Check the network address name and reissue the command.

Which is **INCORRECT** or at least highly unhelpful because:
i) netstat -abn shows the sql server process listening on port 7024; no other processes are listening on this port
ii) I can telnet to port 7024 on this machine and issue the 16 keystrokes

Please, someone help, I am crying tears of despair. SQL below.

Cheers,

Mike




/*
The following is a complete list of the SQL statements issued.
Please assume they are issued on the relevant master databases.
*/



/* -------- 1. ENABLE OUTBOUND CONNECTIONS ON THE PRIMARY -------- */

DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE BILL_PRIMARY_CERT
GO
DROP MASTER KEY
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '**************' -- real password used
GO

CREATE CERTIFICATE BILL_PRIMARY_CERT
WITH SUBJECT = 'BILL_PRIMARY_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO

CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BILL_PRIMARY_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO

BACKUP CERTIFICATE BILL_PRIMARY_CERT
TO FILE = 'C:certificatesBILL_PRIMARY_CERT.cer'
GO

-- then copy certificate to other two machines




/* -------- 2. ENABLE OUTBOUND CONNECTIONS ON THE MIRROR -------- */

DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE BILL_SECONDARY_CERT
GO
DROP MASTER KEY
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '**************' -- real password used
GO

CREATE CERTIFICATE BILL_SECONDARY_CERT
WITH SUBJECT = 'BILL_SECONDARY_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO

CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BILL_SECONDARY_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO

BACKUP CERTIFICATE BILL_SECONDARY_CERT
TO FILE = 'C:certificatesBILL_SECONDARY_CERT.cer'
GO

-- then copy certificate to other two machines




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


DROP ENDPOINT Mirroring
GO
DROP CERTIFICATE BILL_WITNESS_CERT
GO
DROP MASTER KEY
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '**************' -- real password used
GO

CREATE CERTIFICATE BILL_WITNESS_CERT
WITH SUBJECT = 'BILL_WITNESS_CERT for database mirroring',
START_DATE = '01/01/2006', EXPIRY_DATE = '01/01/2099'
GO

CREATE ENDPOINT Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=7024
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BILL_WITNESS_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
)
GO

BACKUP CERTIFICATE BILL_WITNESS_CERT
TO FILE = 'C:certificatesBILL_WITNESS_CERT.cer'
GO

-- then copy certificate to other two machines




/* -------- 4. ENABLE INBOUND CONNECTIONS ON THE PRIMARY -------- */

/* enable inbound from the mirror */

DROP CERTIFICATE BILL_SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO

CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO

CREATE CERTIFICATE BILL_SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'c:certificatesBILL_SECONDARY_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO

/* enable inbound from the witness */


DROP CERTIFICATE BILL_WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO

CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO

CREATE CERTIFICATE BILL_WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'c:certificatesBILL_WITNESS_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO




/* -------- 5. ENABLE INBOUND CONNECTIONS ON THE MIRROR -------- */

/* enable inbound from the primary */

DROP CERTIFICATE BILL_PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO

CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO

CREATE CERTIFICATE BILL_PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'c:certificatesBILL_PRIMARY_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO

/* enable inbound from the witness */

DROP CERTIFICATE BILL_WITNESS_CERT
GO
DROP USER MIRROR_WITNESS_USER
GO
DROP LOGIN MIRROR_WITNESS_LOGIN
GO

CREATE LOGIN MIRROR_WITNESS_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_WITNESS_USER
FOR LOGIN MIRROR_WITNESS_LOGIN
GO

CREATE CERTIFICATE BILL_WITNESS_CERT
AUTHORIZATION MIRROR_WITNESS_USER
FROM FILE = 'c:certificatesBILL_WITNESS_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_WITNESS_LOGIN
GO




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

/* enable inbound from the mirror */

DROP CERTIFICATE BILL_SECONDARY_CERT
GO
DROP USER MIRROR_SECONDARY_USER
GO
DROP LOGIN MIRROR_SECONDARY_LOGIN
GO

CREATE LOGIN MIRROR_SECONDARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_SECONDARY_USER
FOR LOGIN MIRROR_SECONDARY_LOGIN
GO

CREATE CERTIFICATE BILL_SECONDARY_CERT
AUTHORIZATION MIRROR_SECONDARY_USER
FROM FILE = 'c:certificatesBILL_SECONDARY_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_SECONDARY_LOGIN
GO

/* enable inbound from the primary */

DROP CERTIFICATE BILL_PRIMARY_CERT
GO
DROP USER MIRROR_PRIMARY_USER
GO
DROP LOGIN MIRROR_PRIMARY_LOGIN
GO

CREATE LOGIN MIRROR_PRIMARY_LOGIN
WITH PASSWORD = '****************' -- real password used
GO

CREATE USER MIRROR_PRIMARY_USER
FOR LOGIN MIRROR_PRIMARY_LOGIN
GO

CREATE CERTIFICATE BILL_PRIMARY_CERT
AUTHORIZATION MIRROR_PRIMARY_USER
FROM FILE = 'c:certificatesBILL_PRIMARY_CERT.cer'
GO

GRANT CONNECT ON ENDPOINT::Mirroring
TO MIRROR_PRIMARY_LOGIN
GO




/* -------- 7. SET MIRROR'S PARTNER TO THE PRIMARY SERVER -------- */

ALTER DATABASE failover_test
SET PARTNER OFF
GO

ALTER DATABASE failover_test
SET PARTNER = 'TCP://10.152.58.242:7024';
GO



/* -------- 8. SET PRIMARY'S PARTNER TO THE MIRROR SERVER -------- */

ALTER DATABASE failover_test
SET PARTNER OFF
GO

ALTER DATABASE failover_test
SET PARTNER = 'TCP://10.152.58.243:7024';
GO


/*
Response:
Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://10.152.58.243:7024" can not be reached or does not exist. Check the network address name and reissue the command.
*/

View 6 Replies View Related

Regarding Collation For Mirroring && Error 1418

Nov 10, 2006

Can I know is it possible that if the database servers' collation in both principal and mirror server might unable to get the mirroring? Because one of database collation is Latin1_General_CI_AS while another server (mirror) is SQL_Latin1_General_CP1_CI_AS. I did several type of troubleshooting but still unable to solve the Error 1418. All the ports are stated as started, able to use telnet to connect to the server's port but still unable to get a connection for the database mirroring. I do really need help on it. Thx for the assistance and kindness.

Best Regards,

Hans

View 4 Replies View Related

Problems On Connection During Mirroring (Error 1418)

Nov 3, 2006

Hi everyone, I am a newbie on setting up database mirroring and right now I am facing the connection problem during mirroring which is Error 1418 which kinda common problem. I just view through all the threads regarding this problems and BOL also. However, I just based on all the guide to setting it up and the result are still the same.

I did tried on both instance names in my server for mirroring and displayed the same results as well. When I type this t-sql on mirror server :

ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://TSUYOSHI:5022' -- which is the principal server

It shows connection ok to me. However, when I type this t-sql on the principal server :

ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://HAN:5023' -- which is the Mirror server

It shows connection ok to me. However, when I type this t-sql on the principal server :

Msg 1418, Level 16, State 1, Line 1

The server network address "TCP://HAN:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational.

I just wondering that why mirror can get a connection to principal but principal can't? Is that any extra configuration especially security settings that needed to be set within the database (MS sql server 2005) ?

I hope I am able to get any assistance from anyone at here and will appreciate on it. Thanx. Anyway, sorry for the inconvenience

Best Regards,

Hans

View 11 Replies View Related

Mirroring Gives Me The Error 1418 And In The Server Log Gives The Error 10054

Aug 16, 2006

Hi,

I am trying to mirror the databse with a witness server.

I have sql server 2005 with SP1 or both my mirror and principle and sql express on my witness.

The problem is when I click start mirroring, I get the following error

The server network address"TCP://serveraddress:7026" can not be reached or does not exist. Check the network address name and that the ports for the local and remote epoints are operational.

(Microsoft SQL server, error:1418)

I have pinged the address it works fine . The telnet comes up with a blank screen.

When I look at the the server log file viewer, I get the following message

An error occured while receiving data: 10054 (An existing connection was forcibly closed by the remote host)

for 'TCP://serveraddress:7026'.

Please help me soon

View 6 Replies View Related

Error 1418, NT AUTHORITYAnonymous Login

May 3, 2007

Hello All!

Can you help me with that problem:

I want to mirror some database to another server (without witness server)

I have 2 machines, where Microsoft SqL '05 Developer Edition SP1 installed.

Two machines working in domain network, one of them is a server, secondmachine enters into domain with username and password.

I can ping each machine by the ip adress and by the networks name, like a.machine.com, b.machine.com

TCP/IP is enabled in Server Configuration.


What I DO:


1. Create full backup of my database on server A

2. Create full backup of transaction logs on server A

3. Restore my database and transaction logs from backup WITH NO RESTORE on server B

4. On machine A: I open tasks -> mirroring properties dialog (configuring mirroring) and go to configure security dialog.

5. I use standart properties (port, endpoint name, ecryption)

6. On page "Mirror Server Instanse" i click "Browse for more" and type there server B adrress (b.machine.com). Click connect.

7. Server configuring was sucsessful

8. Server network addresses were:

Principal : TCP://a.machine.com:5022

Mirror : TCP://b.machine.com:5022

9. I click Start Mirroring but system gives me error:
Error 1418, The Server network address can not be reached or does not exists.

I open logs on machine b (mirror server):
Message

Database Mirroring login attempt by user 'NT AUTHORITYAnonymous login.' failed with error:
'Connection handshake failed. The login 'NT AUTHORITYAnonymous login' does not have CONNECT permission on the endpoint.
State 84.'. [CLIENT: 172.168.0.130]

and this message before:
The database 'mybase' is marked RESTORING and is in a state that does not allow recovery to be run.


When I configured database mirroring, i choose "Windows Authentication", Why 'NT AUTHORITYAnonymous login.' ...??

View 5 Replies View Related

Error 1418 And Good Basic Setup Guide

Jun 16, 2006

Hi,

I'm new to DB Mirroring, and I am trying to get it going in a test enviroment between two SQL 2005 Dev Servers. I have followed documentation that I have found but cannot get past a 1418 error when establishing a connection between the servers initially. Does anyone know of any good step by step guides that I could look at, in case I have missed something stupid.



Thanks in advance



Stupots

View 19 Replies View Related

Error 1418, Fails To Connect Mirror Server

Feb 20, 2006

II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below
a) On primary server:
Error: 1474, Severity: 16, State: 1
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'
b) On mirror server:
Database Mirroring login attempt by user 'NT AUTHORITYANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITYANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.

It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command
GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomainmyuser];
Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints
Hope someone can help me resolve this issue... Thank you very much..

View 44 Replies View Related

Error On Mirroring Job

May 1, 2007

When I use database mirroring, I get the following error when the mirroring monitor runs.

Incorrect syntax near '-'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed.

This is the stored procedure that mirroring creates automatically. It isn't a user sproc. Several other people have posted this error and it seems to be a Microsoft bug.

Any ideas.
Jeff

View 1 Replies View Related

Error While Doing Mirroring

Nov 10, 2006

I got the following error log from SQL profiler when I tried to start mirroring

"
2006-11-10 11:54:47.28 Logon
Database Mirroring login attempt by user 'NT AUTHORITYANONYMOUS LOGON.' failed
with error: 'Connection
handshake failed. The login 'NT
AUTHORITYANONYMOUS LOGON' does not have CONNECT permission on the endpoint.
State 84.'. [CLIENT: 10.77.100.86]"Anybody knows the solution for the same please let me know

View 3 Replies View Related

Error In Database Mirroring

Oct 14, 2006

Hi,

when I tried to mirror database giving principal and mirror server names every details I am getting the error

'Database mirroring is disabled by default. Database mirroring is currently provided for evaluation purposes only and is not to be used in production environments.To enable database mirroring for evaluation purposes, use trace flag 1400 during startup.'

How to user trace flag 1400 during startup?

Since I am newbie Kindly guide me with step by step procedure.

thanks

View 3 Replies View Related

Database Mirroring Error

Aug 23, 2007

When I configured database mirroring between two servers in separate DMZ regions, I get the following error on the principle server when I execute the ALTER DATABASE xxxxxxxx SET PARTNER = '****':

The server network address %%% can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)

Ports have been opened on both machines and I can TELNET both without any problems. I have included the steps that I used.

DATABASE MIRRORING TEST CONFIGURATION
SQL Server 2005 STD
Principal: AA-AAA-AA01
Mirror: BB-BBB-BB07

A. Create certificates for outbound connection on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.
1. In the master database, create a database Master Key.
M:DBADMMIRRORINGCREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO

2. In the master database, create an encrypted certificate on the server instance.
M:DBADMMIRRORINGCREATE_AA01_MIRRORING_CERT
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
WITH SUBJECT = 'AA-AAA-AA01 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO

3. Create an endpoint for the server instance using its certificate.
M:DBADMMIRRORINGCREATE_AA01_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4. Back up the certificate to a file and securely copy it to the other system or systems.
M:DBADMMIRRORINGCREATE_AA01_CERT_BKUP
BACKUP CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
TO FILE = 'M:DBADMMIRRORINGAA_AAA_AA01_MIRRORING_CERT_BKUP.cer';
GO

B. Create certificates for outbound connection on mirror server. Refer to
http://msdn2.microsoft.com/en-us/library/ms186384.aspx.
1. In the master database, create a database Master Key.
M:DBADMMIRRORINGCREATE_MAST_KEY_ENCRY
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<1_Strong_Password!>';
GO

2. In the master database, create an encrypted certificate on the server instance.
M:DBADMMIRRORINGCREATE_BB07_MIRRORING_CERT
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
WITH SUBJECT = 'BB-BBB-BB07 certificate for database mirroring';
GO
SELECT * FROM SYS.CERTIFICATES
GO

3. Create an endpoint for the server instance using its certificate.
M:DBADMMIRRORINGCREATE_BB07_ENDPOINT
CREATE ENDPOINT Endpoint_Mirroring
STATE = STARTED
AS TCP (
LISTENER_PORT=5999
, LISTENER_IP = PARNTER
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
GO

4. Back up the certificate to a file and securely copy it to the other system or systems.
M:DBADMMIRRORINGCREATE_BB07_CERT_BKUP
BACKUP CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
TO FILE = 'M:DBADMMIRRORINGBB_BBB_BB07_MIRRORING_CERT_BKUP.cer';
GO

C. Configure server instances inbound mirroring connections on principal server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.

1. Create a login for other system.
M:DBADMMIRRORINGCREATE_BB07_LOGIN
USE master;
CREATE LOGIN BB07_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@#';
GO

2. Create a user for that login.
M:DBADMMIRRORINGCREATE_BB07_USER
USE master;
CREATE USER BB07_MIRROR_ADMIN FOR LOGIN BB07_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.
M:DBADMMIRRORINGCREATE_BB07_USER_CERT_LINK
USE master;
CREATE CERTIFICATE BB_BBB_BB07_MIRRORING_CERT
AUTHORIZATION BB07_MIRROR_ADMIN
FROM FILE = 'M:DBADMMIRRORINGBB_BBB_BB07_MIRRORING_CERT_BKUP.cer'
GO

5. Grant CONNECT permission on the login for that mirroring endpoint.
M:DBADMMIRRORINGGRANT_BB07_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO BB07_MIRROR_ADMIN;
GO

D. Configure server instances inbound mirroring connections on master server. Refer to
http://msdn2.microsoft.com/en-us/library/ms187671.aspx.

1. Create a login for other system.
M:DBADMMIRRORINGCREATE_AA01_LOGIN
USE master;
CREATE LOGIN AA01_MIRROR_ADMIN
WITH PASSWORD = '1Sample_Strong_Password!@#';
GO

2. Create a user for that login.
M:DBADMMIRRORINGCREATE_AA01_USER
USE master;
CREATE USER AA01_MIRROR_ADMIN FOR LOGIN AA01_MIRROR_ADMIN;
GO

3. Obtain the certificate for the mirroring endpoint of the other server instance.

4. Associate the certificate with the user created in step 2.

M:DBADMMIRRORINGCREATE_AA01_USER_CERT_LINK
USE master;
CREATE CERTIFICATE AA_AAA_AA01_MIRRORING_CERT
AUTHORIZATION AA01_MIRROR_ADMIN
FROM FILE = 'M:DBADMMIRRORINGAA_AAA_AA01_MIRRORING_CERT_BKUP.cer'
GO

5. Grant CONNECT permission on the login for that mirroring endpoint.
M:DBADMMIRRORINGGRANT_AA01_LOG_ACCESS
USE master;
GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO AA01_MIRROR_ADMIN;
GO



E. Back up the Principal Database and copy backup files to mirror server.

F. Restore the Principal Database on the standby database server (use NORECOVERY).

G. Configure the mirroring partners. Refer to http://msdn2.microsoft.com/en-us/library/ms191140.aspx.

1. On the mirror server instance on BB-BBB-BB07, set the server instance on AA-AAA-AA01 as the partner (making it the initial principal server instance).

M:DBADMMIRRORINGACTIVATE_MIRROR_FOR_AA01
--At BB-BBB-BB07, set server instance on AA-AAA-AA01 as partner (principal server):
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://AA-AAA-AA01.mycompany.com:5999';
GO

2. On the principal server instance on AA-AAA-AA01, set the server instance on BB-BBB-BB07 as the partner (making it the initial mirror server instance).

M:DBADMMIRRORINGACTIVATE_MIRROR_FOR_BB07
--At AA-AAA-AA01, set server instance on BB-BBB-BB07 as partner (mirror server).
ALTER DATABASE BESmgmt
SET PARTNER = 'TCP://BB-BBB-BB07.mycompany.com:5999';
GO

3. Execute the following SELECT on both servers to review mirroring information.
SELECT * FROM SYS.DATABASE_MIRRORING



View 9 Replies View Related

Error On Mirroring Snapshot

Feb 2, 2007

Hi guys, does anyone get this snapshot on mirroring. My mirror server had been down for 1 month+ and just up again. Then my principal server will automatic synchronizing with it. Once i create snapshot on the mirror server for checking whether it get the latest record, there's an error msg which shows 'The database must be online to have a database snapshot.' Then I leave it for 1 hour+ for synchronizing and then still the same. Can I get any solution of it? Thx for the assistance.

Best Regards,

Hans

View 1 Replies View Related

Database Mirroring - Port Error

Sep 11, 2007

OS:Windows XP Professional Edition
SQLServer: 2005(DE).SP2(Have installed 2 instances of 2005 in my local system)
I am trying to do database mirroring,via SQL Server Management Studio.
1)configure security setup,(showed success).
2)when i start mirroring,i am getting the following error.

TITLE: Database Properties
------------------------------

An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'MIRROR_DEMO'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://tp190.DELL.servers.corp:5023" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I have followed the microsoft link and i have enabled the port numbers aswell.

View 6 Replies View Related

Database Mirroring Monitor Job Error

Apr 13, 2007

We have recently set up two SQL Server 2005 Standard Edition serverswith database mirroring. The mirrors function and fail over without aproblem, but the Database Mirroring Monitor Job fails every time withthe following error message:Incorrect syntax near '.'. [SQLSTATE 42000] (Error 102) Incorrectsyntax near the keyword 'with'. If this statement is a common tableexpression or an xmlnamespaces clause, the previous statement must beterminated with a semicolon. [SQLSTATE 42000] (Error 319). The stepfailed.Both SQL Servers are running SP2 with the latest patches.Can anyone help with the resolutionto this this issue?Thank you!Bosko

View 5 Replies View Related

Database Mirroring Job Failed With Error

Apr 18, 2008



Hello,

Database Mirroring Monitor Job Error


We have set up two SQL Server 2005 Standard Edition servers
with database mirroring. The mirrors function and fail over without a
problem, but the Database Mirroring Monitor Job fails every time with
the following error message:

Incorrect syntax near 'Virtual'. [SQLSTATE 42000] (Error 102) Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause<c/> the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319). The step failed.,00:00:01,15,319,,,,0
Both SQL Servers are running SP2 with the latest patches.

Does any one have any idea about this error?





View 12 Replies View Related

Error While Configuring Database Mirroring

Dec 18, 2007

Hi all,

I am trying to set up Database mirroring on my test & DR server. But , I am getting the following Error:
( On local PC Mirroring is successful).

Both the test & DR have same service accounts for SQL Server.

Thanks for all your help.

Thanks.

Error:
TITLE: Database Properties
------------------------------

An error occurred while starting mirroring.

------------------------------
ADDITIONAL INFORMATION:

Alter failed for Database 'databasename'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The server network address "TCP://Mirrorserver.domainname.local:5022" can not be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

View 3 Replies View Related

Database Mirroring Transport Error

May 24, 2006

Hi

I am trying to configure Database Mirroring. I had do set dbcc traceon(1400,-1) manually using tsql as the server does not start when configring the startup parameter using the advanced properties of sql server configuration using -T 1400.

After using the db-mirror properties and the wizard in management studio i get an error "database mirroring transport is disabled in the endpoint configuration". I also get this error when executing

alter database set partner="..."

Am I missing something?

Thanks

View 1 Replies View Related

Error Occurred While Starting Mirroring

May 8, 2008



Hi,

I've Configured the Mirroring using the two machines, I've checked up everything,creation of end points successfully on both the machines, databases are in full recovery mode as well.

while Start Mirroring i'm facing following error :

TITLE: Database Properties
------------------------------
An error occurred while starting mirroring.
------------------------------
ADDITIONAL INFORMATION:
Alter failed for Database 'MIS'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Database&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The server network address "TCP://DBD-P3-01047.macmill.com:5023" can not be reached or does not exist. Check the network address name and reissue the command. (Microsoft SQL Server, Error: 1418)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=1418&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
and when i'm using the following command :

ALTER DATABASE [mis] SET PARTNER =N'TCP://DBD-P3-01063.macmill.com:5022'
to start mirroring the following error has come up :

The partner server instance name must be distinct from the server instance that manages the database.

The ALTER DATABASE SET PARTNER command failed.

How can i resolve this issue?Help is greatly appreciated!!!!
- Hitesh

View 8 Replies View Related

Database Mirroring | Can Witness Live On Mirroring Server?

May 3, 2008



Server A = primary SQL DBs (mirroring origination)
Server B = failover SQL DBs (mirroring destination)

For database mirroring a witness is required.
Can the witness live in another instance of SQL on server B?

View 7 Replies View Related

Can't Restart Mirroring. Error 1416 And 1443.

Dec 6, 2006

After stop mirroring, can't restart mirroring. Tried following two things, still can't restart mirroring.

1. Made a full DB backup and a log backup on principle, copied backup files to mirror and restored DB and log with NORECOVERY. Mirror DB is in "Restoring" status.

2. Delete DB on both principle and mirror, recreate DB on the principle, restore DB and log on the mirror with NORECOVERY. Mirror DB is in "Restoring" status.

In both cases, after clicked "Start Mirroring", got error 1416. In sql server log file, got error 1443 ("Database mirroring has been terminated..."). Please help.

View 3 Replies View Related

Error 1413 During Setting Up Mirroring Session

May 28, 2007

hi guys , does anyone of you have a good solution on the Error 1413: Communications to the remote server instance failed before database mirroring was fully started issue?



Best Regards,

Hans

View 1 Replies View Related

SQL Database Mirroring Error: 1443, Severity: 16, State: 2

May 7, 2007

Hello, I am trying to mirror two databases and i receive the following error to the principal error log. "Error: 1443, Severity: 16, State: 2 ".

I HAVE:
1) SQL server 2005 enterprise SP2
2)ServerA And ServerB in the domain.
3)I have installed both ServerA and ServerB sql server with network system account and the services run both as NT AUTHORITYNETWORK SECURITY.
4) The principal database "Test" is on ServerA, i create a full backup and log backup and restore to ServerB the Full backup and the Log with NORECOVERY.So the database "Test" in ServerB is Restoring......
5)I Have created to both Servers Endpoint to Port 5022 with AUTHORIZATION=NT AUTHORITYNETWORK SECURITY.
6)The endpoint are both operational (I have tested through telnet command)....
7)I have added NT AUTHORITYNETWORK SECURITY user to both Security>logins and i gave full permittions sysadmin to the user.
8)I am executing the query "Alter database [TEST] set partner= N'TCP://xx.xx.xx.xx:5022'";
successfully from ServerB (Mirror) but from ServerA i receive that error!!!!

I believe it's network account security issue, but i cannot find a solution for that..... (I also tried to add DCServerA$ user to logins on ServerB AND DCServerB$ user to logins on ServerA cause previously i recaieved an login security error!!!)

View 3 Replies View Related

Where To Find Detailed Error Report When Setting Up Mirroring Fails

Dec 14, 2006

I had a problem with mirroring that I only was able to resolve when I attempted to replicate instead. Replication gave me an error message that expained the problem; mirroring simply failed.

Is there any place where mirroring logs the details of what it is doing & why it is failing?

BTW, the problem was that the server name was changed after SQL was installed. Although i knew this, the forums indicated taht SQL 2005 automatically picks up the new name after it is restarted. This apparently is not true...



Thanks



Dave S

View 2 Replies View Related

Recovery :: Database Mirroring Endpoint Cannot Listen For Connections Due To Error - 10049

Jul 5, 2015

I am getting the following error when creating a endpoint (that will be used for allwayson) The Database Mirroring endpoint cannot listen for connections due to the following error: '10049(The requested address is not valid in its context.)'.

My physical network card is configured with:

10.9.255.170

and iam creating my endpoint with 10.9.255.82

The creation of endpoint is being done with;

CREATE ENDPOINT [Hadr_endpoint]
AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (10.9.255.82)
FOR DATA_MIRRORING (ROLE = ALL, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
IF (SELECT state FROM sys.endpoints WHERE name = N'Hadr_endpoint') <> 0
BEGIN
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED
END
GO
use [master]
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [account]
GO

View 8 Replies View Related

SQL Server 2008 :: Syntax Error When Trying To Change Mirroring For Dedicated Network Through Specific IP

Jun 10, 2015

We have a database in SQL Server 2008 R2 with mirroring and want that replication is done by dedicated network.We stop the endpoint and when we try to run the following command, syntax error occurred:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '192.168.1.14'.

What is the correct syntax of the command line below?

ALTER ENDPOINT Endpoint_Mirroring AS TCP (LISTENER_IP = '192.168.1.14')

View 2 Replies View Related

Mirroring :: Database Mirroring Changes In Application

Oct 12, 2015

Using SQL Server 2008, we would like propose mirroring between two servers of a critical database. Since we initiate, may require to clarify on its purpose and also required changes from application end.Any changes required from OS Level? (I believe both servers IP or Host name should be added in host entries. Mirroring ports should be allowed/open including Principal and mirror server IP Addresses): Windows Team.Any changes required from Application? (Instance name, authentication: user name and its password should be added in web config files): Application Team.Any changes required from Network Team?Also for mirroring both the principal and mirror servers should be with same version, does it only mean SQL Server 2008 versions are enough or does it also mean to say build numbers 10.00.4000 should also be same.URL....

View 5 Replies View Related

Mirroring :: Setting Up Mirroring With Different Vlans?

Sep 15, 2015

I need to set up asyncronous data replication across two clustered instances of SQL 2012 across 2 Datacenters. Both the datacenters have a common domain however the vlans are different. There are only 3 small databases on the primary instance.

any issue in setting up mirroring in this case as vlans are different.

Operating system is Windows 2012.

View 2 Replies View Related

Server Error 1431 When Setting Up Database Mirroring Using Local System Accounts And Certificate Authentication

May 24, 2006

I attempted to setup database mirroring using a High Availability scenario but when I installed SQL is chose to use local system accounts for all the services. Consequently, I stubled upon a microsoft article explaining how to setup mirroring using local system accounts and certificate authentication but I am stil not able to get it to work. When I try ti initiate the mirror from the mirror server I receive an error stating "Neither the partner nor the witness server instance for database "EDENLive" is available. Reissue the command when at least one of the instances becomes available." I have checked all the endpoints and everything seems to be in order. I even checked to make sure that each server was listening on the appropriate ports and I AM able to telnet to the ports. Please help!

View 1 Replies View Related

Mirroring

Mar 7, 2000

Hi All
By mirroring I mean device mirror, since I do not think that there is a database mirror
We only have disk mirror. I also know that SQLServer 7.0 does not provide Mirror, unmirror or remirror. and for this we have to refer to Windows NT disk mirroring.
I need mirroring in both versions SQL Server 6.5 and 7.0

I want to have mirror on another server. I need this for tonight.
Sincerely.

View 1 Replies View Related

Help Need For Mirroring

Apr 1, 2008

I hereby let u know the steps that i undertook for performing mirroring



1. Created a database in Principal server named Mirror1 and one table named tblMirror1.( no values inside the table)

2. Repeated the same in Mirror in Witness

3. Selected the Principal server's database -->right click-->tasks-->mirror

4. from the left hand panel selected Options -->recovery model-->full

5. selected Mirroring from left hand panel-->configure secuirty-->entered mirror and witness name--Success



6. When I click on start mirroring I have an error stating

" Alter failed for database mirror1"

"Database mirroring cannot be enabled because Mirror1 database may have bulk logged changes that have not been backed up etc...."




7. followed this one
BACKUP database mirror1 TO DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'

got the answer as

Processed 160 pages for database 'mirror1', file 'Mirror1' on file 2.
Processed 1 pages for database 'mirror1', file 'Mirror1_log' on file 2.
BACKUP DATABASE successfully processed 161 pages in 0.318 seconds (4.147 MB/sec).

According to sources
Run a restore of this backup on your mirror.
This can be done through the GUI or using a T-SQL command.

The database restore must use the NO RECOVERY option, so the database stays in a loading state.

Also the database name on the mirror must be the exact same name as the principal

RESTORE TestMirror FROM DISK='C:Backup TestMirror_FULL.BAK' WITH NORECOVERY



Hence did the below


RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY

ERROR

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


HENCE MODIFIED AS

use master
go
RESTORE database mirror1 FROM DISK='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK' WITH NORECOVERY

Msg 3201, Level 16, State 2, Line 1
Cannot open backup device 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupmirror1_FULL.BAK'. Operating system error 2(The system cannot find the file specified.).
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

========

Kindly let me know please where am i going wrong

Regards
cmrhema

View 1 Replies View Related

Db Mirroring

Mar 13, 2007

hello

if i run dbcc dbreindex on a database thats the principal in a mirrored pair, will the mirrored database indexes also be rebuilt?

View 1 Replies View Related

Db Mirroring

Mar 27, 2007

i am for the first time creating the database mirroring.I am moving the all the usr databases and everything from test server to a new server called test1.And from that new server i am restoring on the another new server called test2 for database mirroring.But in the test server , the transaction logs are backup for every 15 minutes.So in this case is there a need to move all the logs to test1 and then to test2 for mirroring?? or there is no need to move transaction logs to the new servers at all??


my secnario
------------
server1 server2 server3
-------- ------- --------
test test1 (moving everything from test) test2(mirror for test1 )

all the transaction logs are backup in test for every 15 minutes. so in this case , do i need to move the transcation logs from test to test1 and test2
or there is not need to move at all??


thanks,

View 1 Replies View Related







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