SQL Server Mirroring

Modes

High Performance (asynchonus) - Commit changes on primary and then transfer them to the mirror

Cannot use manual or automatic failover because these require guaranteed consistency between the primary and the mirror.
If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data).
transactions commit without waiting for the mirror server to write the log to disk
Have to use force service which can cause data loss.
http://msdn.microsoft.com/en-us/library/ms189270.aspx
ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
Requires SQL Server Enterprise Edition to use

High Safety without automatic failover (syncronous) - Always commit changes at both the primary and mirror.

Will allow manual failover via mirroring/failover button.
As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
When the partners are connected and the database is already synchronized, manual failover is supported. If the mirror server instance goes down, the principal server instance is unaffected and runs exposed (that is without mirroring the data). If the principal server is lost, the mirror is suspended, but service can be forced to the mirror server (with possible data loss).
This mode not available if witness selected.

High Safety with automatic failover (syncronous) - Always commit changes at both the primary and mirror - requiring witness

Will allow manual failover via mirroring/failover button.
Will allow automatic failover if the primary becomes unavailable (controlled by witness)
As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
Always will commit at both databases. If we lose mirror then primary will stop writing? Tested that it still allows commit if mirror unavailable, however if both mirror and witness unavailable then it will bring the database unavailable.
Principle server has to be able to form a quorum with one other server (mirror or witness) to remain in service.
Automatic failover from principle to mirror occurs when the following conditions are met:
1. databases are syncronized
2. both witness and mirror can connect to each other but the principle server cannot connect to either of the other two.
If Mirror is lost but Principle and Witness can talk to each other to form the quorum then normal service resumes with unavailable mirror.
If Witness is lost but Principle and Mirror can talk to each other to form the quorum then normal service resumes with unavailable witness.
If Primary is lost but Mirror and Witness can talk to each other to form the quorum then failover to Mirror will occur.
If Mirror and Witness is lost then Principle database will be unavailable as needs to talk to one of the 2 other servers to continue and there will be loss of service.
If Principle and Witness is lost then the Mirror database will be unavailable still as no witness means auto failover will not occur and there will be loss of service.

Loss of Service Scenario Summary For High Safety with Automatic Failover

Loss of Service will occur for the following scenarios:
1. Loss of all three servers or communication links
2. Loss of any two servers

Options if Loss of Service occurs

1. Preferable option is always to bring back the quorum by making available the principle and at least one of the mirror or the witness server. This will make the current principle database available again and mirroring should resume if the principle and mirror databases can syncronize.
2. If Principle Server is available but option 1 is not possible then we can make the principle database available again by terminating the mirroring session (telling the principle it's not configured for mirroring any more):
ALTER DATABASE <db name> SET PARTNER OFF;
Database now available
The mirroring session will then have to be re-established by re-entering the mirroring details for that system and starting up syncronization. For CNP specific mirroring configuration details look at CNP Database. The mirror should automatically catch up without an additional restore (unless a transaction log backup has taken place since the mirror became unavailable in which case restore the transaction log in norecovery mode first).
3. If Mirror Server is available but option 1 is not possible then we can manually promote the mirror database to principle role by removing the mirroring set up and changing the state of the database to bring it online.
ALTER DATABASE <db_name> SET PARTNER OFF
RESTORE DATABASE <db_name> WITH RECOVERY;
Database now available.
The mirroring session will then have to be re-established by following the Setting Up SQL Server Mirroring Step 1 procedures (restore of the database will be required on the mirror)

Setting Up SQL Server Mirroring

Note: This is the method without requiring an AD account to run the sql server services
Prerequisites
The SQL Server principle,mirror,witness servers need to be able to talk to each other and therefore all need to be added to active directory.
Step 1
We firstly need to restore a copy of the primary database to the mirror server so we need to restore a database backup and a transaction log backup. It is important that the last transaction log of the backup +1 is still within the transaction log file of the primary database or there will be a gap and the mirroring process won't be able to bring the mirror in line with the primary. Therefore you will need to restore the last backup and transaction log taken (or create a new backup). Restore this database in norecovery mode so its inaccessible to read or write.
From the Primary Database in the management studio right click on properties / mirroring / configure security.
Decide if a witness server is needed (only required for high safety with automatic failover).
Confirm Listener Port and Endpoint name.
Select mirror server instance (include the correct port as part of the connection string e.g "<ServerName>\SQLSERVER,1443" and select "connect".
It will now ask for credential to connect to the mirror server. This is purely for setting up the connecting users and can be given the "sa" account and password.
Leave the accounts blank if the endpoint users have already been set up - and then click finish - otherwise:
Populate principle with NETP\<PRINCIPLE HOSTNAME>$
Populate mirror with NETP\<MIRROR HOSTNAME>$
If applicable witness with NETP\<WITNESS HOSTNAME>$
Select "Finish" to set up the mirroring.
Notes
When choosing the endpoint ports - if a second instance is used for mirroring on the same server with an existing mirrored instance then a different endpoint port will be required on those servers. The witness endpoint can stay at the default 5022. I set the principle and standby ports to 5023 on the second instances. Also note if you do not set the endpoint ports correctly in the wizard on the first time through that you will notice they are greyed out when re-entering the wizard. To get around this delete the endpoint manually on the primary and secondary server using 'drop endpoint mirroring' (assuming default endpoint name 'mirroring' used). It will then be enabled in the wizard and the port + name can be changed and the endpoint will be recreated.
Setting up database mirroring monitor
In SSMS under database,tasks - select database mirroring monitor.
If the mirrored instance keeps showing "waiting for data" and "disconnected", then go into "manage instance connections" and change the connection of the mirror to use "sa" and also ensure the sql browser service is running on both instances. This seems to be a bug as it will only connect through the browser if using a named instance. Save the connection then it should show "connected".
Please ensure all 4 servers are showing connected. If the witness server is not showing connected then you may need to check the witness is populated in database mirroring properties and also failover to the mirror and check the details in there are populated. If not then populate save, failover again and recheck the monitor status again.
Step 2 - Create Logins on Mirror Server
Any user logins created on the primary that map to a database will need to be created with the same SID on the mirror because if they are created manually on the mirror without specifying the same SID they will be given a different unique SID and the database users which are mirrored from the principle will not then map properly to the login on the server. This in turn will cause logins to fail if the mirror gets promoted to a principle. The exception to this is if the users are created using an AD user as the SID's will then be inherited from AD and match.
Once all Logins and users have been created on Primary run the "create mirror users.sql" script on the primary from the pnc workbench area. This will create a script to create logins. We are only interested in the user logins mapped to databases so copy and paste these onto the mirror and run the script in order to create the logins on the mirror with the same SID's as the primary.
Manual Method to Create Endpoint users for each principle, mirror or witness (only applies if problems using the auto method above)
The below users should not need to be set up manually first as they will be created during the mirroring set up process through sql server management studio. However these endpoint connection users should be checked on each of the principle, mirror and witness servers.
On the primary server (in this example rdcmssdbt1r) create a sql server windows login account of the name of the mirror server (in this example mdcmssdbt2r):
USE master;
GO
CREATE LOGIN [netp\rdcmssdbt2r$] FROM WINDOWS;
GO
GRANT CONNECT on ENDPOINT::Mirroring TO [netp\rdcmssdbt2r$];
GO
And vice versa on the mirror server (in this example rdcmssdbt2r) create a windows login account of the name of the primary server (in this example mdcmssdbt1r):
USE master;
GO
CREATE LOGIN [netp\rdcmssdbt1r$] FROM WINDOWS;
GO
GRANT CONNECT on ENDPOINT::Mirroring TO [netp\rdcmssdbt1r$];
GO

Failing Over To The Mirror

To Manually Failover (High Safety Only, Primary Server Available) - Right click the database properties / mirroring from management studio connected to the primary server. Select the "FAILOVER" button to switch the mirror to primary. The primary will then become the mirror. This is more akin to the oracle switchover where both instances are required to be up and syncronized so no data loss will occur and services can be switched back.
To Force Failover (Primary Server Not Available and no Witness set up) - From the mirror database run : ALTER DATABASE <database_name> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
The mirror will become the primary and there could be possible data loss using high performance mode if it has any lag behind the primary.
To Force Failover (Primary Server or Mirror Server not available and witness set up but also not available) - In this scenario a quorum cannot be formed from either the primary or mirror as it cannot talk to the other or the witness. Therefore automatic failover cannot occur.
If the witness is brought back first and the principle is still down then use ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS to make it the principle.If the old principle is then bought back online the mirroring state will be suspended. Mirroring can be resumed by then using ALTER DATABASE <dbname> SET PARTNER RESUME. Note: Clicking "resume" from the mirroring page failed as was prompting for username. Worked fine from command line.
If witness brought back and the mirror is shutdown then primary will be bought back online automatically as it can form a quorum with the witness server.

Resuming Mirroring after disconnect

If there is a mirroring disconnect and a transaction log backup has occurred from the principle after the disconnect then no truncation of the log will occur whilst it is disconnected to enable mirroring to catch up automatically (even when backup taken). This means that the transaction log will continue to grow despite backups until mirroring is re-established and syncronisation completes.
A resume should automatically catch up providing the mirroring was not manually removed to enable the principle to function online (possible in the case of a fully broken quorum with witness).
If mirroring was manually removed then any subsequent transaction log backups from the principle will have to be re-applied to the mirror before it can successfully re-syncronise with the principle.

Transaction Log and transaction log backups during mirroring outage

If Mirroring is paused or the mirror server is unavailable during mirroring then any backup of the transaction log that occurs in this state will not truncate the backup log so it will continue to grow until mirroring is resumed. This is because SQL Server needs to send the transaction log to the secondary server during the mirroring process so will retain it until it is sent. Be mindful during periods of downtime that there is sufficient space on the server for the log to grow.

Search This Blog

Oracle - SQL - MySQL DBA - Life Long Learning Experience