Create Publication SQL 2005

Back Home Up Next


These screenshots demonstrate creation of a Snapshot Publication in SQL Server 2005.

Step 1 - New Publication

Step 2 - New Publication Wizard, Welcome

Step 3 - Distributor

Step 4 - Snapshot Folder

Step 5 - Publication Database

Step 6 - Publication Type

Transactional Publications...
 require a Primary Key on the article. Not even a Unique Key is sufficient!

Transactional Publications with Updateable Subscriptions...
 also require a Primary Key on the article, and once again not even a Unique Key is sufficient! In addition a column is automatically added with "uniqueidentifier" data type.

When a Subscription is created on this Publication type one of two replication change options are available, "Simultaneously Commit Changes", or, "Queue Changes and Commit When Possible". This also requires creating a Linked Server definition.

Snapshot Publications...[no notes as of yet]

Merge Publications...
 automatically have a column of "uniqueidentifier" data type added to the Publication. They have a default Agent Schedule of 14 days!

With Merge Subscriptions the available synchronization schedules are: "Run Continuously", "Run On Demand Only" (which is the default), and "Define Schedule...".

Merge Subscriptions also require a Conflict Resolution setting, which is either "Client" (the default), or "Server".  The Client setting result is "First to Publisher" wins, the Server setting is assigned a priority value of "75.00".

SQL Mobile Edition only supports Merge Replication.

Article Selection Dialogs

Step 7A - Article Selection

All five of the above Article types are available for Snapshot Publications.

Only Table Articles are available for either of the two Transactional Publication types.

Only Table & Stored Procedure Article types are available for Merge Replication Publications.

Only SQL Enterprise Edition makes Indexed View articles available.

If you select an article that references an underlying base table (i.e., Function, Stored Procedure) the base table must be included in the article selection also.

Step 7B - Article Selection, Tables & Columns

Step 7C - Article Selection, Stored Procedures

Step 7D - Article Selection, Views

 

Step 7E - Article Selection, User-Defined Functions & Indexed Views

 
I consolidated the Article Properties Dialog Boxes
 

Step 8A - Article Properties Button, Table Articles

Step 8B - Article Properties Button,
Stored Procedures

Step 8C - Article Properties,
View Articles

Step 8D - Article Properties,
Indexed Views Articles

Step 8E - Article Properties, User-Defined Functions

Step 9 - Filter Table Rows

Step 9B - Filter Table Rows,
Add Button

Step 10 - Snapshot Agent

Step 10B - Snapshot Agent,
Schedule Button

Step 11 - Agent Security

Step 12 - Agent Security,
Security Settings Button

Step 13 - Wizard Actions

Step 14 - Script File Properties

Step 15 - Complete the Wizard

Step 16 - Creating the Publication