After you have configured a distributor and enabled publishers, publication databases, and subscribers, you can create publications. You will manage the publications you create as you would any other SQL Server resource.
The easiest way to create publications is by using SQL Server Management Studio. To do this, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Right-click the Local Publications folder, and then select New Publication. This starts the New Publication Wizard. Click Next to skip the Welcome screen.
Choose the database on the selected server that contains the data or objects you want to publish. You can select user databases only. Click Next.
Choose the type of replication you want to use for the publication. The options are:
Snapshot publication. Creates a publication setup for snapshot replication.
Transactional publication. Creates a publication setup for transactional replication.
Transactional publication with updatable subscriptions. Creates a publication setup for transactional replication with subscriptions that can be updated.
Merge publication. Creates a publication setup for merge replication.
If you are creating a snapshot or transactional publication, continue using the steps listed in the following subsection, "Snapshot and Transactional Publications."
If you are creating a merge publication, continue using the steps listed in the subsection titled "Merge Publications" later in this chapter.
Snapshot and transactional publications are the most commonly used types of publications. With snapshot publications, the publisher periodically replaces subscriber data with an updated snapshot. With transactional publications, the publisher updates data and changes are sent to subscribers through transactions.
After you have started a new publication, as discussed in the subsection titled "Creating Publications" earlier in this chapter, you can create a snapshot or transactional publication by completing the following steps:
Click Next to continue. On the Articles page, shown in Figure 12-7, select the objects for replication. The Objects To Publish pane shows the types of objects that are available for replication. Click the plus sign (+) next to the object to see a list of available objects of the specified type.
To select all the columns in a table or view, select the table or view name entry. Which objects are available depends on the types of objects in the database and may include tables, stored procedures, user-defined functions, and views. Tables without primary keys cannot be published for transactional replication, and you will see a key surrounded by a red circle with a line through it in the Specify Articles dialog box. Additionally, tables referenced by views are required.
To select individual columns in a table or view, expand the table or view entry, and then select the appropriate entries for the columns to include. If you are using transactional replication and selecting individual columns, keep in mind that primary key columns are required and must be published. Primary key columns are indicated by a key with a green asterisk. By clearing a column check box, you exclude the related column from replication (which was previously referred to as vertically filtering a table).
Default properties are set for each selected object (article), including the destination object name and object owner, action to use if the object exists, and if user triggers and extended properties should be copied. You can manage these default properties by setting global defaults, by setting defaults for an individual article, or both:
To set defaults for an individual article, select it under Objects To Publish, click Article Properties, and then select Set Properties Of Highlighted... Article. Refer to the subsection titled "Setting Publication Properties" later in this chapter for details.
To set defaults for all articles of a particular type, select the object type under Objects To Publish, click Article Properties, and then select Set Properties Of All... Articles. Refer to the subsection titled "Setting Publication Properties" later in this chapter for details.
After you select objects to use in the publication, click Next. If there are any issues that require changes to the publication, you will see a prompt similar to the one shown in Figure 12-8. Read the description carefully to determine how to resolve the issue and make changes as necessary. Some important issues that may be described in a prompt include the following:
Tables referenced by views are required and so are objects referenced by stored procedures. If you do not select referenced tables or objects, you must create them manually at the subscriber.
SQL Server adds uniqueidentifier columns to any tables you have selected for replication. Adding a uniqueidentifier column will cause INSERT statements without column lists to fail and will increase the time needed to generate the first snapshot.
IDENTITY columns require the NOT FOR REPLICATION option. If a published IDENTITY column does not use this option, INSERT commands may not replicate properly.
Click Next. Use the Filter Table Rows page to exclude unwanted rows from published tables. As you define filters, they are added to the Filtered Tables list. Selecting a filter shows the related WHERE clause in the Filter box. If you want to define a new filter, click Add to display the Add Filter dialog box shown in Figure 12-9. By default, all rows are published. To change this behavior, select a table to filter, and then create a filter statement that identifies which rows subscribers will receive. Enter a WHERE clause for the corresponding SELECT published_columns FROM TableName statement.
Click Next. The Snapshot Agent initializes subscriptions by creating a snapshot of publication schema and data that can be pushed to subscribers or pulled by subscribers. You can create a snapshot immediately by selecting Create A Snapshot Immediately. If you want the Snapshot Agent to create snapshots periodically, select Schedule The Snapshot Agent To Run.... By default, snapshots are made once a day. To change this schedule, click Change, and then set a new schedule.
Click Next. Set a login for each agent used in replication, which can include the Snapshot, Log Reader, and Queue Reader Agents. To configure the login for the Snapshot Agent, click the related Security Settings button, and then:
Specify the Windows account under which the agent runs at the Distributor. This account is referred to as the process account. The account must be a member of the db_owner fixed database role in the distribution database and must have write permissions on the snapshot share. Be sure to type domain account names in the form domainaccount, such as cpandlsqlserver. Then type and confirm the account password.
Specify whether the agent should make connections to the publisher by impersonating the account specified in the Process Account text box or by using a SQL Server account. If you choose to use a SQL Server account, enter a SQL Server login and password. Typically, you will want to impersonate the Windows account rather than use a SQL Server account.
The Log Reader Agent is used with updatable and nonupdatable transactional publications. By default, the Log Reader Agent uses the same login as the Snapshot Agent. To specify separate security settings for the Log Reader Agent, clear the Use The Security Settings From The Snapshot Agent check box, and then click the related Security Settings button. You can then specify the process account and how connections to the publisher are made.
The Queue Reader Agent is used with updatable transactional publications. By default, the Queue Reader Agent has a separate security context from the other agents. To configure security, click the related Security Settings button, and then specify the process account.
Click Next. Choose the wizard action upon completion. By default, the wizard creates the publication. You can also generate a script file with the steps to create the publication. If you want to only generate a script, clear Create The Publication. Click Next.
On the Complete The Wizard page, type a name for the publication, and then click Finish. You will see a dialog box that shows the progress of the creation process. If errors occur, you must resolve any problems before you can continue, or else you must restart the publication definition process.
Once you have started a new publication, as discussed in the subsection titled "Creating Publications" earlier in the chapter, you can create a merge publication by completing the following steps:
Click Next to continue. Select the types of subscribers that will subscribe to the publication. You can choose any or all of the following:
SQL Server 2005. With this type of subscriber, snapshots are formatted using Native SQL Server format.
SQL Server 2005 Mobile Edition. With this type of subscriber, snapshots are formatted using Character format.
SQL Server 2000. With this type of subscriber, logical records, replication of DDL changes, and some filter optimizations are not supported.
SQL Server for Windows CE. With this type of subscriber, snapshots are formatted using Character format. Additionally, logical records, replication of DDL changes, and some filter optimizations are not supported.
Click Next to continue. On the Articles page, shown previously in Figure 12-7, select the objects for replication. The Objects To Publish pane shows the types of objects that are available for replication. Click the plus sign (+) next to the object to see a list of available objects of the specified type.
To select all the columns in a table or view, select the table or view name entry. The objects available depend on the types of objects in the database; they can be tables, stored procedures, user-defined functions, and views. Tables referenced by views are required.
To select individual columns in a table or view, expand the table or view entry, and then select the appropriate entries for the columns to include. If you are selecting individual columns, keep in mind that both primary key and Rowguid columns are required and must be published. Primary key columns are indicated by a key and a green asterisk. Rowguid columns are indicated by a green asterisk. By clearing a column check box, you exclude the related column from replication (which was previously referred to as vertically filtering a table).
Default properties are set for each selected object (article), including the destination object name and object owner, action to use if the object exists, and if user triggers and extended properties should be copied. You can manage these default properties by setting global defaults, by setting defaults for an individual article, or both:
To set defaults for an individual article, select it under Objects To Publish, click Article Properties, and then select Set Properties Of Highlighted... Article. Refer to the subsection titled "Setting Publication Properties" later in this chapter for details.
To set defaults for all articles of a particular type, select the object type under Objects To Publish, click Article Properties, and then select Set Properties Of All... Articles. Refer to the subsection titled "Setting Publication Properties" later in this chapter for details.
After you select objects to use in the publication, click Next. If there are any issues that require changes to the publication, you will see a prompt, similar to the one previously shown in Figure 12-8, describing the issue or issues. Read the description carefully to determine how to resolve the problems indicated, and then make changes as necessary. Common issues include:
Tables referenced by views are required and so are objects referenced by stored procedures. If you do not select referenced tables or objects, you must create them manually at the subscriber.
SQL Server adds uniqueidentifier columns to any tables you have selected for replication. Adding the uniqueidentifier column will cause INSERT statements without column lists to fail and will increase the time needed to generate the first snapshot.
IDENTITY columns require the NOT FOR REPLICATION option. If a published IDENTITY column does not use this option, INSERT commands may not replicate properly.
Click Next. Use the Filter Table Rows page to exclude unwanted rows from published tables. As you define filters, they are added to the Filtered Tables list. Selecting a filter shows the related WHERE clause in the Filter text box. You can define filters manually and then extend those filters to other tables, or you can attempt to automate the process.
If you want to define a new filter:
Click Add, and then select Add Filter to display the Add Filter dialog box. By default, all rows are published. To change this behavior, select a table to filter, and then create a filter statement that identifies which rows subscribers will receive. Enter a WHERE clause for the corresponding SELECT published_columns FROM TableName statement.
Specify how many subscribers will receive data from this table: one or many. Merge publications use static or parameterized filters. Static filters are evaluated when the publication is created, and all subscribers to the publication receive the same data. Parameterized filters are evaluated during replication synchronization, and different subscribers can receive different partitions of data based on the subscriber login or computer name.
After you define a filter, you can extend the filtering to a related table by defining a join. To do this, create a filter on a table as discussed in the previous step, then in the New Publication Wizard, select the filter, click Add, and then select Add Join To Extend The Selected Filter. This displays the Add Join dialog box shown in Figure 12-10. You must now:
Select the joined table from the list of published tables available in the Joined Table drop-down list, and then define the INNER JOIN clause using the builder or manually.
Specify the join options. If there is a one-to-one or one-to-many relationship between rows in the joined table, select Unique Key. If rows in the joined table do not relate to exactly one row in the filtered table, clear Unique Key. Additionally, if related changes should be handled as a logical record and you are working with a unique key, select Logical Record.
An alternative to defining filters manually is to generate filters automatically. Click Add, and then select Automatically Generate Filters. In the Generate Filters dialog box, you must then define a new filter as explained previously. SQL Server will then use defined relationships to add joins that extend the filter to other tables.
Click Next. The Snapshot Agent initializes subscriptions by creating a snapshot of publication schema and data that can be pushed to subscribers or pulled by subscribers. You can create a snapshot immediately by selecting Create A Snapshot Immediately. If you want the Snapshot Agent to create a snapshot periodically, select Schedule The Snapshot Agent To Run.... By default, snapshots are made once every 14 days. To change this schedule, click Change, and then set a new schedule.
Click Next. Configure the login for the Snapshot Agent. Click the related Security Settings button.
Specify the Windows account under which the agent runs at the Distributor. This account is referred to as the process account. The account must be a member of the db_owner fixed database role in the distribution database and must have write permissions on the snapshot share. Be sure to type domain accounts names in the form domainaccount, such as cpandlsqlserver. Then type and confirm the account password.
Specify whether the agent should make connections to the publisher by impersonating the account specified in the Process Account text box or by using a SQL Server account. If you choose to use a SQL Server account, enter a SQL Server login and password. Typically, you will want to impersonate the Windows account rather than use a SQL Server account. Click OK.
Click Next. Choose the wizard action upon completion. By default, the wizard creates the publication. You can also generate a script file with the steps to create the publication. If you want to only generate a script, clear Create The Publication. Click Next.
On the Complete The Wizard page, type a name for the publication, and then click Finish. You will see a dialog box that shows the progress of the creation process. If errors occur, you must resolve any problems before you can continue or you must restart the publication definition process.
You can view or change the properties of publications at any time. To do so, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Click the Local Publications folder to see a list of publications for the replicated database. The icon associated with a publication tells you its type:
Snapshot. Purple book icon with a blue circle
Transactional. Blue book icon with a green arrow pointing right
Merge. Yellow book icon with green arrows pointing left and right
Right-click the publication you want to change, and then select Properties to display the Properties dialog box.
Use the Properties dialog box to configure all of the publication options discussed in the subsection titled "Creating Publications" earlier in this chapter.
Publication properties control the behavior of replication. You can modify the properties at any time. To edit the properties of an existing publication, follow these steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Click the Local Publications folder to see a list of publications for the replicated database.
Right-click the publication you want to edit, and then select Properties.
This displays the Publication Properties dialog box. The available publication properties depend on the replication type, and you may see one or more of the following pages:
General. Allows you to configure basic options and is available for all replication types. You can view the article name, source database, and type. You can set the description and the subscription expiration. By default, subscriptions do not expire, but you can set an expiration interval in hours.
Articles. Allows you to view and configure published articles.
Filters Rows. Allows you to view and work with row filters.
Snapshot. Sets options for snapshots, which are used with all replication types. You can set options that control the snapshot format, either Native SQL Server or Character. Native SQL Server can only be used when all subscribers are running SQL Server. Character format is required when a publisher or subscriber is not running SQL Server. You can also specify where to put snapshot files and additional scripts to run before or after applying a snapshot.
FTP Snapshot. Allows subscribers to download snapshot files using FTP. If selected, snapshot files are placed in the FTP root folder by default, and anonymous login is used. You can specify an alternate path from the root folder and provide a login and password.
Subscription Options. Provides options for allowing or disallowing anonymous subscriptions, attachable subscription databases, pull subscriptions, non-SQL Server subscribers, and replication of schema changes. By default, anonymous subscriptions, pull subscriptions, and replication of schema changes are allowed.
Publication Access List. Controls who can access the publication. By default, only sa, local administrators, the database owner, the process accounts and distributor_admin have access to the publication data.
Agent Security. Allows you to view or change the process account for agents used by the publication.
All publications use one or more replication agents, including the Snapshot, Log Reader, and Queue Reader Agents. The Snapshot Agent is used with all publication types. The Log Reader Agent is used with updatable and nonupdatable transactional publications. By default, the Log Reader Agent uses the same login as the Snapshot Agent. The Queue Reader Agent is used with updatable transactional publications. By default, the Queue Reader Agent has a security context that is separate from the other agents.
If agent security is not configured properly, replication will fail. You can configure security for these agents by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Click the Local Publications folder to see a list of publications for the replicated database.
Right-click the publication you want to configure, and then select Properties to display the Properties dialog box.
Select the Agent Security page.
To configure the login for the Snapshot Agent or Log Reader Agent, click the related Security Settings button, and then:
Specify the Windows account under which the agent runs. This account is referred to as the process account. Be sure to type domain account names in the form domainaccount, such as cpandlsqlserver. Then type and confirm the account password.
Specify whether the agent should make connections to the Publisher by impersonating the account specified in the Process Account text box or by using a SQL Server account. If you choose to use a SQL Server account, enter a SQL Server login and password. Typically, you will want to impersonate the Windows account rather than use a SQL Server account.
To configure security for the Queue Reader Agent, which is used for transaction replication with updating, click the related Security Settings button, and then specify the process account.
All publications have access control lists (ACLs). For publications, access control lists determine which logins can be used by pull and immediate updating subscribers to access the publication. By default, only sa, local administrators, the database owner, the process accounts, and distributor_admin have access to the publication data. To add or remove users, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Click the Local Publications folder to see a list of publications for the replicated database.
Right-click the publication you want to change, and then select Properties to display the Properties dialog box.
Select the Publication Access List page. Use the buttons provided to add or remove logins.
Scripts can help you manage publications. You can generate scripts to create the objects specified in a publication and enable the publication, or to drop the objects specified in a publication and disable the publication.
To create a script for a publication, complete the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Click the Local Publications folder to see a list of publications for the replicated database.
Right-click the publication you want to edit, and then select Generate Scripts.
In the Generate SQL Script dialog box, specify the type of script to generate. Typically, you will want to create or enable the replicated objects rather than drop or disable them.
The script will call replication stored procedures to perform the necessary tasks, and it creates any necessary jobs when executing. To script the jobs and create a record of the jobs, select the Replication Jobs check box.
Click Script To File. Use the Script File Location dialog box to select the location where you want to save the script to a .sql file, and then click Save. By default, the file is saved as Unicode text. The file can be executed in Query view to recreate or drop the publication.
Click Close.
When you are finished using a publication, you can delete it to release resources that it is using. But before you do this, you may want to create a script that allows you to recreate the publication automatically if you need it again. After creating the script, you can delete the publication by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Click the Local Publications folder to see a list of publications for the replicated database.
Right-click the publication you want to remove, and then select Delete.
When prompted to confirm the action, click Yes.