Database Mirroring

In one of my previous posts I talked about Transaction Log Shipping and how it can be used to create a copy of the database on a secondary server and periodically apply the transaction logs. Mirroring is a similar process except a few differences.

Whereas in Log Shipping, transaction logs are periodically backed up and physically copied to the secondary server and applied to the database in recovery mode. In mirroring, SQL Server directly reads the data from the transaction logs and copies them from the principal server to the mirrored server.

 Mirroring can be configured to operate synchronously or asynchronously. In synchronous configuration, primary or principal server sends the data to the mirrored server and waits until the mirrored server saves the data on the hard-drive before committing the transaction. In this scenario, basically buffered data is sent to the mirrored server and principal waits for a signal from the mirrored instance. Once the signal is received from the mirror, only then the transaction is committed on the principal. As you can imagine the performance of your database depends on several factors such as network bandwidth, disk write speed etc.

 In asynchronous mode, the principal server doesn't wait for the mirrored server before committing the transaction. It sends the buffered data to the mirrored instance, while simultaneously committing the transaction.

Mirroring supports only one mirrored instance. This is yet another difference between Mirroring and Transaction Log Shipping, which supports multiple instances of the secondary server.

Mirroring supports automatic failover. In case of a failure on primary server, SQL Server automatically brings the mirrored instance online, usually within a few seconds.

While Log Shipping has been available since SQL 2000, mirroring was introduced only in SQL Server 2005.

Another key advantage of mirroring is built-in support in .NET Framework which doesn't require special routing or switching code to handle the fail-over switch from one server to another. It requires ADO.NET 2.0 and higher.

SQL Server 2008 introduced another key feature in mirroring, i.e. ability to automatically repair corrupt pages. For example, if a page is corrupted on the principal server, SQL Server will replace it with a page from the mirror and vice versa.

We can go in more technical details in further posts if needed, but in a nutshell this is what mirroring is all about.

As always, your comments are welcome!

Transaction Log Shipping

Recently I had to setup log shipping for our production environment. Although, this wasn’t entirely new for me, nonetheless this was a good refresher. I decided to go ahead and note the steps involved in configuring transaction log shipping and various available options.

Points to Ponder

Some of you may disagree but I feel these are important considerations, especially in your production environment where high availability is important.

In our environment, we have about 100 databases. There are two clustered environments and we host half databases on one cluster and the other half on the other cluster. Each cluster has 2 SQL Servers.

Our initial thought was to go ahead and setup log shipping in a way that the secondary server for the databases on one cluster will be the other cluster and vice-versa. It just didn’t feel right. Take for example, if your cluster 1 goes down then all load will end up on cluster 2 which may not be feasible or desirable.

So, we decided to setup a third environment just to act as a secondary cluster for both clusters. It may be overkill for some, but it certainly was a worthwhile investment for us.

What is Log Shipping?

Transaction log shipping is basically a way to automatically backup transaction logs, copy them to the secondary server and restore them in the database on the secondary server.

You can actually set it up yourself using SQL server agent and using the following steps…

         Create a SQL Agent Job to backup transaction logs for a specific database.

         As a second step in the same SQL Agent Job create an execute Xp_CmdShell to copy the transaction log files to the secondary server.

         Create a SQL Agent Job on the secondary server to restore the log files to a copy of the same database on the secondary server.  The database on the secondary server obviously has to be in “Restoring” mode.

But SQL Server takes care of all that for you. Using SQL Management Studio, you can simply walk through the steps to setup log shipping in just a few minutes.

Using SQL Management Studio to Setup Log Shipping

Below is a step by step process on how to setup log shipping for a specific database.

Note: Your database recovery model must be full or bulk logged in order to configure transaction log shipping.

         Right click on Database > Properties

         Select Transaction Log Shipping

o  Check enable this as a primary database in a log shipping configuration and then click on Backup Settings.

         Define the network path where you want to backup the transaction log. If the backup path is on the same server, also type the physical path, otherwise leave it blank. You can change settings such as delete files older than 72 hours and alert settings, but generally defaults are good. If you are short on space, you may want to lower the 72 hour threshold.

         You can change the schedule by clicking on the Schedule button. 15 minute default is generally good.

         Click OK and then from the main window click on “Add” button to configure secondary server. Click on connect to connect to the secondary server.

   The three tabs allow you to setup secondary database information and restore location. 
       There are three ways to setup a database on the secondary server.

•             Manually copy the primary database and restore on the secondary server. Remember to keep in Restoring mode.
•       Chose from one of two options on the first tab (see above) to restore the database to the secondary server.

  Second tab allows you to configure the restore location

       You can change the restore schedule by clicking on the Schedule button but default work just as well.

       On the last tab, you can set database recovery mode and alert settings.

      Click on OK and you have configured the transaction log shipping.

      You can optionally setup the Monitor Server, which allows you to monitor your transaction log jobs remotely. This is only useful when you have a separate monitor server, because history and status of the backup log operation is stored on the primary server and that of the restore operation is stored at the secondary server. Remote monitoring server allows the history and status of both copy and restore operations on the remote server. You can also configure alerts to fire if alert service fails.

      That’s all there is to it. One issue that I have seen is the permissions issue. Make sure the user under which SQL agent runs has the read/write permissions on both primary and secondary servers.

Creating Indexes using T-SQL

In my previous post, we discussed creating indexes using SQL Management Studio. You can achieve the same results using SQL Scripts.

Using T-SQL

1.  Create a Non-Clustered, Non-Unique Index

/****** Object:  Index [idx_Test]    Script Date: 02/05/2012 12:56:50 ******/
CREATE NONCLUSTERED INDEX [idx_Test] ON [dbo].[DTA_input]
(
[SessionID] ASC,
[GlobalSessionID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

2. Create a Unique Non-Clustered Index

/****** Object:  Index [idx_Test]    Script Date: 02/05/2012 12:59:41 ******/
CREATE UNIQUE NONCLUSTERED INDEX [idx_Test] ON [dbo].[DTA_input]
(
[SessionID] ASC,
[GlobalSessionID] ASC
)WITH (PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF,
ONLINE = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

HINTS / CLAUSE DEFINITIONS

PAD_INDEX
This option specifies whether you want to leave some space in each node (also referred to as page) for future inserts/updates. This is only useful when you specify a fill factor, because it uses the % specified in fill factor. Default is OFF.

STATISTICS_NORECOMPUTE
This flag determines whether index statistics are automatically recomputed. If you set it to ON, your SQL Query Optmizer may not be able to pick the optimal execution plan for any queries using this table.

SORT_IN_TEMPDB
Specifies that during index build/rebuild, intermediate sort results will be stored in tempDB. If your tempDB is on a different disk(s) than your production DB, it may reduce the time needed to create an index.

IGNORE_DUP_KEY
As I discussed in one of my previous post, when this option is ON and an attempt is made to insert a duplicate key, server issues a warning and ignores the duplicate row. If this option is OFF, server issues an error message and rolls back the entire INSERT. This clause can only be turned on if you have specified UNIQUE clause in your index.

DROP_EXISTING
This clause signals the SQL Server to drop and rebuild the pre-existing index with the same name. When you drop a clustered index, all non-clustered indexes must be rebuilt because they contain pointers to clustered index keys. This clause is extremely useful when dropping a clustered index on the table that also has non-clustered indexes. The non-clustered indexes are rebuilt only once and only if the keys are different.

ONLINE
This clause needs some explanation. When this clause is ON, it means database can be online i.e. being used for other processes while Index is being built or rebuilt. In other words, Index operations do not need exclusive lock. Default is OFF meaning indexing operation requires exclusive lock on the table. This was a nice enhancement in SQL 2005. Prior versions required exclusive lock for index operations. Some of columns such as VARCHAR(MAX) cannot be indexed while online.

ALLOW_ROW_LOCKS
This clause specifies whether data row is locked when performing operations on the indexed keys. When performing OLTP operations, it is a good practice to leave turn this ON.

ALLOW_PAGE_LOCKS
This clause determines whether the entire data page will be locked during index operations.

If both clauses are OFF, SQL Engine will not lock data page or data rows instead entire table will be locked during the operation.

Generally, it is a good idea to leave the defaults alone unless you have a very good reason to change them.

Thank you and as always, your comments are welcome!