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.

Microsoft Visual Studio Team Foundation Server 11 Beta Power Tools

Power Tools are a set of enhancements, tools, and command-line utilities that increase productivity of Team Foundation Server scenarios.

http://visualstudiogallery.msdn.microsoft.com/27832337-62ae-4b54-9b00-98bb4fb7041a

Learn Everything About SharePoint 2013!

Learn Everything About SharePoint 2013!
The SharePoint 2013 Beta series at USP Journal is a subscription series that aims to keep you updated on the latest news, updates, and information regarding the next version of Microsoft’s collaboration platform, currently called SharePoint 15

http://sharepoint2013beta.com/

What To Expect in SharePoint 15

What To Expect in SharePoint 15

A new version of the Microsoft collaboration and enterprise content management platform is in the works, and it will offer improved social networking, client and mobile UI enhancements, an app marketplace, and better cloud support. And that just scratches the surface.

http://redmondmag.com/articles/2012/04/01/whats-next-for-sharepoint.aspx

• By Jeffrey Schwartz
• 04/04/2012

Ascendum Unveils Latest Innovation: New SharePoint On Demand(TM) Changes the Rules by Taking Away the Hassles but Not the Power of Industry-Leading Platform

http://finance.yahoo.com/news/ascendum-unveils-latest-innovation-sharepoint-150000589.html

Health Care Templates fore Sharepoint 2010

Health Care Templates for SharePoint 2010 are available at http://www.techsolutions.net/SharePointSolutions.aspx

Be the Next Microsoft Employee Show

SQL SERVER Agent Job configuration and running history.

Problem: I came across a requirement  to generate a list of jobs for specific databases along with their configuration 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

http://sqlconcept.com/2011/06/25/how-to-query-currently-running-sql-server-agent-jobs/

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.

Step1:

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

sp_add_category

sp_add_job

sp_add_jobstep

sp_add_jobschedule

There are more procedures but these 4 are of my concerns.

Step2:

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

msdb.dbo.sysjobs

msdb.dbo.syscategories

msdb.dbo.sysjobsteps

msdb.dbo.sysjobschedules

msdb.dbo.sysschedules

Step3:

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 http://msdn.microsoft.com/en-us/library/ms174997.aspx online help and apply case statements to generate all required information.

Step4:

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  

msdb.dbo.sysjobhistory 

Conclusion:

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.

Query:

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

use msdb

go

; with jobHistory

as

(

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

)

SELECT j.name,j.enabled,description,

            CASE c.category_class

         WHEN 1 THEN 'JOB'

         WHEN 2 THEN 'ALERT'

         WHEN 3 THEN 'OPERATOR'

         ELSE 'NONE' 

       END  ,

       CASE c.category_type 

            WHEN 1 THEN 'LOCAL'       

            WHEN 2 THEN 'MULTI-SERVER' 

            ELSE 'NONE'

          END 

      , c.name

       ,step_id, 

            JS.step_name, 

            subsystem, 

            command, 

            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, 

            server, 

            database_name, 

            database_user_name, 

            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,            

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

           JS.step_name, 

           [message],

              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

FROM         

      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