Database Mirroring on SQL Server 2008

I know it has been a very long time since I posted, and for that I am sorry. Sometimes work and personal life conspire to evaporate any free time you might have. But here I am with a new and hopefully useful bit of hard won experience to share with you.

I was recently engaged at a client to assist them with implementing database mirroring on SQL Server 2008. Everything seemed straight forward enough, they were conducting a proof of concept (POC) around using SQL Server to host their new ERP system and needed to prove the high availability features of automatic failover. No problem so far. They had a primary and secondary server and a virtual server to act as the witness already in place.

 

The wrench got thrown in at our kick-off meeting when the client informed me that since they are already implementing SharePoint 2007, they want the POC to use SharePoint as the application that will utilize the mirrored database set up. I’m not sure how many of you know this, but in order for an application to be natively ready to utilize a SQL Server mirrored environment and facilitate automatic fail-over, it must be capable of implementing the FAILOVER PARTNER attribute of a mirroring aware data source. SharePoint does not. There are many posts around the net on DB Mirroring, and SharePoint on mirrors, but I failed to find a comprehensive source that provided all the information I needed. Hence, my contribution to the community via this post. Actually this and the next post. I plan to break it up into a pair of posts, one that deals with the setting up of the mirroring, and one that deals specifically with the steps necessary to implement SharePoint on the mirroring session and provide automatic fail-over.

So in this post we will go through the process of creating a mirroring session and implementing mirroring for a database. We’ll use the AdventureWorks2008 sample database that Microsoft was so kind to create for us.

Any time you embark on configuring something in SQL Server, it is a good idea to be sure all the prerequisites are met ahead of time. There is a good source for prerequisites on SQL Server database mirroring at Microsoft TechNet. Here is the link Prerequisites, Restrictions, and Recommendations for Database Mirroring

Some things to note: Make sure both partners are using the same version and EDITION of SQL Server 2008. If you are running Enterprise on the primary, you should run Enterprise on the secondary. Both servers should also utilize the same default collation. You can not implement mirroring on database servers that have implemented the FILESTREAM option. Use of a third server, which can be a different Edition of SQL Server 2008, even EXPRESS, is required to fill the role of witness if you are implementing high availability mode (high safety with automatic fail-over).

So let’s begin. The first step is to ensure we have 3 server instances available to us. In a production application, these will be 3 separate servers, even if any or all are virtual. If you are doing this on your own, you can install a named instance of SQL Server along with your default instance and install SQL Server Express to act as the witness.

 

Once you have established your servers, you will need to ensure you have met all the requirements and pre-requirements as laid out in the above link. When you are ready to begin, follow the steps below.

Step 1:
Set Recovery Model to FULL.

 

Step 2:
Backup the database.

Step 3:
Copy the backup to the designated mirror instance.
Step 4:
Restore the backup on the designated mirror instance with the options selected as below.

 

You must choose the “Leave the database non-operational, and do not roll back uncommitted transactions. Additional transaction logs can be restored (RESTORE WITH NORECOVERY)” option for database mirroring.

Step 5:
On the designated principal instance, right click the database and select “Tasks” and then “Mirror”. This will launch the Database Mirroring Wizard.

 

Click “Next” and move through the wizard, selecting the Principal or primary server, the secondary or Mirror server, and the Witness server. In order to achieve automatic fail over a witness server is required.

There will be an identical screen on which to select the Witness server. The wizard creates and configures the endpoints for you.

These accounts are used for communication between the three servers.

 

Final check before finishing.

 

This screen shows the configuration progress for each server. Any warnings or errors will be caught and displayed here. When this screen finishes, you will be prompted to start mirroring.

 

Once the process concludes and the databases have synchronized, the mirroring session is started and all transactions made at the principal will be duplicated on the mirror. In the event of a failure of the primary/principal the Witness and the Mirror form a quorum and determine to switch control to the mirror, which exits the restoring state and becomes a disconnected principal. When the mirroring session is started the Mirror page of the database properties is as shown below.

 

You will see in the database object tree view in SQL Server Management Studio, that one database shows Principal, synchronized, and one shows Mirror, synchronized.

That’s it. You have set up mirroring on your database. Now when a problem occurs with the principal, the mirror will take over operation as the principal database. Any mirroring aware applications can implement the FAILOVER PARTNER attribute of the connection string to ensure continuous operation through a failed database server.

You can manual fail over the database from primary server to the secondary server in two ways.

One: Open the properties for the database and select the Mirror page. There is a button “Failover” that if you press, will manually fail over the database from the primary to the secondary.
Two: You can issue a T-SQL Query to fail over the database. On the primary server with the principal database execute the following code;
ALTER DATABASE AdventureWorksDW SET PARTNER FAILOVER

That’s all there is too setting it up. Of course there is a ton more information on Microsoft MSDN and TechNet as well as in BOL. Look it up, there’s much, much more to learn about database mirroring.

In my next post we will continue from here and implement automatic failover for a SharePoint installation. Until thin, keep slinging the SQL.

Productivity enhancements for SSMS

SQL Server 2008 includes several enhancements geared toward improving productivity and customer satisfaction. Some of the enhancements have been long sought after by the SQL Server database developer and administrator.

IntelliSense!
Yes we have long awaited the ability to write T-SQL code with IntelliSense helping code faster. IntelliSense with SQL 08 provides word completion and displays parameter information for functions and stored procs. In the XML editor, IntelliSense can completely show an element declaration. It also indicates errors with red squiggly lines and references them in the Error List window, so you can quickly navigate to the error line by double-clicking the entry. IntelliSense is provided in the Query editor as well as the XML editor, and supports most, but not all, T-SQL syntax. Below are some screen-shots of IntelliSense at work.

Collapsible Regions 
Similar functionality is now provided in the SMS08 Query Editor to regions delimited by BEGIN...END blocks and multi-line statements, such as SELECT statements that span two or more lines. This is similar to what Visual Studio has offered for a few releases now. Here's an example (note the '-' sign on the left, click to collapse text):

Delimiter Matching
Also similar to Visual Studio, SQL Server 2008 Management Studio now offers delimiter matching and highlighting. When you finish typing the second delimiter in a pair, the editor will highlight both the delimiters, or you can press CTRL + ] to jump to the matching delimiter when you are on one of them. This will help you keep track of parenthesis' and nested blocks. Automatic delimiting will recognize these delimiters;
(...), BEGIN...END, BEGIN...END TRY, BEGIN...END CATCH. Brackets and quotes are not recognized for delimiter highlighting.

SMS Object Explorer
More context menu choices have been provided for your right-click menu in Object Explorer. These choice include options for changing table design, to opening the table with a certain number of returning records, to getting some of the new reports available. Options for partitioning (Storage), Policies, and Indexing are provided as well.

You will notice a new "Start PowerShell" option. Look for a future post to cover the new Windows PowerShell integration.

Integrated T-SQL Debugging
Debugging has now been integrated into the Query Editor! You can set breakpoints, step through code, step into code a particular location, and even set watches up to monitor variable values, locals and the call stack. Woohoo!!!!

Other improvements
Multi-Server queries, allow you to run a query across numerous servers and return the results with the server name prepended.
Launch SQL Profiler directly from Management Studio.
Customizable tabs, allow you to modify the information shown and the layout from the tools\options dialog.
Object Explorer Detail Pane has been improved for better functionality and productivity with navigational improvements, detailed object information pane at the bottom, and integrated object search.
Object Search, allow you to search within a database on a full or partial string match and return objects to the Object Explorer Details pane.
Activity Monitor, you have to see it to appreciate it. Built brand new from scratch, it is based on the Windows Resource Monitor and allows you to see graphs of processor wait time, waiting processes, database I/O and batch requests. Detail grids are provided for Processes, Resource Waits, Data File I/O, and Recent Expensive Queries. It's a vast improvement for the DBA.
Performance Studio, new performance tuning tool that tracks historical performance metrics and stores them using drill-through reports.
Partitioning Setup GUI, at last a way to create and manage table partitions graphically. This is accessed from the context menu for a table under Storage. A wizard will launch allowing you to create or manage partitions.
Service Broker Hooks, new context menu items centralize access to the Service Broker T-SQL templates for messages, contracts, queues, etc. Read only property pages are provided for each of these objects as well.

The improvements in the toolset provided with SQL Server 2008 are vast and far sweeping. There are more for you to discover and the best way is to get your hands on it. You can download SQL Server Express 2008 from Microsoft if you don't have a developers edition license.

Please, as always, post your comments and questions. Until next time, happy slinging.

Technorati Tags: SQL Server 2008,SQL Server,SSMS,SMS,SQL Server Management Studio,T-SQL,Query Editor,Enhancments,Object Explorer,Object Explorer Detail view,Results Grid,Activity Monitor,SQL Tool Enhancemenets.