Réplication des base de données pour les institutions financières

BIBLIOGRAPHIE

Sébastien ROHAUT (2011), Algorithmique et techniques fondamentales de programmation, éditions ENI.

Christophe HARO (2012), Algorithmique : raisonner pour concevoir, éditions ENI, JONIFAR LINA.

Lambert SONNA MOMO (2001), Réplication et Durabilité dans les systèmes répartis. Mathieu EXBRAYAT (2007), Bases de Données Réparties Concepts et Techniques, ULP Strasbourg.

J. AKOKA et I. WATTIAU (2011), Base de données réparties, le Cnam édition.

Pascal MOLLI et Al (2005), Réplication des données, édition INRIA Lorraine.

Pascal ROQUES (2006), UML 2 par la pratique: étude des cas et exercices, 5ième édition EYROLLES, Paris.

Jérôme GABILAUD (2010), Administration de bases de données avec SQL Server Management Studio 2008, Editions ENI, Pari.

Osée MUHINDO MASIVI (2012), Cours de Théorie des Automates et Calculabilité, cours inédit.

Osée MUHINDO MASIVI (2012), Cours de Laboratoire Informatique II, cours inédit.

Norbert VAGHENI (2012), Cours de Gestion des Institutions Financières Congolaises, cours inédit.

Kasereka Mbahweka (2012), Séminaire de gestion des Micro finances, inédit.

MUMBERE MUYISA (2012), Cours d'Administration des réseaux informatiques, cours inédit.

Dominique LIEFFRING (2007), Cours de SQL SERVEUR 2005 Théorie, Institut d'Enseignement de Promotion Sociale de la Communauté Française Arlon, Réf. : D-F04-09.

Cédric COULON (2006), Réplication Préventive dans une grappe de bases de données, thèse Université de Nantes.

ABDESSAMAD IMINE (2006), Conception Formelle d'Algorithmes de Réplication Optimiste Vers l'Edition Collaborative dans les Réseaux Pair-à-Pair, thèse, université Henri Poincaré, Nancy 1.

Muyisa FRED (2011), modélisation informatique de la gestion d'une bibliothèque universitaire, cas de l`UNILUK.

~ 59 ~

Mumbere MUYISA (2009), Mise en ligne d'une base de données pour une institution universitaire, cas de l'UNILUK.

Jean-Eric PELET & Stéphane MENET (MSTC 2) (2003), L'INTRANET dans une entreprise Pourquoi et comment, article.

PIERRE SUTRA MARC SHAPIRO (2007), Comparaison d'Algorithmes Optimistes pour la Réplication de Bases de Données, INRIA / LIP6.

Wikimedia Foundation (2013), Architectures Client Serveur. Disponible sur http://www.fr.wikipedia.org/client-serveur/

Microsoft Corporation (2003), how to Program the SQL Merge Control by Using Visual Basic _NET-3.mht. Disponible sur http://www.Support.microsoft.com/

~ 60 ~

ANNEXES

ANNEXE 1 : TRADUCTION DES ALGORITHMES DE REPLICATION SOUS VB.NET

2008

' Projet de Mémoire Akangba Logo Thierry

' Université Adventiste de Lukanga

' Sujet: Algorithme de Réplication de base de données d'une institution financière ' copyright Thierry Logo Akangba

'

My Bank Systems 2013

Imports SQLMERGXLib

'This class demonstrates using the SQL Server Merge Agent replication control. Module MergeApp

Sub Merge_Replication()

Prior to running this code, replication needs to be setup as follows:

Create a merge publication called "SampleMergePublication" and configure it to allow pull subscriptions.

' This code will first generate the snapshot. Then the subscription database

' and pull subscription will be created through code. Then the snapshot will be applied at the subscriber

using

' the SQLMergeClass object.

'

' You will also need to set a reference to the following COM dll:

' -Microsoft SQL Merge Control 8.0
'

Dim strPublisher As String Dim strDistributor As String Dim strSubscriber As String Dim strPublisherDatabase As String

Dim strSubscriberDatabase As String

Dim strPublication As String

~ 61 ~

Dim oMerge As SQLMergeClass

strPublisher = "WIN-7C9TIYLXVA5" 'change to the name of your publisher strDistributor = "WIN-7C9TIYLXVA5" 'change to the name of your distributor strSubscriber = "WIN-7C9TIYLXVA5" 'change to the name of your subscriber strPublication = "Ma Replication de Fusion"

strPublisherDatabase = "BDEssai"

strSubscriberDatabase = "Ngabo_DB"

oMerge = New SQLMergeClass()

'Set up the Publisher.

oMerge.Publisher = strPublisher

oMerge.PublisherSecurityMode = SQLMERGXLib.SECURITY_TYPE.NT_AUTHENTICATION

oMerge.PublisherDatabase = strPublisherDatabase

oMerge.Publication = strPublication

'Set up the Distributor.

oMerge.Distributor = strDistributor

oMerge.DistributorSecurityMode = SQLMERGXLib.SECURITY_TYPE.NT_AUTHENTICATION

'Set up the Subscriber.

oMerge.Subscriber = strSubscriber

oMerge.SubscriberDatabase = strSubscriberDatabase

oMerge.SubscriberSecurityMode = SQLMERGXLib.SECURITY_TYPE.NT_AUTHENTICATION

'Set up the subscription.

oMerge.SubscriptionType = SQLMERGXLib.SUBSCRIPTION_TYPE.PULL oMerge.SynchronizationType = SQLMERGXLib.SYNCHRONIZATION_TYPE.AUTOMATIC oMerge.SubscriptionName = "PullMergeSubscription"

'Create the database and subscription.

oMerge.AddSubscription(SQLMERGXLib.DBADDOPTION.CREATE_DATABASE, SQLMERGXLib.SUBSCRIPTION_HOST.NONE)

'Synchronize the subscription.

MsgBox("Starting synchronization...") oMerge.Initialize()

oMerge.Run()

~ 62 ~

oMerge.Terminate()

MsgBox("Synchronization completed.")

End Sub

End Module

` le Bouton BtnRepliquer fait appel à la fonction Merge_Replication

Private Sub BtnRepliqer_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdRepliquer.Click

Merge_Replication()

End Sub

~ 63 ~

ANNEXE 2 : SCRIPT DE REPLICATION GENERE APRES CREATION
DE LA BASE MAITRE (PUBLICATION : REP MY BANK SERVER)

-- Enabling the replication database

use master

exec sp_replicationdboption @dbname = N'My_Bank_Server', @optname = N'publish', @value = N'true' GO

exec [My_Bank_Server].sys.sp_addlogreader_agent @job_login = null, @job_password = null,

@publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N"

GO

exec [My_Bank_Server].sys.sp_addqreader_agent @job_login = null, @job_password = null, @frompublisher =

1

GO

-- Enabling the replication database

use master

exec sp_replicationdboption @dbname = N'My_Bank_Server', @optname = N'merge publish', @value = N'true'

GO

-- Adding the merge publication

use [My_Bank_Server]

exec sp_addmergepublication @publication = N'Rep_My_Bank_Server', @description = N'Merge publication of database "My_Bank_Server" from Publisher "WIN-7C9TIYLXVA5".', @sync_mode = N'native', @retention = 14, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @dynamic_filters = N'false', @conflict_retention = 14, @keep_partition_changes = N'false', @allow_synctoalternate = N'false', @max_concurrent_merge = 0, @max_concurrent_dynamic_snapshots = 0, @use_partition_groups = N'false', @publication_compatibility_level = N'90RTM', @replicate_ddl = 1, @allow_subscriber_initiated_snapshot = N'false', @allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'both', @automatic_reinitialization_policy = 0 GO

exec sp_addpublication_snapshot @publication = N'Rep_My_Bank_Server', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 500, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

~ 64 ~

exec sp_grant_publication_access @publication = N'Rep_My_Bank_Server', @login = N'sa'

GO

exec sp_grant_publication_access @publication = N'Rep_My_Bank_Server', @login = N'AUTORITE

NT\SYSTEM'

GO

exec sp_grant_publication_access @publication = N'Rep_My_Bank_Server', @login =

N'BUILTIN\Administrateurs'

GO

exec sp_grant_publication_access @publication = N'Rep_My_Bank_Server', @login = N'WIN-

7C9TIYLXVA5\SQLServer2005SQLAgentUser$WIN-7C9TIYLXVA5$MSSQLSERVER'

GO

exec sp_grant_publication_access @publication = N'Rep_My_Bank_Server', @login = N'WIN-

7C9TIYLXVA5\SQLServer2005MSSQLUser$WIN-7C9TIYLXVA5$MSSQLSERVER'

GO

exec sp_grant_publication_access @publication = N'Rep_My_Bank_Server', @login = N'WIN-

7C9TIYLXVA5\Administrateur'

GO

exec sp_grant_publication_access @publication = N'Rep_My_Bank_Server', @login = N'distributor_admin'

GO

exec sp_grant_publication_access @publication = N'Rep_My_Bank_Server', @login = N'informaticien'

GO

-- Ajout d'Articles de Fusion appelés Merge Articles

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'sysdiagrams', @source_owner = N'dbo', @source_object = N'sysdiagrams', @type = N'table', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 10000, @identity_range = 1000, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'tExercice', @source_owner = N'dbo', @source_object = N'tExercice', @type = N'table', @description = N", @creation_script = N",

~ 65 ~

@pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'tMasse', @source_owner = N'dbo', @source_object = N'tMasse', @type = N'table', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'tAgence', @source_owner = N'dbo', @source_object = N'tAgence', @type = N'table', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'tCompte', @source_owner = N'dbo', @source_object = N'tCompte', @type = N'table', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

use [My_Bank_Server]

~ 66 ~

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'tOperateur', @source_owner = N'dbo', @source_object = N'tOperateur', @type = N'table', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'tClient', @source_owner = N'dbo', @source_object = N'tClient', @type = N'table', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'tMandataire', @source_owner = N'dbo', @source_object = N'tMandataire', @type = N'table', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'tOperation', @source_owner = N'dbo', @source_object = N'tOperation', @type = N'table', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'auto', @pub_identity_range = 1000, @identity_range = 100, @threshold = 80, @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

~ 67 ~

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'tBilletage', @source_owner = N'dbo', @source_object = N'tBilletage', @type = N'table', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'tTransactionComptable', @source_owner = N'dbo', @source_object = N'tTransactionComptable', @type = N'table', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x000000000C034FD1, @identityrangemanagementoption = N'none', @destination_owner = N'dbo', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = N", @vertical_partition = N'false', @verify_resolver_signature = 1, @allow_interactive_resolver = N'false', @fast_multicol_updateproc = N'true', @check_permissions = 0, @subscriber_upload_options = 0, @delete_tracking = N'true', @compensate_for_errors = N'false', @stream_blob_columns = N'false', @partition_options = 0

GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'fn_diagramobjects',

@source_owner = N'dbo', @source_object = N'fn_diagramobjects', @type = N'func schema only', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'fn_diagramobjects', @force_reinit_subscription = 1 GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'sp_alterdiagram',

@source_owner = N'dbo', @source_object = N'sp_alterdiagram', @type = N'proc schema only', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'sp_alterdiagram', @force_reinit_subscription = 1 GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'sp_creatediagram',

@source_owner = N'dbo', @source_object = N'sp_creatediagram', @type = N'proc schema only', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'sp_creatediagram', @force_reinit_subscription = 1 GO

use [My_Bank_Server]

~ 68 ~

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'sp_dropdiagram',

@source_owner = N'dbo', @source_object = N'sp_dropdiagram', @type = N'proc schema only', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'sp_dropdiagram', @force_reinit_subscription = 1 GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'sp_helpdiagramdefinition', @source_owner = N'dbo', @source_object = N'sp_helpdiagramdefinition', @type = N'proc schema only', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'sp_helpdiagramdefinition', @force_reinit_subscription = 1

GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'sp_helpdiagrams',

@source_owner = N'dbo', @source_object = N'sp_helpdiagrams', @type = N'proc schema only', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'sp_helpdiagrams', @force_reinit_subscription = 1 GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'sp_renamediagram',

@source_owner = N'dbo', @source_object = N'sp_renamediagram', @type = N'proc schema only', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'sp_renamediagram', @force_reinit_subscription = 1 GO

use [My_Bank_Server]

exec sp_addmergearticle @publication = N'Rep_My_Bank_Server', @article = N'sp_upgraddiagrams',

@source_owner = N'dbo', @source_object = N'sp_upgraddiagrams', @type = N'proc schema only', @description = N", @creation_script = N", @pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_owner = N'dbo', @destination_object = N'sp_upgraddiagrams', @force_reinit_subscription = 1 GO

use [My_Bank_Server]

exec sp_changemergepublication N'Rep_My_Bank_Server', N'status', N'active'

GO

-- Adding the merge subscriptions

use [My_Bank_Server]

exec sp_addmergesubscription @publication = N'Rep_My_Bank_Server', @subscriber = N'WIN-7C9TIYLXVA5', @subscriber_db = N'Ma_Bank_Client', @subscription_type = N'Push', @sync_type = N'Automatic', @subscriber_type = N'Global', @subscription_priority = 75, @description = N", @use_interactive_resolver = N'False'

~ 69 ~

exec sp_addmergepushsubscription_agent @publication = N'Rep_My_Bank_Server', @subscriber = N'WIN-7C9TIYLXVA5', @subscriber_db = N'Ma_Bank_Client', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @publisher_security_mode = 1, @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0

GO