Create mirror between two server without witness (High safety without automatic failover - synchronous).
This exemple use TechNet Virtual Labs :
Exploring AlwaysOn Availability Groups in SQL Server 2014
This script will not change anything in your database, it just will create the scripts for.
All script was create for TechLabs :
Primary Server SQL Server : SQLONE
Secondary Server SQL Server : SQLTWO
Mirroring User : contoso\SQLService
Temp Path : \\SQLONE\s$
Mirroing Port : 5022
ENDPOINT name : Hadr_endpoint
You need change it for yours servers.
We use TechLabs to do this tutorial then we delete all configurations before we start.
** Attention when you run on your SQL Server.**
1. Remove Database Mirroring;
2. Remove Certificates, Master Key, EndPoints. (if it's possible, and maybe, additional steps are necessary).
Step 1: Prepare mirror
1. Add Login user
You need to create a user on AD, and add this user on both SQL Servers
2. Create End Points
You need to use the name of login created the End Points, and you cannot use your login.
Step 2: Mirroring a database
1. Set full recovery model on database.
2. Change owner user to "sa".
It's not necessary, but if you do not change it, you must be sure that you have the same user on secondary server.
3. Set Trustworthy OFF
Trustworthy is always OFF on a new mirror database.
4. Backup database
5. Backup login
6. Restore database
7. Restore log
8. Set partners
* High safety without automatic failover:
All committed transactions are guaranteed to be written to disk on the mirror server.
Manual failover is possible when the partners are connected to each other and the database is synchronized.
The loss of a partner has the following effect:
* If the mirror server instance becomes unavailable, the principal continues.
* If the principal server instance becomes unavailable, the mirror stops but is accessible as a warm standby; the database owner can force service to the mirror server instance (with possible data loss).
* We will use xp_cmdshell.