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.
Author |
Topic |
Norwich
Posting Yak Master
158 Posts |
Posted - 2006-06-12 : 05:01:21
|
HiI'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????RegardsNThe 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.ThanksNathan Skerl |
|
|
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)RegardsNThe revolution won't be televised! |
|
|
Norwich
Posting Yak Master
158 Posts |
Posted - 2006-07-17 : 04:24:40
|
Hi NathanI 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.RegardsNThe revolution won't be televised! |
|
|
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. :) |
|
|
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 = 0Can you point me in the right direction?RegardsNThe revolution won't be televised! |
|
|
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. :) |
|
|
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 PartnershipsAlso, 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 AdventureWorksSET PARTNER ='TCP://nskerlxp.MyDomain.Local:10111'GO----------------------------------------------------------- PRINCIPLE: Partner PRINCIPLE with MIRROR---------------------------------------------------------ALTER DATABASE AdventureWorksSET PARTNER = 'TCP://nskerlxp.MyDomain.Local:10112'GONathan Skerl |
|
|
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 PerfStatsSET PARTNER ='TCP://sldews197.domain.local:5022'GO----------------------------------------------------------- PRINCIPLE: Partner PRINCIPLE with MIRROR---------------------------------------------------------ALTER DATABASE PerfStatsSET PARTNER = 'TCP://SLDEWS197.domain.local:5023'GO It's returning the follwoing error now: quote: Msg 1452, Level 16, State 6, Line 4The 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 5The 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!RegardsNThe revolution won't be televised! |
|
|
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;GOCREATE ENDPOINT MirroringEndPoint_Principle STATE=STARTEDAS TCP (LISTENER_PORT=10111)FOR DATABASE_MIRRORING (ROLE=PARTNER) -- Enabled as Partner onlyGO----------------------------------------------------------- PRINCIPLE: Create WITNESS --> PRINCIPLE Login---------------------------------------------------------USE master;GO--CREATE LOGIN [MYDOMAIN\nskerl] FROM WINDOWS ;--GOGRANT CONNECT ON ENDPOINT::MirroringEndPoint_Principle TO [MYDOMAIN\nskerl];GO----------------------------------------------------------- MIRROR: Create EndPoint---------------------------------------------------------USE AdventureWorks;GOCREATE ENDPOINT MirroringEndPoint_Mirror STATE=STARTEDAS TCP (LISTENER_PORT=10112)FOR DATABASE_MIRRORING (ROLE=ALL) -- enabled as Witness or PartnerGO----------------------------------------------------------- MIRROR: Create WITNESS --> MIRROR Login---------------------------------------------------------USE master;GO--CREATE LOGIN [MYDOMAIN\nskerl] FROM WINDOWS;--GOGRANT CONNECT ON ENDPOINT::MirroringEndPoint_Mirror TO [MYDOMAIN\nskerl];GO----------------------------------------------------------- WITNESS: Create EndPoint---------------------------------------------------------USE Master;GOCREATE ENDPOINT MirroringEndPoint_Witness STATE=STARTEDAS 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;--GOGRANT 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 AdventureWorksGOBACKUP DATABASE AdventureWorksTO DISK = 'C:\AdventureWorks_Data.bak'WITH FORMATGOBACKUP LOG AdventureWorksTO DISK = 'C:\AdventureWorks_Log.bak'WITH FORMAT----------------------------------------------------------- MIRROR: Restore from PRINCIPLE backup---------------------------------------------------------RESTORE FILELISTONLYFROM DISK='C:\AdventureWorks_Data.bak'GORESTORE 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';GORESTORE LOG AdventureWorks FROM DISK='C:/AdventureWorks_Log.BAK' WITH NORECOVERYGO -- 3. Setup Mirroring Partnerships----------------------------------------------------------- MIRROR: Partner MIRROR with PRINCIPLE ---------------------------------------------------------ALTER DATABASE AdventureWorksSET PARTNER ='TCP://nskerlxp.MYDOMAIN.Local:10111'GO----------------------------------------------------------- PRINCIPLE: Partner PRINCIPLE with MIRROR---------------------------------------------------------ALTER DATABASE AdventureWorksSET PARTNER = 'TCP://nskerlxp.MYDOMAIN.Local:10112'GO----------------------------------------------------------- PRINCIPLE: Position WITNESS in quorum---------------------------------------------------------ALTER DATABASE AdventureWorksSET WITNESS = 'TCP://nskerlxp.MYDOMAIN.Local:10113'GO-- 4. InspectSELECT 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_lsnFROM sys.database_mirroringWHERE mirroring_guid IS NOT NULL;-- 5. Forcing a Failover on PRINCIPLEALTER DATABASE AdventureWorks SET PARTNER FAILOVERGO Nathan Skerl |
|
|
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 okBut 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 AdventureWorksDWgo:ON ERROR EXITgo:CONNECT $(PrincipalServer)-- Mirroring ONLY supports the FULL Recovery ModelALTER DATABASE $(Database2Mirror) SET RECOVERY FULLgoUSE $(Database2Mirror)goCREATE 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 INITGO: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 OUTPUTSELECT @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 OUTPUTIF @SQLDataRoot IS NULLBEGIN RAISERROR ('Did not find the correct SQL Data Root Directory. Cannot proceed. Databases backed up but not yet restored.', 16, -1)ENDCREATE 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 sysnameDECLARE FileListCursor CURSOR FAST_FORWARD FOR SELECT LogicalName, PhysicalName FROM #BackupFileListOPEN FileListCursorFETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalNameSELECT @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, @PhysicalNameWHILE @@FETCH_STATUS <> -1BEGIN SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName + ''' TO ''' + @PhysicalName + '''' FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalNameEND-- 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 @ExecStrEXEC (@ExecStr)DEALLOCATE FileListCursorGO:CONNECT $(PrincipalServer)SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns ONLINESELECT 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 sdWHERE sd.[database_id] = db_id(N'$(Database2Mirror)')go:CONNECT $(MirrorServer)SELECT DATABASEPROPERTYEX(N'$(Database2Mirror)', N'Status') -- Returns RESTORINGSELECT 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 sdWHERE 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'GOALTER 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'GOSELECT 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 sdWHERE 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'GOALTER 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'GOand the log is: Msg 1452, Level 16, State 6, Line 3The 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! |
|
|
|
|
|
|
|