Solved

Merge Replication

Posted on 2011-03-16
4
600 Views
Last Modified: 2012-05-11
From BOL
"  If you change schema options after a publication is created, you must generate a new snapshot."
Is there any workaround for this if it's merge application?  To elaborate, we are having merge replications for a database which's about 5 GB. With in an year or so, the size of the database is expected to grow by a huge margin.
Since the size of the database is relatively small, my boss is ok with regenerating a snapshot on the subscriber if there are any schema changes made on the publisher. But he's totally against generating a new snapshot for any schema changes made in the near future (as we are planning to implement merge application for various other application databases, so they want to setup company wide standards for similar tasks)  , so he's asking me to give an alternate solution for this. Can you guys give me some input into this, your response is truly appreciated.

FYI, the version of the database in question is  SQL Server 2008 R2 enterprise on Windows 2008 R2 enterprise
0
Comment
Question by:rocky_lotus_newbie
[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
  • 2
4 Comments
 
LVL 4

Author Comment

by:rocky_lotus_newbie
ID: 35153673
The URL from which the excerpt taken above is http://msdn.microsoft.com/en-us/library/ms151313.aspx
0
 
LVL 4

Author Comment

by:rocky_lotus_newbie
ID: 35153677
(as we are planning to implement merge application for various

TYPO: it should be ' as we are planning to implement merge replication for various
0
 
LVL 28

Accepted Solution

by:
Ryan McCauley earned 250 total points
ID: 35159459
I think there's some confusion here. In SQL Server replication, DDL changes are automatically replicated to all subscribers unless you explicitly specify that they not be (when you create the subscription):

http://msdn.microsoft.com/en-us/library/ms147331(v=SQL.105).aspx

However, the only types of DDL that is replicated is ALTER statements - this means both DROP and CREATE statements are not replicated, and if I remember correctly, they're actually blocked when your database is enrolled as a publisher - you have to disable replication in order to do any DROP statements. I suspect you'll be able to run CREATE statements on your source database with replication active, but you won't be able to alter the replication stream to include data in these new tables. To add them, you'll have to either snapshot them and add a new replication stream for just the new tables, or you'll have to stop the existing stream, run a new snapshot, and then create a new merge replication stream that includes these new tables as well as your old ones.

The reason they ask for a new snapshot is that SQL Server can't alter the replication stream on the fly - adding or removing tables from replication - so they require that you don't change what you're replicating once you've started.

Though it would behoove you to set up a test environment and check to be sure, you'll be able to execute ALTER statements all day long on your replicated database, but you won't be able to add new objects to replication or remove objects from replication without generating a new snapshot.

Hope that clears things up!
0
 
LVL 10

Assisted Solution

by:TAB8
TAB8 earned 250 total points
ID: 35160558
If you dont want to apply large snapshots with merge replication but you want to add/ remove objercts , what i do is the following  "quicker than a applying big snapshot"
- stop users for accessing the database "no changes"
- delete the subscription from the subscriber
- manualy add your change to the subscriber ie new table
- manualy add your change to the publisher ie new table
- run the snapshot job on the publication "this just creates the snap it dosent apply it"
- on the  subscriber .. create a new subscritption ## deselect the initialise option##
what this will do is first time you run the subscription job it will populate the replication system tables and add the repl triggers to the new table if required ..  it will not reimport all your data ie 5gb data .. so wont take long
I have used this method many times to aviod resnapshots

Hope this helps
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 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