Solved

Merge Replication

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

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
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.

863 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

17 Experts available now in Live!

Get 1:1 Help Now