Administering Replication in Management Studio
The administration of replication in Management Studio can be accessed from the Replication folder, which is under the server folder in Object Explorer (see Figure 12-3).
Figure 12-3. Replication folder in Object Explorer.
One server instance can be both a Publisher (publish data to others) and a Subscriber (subscribe to data published by others) at the same time. The Replication folder provides a consolidated view of both publication and subscription activity on the server instance. The Replication folder is structured as shown in Figure 12-4.
Figure 12-4. Replication folder structure.
All objects in Local Publications relate to the "local" server instance (the one to which you are connected) in its role as a Publisher. Thus, publications in Local Publications are of objects on this server instance, and the subscriptions below the publications represent the instances receiving copies of those objects. The objects in Local Subscriptions relate to the server instance in its role as a Subscriber. These subscriptions indicate objects that are being copied from other Publishers.
Publications and subscriptions in Object Explorer have the icons shown in Table 12-1.
Publication icons show the type of the publication. Subscription icons tell whether the agent that synchronizes the subscription can be administered from the local server instance.
When you see the darker-colored icon for a subscription, you can right-click on the subscription and select View Synchronization Status. Management Studio displays a dialog box that shows whether the agent is running and displays the last message from the agent. It also provides buttons to start or stop the agent. When you see the lighter-colored icon for a subscription, you have to connect to another server instance to administer the synchronization agent.
Like table names, publication names are unique within a database, but the same publication name can be used in different databases. Because the Replication folder is at the server instance level, the database context for a publication must be explicitly stated. Publications in Object Explorer are identified as
Note that a colon is used to separate the database and publication names because publications cannot be referenced if they use the standard period separator for database object names.
Identifying a subscription is more difficult than identifying a publication because a subscription doesn't have a name of its own. To uniquely identify a subscription, you need to identify the publication and the subscription database, which requires five pieces of information:
Subscriptions in Local Subscriptions specify all of these except the Subscriber name (the Subscriber is always the instance to which you are connected). Subscriptions in Local Subscriptions are identified as
Subscriptions under a publication node have a simpler format because the publication context is provided by the parent. These subscriptions are identified as
Management Studio displays a detailed tooltip such as the one shown in Figure 12-5 that explains the parts of the name when you point to a publication or subscription. For a publication, the tooltip shows the Publisher, publication database, and publication type. For a subscription, the tooltip shows the Publisher, publication database and type, Subscriber, subscription database, and where the synchronization agent runs. The tooltip remains visible for five seconds. If you need more time to read everything, point to the node again to see the tooltip for another five seconds.
Figure 12-5. Tooltip detailing a local subscription.
All replication commands are available from the context menus on the objects under Replication. The commands you see when you right-click on Local Publications and its children let you configure the server as a Publisher, such as for creating and editing publications. The commands you see when you right-click Local Subscriptions and its children let you configure the server as a Subscriber, such as for creating subscriptions to publications from other servers and synchronizing the subscriptions.
Using the New Publication Wizard to Create Publications
The first step to configuring replication is to create a publication. To start the New Publication Wizard, expand Replication, right-click Local Publications, and select New Publication.
This wizard has about ten pages, although the number of pages varies depending on whether this is your first publication and what type of publication you create. Most pages are straightforward, with good explanations of the task for the page. If you need additional information, click Help. This chapter focuses on the most complex pages of the wizard.
The wizard asks you which database (or part of the database) you want to publish. It also asks you to choose the type of publication to create: snapshot, transactional, or merge. If you are not familiar with the publication types and how they fit your needs, SQL Server Books Online discusses this information in detail. Because the publication types have different requirements and properties, the pages you see in the wizard and the options on a given page vary depending on the type of publication you create.
Articles Page of the New Publication Wizard
You choose the database objects to replicate in the publication on the Articles page, shown in Figure 12-6. (To extend the publication metaphor, the objects that are published are referred to as "articles.")
Figure 12-6. New Publication Wizard, Articles page
The Articles page is the core of the wizard. On this page, you select which database objects to replicate and the properties and behavior of the objects as they are replicated. SQL Server allows you to publish tables, stored procedures, views, indexed views, and user-defined functions. The wizard presents these objects in a tree so you can easily navigate the list of objects. You choose an object for publication by checking the box next to the object, and you can easily publish all objects of a certain type by checking the box next to the type name. For example, checking the box next to Tables publishes all table objects that are eligible to be published.
If you have many objects of one type, such as hundreds of stored procedures, working with the list can be a challenge. To see more of the list at one time, make the window bigger by dragging the bottom-right corner of the window or clicking the Maximize button in the title bar.
To publish only a few objects of one type, expand the type and check the boxes of the objects you want to publish. To publish most, but not all, objects of a specific type, it is faster to first check the box next to the type name to publish all eligible objects of that type, and then uncheck the boxes for objects you don't want to publish.
After you select the objects you want to publish, you can hide the objects you don't want by clicking the Show only checked objects in the list box. To see the objects in the list again, uncheck the box.
SQL Server enables you to filter the data in published tables so that only certain columns or rows in the table are replicated. You may filter a table to control access to sensitive information, such as a salary column, or to reduce the size of the data being replicated. You can specify which columns to publish in the Articles page. You can specify which rows to publish on the Filter Table Rows page later in the wizard.
To see the columns of a table, expand the individual table node. The column names are displayed under the table, with check boxes next to each name. When you publish a table, the wizard automatically publishes all eligible columns in the table and checks the boxes next to the columns. If you do not want to publish a column, uncheck the box next to the column.
To publish only a few columns of a table, expand the table but do not check the box for the table. Instead, check the boxes next to the columns you want to publish. To publish most, but not all, columns in a table, it is faster to first check the box next to the table, and then uncheck the boxes for columns you don't want to publish.
In some cases, SQL Server may not allow you to publish a table if that table does not meet the requirements for an article in the publication you are creating. For example, if a table does not have a primary key, you cannot publish it in a transactional publication. Tables that are not eligible to be published are included in the list, but the icon for that table is marked with a special symbol and you cannot check the box for that table. Similarly, SQL Server may not allow you to publish certain columns of a table. Conversely, SQL Server may require that a certain column, such as the primary key column, be published if the table is published. The icons presented in Table 12-2 are used in Objects to publish to help communicate these restrictions.
If a table or column has a special icon to indicate that it cannot be published or it must be published, select the object and the New Publication Wizard explains the reason. When a restricted object is selected, an Information Panel is automatically displayed at the bottom of the page (see Figure 12-7). When you select an object without a restriction, the Information Panel is hidden.
Figure 12-7. New Publication Wizard, Articles page, showing filtered columns and table and column restrictions.
Each article that is published has properties that govern how the object is created at the Subscribers, how the replicated object behaves, and other settings. Although you usually do not have to change the default article properties, it is good to understand what the defaults are so that you know how to adjust them if the need arises.
To view or change the properties of an individual object, select it in Objects to publish and click Article Properties. When you click the button, a menu is displayed with the following commands: Set Properties of Highlighted <type> Article and Set Properties of All <type> Articles, where <type> is a Table, Stored Procedure, View, and so on.
If you click Set Properties of Highlighted <type> Article, Management Studio displays the Article Properties dialog box for the selected article (see Figure 12-8).
Figure 12-8. Article Properties dialog box.
If you right-click on an object in Objects to publish, you get a menu of commands for that object, including the two for article properties. In some cases, you may be able to set common properties directly from the context menu without going through the Properties dialog box.
Like most windows in Management Studio, you can resize the Article Properties dialog box to see more properties at one time. The dialog box uses a property grid to display most of the properties. Note that when you click on a property in the grid, a short description of the property is displayed in the Description box below the grid.
If you cannot read the entire description displayed below a property grid, you can increase the size of the Description box by pointing the mouse at the area between the grid and the Description box. When you see a resize cursor, click and drag up.
You can also change the properties of all articles of a single type at one time. Let's use Tables as an example. After you have checked the boxes for the tables you want to publish, highlight the Tables item or one of the tables in Objects to publish. Click Article Properties and select Set Properties of All Table Articles. Management Studio displays the Properties For All Tables dialog box. This dialog box displays the default values for the properties of table articles. If you change a property value and click OK, the value of that property is changed for all the currently published tables and it becomes the new default value for any other tables that you publish in this run of the wizard. Note that the new default value is not remembered the next time you run the New Publication Wizard or if you open the Publication Properties dialog box for this publication.
For example, the default value for the table article property Convert data types is False. You publish two tables, highlight the Tables item, click Article Properties, and select Set Properties of All Table Articles. Management Studio displays the Properties For All Table Articles dialog box. You change the Convert data types value to True and click OK. The value of this property for each published table is changed to True. The default value for this property is also changed to True. You then publish a third table, and Convert data types for the new article defaults to True. The next time you run the New Publication Wizard, the default value for Convert data types has returned to False.
Filter Table Rows Page of the New Publication Wizard
In some publications, you may need to filter the rows in one or more published tables so that only certain data is replicated to the Subscribers. For snapshot and transactional publications, all subscriptions receive the same data, but for merge publications, you can customize the filters so that different subscriptions receive different data. This can be helpful in circumstances such as a sales application where salespeople need only their own customer and order information. The Filter Table Rows page of the New Publication Wizard allows you to define these filters. If you do not need to filter table rows, click Next on this page and continue through the wizard. Because filtering in a merge publication offers additional functionality, how this page operates with each publication type is discussed separately.
The Filter Table Rows page for a snapshot or transactional publication is shown in Figure 12-9. The Filtered Tables list shows each table in the publication for which you have defined a row filter. A row filter is the WHERE clause in the following query:
Figure 12-9. New Publication Wizard, Filter Table Rows (snapshot or transactional publication).
When you select a table in the list, the row filter statement is displayed in the Filter box. You can add a row filter on another table by clicking Add, choosing the table, and specifying the filter statement. Note that a row filter affects only the data replicated from the filtered table. If other tables are dependent on data in that table, such as an order table with a foreign key constraint on a customer table, you must make sure that dependent tables are also filtered as needed.
As shown in Figure 12-10, in the merge publication the Filter Table Rows page looks a little different.
Figure 12-10. New Publication Wizard, Filter Table Rows (merge publication).
You will notice immediately that merge publications allow filtering across a hierarchy of tables, in addition to filtering each table individually. Unlike other publications, you can configure a merge publication so that when you apply a row filter to one table, dependent tables are automatically filtered so that foreign key and other constraints are not violated in the data that the Subscriber receives. You extend filtering from a filtered table by specifying join filters to define the relationship between the filtered table and the joined tables. SQL Server can then cascade the effects of the filter statement to the joined tables.
For example, your publication contains the tables Customers, Orders, and OrderDetails. You define a row filter on the Customers table that includes only French customers: WHERE Country = 'France'. You then add join filters that link Customers.CustomerID with Orders.CustomerID and Orders.OrderID with OrderDetails.OrderID. When the entire set of filters is evaluated to create a subscription, the Subscriber tables will contain only rows in the Customers table where Country is equal to 'France,' rows in the Orders table that relate to French customers, and rows in the OrderDetails table that relate to the orders from French customers. Customer, order, and order detail data for customers not in France has been filtered out.
You must always begin with a row filter on a single table, and then extend from that table with join filters. You can have filters on several hierarchies in one publication, each starting from a different filtered table, but the hierarchies must not overlap. You can also have filtered tables that are not extended by joins.
If you have defined primary and foreign key relationships in your database, Management Studio can generate join filters automatically based on those relationships. To do this, click Add and select Automatically Generate Filters from the menu. In the Generate Filters dialog box, choose the table for the row filter, enter the filter statement, specify how many subscriptions will receive each published row, and click OK. SQL Server analyzes the relationships between the tables and creates the join filters that will extend the row filter on your starting table.
If Filtered tables contains a large number of tables, it can be difficult to locate a particular table if you want to change its row or join filter or see how it fits in the hierarchy. To quickly find a table, click Find Table, select the table, and click OK. Management Studio then locates the table, selects it, and expands and scrolls the tree so it is visible.
To add a join manually, select the table in Filtered tables from which you want to join and click Add. Select Add Join to Extend Filtered Table.
The table from which you are joining is automatically specified as the Filtered table in the dialog box. In Joined table, select the table to which you want to join (see Figure 12-11). When you select the joined table, Management Studio looks to see whether there is a defined relationship between the two tables. If so, it automatically enters the column names in the join builder grid in step 2 of the dialog box. You can remove those columns, add other pairs of columns, or change the operator that defines the relationship between the columns. As you make changes in the builder, you can see in the Preview box how the join statement will look.
Figure 12-11. Add Join dialog box.
Optionally, you can write the join statement yourself and not use the builder. If you select Write the join statement manually, the middle section of the dialog box changes to look like Figure 12-12.
Figure 12-12. Write the join statement manually in the Add Join dialog box.
Double-clicking a column name in the Filtered table columns or Joined table columns lists inserts the column name into the Join statement text box at the cursor location.
When adding join filters, you can optionally specify that rows in the two tables should be treated as one "logical record." By default, merge replication synchronizes data changes between the Publisher and a Subscriber on a row-by-row basis. If you enable a logical record, related changes are processed as a unit during synchronization. This ensures that during synchronization, related changes always go together. For example, if the Orders and OrderDetails tables are enabled as a logical record, and a change is made to an order and to the details of that order, both changes must be replicated or neither is replicated. If an error occurs when synchronizing the row in Orders, you know that the change to the row in OrderDetails will not be synchronized either.
When you enter the Add Join or Edit Join dialog box you may find that the Logical record option button is disabled or not visible. This happens when the publication and article requirements for logical records are not met. For more information on the requirements, see "Considerations for Using Logical Records" in SQL Server Books Online.
Merge publications also enable you to partition published data so that different subscriptions receive different partitions. You do this by specifying a parameterized row filter instead of a static row filter on a table. In the preceding example, the filter statement WHERE Country = 'France' is a static filter because it compares constant values that evaluate identically for all subscriptions, sending the same rows to everyone. A parameterized filter uses a function, such as SUSER_SNAME() or HOST_NAME(), that evaluates differently for each subscription. Using HOST_NAME() in the filter criteria gives the most flexibility because you can override HOST_NAME() and supply your own value when synchronizing the subscription. In this example, you could specify the filter statement as WHERE Country = HOST_NAME(), and then specify a different country name for each subscription. This makes it easy to create one subscription that contains French customers, another that contains Canadian customers, and so on. For a complete discussion, see the topic "Parameterized Row Filters" in SQL Server Books Online.
By default, the HOST_NAME() function returns the name of the computer that connects to a SQL Server instance, but you can override this when creating a subscription. In the New Subscription Wizard, specify the new value on the HOST_NAME() Value page. Note that this changes the value returned by all calls to the function, not just from replication. Be careful that other applications are not dependent on the value of the HOST_NAME() function.
When adding a row filter on a table in a merge publication, the Add Filter dialog box has an additional option that is not available for snapshot and transactional publications.
When adding a parameterized filter with the SUSER_SNAME() or HOST_NAME() functions, you can specify how many subscriptions will receive each published row in the table. If the published data is well partitioned by your filters so that a given row will be replicated to exactly one subscription, SQL Server can optimize the performance of the synchronization. For example, you could define a row filter to compare SalesPersonID to HOST_NAME(). Because the ID of each salesperson is unique, each subscription will contain only the rows for that person and no others. Therefore, you can choose A row from this table will go to only one subscription (see Figure 12-13). As you extend filtering from this table by adding join filters, the performance improvement extends to all the joined tables as well.
Figure 12-13. Add Filter dialog box for a merge publication.
Snapshot Agent Page of New Publication Wizard
The Snapshot Agent page (see Figure 12-14) enables you to schedule when the agent runs and, for transactional publications, whether the snapshot files are persisted. Controlling when the agent runs is important because generating a snapshot can adversely affect the performance of your server, especially if the size of the published data is large. You may want to run the Snapshot Agent during off-peak hours rather than when you are running the wizard.
Figure 12-14. New Publication Wizard, Snapshot Agent page (merge publication).
For merge publications, the Snapshot Agent always generates a new snapshot and cleans up previous snapshots. The new snapshot files remain until the Snapshot Agent runs again, and Merge Agents always have a snapshot to use to initially synchronize subscriptions. In this mode, it does not matter whether you run the Snapshot Agent before or after you create a subscription to initialize it.
For transactional publications, however, you can choose whether you want this behavior. If you check the Create a snapshot immediately and keep the snapshot available to initialize subscriptions box, the behavior is exactly like that described for merge publications, except it is a Distribution Agent, not a Merge Agent, that applies the snapshot. If you do not check this box, however, the behavior of the Snapshot Agent is different. When the agent runs, it generates a snapshot only if there is a subscription waiting to be initialized or reinitialized, and when all subscriptions have been initialized, the Snapshot Agent deletes the snapshot files. If the Snapshot Agent runs and there are no subscriptions needing initialization and no snapshot files to delete, the agent stops without doing anything. In this mode, you must run the Snapshot Agent after you create a subscription to initialize it.
For a transactional publication, the first check box is different (see Figure 12-15).
Figure 12-15. The check box portion of the New Publication Wizard, Snapshot Agent page (transactional publication).
Be aware that keeping the snapshot files may require a large amount of disk space because the snapshot files include a copy of all of the published data. Keeping the snapshot files available can cause SQL Server to keep replicated transactions in the distribution database longer. For more information, see the topic "Subscription Expiration and Deactivation" in Books Online.
If you do not expect to create subscriptions frequently, do not create a schedule for the Snapshot Agent. If you create all subscriptions as part of an initial configuration, the snapshot is needed for only the time of your configuration. After that, you can generate a new snapshot when circumstances arise, such as when you create a new subscription or need to reinitialize a subscription.
Wizard Actions Page of New Publication Wizard
New Publication Wizard creates your publication when you click Finish, but the wizard can also generate a script of the steps needed to create the publication. Having a script is an important part of a recovery plan for the database so you can easily re-create the publication in the future without having to go through the wizard again. The script is also helpful if you plan to make a similar publication. It may be faster to modify and execute the script than to use the wizard again.
Just before the final page of the wizard, you will see the Wizard Actions page. To have the wizard generate a script, check the Generate a script file box. With this box checked, when you click Next, you will be asked for the file location and other options for creating the script. When you click Finish on the last page of the wizard, the wizard creates the publication, generates the script, or both, according to which boxes are checked on the Wizard Actions page (see Figure 12-16).
Figure 12-16. New Publication Wizard, Wizard Actions page.
You can generate a script any time after the publication is created by right-clicking on the publication in Object Explorer and clicking Generate Scripts.
Using the New Subscription Wizard to Create Subscriptions
Creating a publication just defines the set of database objects that you want to replicate. To actually get a copy of those objects to other server instances, you must create subscriptions to the publication. You can create a subscription in Management Studio in two ways. You can start from the publication object at the Publisher, or you can start from the Local Subscriptions folder on the Subscriber. To start from the Publisher, expand the Replication folder, expand Local Publications, and right-click on the publication. From the context menu, select New Subscription. To start from the Subscriber, expand the Replication folder and right-click on Local Subscriptions. From the context menu, select New Subscription. If you are an administrator or database owner on both server instances, it doesn't matter which method you choose; the wizard is the same, although some of the defaults are different.
In the New Subscription Wizard, begin by identifying the publication to which you want to subscribe (see Figure 12-17).
Figure 12-17. New Subscription Wizard, Publication page.
Select the Publisher from the list or select <Find SQL Server Publisher> to see the Connect to Server dialog box. In the Connect to Server dialog box, specify the Publisher and how the wizard should connect to it. The wizard then displays a list of publication databases on the Publisher. Expand the appropriate database and select the publication.
Databases and publications contains only publications that the account used to connect to the Publisher has permission to see. If you cannot see a publication that you think you should be able to see, make sure you have connected to the Publisher using the appropriate credentials, or have the administrator of the Publisher add your login to the publication access list in the Publication Properties dialog box.
After selecting the Publisher and publication, you must select one or more Subscribers and subscription databases (see Figure 12-18).
Figure 12-18. New Subscription Wizard, Subscribers page.
The New Subscription Wizard enables you to create subscriptions in Oracle or IBM DB2 databases as well as at SQL Server instances. If the server instance that you want as a Subscriber is not in the list, click Add Subscriber and select Add SQL Server Subscriber or Add Non-SQL Server Subscriber from the menu.
If the subscription database doesn't exist as a SQL Server Subscriber, you can create one from this wizard page as long as you have the necessary permissions on the Subscriber. In the Subscription Database column, click the drop arrow and select <New Database> from the list. Management Studio displays the New Database dialog box.
You can create more than one subscription to the selected publication in the New Subscription Wizard. The only restriction is that the Merge or Distribution Agents for the subscriptions must all run on the Distributor or must all run on the Subscribers, as specified on the Merge Agent Location or Distribution Agent Location page of the wizard.
The remaining pages in the New Subscription Wizard ask for values for the properties of the subscription and the Merge or Distribution Agent. The number of pages varies depending on the type and properties of the publication to which you are subscribing. The pages are straightforward, with good explanations on the page of the question being asked. If you need additional information, click Help.