Microsoft Press microsoft sql server 2005 PHẦN 9 doc

706 Chapter 19 Managing Replication

Lesson 2: Setting Up Replication

The most straightforward method to configure replication is via SSMS. But you can

also use Transact-SQL statements or SQL Server RMOs to configure replication. The

general steps for configuring replication are as follows:

1. Set up a Distributor for the Publisher to use.

2. Create a publication to replicate that includes the articles you want to copy.

3. Configure the Subscriber and a subscription.

In this lesson, you see how to use SSMS to perform these steps to configure a replica￾tion topology. You also see how to generate the equivalent Transact-SQL configura￾tion scripts.

After this lesson, you will be able to:

■ Create the distribution database.

■ Enable a database for replication.

■ Create a publication.

■ Subscribe to a publication.

Estimated lesson time: 40 minutes

How to Set Up the Distributor

The first step in setting up replication is configuring the Distributor. You can assign

each Publisher to only one Distributor instance, but multiple Publishers can share a

Distributor. As noted earlier, you can configure the Distributor server to act as the dis￾tributor of its own data (local Distributor), which is the default, or as a distributor of

data for remote servers (remote Distributor).

BEST PRACTICES Remote Distributor

You might decide to use a remote Distributor if you want to offload the Distributor processing from

the Publisher computer to another computer or if you want to configure a centralized Distributor

for multiple Publishers.

Note that the server you choose as the Distributor should have adequate disk space

and processor power to support replication and the other activities that need to run

on that server.

C1962271X.fm Page 706 Friday, April 29, 2005 8:04 PM

Lesson 2: Setting Up Replication 707

Here is how to configure the Distributor as a local Distributor:

1. Open SSMS.

2. Connect to the database engine instance you want to configure as Publisher and

Distributor.

3. Right-click the Replication folder and choose Configure Distribution.

4. On the Configure Distribution Wizard page, click Next.

5. On the Distributor page, select server_name Will Act As Its Own Distributor and

click Next.

6. On the Snapshot Folder page, type the path to a local folder or the Universal

Naming Convention (UNC) name for a shared folder in which you want to store

the files that will hold the publication’s schema and data. Click Next.

NOTE Snapshot folder choices

Consider three important factors as you make your Snapshot folder choice. First, if your sub￾scription topology uses pull subscriptions, use a UNC network path. Second, plan how much

space the snapshot files will occupy. And finally, secure the folder and grant permission to

only the Snapshot Agent (write) and to the Merge or Distribution Agent (read). Lesson 3 in

this chapter provides more details about how to secure replication.

7. On the Distribution Database page, type the name of the database and the path

of its data and log files, as Figure 19-7 shows. By default, SQL Server names this

database distribution and places it in the \Program Files\Microsoft SQL Server\

MSSQL.x\MSSQL\Data folder, where x is the number assigned to the instance

on which you are configuring replication. Click Next.

BEST PRACTICES Configuring the distribution database

Transactional replication demands more from the distribution database than any other repli￾cation type. If you plan to use transactional replication in large and volatile databases, con￾sider placing the log and data files of the distribution database in different disk channels,

using RAID 1 or RAID 10 for the data files and RAID 1 for the log files.

8. On the Publishers page, add other publishers you want to authorize as Publish￾ers to this Distributor, and click Next. By default, SSMS also configures the Dis￾tributor as a Publisher.

9. On the Wizard Actions page, you can use the available check boxes to indicate

whether you want SSMS to execute the commands now, script them, or both. By

default, the Configure Distribution check box is selected. Click Next.

C1962271X.fm Page 707 Friday, April 29, 2005 8:04 PM

708 Chapter 19 Managing Replication

Figure 19-7 Configuring the distribution database

10. If you chose to script the commands, you now see the Script File Properties page.

You use this page to configure the name, path, and format of the script. By

default, SQL Server creates this script file in your My Documents folder. You can

also specify whether you want SQL Server to overwrite an existing script file

with the same name or to append this script to it. Click Next.

BEST PRACTICES Scripting the configuration

Scripting the Distributor configuration is a good idea for documentation purposes; plus, you

can use the script in a recovery plan. Additionally, you can use scripts to create more com￾plex database file configurations.

11. On the Complete The Wizard page, review the summary of choices you made

and then click Finish.

12. Wait for the Configure Distribution Wizard to complete the configuration. After

it finishes, click Close.

The following code block shows the Distributor configuration script:

Distributor Configuration Script

/*** Scripting replication configuration for server COMPUTERNAME. ***/

/*** Please Note: For security reasons, all password parameters

were scripted with either NULL or an empty string. ***/

/*** Installing the server COMPUTERNAME as a Distributor. ***/

C1962271X.fm Page 708 Friday, April 29, 2005 8:04 PM

Lesson 2: Setting Up Replication 709

use master

exec sp_adddistributor @distributor = N'COMPUTERNAME', @password = N''

GO

exec sp_adddistributiondb @database = N'distribution'

, @data_folder = N'C:\MSSQL\Data'

, @data_file_size = 4

, @log_folder = N'C:\MSSQL\Data'

, @log_file_size = 2

, @min_distretention = 0, @max_distretention = 72

, @history_retention = 48, @security_mode = 1

GO

use [distribution]

if (not exists (select * from sysobjects

where name = 'UIProperties' and type = 'U '))

create table UIProperties(id int)

if (exists (select * from ::fn_listextendedproperty('SnapshotFolder'

, 'user', 'dbo', 'table', 'UIProperties', null, null)))

EXEC sp_updateextendedproperty N'SnapshotFolder'

, N'C:\MSSQL\ReplData', 'user', dbo, 'table'

, 'UIProperties'

else

EXEC sp_addextendedproperty N'SnapshotFolder'

, 'C:\MSSQL\ReplData'

, 'user', dbo, 'table', 'UIProperties'

GO

exec sp_adddistpublisher @publisher = N'COMPUTERNAME'

, @distribution_db = N'distribution'

, @security_mode = 1, @working_directory = N'C:\MSSQL\ReplData'

, @trusted = N'false', @thirdparty_flag = 0

, @publisher_type = N'MSSQLSERVER'

GO

Be aware that the @distributor, @data_folder, @log_folder, SnapshotFolder, @working_

directory, and @publisher parameters you see in this script are all specific to your envi￾ronment. The Distributor configuration script that the wizard generates uses three

main stored procedures. The first procedure, sp_adddistributor, defines the Distribu￾tor when the server acts as Publisher. To configure the server as its own Distributor,

set the @distributor parameter to its own server name; to use a remote server, use the

remote server name.

The second stored procedure, sp_adddistributiondb, creates the distribution database

with the specified parameters. If you want to use a distribution database with multiple

data files or filegroups, first create the database by using a CREATE DATABASE state￾ment and set the name in the @database parameter. In addition, sp_adddistributiondb

uses retention parameters to control how many hours SQL Server stores transactions

in the database before it erases them (this affects only transactional replication). If the

Distribution Agent fails to copy the transactions within the maximum specified

C1962271X.fm Page 709 Friday, April 29, 2005 8:04 PM

710 Chapter 19 Managing Replication

period, SQL Server marks the subscription as inactive, and the Snapshot Agent reini￾tializes the database. Increasing this value increases the space required to hold the

transactions, but it can help you avoid making full copies of the publication again,

thus losing the advantage of using transactional replication.

The third procedure in the script is sp_adddistpublisher. This procedure, executed at

the Distributor, configures a Publisher to use the distribution database. The script also

uses the sp_addextendedproperty or the sp_updateextendedproperty stored procedure to

store the Snapshot folder path as an extended property.

NOTE Disabling publishing

If you want to disable the publishing on a server, right-click the Publication folder and choose

Disable Publishing And Distribution.

Quick Check

■ Which type of replication is more demanding of the distribution database?

Quick Check Answer

■ Transactional replication is more demanding on the Distributor and the

distribution database, which stores the data captured from the transaction

log for use by transactional replication processes.

How to Create a Publication

After you have configured the Publisher to use a specific Distributor, the next step in

setting up replication is to create the publication you want to publish. Here are the

steps for creating a publication:

1. Open SSMS.

2. Connect to the database engine instance in which you want to create the publi￾cation.

3. Expand the Replication, Local Publications folder.

4. Right-click the Local Publications folder and choose New Publication.

5. On the New Publication Wizard page, click Next.

6. On the Publication Database page, select the database in which you want to cre￾ate the publication. Click Next.

C1962271X.fm Page 710 Friday, April 29, 2005 8:04 PM

Lesson 2: Setting Up Replication 711

7. On the Publication Type page, (shown in Figure 19-8), select the type of publi￾cation you want to use (Snapshot Publication, Transactional Publication, Trans￾actional Publication With Updatable Subscriptions, or Merge Publication). Click

Next.

Figure 19-8 Configuring publication type

8. On the Articles page, select the check boxes for the database objects you want to

publish. Keep in mind that if you choose objects such as a stored procedure,

view, indexed view, or user-defined function (UDF), you must also publish the

objects on which those objects depend. For example, if you choose a stored pro￾cedure that references two tables, you must include those two tables in the pub￾lication. Click Next.

9. On the Filter Table Rows page, you can create a row filter to filter the table you

publish. To configure a row filter, click Add. Use the Add Filter dialog box to

define the filter, click OK, and click Next.

NOTE Setting up filters

The Publication Wizard offers two pages that let you set up filters. If you want to filter col￾umns, use the Articles page. If you want to filter by rows, use the Filter Table Rows page.

C1962271X.fm Page 711 Friday, April 29, 2005 8:04 PM

712 Chapter 19 Managing Replication

10. On the Snapshot Agent page, select the Create A Snapshot Immediately And

Keep The Snapshot Available To Initialize Subscriptions check box to create a

snapshot now. Select the Schedule The Snapshot Agent To Run At The Following

Times check box. By default, the New Publication Wizard configures the Snap￾shot Agent to run on an hourly basis. If you want to change this schedule, click

Change to define a new schedule. Click OK to save your new schedule and then

click Next to continue.

BEST PRACTICES Executing the Snapshot Agent

Creating a snapshot can be a demanding process. You should configure the Snapshot Agent

to run only at off-peak times.

11. On the Agent Security page, click Security Settings to open the Snapshot Agent

Security dialog box. Use the options in this dialog box to assign the account by

which you want to run the Snapshot Agent process and connect to the Publisher.

Click OK to close the Snapshot Agent Security dialog box and then click Next.

MORE INFO Security

You can configure the Snapshot Agent to run under the SQL Server Agent service account.

However, this setup is not recommended because it does not follow the principle of least

privilege. For details about how to provide a secure environment for replication, see Lesson 3

of this chapter.

12. On the Wizard Actions page, you can use the available check boxes to indicate

whether you want SSMS to execute the commands now, script them, or both. By

default, the Create The Publication check box is selected. Click Next.

13. If you chose to script the commands, you now see the Script File Properties page.

You use this page to configure the name, path, and format of the script. By

default, SQL Server creates this script file in your My Documents folder. Click

Next.

14. On the Complete The Wizard page, type a name for your publication in the Pub￾lication Name text box. Review the summary of your choices, and click Finish to

create the publication.

15. Wait for the New Publication Wizard to create the publication. After it com￾pletes, click Close.

C1962271X.fm Page 712 Friday, April 29, 2005 8:04 PM

Lesson 2: Setting Up Replication 713

The following code block shows the publication configuration script that the New

Publication Wizard generates:

Publication Configuration Script

use [AdventureWorksRepl]

exec sp_replicationdboption @dbname = N'AdventureWorksRepl'

, @optname = N'publish'

, @value = N'true'

GO

-- Adding the snapshot publication

use [AdventureWorksRepl]

exec sp_addpublication @publication = N'MSPressRepl'

, @description = N'Snapshot publication of database ''AdventureWorksRepl'' from Publisher

''COMPUTERNAME''.'

, @sync_method = N'native'

, @retention = 0

, @allow_push = N'true'

, @allow_pull = N'true'

, @allow_anonymous = N'true'

, @enabled_for_internet = N'false'

, @snapshot_in_defaultfolder = N'true'

, @compress_snapshot = N'false'

, @ftp_port = 21

, @allow_subscription_copy = N'false'

, @add_to_active_directory = N'false'

, @repl_freq = N'snapshot'

, @status = N'active'

, @independent_agent = N'true'

, @immediate_sync = N'true'

, @allow_sync_tran = N'false'

, @allow_queued_tran = N'false'

, @allow_dts = N'false'

, @replicate_ddl = 1

GO

exec sp_addpublication_snapshot @publication = N'MSPressRepl'

, @frequency_type = 4

, @frequency_interval = 1

, @frequency_relative_interval = 1

, @frequency_recurrence_factor = 0

, @frequency_subday = 1

, @frequency_subday_interval = 1

, @active_start_time_of_day = 0

, @active_end_time_of_day = 235959

, @active_start_date = 0

, @active_end_date = 0

, @job_login = null

, @job_password = null

, @publisher_security_mode = 1

use [AdventureWorksRepl]

exec sp_addarticle @publication = N'MSPressRepl'

, @article = N'SalesOrderDetail'

, @source_owner = N'Sales'

C1962271X.fm Page 713 Friday, April 29, 2005 8:04 PM

714 Chapter 19 Managing Replication

, @source_object = N'SalesOrderDetail'

, @type = N'logbased', @description = null

, @creation_script = null

, @pre_creation_cmd = N'drop'

, @schema_option = 0x000000000803509D

, @identityrangemanagementoption = N'manual'

, @destination_table = N'SalesOrderDetail'

, @destination_owner = N'Sales'

, @vertical_partition = N'false'

GO

use [AdventureWorksRepl]

exec sp_addarticle @publication = N'MSPressRepl'

, @article = N'SalesOrderHeader'

, @source_owner = N'Sales'

, @source_object = N'SalesOrderHeader'

, @type = N'logbased'

, @description = null

, @creation_script = null

, @pre_creation_cmd = N'drop'

, @schema_option = 0x000000000803509D

, @identityrangemanagementoption = N'manual'

, @destination_table = N'SalesOrderHeader'

, @destination_owner = N'Sales'

, @vertical_partition = N'false'

GO

As with the previous script for creating the Distributor, this script contains parameters

that are specific to your environment. The script that the New Publication Wizard gen￾erates uses four stored procedures to create the publication configuration. The first

procedure, sp_replicationdboption, enables replication in a database. The parameter

@optname supports the following values: merge publish for merge replication, publish

for snapshot and transactional replication, subscribe for subscription, and sync with

backup for a special type of transactional replication that forces backups of the trans￾action log before sending transactions to the distribution database.

The sp_addpublication stored procedure creates the publication when the publication

type is snapshot or transactional. The @sync_method parameter specifies the format

the bulk copy files use. Use native format when the replication includes only SQL

Server subscriptions, and use character format when other platforms (such as

Microsoft Office Access, Oracle, or IBM DB2) subscribe to the publication.

You use the parameters @enabled_for_internet, @ftp_port, @ftp_address,

@ftp_subdirectory, @ftp_login, and @ftp_password when subscribers use the Internet to

connect for replicating the database. The @enabled_for_internet parameter enables the

configuration, and the rest of the parameters set the configuration of the Snapshot

folder.

C1962271X.fm Page 714 Friday, April 29, 2005 8:04 PM

Lesson 2: Setting Up Replication 715

The sp_addpublication_snapshot stored procedure configures the job that runs the

Snapshot Agent. You configure the job schedule by using the following parameters:

@frequency_type, @frequency_interval, @frequency_relative_interval, @frequency_recurrence_

factor, @frequency_subday, @frequency_subday_interval, @active_start_time_of_day, @active_

end_time_of_day, @active_start_date, and @active_end_date. In the sample script, the Snap￾shot Agent job is set to run once a day every day.

MORE INFO Schedules

If you want a better understanding of the schedule parameters that the Snapshot Agent job uses,

review the documentation about sp_add_schedule in SQL Server 2005 Books Online. To gain a good

understanding of the parameter semantics, you can create a job with multiple schedules and generate

a script to review the resulting parameter settings.

The last three parameters of sp_addpublication_snapshot—@job_login, @job_password,

and @publisher_security_mode—set the security context of the job. You will find more

information about replication security in the next lesson.

Finally, the sp_addarticle stored procedure is executed multiple times, once per article

in the publication, to configure the database objects that will be published. You con￾figure the publication, article name, and object to publish by using the parameters

@publication, @article, @source_owner, and @source_object. When you want to create a

script that configures a large number of articles with the same options, copy and paste

this procedure, replacing the parameter values with the appropriate object names.

The sp_addarticle @type parameter sets what will be published: the schema, the data,

or the execution. For example, to publish table or view data, use the logbased value; to

copy the schema of a stored procedure or view, use proc schema only or view schema

only, respectively; and to replicate the execution of the stored procedure, use proc exec.

NOTE Article types

Some Subscribers support only certain article types. For example, non-SQL Server Subscribers do

not support schema-only or stored procedure replication. So take your environment into consider￾ation before specifying article types.

How to Subscribe to the Publication

The final step in the replication configuration process is configuring the Subscriber to

receive the publication. To configure a subscription for a Subscriber, follow these steps:

1. Open SSMS.

2. Connect to the Publisher database engine instance.

C1962271X.fm Page 715 Friday, April 29, 2005 8:04 PM