Moving a SQL2K publisher to a SQL 2005 instance.

Hi,

I have to move a SQL2K instance (publisher) to a SQL 2005 instance and i prefer not to re-snapshot all the databases being replicated due to the large size of the databases. As i understand all i need to do is

1. Ensure all published databases are in readonly mode.

2. Stop the replication (by either dropping subscriptions, publication or stopping replication agents).

3. Move the databases to the SQL 2005 instance.

4. Configure a SQL 2005 instance for the distributor (for a SQL 2005 publisher, the distribution database must be a SQL 2005 instance).

5. Setup replication to the SQL2K subscriber but not "initialise" each subscription with a snapshot of the publication data and schema.

Are these the only issues i need to consider?
Any comments greatly appreciated.
Thanks,
Priyanga
Is this merge replication or Transactional.
If it is merge, snapshot will need to be generated no matter how you plan to move to SQL Server 2005.
If it is Transactional, I will need to check/verify.|||Sorry, i forgot to mention.

Its for transactional replication.

Cheers,
Priyanga
|||In SQL Server 2005 Books Online, please take a look at topic:

"Initializing a Transactional Subscription Without a Snapshot".

|||Thanks Greg. This is what i have been looking for.

Cheers,
Priyanga
|||

Hi Greg,

I have encountered a problem implementing this solution....

Not initialising the subscription upon its creation at the subscriber enables me to not have to re-snapshot the publication but this does not create the system procedures on the subscribed database which SQL 2005 needs to support replication.

For example, with a SQL 2000 replication topology, it executes [dbo].[sp_MSins_Customers] to do an insert to the Customer table at the subscriber but SQL 2005 executes [dbo].[sp_MSins_dboCustomers] which it cannot find unless you reinitialise from a snapshot. We do not want to do this as we’ll need to snapshot about 100GB of data.

Assistance on this matter is greatly appreciated.

Cheers,
Priyanga

|||

You may need to create these procs at the subscriber manually, if you havent have them on th subscribers.

|||Hi Mahesh,

There could be some issues if i create these procedures manually on the subscriber. For example, since the naming of the procedures differ is it possible that the logic of these procedures also differ.

Why doesn't the process of creating a subscription at the subscriber re-create the new procedures which SQL 2005 needs for replication?

I think this should be flagged as a bug as there is no mention that it can't be done in BOL in 2005.

Cheers,
Priyanga|||Hi Priyanga,

You can specify @.sync_type to 'replication support only' when sp_addsubscription is called. It will create appropriate replication system procedure at subscriber.

BOL has detailed instruction to help you do that. Look at topic "How to: Initialize a Subscription Manually (SQL Server Management Studio)" or "How to: Initialize a Subscription Manually (Replication Transact-SQL Programming)", depending you prefer to use UI or procedure.

Please let me know if it works for you.

Peng|||

Priyanga Karunathilake wrote:

I have encountered a problem implementing this solution....

Oops, looks like you already tried with sync_type as either 'replication support only' or 'initialize with backup'. Replication should create system procs on the subscriber database for both case. Have you run logreader and distribution agent after create subscription? Is it a push or pull subscription?|||

Hi Peng,

The log reader and distribution agents are both running after I setup the subscription. It is a pull subscription.

When I try to set up this scenario where I do a pull subscription without initializing the subscription, the new SQL 2005 distribution stored procedures don’t get created on the subscriber. All I see are sp_MSxxx_tablename type stored procedures which is from the SQL 2000 replication and hense I get the error cannot find sp_MSxxx_dbotablename procedure. Note the dbo in the SQL 2005 procedures.

When I do a push subscription without initializing, it successfully creates the SQL 2005 distribution stored procedures on the subscriber. Also, when I do a pull subscription WITH initialize it creates the SQL 2005 distribution stored procedures on the subscriber.

Cheers,

Priyanga

|||Hi Priyanga,

Pull subscription should also creates the system procs on subscriber DB. The name of the procs depends on parameters @.ins_cmd/@.upd_cmd/@.del_cmd when article is created (i.e. sp_addarticle). The default is sp_MSxxx_dbotablename for SQL 2005. How do you create the replication? Through UI and script? You can query sysarticles on publication DB to get the CALL format you are using.

Another piece of information that might be useful is the @.schema_option of the article.

For the subscriber, is it SQL 2000 or 2005?

-Peng

|||Hi Peng,

I am creating the replication using UI.

The subscriber is a SQL 2000 instance. I scripted out the commands during the pull subscription creation.

What i don't understand is why the distribution procedures don't get created for a pull subscription when the subscription is not initialised. They get created for all other scenarios. I'am beginning to think this is a bug in SQL 2005.

Thanks for you assistance.

Priyanga

--BEGIN: Script to be run at Publisher 'INSPIRON\SQL01'--

use [NorthwindPK]

exec sp_addsubscription @.publication = N'NorthwindPK_SQL2005', @.subscriber = N'INSPIRON\SQL02', @.destination_db = N'NorthwindPK', @.sync_type = N'replication support only', @.subscription_type = N'pull', @.update_mode = N'read only'

GO

--END: Script to be run at Publisher 'INSPIRON\SQL01'--

--BEGIN: Script to be run at Subscriber 'INSPIRON\SQL02'--

use [NorthwindPK]

exec sp_addpullsubscription @.publisher = N'INSPIRON\SQL01', @.publication = N'NorthwindPK_SQL2005', @.publisher_db = N'NorthwindPK', @.independent_agent = N'True', @.subscription_type = N'pull', @.description = N'', @.update_mode = N'read only', @.immediate_sync = 1

exec sp_addpullsubscription_agent @.publisher = N'INSPIRON\SQL01', @.publisher_db = N'NorthwindPK', @.publication = N'NorthwindPK_SQL2005', @.subscriber_security_mode = 1, @.subscriber_login = N'', @.subscriber_password = null, @.distributor = N'INSPIRON\SQL01', @.distributor_security_mode = 1, @.distributor_login = N'', @.distributor_password = null, @.enabled_for_syncmgr = N'False', @.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 = 20051014, @.active_end_date = 99991231, @.alt_snapshot_folder = N'', @.working_directory = N'', @.use_ftp = N'False', @.offloadagent = N'False', @.offloadserver = N'', @.publication_type = 0

GO

--END: Script to be run at Subscriber 'INSPIRON\SQL02'--

|||

Priyanga,

Since the distribution agent on your pull subscriber is effectively SQL2000 binary from way back, it doesn't really understand the commands posted to MSrepl_commands for creating the custom procedures as provided by the 'replication support only' @.sync_type since this is a SQL2005 feature. I can understand your disappointment, but we can't really "fix" this by changing anything in SQL2005.

-Raymond

|||Hi Raymond.

I guess i have to do a full initialise of the subscription when we move to SQL 2005 for our publisher and distributor.....

Do you know whether significant performance enhancements have been made to the speed of initialising a subscription from snaphot?
Taking into consideration that i will be going from a SQL 2005 Publisher and Distributor on separate servers to SQL 2000 subscriber, and have to initialise 150GB of data, would you have an approximate time it might take? The only environemnt stats i can give you is that we have a gigabit network connection, publisher and distributor are 64bit with 16GB memory, all servers connected to SAN and that it would be performed during a scheduled downtime?

Cheers,
Priyanga