cascade delete and merge rep

Hey everyone,
I'm currently running sql 2k sp3 and win2k3 with all of
the latest patches. I'm running merge replication in a
clustered environment. My question is in regards to the
cascade delete option for foreign keys. Whenever a delete
transaction is executed against the parent table with a
foreign key to a child table and the cascade delete option
enabled, merge replication refuses to replicate the
changes due to the merge profile (ChangesPerHistory =
2000, DownloadGenerationsPerBatch = 2000). If I change
the profile so the changes and generations are set to 10,
then it works, but it's painfully slow. Also, just
executing the delete takes forever. Deleting just 10
records from the parent table using the clustered
index/primary key column in the where clause takes almost
1 minute. Without the cascade option enabled, it takes
less than 1 second. Any ideas on why the cascade delete
option isn't working? If it helps, the two tables above
are fairly large, over 120 million records between the two
of them. Thanks.
What you should do is disable the enforcement of constraint for replication.
What this will mean is that the replication processs can add children
without parents.
What happens with merge replication is sometimes a child delete/update is
replicated before the corresponding parent child delete/update. By
increasing the generationsper batch hopefully the parent and child
modifications will happen in the same batch. If the child record
delete/update occurs before the corresponding parent delete/update the child
delete/update is rolled back and goes into a retry queue for that batch, and
when all the transactions are processed for that batch the records in the
retry queue are tried again. This time hopefully the child record makes it
in as the parent exists.
When you disable this constraint for replication, children can be added
without parents (assuming the parent will be replicated later).
Let me know how this works for you.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Leon" <anonymous@.discussions.microsoft.com> wrote in message
news:28c501c4a1a9$7327f540$a601280a@.phx.gbl...
> Hey everyone,
> I'm currently running sql 2k sp3 and win2k3 with all of
> the latest patches. I'm running merge replication in a
> clustered environment. My question is in regards to the
> cascade delete option for foreign keys. Whenever a delete
> transaction is executed against the parent table with a
> foreign key to a child table and the cascade delete option
> enabled, merge replication refuses to replicate the
> changes due to the merge profile (ChangesPerHistory =
> 2000, DownloadGenerationsPerBatch = 2000). If I change
> the profile so the changes and generations are set to 10,
> then it works, but it's painfully slow. Also, just
> executing the delete takes forever. Deleting just 10
> records from the parent table using the clustered
> index/primary key column in the where clause takes almost
> 1 minute. Without the cascade option enabled, it takes
> less than 1 second. Any ideas on why the cascade delete
> option isn't working? If it helps, the two tables above
> are fairly large, over 120 million records between the two
> of them. Thanks.
|||Leon,
We are currently improving this area for SQL 2005. Would you be willing to
work offline with me on reproducing your current problem?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OemHAtboEHA.596@.TK2MSFTNGP11.phx.gbl...
> What you should do is disable the enforcement of constraint for
> replication.
> What this will mean is that the replication processs can add children
> without parents.
> What happens with merge replication is sometimes a child delete/update is
> replicated before the corresponding parent child delete/update. By
> increasing the generationsper batch hopefully the parent and child
> modifications will happen in the same batch. If the child record
> delete/update occurs before the corresponding parent delete/update the
> child
> delete/update is rolled back and goes into a retry queue for that batch,
> and
> when all the transactions are processed for that batch the records in the
> retry queue are tried again. This time hopefully the child record makes it
> in as the parent exists.
> When you disable this constraint for replication, children can be added
> without parents (assuming the parent will be replicated later).
> Let me know how this works for you.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Leon" <anonymous@.discussions.microsoft.com> wrote in message
> news:28c501c4a1a9$7327f540$a601280a@.phx.gbl...
>
|||Philip,
Absolutely. I'll send you an email from my work account
so we can discuss.
Hilary,
I'll see if that works. Thanks for your help.

>--Original Message--
>Leon,
>We are currently improving this area for SQL 2005. Would
you be willing to
>work offline with me on reproducing your current problem?
>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in
message[vbcol=seagreen]
>news:OemHAtboEHA.596@.TK2MSFTNGP11.phx.gbl...
constraint for[vbcol=seagreen]
can add children[vbcol=seagreen]
child delete/update is[vbcol=seagreen]
delete/update. By[vbcol=seagreen]
parent and child[vbcol=seagreen]
child record[vbcol=seagreen]
delete/update the[vbcol=seagreen]
queue for that batch,[vbcol=seagreen]
the records in the[vbcol=seagreen]
child record makes it[vbcol=seagreen]
children can be added[vbcol=seagreen]
later).[vbcol=seagreen]
message[vbcol=seagreen]
the[vbcol=seagreen]
delete[vbcol=seagreen]
option[vbcol=seagreen]
10,[vbcol=seagreen]
almost[vbcol=seagreen]
delete[vbcol=seagreen]
above[vbcol=seagreen]
two
>
>.
>

Cant upgrade to SP3 in Win Server 2003

I'm trying to install SQL Server 2000 on a newly-installed Win 2003
Server box. My disk is SQL Server SP 2. I got the error message
stating that I couldn't run SQL Server less than SP 3, but I
downloaded the patches from Microsoft's website figuring I'd install
SP 2 off the disk and then install the SP 3 from the download.

Trouble is, when I try to install SP 3, it can't authenticate the
login I give it, either the Windows logon or the sa password, to
actually install SP 3.

I've double-checked the passwords and permissions, and the Windows
logon _should_ be working, and the sa password is definitely what I'm
using in the SP 3 installation program, but no luck.

Any help would be appreciated.

Josephjbloch@.hypotenuse.com (Joe Bloch) wrote in message news:<6347ce8d.0404061048.50c0845@.posting.google.com>...
> I'm trying to install SQL Server 2000 on a newly-installed Win 2003
> Server box. My disk is SQL Server SP 2. I got the error message
> stating that I couldn't run SQL Server less than SP 3, but I
> downloaded the patches from Microsoft's website figuring I'd install
> SP 2 off the disk and then install the SP 3 from the download.
> Trouble is, when I try to install SP 3, it can't authenticate the
> login I give it, either the Windows logon or the sa password, to
> actually install SP 3.
> I've double-checked the passwords and permissions, and the Windows
> logon _should_ be working, and the sa password is definitely what I'm
> using in the SP 3 installation program, but no luck.
> Any help would be appreciated.
> Joseph

http://support.microsoft.com/defaul...4&Product=sql2k

If this doesn't help, perhaps you can give some more information. Is
the Windows account you're using for the installation a local
administrator? By default, members of that group are in the MSSQL
sysadmin role, so that should be the easiest way to avoid permissions
issues.

Simon|||sql@.hayes.ch (Simon Hayes) wrote in message news:<60cd0137.0404070042.1149c29@.posting.google.com>...
> jbloch@.hypotenuse.com (Joe Bloch) wrote in message news:<6347ce8d.0404061048.50c0845@.posting.google.com>...
> > I'm trying to install SQL Server 2000 on a newly-installed Win 2003
> > Server box. My disk is SQL Server SP 2. I got the error message
> > stating that I couldn't run SQL Server less than SP 3, but I
> > downloaded the patches from Microsoft's website figuring I'd install
> > SP 2 off the disk and then install the SP 3 from the download.
> > Trouble is, when I try to install SP 3, it can't authenticate the
> > login I give it, either the Windows logon or the sa password, to
> > actually install SP 3.
> > I've double-checked the passwords and permissions, and the Windows
> > logon _should_ be working, and the sa password is definitely what I'm
> > using in the SP 3 installation program, but no luck.
> > Any help would be appreciated.
> > Joseph
> http://support.microsoft.com/defaul...4&Product=sql2k
> If this doesn't help, perhaps you can give some more information. Is
> the Windows account you're using for the installation a local
> administrator? By default, members of that group are in the MSSQL
> sysadmin role, so that should be the easiest way to avoid permissions
> issues.
> Simon

Joseph,
Are you installing over Terminal services/remote desktop? You might
want to try VNC or use Control Panel: Add/remove programs to locate
your setup.bat file and run from there. You might also want to check
your Windows Temp and TMP Environment Variables. You need to ensure
that these variables have no spaces in the path. For example, set them
to C:/TMP and C:/TEMP instead of C:/Program files/Temp. You might
also want to locate the log file that the Service Pack writes to...you
should find the error in there! Hope this helps!
Lisa|||We're having the same issue. We have the latest MDAC installed, and
everything is fine with the registry entries and temp folders. Any
other ideas?

Thanks,

Christian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||We're having the same issue. We have the latest MDAC installed, and
everything is fine with the registry entries and temp folders. Any
other ideas?

Thanks,

Christian

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||addernoir <addernoir> wrote in message news:<407c4f7e$0$201$75868355@.news.frii.net>...
> We're having the same issue. We have the latest MDAC installed, and
> everything is fine with the registry entries and temp folders. Any
> other ideas?
> Thanks,
> Christian
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Christian,
Have you checked the contents of SQLSP.Log ? You might find more
detail on the source of error there.|||addernoir <addernoir> wrote in message news:<407c4f7e$0$201$75868355@.news.frii.net>...
> We're having the same issue. We have the latest MDAC installed, and
> everything is fine with the registry entries and temp folders. Any
> other ideas?
> Thanks,
> Christian
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Christian,
Have you checked the contents of SQLSP.Log ? You might find more
detail on the source of error there.