- Configuring a Distributor
- Creating a Publication
- Creating Subscriptions
- Testing the Replication
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.
)
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.
)
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.
)
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.
)
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.
)
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