Solved

SQL Server Replication : Add New subscription SQL Server 2005

Posted on 2009-05-07
11
590 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

737 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