Solved

Merge Replication

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

757 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

19 Experts available now in Live!

Get 1:1 Help Now