June 22, 2005 at 11:45 am
I am responsible for setting up replication and testing several replication scenarios and the nuances of replication between two SQL Server 2000 machines. I am begin to suspect that the the devil is in the details with replication and would appreciate any response on the below questions which steers me clear of replication Mordor.
In a nutshell: we run a large ColdFusion application backed by SQL Server 2000. We are setting up to publish one way from our production SQL Server 2000 to a second subscribing server. We will hit the subscribing server to run reports, taking a chunk of the burden from the publisher server. The subscribing server needs to be minimal latency (pretty much real-time), consequently we have chosen transactional replication with real-time updates.
Here’s the crux of the matter: we do a lot of customization of our application and frequently need to add or modify tables, columns, datatypes, indexes, etc on the publishing server. So far, we have found replication to be not very forgiving with such changes:
2) If you add a table to the publishing database or if you add columns or indexes to a table that is already a part of a publication, these schema changes are not propagated to the subscribing database unless you a) mark the publication for initialization and then b) reinitialize all subscriptions to the publication. With such schema changes, must you always reinitialize the snapshot manually? (We’re aware that reinitialization can be scheduled, but this is if you want the changes to take place immediately.)
3) How disruptive does reinitialization need to be? We are finding that a complete reinitialization of the snapshot of the database takes 30-40 minutes. We have a large Web application that hits this database with high traffic. If the checkbox for “do not lock tables during snapshot generation” is checked, can we reinitialize without causing locks or other performance issues that will interfere with the application? Is there any speed or performance advantage to doing a backup and restore of the publishing database (to create the subscribing copy of the database) and then generating/initializing the snapshot?
4) The Replication set-up in MS SQL Server 2000 automatically adds its own identity columns to the subscribing database. Is there any way around this? We want the replicated server for reporting, but we want it to also server as part of the backup strategy. With the new columns, however, it is no longer a duplicate copy of the production database.
I've spent a fair amount of time experimenting with replication and really want to make it work for us, provided that we can get these questions answered. Thank you in advance for your help. Paul Fraser
June 22, 2005 at 12:33 pm
#1. When is your maintenance window?
#2. Why can't you schedule all of these changes to occur in that window?
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 22, 2005 at 12:43 pm
What you're suggesting then is that we schedule snapshot reinitialization during a daily maintenance time, probably 2-4 a.m. in our case.
While these may work for many organizations (and maybe even for us), we are impatient people in the sense that when we make a schema change during the day to production (yes, it does happen), we want it to be available right then on the subscribing (reporting) server.
Do you mind elablorating on what you had in mind? Thanks much. Paul
June 22, 2005 at 3:28 pm
Paul,
I had nothing in mind other than... It is generally unrealistic to have changes made during the day and have immediate gratification for your example. IF you make schema changes during the day THEN they will need to either A. wait until after the maintenance period or B live with the fact that they will be interrupted.
I still think IF you have a maintenance period THEN that is when you should make changes
Good Hunting!
AJ Ahrens
webmaster@kritter.net
June 23, 2005 at 1:04 am
1,2,3. look in Books online. You can add/drop articles/columns from existing publications. You do not need to break replication.
SP_AddArticle, SP_DropArticle to add/drop tables
SP_ReplAddColumn, SP_ReplDropColumn to add/drop columns
4. The only time replication adds the identity column is for merge replication. Is that what you aqre doing?
Replication has been very reliable for us. Good Luck!
HTH
June 23, 2005 at 2:54 pm
Thanks, it is helpful to know that you can add articles and columns to existing publications and have the schema changes propagate to the subscriber without having to reinitialize the snapshot or otherwise breaking replication. You can also add columns from within the Enterprise Manager GUI and have them propagate as long as you do it within the publication properties under the columns tab.
I must have been confused on replication adding an identity column. Sure enough transaction replication does NOT add such a column on the subscriber's tables. I believe all published articles/tables on the publisher need to have a primary key. Thanks again.
June 28, 2005 at 9:34 am
Hi all,
I am new to this forum, and noticed this thread and thought this is more or less the same issue we have at the moment in work.
We have a live database, and another database that is used solely for reporting purposes which is synchronised on a frequent basis. As like in Paul scenario, it helps take the load off the Live database.
However, my scenario is slightly different in that the db schema used for the reporting will differ slightly compared to that of the live database (we hope to optimise some of the schema, and teh live database schema is right-protected and we can't really change that).
Is Replication suitable for my requirements here?
Thanks
Tryst
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply