Fail Over techniques for Database Mirroring using SQL SERVER 2008, Manual

Fail-over techniques in database mirroring. There are different techniques for database mirroring.

1. Automatic fail over
2. Manual fail over
3. Forced fail over

Manual failover disconnects the clients from the database and reverses the roles of the partners. Only high-safety mode supports manual fail over.

http://msdn.microsoft.com/en-us/library/ms186348.aspx

During a manual failover, the principal and mirror server roles are swapped for the database on which the failover occurs. The mirror database becomes the principal database and the principal database becomes the mirror. For example, the following table shows the how a manual failover swaps the roles of two mirroring partners: System

The following figure illustrates an instance of using manual failover to maintain database availability while you upgrade a database server instance. When the upgrade is completed, an administrator may optionally fail over back to the original server instance. This is useful when the administrator wants to stop the mirroring session and use the mirror server elsewhere. In this way, a single server instance can be used repeatedly when updating a series of database server instances.

Manual failover initiates the following sequence of actions:

1.       The principal server disconnects clients from the principal database, sends the tail of the log to the mirror server, and, in preparation for switching to the mirror role, sets the mirroring state to SYNCHRONIZING.

2.       The mirror server records the log sequence number (LSN) of the last log record received from the principal as the failover LSN.

3.       If any log is waiting in the redo queue, the mirror server finishes rolling forward the mirror database. The amount of time required depends on the speed of the system, the recent workload, and the amount of log in the redo queue. For a synchronous operating mode, the failover time can be regulated by limiting the size of the redo queue. However, this can cause the principal server to slow down to allow the mirror server to keep up.

4.       The mirror server becomes the new principal server, and the former principal server becomes the new mirror server.

5.       The new principal server rolls back any uncommitted transactions and brings its copy of the database online as the principal database.

6.       The former principal takes on the mirror role, and the former principal database becomes the mirror database. The new mirror server quickly resynchronizes the new mirror database with the new principal database.

Manual fail-Over through Query

http://msdn.microsoft.com/en-us/library/ms179481.aspx

When the mirrored database is synchronized, the database owner can initiate manual failover to the mirror server. Manual failover can be initiated only from the principal server.

1.       Connect to the principal server.

2.       Set the database context to the master database:

3.       Issue the following statement on the principal server:

This initiates an immediate transition of the mirror server to the principal role.

Failover techniques for Database Mirroring using SQL SERVER 2008, Automatic

Fail-over techniques in database mirroring.

There are different techniques for database mirroring.

1. Automatic fail over
2. Manual fail over
3. Forced fail over

Automatic Fail-Over:

To test database mirroring auto fail over solution you need to restart database engine and your Principal and Mirror servers will be reversed but you need to set your mirroring solution as (Principal – Witness - Mirror). You can view how we can test auto fail over solution look at this link.

http://shamas-saeed.blogspot.com/2012/04/verify-database-mirroring-solution-for.html

The following illustration shows a single instance of automatic failover copy from msdn help.

Initially, all three servers are connected (the session has full quorum). Partner_A is the principal server and Partner_B is the mirror server. Partner_A (or the principal database on Partner_A) becomes unavailable. The witness and Partner_B both recognize that the principal is no longer available the session retains quorum. Partner_B becomes the principal server and makes its copy of the database available as the new principal database. Eventually, Partner_A reconnects to the session and discovers that Partner_B now owns the principal role. Partner_A then takes on the mirror role.

Let see changes in SSMS

Server	Before failover	After failover
MSSQLSERVER	PRINCIPAL	MIRROR
MIRROR	MIRROR	PRINCIPAL

Look at this screen shot. Principal and Mirror are fully synchronized.

Look at this screen shot. We are going to restart database engine to test our fail over solution.

After successful restart Principal and Mirror has been changed.

Automatic fail-Over for one database:

If you want to test over fail over for any single database then you need to go to database mirror properties and press fail-over option to set one database as auto fail-over.

Future Work:

• Manual fail-over with out Witness
• Forced fail-over

Verify database mirroring solution for auto fail-over using SQL SERVER 2008

Previous Post:

   In my previous post we have learned how to setup database mirroring "Step by Step configuration of Database mirroring using SQL Server 2008". 

After successful configuration of data mirroring we have looked into current running states of Principal and Mirror server. We have configured our database mirroring setup using (Principal, Mirror and Witness) database instances. To verify that how mirroring works lets start with some database DML operation on principal server database.

Step 1: I have a table TeaserProspect on my System database in Principal.I am going to drop it.

Step 2: As per mirroring solution this change must reflect on mirror server automatically. Now the question is how can we verify that these changes successfully synchronized to mirror server. To verify that lets stop Principal server database engine to fail-over principal database. There are other way around to test fail-over and we will discuss those in later blogs.

Step 3: Now drill down Mirror server and look at the status on System database now. Previously it  shows "mirror, synchronized / restoring" but now it has been changed to " Principal, Disconnected." Remember principal server has been stopped. 

It means our Mirror server successfully changed to Principal server on fail-over without any downtime. Now lets figure out that our changes has also been transferred.

Step 4: Try to find "teaserProspect" table in system database. I have tried to find out using information schema DMV and nothing returns. Lets perform these steps again to complete Principal-Mirror-Principal cycle. 

To do so I have created "teaserProspect" table again but this time on newly active System database on Mirror Server.

Step 5: lets start our Principal server again and you will see it will show you as active mirror server. It means our Mirror server is Principal now and Principal server acting as a mirror.

Step 6: To verify our data changes i am going to restart our mirror server again.

Step 7: Principal and Mirror servers will reverse again.

Step 8: Lets check "teaserProspect" table in system database on Principal that we have created on Mirror server when it was Active.

:) Our table is there. It means we are able to get high available database mirroring solution on our servers. 

Hope you guys will get help to configure database mirroring on your servers. Please post your comments and queries if there are any.

Previous Steps:

1. High availability solution
2. Getting Started with database mirroring
3. Step by Step Configuration of Database mirroring using SQL Server 2008, R2

Future Works:

In our future posts we will discuss some common error and problems I have faced during Administration mirroring solution. These are related to some common errors, user rights, database engine user and manual failover etc.

Step by step configuration of Database Mirroring in SQL Server 2008, R2

Previous Post:

In previous post I have define some ground work settings before starting database mirroring. 

We can implement database mirroring technique in different ways
1- Same machine or server with 3 different database instances install on it.
2- Different machines or servers for Principal, Witness and mirror.
3- Use same or different machines with Principal and Mirror there is no witness required.


I am going to explain database mirroring using 1st point. In this point i have installed three different instances of sql server 2008 on same machine. look at the picture below

From the picture above we can see following instances installed on my setting

1. na-sash\MSSQLSERVERR2:   We will use this instance as a Principal Server.
2. na-sash\mirror:   We will use this instance as a Mirror Server.
3. na-sash\witness:   We will use this instance as a Witness Server.

To start database mirroring i am using a database "System" running on Principal server. 

Step 1: Take a full backup of system database.

Step 2: Open the mirror server and restore that database in Recovery mode as seen in following diagrams.

 

Remember to change RESTORE WITH RECOVERY to RESTORE WITH NO RECOVERY MODE

Pic Step-2


Step 3: Open the Principal server and get properties of System database. If you look at the System database that you have restored recently on mirror server. It will show you in restoring... mode.




Step 4: On properties go to the Mirror tab.

Pic step - 4




Step 5: Click on Configure security.

Pic step - 5


Step 6: SQL server will ask for Witness configuration. As we have already defined if we need to create auto fail over we will include witness server as well.

Pic step - 6


A conformation window will explain about 3 servers and if you want to add Witness server later you can unchecked witness server here

Pic step - 6 -1 

Step 7: 

First tab will configure your principal server settings. If you are using same server with different instances as I am using in my example then you need to change port number for each configuration step. look at the listener port and End Point Name. 

Pic step 7-1

Step 7-2: 

Second tab will configure your Mirror server settings. If you are using same server with different instances as I am using in my example then you need to change port number for each configuration step. look at the listener port and End Point Name. 

Pic Step 7-2 

Step 7-3: 

Second tab will configure your Witness server settings. If you are using same server with different instances as I am using in my example then you need to change port number for each configuration step. If you did not ticked Witness server check box in Step 6 then this window will not appear. look at the listener port and End Point Name.

Pic Step - 7-3

Step 8: 

 If you are using Domain account for all three servers and account has rights in all three servers then you will specify that account in blank text boxes. If you are using SQL Server account then leave it blank and it will users that you specify during step 7 connectivity.

Pic Step - 8

Step 9: 

 Final summery before setup will confirm all previous settings.

Pic Step - 9

Step 10: 

After you press finish you will step back to Mirror window but this time all three text boxes (Principal, Witness and Mirror) will have access path defined for mirroring. A conformation window will appear that will ask to Start Mirroring in High Safe Mode. Press start mirroring and on pressing refresh button at bottom you will see synchronized message as shown in pic below.

Congrats you have successfully configured database mirroring setup on you setup. To confirm that database mirroring drill down to you system database at Mirror server and you will see following status

 DB Name: (Mirror, Synchronized / restoring...)

and on the Principal server you will see following status

 DB Name: (Principal, Synchronized)

Previous Steps:

1. High availability solution
2. Getting Started with database mirroring

Next Steps:

1. Performing Auto Fail-over using Auto fail-over configuration.

Getting started with Database Mirroring using SQL SERVER Management Studio

SQL SERVER provides a number of fail over techniques over the time. Database mirroring is one of the popular solution from all others (Log shipping, snapshot, replication). I am going to teach you simple steps to test and verify database mirroring through step by step screen shot technique. Major problem for most of DBA's is availability of proper hardware to install any technique prior to implement in production environment.


Database Mirroring

Database mirroring works with all the standard hardware that supports SQL Server 2008 and it ensures no data loss in the event of a database failure. The mirror database will always be updated with the current transaction that's being processed on the primary database server. figure below illustrates the flow of data.

If your principal server does go down, you can rest assured that the mirror server has an exact point-in-time copy of the principal database as of the last committed transaction. Thus, the mirror is always ready to take over the principal server role.

Operating Modes

The database mirroring topology you'll use will depend on the transaction safety and operating modes you've chosen. The operating modes that are supported by database mirroring include high-safety (with or without automatic failover) and high-performance.

High-Safety (with automatic failover) This mode supports maximum database availability with synchronous data transfer and automatic failover to the mirror database. This operating mode is best used when you have fast and very reliable communication between the principal and the mirror servers and you require automatic failover for a single database. In this scheme, the principal database waits to commit a transaction until it receives a message from the mirror server that the mirror server has hardened the transaction's log to the disk.

High-Safety (without automatic failover) This mode supports maximum database availability with synchronous data transfer but without automatic failover to the mirror database. In this mode, if the mirror server instance becomes unavailable, the principal server instance continues to function but will not be able to mirror the data. If the principal server goes down, database mirroring is suspended but you can manually force the service to fail over.

High-Performance In this operating mode, the transfer of data is asynchronous. The principal server does not wait for an acknowledgment from the mirror as it does in the above two modes. The mirror server does its best to keep up with the principal, but it is not guaranteed at any point that all the most recent transactions from the principal will be hardened in the mirror server's transaction log. If the principal server goes down, database mirroring is suspended but you can manually force the service to fail over.

Prerequisites

You need to remember some basic settings before setup this technique.

Setting up database mirroring is a simple process if you establish a strong foundation using best practices:

• Principal and mirror servers are running on the same edition of SQL Server, You can use either the Standard or Enterprise edition.
• If you are planning to use high-safety with automatic failover, then make sure that the witness server is available 
• Make sure that the mirror server instance has the identical jobs, logins, SQL Server Integration Service (SSIS) packages and Link servers.
• If you are planning to configure database mirroring on same server then you need different endpoints and ports that must open through network and system security.
• If you are planning to configure database mirroring on different server then you can set same port but again it must enable on all servers.
• You can configure database mirror in automatic fail over (with Witness) or Manual fail over (without witness). for Auto fail over witness server needs SQL server installed on Witness machine but there is no restriction of Express, Standard or Enterprise edition.
• You database Engine must be running with Domain Admin account on Network, otherwise you need to set it with your local account that has admin rights.
• 
  

Previous Link

1. Failover techniques in SQL Server

Next Steps

1. Step by Step configuration of Database mirroring using SQL Server 2008, R2 
2. Performing Auto Fail-over using Auto fail-over configuration.

Returning Valid date values from nVarchar column without conversion error

I come across a problem that is common in our daily usage but a little tricky. We have a table and a column with thousands of records and there is a column nVarchar that stores date values in a specific format. But when i applied convert or cast statement to convert these values into date format it generate error. It means there are invalid values in this column. Now i need my result set with out these invalid values and i don't have enough time to find out error values and remove or replace them.

Following is the query i have used to find out valid values and store it in a temp table then query my table to get valid values and use it as comparison.

Declare @DailyReport table(ID int, dtm datetime,DateOfContact varchar(50))

Insert into @DailyReport values (1,getdate(),'23-04-2012')
Insert into @DailyReport values (2,getdate(),'24-04-2012')
Insert into @DailyReport values (3,getdate(),'21-04-2012')
Insert into @DailyReport values (4,getdate(),'test')
Insert into @DailyReport values (5,getdate(),'26-04-2012')

;With DOC
as
(
 Select id,
convert(datetime,dateofcontact,103) as DOC
 From @DailyReport
 where
 (isnumeric(substring(dateofcontact,0,2)) = 1
 and
  isnumeric(substring(dateofcontact,4,2)) = 1
 and
  isnumeric(substring(dateofcontact,7,4)) = 1
  )
 )
select * from doc

here is the code picture and result. i have used pre-conversion check to validate date and omit all invalid rows before conversion.

Date time conversion

Return data set using (Commutative law of addition)

I have came through a very nice example today and want to share with you all. I know how to solve the problem but happy to know it is commutative law of addition and query to get that.

a+b = b+a

Here is a table tbl has four rows of data.

a      b
524, 307
594, 307
307, 524
307, 594

for each relationship in which a likes b and b likes a but the vice versa row should be rejected

result of the query should be

a  b

524,307

594,307

I have solved this using following query and result set.

but i am not satisfied with my query as i know there must be some good query to get this result. i have received many results but query that satisfy me is as below

I have found a problem in this query when we have same a+b with different values this query omit that. like 400+431 = 831 and 524+307 = 831. Now both above mentioned queries does not show one row even both have different a and b values.

There is another solution which looks like it returns correct data but there is a condition that single row data will not return there must be pair available. look at the attached screen below mention by JOHNQ