Solved

Merge Replication

Posted on 2011-03-16
4
597 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
  • 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

856 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