Transaction Replication: Transaction Replication is used when DML or DDL schema changes performed on an object of one database on one SQL Server Instance/ server needs to be replicated immediately on the database residing on another SQL Server Instance/server.
Change in Schema as well as in data will be reflected in replicated database immediately but this is depends on the network/internet setup you have between both SQL Server Instance/Server.
You can select any number of articles (object) as a candidate of replication no matter whether it is function, stored procedure or table. Just keep one thing in mind that any table you choose as a candidate of transaction replication, supposed to have Primary Key. Table without primary key will not be a part of transaction replication.
You can select two different SQL Server installation located in two different Server may or may not be in same premises but in same network. I have had two different instance of SQL Server 2012 in one server while writing this article.
Instance 1: “WIN-9H6QATRKY81\SQL2K12DEV”, I will call this instance as ServerA in this article.
Instance 2: “WIN-9H6QATRKY81\SQL2K12DEVTest”, I will call this instance as ServerB in this article.
If you are ready with AdventureWorks2012 database or any other database of your choice in one server and have another server available for subscriber, let us start creating replication.
1.) Create Distributor by login to ServerB (Subscriber) as I want to create Distributor in Subscriber server. If budget permits, it is good to have third dedicated server for distributor.
2.) Select or add the server where you wanted to create distributor database, since we are already logged into to ServerB, we can directly select first radio button from following screen capture.
3.) If your SQL Server Agent not set to start automatically at startup, it will ask you to start it as SQL Server Agent is responsible to run the job which will transmit data.
4.) Give shared network path where replication can create snapshot and use that location as a primary source for data. Note that both or all three server should have proper access to this shared folder as we are going to save all script file along with distributor database in this path.
5.) Give path where SQL Server can create “Distributor” database which is responsible to receive data from publisher and send it to subscriber.
6.) Select Publisher server for distribution database. By default it has selected ServerB but we want ServerA to be the publisher so add ServerA instance here.
7.) login to publisher database and it will be selected.
8.) Now deselect ServerB and Select ServerA where we have just logged in from above screen.
9.) I have provided password for my SA login password so that distributor can use this password while connecting to publisher. You can use any other login with sufficient permission.
10.) Select “Configure Distribution” checkbox along with “Generate Script” check box
11.) Give the path for saving a script file. I have used the same shared path I have used before.
Here is the script I have received after clicking on the “Finish” button from above screen.
1 | /****** Scripting replication configuration. Script Date: 11/25/2012 3:49:05 PM ******/ |
3 | /****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/ |
5 | /****** Installing the server as a Distributor. Script Date: 11/25/2012 3:49:05 PM ******/ |
9 | exec sp_adddistributor @distributor = N'WIN-9H6QATRKY81\SQL2K12DEVTEST', @password = N'' |
13 | exec sp_adddistributiondb @database = N'distribution', @data_folder = N'E:\ReplicationShare', @log_folder = N'E:\ReplicationShare', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1 |
19 | if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) |
21 | create table UIProperties(id int) |
23 | if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) |
25 | EXEC sp_updateextendedproperty N'SnapshotFolder', N'E:\ReplicationShare', 'user', dbo, 'table', 'UIProperties' |
29 | EXEC sp_addextendedproperty N'SnapshotFolder', N'E:\ReplicationShare', 'user', dbo, 'table', 'UIProperties' |
33 | exec sp_adddistpublisher @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = N'E:\ReplicationShare', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER' |
12.) Now manually confirm that you have received “Distributor” database under “System Databases” in the ServerB or not.
13.) Go to ServerA, this is the time to create publication now.
14.) Select ServerA as a pubcation.
15.) Give the same shared path to the replication to save and keep snapshot of publication.
16.) Select the database you wanted to replicate. I have selected “AdventureWorks2012” from ServerA.
17.) Select “Transactional Replication” from following screen.
18.) Select Table, SP, Functions etc. which you want to replicate. Confirm the table you select for replication, supposed to have primary key. Select other supported objects of table too. For example you have one computed column populated from UDF in your table so you have to select that function along with table.
19.) Click on next from following screen as I don’t want to specify any criteria for data. I want to transfer all data from my table.
20.) Select checkbox for “Create a snapshot immediately”.
21.)Click on “Security Settings” from following screen shot.
22.) provide sufficient credential here so that replication works smooth without any security issues.
23.) Once you provide security credential and click on “Ok” button, you will get screen something like this:
24.) select both check box from following screen.
25.) give proper path to save script for your publication.
Here is the script generated for publication:
1 | /****** Scripting replication configuration. Script Date: 11/25/2012 4:01:52 PM ******/ |
3 | /****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/ |
5 | /****** Installing the server as a Distributor. Script Date: 11/25/2012 4:01:52 PM ******/ |
9 | exec sp_adddistributor @distributor = N'WIN-9H6QATRKY81\SQL2K12DEV', @password = N'' |
13 | exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2K12DEV\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1 |
19 | if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) |
21 | create table UIProperties(id int) |
23 | if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) |
25 | EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\win-9H6QATRKY81\ReplicationShare', 'user', dbo, 'table', 'UIProperties' |
29 | EXEC sp_addextendedproperty N'SnapshotFolder', N'\\win-9H6QATRKY81\ReplicationShare', 'user', dbo, 'table', 'UIProperties' |
33 | exec sp_adddistpublisher @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @distribution_db = N'distribution', @security_mode = 0, @login = N'sa', @password = N'', @working_directory = N'\\win-9H6QATRKY81\ReplicationShare', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER' |
37 | use [AdventureWorks2012] |
39 | exec sp_replicationdboption @dbname = N'AdventureWorks2012', @optname = N'publish', @value = N'true' |
43 | use [AdventureWorks2012] |
45 | exec [AdventureWorks2012].sys.sp_addlogreader_agent @job_login = N'win-9H6QATRKY81\Administrator', @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N' ', @job_name = null |
51 | use [AdventureWorks2012] |
53 | exec sp_addpublication @publication = N'AdventureWorks2012', @description = N'Transactional publication of database ''AdventureWorks2012'' from Publisher ''WIN-9H6QATRKY81\SQL2K12DEV''.', @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' |
57 | exec sp_addpublication_snapshot @publication = N'AdventureWorks2012', @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'win-9H6QATRKY81\Administrator', @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N'' |
59 | use [AdventureWorks2012] |
61 | exec sp_addarticle @publication = N'AdventureWorks2012', @article = N'Customer', @source_owner = N'Sales', @source_object = N'Customer', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Customer', @destination_owner = N'Sales', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_SalesCustomer', @del_cmd = N'CALL sp_MSdel_SalesCustomer', @upd_cmd = N'SCALL sp_MSupd_SalesCustomer' |
26.) Provide the name for your publisher. I have provided “AdventureWorks2012”
27.) now connect to ServerB and add “New Subscription”
28.) Subscriber needs publisher to get data so select out publisher we have created.
29.) our publication name was “AdventureWorks2012” which I have selected from following screen:
30.) select “Pull Subscription” as I want to run all replication related job in ServerB and pull the data from ServerA. In Push method, replication related job runs from my ServerA which is primary server for my application and I doesn’t want to add overhead of replication job.
31.) If you already have Subscription database then select it otherwise create a new one. I have created new database named “AdventureWorks2012Subscriber”
32.) my newly created subscription database “AdventureWorks2012Subscriber” is selected now.
33.) SQL Server Agent would need proper credential to connect with Distrubtor database and subscription database. Give proper credential to keep your replication smooth.
34.) select “Run Continuously” for agent.
35.) select “Immediately” so that subscription gets populated immediately.
36.) select both the checkbox as usual.
37.) Give the path for script of subscription.
Here is the script generated for subscription.
3 | use [AdventureWorks2012] |
5 | exec sp_addsubscription @publication = N'AdventureWorks2012', @subscriber = N'WIN-9H6QATRKY81\SQL2K12DEVTEST', @destination_db = N'AdventureWork2012Subscriber', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only' |
15 | use [AdventureWork2012Subscriber] |
17 | exec sp_addpullsubscription @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @publication = N'AdventureWorks2012', @publisher_db = N'AdventureWorks2012', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 1 |
21 | exec sp_addpullsubscription_agent @publisher = N'WIN-9H6QATRKY81\SQL2K12DEV', @publisher_db = N'AdventureWorks2012', @publication = N'AdventureWorks2012', @distributor = N'WIN-9H6QATRKY81\SQL2K12DEV', @distributor_security_mode = 0, @distributor_login = N'sa', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @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 = 20121125, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = N'win-9H6QATRKY81\Administrator', @job_password = null, @publication_type = 0 |
So finally we have implemented Transaction Replication. You can confirm whether replication is working or not by inserting few records in one of the table in ServerA which supposes to take effect immediately in ServerB’s database.
source:extreme-advice.com
0 comments:
Post a Comment