Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Setting up Transactional Replication

Posted by jeffprom on August 22, 2008

While this method does seem to have some security concerns, it should get you up and running. This was written to be used with SQL 2005 and Windows Server 2003.

From the remote server:
Configure Server policy and permissions:
1. Share a folder. In our case we’ll call it E:\Replicator (under Sharing, Permissions)(also under Security tab)
add anonymous and everyone, and give full control
2. In management studio right click your DB to replicate, properties, permissions, sa (or whatever your sysadmin account is)
set connect, and connect replication = Grant
3. From windows, Start, Run, gpedit.msc, Windows Settings, Security Settings, Local Policies, Security Options
Network access: Shares that can be access anonymously (add the word Replicator at the end)
Network access: Restric anonymous access to Named Pipes and Shares: Disabled

Configure Distribution:
1. Replication -> Local Publications
right click, configure distribution
2. server will act as it’s own distributor
3. snapshot folder = \\serverIPAddress\Replicator (use the UNC and NOT E:\Replicator)
4. Distribution database name: distribution
keep default locations
5. Next, next, finish

Configure Publication:
1. New Publication
2. DBname
3. Transactional Replication
4. Pick all tables and stored procedures if you like.
5. skip filter. next
6. create snapshot immediately (only need to do a snapshot initially)
7. snapshot agent security settings:
Run under the SQL Server Agent service account
By impersonating the process account
8. Next
9. Create the publication, Next
10. publication name: someUniqueName, Finish

From the main server:
Configure Subscription:
1. New Subscription
2. Publisher, Find SQL Server Publisher, choose remote server and login as sa
3. select publictation, next
4. Run each agent at its Subscriber (pull subscriptions)
5. Subscriber (main server)
6. Subscription Database = NEW DATABASE (not default!!!)
Database Name: someUniqueName
7. OK, next
8. Click the … on the far right to set security
Run under the SQL Server Agent service account.
Using the following SQL Server login: sa account
9. Agent Schedule: Define schedule (in our case we set it up for daily, every 5 minutes)
10. Initialize Immediately
11. Create the subscription
12. Go to your jobs and find the new scheduled job. you can run the job and view history to see if it’s working.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: