Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 DBM Endpoints

Author  Topic 

Norwich
Posting Yak Master

158 Posts

Posted - 2006-06-12 : 05:01:21
Hi

I've been trying to create a mirrored session with my 2 server instances (no witness). I manage to specify the principal and secondary and create the endpoints succesfully but when I try to run the mirror session then I get a "port" error saying that my ports don't exist. I've checked the port numbers and the are unique. I also checked if the endpoints exist????

Regards
N


The revolution won't be televised!

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-06-16 : 14:36:48
Can you post the code you used to create / configure the endpoints? Also, post any mirroring relevant errors from your error log.

Thanks



Nathan Skerl
Go to Top of Page

Norwich
Posting Yak Master

158 Posts

Posted - 2006-06-21 : 05:31:00
This was done on a client's test box which we subsequently formatted.

I will try running a DBM on another machine and post the error (if it comes up again)

Regards
N

The revolution won't be televised!
Go to Top of Page

Norwich
Posting Yak Master

158 Posts

Posted - 2006-07-17 : 04:24:40
Hi Nathan

I recreated the session and the error I'm getting is :


Alter Failed for database 'PerfStats'. (Microsoft.SQLServer.Smo)

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

The server network address "TCP://machinename.domain.port" can't be reached or does not exist. Check the network address name and the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418)


I checked my endpoints using the following code and they seem to be in order :
SELECT *
FROM sys.endpoints;
on both machines and the endpoints exist.

Regards
N

The revolution won't be televised!
Go to Top of Page

mr_mist
Grunnio

1870 Posts

Posted - 2006-07-17 : 05:45:36
You have to make the endpoint active, if I remember rightly. Have you done that?

-------
Moo. :)
Go to Top of Page

Norwich
Posting Yak Master

158 Posts

Posted - 2006-07-17 : 06:03:30
How or where do I do that?

In the sys.endpoints table the state = 0, the state_desc = STARTED and is_admin_endpoint = 0

Can you point me in the right direction?

Regards
N

The revolution won't be televised!
Go to Top of Page

mr_mist
Grunnio

1870 Posts

Posted - 2006-07-17 : 07:26:06
It's part of the CREATE ENDPOINT statement, but it looks as though it is startd, so that's not the problem.

-------
Moo. :)
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-07-17 : 20:44:46
Lets see the code! Walk us through the code you used to:

- 1. Configure the Endpoints
- 2. Move the Mirrored DB from the Principle to the Mirror Server
- 3. Associate Mirroring Partnerships


Also, is this the actual error code:

quote:
..."TCP://machinename.domain.port" ...


Or are you just generalizing the machinename / domain names there?


After you create the endpoints and move the Mirrored DB from the Principle to the Mirror Server you need to setup the Mirroring Partnerships like below (notice I use different ports because Im doing this test on 1 server, 2 instances):

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


Nathan Skerl
Go to Top of Page

Norwich
Posting Yak Master

158 Posts

Posted - 2006-07-18 : 08:48:29
Wait a minute!

quote:
-1. Configure the Endpoints

I used the Wizard to create the endpoints (Right click on Db and select Tasks then Mirror).

quote:
-- 2. Move the Mirrored DB from the Principle to the Mirror Server

Please elaborate. I backed up the Primary and restred to the Secondary Instance. Then ran the wizard to start the DBM session (and that's where it fails)

quote:
- 3. Associate Mirroring Partnerships

If I understand you, you mean how did I associate the primary and the secondary - to start the session? I used the wizard

quote:
Or are you just generalizing the machinename / domain names there?

I was just generalizing.


My ports are unique - 5022and 5023.
See code below:

---------------------------------------------------------
-- MIRROR: Partner MIRROR with PRINCIPLE
---------------------------------------------------------
ALTER DATABASE PerfStats
SET PARTNER =
'TCP://sldews197.domain.local:5022'
GO

---------------------------------------------------------
-- PRINCIPLE: Partner PRINCIPLE with MIRROR
---------------------------------------------------------
ALTER DATABASE PerfStats
SET PARTNER = 'TCP://SLDEWS197.domain.local:5023'
GO


It's returning the follwoing error now:
quote:
Msg 1452, Level 16, State 6, Line 4
The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.
Msg 1418, Level 16, State 1, Line 5
The server network address "TCP://SLDEWS197.domain.local: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'm confused!

Regards
N

The revolution won't be televised!
Go to Top of Page

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-07-19 : 12:04:14
I would recommend going through this via t-sql... at least we will be able to pinpoint the step causing the error.

Here is the code I used to setup a very simple mirroring session using AdventureWorks. Run this in steps, not top to bottom.


-- 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=10111)
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=10112)
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


---------------------------------------------------------
-- WITNESS: Create EndPoint
---------------------------------------------------------
USE Master;
GO

CREATE ENDPOINT MirroringEndPoint_Witness
STATE=STARTED
AS TCP (LISTENER_PORT=10113)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO

---------------------------------------------------------
-- WITNESS: Create PRINCIPLE/MIRROR --> WITNESS Login
---------------------------------------------------------
USE master;
GO
--CREATE LOGIN [ESINTTECH\nskerl] FROM WINDOWS;
--GO
GRANT CONNECT ON ENDPOINT::MirroringEndPoint_Witness 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



Nathan Skerl
Go to Top of Page

kernelvn
Starting Member

4 Posts

Posted - 2007-05-03 : 00:43:56
Dear all,

I'm also having a problem with configuring Mirroring.

If I make by graphic interface, everything is ok

But when I use the code, it doen't work. Could you please help me to identify the problem in the code?

Here is the code:

:SETVAR PrincipalServer DEMO\PARTNERA
:SETVAR MirrorServer DEMO\PARTNERB
:SETVAR WitnessServer DEMO\WITNESS
:SETVAR Database2Mirror AdventureWorksDW
go

:ON ERROR EXIT
go

:CONNECT $(PrincipalServer)

-- Mirroring ONLY supports the FULL Recovery Model
ALTER DATABASE $(Database2Mirror)
SET RECOVERY FULL
go

USE $(Database2Mirror)
go

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

:CONNECT $(MirrorServer)

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5023)
FOR DATABASE_MIRRORING (ROLE=PARTNER)
GO

:CONNECT $(WitnessServer)

CREATE ENDPOINT Mirroring
STATE=STARTED
AS TCP (LISTENER_PORT=5024)
FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO

:CONNECT $(PrincipalServer)

BACKUP DATABASE $(Database2Mirror)
TO DISK = 'C:\TechReady\$(Database2Mirror).bak'
WITH INIT
GO

:CONNECT $(MirrorServer)

DECLARE @InstanceName sql_variant,
@InstanceDir sql_variant,
@SQLDataRoot nvarchar(512),
@ExecStr nvarchar(max)

SELECT @InstanceName = ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLServer')

EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL',
@InstanceName, @InstanceDir OUTPUT

SELECT @ExecStr = 'EXECUTE master.dbo.xp_regread '
+ '''HKEY_LOCAL_MACHINE'', '
+ '''SOFTWARE\Microsoft\Microsoft SQL Server\'
+ convert(varchar, @InstanceDir)
+ '\Setup'', ''SQLDataRoot'', @SQLDataRoot OUTPUT'

EXEC master.dbo.sp_executesql @ExecStr
, N'@SQLDataRoot nvarchar(512) OUTPUT'
, @SQLDataRoot OUTPUT

IF @SQLDataRoot IS NULL
BEGIN
RAISERROR ('Did not find the correct SQL Data Root Directory. Cannot proceed. Databases backed up but not yet restored.', 16, -1)
END

CREATE TABLE #BackupFileList
( LogicalName sysname NULL
, PhysicalName sysname NULL
, [Type] char(1)
, FileGroupName sysname NULL
, Size bigint
, MaxSize bigint
, FileId smallint
, CreateLSN numeric(25,0)
, DropLSN numeric(25,0)
, UniqueId uniqueidentifier
, ReadOnlyLSN numeric(25,0)
, ReadWriteLSN numeric(25,0)
, BackupSizeInBytes bigint
, SourceBlockSize bigint
, FileGroupId smallint
, LogGroupGUID uniqueidentifier
, DifferentialBaseLSN numeric(25,0)
, DifferentialBaseGUID uniqueidentifier
, IsReadOnly bit
, IsPresent bit
)

INSERT #BackupFileList
EXEC('LOAD FILELISTONLY FROM DISK = ''C:\TechReady\$(Database2Mirror).bak''')

UPDATE #BackupFileList
SET PhysicalName
= @SQLDataRoot
+ N'\Data\'
+ REVERSE(SUBSTRING(REVERSE(PhysicalName)
, 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))

DECLARE @LogicalName sysname
, @PhysicalName sysname

DECLARE FileListCursor CURSOR FAST_FORWARD FOR
SELECT LogicalName, PhysicalName
FROM #BackupFileList

OPEN FileListCursor

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

SELECT @ExecStr = N'RESTORE DATABASE $(Database2Mirror)' +
N' FROM DISK = ''c:\TechReady\$(Database2Mirror).bak''' +
N' WITH MOVE ''' + @LogicalName + N''' TO ''' + @PhysicalName + N''''

FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName
+ ''' TO ''' + @PhysicalName + ''''
FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
END

-- NORECOVERY is required for Database Mirroring, replace is not.
-- Replace is used here solely to allow repetitive use of this script.
SELECT @ExecStr = @ExecStr + N' , NORECOVERY, REPLACE'

-- Useful for testing
-- Only return the string and then comment out the EXEC line below.
-- SELECT @ExecStr

EXEC (@ExecStr)

DEALLOCATE FileListCursor
GO

:CONNECT $(PrincipalServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns ONLINE
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go

:CONNECT $(MirrorServer)
SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORING
SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')
go

:CONNECT $(MirrorServer)

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://DEMO:5023'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5091'
-- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5091'
GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://DEMO:5022'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5092'
-- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5092'
GO

ALTER DATABASE $(Database2Mirror)
SET WITNESS = 'TCP://DEMO:5024'
-- SET WITNESS = 'TCP://Server.fully.qualified.dns.name:5090'
-- SET WITNESS = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5090'
GO

SELECT db_name(sd.[database_id]) AS [Database Name],
sd.mirroring_guid,
sd.mirroring_state,
sd.mirroring_state_desc,
sd.mirroring_partner_name,
sd.mirroring_witness_name,
sd.mirroring_witness_state,
sd.mirroring_witness_state_desc,
sd.mirroring_role,
sd.mirroring_role_desc,
sd.mirroring_role_sequence,
sd.mirroring_safety_level,
sd.mirroring_safety_level_desc,
sd.mirroring_safety_sequence,
sd.mirroring_failover_lsn
FROM sys.database_mirroring AS sd
WHERE sd.[database_id] = db_id(N'$(Database2Mirror)')


The error is begin at:

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://DEMO:5023'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5091'
-- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5091'
GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(Database2Mirror)
SET PARTNER = 'TCP://DEMO:5022'
-- SET PARTNER = 'TCP://Server.fully.qualified.dns.name:5092'
-- SET PARTNER = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5092'
GO

ALTER DATABASE $(Database2Mirror)
SET WITNESS = 'TCP://DEMO:5024'
-- SET WITNESS = 'TCP://Server.fully.qualified.dns.name:5090'
-- SET WITNESS = 'TCP://SQLIDW15HA.redmond.corp.microsoft.com:5090'
GO


and the log is:

Msg 1452, Level 16, State 6, Line 3
The partner server instance name must be distinct from the server instance that manages the database. The ALTER DATABASE SET PARTNER command failed.
** An error was encountered during execution of batch. Exiting.


Please help!
Go to Top of Page
   

- Advertisement -