Attachable Subscribers in SQL Server Replication 2008/2005(Part 2)

This feature can be used when deploying a large number of pull subscriptions, which is common in merge replication. Microsoft has announced that Attachable Subscribers in SQL server 2005/2008 are deprecated features ,and this features will be removed in the next releases of SQL server 200x. for more information about this deprecated features information check http://msdn.microsoft.com/en-us/library/ms143550.aspx . Although that Microsoft tries to add alternative solution to MSF files that are created as result of copying the subscription databases (Databaese.msf file) after applying it ,it seems to be inaccurate and associated with incomplete solution with a lot of errors http://msdn.microsoft.com/en-us/library/ms152488.aspx .
- For informational and historical reasons I’ve add a referencing to the method of attachable subscribers in SQL Server 2000 to show the features that have been deprecated in SQL server 2005/08 you can skip this part and move to section B :

A- In SQL Server 2000 the scenario was as below:

On the publisher:
1. Create the database snapshot.
2. Back up the published database.
3. Create a temporary database and restore the published database on the temporary database.
4. Create a new 'no-sync' pull subscription from the temporary database to the published database.
5. Run the merge agent to synchronize the databases.
6. Copy the subscription databases (database.msf file)
7. Register the subscriber computer
8. Register the subscribing database using sp_addmergesubscription
On the subscriber:
1. Attach the subscription database copy (Database.msf)
2. Create a pull subscription using sp_addmergepullsubscription
3. Connect to the publisher using a dial up connection
4. Run the merge agent to synchronize the data.

B-In SQL Server 2008/05 :

-Subscribers could be initialized by using other subscribers replicated databases without direct publisher snapshot sachems:
1 backup subscriber database.
2 Attaches to a separated server backed up database with keeping replication information option check box checked to preserve replication information.

3 -Rowguid column in new attached Db show shared related publisher information.

4- Run generated scripts from old subscriber on a new subscriber to get publisher information such as name, server location, and login account to:
use [CommonDB_Sub]
exec sp_addmergepullsubscription
@publisher = N'ABDELRAHMAN', @publication = N'Pub_ItemSalesPrice',
@publisher_db = N'CommonDB', @subscriber_type = N'Local', @subscription_priority = 0,@description = N'', @sync_type = N'none'
exec sp_addmergepullsubscription_agent @publisher = N'ABDELRAHMAN',
@publisher_db = N'CommonDB',
@publication = N'Pub_ItemSalesPrice', @distributor = N'ABDELRAHMAN',
@distributor_security_mode = 0, @distributor_login = N'sa',
@distributor_password = N'nsg_ss_0103', @enabled_for_syncmgr = N'False',
@frequency_type = 1, @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 = 0, @active_end_date = 0, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publisher_security_mode = 0, @publisher_login = N'sa', @publisher_password = N'nsg_ss_0103', @use_interactive_resolver = N'False', @dynamic_snapshot_location = N'',@use_web_sync = 0

GO
Note : its important to set the prameter of both stored procdsure sp_addmergepullsubscription , sp_addmergepullsubscription_ agent,its important to set @sync_type = N'none' to create subscriber without publisher installation also set @alt_snapshot_folder a path of the replication data after copying unc folder from the publisher .

6 - New subscriber is generated that involve the publisher informations.

7 .In order to register the subscriber information to the publisher you need to run the following script :

use [CommonDB]
exec sp_addmergesubscription @publication = N'Pub_ItemSalesPrice',
@subscriber = N'ABDELRAHMAN\MSSQL2', @subscriber_db = N'CommonDB_Sub', @subscription_type = N'pull', @subscriber_type = N'local', @subscription_priority = 0, @sync_type = N'none'

This will create subscriber to the publisher database

8. start pull data between publisher and subscriber, in our case we need to pull data from publisher through start button in View synchronizations status:

You can see all agent history by press view Job History.

C-Testing Replication Synchronization in SQLserver 2008/05

1. Add new row to the attached subscriber and press view sync status and start sync agent.