The Grateful DBA is happy to announce that I’ll be speaking at several SQL conferences this spring and summer. Not exactly what one would designate a “whirlwind tour,” yet nonetheless I’m truly looking forward to each for many specific reasons. I will be speaking at each of these conferences on a subject which consumes much of my day; transactional replication. So…if you happen to catch it more than once and it sounds familiar, now you know why.
The schedule and a couple notes about each event…
- 4/24/10, SQL Saturday #41, Atlanta, GA – Besides the normal “I’m-REALLY-looking-forward-to-this-SQL-Saturday” reasons, this weekend has an extra bonus attached, and it’s called Railroad Earth. For those not familiar with this wonderful bluegrass-rock-roots-jam band, search them out on MySpace, YouTube, etc for a taste of their style. Anyway, they just so happen to be at the Variety Playhouse that evening after the conference and I will of course will be making my way downtown to boogie.
- 5/8/10, SQL Saturday #38, Jacksonville, FL – This event holds a special place in my heart only because it’s put on by my local SQL Server user group, JSSUG. I will be taking an active role in not just presenting, but helping Brian, Devin, & Scott prepare in any way needed. It takes place right around the corner here in Jacksonville on the lovely campus of The University of North Florida.
- 6/5/10, SQL Saturday #22, Pensacola, FL – You may have guessed by now that these SQL Saturdays are numbered sequentially, and this one being #22 tells me they’ve been working on getting it together for quite a while. The official presenter’s list has not been decided upon yet as I believe the call for speakers hasn’t technically closed. I have of course applied to present and certainly hope to be asked to speak. Either way, I will attend this fine event as a day of free training with some of the top SQL minds in the country is just too good to pass up. (Edited 4/19/10: Speaker’s list appeared over the weekend…it’s official! See ya there!!)
- 7/31/10, SQL Saturday #40, Miami, FL – Same thing here as for Pensacola; the call for speakers won’t close for another month or so, but I certainly hope to be asked to present. Despite the expectation of oppressive heat (c’mon, it is South Florida in July), I always love traveling south the length of my home state as well as catching up with several close friends I happen to have in the Boca/Palm Beach area.
If you attend any or all of these events, please please make sure to find me and say hello. I truly look forward to meeting the 3 people who read my blog. 😉
Have a Grateful day…Troy
Tags: Replication, sqlsaturday, teaching
Posted in Replication, Speaking | Comments (1)
Ok, I know “SQL Server Replication Error 3726” isn’t the most imaginative of headlines for a blog article. What little I know about SEO tells me that titles carry more weight when the search engines do their crawls. So, I had to make the decision that this particular title may not actually bring sexy back. I can live with that.
Error 3726: “Could not drop object ‘myTable’ because it is referenced by a FOREIGN KEY constraint.” This is thrown predominantly upon the initial setup of a replication publication. When the snapshot agent is run, one of the first things that is done is the engine verifying whether the articles already exist at the subscriber. If so, one has a choice of either dropping the article entirely (schema included), just deleting the data (closer to a TRUNCATE than a delete because I’m fairly sure it’s not logged), filter based on predetermined criteria, or simply doing nothing. Whichever of the options you choose; if an article you wish to publish has a foreign key constraint that ties it to an object on the publisher that is not published as well, then this error will occur.
Fair enough, but what options do we have for resolving the error? Several actually. The easiest is to simply add the related table to the list of published articles and rerun the snapshot agent. This time around, the agent will recognize both related tables are being published and proceed as expected.
If for some reason you don’t have the flexibility to be able to alter the article list, then your best bet would be to drop and re-add the constraint. This can be done easily with the traditional ALTER TABLE/DROP CONSTRAINT & ALTER TABLE/ADD CONSTRAINT syntax (link) in all versions. Here is an excellent article on dropping and creating FK constraints by Pinal Dave (link). As well, if you’re creating the publication (transactional or snapshot) using sp_addpublication, then there is an optional “@pre_snapshot” parameter wherein you can add T-SQL to drop the constraint (link).
The issue of foreign keys on the subscribers in transactional or snapshot replication is actually quite debated. I personally am of the opinion they are a waste of resources and that if referential integrity is being maintained on the publishers PRIOR to replicating, then there is no need whatsoever for those keys on the subscribers. It gets a bit more complicated when dealing with merge replication due to the 2-way nature of the beast. What do you say we leave that conversation for another day, eh?
Thanks and have a Grateful day…Troy
Tags: 2000, 2005, errors, foreign-key, Replication
Posted in Replication | Comments (0)