Configuring Database Mirroring in SQL Server

Database mirroring is a solution for increasing database availability. Here’s a step-by-step guide to configure mirroring in SQL Server:

Step 1: Take a Full Backup of the Principal Database

BACKUP DATABASE [Database_Name] TO DISK = 'Path\Backup.bak';

Step 2: Take a Transaction Log Backup of the Principal Database

BACKUP LOG [Database_Name] TO DISK = 'Path\Backup.trn';

Step 3: Remove the Recovering State of the Mirror Database (if applicable)

RESTORE DATABASE [Database_Name] WITH RECOVERY;

Step 4: Ensure the Backup Folder on the Principal Server is Shared and Accessible from the Mirror Server

Step 5: Restore the Full Backup to the Mirror Server with the NORECOVERY Option

RESTORE DATABASE [Database_Name] FROM DISK = N'\\Principal\Backup\Backup.bak' WITH NORECOVERY;

Step 6: Restore the Transaction Log Backup to the Mirror Server with the NORECOVERY Option

RESTORE LOG [Database_Name] FROM DISK = N'\\Principal\Backup\Backup.trn' WITH FILE = 1, 'NORECOVERY, NOUNLOAD, STATS = 10;

Step 7: Configure Database Mirroring from the Principal Server

1. Right-click the database.
2. Choose "Tasks" > "Mirror" or "Properties" > "Mirroring".

Step 8: Launch the Configure Database Mirroring Security Wizard

• Click the "Configure Security" button.
• Click "Next" if the intro screen appears.

Step 9: Skip Witness Server Configuration

• Select "No" for "Do you want to configure security to include a witness server instance".
• Click "Next" to continue.

Step 10: Configure the Principal Server Instance

• Create an endpoint named "Mirroring" with a Listener Port of 5022.
• Click "Next" to continue.

Step 11: Configure the Mirror Server Instance

• Click "Connect..." and select the mirror server.
• Click "Next" to continue.

Step 12: Service Accounts Configuration

• Leave the fields blank if all server instances use the same domain account.
• Click "Next".

Step 13: Complete the Wizard

• Review the summary of the configuration.
• Click "Finish".

Step 14: Start Mirroring

• Click "Start Mirroring".
• If everything is set up correctly, database mirroring will start successfully and will be fully synchronized.

Backout Plan

1. Remove the mirror configuration.
2. Set the mirror database with RECOVERY.

Test Plan

Verify that Both Servers are Listening on the Same Port

SELECT type_desc, port FROM sys.tcp_endpoints;

Verify Database Mirroring Status on Both Servers

SELECT state_desc FROM sys.database_mirroring_endpoints;

• The state_desc column on both the Principal and Mirror server should be STARTED.

Verify that Roles are the Same on Both the Principal and Mirror Server

SELECT role FROM sys.database_mirroring_endpoints;

By following these steps, you can configure database mirroring in SQL Server to ensure high availability and disaster recovery.

Differences Between Log Shipping, Mirroring, and Replication in SQL Server

Log Shipping

Log Shipping automatically sends transaction log backups from one database (primary database) to another database (secondary database) on a different server. An optional third server, known as the monitor server, records the history and status of backup and restore operations and can raise alerts if these operations fail to occur as scheduled.

Mirroring

Database mirroring is a software solution primarily designed to increase database availability. It maintains two copies of a single database on different server instances of SQL Server Database Engine.

Replication

Replication is a set of technologies for copying and distributing data and database objects from one database to another, then synchronizing between databases to maintain consistency. Using replication, data can be distributed to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Components

• Log Shipping: Primary server, secondary server, and monitor server (optional).
• Mirroring: Principal server, mirror server, and witness server (optional).
• Replication: Publisher, subscribers, distributor (optional).

Data Transfer

• Log Shipping: Transaction logs are backed up and transferred to the secondary server.
• Mirroring: Individual transaction log records are transferred using TCP endpoints.
• Replication: Changes are tracked/detected (either by triggers or log scanning) and shipped.

Server Limitation

• Log Shipping: Configurable as one-to-many (one primary server to many secondary servers) or many-to-one (multiple primary databases on different servers to one secondary server).
• Mirroring: One-to-one (one principal server to one mirror server).
• Replication: Various topologies including central publisher/distributor with multiple subscribers, central distributor with multiple publishers and subscribers, and mixed topologies.

Types of Failover

• Log Shipping: Manual.
• Mirroring: Automatic or manual.
• Replication: Manual.

Database Access

• Log Shipping: Secondary database can be used for reporting in STANDBY mode.
• Mirroring: Mirrored database can only be accessed using a snapshot.
• Replication: Subscriber database is open to reads and writes.

Recovery Model

• Log Shipping: Supports both Bulk Logged and Full Recovery Models.
• Mirroring: Supports only Full Recovery Model.
• Replication: Supports Full Recovery Model.

Restoring State

• Log Shipping: Can be completed using either NORECOVERY or STANDBY option.
• Mirroring: Can be completed using NORECOVERY.
• Replication: Can be completed using RECOVERY.

Backup/Restore

• Log Shipping: Can be done manually or through Log Shipping options.
• Mirroring: Must be done manually by the user.
• Replication: Create an empty database with the same name.

Monitor/Distributor/Witness

• Log Shipping: The monitor server should be separate from the primary or secondary servers. If not used, alert jobs are created locally on the primary and secondary servers.
• Mirroring: The principal server cannot act as both principal and witness server.
• Replication: The publisher can also be the distributor.

Types of Servers

• Log Shipping: All servers must be SQL Server.
• Mirroring: All servers must be SQL Server.
• Replication: The publisher can be an Oracle Server.

SQL Server Agent Dependency/Jobs

• Log Shipping: Yes, involves four jobs handled by SQL Server Agent jobs (backup, copy, restore, alert).
• Mirroring: Independent of SQL Server Agent.
• Replication: Yes, involves Snapshot Agent, Log Reader Agent, and Distribution Agent (transactional replication), and Merge Agent (merge replication).

Requirements

Log Shipping:

• Same logical design and collation settings for involved servers.
• Databases must use Full or Bulk-Logged Recovery Models.
• SQL Server Agent should start automatically.
• Sysadmin privileges are required on each server.

Mirroring:

• No differences in system collation settings.
• Identical local Windows groups and SQL Server login definitions on both servers.
• Same external software components installed on both servers.
• Identical SQL Server software versions on both servers.
• Identical global assemblies on both servers.
• Matching certificates and keys for authentication and encryption.

Replication:

• No differences in system collation settings.
• Identical local Windows groups and SQL Server login definitions on both servers.
• Same external software components installed on both servers.
• CLR assemblies deployed on the publisher must also be deployed on the subscriber.
• SQL Agent jobs and alerts required on the subscriber server.
• Matching certificates and keys for authentication and encryption.

Using with Other Features or Components

• Log Shipping: Can be used with Database Mirroring and Replication.
• Mirroring: Can be used with Log Shipping, Database Snapshots, and Replication.
• Replication: Can be used with Log Shipping and Database Mirroring.

DDL Operations

• Log Shipping: DDL changes are applied automatically.
• Mirroring: DDL changes are applied automatically.
• Replication: Only DML changes to published tables are replicated.

Database Limit

• Log Shipping: No limit.
• Mirroring: Generally recommended to have up to 10 databases per server.
• Replication: No limit.

Latency

• Log Shipping: Data transfer latency >1 minute.
• Mirroring: No data transfer latency.
• Replication: Latency can be as low as a few seconds.

Committed/Uncommitted Transactions

• Log Shipping: Both committed and uncommitted transactions are transferred to the secondary database.
• Mirroring: Only committed transactions are transferred to the mirror database.
• Replication: Only committed transactions are transferred to the subscriber database.

Primary Key

• Log Shipping: Not required.
• Mirroring: Not required.
• Replication: All replicated tables must have a primary key.

New Created Database & Stored Procedure

• Log Shipping: Monitoring and history information stored in msdb, accessed using log shipping stored procedures.
• Replication: Creates new stored procedures (three per table) and a distribution database. A rowguid column is created.

Individual Articles

• Log Shipping: No, the whole database must be selected.
• Mirroring: No, the whole database must be selected.
• Replication: Yes, including tables, views, stored procedures, and other objects. Filters can restrict columns and rows sent to subscribers.

FILESTREAM

• Log Shipping: Supports FILESTREAM.
• Mirroring: Does not support FILESTREAM.
• Replication: Supports FILESTREAM.

Database Name

• Log Shipping: Secondary database can have a different name from the primary database.
• Mirroring: Must have the same name.
• Replication: Must have the same name.

Database Availability

• Log Shipping: In standby mode: read-only database. In restoring mode with NORECOVERY: restoring state.
• Mirroring: In recovery state, no user operations allowed. Can take a snapshot.
• Replication: Snapshot replication (read-only), other types (databases are available).

Warm/Hot Standby Solution

• Log Shipping: Provides a warm standby solution with multiple copies of a database and requires manual failover.
• Mirroring: Provides a hot standby server with automatic failover when synchronized, otherwise a warm standby server with possible data loss.
• Replication: Provides a warm standby solution with multiple copies of a database and requires manual failover.

System Data Transferred

• Log Shipping: Mostly.
• Mirroring: Yes.
• Replication: No.

System Databases

• Mirroring: Cannot mirror Master, msdb, tempdb, or model databases.

Mode or Types

Log Shipping:

• Standby mode (read-only): disconnect users when restoring backups.
• No recovery mode (restoring state): user cannot access the secondary database.

Mirroring:

• High-safety mode: supports synchronous operation.
• High-performance mode: runs asynchronously.
• High-safety mode with automatic failover.

Replication:

• Snapshot replication.
• Transactional replication.
• Transactional publication with updatable subscriptions.
• Merge publication.
• Pull/push subscription.

How to Skip the Reboot While Installing SQL Server or Applying Patches Using PowerShell or Command Prompt

How to Skip the Reboot While Installing SQL Server or Applying Patches Using PowerShell or Command Prompt

When installing SQL Server or applying patches, sometimes a reboot is initiated automatically. To avoid this, you can use specific parameters or switches in PowerShell or Command Prompt. Here's how you can skip the reboot:

Using Command Prompt

To install SQL Server without rebooting, use the following command with the /SkipRules and /Action parameters:

setup.exe /SkipRules=RebootRequiredCheck /Action=Install

For applying patches without rebooting, use:

setup.exe /SkipRules=RebootRequiredCheck /Action=Patch

Using PowerShell

In PowerShell, you can achieve the same by invoking the setup executable with the necessary parameters:

Start-Process -FilePath "setup.exe" -ArgumentList "/SkipRules=RebootRequiredCheck /Action=Install"

For patching:

Start-Process -FilePath "setup.exe" -ArgumentList "/SkipRules=RebootRequiredCheck /Action=Patch"

Skip the Server reboot while applying patches or SQL server installation

By including the /SkipRules=RebootRequiredCheck parameter, you instruct the installer to bypass the reboot check, allowing the installation or patching process to complete without requiring a system restart.

Feel free to modify these commands to suit your specific SQL Server version and installation requirements.

SQL Server DBA Responsibilities

A Database Administrator (DBA) is responsible for designing, implementing, and maintaining the database system, as well as establishing policies and procedures for the management, security, maintenance, and use of the database management system. Below are the key responsibilities of a SQL Server DBA.

SQL Server DBA Responsibilities

1. Installing or Upgrading SQL Server
• DBAs are responsible for installing SQL Servers or upgrading to a newer version. This involves understanding the differences between various SQL Server editions and ensuring the correct edition is installed. Knowledge of the required licenses is also essential.
2. Patching SQL Servers
• After installation, DBAs must ensure that SQL Servers are properly patched with the latest Service Packs and cumulative updates to maintain security and performance.
3. Database Server Health Monitoring
• One of the primary responsibilities is to monitor the health of the database server, ensuring optimal utilization of resources such as the processor and memory.
4. Storage Availability
• DBAs need to ensure there is sufficient disk space for database growth. This involves regular monitoring and planning for future storage needs.
5. Performance Tuning & Optimization
• Regular performance tuning and optimization are necessary to maintain database efficiency and speed.
6. Securing the SQL Server Database
• Data security is crucial. DBAs must protect the database from unauthorized access by assigning appropriate permissions to users.
7. Backups and Restoration
• Regular backups are vital to prevent data loss. In the event of a database corruption or server failure, DBAs must restore the database with minimal data loss and downtime.
8. Data Transfer
• In heterogeneous environments, DBAs often need to import or export data to and from different formats (e.g., SQL Server to/from Oracle, Sybase, text files, CSV format).
9. Disaster Recovery
• DBAs must plan and implement strategies for disaster recovery to ensure business continuity in the event of a database or server failure.
10. Deployment of SQL Scripts
• DBAs are responsible for deploying SQL code and scripts to the production environment, ensuring they are tested and safe to execute.
11. Maximizing Uptime
• Ensuring that SQL Servers have minimal downtime is critical. DBAs must implement strategies to achieve high availability and minimal impact on business operations.
12. Documentation
• Proper documentation is necessary to keep track of configurations, procedures, and changes. This is helpful for new DBAs and during emergencies.
13. Communication
• Effective communication skills are essential, as DBAs interact with various teams to ensure smooth database operations.

Being a SQL Server DBA involves a wide range of responsibilities, each crucial to maintaining a healthy, secure, and efficient database environment. Proper management of these responsibilities ensures that the database systems support business needs effectively.