Replication, SQL, Tip & Tricks

Database Replication – Part I

One of my reader had requested to write post on this topic. I have gone through some posts and prepared a step by step process to demonstrate how we can configure Database Replication in SQL.

I will be covering the topic in couple of posts, this is first post in this series.

Today I will be covering basic theory behind the topic and then move to practical approach in my next post.

Brief extract of Topic

Database replication can be done in at least four different ways:

  • Snapshot replication: Data on one server is simply copied to another server, or to another database on the same server.
  • Merging replication: Data from two or more databases is combined into a single database.
  • Transactional replication: Users receive full initial copies of the database and then receive periodic updates as data changes.
  • Peer-to-Peer publication: Peer-Peer publication enables multi-master replication. The publisher streams transactions to all the peers in the topology. All peer nodes can read and write changes and the changes are propagated to all the nodes in the topology.

A distributed database management system ensures that changes, additions, and deletions performed on the data at any given location are automatically reflected in the data stored at all the other locations. Therefore, every user always sees data that is consistent with the data seen by all the other users.


SQL Server replication is based on the “Publish and Subscribe” metaphor. Let us look at each of the individual components in detail.



  • It is a source database where replication starts. It makes data available for replication.
  • Publishers define what they publish through a publication.



  • Articles are the actual database objects included in replication like tables, views, indexes, etc.
  • An article can be filtered when sent to the subscriber.



  • A group of articles is called publication.
  • An article can’t be distributed individually. Hence publication is required.



  • It is intermediary between publisher and subscriber.
  • It receives published transactions or snapshots and then stores and forwards these publications to the subscriber.
  • It has 6 system databases including distribution.


  • It is the destination database where replication ends.
  • It can subscribe to multiple publications from multiple publishers.
  • It can send data back to publisher or publish data to other subscribers.



  • It is a request by a subscriber to receive a publication.
  • We have two types of subscriptions – push and pull.

Push Subscriptions


  • With this subscription, the publisher is responsible for updating all the changes to the subscriber without the subscriber asking those changes.
  • Push subscriptions are created at the Publisher server.

Pull Subscriptions –


  • With this subscription the subscriber initiates the replication instead of the publisher.
  • The subscriptions are created at the Subscriber server.


Detailed Description on Types of Replication

Snapshot Replication

Snapshot replication simply takes a “snapshot” of the data on one server and moves that data to another server (or another database on the same server). After the initial synchronization snapshot, replication can refresh data in published tables periodically—based on the schedule you specify. Although snapshot replication is the easiest type to set up and maintain, it requires copying all data each time a table is refreshed.

Between scheduled refreshes, data on the publisher might be very different from the data on subscriber. In short, snapshot replication isn’t very different from emptying out the destination table(s) and using a DTS package to import data from the source.

Transactional Replication

Transactional replication involves copying data from the publisher to the subscriber(s) once and then delivering transactions to the subscriber(s) as they occur on the publisher. The initial copy of the data is transported by using the same mechanism as with snapshot replication: SQL Server takes a snapshot of data on the publisher and moves it to the subscriber(s). As database users insert, update, or delete records on the publisher, transactions are forwarded to the subscriber(s).

To make sure that SQL Server synchronizes your transactions as quickly as possible, you can make a simple configuration change: Tell it to deliver transactions continuously. Alternatively, you can run synchronization tasks periodically. Transactional replication is most useful in environments that have a dependable dedicated network line between database servers participating in replication. Typically, database servers subscribing to transactional publications do not modify data; they use data strictly for read-only purposes. However, SQL Server does support transactional replication that allows data changes on subscribers as well.

Merge Replication

Merge replication combines data from multiple sources into a single central database. Much like transactional replication, merge replication uses initial synchronization by taking the snapshot of data on the publisher and moving it to subscribers. Unlike transactional replication, merge replication allows changes of the same data on publishers and subscribers, even when subscribers are not connected to the network. When subscribers connect to the network, replication will detect and combine changes from all subscribers and change data on the publisher accordingly. Merge replication is useful when you have a need to modify data on remote computers and when subscribers are not guaranteed to have a continuous connection to the network.


Replication process works in the background with the help of jobs.

These jobs are also called as agents. These jobs internally uses respective .exe files present in …………….. \110\COM folder.

All the agents’ information is present in Distribution db in the following tables.



Snapshot Agent

  • It is an executable file that prepares snapshot files containing schema and data of published tables and db objects.
  • It stores the files in the snapshot folder, and records synchronization jobs in the distribution database.


Distribution Agent

  • It is used with snapshot and transactional replication.
  • It applies the initial snapshot to the Subscriber and moves transactions held in the Distribution db to Subscribers.
  • It runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.

Log Reader Agent

  • It is used with transactional replication, which moves transactions marked for replication from the transaction log on the publisher to the distribution db.
  • Each db has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

Merge Agent


  • It is used with merge replication.
  • It applies the initial snapshot to the Subscriber and moves incremental data changes that occur.
  • Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both.
  • It captures changes using triggers.

Queue Reader Agent


  • It is used with transactional replication with the queued updating option.
  • It runs at the Distributor and moves changes made at the Subscriber back to the Publisher.
  • Unlike Distribution Agent and Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution db.


We will continue on same topic in my next post.

How To, Information, Instalation & Configuration, Maderia, Tip & Tricks

Assisted Setup (Sales Tax) – in Madeira

Today we will see how Sales Tax setup is done in Madeira. Since preview is limited to US so many of the information may not apply or some different options will be available for other countries. 

Whatsoever may be in final release we will see at that time when finally it will be released. Lets look what is available in this preview.

Login to Madeira using your credentials.

From Action Tab on landing/ Role Center Page on Ribbon click on Assisted Setup & Task.


List with available options page is opened.


Select Setup Sales Tax to continue.

Welcome Screen appears click on Next Button.


Default tax group is created and you can start with other setup, click on Next.


We are only talking about Tax since this preview is only for US so options relevant to them only available. 


Select the Account for Tax Account (Sales & Purchase) using the Assist button which lists you the Available Accounts in COA.


Next City, Country & State related information need to be filled, and then click Next Button to proceed.


Depending upon Information provided in Previous Screen it suggests Tax Area Code, click on Next Button to continue.


We are done with Setup now we will assign this to Customer, Vendor and Company.

Click on Next to continue.


You can Assign the Tax Information to Customer as per entering simple query.

We can assign or we can leave this activity for later.

We have option to schedule a reminder for this activity. Click on Schedule to continue.


Enter the Date and Time for Start & Expiry of this reminder.

This will setup a Job for this and keep reminding you so that you have track of, that this activity is still pending and need to be completed.

Once done choose OK Button to continue.


You can see this in Jobs Entry Page.


Similarly you can do for Vendor too.


For company it will be applicable if you selected, so no separate screen for same for query based assignment.

Select OK to Continue.


We are done with our Tax Setup for Company, Customer & Vendor.


That’s all for today, will come up with more information in my next post.

Till then keep exploring and learning.




The New Report Scheduling feature for end users running reports in Microsoft Dynamics Navision 2015

The new Report Scheduling feature for end users running reports:

  • End user can schedule reports for later execution.
  • User can get to his next task quicker.
  • User has an inbox part for viewing the scheduled reports.

 A. Setting up the job queue to run a report

My Customer needs to run a number of long running reports and does not want to wait for them to complete.

The scenario is simplified because a typical customer setup would involve setting up a NAS instance to process the reports.

* Open the Job Queues page.


* On the Home tab, in the New group, choose New to set up a new job queue


* In the Code field, type Reports.

* Fill in the Description field My Report Schedule.

* Leave the Job Queue Category Filter field blank.

* On the Home tab, in the Process group, choose Start Job Queue.

* Close the Job Queue Card


A job queue with no filter runs the reports.

In many customer installations, the reports will be picked up by the DEFAULT job queue

B. Adding the Report Inbox to the Role Center

My Customer wants easy access to the reports that he has scheduled, so he adds the Report Inbox part to the Role Center page.

* To open the Role Center page that you want to customize, in the navigation pane, choose the Home button, then choose the Role Center menu item.

* On the Application menu , choose Customize, and then Customize This Page.

* In the Available Parts pane, select Report Inbox, and then choose the Add button.

* To move the Report Inbox part to top of the second column in the Role Center layout pane, select it and then use the Move buttons.

* (Optional) To add the My Job Queue to the Role Center, in the Available Parts pane, select My Job Queue, and then choose the Add button.

* (Optional) To move the My Job Queue part below the Report Inbox in the Role Center layout pane, select it and then use the Move buttons.



* Verify that the Report Inbox appears in the Role Center.


C.Scheduling a report and viewing the result

* On the Role Center, on the Report tab, choose Customer Order – Summary.

* On the report request window, choose the Print button, and then choose Schedule


* In the Schedule a Report window that appears, in the Description field type a different text.

* In the Report Output Type field, select the down arrow, and choose PDF.

* Choose the OK button.


* Go to the Report Inbox on the Role Center and show that the report appears when it is finished running.

* To view the report, select it in the Report Inbox, and then choose Show.


D. When a scheduled report fails

* Open the Role Center.

* On the Application menu , choose Customize, and then Customize This Page.

* In the Available Parts pane, select My Job Queue, and then choose the Add button.

* To move the My Job Queue part below the Report Inbox in the Role Center layout pane, select it and then use the Move buttons

* In the Role Center, go to the My Job Queue part.

We have already done above step in above steps. Let’s continue with next step. If not already done follow the steps as defined above.

* On the Role Center, on the Reports tab, choose Price List.

* In the Sales Type field select Customer.

* To schedule the report to run later, choose the Print button, then select Schedule.

* In the Schedule a Report window, leave the default values in fields and choose the OK button.


* When the error shows up in the My Job Queue part, select the error, and then choose Show Error.

* Read the error message, and then choose the OK button to close it.


* Open the Job Queue Log Entries page.

* Filter the list to display entries whose Status is Error.

* To view the error message, point to the Error message field for the entry, or, select the entry, and then on the Home tab, choose Show Error Message.

* Return to the Role Center, and then run the Price List report again.

* On the report request window, set the Sales Type field to All Customers, choose Print, and then choose Schedule.

* In the Schedule a Report window, leave the default values in fields and choose the OK button.

* In the Report Inbox on the Role Center, view the completed report.