?
Solved

SQL Server Replication : Add New subscription SQL Server 2005

Posted on 2009-05-07
11
Medium Priority
?
600 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 24330396
do not need to drop anything
just add new  subscription-  it should work fine
0
 
LVL 5

Author Comment

by:Aanvik
ID: 24330471
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 43

Expert Comment

by:Eugene Z
ID: 24331036
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 5

Author Comment

by:Aanvik
ID: 24339442
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 43

Expert Comment

by:Eugene Z
ID: 24339546
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
 
LVL 5

Author Comment

by:Aanvik
ID: 24339576
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 43

Expert Comment

by:Eugene Z
ID: 24339645
try to load (as I said) initial data via SSIS - it will be faster..
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 24339649
to subscriber from publisher
0
 
LVL 5

Author Comment

by:Aanvik
ID: 24339693
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 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 24340879
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
ID: 31579155
Thank You for help.
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

752 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