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-07-28 : 11:06:10
|
| After going from a MySQL replication environment where everything done on the publisher is replicated to the subscriber, I'm having problems with SQL Server 2008 replication. Are there any workarounds to these limits / issues?1. CREATE INDEX and ALTER INDEX are not replicated, so if you add or change an index at, for example, the Publisher, you must make the same addition or change at the Subscriber if you want it reflected there.2. TRUNCATE TABLE is a non-logged operation that does not fire triggers. It is not permitted because replication cannot track the changes caused by the operation: transactional replication tracks changes through the transaction log;3. Users and passwords are not replicated4. Any changes to replication setup and configuration require reinitializing the subscriber with a new snapshot.5. To drop a table on the publisher, that table must be dropped from the replication setup first.One source: http://msdn.microsoft.com/en-us/library/ms151740.aspx |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-28 : 12:01:44
|
| 1. It is VERY common to NOT want the same indexing scheme on the subscriber(s). Anyway, in the article properties you can set copy nonclustered indexes to true. This is only for when the snapshot runs though.2. Right. Can't truncate published tables. You can delete though. Be careful as deleting without a where clause on very large tables will punish the distributor.3. You'll need to manage this yourself. Automatically replicaating users would be a security risk. Like #1, I seldom have all of the same logins on the subscribers that I do on the publishers.4. Yes, but when correctly configured, the snapshot is only generated for the new/modified articles.5. sp_droparticle |
 |
|
|
bryan42
Starting Member
28 Posts |
Posted - 2011-07-28 : 12:07:27
|
| Thanks, russell. What did you mean about sp_droparticle? May that be used to drop a table on the publisher and have it also dropped on the subscriber? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-07-28 : 12:09:55
|
| No, it removes it from the publication. It doesn't drop the table on either side. |
|
|
|
|
|
|
|
|
Problems with replication