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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - SQL Server 2005/Yukon
 DBM Endpoints
 Forum Locked
 Printer Friendly
Author  Topic Next Topic  

Norwich
Posting Yak Master

Kenya
158 Posts

Posted - 06/12/2006 :  05:01:21  Show Profile
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

USA
938 Posts

Posted - 06/16/2006 :  14:36:48  Show Profile
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

Kenya
158 Posts

Posted - 06/21/2006 :  05:31:00  Show Profile
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

Kenya
158 Posts

Posted - 07/17/2006 :  04:24:40  Show Profile
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

United Kingdom
1870 Posts

Posted - 07/17/2006 :  05:45:36  Show Profile  Visit mr_mist's Homepage
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

Kenya
158 Posts

Posted - 07/17/2006 :  06:03:30  Show Profile
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

United Kingdom
1870 Posts

Posted - 07/17/2006 :  07:26:06  Show Profile  Visit mr_mist's Homepage
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

USA
938 Posts

Posted - 07/17/2006 :  20:44:46  Show Profile
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

Edited by - nathans on 07/17/2006 20:47:46
Go to Top of Page

Norwich
Posting Yak Master

Kenya
158 Posts

Posted - 07/18/2006 :  08:48:29  Show Profile
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!

Edited by - Norwich on 07/18/2006 08:50:01
Go to Top of Page

nathans
Aged Yak Warrior

USA
938 Posts

Posted - 07/19/2006 :  12:04:14  Show Profile
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 - 05/03/2007 :  00:43:56  Show Profile
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
   Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.24 seconds. Powered By: Snitz Forums 2000