Replication In SQL Server: Part Three

Overview:

Earlier, in previous parts we saw two types of replication in snapshot.

• Replication in SQL Server: Part One
• Replication in SQL Server: Part Two

Here in part three we will be covering transactional replication.

Types of SQL Server Replication:

• Snapshot Replication. (Explained in Part Two)
• Transactional Replication.(Part Three)
• Merge Replication.
• Peer-to-Peer Replication.

Let’s see each type by an example so we will get a clear gist what each and every replication is doing, starting with Transactional Replication.

Transactional Replication

• Let’s start with transactional replication. The term transactional replication copies data from source destination to target destination. Here the logs are transferred from source database to destination database. I.e. publisher to various subscribers.
  
  
• If a change is made to the source database, that change can be synched to the target database immediately, or the synchronization can be scheduled.
  
  
• In transactional replication, log reader scans the publication database and checks for each committed rows, if any changes in replicated areas the changes are done in distributed database respectively. The distributor agent then replicates those changes to the subscriber . (source: Microsoft TechNet).
  
  
  

Transactional Replication Configuration

As you know, replication architecture has three components: Publisher, Distributor and Subscriber that we need to configure.

• Scenario
  
  In a retail environment, management wants to know how many items are sold hourly. To avoid impacting the Sales database, they process reports and statistics on another server.
  
  
• Architecture
  
  

Let’s Start:

Logically it is advisable to take three instances. Here I am explaining transactional replication local server (SQL).

NOTE

To Configure and to check on your local PC you need to install SQL Server (2008, 2008R2, 2012, 2014 and so on). Express Edition will have less features in local PC.

• Open SQL Server Management Studio
  
  
  
  
• Click on Replication,
  
   
  
  
• Configure Distribution
  
  
  
  
• Click on Configure Distribution -> Next ->
  
  
  
  Select the Server or PC Hostname it will act as its own distributor and click next.
  
  
• Path Selection
  
  
  
  Next
  
  
• Configure Database Name (Distributor)
  
  
  
  In this Case I had kept by default databasename as distributor .Click Next
  
  
• Configuring Instances
  
  
  
  If you are running on multiple instances you can do it by clicking on ADD.
  
  Here, in this case using everything in local Server. Hence the checkbox is checked. Click Next and check Configure distribution Option -> Finish
  
  
• Executing
  
  
  
  
• Click on Replication ->Distributor Properties
  
  
  
  
  
  Now here you will see transaction Retention are removed after 72 hours and agent history is removed after 48 hours. On Click of tab you will able to see the complete log path respectively .
  
  
  
  
• Go to Jobs
  
  
  
  Here you will see the highlighted section you will see new jobs got created.
  
  
• Creating Snapshot Publisher.
  
  Click on Replication ->Local Publication ->New publication
  
  
  
  It prompts you to select database which you want to make as a publisher.
  
  Here in this scenario, selecting bloodbankmanagement database.
  
  Click Next
  
  
  
  In this case we need to select Transactional Replication->Next,
  
  
  
  Select tables, stored procedures, and user defined functions and on...
  
  In this case, selected tables ->Next ->
  
  
• Creating Snapshot and scheduler
  
  
  
  
  
  Click on create Snapshot immediately ->Click security Settings.
  
  
  
  Use SQL server Authentication.>OK -> Next ->Create publication ->Give publication name as “”TEST” -> Finish
  
  
  
  
  
  You will see TEST publisher in Local Publications.
  
  
  
  You will see job as TEST appearing in SQL Server Jobs.
  
  Creating Subscription:
  
  
  
  Click Next,
  
  
  
  Click Next ->
  
  
  
  Select subscriber database will be selection simple blog database->Next,
  
  
  
  Configure Security setting for subscriber,
  
  
  
  Click Ok ->Next,
  
  
  
  Now, it’s asking agent to run continuously, run on demand or schedule it. This depends on your business requirements how you want your data. Here am continuing with default selection run continuously. ->click Next ->Run immediately ->Create the subscription -> Finish,
  
  
  
  
  
  See the Local Subscriber Simple Blog Subscriber appeared.
  
  
  
  Job created in SQL server Jobs ->Right Click ->Start Job as Step.
  
  
  
  After Successful completion of job. You will able to see tables of Blood bank management database which we selected appeared in Simple blog.
  
  
  

Conclusion

Transactional Replication is suitable for small databases where replication is needed. For huge database (TB) on server make sure you have plenty of resources available for replication (Transactional) running efficiently.