Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Creating a Publication
Now that we have configured our server for publishing and distribution, it's time we defined a publication—a set of database objects that will be replicated. For this example, I will create a publication with a single article: the authors' table. Any publication that replicates tables will work similarly. Replication of views, stored procedures and user-defined functions is slightly different (I'll save that for another article).
Let's activate the Create Publication Wizard by choosing Tools, Replication, Create and Manage Publications and clicking Create Publication on the first screen. Note that the welcome screen contains a checkbox that allows us to display advanced options with the wizard.
)
Figure
14.For now, let's skip the advanced options and move to the next screen, which lets us select a database in which the publication will be created. We choose PUBS and click Next. The following screen lets us choose the type of the publication.
)
Figure
15.We want to move transactions from the main data entry server to the reporting server, so let's choose Transactional publication and click Next. On the following screen, we can specify types of subscribers that will be using this publication. Note that we can replicate transactions from SQL Server 2000 to earlier versions of SQL Server, as well as other database engines, such as Microsoft Access or Oracle.
)
Figure
16.For this example, let's choose Servers running SQL Server 2000 (checked by default) and click Next.
NOTE
For those using earlier versions of SQL Server, you'd do yourself a huge favor by upgrading to SQL Server 2000. Previous versions do not support all functionality available with SQL Server 2000 so be sure to check Books Online to ensure that all desired functionality is supported.
The following screen lets us choose the database objects you want to publish. Notice that some tables have a golden key and a red X next to them. This means that a table does not have a primary key and can't be published for transactional replication.
)
Figure
17.Also note that by default the Create Publication Wizard selects tables as articles. Clicking the Article Defaults box lets us choose tables, stored procedures, views, or user-defined functions as default articles. Let's check the authors table in the right pane of the wizard for this publication because that's the one we want to replicate. Notice that the ellipsis button next to the checked table becomes visible, so we can click on that button to customize the article properties.
)
Figure
18.The destination table doesn't have to have the same name or the same owner: We could change the article name and description. I don't recommend changing the article name; however, we could store some useful information in the description box, such as "Replicating only those authors who live in Ohio". The Commands tab lets us specify the replication commands that will be sent to the subscriber(s) when the INSERT, UPDATE, or DELETE command is issued on the publisher.
)
Figure
19.Stored procedures used by default are created during initial synchronization (by default). Furthermore, to optimize performance, SQL Server sends parameters to replication procedures in binary format. All these options are advanced and deserve their own article for further explanation. For now, we won't change any of them.
Yet another advanced tab called Snapshot lets us customize what replication does when applying snapshots to the subscribing databases. Again, we'll review but won't change anything on this tab.
)
Figure
20.After examining the article properties, we click Next and are allowed to specify the publication name and description as shown in the following figure.
)
Figure
21.The next screen in the wizard allows us to take a shortcut by allowing replication to work with default options or lets us customize the publication. For this example, the advanced options are not necessary; we simply click Next and we're taken to the final screen.
)
Figure
22.If we do choose to customize the publication, we have an option to filter the article(s) by specifying the WHERE clause (horizontally) or by replicating values of some but not all table columns (vertically). We then click Finish.
By now, we have configured the publisher, subscriber, and distributor; and have created a publication. The only remaining piece is creating a subscription.