July 17, 2015 at 6:14 am
Hello everyone
I'm a DBA in training and this is my second post here on SQL Server Central.
--========================================================
Sadly I don't have a spare server to mess about with in the office so I've got a laptop (not connected to any networks) hosting multiple Hyper-V VMs on Windows Server 2012 R2 standard and I'm currently trying to setup Mirroring using SQL Server 2014 (Enterprise Evaluation Edition).
Attemp No2:
--========================================================
Mirror a database across 2 instances using 2 different VMs
On VM machine SERVER1:
-Added "local" to the Primary DNS suffix
-Set Hyper-V Virtual Switch to PRIVATE
-Turned off windows Firewall
-Installed 1 instance of SQL Server 2014 (Instance A)
-Created a database called "DBMirror" on instance A
-Backed up database FULL
-Backed up database LOG
-Copied files over to SERVER2
On VM machine SERVER2:
-Added "local" to the Primary DNS suffix
-Set Hyper-V Virtual Switch to PRIVATE
-Turned off windows Firewall
-Installed 1 instance of SQL Server 2014 (Instance B)
-Restored database FULL using MOVE and NORECOVERY on instance B
-Restored database LOG using NORECOVERY
--========================================================
Instance A > DBMirror > Tasks > Mirror
-Followed GUI
-No Witness
-Instance A set to Port 5022 and Endpoint to "Mirroring" (Encryption OFF)
-Instance B set to Port 5022 and Endpoint to "Mirroring" (Encryption OFF)
-Service accounts page left empty (Not on a domain)
-Finish
-Configuring Endpoints - Successful > Close
Sounds good so far
TCP://SERVER1/A.local:5022
TCP://SERVER2/B.local:5022
Click "Start Mirroring"
-FQDN Warning message > Click Yes
--========================================================
RESULTS:
The Server network address "TCP://SERVER/B.local:5022" can not be reached or does not exist.
Error: 1418
--========================================================
Using the below scripts on the Registered Server section to diagnose the problem I am now perplexed.
All the results which come back as exactly the same on both instances
--========================================================
SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;
SELECT * FROM sys.database_mirroring_endpoints;
--========================================================
July 17, 2015 at 7:02 am
How have you configured your listener?
-- server A
CREATE ENDPOINT VLAN
STATE = STARTED
AS TCP(
LISTENER_PORT = 5022
LISTENER_IP = (10.3.230.10)
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = WINDOWS KERBEROS,
ENCRYPTION = SUPPORTED,
ROLE=ALL);
GO
-- Server B
CREATE ENDPOINT VLAN
STATE = STARTED
AS TCP(
LISTENER_PORT = 5022
LISTENER_IP = (10.3.230.11)
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = WINDOWS KERBEROS,
ENCRYPTION = SUPPORTED,
ROLE=ALL);
GO
July 17, 2015 at 9:17 am
I did the endpoints through the GUI initially.
I've deleted those and ran your script and got the below message:
Msg 9693, Level 16, State 1, Line 2
The Database Mirroring endpoint cannot listen for connections due to the following error: '10049(The requested address is not valid in its context.)'.
July 18, 2015 at 6:54 am
DBA on Route (7/17/2015)
Hello everyoneI'm a DBA in training and this is my second post here on SQL Server Central.
--========================================================
Sadly I don't have a spare server to mess about with in the office so I've got a laptop (not connected to any networks) hosting multiple Hyper-V VMs on Windows Server 2012 R2 standard and I'm currently trying to setup Mirroring using SQL Server 2014 (Enterprise Evaluation Edition).
Attemp No2:
--========================================================
Mirror a database across 2 instances using 2 different VMs
On VM machine SERVER1:
-Added "local" to the Primary DNS suffix
-Set Hyper-V Virtual Switch to PRIVATE
-Turned off windows Firewall
-Installed 1 instance of SQL Server 2014 (Instance A)
-Created a database called "DBMirror" on instance A
-Backed up database FULL
-Backed up database LOG
-Copied files over to SERVER2
On VM machine SERVER2:
-Added "local" to the Primary DNS suffix
-Set Hyper-V Virtual Switch to PRIVATE
-Turned off windows Firewall
-Installed 1 instance of SQL Server 2014 (Instance B)
-Restored database FULL using MOVE and NORECOVERY on instance B
-Restored database LOG using NORECOVERY
--========================================================
Instance A > DBMirror > Tasks > Mirror
-Followed GUI
-No Witness
-Instance A set to Port 5022 and Endpoint to "Mirroring" (Encryption OFF)
-Instance B set to Port 5022 and Endpoint to "Mirroring" (Encryption OFF)
-Service accounts page left empty (Not on a domain)
-Finish
-Configuring Endpoints - Successful > Close
Sounds good so far
TCP://SERVER1/A.local:5022
TCP://SERVER2/B.local:5022
Click "Start Mirroring"
-FQDN Warning message > Click Yes
--========================================================
RESULTS:
The Server network address "TCP://SERVER/B.local:5022" can not be reached or does not exist.
Error: 1418
--========================================================
Using the below scripts on the Registered Server section to diagnose the problem I am now perplexed.
All the results which come back as exactly the same on both instances
--========================================================
SELECT EP.name, SP.STATE,
CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))
AS GRANTOR,
SP.TYPE AS PERMISSION,
CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))
AS GRANTEE
FROM sys.server_permissions SP , sys.endpoints EP
WHERE SP.major_id = EP.endpoint_id
ORDER BY Permission,grantor, grantee;
SELECT * FROM sys.database_mirroring_endpoints;
--========================================================
When mirroring in a workgroup you will need to use certificated logins, there are some workarounds on the internet but cert logins are the supported route
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply