Search This Blog & Web

Monday, April 30, 2012

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.

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
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.

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
Before failover
After failover

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

Friday, April 27, 2012

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.

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.



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 configurationAs 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:

Next Steps:

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.

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
Next Steps

Monday, April 23, 2012

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
 Select id,
convert(datetime,dateofcontact,103) as DOC
 From @DailyReport
 (isnumeric(substring(dateofcontact,0,2)) = 1
  isnumeric(substring(dateofcontact,4,2)) = 1
  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

Thursday, April 19, 2012

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

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

Tuesday, April 17, 2012

SQL Agent Job Detail report (Information, Latest history)

Problem: I have assigned a task to generate a list of jobs for specific databases along with their detail like description, steps, schedules and latest history.

Solution: Simple solution for me is to create an excel sheet use a simple query to return jobs name and copy paste their detail one by one but I have to do this task daily or on demand. Now it is bit different I have searched for its solution and find some good articles most effective of all articles is
when I ran its query it shows jobs current running status with some useful information and this query is very good in some conditions but I have to do something more to fulfill my requirements.

Now I have decided to write query that run every useful information about job, its detail and history etc. to do this I have started writing my query using following steps.

I have generated a Create script for an existing job and note which steps SQL Server perform to generate a job.  I have found following procedures used from msdb database
There are more procedures but these 4 are of my concerns.

Now I have used sp_helptext procedure to return structure of every procedure and note tables when all procedure inserts its data. I have drilled down following tables from all above procedures

After that I have my required table and their columns. I have write my required query but now I need to understand columns data for many columns like job status and time interval based columns. For that I have gathered all required information from online help and apply case statements to generate all required information.

Once I have completed my initial work and execute query I got all required information but missed one important info and that is latest job history. For that my friend’s blog I have mentioned above helped me and I got another table  


Finally my query returns all my requried information about all jobs for specific databases along with their detail like description, steps, schedules and latest history.

Here is the query that I use. You can use where clause to limit your databases.

use msdb

; with jobHistory
SELECT row_number() over(partition by job_id order by instance_id desc) as JobExecCount,
      job_id,step_name,[message],run_date,run_time,run_duration,run_status FROM msdb.dbo.sysjobhistory
            CASE c.category_class
         WHEN 1 THEN 'JOB'
         WHEN 2 THEN 'ALERT'
         ELSE 'NONE' 
       END  ,
       CASE c.category_type 
            WHEN 1 THEN 'LOCAL'       
            WHEN 2 THEN 'MULTI-SERVER' 
            ELSE 'NONE'
            CASE flags
                        WHEN 0 THEN 'Normal' 
                        WHEN 1 THEN 'Encrypted command (read only)'
                        WHEN 2 THEN 'Append output files (if any)'  
                        WHEN 4 THEN 'Write TSQL step output to step history' 
                        WHEN 8 THEN 'Write log to table (overwrite existing history)' 
                        WHEN 16 THEN 'Write log to table (append to existing history)' 
                        WHEN 32 THEN 'Write all output to job history' 
                        WHEN 64 THEN 'Create a Windows event to use as a signal for the Cmd jobstep to abort'
                        ELSE 'NONE'
            END AS flags, 
            CASE on_success_action
                        WHEN 1 THEN 'Quit With Success'
                        WHEN 2 THEN 'Quit With Failure'
                        WHEN 3 THEN 'Goto Next Step'
                        WHEN 4 THEN 'Goto Step'
                        ELSE ''
                  END AS on_success_action,
                        CASE on_fail_action
                        WHEN 1 THEN 'Quit With Success'
                        WHEN 2 THEN 'Quit With Failure'
                        WHEN 3 THEN 'Goto Next Step'
                        WHEN 4 THEN 'Goto Step'
                        ELSE ''
                  END  AS on_fail_action,             as ScheduleName, 
         ch.enabled as ScheduleEnabe, 
             CASE freq_type                      
                              WHEN 1 THEN  'One time only'                   
                              WHEN 4 THEN  'Daily' 
                              WHEN 8 THEN  'Weekly' 
                              WHEN 16 THEN 'Monthly' 
                              WHEN 32 THEN 'Monthly, relative to freq_interval' 
                              WHEN 64 THEN 'Runs when the SQL Server Agent service starts'
                              WHEN 128 THEN 'Runs when the computer is idle'
                              ELSE 'NONE' END as freq_type, 
          CASE WHEN freq_type = 8 THEN
                  CASE freq_interval 
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 4 THEN 'Tuesday'
                        WHEN 8 THEN 'Wednesday'
                        WHEN 16 THEN 'Thursday'
                        WHEN 32 THEN 'Friday'
                        WHEN 64 THEN 'Saturday'
                  ELSE 'NONE' END
                  WHEN freq_type = 8 THEN
                  CASE freq_interval
                        WHEN 1 THEN 'Sunday'
                        WHEN 2 THEN 'Monday'
                        WHEN 3 THEN 'Tuesday'
                        WHEN 4 THEN 'Wednesday'
                        WHEN 5 THEN 'Thursday'
                        WHEN 6 THEN 'Friday'
                        WHEN 7 THEN 'Saturday'
                        WHEN 8 THEN 'Day'
                        WHEN 9 THEN 'Weekday'
                        WHEN 10 THEN 'Weekend day'
                  ELSE 'NONE' END
                  ELSE '' END AS freq_interval, 
          CASE freq_subday_type
                  WHEN 1      THEN 'At the specified time'
                  WHEN 2      THEN 'Seconds'
                  WHEN 4      THEN 'Minutes'
                  WHEN 8      THEN 'Hours'
              END AS freq_subday_type, 
          freq_subday_interval, -- Number of freq_subday_type periods to occur between each execution of the job.
          CASE freq_relative_interval
                  WHEN 1 THEN 'First'
                  WHEN 2 THEN 'Second'
                  WHEN 4 THEN 'Third'
                  WHEN 8 THEN 'Fourth'
                  WHEN 16 THEN 'Last'
                  END AS freq_relative_interval, 
               freq_recurrence_factor,  -- Number of weeks or months between the scheduled execution of a job
          active_start_date as ActiveAt,  -- Date on which execution of a job can begin. The date is formatted as YYYYMMDD. NULL indicates today's date.
          active_end_date,  -- Date on which execution of a job can stop. The date is formatted YYYYMMDD.
          active_start_time,  -- Time on any day between active_start_date and active_end_date that job begins executing. Time is formatted HHMMSS, using a 24-hour clock.
          active_end_time,  -- Time on any day between active_start_date and active_end_date that job stops executing. Time is formatted HHMMSS, using a 24-hour clock.   
              run_date, -- Date the job or step started execution. For an In Progress history, this is the date/time the history was written.
              run_time, -- Time the job or step started.
              run_duration, -- Elapsed time in the execution of the job or step in HHMMSS format.
              CASE run_status
                  WHEN 0 THEN 'Failed'
                  WHEN 1 THEN 'Succeeded'
                  WHEN 2 THEN 'Retry'
                  WHEN 3 THEN 'Canceled'
                  END AS run_status
      msdb.dbo.sysjobs j LEFT OUTER JOIN
msdb.dbo.syscategories c ON j.category_id = c.category_id left outer join
msdb.dbo.sysjobsteps js ON js.job_id = j.Job_ID
left outer join msdb.dbo.sysjobschedules jch ON j.Job_ID= jch.Job_ID
inner join  msdb.dbo.sysschedules ch  on ch.schedule_id= jch.schedule_id
left outer join (select * from jobHistory where JobExecCount = 1 ) jH on jh.Job_ID = j.Job_ID