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