When upgrading a SQL Server 2005 database which has replicated stored procedures and views, the DDT throws errors messages. I don't know if you are dropping and recreating them but is it possible to alter them instead?

On a similar note, it would be useful if the DDT didn't drop the indexes added to the database by SQL Server for merge replication. These indexes would never be in the DDT model and are used only by replication. Replication will work without them but performance drops - we have a replicated system with around 800 databases and we can't afford the indexes to be dropped.

Thanks,

Aaron

Previously, the DDT dropped all stored procedures, UDFs, etc. and then added them back through the meta-data.  But we made a change that only drops sprocs, views, and UDFs that were deployed by the DDT (we used an extended property to set a flag).  So here is when you can run into an issue and when you may want or need to create a pre-profile or post-profile script to get things "lined up."  Let's say for a moment that you have a stored procedure that was created through SQL Server Management Studio and was never part of a DDT profile...in this scenario depending on how it manifests itself, you could get an error saying that the SPROC already exists on the database (since it doesn't have that extended property which tells the DDT to update this SPROC).  Past that, I am not sure why you would get an error.  In regards to calling an ALTER, this will not always work in a deployment environment that supports dynamic (and non-incremental) updates.  For example, prioritizations, etc.  In fact, in some cases we do call ALTER...but we try to create clean sprocs so for the most part, the SPROCS with the aformentioned properties get "smoked" (Texan for deleted) before getting deployed again.  This has been our standards now for quite a number of years and it has worked really well in 99% of the environments.  Not that we aren't open to addressing your issue that may be coming up, but this should at least give you a bit of background and history on the topic.

Now...this works great for elements that do not necessarily tie themselves directly to the table such as indexes.  We actually use a combination of SMO and T-SQL ALTER commands (for certain things, but we primarily stick to SMO) to atler the databases and tables.  However, in regards to indexes it gets far more complicated...because now you are expecting the meta-data of the tables to be modified from two sources...and this is when synchronization issues can creep up on you.  We may consider implementing the same logic that we use on the SPROCS, but this will require a lot of discussion in some development meetings as I can see some "dead bodies" floating up on making a blanket change on this.  But we will talk about this in our next SF development meeting.