Skip to content

Instantly share code, notes, and snippets.

@napcs

napcs/gist:1015574

Forked from ianic/gist:976147
Created June 8, 2011 22:17
Show Gist options
  • Save napcs/1015574 to your computer and use it in GitHub Desktop.
Save napcs/1015574 to your computer and use it in GitHub Desktop.
Download ZIP
database mirroring patch for activerecord sql server adapter
Raw
gistfile1.txt
From 6f174a93fdda24b0913f65734d0b36849910e3dc Mon Sep 17 00:00:00 2001
From: Igor Anic <igor.anic@gmail.com>
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment