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


ADVERTISEMENT

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 - 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

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

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

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

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 :: 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 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

SQL 2012 :: Database Mirroring And NT Authority Account For Database Engine?

Dec 2, 2014

I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?

View 2 Replies View Related

The Database Is Being Closed Before Database Mirroring Is Fully Initialized

Jun 12, 2007

When I issue this command:


ALTER DATABASE foo set PARTNER = 'TCP://10.3.3.1:1234'
I get this error message:




The database is being closed before database mirroring is fully initialized. The ALTER DATABASE command failed.
What does that mean, and how do I fix it?

View 8 Replies View Related

Database Mirroring Hangs On ALTER DATABASE SET PARTNER

Jun 26, 2005

Hi,

View 6 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

Database Mirroring Monitor Not Working For One Database

Apr 2, 2008

I set up a new mirror server. Everything is good except that the Database Mirroring Monitor is not working for one of the databases. In the monitor the principal data is showing up as blank

If I look at dbm_monitor_data on the principal most of the data columns (e.g. Send_queue_size) are null where as they have data for the other databases.


Both servers are SQL Server 9.0.2047 Enterprise Edition.


Any idea what might be going on here?


Thanks in advance.

View 3 Replies View Related

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

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

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 View Related

Database Mirroring

Oct 12, 2007

Hi All,

Does anybody know the OS requirements for Database Mirroring? I checked BOL and Google but couldn't find any info on that.

Thanks.

View 5 Replies View Related

Database Mirroring

Feb 27, 2008

Hi All,

I have read on the web that high protection mode not recommended, except in the event of replacing the existing witness server. But I can't find the reason why anywhere. Can anybody explain? Thanks.

View 2 Replies View Related

Database Mirroring

Mar 12, 2008

Hi All,

Couple of questions about database mirroing.

1. Once the mirroring is setup is it possible to switch between high-protection and high-performance modes? If it is will I have to stop the mirroring, switch the modes and then restart it again?

2. Let's say the principal server went down and I manually failed over to the mirror server. Mirror server runs as a new principal server for a couple of days and then I bring the original principal server back up. What needs to be done in order to bring transactions on the principal server up to date?

Thanks.

View 1 Replies View Related

Database Mirroring

Mar 15, 2007

for a database mirroring , which stretergy is good.
Creating with witness server or without it.?

I am firsttime doing this , so i am posting it to the forum..

and also if we are creating the database mirroring with witness server for automatic failover , will the witness server need the same amount of harddisk space like the pricipal or mirror server.??

thanks

View 2 Replies View Related

Database Mirroring

Jul 30, 2007

we want to migrate our production server , without taking the database down.so what we did was , we mirrored the databases from production to one of our development servers and we took the prodcution server for uprgrade.

so today we are moving back (mirroring) all the databases from that development server to our new production server.

my question is , when i move

productionserver_old(principal server) - having the script for principal server

development server(mirror server---->principalserver)) - having the script for mirror server , which is now acting as principal server

new production server(mirror server) - having the script for mirror server. will become principal server , once i moved all the databases

now while mirroring between the developement server and the new production server, i have to run the scripts for the mirror server only.

so both the servers are havign the mirror script now...

is that a problem, or any other way i can handle this....

View 12 Replies View Related

Database Mirroring

Mar 21, 2008

Hi All,

Does anybody know any good Tutorials for Database Mirroring (Automatic Failover) or Manual Sync with Mirror Server. I tried some sites online, but doesn't have detailed steps..
pls.help as i need to sync data with One of my Mirror using Sql Server 2005.

thanks

View 2 Replies View Related

Database Mirroring

Mar 14, 2008

Hi,

I have some questions about database mirroring. we have almost 40 databases in one server. Can I set up the database mirroring for all the databases. Is it going to any affect on performance. we have already setup the mirroring on almost 30 databases, and I need to set up for the rest of 10. please some body could help on this. Thank you.

View 3 Replies View Related

Mirroring Between Two Database

Jul 7, 2006

Hi,
i'm a novice of sql server and I have a problem.
I have to reply a server in which there are database that are managed with sql server 2000 sp3; what I must make in order to set up the mirroring between the database of the two server so that the data are always aligned ?

thanks for the attention
best regards

alessandro

View 3 Replies View Related

Database Mirroring

Apr 18, 2007

I am new user of SQl and preparing the exam database mirroring....can you tell me how i start the mirroirng ..what is my first tep..and is there only one instance

View 3 Replies View Related

Database Mirroring

Mar 12, 2008

Hi All,

Couple of questions about database mirroing.

1. Once the mirroring is setup is it possible to switch between high-protection and high-performance modes? If it is will I have to stop the mirroring, switch the modes and then restart it again?


2. Let's say the principal server went down and I manually failed over to the mirror server. Mirror server runs as a new principal server for a couple of days and then I bring the original principal server back up. What needs to be done in order to bring transactions on the principal server up to date?


Thank you.

View 6 Replies View Related

Database Mirroring

Jan 4, 2008



Can we have database mirroring for two databases. Like i have an application where in the db used are two, namely DB1 and DB2, DB1 is used in the connection string and with the help of DB1 the tables and sps of DB2 are handled.

in this case can i go ahead with database mirroring. Please comment and give the idea or link on the same.

View 1 Replies View Related







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