Lesson 3: Publishing
This lesson discusses publishing tasks, as well as aspects of publishing that are specific to each replication type. Initial synchronization is necessary whenever a new Subscriber subscribes to a publication.
After this lesson, you will be able to
- Create publications
- Configure initial synchronization
Estimated lesson time: 50 minutes
Creating Publications
After you have configured your servers, you can create publications. You will use the Create Publication Wizard to create new publications. SQL Server Enterprise Manager provides several ways to launch the wizard: with the Tools/Wizards menu, with the Tools/Replication/Create and Manage Publications menu, from the console tree, or from the Replicate Data taskpad. You can create one or more publications from each user database on a Publisher.
Defining a Publication
A publication contains one or more articles. When you create a publication, you can specify
- Whether the publication is a snapshot, transactional, or merge publication.
- Whether all Subscribers will be SQL Servers.
- Snapshot requirements, such as when to schedule the Snapshot Agent or whether to maintain a publication snapshot at the Distributor.
- The tables or stored procedures that will be the articles in the publication. You can also set options such as filters on these articles.
- Whether to allow anonymous or updating Subscribers or pull subscriptions.
- Publications that share an agent. The default is that each publication has its own agent.
NOTE
You cannot create transactional publications on the Desktop Edition of SQL Server. Using the Desktop Edition, you can subscribe to transactional publications on another server and you can create and subscribe to snapshot and merge publications.
IMPORTANT
To maintain referential integrity between published tables, include all tables that are involved in the relationship within one publication. This guarantees that transactions are applied at the same time and that integrity is maintained.
IMPORTANT
You can add new articles and change a number of other publication properties after creating the publication, but you have to remove all Subscribers before making changes. For this reason, you should carefully plan your publications before adding Subscribers.
Specifying Articles
As shown in Figure 16.1, the Specify Articles screen of the Create Publication Wizard lists the tables and, for snapshot and transactional publications, the stored procedures in the database. To publish a table or stored procedure, check it in the list. To change the properties of an article, click the ellipsis button (…) next to the article.
Figure 16.1 The Specify Articles screen in the Create Publication Wizard
Tables published as articles in merge publications cannot have a timestamp column. If a table has a timestamp column, it will have a clock icon in place of the check box in the list of tables in the Create Publication Wizard, as shown in Figure 16.2. If you need to publish the table using merge replication, drop the timestamp column, using the ALTER TABLE statement, before you create the publication.
Figure 16.2 The Specify Articles screen, showing a table with a timestamp column
Creating Filters
You can define an article that is a subset of a table by creating filters or scripts that specify the columns or rows to be included. You can define a subset of data vertically, horizontally, or as a combination of the two:
- To create a vertical filter, select only the columns that you want to replicate. Merge replication does not allow vertical filters. The replicated columns must contain the primary key, unless you are using snapshot replication.
- To create a horizontal filter, use a WHERE clause to restrict the set of rows to be replicated.
Merge replication supports dynamic filters that allow each Subscriber to receive different replicated data from the same publication.
Using the Immediate Updating Subscribers Option
Setting the Immediate Updating Subscribers option when you create a publication allows a Subscriber to update a copy of the local data, provided that the changes can automatically be reflected on the Publisher by using a distributed transaction. The Publisher then sends changes to all other Subscribers. This option is not available for merge replication.
You must include a timestamp column in tables that will be updated. When a user attempts to update a row on the Subscriber, SQL Server uses the timestamp column to determine whether any updates have been made at the Publisher. If no timestamp column exists in the table, the Create Publication Wizard will add one.
Publishing Considerations
This section describes several publishing considerations and restrictions in SQL Server 7.
Publishing Restrictions
Certain restrictions exist on replication publishing in SQL Server:
- Publications cannot span databases; each publication can contain articles from only one database.
- It is not possible to replicate the model, tempdb, msdb, master, or distribution databases, because these are system databases.
- A table should have a primary key to identify a row and ensure entity integrity, except in snapshot replication.
Limited Support for Nonlogged Operations
Limited support exists for nonlogged operations on text, ntext, and image data types, as the following table indicates. Changes made to columns with these data types are not logged if the changes are made using the WRITETEXT or UPDATETEXT operations. Do not use these operations if you want changes to be replicated in all cases.
| Type of replication | Support for nonlogged changes |
|---|---|
| Snapshot | Changes are detected and replicated |
| Transactional | Log Reader Agent does not detect changes |
| Merge | Triggers do not detect changes |
Due to the potentially large size of this data, the max text repl size server configuration option specifies the maximum size of text and image data change that may be replicated. The default is 64 KB.
Ensuring Unique Identities in Merge Replication
It is essential to uniquely identify rows in merge replication. A table published for merge replication must have a column that uses the uniqueidentifier data type and the ROWGUIDCOL property. Globally unique values can be generated for the column by setting its default to the NEWID function. When SQL Server finds a column with this property, it automatically uses the column as the row identifier for the replicated table.
If there is no column with the ROWGUIDCOL property, SQL Server adds one to the base table.
Columns with the uniqueidentifier data type hold a 128-bit globally unique identifier (GUID) that is generated by the NEWID function. These values are guaranteed to be globally unique, so no two rows will ever have the same GUID. Here is an example of a GUID:
6F9619FF-8B86-D011-B42D-00C04FC964FF |
Initial Synchronization
Before a new Subscriber can receive a publication, the source and destination tables should contain the same schema and data. The synchronization process that accomplishes this is called the initial snapshot, and the Publisher initiates the process.
All articles within a publication are synchronized initially as a single logical unit. This helps to maintain integrity relationships that originate from the underlying tables.
The Snapshot Agent creates different scripts depending on the type of replication. The scripts are stored in the distribution working folder, and the status of the synchronization jobs are stored in the distribution database. The different scripts contain
- Schema definitions
- The bulk copy program (bcp) output file(s) of the data to be replicated
- Index definitions
- Additional script files for merge replication
The synchronization process uses the bcp utility in either native (SQL Server) or character format. Native format is smaller and faster but cannot be used by non_SQL Server Subscribers. When creating the publication, you can specify whether all Subscribers will be SQL Servers. This determines which bcp format will be used. You can modify the format later by editing the publication properties.
Synchronization Frequency
For new Subscribers only, you can schedule when the initial synchronization files are created. After initial synchronization, a Subscriber does not have to be reinitialized unless a problem occurs.
You have the option to bypass the initial synchronization. This is useful when you have employed other means to do an initial synchronization or when you want a slightly different schema at your Publisher than at your Subscriber. For example, if you are publishing a 20-GB database, you can perform the synchronization manually using a tape backup and restore, meaning that the 20 GB of data will not have to be transferred across the network between the Publisher and the Subscriber. However, if you elect to bypass the initial synchronization, you must complete the step manually. Depending on the type of replication you are using, this can include performing such tasks as adding timestamp and uniqueidentifier columns.
Editing Publication Properties
To edit the properties of a publication, select Tools/Replication/Create And Manage Publications, navigate to the publication, and click Properties & Subscriptions. Alternatively, navigate to the publication in the console tree in the Publications node below the published database, right-click the publication, and select Properties. This opens the publication Properties dialog box, which allows you to define the following:
- Articles and filters
- Subscription properties
- Subscription options:
- Whether to allow pull and anonymous subscriptions
- Whether to allow snapshot downloads using FTP
- Whether to report merge conflicts at the Publisher (centralized conflict reporting) or at the Subscribers (decentralized conflict reporting)
- The publication access list
- Service and agent status
- Scripts that are used to create and delete the publication
IMPORTANT
Before making changes to the properties of a publication, it is necessary to remove all subscriptions to the publication. Do this from the Subscriptions tab of the publication Properties dialog box. After removing all Subscribers, you must close the publication Properties dialog box and either reopen it (as prompted) or refresh your console tree view (right-click your server and click Refresh) and then reopen the publication Properties dialog box to make your changes.
Agents Created for a Publication
When you create a publication, one or more agents are created to support it, as shown in the following table. You can see information about these agents in the Replication Monitor after creating a publication.
| Type of replication | Agents created |
|---|---|
| Snapshot | Agent for initial synchronization and ongoing replication |
| Transactional | Snapshot Agent for initial synchronization and Log Reader Agent for ongoing replication |
| Merge | Snapshot Agent for initial synchronization |
Exercise: Creating a Merge Publication
In this exercise, you will create a merge publication.
- To create a merge publication on the Publisher
- In the console tree, click your server.
- On the Tools menu, point to Replication, and then click Create And Manage Publications.
- Verify that StudyNwind is selected, and then click Create Publication.
- Use the information in the next table to create a new publication.
- Expand Replication Monitor, expand Publishers, and expand your server. If StudyNwind_Merge_Publication is not listed, right-click your server name and then click Refresh.
- Click the StudyNwind_Merge_Publication.
- In the details pane, right-click the Snapshot Agent and click Start. The agent will run for a few minutes, generating the initial snapshot of the new publication. Wait until the Last Action column in the details pane displays the following message: "A snapshot of 6 articles was generated."
Note that this time the Create Publishing Wizard does not prompt you for the information needed to enable publishing and distribution; rather, you immediately begin defining the new publication.
| Option | Value |
|---|---|
| No, I will define the articles and properties | Selected |
| Publication Type | Merge publication |
| All Subscribers will be servers running SQL Server | Selected |
| Specify articles | Check dbo.Customers, dbo. Employees, dbo.Order Details, dbo.Orders, dbo.Products and dbo.Shippers |
| When prompted to add an indexed uniqueidentifier column | Click OK |
| Publication name | StudyNwind_Merge_Publication |
| No, create a publication without data filters and with the following properties | Selected |
Note that you are warned that the published data references data not being published. This is intentional for the exercise. When publishing live data, you should be careful to include all data that will be needed on the Subscribers. The Create Publication Wizard makes this easier for you by issuing this warning.
Exercise: Reviewing the Snapshot Agent Job History
In this exercise, you will open the Replication Monitor and review the history of the creation of the snapshot files.
- To review the Snapshot Agent job history
- When the snapshot is complete, in the details pane, right-click Snapshot, and then click Agent History.
- Click Session Details.
- Open Windows Explorer, and review the directories and files that were created under the C:\Mssql7\Repldata\Unc folder.
Review the session details associated with the creation of the snapshot.
Notice that these are the same files that the Agent History indicated were created.
Lesson Summary
All data that is to be replicated must be defined as part of a publication on the Publisher. Each table or stored procedure that is to be replicated is defined as an article in a publication. Subscribers subscribe to an entire publication. When you create a new publication, a snapshot is created for the publication. This snapshot is used to perform an initial synchronization when Subscribers subscribe to the publication. You can specify various properties for publications, such as whether Subscribers will be able to create anonymous subscriptions and whether FTP can be used to download the initial snapshot.
