Introduction
It is another high availability feature available from SQL Server 2005. Previous versions support the simple high availability feature called Transaction Log Shipping. Log shipping has its own limitation as it doesn’t support automatic failover as well as there might be data loss. All these limitation we can overcome with database mirroring.
Database mirroring supports automatic failover and the transactions are applied to standby (Mirror) server immediately once they are committed at principle server. Like Log shipping no need of backup, copy and restore operations and jobs.
Points to remember
· In Principal server database is in ONLINE state.
· In mirror server database is in a restoring state, which means it is not available for incoming requests.
· However, we can create a database snapshot of the mirror database, which provides a point-in-time read-only view of the database.
Advantages and benefits:
· Protection against database failures
· Automatic failure detection and failover
· Support of easy manual failover
· Automatic client redirection
· Multiple operating modes
· No special hardware requirements
· Minimized chance of data loss
· Relatively ease of set up and configuration
FAQ: - What are new features introduced in SQL Server 2008 mirroring?
1. Automatic page repair
Database mirroring can, however, recover from the following errors:
• Error 823: Operating system Cyclic Redundancy Check (CRC) failure
• Error 824: Logical errors including a bad page checksum or torn write
• Error 829: Page has been marked as restore pending
* To view the repaired pages
Select * from sys.dm_db_mirroring_auto_page_repair
2. Log Stream Compression
Log stream compression between the principal and the mirror server to minimize network bandwidth.
2. Mirroring Architecture
Mirroring Operating Modes
* Synchronous
* High Availability (High safety with automatic failover)
* Principal, Mirror and witness
* Supports automatic failover
* High protection
* Principal, Mirror
* No automatic failover
* Asynchronous
* High performance
* Principal, Mirror
* No automatic failover
FAQ: - How to enable mirroring feature in SS 2005 RTM?
Requirements
1. SQL Server 2005 with SP1 or SQL Server 2008
2. Database should be in FULL recovery model.
3. Service Broker should be enabled on the database.
4. Both the servers should have either Enterprise or standard editions.
5. Both the servers should have same edition.
6. Witness server can have any edition.
1. Configuring security and communication between instances
a. Configuring endpoint
b. Creating logins for other servers service accounts
c. Grant connect permission to this logins on endpoints
2. Create mirror database
a. Take full and T.Log backup from principle server and restore it in mirror server with NORECOVERY.
3. Establish mirroring session using ALTER DATABASE command
Steps
1. Go to SSMS
2. Connect 2 or 3 instances
For example
CLASS2\sql2K8 - Principal
CLASS2\FIRST - Mirror
CLASS2\THIRD - Witness
3. Note down the above instances service accounts
a. CLASS2\SQL2K8 (CLASS2\KAREEM)
b. CLASS2\FIRST (CLASS2\KAREEM)
c. CLASS2\THIRD (CLASS2\SQLUSER)
4. Verify both Principal and Mirror has same editions or not i.e. Enterprise or Standard.
By running the following command in both the servers
Select serverproperty('edition')
5. Go to Principal server and create a sample database (In real time environment we have to use existing database) with the name OptimizeSQL
6. Create one sample table in the database with some rows.
7. Take FULL and Transaction Log Backup of OptimizeSQL database in principal server.
Use master
go
backup database OptimizeSQL to disk='\\Class2\backups\OptimizeSQL.bak'
go
backup log OptimizeSQL to disk='\\Class2\backups\OptimizeSQL.bak'
go
8. Go to Mirror Server and create a folder with the name d:\OptimizeSQL_Files and grant read write permissions to service account. Restore database by using the Recovery State WITH NORECOVERY
RESTOREDATABASE OptimizeSQL
FROM DISK='\\Class2\backups\OptimizeSQL.bak'
WITH FILE= 1,
MOVE 'OptimizeSQL' TO 'd:\OptimizeSQL_Files\OptimizeSQL.mdf',
MOVE 'OptimizeSQL_log' TO ' d:\OptimizeSQL_Files\OptimizeSQL_1.ldf' ,NORECOVERY
GO
RESTORE LOG OptimizeSQL
FROM DISK='\\Class2\backups\OptimizeSQL.bak'
WITH FILE= 2,NORECOVERY
GO
9. Configuring Mirroring –
10. Go to Principal Server à Right Click on database OptimizeSQLà Tasks à Mirror
11. Click on Configure Securityà Click Next
| |
Next
12. Select Yes if you have witness instance otherwise select No.
| |
13. Next àNext
14. Select principal instance à Next
15. Click on Connect select Mirror Server instance name (e.g class2\First)
16. Select Connect à Next
17. Once again click on Connect button select Witness Server instance name (CLASS2\THIRD) à Next
18. Enter service accounts
19. Click Next à Finish
20. Close.
21. Select “Do Not Start Mirroring”.
22. Select Start Mirroring
23. Check the status à OK
FAQ: - While configuring mirroring what errors you have faced?
Answer:
Points to Remember
1. One job is created on both the servers
Database Mirroring Monitor Job
2. Default Partner Timeout is 10Sec.
3. How can you say that both the dbs are 100% sync?
a. We can view unsent log and un restored log values. If both are 0 then 100% sync. (In Mirroring Monitor)
b. We can view Mirroring Failover LSN and Replication LSN with sys.database_mirroring. Both should be same.
4. Mirroring States
1. Synchronizing
2. Synchronized
3. Disconnected (If mirror or principal failed)
4. Suspended (If the principal is un available or unable to send transactions to mirror)
5. Pending Failover – If the unsent log is >0.
5. To change mirroring timeout (Run in principal server)
Alter database OptimizeSQL SET PARTNER TIMEOUT 30
Monitoring Mirroring
· We can monitor mirroring using the following options
o Using MSDB tables and Views
o Using Database Mirroring Monitor
o Using Performance Monitor
o Using Profiler
1. Using MSDB tables and Views
o To view complete details of mirroring (In Principal Server)
Select * from sys.database_mirroring
o To view mirroring endpoint details (In Principal Server)
Select * from sys.database_mirroring_endpoints
o To view about Principal, mirror server details and mirroring state run the following query in witness server
Select * from sys.database_mirroring_witnesses
2. Using Database Mirroring Monitor
o We can monitor the following features
* Unsent Log (at principal)
* Un restored Log (at mirror)
* Transaction Rate
* Commit Overhead (Transactions applied rate at mirror)
Ex: Go to principal server and run the following query
use OptimizeSQL
go
declare @n int=100
while @n<=1000000
begin
insert emp values(@n,'Rajesh',60)
set @n+=1
end
b. Right click on OptimizeSQL db --> Tasks -->Launch Database Mirroring Monitor
c. Select "Database Mirroring Monitor"
d. Click on Register Mirror databases
e. Click on Connect and select Mirror Server
f. Select the database OptimizeSQL --> OK
g. Observe the parameters by refreshing (F5) the monitor.
Configuring Thresholds
· Go to Mirroring Monitor à Select “Warnings” tab à Set Thresholds à
3. Using Performance Monitor
a. We can monitor the following counters for Mirrored databases
* We can use the performance object called
"<instanceName>:Database Mirroring"
* Counters which we have to observe regularly
* Bytes sends/sec
* Log Harden time (Commit overhead)
* Total sends/sec
* Transaction delay (Principal)
* Pages Sends/sec
Steps
1. Start à run à perfmon
2. Add counter (Ctrl + I) or Click on +symbol, present on toolbar.
3. Add required counters by selectingMSSQL$SQL2K8:DatabaseMirroringperformance object
4. To view the changes run the previous script.
5. Configuring Alerts
Performing Fail Over
1. Fail over process depends on Operating Modes.
2. If the operating mode is "High safety with automatic failover" then witness server makes mirror db online automatically within the configured timeout.
3. In case of other operating modes we have to perform fail over manually.
In High Performance
1. Run the following command in mirror server
ALTER DATABASE <dbname> SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
2. Transfer the logins.
3. Make the database available to the users and applications.
In High Protection
1. Run the following commands in Mirror server
ALTER DATABASE <dbname> SET PARTNER OFF; (To break mirroring)
2. Database comes into restoring state run the following command to take it online
RESTORE DATABASE <dbname> WITH RECOVERY
Threads created for database mirroring
FAQ :- If mirror server is failed then what is the effect on principle database T.Log file?
Quorum
Quorum is a relationship that exists when two or more server instances in a database mirroring session are connected to each other. Typically, quorum involves three interconnected server instances. When a witness is set, quorum is required to make the database available. Designed for high-safety mode with automatic failover, quorum makes sure that a database is owned by only one partner at a time.
Three types of quorum are possible:
· A full quorum includes both partners and the witness.
· A witness-to-partner quorum consists of the witness and either partner.
· A partner-to-partner quorum consists of the two partners.
Possible Failures during Database Mirroring
· As part of mirroring generally we have two types of errors
· Soft errors
· Hard Errors
· Soft Errors
· Errors identified by SQL Server service i.e. sqlservr.exe is called soft error.
- Network errors such as TCP link time-outs, dropped or corrupted packets, or packets that are in an incorrect order.
- A hanging operating system, server, or database state.
- A Windows server timing out.
· Hard Errors
· Errors identified by windows and notified to sqlservr.exe file are called hard errors.
· A broken connection or wire
· A bad network card
· A router change
· Changes in the firewall
· Endpoint reconfiguration
· Loss of the drive where the transaction log resides
· Operating system or process failure
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.