SQL Server Replication (Part 6) : Monitoring Replication

We can monitoring Replication by Launch Replication Monitor

We can ensure, agents and replication running well :)

SQL Server Replication (Part 5) : Create Subscription

After Publication has been created successfully, next we will create Subscriptions

1. Expand Folder Replication, Expand folder Local Publications, Expand PUBLICATION_VMJ (previously created), right click : New Subscription

2. Click Next

3. Choose the publication, click next

4. Choose "Run all agents at the distributor", click next

5. Click Button Add subscriber, choose Add SQL Server Subscriber

6. Specify login SQL Server on Server Backup, (make sure, you enter hostname, if IP Address, we will get error)

7. On server backup, choose new database, click next

8. Enter Database name, specify database files, click OK

9. After DB name has been specified, click next

10, click button [. . .] to specify agent security

11. Choose Run continously, click next

12. Leave as default, then click next

13. Check create the subscription(s) only, click next

14. Click Finish

15. Subscription has been created

SQL Server Replication (Part 4) : Create Publication

Let's Open SQL Server Management Studio on Server Database Primary, here step by step to create Publication :

1. Expand Folder Replication, right click New Publication

2. Click Next  

3. Choose the database, click next

4. Choose Publication Type : Transactional Publication

5. Choose objects , then click next

6. If we want to filter table rows, clik button add, if don't need, click next

7. Check first option : create a snapshot immediately , click next

8. On Agent Security, click button security settings

9. On Domain or Machine account under which the Snapshot Agent process will run, choose : "Run under the SQL Server Agent service account", on connect Publisher, specify SQL Server Login, click OK

10. Now we can see "SQL Server Agent account" on Snapshot Agent and on Log Reader Agent

11. Check only "Create the publication" and click OK

12. Specify Publication name, then click finish

13. Creating Publication success

SQL Server Replication (Part 3) : Configure Permission Folder

After distribution has been configured, then we need to setup permission folder...

Go to path : C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL right click Folder ReplData, click properties, open tab security.

We find path C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\RepData from snapshot folder which has been specified on previous part (configure distribution)

1. Click on SQLServerMSSQLUser, then click edit

 

2. After click edit, then click add

3. Click Advanced

4. Click Object Types, we need only search groups

5. Check Group only, then click OK

6. Click Find Now, then choose SQLServerSQLAgentUser, click OK

7.  Click OK

8. Give Full Access for SQLServerSQLAgentUser

 

After this part, we go ahead to part 4 : Create Publication

SQL Server Replication (Part 2) : Configure Distribution

On this part, we will configure distribution, Open SQL Server Management Studio,

1. Right Click Folder Replication, click Configure Distribution

2. Click Next

3. Choose option first : <hostname> will act its own Distributor, SQL Server will create a distribution database and log, click Next

 4. Browse Location for Snapshot Folder, then click next

5. Specify Distribution Database Name, location log & Database file

6. We will our server will be act as publisher, click next

7. Check configure Distribution,  click next

8.  Verify the result, if our choices already OK, then we can clik Finish

9. Configuration Distribution Success

SQL Server Replication (Part 1) : Preparation

I have 2 servers : (already installed SQL Server 2008R2 Machine on Windows Server 2008R2)

Server Primary : IP 10.10.10.21 hostname WIN-92JP95SLO0R

Server Backup : IP 10.10.10.12 hostname WIN-FRHI57MGALL

On this part 1, before setup replication on SQL Server Management Studio, we need preparation :

1. Config hosts file on path

C:\Windows\System32\drivers\etc\hosts, append this line on both servers

10.10.10.21        WIN-92JP95SLO0R
10.10.10.12        WIN-FRHI57MGALL

Why we need do this ? Because when create subscriber, we need to connect SQL Server Machine by hostname, not IP, when we input IP Address, we will get error message like this : http://willyristanto.blogspot.com/2015/04/verfiy-that-instance-name-is-correct.html

2. Setup SQL Server Agent to start automatically

Open services.msc, right click on SQL Server Agent, click properties

Choose Startup Type : Automatic, then click start

Okay, part 1 finish, then we go to part 2