aiDeeva.com

Category: High Availability & DR

  • How many databases can be mirrored on a single instance of Microsoft SQL Server?

    It is frequently asked by the DBAs or SystemAdmins or Customer that how many databases can be mirrored on a single instance of Microsoft SQL Server?

    Answer of the above question is you can configure 10 databases for 32-bit operating system.On a 32-bit system, database mirroring can support a maximum of about 10 databases per server instance because of the numbers of worker threads that are consumed by each database mirroring session.

    For 64-Bit Operating system you can mirror more than 10 databases depending on the number of processors and worker threads. Many company has deployed more that 10 Databases as mirrored.

  • Peer-to-peer transactional replication and conflict

    Peer-to-peer transactional replication
    Peer-to-peer transactional replication allows you insert, update, or delete data at any node in a topology and have data changes propagated to the other nodes.

    Because we can change data at any node, data changes at different nodes could conflict with each other.

    In SQL Server 2008, peer-to-peer replication introduces the option to enable conflict detection across a peer-to-peer topology. This option helps us prevent the issues that are caused by undetected conflicts.

    When we enable conflict detection, a conflicting change is considered a critical error that causes the Distribution Agent to fail. In the event of a conflict, the topology remains in an inconsistent state until the conflict is resolved and the data is made consistent across the topology.

    To use conflict detection, all nodes must be running SQL Server 2008 or a later version, and detection must be enabled for all nodes. You can enable and disable detection in Management Studio either by using the Subscription Options page of the Publication Properties dialog box or the Configure Topology page of the Configure Peer-to-Peer Topology Wizard.

    You can also enable and disable conflict detection by using the sp_addpublication or sp_configure_peerconflictdetection stored procedures.
    Syntax
    sp_configure_peerconflictdetection [ @publication= ] ‘publication’
    [ , [ @action= ] ‘action’]
    [ , [ @originator_id= ] originator_id ]
    [ , [ @conflict_retention= ] conflict_retention ]
    [ , [ @continue_onconflict= ] ‘continue_onconflict’]
    [ , [ @local= ] ‘local’]
    [ , [ @timeout= ] timeout ]

    sp_help_peerconflictdetection stored procedure returns information about the conflict detection settings for a publication that is involved in a peer-to-peer transactional replication
    topology.

  • RAID Levels

    RAID Levels
    RAID 5:-  RAID 5 will provide good performance throughput and prevent data loss if there is a disk failure. For the database which has high read/write activity, RAID 5 will be a better choice when compared to RAID 0 and RAID 1 in terms of data availability and data protection. For an ideal SQL Server configuration, you should have a combination of RAID 1 and RAID 5. You should mirror the operating system using RAID 1, and place transaction logs on a RAID 1 that is separate from the RAID 1 that hosts the operating system. SQL Server writes data to the transaction logs and maintains serial information of all modifications that occurred in a SQL database. The transaction log files can be used for rollback and roll forward of information from a SQL Server database. The SQL Server files and filegroups should be placed on a RAID 5, because you get best performance throughput by placing database files on RAID 5 disk array.

    RAID 10 RAID 10 is a better choice than RAID 5, but RAID 10 would be more expensive than RAID 5. RAID 10 is a combination of RAID 0+1, which is known as striping with mirroring. You should first mirror disks and then create a stripe set of mirrored disks. This provides high fault tolerance for data and excellent throughput performance.

    RAID 1 RAID 1 is known as disk mirroring. You need a minimum of two disks to form a RAID 1 array. One primary disk is used for read/write operations and the data is replicated to the second disk. This RAID level offers better read performance but slower write performance.

    RAID 0 RAID 0 is known as disk striping. This RAID level stripes data across disks in the array, offering better throughput on the read/write operations. However, there is no data protection offered in this RAID level. If one disk fails, the data stored on the disk will be lost.

  • Database Mirroring Vs Log Shipping

    Please read the below table to find out the diffrence between mirroring and log shipping.

    Database Mirroring Log-shipping
    Database mirroring is functionality in the SQL Server engine that reads from the transaction log and copies transactions from the principal server instance to the mirror server instance.  Database mirroring can operate synchronously or asynchronously. Log shipping is based on SQL Server Agent jobs that periodically take log backups of the primary database, copy the backup files to one or more secondary server instances, and restore the backups into the secondary database(s).  Log shipping supports an unlimited number of secondary’s for each primary database.
    Database mirroring can operate synchronously or asynchronously. If configured to operate synchronously, the transaction on the principal will not be committed until it is hardened to disk on the mirror. Log shipping is always asynchrony. Log shipping totally depends on the log backup and restore schedule
    Database mirroring supports only one mirror for each principal database. That means DB mirroring is at database level Log-shipping can work on database and server level. You can configure multiple databases in logshipping
    Data Transfer:    Individual T-Log records are transferred using TCP endpoints
    Transactional Consistency:  Only committed transactions are transferred
    Server Limitation:   Can be applied to only one mirror server
    Failover:   Automatic
    Failover Duration:  Failover is fast, sometimes < 3 seconds but not more than 10 seconds
    Role Change:   Role change is fully automatic
    Client Re-direction:  Fully automatic as it uses .NET 2.0/.Net 3.0    		 With Log Shipping:

    Data Transfer:    T-Logs are backed up and transferred to secondary server

    Transactional Consistency:  All committed and un-committed are transferred

    Server Limitation:   Can be applied to multiple stand-by servers

    Failover:   Manual

    Failover Duration:  Can take more than 30 mins

    Role Change:   Role change is manual

    Client Re-direction:  Manual changes required
    Support only full recovery model Supports full and bulk-logged recovery model
    Mirror database is always in recovery mode. To read it you have use database snapshot. You can use the stand-by option to read the database on standby server
    Auto Page Recovery introduced with SQL SERVER 2008 so it will recover the damaged pages. Not supported

  • MySQL Replication

    Problem/Error

    Could not parse relay log event entry. The possible reasons are: the master’s binary log is corrupted (you can check this by running ‘mysqlbinlog’ on the binary log), the slave’s relay log is corrupted (you can check this by running ‘mysqlbinlog’ on the relay log), a network problem, or a bug in the master’s or slave’s MySQL code. If you want to check the master’s binary log or slave’s relay log, you will be able to know their names by issuing ‘SHOW SLAVE STATUS’ on this slave

    Resolution Steps

    You have to follow below steps to troubleshoot the error.

    Execute the below command

    SHOW MASTER STATUS

    SHOW SLAVE STATUS

    Check the error log for replication and its position.

    Ideally there are three sets of file/position coordinates in SHOW SLAVE STATUS to identify the correct file

    1) The position, ON THE MASTER, from which the I/O thread is reading: Master_Log_File/Read_Master_Log_Pos.

    2) The position, IN THE RELAY LOGS, at which the SQL thread is executing: Relay_Log_File/Relay_Log_Pos

    3) The position, ON THE MASTER, at which the SQL thread is executing: Relay_Master_Log_File/Exec_Master_Log_Pos

    Next you have to check the error log for the log position to identify the correct binary log file and set the correct log file using below command.

    CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000480′

    Once the problem is resolved you can use Maatkit tool to sync table to multiple slaves.

    mk-table-checksum command is used to check what tables are out of sync and when use mk-table-sync command is used to resync them.