REPLICATION

(Home)

Without Identity keys

Publication Box

Expand the Replication Folder on the Server that the database lies.
Right Click Publications  - Select new publication.

Welcome to the Wizard. Do not select advanced option! 

Select next

Now select a distribution server.

Usually you take the default on this one.

Select next

Selected by default - Yes, Configure the SQL Server Agent service to start automatically.

Select next

Specify Snapshot Folder

Take default if possible, Otherwise point to your folder.

Subscriber Box

Now Go to Subscribing box (usually a server). Open Enterprise manager.

Right Click Replication - Select Pull Subscription.

Find  and select the Publishing server. 

Select the publication from that server you would like to subscribe too.

Select next

Select transaction type - There are 3 of them

Under Replication Monitor

Now go back to the Publication Box and Reintialize the publication or all publications!

Now Start the snap shot by selecting start agent!

If you have selected Transactional this is as far as you need to go on this box.

Back to the Sub

With Identity Keys

Concept

From aBox to bBox
One way replication!
aBox is a parent PDC(primary domain controller/highest tree in the forest)
bBox is a member server(automatically creates a trust relationship)
aBox is both publisher and distributor of publication!
bBox is subscriber!


Work Area

(All of this can be done from within enterprise manager 
create two registered connections aBox and bBox.)


Create a Trust

Typically you can not replicate between a local SQL Server and a named SQL Server!
Both must be named SQL Servers!

Create a trust between two boxes!


Logins

aBox

Make Sure that on aBox that a valid sql server login
login exists!

Permissions for aBox:
SQL Server System Administrator
On DB of your choice +

SQL Server

Synchronize the logins from within the Services on bBox
Chose MSSQLSERVER
Chose MSSQLSERVER AGENT

Rt Click Service(MSSQLSERVER)
Select properties/logon
Goto Logon tab and synchronize id’s and passwords!


Distribution Agent



Scripting Publishing Database

Generate TABLE script by going to Publishing Database!
Right click 
Menu: All Tasks
Menu: Generate SQL Scripts

Select Show all Button
Select All tables check box

Select Options Tab
Under Table Scripting Options Select
Script Indexes
Script Triggers
Script Primary Key, Foreign Keys, defaults, …


Before running store procedure below!
Increase query Analyzer buffer size!
Goto
Menu: Options
Select results tab
Change from default 256 to 5000(by chr)


Run
sp_scriptpublicationscustomprocs …

sp_scriptpublicationscustomprocs ‘MetData30’

This stored procedure will generate all of the scripts associated with a database!


Publication

Create a publication from aBox.
Menu: New Publication
Select Advanced Options!

Subscription

Create a pull subscription from bBox.
Menu: New Subscription
No need to select advanced options here!


Executing Environment

Intialize All Subscriptions
This creates a snap shot

You could also go to publication!
You’ll see 3 rows of information!

Rt Click on Snapshot!
Start Agent


This creates a SNAPSHOT


Start Synchronize (Click on 3rd row)

Case Resolution: - Changed the SQL Server agent startup account on the Subscriber to a Domain account that is setup as a SQL Sysadmin account - Setup the same account as a sysadmin on the Publisher - Shared the Replication folder and set this account with full control - Changed the Job owner for the Distribution Agent Job to this account

Additional Information: Detailed steps to resolve the issue: - Transactional Replication with a Pull Subscription - Hard Drive on the Subscriber crashed so Subscriber SQL Server was re-installed. - On the Subscriber Setup a login for "NMAR\RepAxxxx" and set this to sysadmin role - Change the SQL Server Agent Startup account on the subscriber to "NMAR\RepAxxxx" - Stopped and re-started the SQL Agent -

On the Publihser Setup a login for "NMAR\RepAxxxx" and set this to sysadmin role - Deleted the Subscription from the Publisher - Subscription still on the Subscriber so ran sp_removedbreplication to remove the subscription from the subscriber - Re- Created the Pull Subscription - Distribution agent giving the error "login failed for user nmar\administrator" -

On the subscriber went to properties for the Subscription and selected the Synchronization tab. Selected Distribution Agent properties and changed the owner from "nmar\administrator" to "nmar\repaxxxx" -

Now distribution agent runs and then stops - Customer is using a folder different than default for the snapshot and has specified drive letter - Created a share, on the Publisher, for the snapshot folder "\\nmarserver\repldata" and gave "NMAR\RepAxxxx" Full control on this - Changed the snapshot folder for the publication to the Share as following:

1. tools-->Replication-->"Configure Publishing, Subscribers..."

2. Select the Publication Tab and select the Publication and then select the elipses button

3. Did the same from the Publication Properties - Now the Distrib agent was failing with error "Incorrect path c:\repldata\unc" - Re-Initialized the Subscription but still the same error - Removed the Subscription from the Subscriber. Re- created it. In the snapshot delivery dialog changed the Snapshot folder - Replication worked without any problems - Agents Folder and Publications folders on the Publisher still had Red x's but none of the agents did - Re-Started SQL Server and the x's went away