Solved

SQL Server Replication : Add New subscription SQL Server 2005

Posted on 2009-05-07
11
557 Views
Last Modified: 2012-05-06
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.




0
Comment
Question by:Aanvik
  • 6
  • 5
11 Comments
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
do not need to drop anything
just add new  subscription-  it should work fine
0
 
LVL 5

Author Comment

by:Aanvik
Comment Utility
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
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
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
 
LVL 5

Author Comment

by:Aanvik
Comment Utility
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
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:Aanvik
Comment Utility
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
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
try to load (as I said) initial data via SSIS - it will be faster..
0
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
to subscriber from publisher
0
 
LVL 5

Author Comment

by:Aanvik
Comment Utility
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
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
Comment Utility
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
 
LVL 5

Author Closing Comment

by:Aanvik
Comment Utility
Thank You for help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now