Transactional Replication – Configure Subscriber

Unlike previous versions, of SQL Server 2005 allows you to use the same wizard to create either pull or push subscriptions. Perform the below steps to Configure Subscriber

Step1 : Connect to an instance (SansuSQLExpress) of SQL Server by using the SQL Server Management Studio (SSMS), navigate to the “replication” folder then local subscriptions folder, right-click this folder, and select New Subscriptions. This invokes the new publication wizard and if you dont ever want to see this screen again, simply check the option to skip the introductory screen in the future.

Step2 : Next you need to select the publication that will be used for this subscription. If there is more than one publication select the one that you want to use for this subscription and click Next.

Step3 : Next you are prompted to select the location where the distribution agents will run. 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, Ill use push subscriptions, but the wizard screens are nearly identical for pull subscriptions.

Step4 : Next you choose a subscribing server and database. You can use an existing database or create a new database; if you choose to create a new database on the subscribing server, youll 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.

Step5 : After specifying the subscriber server and database, you need to configure distribution agent security. For this example, Ill use the SQL Server Agent service account for running the distribution agent and for connecting to the subscriber.

Step6 : Now you need to define a synchronization schedulehow 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.

Step7 : After indicating the desired synchronization schedule, you can initialize the subscription database. 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 dont want to initialize the subscriptionsthis option is useful if the schema and data already exist on the subscriber.
Other options are to initialize subscriptions immediately or at first synchronizationthat is, the first time the snapshot agent runs.

Step8 : Next page asks for the option of scripting the subscriptions. If you choose it you need to provide the file name and path in which the script will be stored. Then the wizard presents a synopsis of the steps its about to undertake; once you click Finish, the wizard will create the script for adding a subscriber and/or save the script, depending on what you specified.

The script created will be as follows,

-----------------BEGIN: Script to be run at Publisher 'SANSU'-----------------
USE [AdventureWorksDW]
EXEC sp_addsubscription @publication = N'DimGeography', @subscriber = N'SANSUSQLEXPRESS', 
@destination_db = N'Geo', @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'DimGeography', @subscriber = N'SANSUSQLEXPRESS', @subscriber_db = N'Geo', @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 = 20071019, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', 
@dts_package_location = N'Distributor' GO
-----------------END: Script TO be run AT Publisher 'SANSU'-----------------

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

• sp_MSupd_dboDimGeography
• sp_MSdel_dboDimGeography
• sp_MSins_dboDimGeography