Replication in SQL Server: Part Two

• 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 database name 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, then click 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
  
  
• Select Replication Type.
  
  
  
  In this case we need to select Snapshot Replication, then click Next.
  
  
  
  Select tables, storedprocedures, 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 running 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 appear in Simple blog



Conclusion

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

Refer: Replication In SQL Server: Part One.