Aanvik
asked on
SQL Server Replication : Add New subscription SQL Server 2005
Hi,
I have this situation where we have replication setup between 2 servers. This is Transactional replication. Now there's a requirement to add another subscription.
Now my question is do I have to break the existing replication and configure it all again or can I simply add another subscription and it should work fine? We have subscription and distribution setup on 1 server which is kinnda readonly server and Publication setup on a production box. Its pull subscription.
Any help would be appreciated.
I have this situation where we have replication setup between 2 servers. This is Transactional replication. Now there's a requirement to add another subscription.
Now my question is do I have to break the existing replication and configure it all again or can I simply add another subscription and it should work fine? We have subscription and distribution setup on 1 server which is kinnda readonly server and Publication setup on a production box. Its pull subscription.
Any help would be appreciated.
ASKER
Thx for the comments... But the issue is we have setup the snapshot folder like D:\App\Repl and not as a network folder. so how the initial snapshot would be applied... I m not sure.
Pls suggest.
Pls suggest.
try:
SSMS->right click replication->Distributor Properties->Click publishers-> near "Distribution Database"
Distribution (default one) click | ...|=- > here change Default Snapshot Folder e.g: \\servername\Visibaleshare
click 'OK' ...
SSMS->right click replication->Distributor Properties->Click publishers-> near "Distribution Database"
Distribution (default one) click | ...|=- > here change Default Snapshot Folder e.g: \\servername\Visibaleshare
click 'OK' ...
ASKER
HI, Thx for your comments... But to apply those changes on new subscription I should have all the files in the snapshot folder.. but looks like somehow they are all deleted... Is there any way I can still do the same. I am ok if I don;t have to change that to network path.
it is for publication (snapshot is part of publication) - > the folder needs to be in UNC format e,g, \\server\share
to be accessible from subscriber
------------------------
Snapshot is time to time cleaned by replication cleanup job (it makes sense..)
--------------------------
But if it is OK for you to recreate all replication - you can do it and set right path to snapshot
---
also you can skip snapshot part for new subscriber if you can export to subscriber data via (for example SSIS export\import utility)
and uncheck snapshot part in subscription creation
to be accessible from subscriber
------------------------
Snapshot is time to time cleaned by replication cleanup job (it makes sense..)
--------------------------
But if it is OK for you to recreate all replication - you can do it and set right path to snapshot
---
also you can skip snapshot part for new subscriber if you can export to subscriber data via (for example SSIS export\import utility)
and uncheck snapshot part in subscription creation
ASKER
Actually in our case Distribution and subscription is on the same server so its not a UNC path but a local path like E:\MSSQL\RepData.
Now doing complete replication takes about more then 7 hrs (3 Hrs for snapshot only) and I was planning to avoid doing this replication again if somehow I can get those files in snapshot folder (E:\MSSQL\RepData.) or somehow I can skip them when I want to add a new subscription. Make sense ?
Now doing complete replication takes about more then 7 hrs (3 Hrs for snapshot only) and I was planning to avoid doing this replication again if somehow I can get those files in snapshot folder (E:\MSSQL\RepData.) or somehow I can skip them when I want to add a new subscription. Make sense ?
try to load (as I said) initial data via SSIS - it will be faster..
to subscriber from publisher
ASKER
I mean I can do backup and restore as well... if this is the case.
But then could you please explain in step by step approach if I want to add another subscriber to existing replication. I tried in a test system with the same params as prod but it says unable to read data from E:\MSSQL\RepData as this folder is not there on the new subscription.
I would appreciate if you could point me to any site where its explained. would save lot for time for me.
But then could you please explain in step by step approach if I want to add another subscriber to existing replication. I tried in a test system with the same params as prod but it says unable to read data from E:\MSSQL\RepData as this folder is not there on the new subscription.
I would appreciate if you could point me to any site where its explained. would save lot for time for me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You for help.
just add new subscription- it should work fine