Advertisements

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.

REPLICATION ENTITIES

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

Publisher

 

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

Article

 

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

Publication

 

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

Distributor

 

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

Subscriber

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

Subscription

 

  • 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 AGENTS

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.

dbo.MSxxx_agents

dbo.MSxxx_history

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.

Advertisements

Appending other information to Navision Reports

There is always requirement from many customers to Append information to the Navision Reports.

Recently one of my reader have requested me for any such functionality which can help him Append information from Word or Excel Files to Navision Reports.

While searching for solution for such requirement i came across this scope if we can do the same from outside Navision integrating the functionality with Navision.

Although i am not sure how much this post will answere to the requirement raised by my this reader but still this can be an work around to this requirement.

In my previous 2 posts i have answered this approach, Let us assemble all of them to provide solution to this requirement.

Please see my privious posts, i will not repeat already covered part in this post. Here is the link to those posts:

Converting Office Files into PDF from Navision

Merging PDF Files in Navision

In this post i will be using these functions discussed in above posts.

The Same Setup will be applicable to this post too.

PDFConvMerge-2-2

Since we are appending our other information to the Navision Report so we will be at First Step saving our Customer Sales Invoice to PDF.

Later some information which need to be appended to this report is in Word File the path of which is stored on Customer Card.

How to work with Converting Office file to PDF is already discussed in previous post Link shared above.

So Now we have Our Report in PDF and Additional information to be appended to Report is also in PDF. Now it will be easy for us to Merge both the File to have single PDF File.

How to Merge these files is also already discussed in my previous post. Link shared above.

Let us see how we can assemble above both functionality to get our required result.

Approach is very simple, i have created a button on Sales Invoice to Print the Report which performs above discussed process.

PDFConvMerge-5

On Sales Invoice Page I have added one Button which calls my Function to Print & Merge the document, it takes Invoice No & Sell-to Customer No. as a parameter.

PDFConvMerge-6

I have created a function to Save the output of Report to PDF, using standard functionality available in Navision.

PDFConvMerge-7

The PrintSalesInvoice function called from Sales Invoice Page with Invoice No & Sell-to Customer No.

This Function calls the SaveReportAsPDF explained above.

Converts the Office file that is attached on Custommer Card, discussed in previous post Link provided above.

Calls the MergePDFFiles, discussed in previous post Link provided above.

So Now at single click of Button entire task is done for us.

To keep it simple no additional checks and other routine tasks have been included like after task is completed clean up of all temporary files and moving the Result file to desired Location etc.

Now you know the logic so you can tweak the code accordingly to get the functionality working as per your requirement.

Thats all for this post. I will come up with more information in my up coming posts.

You keep you requirements posting to me, i will try my best to answer your queries in form of my future posts, at earliest possible from my end.

Till then kee exploring & Learning.

Merging PDF Files in Navision

Many of time we have multiple files in pdf but for some certain reasons we require to merge them to single File.

Continuing from my previous post i am extending my functionality with Merging of Mulitiple pdf files to single pdf file.

PDFConvMerge-2-2

In my Setup Page created in previous post i have added one more field to store the Tool Path & File Name for Merging multiple files.

Based on the dll provided by http://www.pdfsharp.net this post is written.

Visit the Site download the utility and extract the dll file and exe file from the zip and store in same Folder.

The format of command to merge the files at command prompt is similar to:

Command to merge :- pdfmerge output=.\ActionFolder\result.pdf input=.\ActionFolder\1.pdf,.\ActionFolder\2.pdf,.\ActionFolder\3.pdf

We will prepare the Function which will take as parameter the Output & Input File and run the utility to Merge these files to single file using WsShell in Navision.

PDFConvMerge-4

Function is very simple to accept the Input & Output File Names and Prepare Single PDF file from multiple pdf files provided as input (comma seperated List of Files) to the function.

To keep it simple no additional codes for test and other routine job is not added. Now you are familiar with the functionality you can tweak the code accordingly to meet all other requirements.

Thats all for this post. Will comeup with more Information in my upcoming posts.

Till then keep exploring and learning.

 

 

Important

Most of the contents you find in this blog will be either inherited from MSDN or Navision Developer IT Pro Help. Some places images are also directly taken from these sites. Purpose is simple to try those stuffs and re-produce adding few things as per my understanding to make easy understanding for others and quick reference.

Here nothing under my own brand or authorship of the content. At any point of time we are just promoting Microsoft stuffs nothing personnel with same.

Hope stuffs used here will not violate any copyright agreement with them. In case by mistake or in-intestinally it happens and the Microsoft feels these should not be used Microsoft have full right to inform me about same and will be glad to take down any such content which may be violating the norms.

Purpose is to promote Navision and share with community.

FB Profile

Like FB Page

%d bloggers like this: