|
Algorithmes de réplication de bases de données.( Télécharger le fichier original )par Thierry LOGO Université Adventiste de Lukanga - Licence 2013 |
BIBLIOGRAPHIESé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 ~ ANNEXESANNEXE 1 : TRADUCTION DES ALGORITHMES DE REPLICATION SOUS VB.NET2008 ' 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 -- 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 |
|
