In the news, there’s a new Service Pack in the works for SQL Server 2005. In this week’s feature, we’ll continue the introduction to SQL Server Features and talk about Database Mirroring. The web link shows you how to set up peer-to-peer replication in SQL Server 2005 and this week’s tip is on Full Text Indexes.
News this week:
Microsoft will create a third Service Pack for SQL Server 2005, slated for release sometime after SQL Server 2008 ships. You can read more about it at http://tinyurl.com/53s8pr.
In a previous Podcast I started an overview of the major features you can find in SQL Server 2005. I’ll cover each feature in more depth in future Podcasts, but we need a place to start the discussion for those features, so we’ll cover the basics first.
This week we’ll take a look at Database Mirroring, which is new for SQL Server 2005. In a previous Podcast I explained that SQL Server supports Microsoft Windows Clustering, which maintains multiple systems as a single name, providing a “safety net” in case one system goes offline due to hardware or software system failures. Database Mirroring has a similar function, but accomplishes it in a different way. In Clustering, two or more “nodes”, or systems, share a single name. When one node has an issue that takes it offline, the name is preserved for the users and applications. In database mirroring, one system can copy a database to another automatically – but both systems retain their original names.
The hardware requirements for Database Mirroring is far less strict than for Clustering. All you need is enough room on the receiving system to hold the database that is mirrored. Each database that you want to mirror needs to be in the Full recovery mode, since the transaction log is the mechanism used for the data transfer. That means that anything you send through the BULK LOGGED command won’t be mirrored, since they bypass the log.
The “master” system, the one that holds the database you want to copy, is called the Principal. The server that receives the data is called the Mirror. Another optional system can help monitor the process, and is called the Witness. I’ll explain more about that in a moment.
Database Mirroring uses three modes, called “Safety Levels”:
The first is called High Performance, and it simply transfers the data between the two systems. Should the Principal fail, then you have to take some manual steps to activate the database on the Mirror. At this level you don’t even have to be constantly connected, although you would lose any data during the times you weren’t connected in case of a failure.
The second level is called High Protection. You still have to recover the Mirror manually, but you have a constant connection so you don’t lose data.
The third level, the highest, is called “High Availability”. In this level the Mirror activates the database automatically, but you need that third system, the Witness, to make that happen.
To set up Mirroring, you take the following steps:
First, you stop all the activity on the Principal system and take a full backup of the database. Then you restore that database on the Mirror, and leave it in the WITH NO RECOVERY mode. That leaves it ready for more data inserts.
Next, you need to set up the Endpoints in the system. An Endpoint is a TCP/IP port that listens for certain database operations. You’ll do this on both the Principal and Mirror system. The process to do that is explained in Books Online, and I’ll give you a reference for all that at the end of this section.
Next, you’ll enable the database for mirroring. You can do that using Transact-SQL commands. In effect, you’re telling the system to transmit the data from one database to another across those Endpoints you just made.
Once the database is in a Mirroring state, there are various views and stored procedures you can use to monitor it.
Should the Principal fail, you’ll have an up-to-date copy of the database on the Mirror. You can then follow the steps to activate the Mirror database, point your applications to the new server, and you’re back in business.
One final note – if you’re using the latest programming languages from Microsoft, the application connections can be Mirroring-aware, meaning that they can automatically look to the new server for the data. The names of the servers don’t change, but the code can keep the users up and running once you activate the database.
With this overview complete, you’re ready to read the details in Books Online at http://tinyurl.com/67t4pp.
Replication is another feature that we’ll talk about on this podcast shortly. If you’re interested in learning more about using it not only to copy data but in a peer-to-peer fashion, TechNet Magazine has a great explanation – find it at http://tinyurl.com/4uhqb8.
Tip of the Week:
If you’re using Full-Text Indexing in your database, you can set up Full-text indexes on columns that contain formatted Microsoft Word documents and other “formatted binary” data. You can store these documents in a varbinary(max) or an image column. But there’s a catch – you cannot use the LIKE predicate to query formatted binary data. Find out more at http://tinyurl.com/6g8rmh.