February 8, 2009 at 6:55 pm
Hi
I am in the process of setting up a reporting server and considering Transaction Replication to get the data across to it (both running SQL Server 2005 in windows 2003 env). HOwever, our application runs a nightly 'sync' job that involves dropping tables and then rebuilding+repopulating (using batch copy operations). As I understand it Transactional Replication won't allow the tables marked for replication to be dropped.
So the solution I have tried is to delete the replication on the database prior to the sync and then reinstate +re initialise it after the sync job has finished. Our DB's are and will like remain small (<100-200M) so the time penalty is not a big issue.
I would greatly appreciate any input of whether this process of nightly dropping and recreating the replication ( publication and corresponding subscription) will have any unintended consequences or side effects that might compromise the process/data etc??
Thanks in advance for you assistance.
Matt
February 8, 2009 at 9:03 pm
Sounds painful. Any reason you couldn't do log shipping? Curious.
I can't think of any side effects but I can't believe it would go smoothly all the time. Dropping replication and then recreating it is not always a smooth effort so, my fear would be that you would run into glitches periodically that would cause pain trying to figure them out.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 8, 2009 at 10:34 pm
Hi David,
Thanks for the reply. I have considered transaction logging but was under the impression that it would not be all that suitable as it is desirable that SQL have exclusive access to the DB's during the trn's restores or otherwise there is a significant performance penalty during that time. This is only based on the my study at the university of google so would welcome some other input on this?
Thanks
Matt
February 9, 2009 at 3:46 am
matt.ma (2/8/2009)
I have considered transaction logging but was under the impression that it would not be all that suitable as it is desirable that SQL have exclusive access to the DB's during the trn's restores or otherwise there is a significant performance penalty during that time.
When restoring logs it definitely requires exclusive access to the databases as it is really nothing less than a restore process. So, if you didn't have times when you could have no access for reporting so that you could do the log restore this may not work.
However, in thinking about this, you will suffer similar or equal impact with the reinitialization via snapshots for your daily replication rebuild. Correct?
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 9, 2009 at 4:29 am
Hi David,
I really appreciate the time your taking to assist me with this as I am quite new to SQL Server and am still in that space of 'don't know what I don't know'.
It is true that there will be down time with the re-initialisation but it would be possible to do that at night when the server will largely be idle. The thing that I like about the transaction replication is that the DB is up-to-date during business hours. However, with log shipping it would be up to 24 hours out of sync by the end of the day if we needed to make the DB available for reporting throughout the day. Reflecting on this more having the reporting server up to date is a nice to have but probably not critical.
After your earlier email I tried dropping and adding the replication and it did hiccup on a couple of occasions ( at the subscriber end) and so I think keeping it simple with Log Shipping will probably suffice. We also have the benefit of a high availability solution thrown in!
Thanks for your assistance with this. Much appreciated.
Matt
February 9, 2009 at 12:18 pm
As Long as you are not changing the schema. I believe the 'sync' jobs should apply the "diff" and not drop+create the tables entirely.
There are many solutions that can be used but the most effective way is to simply do that. It does not requires a lot of effort to do it.
Just my $0.02
* Noel
February 9, 2009 at 12:24 pm
noeld (2/9/2009)
As Long as you are not changing the schema. I believe the 'sync' jobs should apply the "diff" and not drop+create the tables entirely.There are many solutions that can be used but the most effective way is to simply do that. It does not requires a lot of effort to do it.
Just my $0.02
Noel - Wondering if you can elaborate a bit on your suggestion. Thanks.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 9, 2009 at 12:33 pm
There were some cases I had to deal with situations similar to this.
Suppose (as in this case) your DB is small.
Instead of dropping the tables you could have staging tables that are not replicated and then:
1. Truncate staging tables (minimally logged)
2. Populate staging_table (bcp minimally logged as well)
3. Use binary_checksum(*) or any other method to determine "new", "updated", "deleted" rows
4. Delete the "deleted"
5. Update the "modified"
6. Insert the "new"
7. Don't worry about the rest of the rows
Replication will have minimal overhead as long as the changes are not massive of course.
BTW SQL 2008 MERGE simplifies this *a lot*
Hope it helps.
* Noel
February 9, 2009 at 12:36 pm
Ok, understand now. Yes, we do that now, or some variation of that. I was going off the OP question about having to drop the tables as being a requirement. I didn't really pick at that requirement though and hopefully your suggestion will open up some other doors for consideration.
Thanks! 😀
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
February 9, 2009 at 5:58 pm
Hi
Thanks for the great idea. As a variant on that could I replace the 'sycn' with transaction replication between the databases on the same server as well as replicating the DB's to a secondary server? (The reason the checksum approach would not work for us is that there are 'text' dataypes in the DBs.)
It seems that this would involve creating two publications i) to publish most (not all) of the tables from DB1 to DB2 on the same server and then ii) to publish all the tables from DB1 to DB1 on the secondary server. The thing I am not clear on is whether there would be problems causes by both publications making use of the same trn log file. Would the first log reader to run alter something so that those transactions would then not be available to the second log reader ??
It would be great to avoid the sync operation as it is clumsy despite being the fastest way to achieve the result.
Thanks
Matt
:w00t:
February 9, 2009 at 7:23 pm
It should still only be the one publication on DB1 (primary server) with subscriptions for DB2 (primary server) and DB1 on secondary server. Should work fine.
Glad that NoelD was able to help out with that suggestion!
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply