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.
Configuring Mirroring – Steps
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 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
selectserverproperty('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.
usemaster
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 restore database by using the Recovery State WITH NORECOVERY
RESTOREDATABASE OptimizeSQL
FROMDISK='\\Class2\backups\OptimizeSQL.bak'
WITH FILE= 1,
MOVE'OptimizeSQL'
TO'C:\Program Files\Microsoft SQL Server\MSSQL10.FIRST\MSSQL\DATA\OptimizeSQL.mdf',
MOVE'OptimizeSQL_log'
TO'C:\Program Files\Microsoft SQL Server\MSSQL10.FIRST\MSSQL\DATA\OptimizeSQL_1.ldf',NORECOVERY
GO
RESTORE LOG OptimizeSQL
FROM DISK=N'\\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
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
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:
1. Error No: 1418
Solution:
Before starting Mirroring check that logins are created for the respective instance service accounts. If they are not created create manually and grant connect permission on Endpoint, then start mirroring again.
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 unrestored 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)
alterdatabase OptimizeSQL SETPARTNER 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
select*from sys.database_mirroring
o To view mirroring endpoint details
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
FAQ: - What are the major new features introduced in Mirroring 2008 version?
1. Auto Page Repair.
select*fromsys.dm_db_mirroring_auto_page_repair
2. Transactions are sending to Mirror by compressing.
· To view total bytes send from principal and total bytes received at mirror we can use (run in witness server)
select*from sys.dm_db_mirroring_connections
2. Using Database Mirroring Monitor
o We can monitor the following features
* Unsent Log (at principal)
* Unrestored 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 OptimizeSQLdb --> 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.
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
The kinds of threads that a server instance creates for a database mirroring session depend partly on the mirroring roles that the server instance is performing. A given session has some or all of the following threads:
· One global thread for database mirroring communications. This thread is started by Service Broker.
· If the server instance is acting as a mirroring partner (whether it is the principal server or mirror server):
· One thread per mirrored database for event processing.
· One thread per mirrored database for asynchronous tasks (such as log send or log write) that would otherwise block the event thread.
· Whenever the instance is acting as a mirror server:
· One redo manager thread, which submits log for redo, performs page read-ahead, lock reacquisition, and so on.
· In SQL Server Standard, one redo thread per mirror database, or in SQL Server Enterprise, one redo thread per mirror database for every four CPUs. These threads perform the actual log redo.
· If the instance is acting as a witness:
· One global thread for processing the witness messages for all mirroring sessions in which the instance is acting as the witness.
Impact of Pausing a Session on the Principal Transaction Log
At any time, the database owner can pause a session. Pausing preserves the session state while removing mirroring. When a session is paused, the principal server does not send any new log records to the mirror server. All of these records remain active and accumulate in the transaction log of the principal database. As long as a database mirroring session remains paused, the transaction log cannot be truncated. Therefore, if the database mirroring session is paused for too long, the log can fill up.
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.
Manual Fail Over Error
Could not write a checkpoint record in database ID 8 because the log is out of space. Contact the database administrator to truncate the log or allocate more space to the database log files.
Msg 3167, Level 16, State 3, Line 1
RESTORE could not start database 'Hospital'.
Msg 3167, Level 16, State 1, Line 1
RESTORE could not start database 'Hospital'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'Hospital' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.