Configuring a Distributor

Figure 1

Figure 2

Figure 3

Figure 4

Figure 5

Figure 6

Listing 1 Sample script for configuring a distributor and adding a publisher.

use master
GO
exec sp_adddistributor @distributor = N’server\instance’, @password = N’’
GO

/* create and configure distribution database */

exec sp_adddistributiondb @database = N’distribution’,
@data_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\Data’,
@data_file_size = 4, @log_folder = N’C:\Program Files\Microsoft SQL Server\MSSQL.4\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:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\ReplData’,
’user’, dbo, ’table’, ’UIProperties’
else
 EXEC sp_addextendedproperty N’SnapshotFolder’,
 ’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\ReplData’, ’user’, dbo, ’table’,
 ’UIProperties’
GO

/* add a publisher */

exec sp_adddistpublisher @publisher = N’server\instance’, @distribution_db = N’distribution’,
@security_mode = 1,
@working_directory = N’C:\Program Files\Microsoft SQL Server\MSSQL.4\MSSQL\ReplData’,
@trusted = N’false’, @thirdparty_flag = 0, @publisher_type = N’MSSQLSERVER’
GO

Figure 7

Creating a Publication

• Snapshot Publication
• Transactional Publication
• Transactional Publication with Updatable Subscriptions
• Merge Publication

Figure 8

Figure 9

• Destination Object Name, Destination Object Owner. The destination table isn’t required to have the same name or the same owner as the source object.
• Convert Data Types. This option automatically changes a user-defined data type to the base data type, because the user-defined data type might not exist on the subscriber(s).
• Convert TIMESTAMP to BINARY. When replicating a column with a TIMESTAMP data type, you can convert it to BINARY. The TIMESTAMP data type tracks the sequence of modifications; every time you change a data row, SQL Server will automatically change the value of the column with the TIMESTAMP data type. This is important because, if you’re not careful, you might end up with different values in the column with the TIMESTAMP data type on the publisher and the subscriber.
• Convert MAX Data Types to NTEXT and IMAGE. This option translates VARCHAR(MAX) and VARBINARY(MAX) data types, which are new in SQL Server 2005, to respective data types supported in previous versions.
• Convert XML to NTEXT. Translates the new XML data type to NTEXT.
• Another option that wasn’t available through wizards in previous versions of SQL Server is automatic identity range management. This option allows the database administrator to set the ranges of valid values for the identity column in the publisher and subscriber databases. For example, we could assign values 1,000,000 and greater to the publisher and 1 to 1,000,000 to the subscriber. When the publisher database reaches the upper limit for the identity range, it will automatically assign a new range so that publisher and subscriber identity values don’t overlap.
• The final group of options (not shown in Figure 9) determines how to replicate INSERT, UPDATE, and DELETE statements to the subscriber.

Figure 10

Figure 11

Figure 12

Listing 2 Script for creating the example publication.

use [AdventureWorksDW]
exec sp_replicationdboption @dbname = N’AdventureWorksDW’, @optname = N’publish’, @value = N’true’
GO
-- Adding the transactional publication
use [AdventureWorksDW]
exec sp_addpublication @publication = N’DimAccount’,
@description = N’Transactional publication of database ’’AdventureWorksDW’’ from Publisher ’’server\instance’’.’,
@sync_method = N’concurrent’, @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, @ftp_login = N’anonymous’, @allow_subscription_copy = N’false’, @add_to_active_directory = N’false’,
@repl_freq = N’continuous’, @status = N’active’, @independent_agent = N’true’, @immediate_sync = N’true’,
@allow_sync_tran = N’false’, @autogen_sync_procs = N’false’, @allow_queued_tran = N’false’, @allow_dts = N’false’,
@replicate_ddl = 1, @allow_initialize_from_backup = N’false’, @enabled_for_p2p = N’false’,
@enabled_for_het_sub = N’false’
GO


exec sp_addpublication_snapshot @publication = N’DimAccount’, @frequency_type = 4, @frequency_interval = 1,
@frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8,
@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 [AdventureWorksDW]
exec sp_addarticle @publication = N’DimAccount’, @article = N’DimAccount’, @source_owner = N’dbo’,
@source_object = N’DimAccount’, @type = N’logbased’, @description = null, @creation_script = null,
@pre_creation_cmd = N’drop’, @schema_option = 0x000000000803509F, @identityrangemanagementoption = N’manual’,
@destination_table = N’DimAccount’, @destination_owner = N’dbo’, @vertical_partition = N’false’,
@ins_cmd = N’CALL sp_MSins_dboDimAccount’, @del_cmd = N’CALL sp_MSdel_dboDimAccount’,
@upd_cmd = N’SCALL sp_MSupd_dboDimAccount’
GO

• General. Shows the publication’s name, description, type, and the database on which the publication is based. You can modify subscription expiration options from this page.
• Articles. Lets you review the published articles, modify their properties, or add new articles to the publication.
• Filter Rows. Allows you to create horizontal filters for articles.
• Snapshot. Enables you to specify the snapshot folder location, snapshot format, or additional scripts to be executed before and after applying the snapshot.
• FTP Snapshot. Settings that let you allow subscribers to download the snapshot from an FTP share and configure FTP security.
• Agent Security. Controls security settings for the log reader and snapshot agents.
• Publication Access List. Specifies SQL Server and Windows logins who have permissions to create and synchronize subscriptions.
• Subscription Options. Provides a multitude of options for subscribers to the current publication.

Creating Subscriptions

Unlike previous versions, of SQL Server 2005 allows you to use the same wizard to create either pull or push subscriptions. To invoke the new subscription wizard right-click the publication (or the local subscriptions folder) and choose New Subscriptions from the pop-up menu. After you get past the introductory screen, select the publication for which you want to create subscription(s). Next, indicate whether you want to use pull or push subscriptions (see Figure 13). Pull subscriptions reduce the load on the publisher, whereas push subscriptions make it easy to administer all subscriptions at the central location. For this example, I’ll use push subscriptions, but the wizard screens are nearly identical for pull subscriptions.

Figure 13

Next you choose a subscribing server and database, as shown in Figure 14. You can use an existing database or create a new database; if you choose to create a new database on the subscribing server, you’ll get the typical dialog box for creating databases. More interestingly, note that the wizard allows you to use a non-SQL Server subscriber. You can choose either an Oracle or IBM DB2 subscriber for push subscriptions; only SQL Server subscribers are supported if using pull subscriptions.

Figure 14

Non-SQL Server subscriptions have certain limitations and other considerations of which you need to be aware. If you plan to use a non-SQL Server subscription be sure to consult the Heterogeneous Database Replication topic in SQL Server Books Online.

After specifying the subscriber server and database, you need to configure distribution agent security (see Figure 15). I’ll discuss replication agent security in greater detail in another article. For now, keep in mind that you can either impersonate the SQL Server Agent or use a separate Windows login or SQL Server login for the distribution agent. For this example, I’ll use the SQL Server Agent service account for running the distribution agent and for connecting to the subscriber.

Figure 15

Now it’s time to define a synchronization schedule—how often you want the replicated transactions to be delivered to the subscriber(s). Replicating transactions continuously is the best option if you want to achieve minimal latency; however, this option requires more work on the publisher for push subscriptions and on the subscriber for pull subscriptions. Scheduled delivery is a good option if you want to minimize the load during business hours and deliver commands only at certain times each day. On-demand delivery can be a viable option if you want to synchronize your databases only occasionally.

After indicating the desired synchronization schedule, you can initialize the subscription database (see Figure 16). During initialization, replication creates the published objects’ schemas and copies data from the snapshot folder to the subscription database; in addition, the stored procedures used for replication are created in the subscriber database. In the dialog box, you can specify that you don’t want to initialize the subscriptions—this option is useful if the schema and data already exist on the subscriber. Other options are to initialize subscriptions immediately or at first synchronization—that is, the first time the snapshot agent runs.

Figure 16

You’re done specifying all the information that the wizard needs to create subscriptions. At this point, you have the option to script the subscription and/or to create subscriptions. The wizard allows you to review the summary of the steps it’s about to undertake before you click the Finish button.

Listing 3 shows a script for creating a subscription.

Listing 3 Sample script for creating a subscription.

-----------------BEGIN: Script to be run at Publisher ---------------
use [AdventureWorksDW]
GO
exec sp_addsubscription @publication = N’DimAccount’,
@subscriber = N’server\subscriber_instance’,
@destination_db = N’AdventureWorksDWSub’,
@subscription_type = N’Push’,
@sync_type = N’automatic’,
@article = N’all’,
@update_mode = N’read only’,
@subscriber_type = 0

exec sp_addpushsubscription_agent @publication = N’DimAccount’,
@subscriber = N’server\subscriber_instance’,
@subscriber_db = N’AdventureWorksDWSub’,
@job_login = null,
@job_password = null,
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20060627,
@active_end_date = 99991231,
@enabled_for_syncmgr = N’False’,
@dts_package_location = N’Distributor’

To review subscription properties, expand the publication found in the local publications folder, right-click the subscription, and select Properties from the pop-up menu. Figure 17 shows the resulting screen for this example.

Figure 17

If we check the stored procedures folder on the subscriber database, we’ll find three new procedures that will be used for delivering the replicated transactions:

• sp_MSupd_dboDimAccount
• sp_MSdel_dboDimAccount
• sp_MSins_dboDimAccount

Testing the Replication

UPDATE dimAccount
SET AccountDescription = ’work in progress’
WHERE AccountKey = 11

{CALL [sp_MSupd_dboDimAccount] (,,,,N’work in progress’,,,,,,11,0x1000)}

SELECT
 AccountDescription
FROM dimAccount
WHERE AccountKey = 11

Results:

AccountDescription
--------------------------------------------------
work in progress

ummary