question

Mandar Alawani avatar image
Mandar Alawani asked

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

sql-server-2005database-mirroring
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Lynn Pettis avatar image
Lynn Pettis answered

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.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered

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

Follow Pinal Dave's blog post....

Blog here

4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

the sql services on both my SQL instances (PRINCIPAL and MIRROR) are running under Local System account.
0 Likes 0 ·
Did you also read the page that is linked to in the blog?
0 Likes 0 ·
can anyone help me on this??..its kinda urgent
0 Likes 0 ·
When restored the DB on the mirror did you set the recovery mode?
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.