Database Mirroring | SQL Freelancer Blog

Steps to Apply a Service Pack or Patch to Mirrored SQL Server Databases

By bradyupton in Database Mirroring, High Availability June 13, 2012 0 Comment

In this post, I am going to outline my environment and then walk through the process of patching mirrored servers.

My test environment consists of two SQL Server 2005 SP2 servers named SSQL1 (principal) and SSQL2 (mirror) that contain eleven mirrored databases. The database mirroring operating mode is set to asynchronous and I’m upgrading to SP4.

Here is a look at the two mirrored instances.

Step 1
Always backup all system and user databases before applying patches.

Step 2
Remote Desktop into the “Mirror” server (SSQL2 in our example) and download/copy the patch to the server.

Step 3
Stop all SQL Services on the “Mirror” server.

Step 4
Run the patch on the “Mirror” server.

Step 5
Once the patch is complete, reboot the “Mirror” server.

Step 6 (optional)
If your database mirroring is set to asynchronous (High Performance mode), we will need to synchronize the databases first. To do this we will need to issue the following statement for every database on the “Principal” server that is mirrored.

ALTER DATABASE databasename SET SAFETY FULL

Here are the commands for the 11 databases on my server.

Step 7
The databases might change to “synchronizing” while the transactions catch up. Once all of the databases show “synchronized”, as shown below, we can perform the manual failover.

We can perform the failover using the following statement on the principal server for each database:

ALTER DATABASE databasename SET PARTNER FAILOVER

Here are the commands for the 11 databases on my server.

We can now see that the servers have switched roles.

Click here to view the rest of this post.

SQL Server High Availability Options

By bradyupton in AlwaysON, Clustering, Database Mirroring, High Availability, Log Shipping, Replication October 24, 2011 0 Comment

Finding the right high availability option can be tricky. The decision really depends on these items:

Needs
Budget
Scope
SQL Server version
Level of automation
Team level support
etc.

At a high level, there are five main high availability options including a new feature set to be release with SQL Server 2012:

Replication
Mirroring
Log Shipping
Clustering
AlwaysON

SQL Server Replication Overview

At a high level, replication involves a publisher and subscriber, where the publisher is the primary server and the subscriber is the target server. Replication’s main purpose is to copy and distribute data from one database to another. There are four types of replication that we will outline:

Snapshot replication
Transactional replication
Merge replication
Peer to Peer replication

Snapshot: Snapshot replication occurs when a snapshot is taken of the entire database and that snapshot is copied over to the subscriber. This is best used for data that has minimal changes and is used as an initial data set in some circumstances to start subsequent replication processes.

Transactional: Transactional replication begins with a snapshot of the primary database that is applied to the subscriber. Once the snapshot is in place all transactions that occur on the publisher will be propagated to the subscriber. This option provides the lowest latency.

Merge: Merge replication begins with a snapshot of the primary database that is applied to the subscriber. Changes made at the publisher and subscriber are tracked while offline. Once the publisher and subscriber are back online simultaneously, the subscriber synchronizes with the publisher and vice versa. This option could be best for employees with laptops that leave the office and need to sync their data when they are back in the office.

Peer to Peer: Peer to Peer replication can help scale out an application. This is because as transactions occur they are executed on all of the nodes involved in replication in order to keep the data in sync in near real time.

Pros and Cons for SQL Server Replication
Pros	Cons
Can replicate to multiple servers	Manual failover
Can access all databases being replicated	Snapshot can be time consuming if you have a VLDB
Replication can occur in both directions	Data can get out of sync and will need to re-sync

SQL Server Database Mirroring Overview

Database Mirroring involves a principal server that includes the principal database and a mirror server that includes the mirrored database. The mirror database is restored from the principal with no recovery leaving the database inaccessible to the end users. Once mirroring is enabled, all new transactions from the principal will be copied to the mirror. The use of a witness server is also an option when using the high safety with automatic failover option. The witness server will enable the mirror server to act as a hot standby server. Failover with this option usually only takes seconds to complete. If the principal server was to go down the mirror server would automatically become the principal.

Click here to view the rest of this post.

Configure SQL Server Database Mirroring Using SSMS

By bradyupton in Database Mirroring, High Availability July 15, 2011 0 Comment

In this post I am going to outline my environment and then walk through the process of setting up Database Mirroring. This will include the configurations, backups, restores and verification process. Let’s jump in.

My test environment consists of two separate VM’s running VM Workstation with Windows 2008 R2 Datacenter Edition and SQL Server 2008 R2 Enterprise named appropriately Principal and Mirror. The SQL Server and SQL Server Agent Services accounts are running as domain users (DOMAIN\User). Windows Firewall is OFF for the sake of this example.

I created a database on the Principal SQL Server instance and named it TestMirror. The recovery model is set to FULL RECOVERY.

1st step: Issue a full backup of the database.

BACKUP DATABASE TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.bak';

2nd step: Issue a transaction log backup of the database.

BACKUP LOG TestMirror TO DISK = 'C:\Program Files\Microsoft SQL 
Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Backup.trn';

Below are the two files in the file system:

Click here to view the rest of this post.