Saturday, July 30, 2011

Database Mirroring

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
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
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.
backup database OptimizeSQL to disk='\\Class2\backups\OptimizeSQL.bak'
backup log OptimizeSQL to disk='\\Class2\backups\OptimizeSQL.bak'
8.       Go to Mirror Server and restore database by using the Recovery State WITH NORECOVERY
TO'C:\Program Files\Microsoft SQL Server\MSSQL10.FIRST\MSSQL\DATA\OptimizeSQL.mdf',
TO'C:\Program Files\Microsoft SQL Server\MSSQL10.FIRST\MSSQL\DATA\OptimizeSQL_1.ldf',NORECOVERY
FROM DISK=N'\\Class2\backups\OptimizeSQL.bak'
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?
1.       Error No: 1418
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.
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
declare @n int=100
while @n<=1000000
                insert emp values(@n,'Rajesh',60)
                set @n+=1
                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
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
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
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 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
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.

SQL Server Error Logs

* Error Logs maintains events raised by SQL Server database engine or Agent. * Error Logs are main source for troubleshooting SQL Server...

SQL Server DBA Training

SQL Server DBA Training
SQL Server DBA