May 28, 2009 at 10:11 pm
Hi,
I have a SQL server 2000 instance on a server that acts as both publisher and distributor and another SQL server 2000 instance that acts as a subscriber to the above mentioned SQL server 2000 instance. Now I want to migrate both of these instances to separate SQL Server 2005 instances.
Could any one please tell me how to migrate these Replication settings onto the SQL Server 2005 instance so that the replication still works in the new SQL Server 2005 instance without any problems?
Should I setup the replication right from the scratch or is there any scripting method available?
Thank you,
Yours
SQL Buddy
May 29, 2009 at 4:37 am
Having been through this exercise myself, I would recommend starting from scratch. Generating a script from Enterprise Manager will give you a lot of grief because you potentially wind up with some undesirable settings when running the script into a SQL Server 2005 environment. I seem to remember pointing Management Studio at my SQL Server 2000 server and even that didn't generate what I wanted for my environment.
It may be that it will be okay for you, but if you haven't got too much to set up I'd start from scratch.
As an example of "the grief" I suffered:
we use custom stored procedures in all our replication and in our SQL Server 2000 publication we had put dbo. in front of all the procedure names. When the generated script was run into our new SQL Server 2005 environment, dear old SQL Server decided to put square brackets round the procedure name so dbu.up_myproc became [dbo.up_myproc]. So all of a sudden, we started seeing "cannot find procedure dbo.myproc". After a bit of head scratching, I realised that the square brackets were causing the dbo. bit not be seen as the owner qualifier but part of the actual procedure name. So I had to modify the scripts to change [dbo.up_myproc] to [dbo].[up_myproc].
There are other such little "niceties" to get you fuming but it's all good fun.
May 29, 2009 at 8:19 am
Hi SSC Veteran,
I will try to set up the replication right from the scratch.
Thank you very much for your help. I really appreciate it.
Yours
SQLBuddy
May 29, 2009 at 8:33 am
If it's not too muck work, I think you are making the right decision.
If you get round to generating scripts using SQL Server 2005 Management Studio against your SQL Server 2005 environment you will notice some significant differences. We use SQL Server Authentication for our Log Reader and Distribution Agents. One of the traps I fell into was that I didn't spot that some of the sp calls are now generated with login and password parameters supplied. For obvious security reasons the password parameter values are set to NULL rather than the actual password. So if you take the script and run it elsewhere as is, your agents fail to login. Easily fixed but just a pain. However, I guess if you use Windows authentication then it's not a problem.
Good luck.
Mike
May 29, 2009 at 8:53 am
Hi Mike,
As there is not too much work, I will try to setup the replication right from the scratch.
You are right. Even in my case the agents are using SQL Server authentication. Also, in the scripts generated, the passwords are usually set to NULL. They are the big Caveats.
You advices are very helpful. I really appreciate your help.
Thank you very much,
Yours
Varma.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply