Transactional Replication – Configure Publication

Once youve configured a distributor, youre ready to create publications. Perform the below steps as follows,

Step1 : Connect to an instance (Sansu) of SQL Server by using the SQL Server Management Studio (SSMS), navigate to the “replication” folder then local publications folder, right-click this folder, and select New Publication. This invokes the new publication wizard and if you dont ever want to see this screen again, simply check the option to skip the introductory screen in the future.

Step2 : The next page asks to choose the Distributor. You can either choose the publisher (Sansu) as the distributor or choose some other servers as distributor by clicking the “Add” button. You need to ensure that the distributor you choose is already configured as distributor in this case I have chosen SansuTest.

Step3 : In the Next screen you need to type the password through which the publisher will connect to the distributor. This is the same password which you typed while configuring distributor (Refer Step8 in Configuring Distributor).

Step4 : The subsequent screen allows you to choose the database in which you want to create a publication; for purposes of this article, Ill create a publication within the AdventureWorksDW database that can be created as part of SQL Server 2005 installation.

Step5 : After selecting the database, you must choose the publication type. The wizard offers the following options:

• Snapshot Publication
• Transactional Publication
• Transactional Publication with Updatable Subscriptions
• Merge Publication

The wizard includes a brief description of each type of publication. Ill use the transactional publication.

Step6 : Now I need to choose the articles to be published. In the below screen shot you can see that I am unable to choose the table “Databaselog” because in Transactional Replication you cannot publish a table if the table does not have Primary Key column. Primary Key columns are required for all tables involved in Transactional Replication.

Step7 : In the next screen I choose the “DimGeography” Table as the article to be published. Pls note that this table has a Primary key column named “GeographyKey”.

Replication has certain rules as far as which columns can be filtered. Transactional replication prohibits filtering primary-key columns. In addition, if your publication allows updateable subscriptions, you must replicate the msrepl_tran_version column (added by SQL Server when you create such publications). Further, publications that allow updateable subscriptions must replicate any column that doesnt allow nulls, doesnt have a predefined default, and isnt an identity column.

Step8 : If you check the box Show Only Checked Objects in the List, the wizard limits the list of articles to only those that have been checked. The Article Properties button allows you to set properties for the highlighted article or for all table articles.

Most properties you can set for table articles are self-explanatory; for example, the Copy Foreign Key Constraints option instructs the replication to include foreign key constraints when creating the table in the subscriber database.

Step9 : Once youve set the necessary properties for the article you want to replicate, you can add publication filters. In previous versions of SQL Server, these filters were referred to as horizontal filtersyou create them by supplying a WHERE clause to limit the number of published rows. As shown earlier, now you can filter the publication vertically by specifying which columns to publish.

Step10 : Next the snapshot agent page appears. The snapshot agent copies the schema and data of the replicated articles into the snapshot folder. On this page you can indicate that the snapshot agent should run immediately or on a regular schedule. By clicking the change button you can schedule it as per your desire.

Step11 : Next you specify the security settings for the snapshot and log reader agents.

Step12 : At this point, the wizard presents a synopsis of the steps its about to undertake; once you click Finish, the wizard will create the script for adding a distributor and/or save the script, depending on what you specified.

Step13 : The Script for creating the same publication will be as follows,

/****** Scripting replication configuration for server SANSU. Script Date: 10/19/2007 6:56:32 PM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Installing the server SANSUTEST as a Distributor. Script Date: 10/19/2007 6:56:32 PM ******/
USE master
EXEC sp_adddistributor @distributor = N'SANSUTEST', @password = N''
GO
USE [AdventureWorksDW]
EXEC sp_replicationdboption @dbname = N'AdventureWorksDW', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
USE [AdventureWorksDW]
EXEC sp_addpublication @publication = N'DimGeography', 
@description = N'Transactional publication of database ''AdventureWorksDW'' from Publisher ''SANSU''.', 
@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'DimGeography', @frequency_type = 4, 
@frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, 
@frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, 
@active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = NULL, 
@job_password = NULL, @publisher_security_mode = 1
USE [AdventureWorksDW]
EXEC sp_addarticle @publication = N'DimGeography', @article = N'DimGeography', @source_owner = N'dbo', 
@source_object = N'DimGeography', @type = N'logbased', @description = N'', @creation_script = NULL, 
@pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', 
@destination_table = N'DimGeography', @destination_owner = N'dbo', @status = 0, @vertical_partition = N'false', 
@ins_cmd = N'CALL sp_MSins_dboDimGeography', @del_cmd = N'CALL sp_MSdel_dboDimGeography', @upd_cmd = N'SCALL sp_MSupd_dboDimGeography'
GO