November 19, 2010 at 11:44 pm
I have a migration I need to setup but since my brain has moved on to newer technologies, I am looking for some ideas about getting the migration done. I have SQL 2000 on a main office server which does merge replication with the SQL 2000 servers in the branches. I want to bring a new SQL 2008 server online at the main office and have it live getting copies of the merge data so that I can ship new 2008 machines to the branches. However, peer to peer is not available for SQL 2000 and it appears that you cannot have a publisher of an article also be a subscriber and I cannot subscribe at the 2008 box to the existing 2000 publisher. Any simple way to swing from 2000 main office/2000 branch merge to 2008 main office/2000 branch merge? It is beginning to look like I have to swing all of the branch nodes at once to quit merge with the 2000 box and merge with the 2008 box. And no 2008 cannot subscribe to a 2000 publisher...
Thanks!
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
November 21, 2010 at 8:41 pm
So I gave up daisy chaining in favor of scripting out deletion of the old merge articles between 2000 and 2000 and creating new merges between 2008 and 2000 stores. I made a script, all of the objects I expect to see appear but no replication. I have snapshot and merge jobs, publication and subscription, no errors when it runs. Any ideas? Here is my script.
--delete 2000 to 2000 merge replication dist, pub and subs
:connect SQL2KA
use [master]
exec sp_dropdistributor @no_checks = 1
GO
-- Dropping the merge pull subscription
/****** Begin: Script to be run at Subscriber ******/
:connect SQL2KA\INSTANCE2
use [NW]
exec sp_dropmergepullsubscription @publisher = N'SQL2KA', @publisher_db = N'Northwind', @publication = N'Northwind'
GO
/****** End: Script to be run at Subscriber ******/
/****** Begin: Script to be run at Publisher ******/
/*use [Northwind]
exec sp_dropmergesubscription @subscription_type = N'pull', @publication = N'Northwind', @subscriber = N'SQL2KA\INSTANCE2', @subscriber_db = N'NW'
*/
/****** End: Script to be run at Publisher ******/
--create 2008 to 2000 merge replication for customer table in Northwind
/****** Scripting replication configuration. Script Date: 11/21/2010 7:27:49 PM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Installing the server as a Distributor. Script Date: 11/21/2010 7:27:49 PM ******/
:connect SQL2K8B
use master
exec sp_adddistributor @distributor = N'SQL2K8B', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\sql2k8b\ReplData', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', N'\\sql2k8b\ReplData', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'SQL2K8B', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'\\sql2k8b\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
use [Northwind]
exec sp_replicationdboption @dbname = N'Northwind', @optname = N'merge publish', @value = N'true'
GO
-- Adding the merge publication
use [Northwind]
exec sp_addmergepublication @publication = N'Northwind', @description = N'Merge publication of database ''Northwind'' from Publisher ''SQL2K8B''.', @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_subdirectory = N'ftp', @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 = null, @publication_compatibility_level = N'80RTM', @replicate_ddl = 0, @allow_subscriber_initiated_snapshot = N'false', @allow_web_synchronization = N'false', @allow_partition_realignment = N'true', @retention_period_unit = N'days', @conflict_logging = N'publisher', @automatic_reinitialization_policy = 0
GO
exec sp_addpublication_snapshot @publication = N'Northwind', @frequency_type = 4, @frequency_interval = 14, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 1, @frequency_subday_interval = 5, @active_start_time_of_day = 500, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = N'petertrast\sql2000', @job_password = null, @publisher_security_mode = 1
use [Northwind]
exec sp_addmergearticle @publication = N'Northwind', @article = N'Customers', @source_owner = N'dbo', @source_object = N'Customers', @type = N'table', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000B230034FF1, @identityrangemanagementoption = N'manual', @force_reinit_subscription = 1, @column_tracking = N'false', @subset_filterclause = null, @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
-----------------BEGIN: Script to be run at Publisher 'SQL2K8B'-----------------
use [Northwind]
exec sp_addmergesubscription @publication = N'Northwind', @subscriber = N'SQL2KA\INSTANCE2', @subscriber_db = N'NW', @subscription_type = N'pull', @subscriber_type = N'local', @subscription_priority = 0, @sync_type = N'Automatic'
GO
-----------------END: Script to be run at Publisher 'SQL2K8B'-----------------
-----------------BEGIN: Script to be run at Subscriber 'SQL2KA\INSTANCE2'-----------------
:connect SQL2KA\INSTANCE2
use [NW]
exec sp_addmergepullsubscription @publisher = N'SQL2K8B', @publication = N'Northwind', @publisher_db = N'Northwind', @subscriber_type = N'Local', @subscription_priority = 0, @description = N'', @sync_type = N'Automatic'
exec sp_addmergepullsubscription_agent @publisher = N'SQL2K8B', @publisher_db = N'Northwind', @publication = N'Northwind', @subscriber_security_mode = 1, @subscriber_login = N'', @subscriber_password = null, @distributor = N'SQL2K8B', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 1, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20101121, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @offloadagent = N'False', @offloadserver = N'', @publisher_security_mode = 1, @publisher_login = null, @publisher_password = null, @use_interactive_resolver = N'False', @dynamic_snapshot_location = null
GO
-----------------END: Script to be run at Subscriber 'SQL2KA\INSTANCE2'-----------------
Peter Trast
Microsoft Certified ...(insert many literal strings here)
Microsoft Design Architect with Alexander Open Systems
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply