Categories
High Availability

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.

subsconfig_image024

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.

subsconfig_image025

 

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.

subsconfig_image026

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.

subsconfig_image027

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.

subsconfig_image028

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.

subsconfig_image029

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.

subsconfig_image030

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

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse.

I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members.

I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

*