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 |
|
tfountain
Constraint Violating Yak Guru
491 Posts |
 Posted - 2008-10-31 : 11:05:06
|
| I have snapshot replication from SQL Server 2005 to Oracle 10gR2. This works fine and dandy but now we have problems as our development staff makes changes. What I have found is that when we attempt to modify (drop, sp_rename, etc) columns on a table that is used in replication, even if the column is not in the publication, errors are generated and the modification fails.My question is this, how do other shops deal with this situation? Basically I need to find a way to allow developers to make changes but I want to balance the permissions they have on the server. Right now the only way I have found to do allow changes to the data structure is to actually drop replication altogether (sp_removedbreplication), make the data structure changes, and recreate the publications and subscriptions.Thoughts? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2008-10-31 : 11:27:39
|
| Drop the table from publication, make the changes and add the table it back in.This should not be done on a production servers by developers, only by a DBA. |
 |
|
|
tfountain
Constraint Violating Yak Guru
491 Posts |
Posted - 2008-11-03 : 11:49:08
|
quote:
Originally posted by RickD
Drop the table from publication, make the changes and add the table it back in.This should not be done on a production servers by developers, only by a DBA.
And here is my problem :). Our development staff is responsible for data structure changes. What happens now is an error is generated when attempting to remove a column from a table that participates in replication (even if the column isn't used in replication - a MS bug IMO).I want to devise some sort of process/procedure or something that gets me out of the fold on an "as-needed" basis, that allows the developers to do this task. Keep in mind, our development staff has a pretty advanced workflow for making these types of changes so I have that as a basis to work with.FYI - we have a workflow in place where changes move up through several environments - DEV, QA, Staging, Production. |
|
|
|
|
|
|
|
|
Replication and data structure changes