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.




LVL 5
AanvikAsked:
Who is Participating?
 
Eugene ZConnect With a Mentor Commented:
due to provide what you need - you must to set UNC path visible and accessible  from all Replication servers
Or you can use way that I posted
or recreate publication

---
or even better:from SSMS right click your Publication -> properties->Snapshot- > "Location of snapshot files"   uncheck ".. default "-> check "Put the files in ..folder'Here you can set UNC path to place where the publication will set snapshot for your subscription
0
 
Eugene ZCommented:
do not need to drop anything
just add new  subscription-  it should work fine
0
 
AanvikAuthor Commented:
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.
0
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Eugene ZCommented:
try:
SSMS->right click replication->Distributor Properties->Click publishers-> near "Distribution Database"
Distribution  (default one) click | ...|=-  > here change Default Snapshot Folder  e.g:  \\servername\Visibalesharefolder..
click 'OK' ...
0
 
AanvikAuthor Commented:
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.
0
 
Eugene ZCommented:
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
 
0
 
AanvikAuthor Commented:
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 ?
0
 
Eugene ZCommented:
try to load (as I said) initial data via SSIS - it will be faster..
0
 
Eugene ZCommented:
to subscriber from publisher
0
 
AanvikAuthor Commented:
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.



0
 
AanvikAuthor Commented:
Thank You for help.
0
All Courses

From novice to tech pro — start learning today.