Personal Notes of Edwin Eu

Archive for November, 2017|Monthly archive page

DBA – How to add additional article into existing Publication. 

In Microsoft SQL Server on 11/07/2017 at 8:42 AM

How to add additional article into existing Publication.

Method 1: To add an article after a publication is created

  1. On the Articles page of the Publication Properties – <Publication> dialog box, clear the Show only checked objects in the list check box. This allows you to see the unpublished objects in the publication database.
  2. Select the check box next to each article you want to add.
  3. Click OK.


Method 2:  To add an article after a publication is created through Replication Transact-SQL Programming.

EXEC sp_addarticle @publication = N’RMS_Publication’,
@article = N’lwcase’, @source_owner = N’dbo’, @source_object = N’lwcase’,
@type = N’logbased’, @description  = null, @creation_script = null,
@pre_creation_cmd = N’drop’,
@identityrangemanagementoption = N’manual’, @destination_table = N’lwcase’,
@destination_owner = N’dbo’, @vertical_partition = N’false’,
@ins_cmd = N’CALL sp_MSins_dbolwcase’, @del_cmd = N’CALL sp_MSdel_dbolwcase’, @upd_cmd = N’SCALL sp_MSupd_dbolwcase’

DBA Notes – Transactional Database Replication

In Microsoft SQL Server on 11/06/2017 at 3:09 PM

I was assigned a database replication projects to setup database replication that supported ETL projects for a Fire and Policy department.

The Source and Target database are located in the same domain.

I used my previous experiences and I know how to setup database replication. My previous working experiences were setting – up a Transactional replication to replication wind farm database.

It’s important to understand the database replication terminology terms like:

    • Publisher
      • The MS SQL Server Database instance that provided data available to Subscribers. The Articles like Tables, and Views which are to be replicated are defined at the publisher.
    • Distributor 
      • The MS SQL Server Database instance that distributes data from one or more Publisher to one or more Subscribers
    • Subscriber
      • The MS SQL Server Database that going to receive data from Publishers.
    • Pull Distribution
      • Setup and located in Subscriber MS SQL Server
    • Push Distribution
      • Setup  and located in Publisher MS SQL Server.
    • Articles
      • An article is a SQL Server object like Table, View, Function, stored procedure, etc.
    • The Snapshot Agent
      • Distributes data exactly as of a point in time.
      • Prepares snapshot files containing schema and data of publication articles
      • Stores the files in the snapshot folder
    • The Distribution Agent
    • The Log Reader Agent