database mirroring patch for activerecord sql server adapter

gistfile1.txt

From 6f174a93fdda24b0913f65734d0b36849910e3dc Mon Sep 17 00:00:00 2001
From: Igor Anic <[email protected]>
Date: Wed, 25 May 2011 12:39:39 +0200
Subject: [PATCH] initial commit of databse mirroring funcionality to the new repo

---
 .gitignore                                         |    3 +-
 MIRRORING_HOW_TO                                   |   48 +++++++
 Rakefile                                           |   14 ++-
 .../connection_adapters/sqlserver/mirroring.rb     |   85 +++++++++++++
 .../connection_adapters/sqlserver_adapter.rb       |    1 +
 test/cases/dblib_mirroring_test.rb                 |   19 +++
 test/cases/mirroring_test.rb                       |  134 ++++++++++++++++++++
 test/cases/odbc_mirroring_test.rb                  |   18 +++
 8 files changed, 320 insertions(+), 2 deletions(-)
 create mode 100644 MIRRORING_HOW_TO
 create mode 100644 lib/active_record/connection_adapters/sqlserver/mirroring.rb
 create mode 100644 test/cases/dblib_mirroring_test.rb
 create mode 100644 test/cases/mirroring_test.rb
 create mode 100644 test/cases/odbc_mirroring_test.rb

diff --git a/.gitignore b/.gitignore
index 2fac950..83e0852 100644
--- a/.gitignore
+++ b/.gitignore
@@ -7,4 +7,5 @@ doc/
 .bundle
 Gemfile.lock
 test/profile/output/*
-.rvmrc
\ No newline at end of file
+.rvmrc
+*.diff
diff --git a/MIRRORING_HOW_TO b/MIRRORING_HOW_TO
new file mode 100644
index 0000000..37ec866
--- /dev/null
+++ b/MIRRORING_HOW_TO
@@ -0,0 +1,48 @@
+This adapter has basic support for database mirroring. 
+In case of failed connection to primary server, adapter will try to connect to mirror.
+
+Configuration for dblib mode, add :dataserver_mirror key to your database.yml:
+  ...
+  :dataserver        => "primary_server",
+	:mirror
+    :dataserver      => "mirror_sql_server",
+  ...
+
+Configuration for dblib mode, add :dsn_mirror key to your database.yml:
+  ...
+  :dsn        => "dsn_for_primary_server",
+	:mirror
+    :dsn      => "dsn_for_mirror_sql_server",
+  ...
+
+
+To run tests:
+
+1. Set up mirrored database:
+   - create 'activerecord_unittest_mirroring' database,
+   - add 'rails' user as database owner
+   - configure mirror to other server 
+   - create same (rails) user on another server with same sid
+
+2. Set up environment:
+   - for dblib mode 
+     ENV['ACTIVERECORD_UNITTEST_DATASERVER_PRIMARY'] 
+     ENV['ACTIVERECORD_UNITTEST_DATASERVER_MIRROR']  
+   - for odbc
+     ENV['ACTIVERECORD_UNITTEST_DSN_PRIMARY'] 
+     ENV['ACTIVERECORD_UNITTEST_DSN_MIRROR']
+
+3. Run tests:
+   - for dblib mode: 
+     rake test:mirroring:dblib
+   - for odbc mode: 
+     rake test:mirroring:odbc
+
+Test will create table 'programmers', insert a record, force failover to mirror server, insert second record, force failover back to primary, and insert third record.
+
+
+There are few mirroring introspection methods added to every active record class:
+  - db_mirroring_status 
+  - db_mirroring_active?
+  - db_mirroring_synchronized?
+  - server_name 
diff --git a/Rakefile b/Rakefile
index 3a900a7..268c6eb 100644
--- a/Rakefile
+++ b/Rakefile
@@ -35,7 +35,19 @@ namespace :test do
     end
     
   end
-  
+
+  namespace :mirroring do 
+    
+    ['dblib','odbc'].each do |mode|
+      Rake::TestTask.new("#{mode}") do |t|
+        t.libs = test_libs(mode)
+        t.test_files = Dir.glob("test/cases/**/#{mode}_mirroring_test.rb")
+        t.verbose = true
+      end
+    end
+    
+  end
+
 end
 
 
diff --git a/lib/active_record/connection_adapters/sqlserver/mirroring.rb b/lib/active_record/connection_adapters/sqlserver/mirroring.rb
new file mode 100644
index 0000000..dcce991
--- /dev/null
+++ b/lib/active_record/connection_adapters/sqlserver/mirroring.rb
@@ -0,0 +1,85 @@
+module ActiveRecord
+  class Base
+    def self.db_mirroring_status
+
+      #Returns hash with db mirroring status details
+      #  if mirroring is inactive for current database returns empty hash
+      connection.select_one("
+          SELECT 
+              DB_NAME(database_id) database_name
+            , mirroring_role_desc 
+            , mirroring_safety_level_desc
+            , mirroring_state_desc
+            , mirroring_safety_sequence
+            , mirroring_role_sequence
+            , mirroring_partner_instance
+            , mirroring_witness_name
+            , mirroring_witness_state_desc
+            , mirroring_failover_lsn
+         FROM sys.database_mirroring
+         WHERE mirroring_guid IS NOT NULL
+       	   and database_id = db_id(); 
+        ") || {}
+    end
+    
+    #Returns true if current database is db mirroring principal
+    def self.db_mirroring_active?
+      db_mirroring_status["mirroring_role_desc"] == "PRINCIPAL"
+    end
+
+    #Returns true if db mirroring is in synchronized state
+    def self.db_mirroring_synchronized?
+      db_mirroring_status["mirroring_state_desc"] == "SYNCHRONIZED"
+    end
+    
+    #Returns current database server name
+    def self.server_name
+      connection.select_value("select @@servername")
+    end
+
+  end
+end
+
+module ActiveRecord
+  module ConnectionAdapters
+
+    module SqlServerMirroring
+
+      protected
+
+      def mirror_defined?
+        !@connection_options[:mirror].nil?
+      end
+      
+      def switch_to_mirror
+        @connection_options[:mirror].each_key do |key|
+          tmp = @connection_options[:mirror][key]
+          @connection_options[:mirror][key] = @connection_options[key]
+          @connection_options[key] = tmp
+        end
+      end      
+
+    end
+
+    class SQLServerAdapter
+      include ActiveRecord::ConnectionAdapters::SqlServerMirroring
+      
+      def connect_with_mirroring
+        return connect_without_mirroring unless mirror_defined?
+        connect_without_mirroring rescue connect_to_mirror
+        connect_to_mirror if @auto_connecting && !active?
+        @connection
+      end            
+
+      alias_method_chain :connect, :mirroring
+      
+      private 
+
+      def connect_to_mirror
+        switch_to_mirror
+        connect_without_mirroring        
+      end
+
+    end
+  end
+end
diff --git a/lib/active_record/connection_adapters/sqlserver_adapter.rb b/lib/active_record/connection_adapters/sqlserver_adapter.rb
index c302fd5..7d34ecf 100644
--- a/lib/active_record/connection_adapters/sqlserver_adapter.rb
+++ b/lib/active_record/connection_adapters/sqlserver_adapter.rb
@@ -470,3 +470,4 @@ module ActiveRecord
   
 end #module ActiveRecord
 
+require 'active_record/connection_adapters/sqlserver/mirroring'
diff --git a/test/cases/dblib_mirroring_test.rb b/test/cases/dblib_mirroring_test.rb
new file mode 100644
index 0000000..e130ada
--- /dev/null
+++ b/test/cases/dblib_mirroring_test.rb
@@ -0,0 +1,19 @@
+#For dblib connection mode define database mirroring server with :dataserver_mirror key in database.yml. 
+#Example:
+configuration = {
+  :adapter           => 'sqlserver',
+  :mode              => 'dblib',
+  :dataserver        => ENV['ACTIVERECORD_UNITTEST_DATASERVER_PRIMARY'],
+  :username          => ENV['ACTIVERECORD_UNITTEST_USER'] || 'rails',
+  :password          => ENV['ACTIVERECORD_UNITTEST_PASS'] || '',
+  :database          => 'activerecord_unittest_mirroring',
+  :appname           => 'SQLServerAdptrUnit',
+  :azure             => false,
+  :mirror            => { 
+    :dataserver => ENV['ACTIVERECORD_UNITTEST_DATASERVER_MIRROR']
+  }
+}
+
+require 'cases/sqlserver_helper' 
+ActiveRecord::Base.configurations = ActiveRecord::Base.configurations = {'mirroring' => configuration}
+require 'cases/mirroring_test.rb'
diff --git a/test/cases/mirroring_test.rb b/test/cases/mirroring_test.rb
new file mode 100644
index 0000000..80975cb
--- /dev/null
+++ b/test/cases/mirroring_test.rb
@@ -0,0 +1,134 @@
+require 'pp'
+#define model
+class Programmer < ActiveRecord::Base 
+end                
+
+Programmer.establish_connection 'mirroring'
+
+#dummy class to include SqlServerMirroring module, and test it
+class ForMirroringTests
+  include ActiveRecord::ConnectionAdapters::SqlServerMirroring
+
+  def initialize(options)
+    @connection_options = options
+  end
+
+  attr_reader :connection_options
+end
+
+class MirroringTestSqlserver < ActiveRecord::TestCase
+  
+  def setup        
+    create_database_schema    
+  end 
+
+  private 
+
+  def create_database_schema    
+    ActiveRecord::Schema.define do
+      
+      Programmer.connection.create_table :programmers, :force => true do |t|
+        t.column :first_name, :string
+        t.column :last_name, :string
+      end
+      
+    end  
+  end     
+
+  def db_failover
+    Programmer.connection.execute("use master; ALTER DATABASE activerecord_unittest_mirroring SET PARTNER FAILOVER") 
+  end
+  
+  def failover                    
+    begin
+      db_failover
+    rescue 
+      sleep 1
+      retry
+    end
+    Programmer.connection.reconnect!
+    print_current_server_name
+  end         
+  
+  def print_current_server_name
+    print "connected to #{Programmer.server_name}\n"
+  end
+
+  public 
+
+  def test_create    
+    print_current_server_name
+    Programmer.create(:first_name => "Sasa",  :last_name => "Juric")
+    assert_equal 1, Programmer.count                                                          
+    
+    failover
+    
+    Programmer.create(:first_name => "Goran",  :last_name => "Pizent")    
+    assert_equal 2, Programmer.count
+    
+    failover
+    
+    Programmer.create(:first_name => "Vedran",  :last_name => "Skrnjug")    
+    assert_equal 3, Programmer.count    
+  end
+
+  def test_status_flags
+    assert Programmer.db_mirroring_active?
+  end
+  
+  def test_status_flags_without_mirroring
+    assert !Topic.db_mirroring_active?
+    assert !Topic.db_mirroring_synchronized?
+  end
+
+  def test_mirroring_status
+    status = Programmer.db_mirroring_status
+    assert !status.empty?
+    assert_equal "activerecord_unittest_mirroring", status["database_name"]
+    assert_equal "PRINCIPAL", status["mirroring_role_desc"]
+    assert ["SYNCHRONIZED", "SYNCHRONIZING"].include? status["mirroring_state_desc"]
+  end
+
+  def test_mirroring_status_without_mirroring
+    assert Topic.db_mirroring_status.empty?
+  end
+
+  def test_switch_to_mirror
+    fmt = ForMirroringTests.new({  
+      :adapter => :sqlserver,
+      :mode => :dblib,
+      :username => "sa",
+      :password => "cheese",
+      :database => "db_name",
+      :host => "primary_server",
+      :mirror => {
+        :host => "mirror_server",
+        :port => 1434,
+        :password => "mouse"
+      }
+    })    
+    
+    fmt.send(:switch_to_mirror)
+    options = fmt.connection_options
+    assert_equal "mirror_server", options[:host]
+    assert_equal 1434, options[:port]
+    assert_equal "mouse", options[:password]
+
+    assert_equal :sqlserver, options[:adapter]
+    assert_equal :dblib, options[:mode]
+    assert_equal "sa", options[:username]
+    assert_equal "db_name", options[:database]
+
+    fmt.send(:switch_to_mirror)
+    options = fmt.connection_options
+    assert_equal "primary_server", options[:host]
+    assert_nil   options[:port]
+    assert_equal "cheese", options[:password]
+
+    assert_equal :sqlserver, options[:adapter]
+    assert_equal :dblib, options[:mode]
+    assert_equal "sa", options[:username]
+    assert_equal "db_name", options[:database]    
+  end
+
+end
diff --git a/test/cases/odbc_mirroring_test.rb b/test/cases/odbc_mirroring_test.rb
new file mode 100644
index 0000000..7b4208d
--- /dev/null
+++ b/test/cases/odbc_mirroring_test.rb
@@ -0,0 +1,18 @@
+#For odbc connection mode define database mirroring server with :dsn_mirror key in database.yml. 
+#Example:
+configuration = {                                                    
+  :adapter    => 'sqlserver',
+  :mode       => 'ODBC',
+  :host       => 'localhost',
+  :username   => 'rails',
+  :dsn        => ENV['ACTIVERECORD_UNITTEST_DSN_PRIMARY'],
+  #    :dsn_mirror => ENV['ACTIVERECORD_UNITTEST_DSN_MIRROR'],
+  :database   => 'activerecord_unittest_mirroring',
+  :mirror     => { 
+    :dsn => ENV['ACTIVERECORD_UNITTEST_DSN_MIRROR']
+  }
+}
+
+require 'cases/sqlserver_helper' 
+ActiveRecord::Base.configurations = ActiveRecord::Base.configurations = {'mirroring' => configuration}
+require 'cases/mirroring_test.rb'
-- 
1.7.3.5