Introduction to Database Mirroring

 Introduction to Database Mirroring

Mirroring is mainly implemented for increasing the database availability. Similar to log shipping mirroring is also implemented on per database basis. Database mirroring maintains two copies of a single database that must reside on different instances of SQL Server Database Engine (server instances). Typically, these server instances reside on computers in different locations. One server instance serves the database to clients (the principal server), while the other server instance acts as a hot or warm standby server (the mirror server).

Mirroring provides a hybrid solution i.e

1. Provides a copy of the database like Log Shipping and
2. Rapid failover capabilities like Clustering

Components in Mirroring :

Database mirroring consist of the following components, lets discuss each one in detail.

1. Principal —> The Principal is the originating server i.e it is the source server which contains the database which is configured for mirroring.There can be only one principal database and it has to be in a separate SQL Server instance than the mirror database.

2. Mirror —> The Mirror is the receiving database in a mirror pair i.e it is the destination server which contains the mirrored database.There can be only one mirror for each principal database.The mirror needs to be on its own separate SQL Server instance preferably on separate physical server.

3. Witness —> A Witness is optional and it monitors the Mirrored Pair.It ensures that both principal and mirror are functioning properly.The Witness is also a seperate SQL Server instance preferably on a seperate physical server than principal and mirror.One Witness server can monitor multiple Mirrored Pairs.

4. Quorum —> A Quorum is the relationship between the Witness,Principal and the Mirror.It will be discussed later in this article

5. Endpoint —> Endpoint is the method by which SQL Server Database engine communicates with applications.In the context of Database mirroring endpoint is the method by which the Prinicpal communicates with the Mirror.The mirror listens on a port defined in the endpoint.The default is 5022.Each database mirror pair listens on its own unique port.To list all the database mirror endpoints run,

1	Select * from sys.database_mirroring_endpoints

To list all the endpoints

?

1	Select * from sys.tcp_endpoints

?

Operating modes

SQL Server database mirroring can be set to provide high availability or disaster recovery. Depending on the needs, a DBA can choose among three available modes

• High safety – Data is written and committed on the principal and mirror databases synchronously. Only after committing on both databases, the database application can continue with activity
  • Might produce delay and slower operation because transactions must be committed on both databases
  • If the principal database goes down, two options are available:
    • Do nothing – wait for the principal to become available again. During that time, the SQL Server instance is unavailable. Mirroring will continue where it has stopped
    • Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Possible data loss due to committed transactions on the original principal database which are not yet committed on the mirror currently acting as the principal
• High safety with automatic failover – Three servers are necessary. Data is written and must be committed synchronously both on the principal and mirror databases. Only after committing on both databases, the application can continue running
  • Might produce delay and slower operation because transactions must be committed on both databases
  • If the principal database goes down, only one option is available:
    • Let the automatic failover process complete, the mirrored database becomes the principal
• High performance – the asynchronous communication, data is written and committed on the principal server, and later sent and committed to the mirror server. Automatic failover isn’t possible and the witness server can’t be used
  • The high performance mode is only available in the Enterprise edition of SQL Server
  • If the principal database goes down, three options are available:
    • Do nothing – wait for the principal to become available again. The SQL Server is unavailable. Mirroring will continue where it has stopped
    • Force the SQL Server instance on the mirror database – the mirror database becomes the principal. Greater possibility for data loss, due to asynchronous communication between databases
    • Manual update – to reduce data loss, take the tail of the log backup if the failed server allows, remove mirroring and restore the tail of the log on the previously mirrored database

Prerequisites for Database Mirroring

1. Make sure that the two partners, that is the principal server and mirror server, are running the same edition of Microsoft SQL Server 2005. The partners require either SQL Server 2005 Standard Edition, SQL Server 2005 Enterprise Edition or SQL Server 2005 Developer Edition.

2. If you are using a witness, make sure that SQL Server 2005 is installed on its system. The witness can run on any reliable computer system that supports SQL Server 2005 Standard Edition, Enterprise Edition, Workgroup Edition, SQL Server 2005 Developer Edition or Express Edition.

3. SQL 2005 SP1 or later version is required for Mirroring

4. The principal database must be in the FULL recovery model. Log records that result from bulk-logged operations cannot be sent to the mirror database.

5. Verify that the mirror server has enough disk space for the mirror database.

6. All of the server instances in a mirroring session should use the same master code page and collation. Differences can cause a problem during mirroring setup.

7. The mirror database must have the same name as the principal database.

8. The mirror database must be initialized from a restore of the principal database with NORECOVERY, followed by restores in sequence of principal transaction log backups.Prior to configuring mirroring ensure that atleast 1 tran log is restored in addition to full backup with NORECOVERY mode.

SQL Server Installation and Setup Best Practices

Installing SQL Server, especially on standalone servers, is a relatively easy process. However, efficiently installing SQL Server, is a whole different story. Via this article, I will be sharing with you, some useful tips regarding SQL Server Installation and Setup Best Practices. The list of best practices presented in this article, is not exhaustive, but I believe, they help a lot, towards efficiently installing a new SQL Server instance.

 

Planning/Preparation

The first thing to do for installing a new SQL Server instance, is not the installation process itself, but rather perform the proper planning.

To this end, you need to make sure that you are allocating an adequate amount of CPUs and RAM, based on the estimated workload to be processed by SQL Server.

Also, regarding storage, please check the below recommendations.

• Provision of adequate disk space
• Best Practice: Make sure that you plan for the right disk capacity for your data, logs, backups and tempdb files.
• Proper allocation unit size for drives
• Best Practice: Usually, a 64KB allocation unit size is used for data, logs, and tempdb file drives.
• Benchmark drives with diskspd
• https://github.com/microsoft/diskspd
• Using the above tool, you can get useful performance info about the storage/disks you will be allocating for SQL Server, and thus check if the available throughput will be adequate for your SQL Server instance’s performance needs.

Installation Process – Feature Selection

The first step when you begin the SQL Server installation process, is the feature selection. This is the process, where among other, you select which features of SQL Server to install.

Below, I list the sub-steps of this process, along with providing the best practices recommendations.

• Instance features
• Database Engine Services, Replication, Full-Text Search, etc.
• Shared Features
• Client Tools Connectivity, Integration Services, etc.
• Paths
• Instance root directory, Shared feature directory, Shared feature directory (x86)

• Best Practices:
• Install only the features you need – C drive can be used for the above paths.

 Installation Process – Instance Configuration

The nest step in the installation process, is the instance configuration options. In this dialog, you are presented with 2 options:

• Option 1: Default Instance
• You connect to SQL Server, by only specifying the server name or IP (i.e. server1)
• Option 2: Named Instance
• You connect to SQL Server by specifying the server name or IP, along with the instance name (i.e. server1/instance1)

• Best Practice:
• If you will just be using one instance on the server, you can go with the default instance option, otherwise, use named instances.

Installation Process – Server Configuration

The next step, is to select the server configuration options. To this end, in this step, you will have to set up the service accounts as per below.

Service Accounts

• SQL Server Agent
• SQL Server Database Engine
• SQL Server Browser

• Best practices:
• Use domain users (not admin) account for Database Engine and Agent. If not on a domain, use local windows accounts as service accounts.
• Use local service for Browser service.

The “Perform Volume Maintenance Task” Privilege

Another option you are presented with in this step, is to grant the “Perform Volume Maintenance Task” privilege to SQL Server Database Engine Service. This allows using the “Database Instant File Initialization” option which allows faster database creation.

• Potential security risk

• Possibility of someone hacking into your server and possibly read the non-zeroed memory spaces and retrieve that data

• Best practice:
• To be used only when SQL Server will be hosting large databases and only in the case you took adequate mitigation actions for the above security risk.

 

Installation Process – Database Engine Configuration

Next, you are presented with the Database Engine configuration options. This configuration step, contains multiple, critical configuration sub-steps which are presented below, along with the relevant best practices.

• Server Configuration
• Authentication Mode
• Windows Authentication Mode
• This option, allows only Windows/Active Directory users to connect to the SQL Server instance.
• Mixed Mode (SQL Server authentication and Windows authentication)
• This option, besides allowing Windows/Active Directory users to connect to the SQL Server instance, it also allows creating local SQL Server users with a username/password, for connecting to the SQL Server instance.
• Best Practice:
• The best practice is to use Windows Authentication (given that the applications to be connecting to SQL Server, work with this option).
• Specify SQL Server Administrators
• You can add current user and/or group or other users.
• Critical: Don’t lock yourself out – make sure that you add at least one a SQL Server administrator.
• Data Directories
• Data root directory
• System database directory (read only – inherits from the data root directory)
• Never use the OS drive
• User database directory, User database log directory, Backup directory

• Best Practices for Data Directories:
• Use separate physical disk pools for database data and log files
• Use separate physical disk pools for tempdb data and log files

• TempDB
• For data files
• Number of files
• Initial size (MB)
• Autogrowth (MB)
• Data directories

• Best Practices for TempDB Data Files
• Use the same number of tempdb files as the number of logical processors up to 8 logical CPUs (if more than 8, don’t add more unless you observe contention).

• For log files
• Initial size (MB)
• Autogrowth (MB)
• Log directory

• Other Best Practices for TempDB:
• Use 2 dedicated disks for TempDB data and log files for parallelism

 

• MaxDOP
• Maximum degree of parallelism
• Distributes a SQL Server request for parallel execution against the available logical CPUs
• SQL Server installation wizard (since the 2016 version), recommends the MaxDOP value to be based on the available NUMA nodes on the server (formula)
• Best Practices:
• Usually the recommended MaxDOP value is OK.
• If you add more NUMA nodes in the future, you might need to revise this value.
• Memory (RAM)
• Min
• Max
• Recommendation is automatically given based on available RAM resources
• Best Practices:
• Make sure you leave an adequate amount of RAM for the OS.
• If you add more RAM in the future, you will need to revise this value.
• FILESTREAM
• Use only if required.

 

Post-Installation Initial Setup

Below, I’m also providing some basic, initial post-installation steps for SQL Server:

• Configure the “Model” System Database
• Recovery Mode
• Initial size and autogrowth settings for data and log files
• … (any other settings you want to include for your new databases that you create on the instance)
• Note: The Model system database serves exactly what its name implies: is the template for every new user database that is created on the SQL Server instance. To this end, whatever settings this database has, are inherited by default to each new database that is created.
• Enable Failed Login Auditing in the SQL Server Security Settings (if not enabled)
• Enable TCP/IP and change the default port from 1433 to something else (range you can use: 49152–65535)
• Read the MS Docs article for SQL Server Security and take the relevant actions