Hi,
I have the same database(3GB) in 2 physical locations in usa and europe
I need to create a publisher and subscriber between these 2 database without sending any snapshot because i have the same data in the 2 sides, is it possible ?
Thx
Jim
Yes it's possible. When you add a subscription through sp_addsubscription for transactinal publication, you can specify that the subscriber already has the schema and initial data by specify @.sync_type='none' or 'replication support only', for merge (sp_addmergepublication), you can specify @.sync_type='none'
Transactional publication:
[ @.sync_type=] 'sync_type'
replication support only
Provides automatic generation at the Subscriber of article custom stored procedures and triggers that support updating subscriptions. Assumes that Subscriber already has the schema and initial data for published tables. When configuring a peer-to-peer transactional replication topology, ensure that the data at all nodes in the topology is identical. Quiesce the replication topology before configuring peer-to-peer transactional replication using this option. For more information, see How to: Quiesce a Replication Topology (Replication Transact-SQL Programming).
Merge:
[ @.sync_type=] 'sync_type'
Is the subscription synchronization type. sync_type is nvarchar(15), with a default of automatic. Can be automatic or none. If automatic, the schema and initial data for published tables are transferred to the Subscriber first. If none, it is assumed the Subscriber already has the schema and initial data for published tables. System tables and data are always transferred.
for more information, see SQL Server 2005 books online:
http://msdn2.microsoft.com/en-us/library/ms181702.aspx
http://msdn2.microsoft.com/en-us/library/ms187717.aspx
Thanks,
Zhiqiang Feng
|||
Hi,
i did what you said above but i ve got the following error while synchronization on the subscriber:
{call sp_MSsetconflicttable (N'Customer', N'MSmerge_conflict_testwithoutsnap_Customer', N'myserver', N'testwithoutsnapshot', N'testwithoutsnap')}
merge publication for customer table:
-- Enabling the replication database
use master
exec sp_replicationdboption @.dbname = N'testwithoutsnapshot', @.optname = N'merge publish', @.value = N'true'
GO
-- Adding the merge publication
use [testwithoutsnapshot]
exec sp_addmergepublication @.publication = N'testwithoutsnap', @.description = N'Merge publication of database ''testwithoutsnapshot'' from Publisher ''myserver''.', @.sync_mode = N'native', @.retention = 14, @.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', @.dynamic_filters = N'false', @.conflict_retention = 14, @.keep_partition_changes = N'false', @.allow_synctoalternate = N'false', @.max_concurrent_merge = 0, @.max_concurrent_dynamic_snapshots = 0, @.use_partition_groups = N'false', @.publication_compatibility_level = N'90RTM', @.replicate_ddl = 1, @.allow_subscriber_initiated_snapshot = N'false', @.allow_web_synchronization = N'false', @.allow_partition_realignment = N'true', @.retention_period_unit = N'days', @.conflict_logging = N'both', @.automatic_reinitialization_policy = 0
GO
exec sp_addpublication_snapshot @.publication = N'testwithoutsnap', @.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 = 500, @.active_end_time_of_day = 235959, @.active_start_date = 0, @.active_end_date = 0, @.job_login = null, @.job_password = null, @.publisher_security_mode = 1
exec sp_grant_publication_access @.publication = N'testwithoutsnap', @.login = N'sa'
GO
exec sp_grant_publication_access @.publication = N'testwithoutsnap', @.login = N'NT AUTHORITY\SYSTEM'
GO
exec sp_grant_publication_access @.publication = N'testwithoutsnap', @.login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access @.publication = N'testwithoutsnap', @.login = N'myserver\SQLServer2005SQLAgentUser$myserver$MSSQLSERVER'
GO
exec sp_grant_publication_access @.publication = N'testwithoutsnap', @.login = N'myserver\SQLServer2005MSSQLUser$myserver$MSSQLSERVER'
GO
exec sp_grant_publication_access @.publication = N'testwithoutsnap', @.login = N'distributor_admin'
GO
-- Adding the merge articles
use [testwithoutsnapshot]
exec sp_addmergearticle @.publication = N'testwithoutsnap', @.article = N'Customer', @.source_owner = N'dbo', @.source_object = N'Customer', @.type = N'table', @.description = N'', @.creation_script = N'', @.pre_creation_cmd = N'none', @.schema_option = 0x0000000004034FD1, @.identityrangemanagementoption = N'none', @.destination_owner = N'dbo', @.force_reinit_subscription = 1, @.column_tracking = N'false', @.subset_filterclause = N'', @.vertical_partition = N'false', @.verify_resolver_signature = 1, @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true', @.check_permissions = 0, @.subscriber_upload_options = 0, @.delete_tracking = N'true', @.compensate_for_errors = N'false', @.stream_blob_columns = N'false', @.partition_options = 0
GO
use [testwithoutsnapshot]
exec sp_changemergepublication N'testwithoutsnap', N'status', N'active'
GO
-- Adding the merge subscriptions
use [testwithoutsnapshot]
exec sp_addmergesubscription @.publication = N'testwithoutsnap', @.subscriber = N'myserver', @.subscriber_db = N'testwithoutsnapshot1', @.subscription_type = N'Pull', @.sync_type = N'None', @.subscriber_type = N'Local', @.subscription_priority = 0, @.description = N'', @.use_interactive_resolver = N'False'
GO
for publication articel i used "Keep existing object unchanged" and create schema at subscriber =false
any suggestion?
Thx
Jim
|||
Hi,
any help?
I am still waiting ?
Regards,
Jim
|||Do you already have a guidcol in your publishing table and subscriber table? If not, how did you add it? The guidcol column value need to be exactly the same on publisher and subscriber table.
Did you run snapshot agent before merge agent?
I was playing with your script yesterday but hit some issues, I will let your know the outcome today.
Thanks,
Zhiqiang Feng
|||
If you don't have a rowguidcol column already on both side of your tables with identical column values, the recommend way is to add it on source db, then backup and restore the db to the destination server. Then from there you can setup no-sync merge replication between the 2 sites.
Or, you could add a new rowguidcol on source table, and find a way to just delivery the value of this new column to the destination table, this way you will avoid the overhead of delivering all 3GB data.
Thanks,
Zhiqiang Feng
|||
Thanks for you patience, I hit a similar error as you encountered. Will dig into it further tomorrow.
Zhiqiang Feng
|||
Hi,
Thx for your reply, i made a publication and i added a rowguid with null option in target data (with or without update rowguid from source DB) and later on a created the subscription , and the system gave me the same error (i can\t make backup on source db because because i can't send any data or any snapshot)
Any other idea ?
Regards,
Jim
|||Hi, Jim,
I think I know what happened, when you use alter table to add a rowguid column, did you add the rowguidcol property to the column? ROWGUIDCOL property is required by merge replication. And if you don't have rowguidcol property for the rowguid column on the destination table, merge replication will not work, of course, we should give a better error message instead of the weird sp_MSsetconflicttable error.
For example, you can use following statement to add rowguid column with rowguidcol property
alter table mytable add ourguid uniqueidentifier default newid() rowguidcol
Let me know if that's the problem you're encountering.
Of course, once you correct this, the other thing is to make sure you have the exact same rowguid column value on both source table and target table before setting up replication.Thanks,
Zhiqiang Feng
|||
The other solution to your case is to actually use normal merge publication, when you create a snapshot at publisher, you can specify to have the snapshot compressed to a local cab file, then copy this cab file to the other location (Europe in your case, or burn a DVD and deliver the DVD to Europe site), when you run merge agent at the subscriber for the first time, you could use the merge agent parameter -AltSnapshotFolder to specify the local copy snapshot cab file in Europe, this way the 3GB data is not replicated through wire instead it's on local network. This is recommended approach as there are several known issues in SQL 2005 for merge nosync replication and you don't want to hit them later on.
Thanks,
Zhiqiang Feng
|||hi Zhiqiang,
I tried this one: alter table mytable add ourguid uniqueidentifier default newid() rowguidcol
and it worked fine but you need to replace the empty "ourguid" filed in subscriber side from publisher side before adding rowguidcol property on the field.
but i have more than 400 table and i need to replace their related rowguidcol from publisher table, very hard way.
about your last post , i know about it but i don't need to send anything between 2 physical location.
Regards,
Jim
No comments:
Post a Comment