xSQL Software official blog moved

Our official blog is now at: http://blog.xsql.com

SQL Jobs on a Mirrored Environment

One of the little challenges a DBA has to deal with when setting up SQL Server mirroring is handling of SQL Server jobs. SQL Server mirroring works at the database level so SQL Server instance level objects are not mirrored. To achieve a transparent failover some of those instance level objects, like certain SQL Jobs related to your mirrored databases, need to be somehow "mirrored" as well. The objective is simple: any relevant jobs that were running on the principal, on failover should start running on the mirror. So we have to make sure the jobs are kept in sync and are activated at the right moment (you don't want those jobs to be running on the mirror until the failover happens).

One approach would be to have scheduled jobs that periodically syncs the SQL Jobs on the principal with those on the mirror and periodically checks the mirroring status to determine if the jobs on the mirror need to be activated.

Another simpler approach would be to use multi-server jobs. You can use a third server, like the witness for example, as the job "master" and do the following:

1. All the jobs that must be "mirrored" should be created on the job "master" server as multi-server jobs. 
2. The target for those jobs is set to be the principal 
3. Create a job on the job master server that checks the mirror status, let's say every minute
4. If a failover has happened and the mirror has become the principal then it changes the target server for those jobs.

Here is a simple stored procedure that checks the mirroring status and switches the target server for those jobs.

CREATE PROCEDURE [dbo].[usp_ChangeJobTarget]
  @DBName varchar(256) = 'MyMirroredDB',
  @Principal varchar(256) = 'MyPrincipal',
  @Mirror varchar(256) = 'MyMirror'
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @current_principal varchar(256)
  DECLARE @principalID int
  DECLARE @mirrorID int
  DECLARE @jobname  varchar(256)

   SELECT @current_principal = principal_server_name FROM sys.database_mirroring_witnesses WHERE database_name = @DBName
   SELECT @principalID = server_id from msdb.dbo.systargetservers WHERE server_name = @Principal
   SELECT @mirrorID = server_id from msdb.dbo.systargetservers WHERE server_name = @Mirror

 IF @current_principal like @Mirror
   BEGIN
    -- principal has changed so we need to change the target for all jobs
    -- first let's get the complete list of jobs that target the principal
   DECLARE @JobList TABLE
    (
      [JobName] [varchar](256) NOT NULL,
      [Changed] bit NOT NULL
    )
    INSERT INTO @JobList
      SELECT [name], 0
         FROM msdb.dbo.sysjobs as a INNER JOIN msdb.dbo.sysjobservers as b on a.job_id = b.job_id
         WHERE b.server_id = @principalID
    -- now for each job on the @JobList we need to change the target from principal to mirror
      SELECT TOP 1 @jobname = [JobName] FROM @JobList WHERE [Changed] = 0
         WHILE (@jobname IS NOT NULL)
           BEGIN
              exec msdb.dbo.sp_delete_jobserver @job_name = @jobname, @server_name = @Principal
              exec msdb.dbo.sp_add_jobserver @job_name = @jobname, @server_name = @Mirror
              UPDATE @JobList SET [Changed] = 1 WHERE [JobName] = @jobname
              SET @jobname = null
              SELECT TOP 1 @jobname = [JobName] FROM @JobList WHERE [Changed] = 0
           END
     END
  END
GO

A couple of notes to keep in mind:

1. There is a small delay on replicating a multi-server job. In other words, if you change a job or add a new job on the job master server it will take up to a minute for those changes to be reflected on the target server(s). 
2. The above stored procedure only goes in one direction – changes the target from principal to mirror in case of failover. You will need to adjust it to make it work both ways – one simple way to accomplish this is to simply compare the current principal with the target for those jobs and if they don’t match then change the target.
3. The above script is provided as is - use at your own risk.

Last but not least: check out our SQL Server comparison and synchronization tools, xSQL Object and xSQL Data Compare they will make your database deployment a breeze. Also, check out our Script Executor - no other tool in the market today comes close to it.

Kindle time at xSQL Software - get one

Don't "burn" your left over 2011 budget, put it to good use and get a great gift. Purchase a new, full xSQL Comparison Bundle license before Friday, December 16, 2011 and we will send you an awesome thank you gift, a Kindle. We will send the gift to whoever you direct us to send it to, anywhere in the United States and Canada. Here are the levels:

• a Kindle when you purchase a single machine / single user license
• a Kindle Touch 3G when you purchase a  five machine / five user license pack
• a Kindle Fire when you purchase a site license (unlimited activations within one physical site)

You must plug in the discount code KINDLE on the "discount" textbox on the shopping cart page otherwise no gift for you. Also, the purchase must be completed before December 16, 2011 - no exceptions.

Once you complete the order email us at sales@xsqlsoftware.com with instructions as to where you want your gift shipped to (a United States or Canada address only).

If you are not already, follow us on twitter and like us on facebook, we would appreciate it very much.

iPad and SQL Server

In the last few months we noticed a jump in the number of downloads for RSS Reporter and were not sure what to attribute it to. So we started probing the users who were downloading the product and found that the increased interest in our RSS Reporter for SQL Server was directly related to an ever increasing number of SQL Server Database Administrators carrying an iPad around.

What's so great about RSS Reporter? A couple of things:

• You don't need a client, just install the RSS Reporter on a machine with IIS that has access to the  SQL Server instances you are interested on and then access it from anywhere with an internet browser, iPad, iPhone, tablet – anything that can read a standard rss feed;
• Your executives need live data on their iPads, no problem, you add an ad-hoc query on RSS Reporter and simply send them the url;
• You need to see what happened with your SQL Server Jobs last night, no problem. You can get an aggregate report from all your SQL Servers right on your iPad, and you can drill down and see exactly what happened.

If you haven't tried RSS Reporter check it out – it is completely free for a single SQL Server Instance and very reasonably priced if you want to monitor multiple SQL Server instances in one place.

After you try make sure to send us your comments and suggestions as on how we might make RSS Reporter even more useful to you.

xSQL’s Oracle Data Compare vs. the others

When you do a search for data compare tools for Oracle a bunch of competing tools come up and at first look they all seem to be similar but once you start digging a bit deeper most of them get crossed off the list because they don’t amount to more than an amateurish effort. The very few that remain are hard to separate and you are inclined to go with the most known brand - after all, who has the time to thoroughly test all the available products!

We decided to take the time and do a more thorough review of a couple of those products that made the first cut and compared them to our Oracle Data Compare. Here is what we found:

1. Functionality: Oracle Data Compare supports Oracle Geometry, complex data types and unicode data; they DON’T
2. Performance: in the tests we did xSQL’s Oracle Data Compare completed the tasks  30% to 40% faster while keeping the memory consumption under 100 MB whereas the next best one was at 200 MB.
3. GUI: An argument can certainly be made in favor of simplicity but in the world of Oracle professionals having flexibility and control is important and xSQL’s Oracle Data Compare gives the user significantly more control and flexibility over the comparison and synchronization operation.

Download the free trial and see for yourself. If you wish to challenge our findings please do so – we will be delighted to hear from you!  Email us at the info address on xsql.com

Note: the introductory price of $174 expires on October 31, 2011. After that the price goes back to $349. To get the introductory price use promo code ORACLEPROMO on checkout.

Oracle Data Compare new build

Just released a new build of Oracle Data Compare - support for Oracle collections was added. Download your three week trial copy here...

xSQL Data Compare for SQL Server - new build available

We just published a new build of the xSQL Data Compare for SQL Server. Following is the list of enhancements and fixes included in this build:

1. Allows session naming by clicking on the name without launching it
2. Adds tooltip on session panel that shows the left/right databases, useful when the names of Sql Server/Database are too long and can't be fully seen in the UI
3. Fixes an issue with screen resizing
4. Fixes an issue with print wrapping for windows that support printing.
5. Adds a UI formatter for XML data types
6. Improves the process of comparing select tables

You can download the new build from http://www.xsql.com/download/sql_server_data_compare/