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


-- 4. 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;

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

Mandar Alawani gravatar image

Mandar Alawani
376 32 37 44

(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

Lynn Pettis gravatar image

Lynn Pettis
275 3

(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

sp_lock gravatar image

sp_lock
9.2k 25 28 31

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1948
x57

asked: Mar 19, 2010 at 06:29 AM

Seen: 2720 times

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