x

Error during database mirroring configuration

Hi, I have been trying to configure DB Mirriring using the below T-SQL NOTE: I DO NOT have a witness

I am trying to configure Mirroring on a single server with two SQL instances. The SQL box is in a WORKGROUP and not IN DOMAIN. Does this make a difference?

-- 1. Configuring Endpoints --------------------------------------------------------- -- PRINCIPLE: Set Recovery Model Full ---------------------------------------------------------

select recovery_model_desc, * from sys.databases where name = 'AdventureWorks'

--USE master; --GO --ALTER DATABASE AdventureWorks --SET RECOVERY FULL; --GO


  • PRINCIPLE: Create EndPoint


USE AdventureWorks; GO

CREATE ENDPOINT MirroringEndPoint_Principle STATE=STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=PARTNER) -- Enabled as Partner only GO


-- PRINCIPLE: Create WITNESS --> PRINCIPLE Login

USE master; GO --CREATE LOGIN [MYDOMAIN\nskerl] FROM WINDOWS ; --GO GRANT CONNECT ON ENDPOINT::MirroringEndPoint_Principle TO [MYDOMAIN\nskerl]; GO


-- MIRROR: Create EndPoint

USE AdventureWorks; GO

CREATE ENDPOINT MirroringEndPoint_Mirror STATE=STARTED AS TCP (LISTENER_PORT=5023) FOR DATABASE_MIRRORING (ROLE=ALL) -- enabled as Witness or Partner GO


-- MIRROR: Create WITNESS --> MIRROR Login

USE master; GO --CREATE LOGIN [MYDOMAIN\nskerl] FROM WINDOWS; --GO GRANT CONNECT ON ENDPOINT::MirroringEndPoint_Mirror TO [MYDOMAIN\nskerl]; GO


-- ALL: inspect Endpoints

SELECT * FROM sys.database_mirroring_endpoints;

-- 2. Move the Mirrored DB from the Principle to the Mirror

-- PRINCIPLE: Backup Mirrored DB

USE AdventureWorks GO BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks_Data.bak' WITH FORMAT GO BACKUP LOG AdventureWorks TO DISK = 'C:\AdventureWorks_Log.bak' WITH FORMAT


-- MIRROR: Restore from PRINCIPLE backup

RESTORE FILELISTONLY FROM DISK='C:\AdventureWorks_Data.bak' GO

RESTORE DATABASE AdventureWorks FROM DISK='C:/AdventureWorks_data.bak' WITH REPLACE,NORECOVERY, MOVE 'AdventureWorks_data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\Data\AdventureWorks_data.mdf', MOVE 'AdventureWorks_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\Data\AdventureWorks_Log.ldf'; GO RESTORE LOG AdventureWorks FROM DISK='C:/AdventureWorks_Log.BAK' WITH NORECOVERY GO

-- 3. Setup Mirroring Partnerships

-- MIRROR: Partner MIRROR with PRINCIPLE

ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://nskerlxp.MYDOMAIN.Local:10111' GO

--------------------------------------------------------- -- PRINCIPLE: Partner PRINCIPLE with MIRROR


ALTER DATABASE AdventureWorks SET PARTNER = 'TCP://nskerlxp.MYDOMAIN.Local:10112' GO****


-- PRINCIPLE: Position WITNESS in quorum

ALTER DATABASE AdventureWorks SET WITNESS = 'TCP://nskerlxp.MYDOMAIN.Local:10113' GO

    1. Inspect SELECT DB_NAME(database_id) AS 'DatabaseName' , mirroring_role_desc , mirroring_safety_level_desc , mirroring_state_desc , mirroring_safety_sequence , mirroring_role_sequence , mirroring_partner_instance , mirroring_witness_name , mirroring_witness_state_desc , mirroring_failover_lsn FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL;

    1. Forcing a Failover on PRINCIPLE

ALTER DATABASE AdventureWorks SET PARTNER FAILOVER GO


When I try to connect to the mirror database , i get the below error: Msg 1418, Level 16, State 1, Line 1 The server network address "TCP://:5023" can not be reached or does not exist. Check the network address name and reissue the command.

Couple of things i have already checked: 1. The ports are started 2. The mirror database is IN RECOVERY state, 3. The PORTS 5022 and 5023 are in LISTENING state as I checked it using netstat -abn COMMAND

Need help ASAP..Help is greatly appreciated

more ▼

asked Mar 19, 2010 at 06:29 AM in Default

avatar image

Mandar Alawani
376 40 43 48

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

First, you indicate that you don't have a witness, why are you trying to add one?

Second, do you have two instances of SQL Server installed? Each instance of SQL Server can have only ONE Database Mirroring Endpoint.

Third, the port numbers you specify in the ALTER DATABASE commands don't match the port numbers assigned when you configured the endpoints.

more ▼

answered Apr 06, 2010 at 03:34 PM

avatar image

Lynn Pettis
275 2 5

(comments are locked)
10|1200 characters needed characters left

My RSS feed to various experts may have paid off...

Follow Pinal Dave's blog post....

Blog here

more ▼

answered Mar 19, 2010 at 07:25 AM

avatar image

sp_lock
10.5k 27 37 37

the sql services on both my SQL instances (PRINCIPAL and MIRROR) are running under Local System account.

Mar 19, 2010 at 08:07 AM Mandar Alawani

Did you also read the page that is linked to in the blog?

Mar 19, 2010 at 09:35 AM sp_lock

can anyone help me on this??..its kinda urgent

Mar 22, 2010 at 04:47 AM Mandar Alawani

When restored the DB on the mirror did you set the recovery mode?

Mar 22, 2010 at 06:07 AM sp_lock
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2017
x30

asked: Mar 19, 2010 at 06:29 AM

Seen: 5094 times

Last Updated: Mar 19, 2010 at 06:58 AM

Copyright 2016 Redgate Software. Privacy Policy