Solved

Transactional Replication Alter Table Best Practices, calling all replication experts

Posted on 2003-11-17
6
1,594 Views
Last Modified: 2012-05-04
We have a database application that we distribute to all of our customers.  We are using Red Gate to create the database script changes from one version to the next (and its worked great for years).  Now, we are introducing transactional replication on this db and need some help from someone who has been there (preferably).  

The problem is that the migration scripts utilize the ALTER TABLE statement which will NOT work on replicated tables.  The tool doesn't know how to generate the correct statements (sp_msrepladdcolumn and sp_msrepldropcolumn).  The data is being replicated to a db that contains triggers and stored procedures that transform the data and send it on to a reporting db.  Dropping and recreating the publication seems like it will have two problems:  one is too much time, the other is that the data will probably be duplicated (not on the subscriber side, but on the reporting db side due to the triggers and sps.)

What we need is a workable solution or the name of a tool that will create migration scripts for replicated databases.  It seems like knowledge of replication here and elsewhere is fairly limited.  Btw, if anyone has the name of a book that they recommend on replication, I would appreciate it.  I looked on Amazon and Barnes and Noble and only found one book that is dedicated to replication and its basically a copy of BOL (SQL Server 2000 Reference Library -  Iseminger).

Thanks!
0
Comment
Question by:PaulBarbin
  • 4
  • 2
6 Comments
 
LVL 13

Expert Comment

by:danblake
Comment Utility
You can just drop the replication articles involved without dropping the entire publication...
(An article is an object being replicated)

use: sp_droparticle
and sp_addarticle

As long as you add the articles afterwards you will be fine.
If you script out the replication scenarios from the server (drop / create), this will allow you to copy the code to add to the script created for both the drops/creates of the items.

The drops go before you're redgate script has run.
The creates go after you're redgate script has completed (sucesfully).


0
 
LVL 9

Author Comment

by:PaulBarbin
Comment Utility
Dan, thanks for the reply.  Comments and questions...

When dropping and readding articles only, would we need to re-run the snapshot agent?  

Besides applying schema changes, one of our other goals was to keep the update time to an absolute minimum.  But we're talking about replicating 3-4 tables with 500,000 rows or more in each, plus 40 other smaller tables.  The time to reinit and rerun the replication can be as much as 20-30 minutes for our largest clients (which is another problem - this isn't for my company, its part of a software package that we sell to hundreds of clients nationwide).

I've done some additional research since then and realized that when we readd the publication and subscription, there is an option to tell the snapshot NOT to sync.  This would be a great fix, except that the option apparently doesn't work.  Microsoft has created a KB doc for it and the fix is available but not publicly yet.  

I have a few other questions I'd like to ask you.  I'll add new questions when we get this one resolved.  Thanks
0
 
LVL 13

Accepted Solution

by:
danblake earned 500 total points
Comment Utility
With this mechanism, you do not need to snapshot re-initialisation.
It keeps the subscription 'up', you just drop the articles within the subscription.
It is possible to have a subscription with no replicated articles.
Then apply schema modifications.
Then add replicated articles
Tran Replication then sends data-modifications on new tables to all sites without snapshots being reinitialised.

I did not need, to re-apply the snapshot after adding an article to a publication, I mainted T-replication on the subscription as a perm up option..

The default for adding an article is not to force a snapshot after applying the transactional replication at one site.
Looking within the sp_addarticle code:
[@force_invalidate_snapshot = ] force_invalidate_snapshot
Acknowledges that the action taken by this stored procedure may invalidate an existing snapshot. force_invalidate_snapshot is a bit, with a default of 0. 0 specifies that adding an article will not cause the snapshot to be invalid. If the stored procedure detects that the change does require a new snapshot, an error will occur and no changes will be made. 1 specifies that adding an article may cause the snapshot to be invalid, and if there are existing subscriptions that would require a new snapshot, gives permission for the existing snapshot to be marked as obsolete and a new snapshot to be generated.

So whilst a single article is down, to allow schema updates to be applied to each site in turn, preventing data-updates at the distributed sites (especially if involved in a merge replication) -- it is possible to do this by using view -> table updates only for all tables involved in replication.  The 'trick' is ensuring that all articles are modified / changed then re-added.  I have used this technique many times.

I also found that dropping/recreating the publication resulted in a snapshot being required, but I do did manage to create several t-sql tran publications without requiring snapshots to be created.

This should allow changing of status of a particular subscription: sp_changesubstatus by stating that the subscription is already syncronised.  or by changing the [@status =] 'status' to active.
Value Description
active Subscriber is synchronized and is receiving data.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 13

Expert Comment

by:danblake
Comment Utility
When dropping and readding articles only, would we need to re-run the snapshot agent?

No.
0
 
LVL 9

Author Comment

by:PaulBarbin
Comment Utility
Thanks again for the info.  I have learned quite a bit about replication in the past 6 months.  But I could still use your expertise.  I'll award you points for this one and open a new one for further discussion.

Thanks again.

Paul
0
 
LVL 13

Expert Comment

by:danblake
Comment Utility
No worries, glad to have been of assistance.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 5 109
Need Counts 11 40
Powershell SMO script not working. 18 99
Sql query to Stored Procedure 6 13
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

744 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

9 Experts available now in Live!

Get 1:1 Help Now