Replication is the process whereby data is copied between databases on the same server or different servers connected by LANs, WANs or the internet.
MSSQL Server repication uses a publisher, distributor and subscriber metaphor.
- Publisher is the server or database that sends its data to another server or database.
- Subscriber is the server or database that receives data from another server or database.
- Distributor is the server that manages the flow of data through the replication system.This server contains the distribution database.
- Publication is a collection of one or more articles that is sent to a subscriber server or database. Publisher contains publication/publications.
- Article is the basic unit of replication and can be a table or a subset of a table.
- Subscription is the group of data that a server or database will receive.
- Push Subscription is subscription when the publishing server will periodically push transactions out to the subscribing server or database.
- Pull Subscription is subscription when the subscribing server will periodically connect to the distribution database and pull information.
- Distribution Database is a system database, which is stored on the Distributor and does not contain any user tables. This database is used to store snapshot jobs and all transactions waiting to be distributed to Subscribers.
Snapshot Replication
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not monitor for updates to the data.When synchronization occurs, the entire snapshot is generated and sent to Subscribers.
Using snapshot replication by itself is most appropriate when one or more of the following is true:
Using snapshot replication by itself is most appropriate when one or more of the following is true:
- Data changes infrequently.
- It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.
- A large volume of changes occurs over a short period of time.
Snapshot replication is most appropriate when data changes are substantial but infrequent.
Step 1:- Configuring Distributor Server
To configure distributor go to server 2 instance replication folder right click on it and select configure distribution option
it opens configure distribution wizard
Check the option - it will act as a own distributor.
SQL Server will create a distribution database and log file. If already a distributor is configured then select the second option add the existing distributor to the wizard.
Select the snapshot folder in the local/shared disk memory address then click on next button
Provide the distribution database name by default distribution, provide file path where data and log file to save in the server location
Provide the publications name with their databases for adding new sql/oracle publication, then select the add button, add the publication
Provide the distribution password which is fulfill the password policies which is used for publication wizard also it should be saved in a file
Generate a script file
Script file location
Complete the wizard
Configuring if any errors then rectified errors
6 jobs are added to the SQL Server Agent at distributor side
Provide the publications name with their databases for adding new sql/oracle publication, then select the add button, add the publication
Provide the distribution password which is fulfill the password policies which is used for publication wizard also it should be saved in a file
Generate a script file
Script file location
Complete the wizard
Configuring if any errors then rectified errors
6 jobs are added to the SQL Server Agent at distributor side
Step 2: Create a Publisher
Go publisher server and expand replication folder, right click on local publication and select the new publication.
Provide the existing distributor as shown in diagram
Provide the administrator password to connect to the distributor which is same as provided at distribution configuration level(password)
Provide the database name in the publication server
Select the type of replication, in this we select the snapshot replication
Provide which article(objects) to the replicated data to the subscriber servers/database
Check create snapshot immediately and don't schedule the snapshot agent it kills the performance issue.
Provide the snapshot security by click on security settings
If you require the script then check the generate a script file with steps to create publication
Script file location
Provide publication name and click on finish button.
If any error appear then rectify and click on close button.
Step 3:- Create a subscriber to the replication
Go to subscriber server/servers we can provide more than one subscribers also, go to subscriber server and expand replication folder, right click on local subscription and select new subscription.
Provide the publication name which is configured
Provide the subscribers (we can provide more than one subscriber also)
Provide the distribution agent security by click on [....]
Provide the synchronization schedules
Initialize the solutions immediately
Provide the actions like generate a script file with steps to create a subscriptions
Script file location
Verify the provided data for subscriber wizard and then click on finish button
If any error appear then rectify then click on close button
2 new jobs are created at distributor server side for push method and also articles in the subscriber servers
Testing:-
Insert/delete/update one record at the publisher then it appears at subscriber level
or
Add one article to the publisher (go to replication folder select the publisher and properties -> articles ->add->ok) then it appears at subscriber servers.
