Replication In SQL Server: Part Three

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