Categories
High Availability

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.

pubconfig_image013

 

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).

pubconfig_image014

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.

pubconfig_image015

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.

pubconfig_image016

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.

pubconfig_image017

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”.

pubconfig_image018

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.

pubconfig_image019

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.

pubconfig_image020

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.

pubconfig_image021

 

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

pubconfig_image022

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.

pubconfig_image023

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

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*