Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bryan42
Starting Member
28 Posts |
 Posted - 2011-10-07 : 14:24:02
|
| I’m setting up transaction replication for several databases with a total of around 200GB of data. To initiate replication, I've restore the databases to the subscriber and am truncating all the tables so the indexes are preserved. Truncating doesn't work, however, as foreign key constraints force me to delete rows individually and slowly. This database cleaning was taking more than four days before I started looking for different options.I tried to create a backup of the databases with everything but the rows of data, but the backup kept failing with SQL Server 2008 R2.Another option would be to remove all constraints from the reporting databases. That should make things fast and simple to configure. Do you see any negatives to this? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2011-10-07 : 17:18:20
|
| Our subscribers are (logically) read only - so we do not have the FK constraints on the subscriber tables.Be One with the OptimizerTG |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2011-10-07 : 19:11:24
|
| You can alter the properties of each/all articles to copy foreign keys, contraints, indexes, et al. Right click your existing publication and view the Properties. Under Articles, select an article and click the Article Properties drop down list. You will get to a list of various schema options that you can selectively turn on or off.Any changes to the Article will require a new snapshot.=======================================Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith |
|
|
|
bryan42
Starting Member
28 Posts |
Posted - 2011-10-08 : 10:18:25
|
| Thank you. That should help significantly. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-10-13 : 14:14:14
|
| Just drop the FKs at the subscribers.By the way, disabling them doesn't allow you to truncate.As TG said, subscriber(s) should be treated as read only, so no need for FKs. |
|
|
|
|
|
|
|
|
Okay to remove constraints on subscriber?