July 27, 2012 at 12:03 pm
i want to create a new distribution database and make the present publisher use this new distribution database.
sp_adddistributiondb
How can we achieve it ? What changes do i need to make at the publisher to use this newly created database as the distribution database? Can we select the distributor database? I am working on a upgrade plan where i want to retain the replication settings in case of rollback, so that we don't have to configure replication again ?
August 10, 2012 at 12:19 am
Hi Muthyla,
Let me know one thing, you are creating Distributor DB 1st time in that server, if NO mean ok.
If you mean better you old Distributor DB.
Thanks
Satish
August 10, 2012 at 8:06 am
muthyala_51 (7/27/2012)
i want to create a new distribution database and make the present publisher use this new distribution database.sp_adddistributiondb
How can we achieve it ? What changes do i need to make at the publisher to use this newly created database as the distribution database? Can we select the distributor database? I am working on a upgrade plan where i want to retain the replication settings in case of rollback, so that we don't have to configure replication again ?
This is going to be a complex change as there is a lot of meta data stored in there so there cannot be a straight swap.
A distributor can only have a single distribution database. You cannot select it as part of a publisher set up.
The only way is to drop all replication from all publishers and the distributor. You then have to recreate it.
This can be done via script without the need to reinitialise all subscribers if you change the @sync_type option
August 10, 2012 at 8:28 am
MysteryJimbo (8/10/2012)
A distributor can only have a single distribution database. You cannot select it as part of a publisher set up.
This is incorrect.
A distributor may have multiple distribution databases. They can each have different retention settings as well. A publisher is bound to a particular distribution database on the publishers tab of the distributor properties dialog.
MysteryJimbo (8/10/2012)
The only way is to drop all replication from all publishers and the distributor. You then have to recreate it.
Agreed, you cannot change the distribution database once set on the publishers tab as its greyed out. You may uncheck and remove the publisher from the distributor but as the warning dialogs informs you, all publications etc will be permanently deleted. It's a really fundamental change
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 10, 2012 at 8:35 am
Perry Whittle (8/10/2012)
MysteryJimbo (8/10/2012)
A distributor can only have a single distribution database. You cannot select it as part of a publisher set up.This is incorrect.
A distributor may have multiple distribution databases. They can each have different retention settings as well. A publisher is bound to a particular distribution database on the publishers tab of the distributor properties dialog.
Apologies. I misread books online when I last looked at it.
August 10, 2012 at 8:40 am
no worries. You're bang on the money about the cure\fix, I hope the OP realises what a mammoth task they have if they want to change the dist database.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 10, 2012 at 8:43 am
muthyala_51 (7/27/2012)
i want to create a new distribution database and make the present publisher use this new distribution database.
This is bugging me :ermm:
but why do you want the present publisher to use a new database, any particular reason for potentially giving yourself a shed load of work?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 14, 2012 at 1:49 pm
Apologies for delay response.
Thanks to everyone for sharing their valuable thoughts. We just want to do this to retain the replication settings. But I realised it's not possible. I came up with a work around for this problem. We can go for another instance or use the existing database only by renaming the publications from the new server.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply