In this video you will learn how to create Transactional replication in SQL Server using SQL Server management studio as well as using T-SQL script. You will also learn how to configure the publication for transactional replication, best practices of configuring publications for Transactional replication, how to add database and it's articles in Transactional replication, configuring Publication agent security and best practices of security configuration of Publisher as well as Subscriber.
It also briefly talk about what exactly is publisher, distributor and subscriber in replication in SQL Server and what is replication in sql server and replication components in SQL Server as well as consideration of Transactional Replication.
Script used in the video to Create Transactional Replication in SQL Server
It also briefly talk about what exactly is publisher, distributor and subscriber in replication in SQL Server and what is replication in sql server and replication components in SQL Server as well as consideration of Transactional Replication.
Script used in the video to Create Transactional Replication in SQL Server
-- Enabling the replication database USE master EXEC Sp_replicationdboption @dbname = N'Tfs_Configuration', @optname = N'publish', @value = N'true' GO EXEC [Tfs_Configuration].sys.Sp_addlogreader_agent @job_login = N'Techbrothers\clusteradmin', @job_password = Mraza1904, @publisher_security_mode = 1 GO -- Adding the transactional publication USE [Tfs_Configuration] EXEC Sp_addpublication @publication = N'TFS_Configuration_pub', @description = N'Transactional publication of database ''Tfs_Configuration'' from Publisher ''TBSSQL\SQLPROD''.', @sync_method = N'concurrent', @retention = 0, @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', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false' GO EXEC Sp_addpublication_snapshot @publication = N'TFS_Configuration_pub', @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, @job_login = N'Techbrothers\clusteradmin', @job_password = Mraza1904, @publisher_security_mode = 1 EXEC Sp_grant_publication_access @publication = N'TFS_Configuration_pub', @login = N'sa' GO EXEC Sp_grant_publication_access @publication = N'TFS_Configuration_pub', @login = N'TECHBROTHERS\sqlsvc' GO EXEC Sp_grant_publication_access @publication = N'TFS_Configuration_pub', @login = N'TECHBROTHERS\clusteradmin' GO EXEC Sp_grant_publication_access @publication = N'TFS_Configuration_pub', @login = N'NT SERVICE\Winmgmt' GO EXEC Sp_grant_publication_access @publication = N'TFS_Configuration_pub', @login = N'NT SERVICE\SQLWriter' GO EXEC Sp_grant_publication_access @publication = N'TFS_Configuration_pub', @login = N'NT SERVICE\MSSQL$SQLPROD' GO EXEC Sp_grant_publication_access @publication = N'TFS_Configuration_pub', @login = N'NT SERVICE\SQLAgent$SQLPROD' GO EXEC Sp_grant_publication_access @publication = N'TFS_Configuration_pub', @login = N'distributor_admin' GO -- Adding the transactional articles USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_AccountHostMapping', @source_owner = N'dbo', @source_object = N'tbl_AccountHostMapping', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_AccountHostMapping', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_AccountHostMapping]', @del_cmd = N'CALL [sp_MSdel_dbotbl_AccountHostMapping]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_AccountHostMapping]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_CatalogPathLength', @source_owner = N'dbo', @source_object = N'tbl_CatalogPathLength', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_CatalogPathLength', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_CatalogPathLength]', @del_cmd = N'CALL [sp_MSdel_dbotbl_CatalogPathLength]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_CatalogPathLength]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_CatalogResourceType', @source_owner = N'dbo', @source_object = N'tbl_CatalogResourceType', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_CatalogResourceType', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_CatalogResourceType]', @del_cmd = N'CALL [sp_MSdel_dbotbl_CatalogResourceType]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_CatalogResourceType]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_InvitationCode', @source_owner = N'dbo', @source_object = N'tbl_InvitationCode', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'tbl_InvitationCode', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_InvitationCode]', @del_cmd = N'CALL [sp_MSdel_dbotbl_InvitationCode]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_InvitationCode]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_InvitationCodeRequests', @source_owner = N'dbo', @source_object = N'tbl_InvitationCodeRequests', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'tbl_InvitationCodeRequests', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_InvitationCodeRequests]', @del_cmd = N'CALL [sp_MSdel_dbotbl_InvitationCodeRequests]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_InvitationCodeRequests]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_JobQueue', @source_owner = N'dbo', @source_object = N'tbl_JobQueue', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_JobQueue', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_JobQueue]', @del_cmd = N'CALL [sp_MSdel_dbotbl_JobQueue]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_JobQueue]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_Message', @source_owner = N'dbo', @source_object = N'tbl_Message', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_Message', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_Message]', @del_cmd = N'CALL [sp_MSdel_dbotbl_Message]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_Message]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_ProcessTemplateDescriptor', @source_owner = N'dbo', @source_object = N'tbl_ProcessTemplateDescriptor', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_ProcessTemplateDescriptor', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_ProcessTemplateDescriptor]', @del_cmd = N'CALL [sp_MSdel_dbotbl_ProcessTemplateDescriptor]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_ProcessTemplateDescriptor]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_QuotaDefinitions', @source_owner = N'dbo', @source_object = N'tbl_QuotaDefinitions', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'tbl_QuotaDefinitions', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_QuotaDefinitions]', @del_cmd = N'CALL [sp_MSdel_dbotbl_QuotaDefinitions]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_QuotaDefinitions]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_ReindexExclusions', @source_owner = N'dbo', @source_object = N'tbl_ReindexExclusions', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_ReindexExclusions', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_ReindexExclusions]', @del_cmd = N'CALL [sp_MSdel_dbotbl_ReindexExclusions]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_ReindexExclusions]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_SeparatorIndex', @source_owner = N'dbo', @source_object = N'tbl_SeparatorIndex', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_SeparatorIndex', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_SeparatorIndex]', @del_cmd = N'CALL [sp_MSdel_dbotbl_SeparatorIndex]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_SeparatorIndex]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_ServiceHost', @source_owner = N'dbo', @source_object = N'tbl_ServiceHost', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_ServiceHost', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_ServiceHost]', @del_cmd = N'CALL [sp_MSdel_dbotbl_ServiceHost]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_ServiceHost]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_ServiceHostInstance', @source_owner = N'dbo', @source_object = N'tbl_ServiceHostInstance', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_ServiceHostInstance', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_ServiceHostInstance]', @del_cmd = N'CALL [sp_MSdel_dbotbl_ServiceHostInstance]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_ServiceHostInstance]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_ServiceHostProcess', @source_owner = N'dbo', @source_object = N'tbl_ServiceHostProcess', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_ServiceHostProcess', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_ServiceHostProcess]', @del_cmd = N'CALL [sp_MSdel_dbotbl_ServiceHostProcess]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_ServiceHostProcess]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_ServicingJobDetail', @source_owner = N'dbo', @source_object = N'tbl_ServicingJobDetail', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_ServicingJobDetail', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_ServicingJobDetail]', @del_cmd = N'CALL [sp_MSdel_dbotbl_ServicingJobDetail]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_ServicingJobDetail]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_ServicingOperation', @source_owner = N'dbo', @source_object = N'tbl_ServicingOperation', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_ServicingOperation', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_ServicingOperation]', @del_cmd = N'CALL [sp_MSdel_dbotbl_ServicingOperation]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_ServicingOperation]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_ServicingOperationGroup', @source_owner = N'dbo', @source_object = N'tbl_ServicingOperationGroup', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_ServicingOperationGroup', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_ServicingOperationGroup]', @del_cmd = N'CALL [sp_MSdel_dbotbl_ServicingOperationGroup]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_ServicingOperationGroup]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_ServicingStep', @source_owner = N'dbo', @source_object = N'tbl_ServicingStep', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_ServicingStep', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_ServicingStep]', @del_cmd = N'CALL [sp_MSdel_dbotbl_ServicingStep]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_ServicingStep]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_ServicingStepGroup', @source_owner = N'dbo', @source_object = N'tbl_ServicingStepGroup', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_ServicingStepGroup', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_ServicingStepGroup]', @del_cmd = N'CALL [sp_MSdel_dbotbl_ServicingStepGroup]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_ServicingStepGroup]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_tmpLobParameter', @source_owner = N'dbo', @source_object = N'tbl_tmpLobParameter', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'tbl_tmpLobParameter', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_tmpLobParameter]', @del_cmd = N'CALL [sp_MSdel_dbotbl_tmpLobParameter]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_tmpLobParameter]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_Trace', @source_owner = N'dbo', @source_object = N'tbl_Trace', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_Trace', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_Trace]', @del_cmd = N'CALL [sp_MSdel_dbotbl_Trace]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_Trace]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_TransactionMark', @source_owner = N'dbo', @source_object = N'tbl_TransactionMark', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'none', @destination_table = N'tbl_TransactionMark', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_TransactionMark]', @del_cmd = N'CALL [sp_MSdel_dbotbl_TransactionMark]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_TransactionMark]' GO USE [Tfs_Configuration] EXEC Sp_addarticle @publication = N'TFS_Configuration_pub', @article = N'tbl_UpdatePackageHistory', @source_owner = N'dbo', @source_object = N'tbl_UpdatePackageHistory', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'tbl_UpdatePackageHistory', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbotbl_UpdatePackageHistory]', @del_cmd = N'CALL [sp_MSdel_dbotbl_UpdatePackageHistory]', @upd_cmd = N'SCALL [sp_MSupd_dbotbl_UpdatePackageHistory]' GO -- Adding the transactional subscriptions USE [Tfs_Configuration] EXEC Sp_addsubscription @publication = N'TFS_Configuration_pub', @subscriber = N'TBSCLIENT\SQLMIRROR', @destination_db = N'SalesOrders', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0 EXEC Sp_addpushsubscription_agent @publication = N'TFS_Configuration_pub', @subscriber = N'TBSCLIENT\SQLMIRROR', @subscriber_db = N'SalesOrders', @job_login = N'Techbrothers\clusteradmin', @job_password = Mraza1904, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor' GO
How to Create Transactional Replication in SQL Server
No comments:
Post a Comment