Solved

SQL Server Replication : Add New subscription SQL Server 2005

Posted on 2009-05-07
11
566 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
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 42

Expert Comment

by:EugeneZ
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
 
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 42

Expert Comment

by:EugeneZ
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 42

Expert Comment

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

Expert Comment

by:EugeneZ
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 42

Accepted Solution

by:
EugeneZ earned 500 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Alter table 4 23
SQL Query stumper 3 36
Query / Window function ? 3 15
Extract string portion 2 12
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 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

21 Experts available now in Live!

Get 1:1 Help Now