When we use SQL Server Locally/Home PC, it is not possible to get Domain User to practics Mirroring.
Now I will describe how we can Mirror SQL Server Database, locally/Without Domain User, to another instance.
I assume the Secondary Database is in Virtual Metchin (VM/VDI). [It can be the same PC BUT another instance]
NOTE: Create Inbound & Outbound Port Rule in Windows Firewall. in this example, I am using 5022 port for Mirroring only.
[[ Prepare Secondary Database for Mirroring ]]
Now, I will follow below 5 steps to complete mirroring. for this, I assume below things...
Principal Database Name: TEST_DB
Principal Server: SQLPRIN
Secondary Database Name: TEST_DB [Assuming the Secondary Database is in Restore mode to start Mirroring]
Secondary Server: SQLSEC
Mirror port for both: 5022
NOTE: If you use same PC/Server with a different instance, please use the different port for Principal and Secondary server; like 5022 for Principal and 5023 for Secondary. Make sure the firewall port rule for both ports.
NOTE: All the steps have been divided into two parts. SETP 1 & 2 for Outbout Authentication and STEP 3 & 4 for Inbound Authentication of SQL Server.
ALL OF THE BELOW STEPS SHOULD BE DONE BY A USER WHICH HAS SYSADMIN ROLE.
STEP 1: For authentication, as there is no Domain User, we will use SQL Server Certificate.
We have to create Certificate using Master Key for the both, Principal & Secondary.
First, create a Master Key then create the Certificate
NOTE: If the SQL Server instance already has a Certificate [have been created for another purpose, like Encreption], we may use that Certificate instead of creating a new one.
We can check all the existing certificates using below SQL:
SELECT * FROM sys.certificates;
Principal Server Certificate Creation:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE SQLPRIN_CER
WITH SUBJECT = 'SQLPRIN certificate for database mirroring',
EXPIRY_DATE = '01/01/2020';
Secondary Server Certificate Creation:
USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';
CREATE CERTIFICATE SQLSEC_CER
WITH SUBJECT = 'SQLSEC certificate for database mirroring',
EXPIRY_DATE = '01/01/2020';
STEP 2: Create Endpoint using above certificate to communicate with each other.
NOTE: If you already have an Endpoint for Mirroring with Windows Authentication (NEGOTIATE) and don't want to drop that, then please check the post to create Endpoint which supports both, Windows and Certificate Authentication. "Endpoint for Mirroring with Windows & Certificate Authentication in SQL Server"
-- To check available Endpoints use below SQL
SELECT * FROM sys.database_mirroring_endpoints;
-- To Drop an Endpoint
DROP ENDPOINT Endpoint_Name
Principal Server Endpoint Creation:
USE master;
CREATE ENDPOINT Mirroring_P
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE SQLPRIN_CER
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
Secondary Server Endpoint Creation:
USE master;
CREATE ENDPOINT Mirroring_S
STATE = STARTED
AS TCP (
LISTENER_PORT=5022
, LISTENER_IP = ALL
)
FOR DATABASE_MIRRORING (
AUTHENTICATION = CERTIFICATE SQLSEC_CER
, ENCRYPTION = REQUIRED ALGORITHM AES
, ROLE = ALL
);
NOTE: Choose REQUIRED ALGORITHM for ENCRYPTION as per your requirements. SQL Server supports both, AES & RC4. For more details please check Create Endpoint.
STEP 3: Backup Certificate as a file for the Partner Database (Principal to/from Secondary). Then Copy the .cer file to the partner pc for later use.
Principal Server Certificate Backup:
USE master;
BACKUP CERTIFICATE SQLPRIN_CER TO FILE = 'C:\SQL_Certificate\SQLPRIN_CER.cer';
NOTE: Copy the file, SQLPRIN_CER.cer, to the Secondary server.
Secondary Server Certificate Backup:
USE master;
BACKUP CERTIFICATE SQLSEC_CERTO FILE = 'C:\SQL_Certificate\SQLPRIN_CER.cer';
NOTE: Copy the file, SQLSEC_CER.cer, to the Principal server.
STEP 4: Now will perform below tasks to use above Certificate Files.
- Create Login for Partner
- Create User for above Login (1)
- Create Certificate for above User (2) using Coppid Partner's Certificate File
- Grand Connect to the Endpoint for above Login (1)
- SET Partner Off in Secondary
- Check Windows Firewall Rule is Enabled
- Check the Port is Open (using Telnet)