Yes...that is true.  If you add a single sproc or view, it has to "smoke" the existing ones before it can re-add the new one. 

Update:



If you have a manually added table in your database that the DDT knows nothing about and do a deploy it happily ignores it. Good.



If you have an object in your database that DDT doesn't mess with at all (schemas, triggers, roles) and do a deploy it ignores them. Good.



If you have a manually added stored proc or view in your database, not defined in the DDT and do a deploy it deletes them. Bad



Hope this helps.




Keith Chisarik

No problem

You are correct. It does leave anything alone that is doesn't know about. This is great.



It means I wasted 3 hours manually entering views and stored procs, but thats OK as I am happier with this anyhow.



Leave the security stuff alone, use DDT for application table management only.



Brilliant!



Thanks for the help.

Keith Chisarik

The DDT only works within the confines of what it knows...so if you have a table you are attempting to update, it needs to know the indexes and relationships to manage them.

But if you manually create a table and the DDT doesn't know about it, it should leave it alone.  There is one confine that it will smoke everything...and that is if you delete the database in the DDT.  Obviously if it deletes the database because you have removed it in your DDT package and there is a history record that tells it to Delete, then it doesn't care about anything else...it will just delete the database.

In short, you should be fine, but I would copy over your ive database and test this first to make sure there isn't something being overlooked.

Can you confirm DDT will not blow away existing Schemas, roles, etc that is doesn't try to manage?

Keith Chisarik

If you create the relationships and indexes through SCRIPT then you will be directly defying how the DDT is designed and you will start to see deployment issues.  Importing the relationships and indexes is part of the enhancement requests that I mentioned in a previous post.

If you manually create the relationships and indexes, then you will see proper behavior from that point forward.  Since we use SMO and logically detect and update relationships, indexes, etc., the DDT relies on the meta-data in order to know whether these items are part of the schema and how to deal with them.

So yes, I agree that your problems are stemming from the indexes and relationships not being imported.

Image of missing index and relationships.......

Keith Chisarik

1) Does the DDT not import relationships and indexes when it imports from SQL Server or did I do something wrong? (see image)



If not can I add these manually using code like I can for stored procs? I am not 100% confident I can rebuild these exactly with using the CREATE TO function....



ALTER TABLE [dbo].[aspnet_Applications] ADD UNIQUE NONCLUSTERED

(

   [ApplicationName] ASC

)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]



2) Also it appears that if I run a DDT "deploy" against an existing database that I ran aspnet_reqsql.exe against that DDT only attempts to modify what it does (tables, views, stored procs), is this right? Can I feel ok that if I recreate the indexes and relationships as in question 1 above, that the schemas, database roles and other things aspnet_reqsql.exe creates will be left intact by the DDT? Here is my deploy log from running a DDT Deploy against a database that was modified with aspnet_reqsql.exe. It looks to me like all is is doing "wrong" is a result of not importing the relationships and indexes. Agree?



• 11/10/2006 11:08:27 AM   -> Deploying meta-data for database 'BayshoreColdwell'

• 11/10/2006 11:08:27 AM   -> Ensuring Filegroups exist on database 'BayshoreColdwell'

• 11/10/2006 11:08:27 AM   -> The datafile 'BayshoreColdwell' was successfully altered.

• 11/10/2006 11:08:27 AM   -> Filegroup verification complete.

• 11/10/2006 11:08:27 AM   -> Ensuring Tables exist on database 'BayshoreColdwell'

• 11/10/2006 11:08:29 AM   -> The table 'aspnet_Applications' is being prepared to be modified.

• 11/10/2006 11:08:29 AM   -> The relationship 'FK__aspnet_Me__Appli__145C0A3F' was successfully dropped.

• 11/10/2006 11:08:29 AM   -> The relationship 'FK__aspnet_Pa__Appli__45F365D3' was successfully dropped.

• 11/10/2006 11:08:29 AM   -> The relationship 'FK__aspnet_Ro__Appli__32E0915F' was successfully dropped.

• 11/10/2006 11:08:29 AM   -> The relationship 'FK__aspnet_Us__Appli__0425A276' was successfully dropped.

• 11/10/2006 11:08:30 AM   -> The index 'aspnet_Applications_Index' was successfully dropped.

• 11/10/2006 11:08:30 AM   -> The index 'UQ__aspnet_Applicati__00551192' was successfully dropped.

• 11/10/2006 11:08:30 AM   -> The index 'UQ__aspnet_Applicati__7F60ED59' was successfully dropped.

• 11/10/2006 11:08:31 AM   -> The table 'aspnet_Applications' was successfully altered.

• 11/10/2006 11:08:36 AM   -> The table 'aspnet_Membership' is being prepared to be modified.

• 11/10/2006 11:08:36 AM   -> The index 'aspnet_Membership_index' was successfully dropped.

• 11/10/2006 11:08:38 AM   -> The table 'aspnet_Membership' was successfully altered.

• 11/10/2006 11:08:39 AM   -> The table 'aspnet_Paths' is being prepared to be modified.

• 11/10/2006 11:08:39 AM   -> The relationship 'FK__aspnet_Pe__PathI__4BAC3F29' was successfully dropped.

• 11/10/2006 11:08:39 AM   -> The relationship 'FK__aspnet_Pe__PathI__4F7CD00D' was successfully dropped.

• 11/10/2006 11:08:40 AM   -> The index 'aspnet_Paths_index' was successfully dropped.

• 11/10/2006 11:08:41 AM   -> The table 'aspnet_Paths' was successfully altered.

• 11/10/2006 11:08:41 AM   -> The table 'aspnet_PersonalizationAllUsers' is being prepared to be modified.

• 11/10/2006 11:08:42 AM   -> The table 'aspnet_PersonalizationAllUsers' was successfully altered.

• 11/10/2006 11:08:43 AM   -> The table 'aspnet_PersonalizationPerUser' is being prepared to be modified.

• 11/10/2006 11:08:44 AM   -> The index 'aspnet_PersonalizationPerUser_index1' was successfully dropped.

• 11/10/2006 11:08:44 AM   -> The index 'aspnet_PersonalizationPerUser_ncindex2' was successfully dropped.

• 11/10/2006 11:08:44 AM   -> The table 'aspnet_PersonalizationPerUser' was successfully altered.

• 11/10/2006 11:08:46 AM   -> The table 'aspnet_Profile' is being prepared to be modified.

• 11/10/2006 11:08:47 AM   -> The table 'aspnet_Profile' was successfully altered.

• 11/10/2006 11:08:48 AM   -> The table 'aspnet_Roles' is being prepared to be modified.

• 11/10/2006 11:08:48 AM   -> The relationship 'FK__aspnet_Us__RoleI__37A5467C' was successfully dropped.

• 11/10/2006 11:08:48 AM   -> The index 'aspnet_Roles_index1' was successfully dropped.

• 11/10/2006 11:08:49 AM   -> The table 'aspnet_Roles' was successfully altered.

• 11/10/2006 11:08:50 AM   -> The table 'aspnet_SchemaVersions' is being prepared to be modified.

• 11/10/2006 11:08:51 AM   -> The table 'aspnet_SchemaVersions' was successfully altered.

• 11/10/2006 11:08:52 AM   -> The table 'aspnet_Users' is being prepared to be modified.

• 11/10/2006 11:08:53 AM   -> The relationship 'FK__aspnet_Me__UserI__15502E78' was successfully dropped.

• 11/10/2006 11:08:53 AM   -> The relationship 'FK__aspnet_Pe__UserI__5070F446' was successfully dropped.

• 11/10/2006 11:08:53 AM   -> The relationship 'FK__aspnet_Pr__UserI__29572725' was successfully dropped.

• 11/10/2006 11:08:53 AM   -> The relationship 'FK__aspnet_Us__UserI__36B12243' was successfully dropped.

• 11/10/2006 11:08:53 AM   -> The index 'aspnet_Users_Index' was successfully dropped.

• 11/10/2006 11:08:53 AM   -> The index 'aspnet_Users_Index2' was successfully dropped.

• 11/10/2006 11:08:54 AM   -> The table 'aspnet_Users' was successfully altered.

• 11/10/2006 11:08:54 AM   -> The table 'aspnet_UsersInRoles' is being prepared to be modified.

• 11/10/2006 11:08:55 AM   -> The index 'aspnet_UsersInRoles_index' was successfully dropped.

• 11/10/2006 11:08:55 AM   -> The table 'aspnet_UsersInRoles' was successfully altered.

• 11/10/2006 11:08:59 AM   -> The table 'aspnet_WebEvent_Events' is being prepared to be modified.

• 11/10/2006 11:09:01 AM   -> The table 'aspnet_WebEvent_Events' was successfully altered.

• 11/10/2006 11:09:01 AM   -> Table verification complete.

• 11/10/2006 11:09:01 AM   -> Dropping unneeded filegroups for 'BayshoreColdwell'

• 11/10/2006 11:09:01 AM   -> Unneeded filegroups dropped on database 'BayshoreColdwell'

• 11/10/2006 11:09:01 AM   -> Ensuring Views exist on database 'BayshoreColdwell'

• 11/10/2006 11:09:03 AM   -> View verification complete.

• 11/10/2006 11:09:03 AM   -> Ensuring Indexes exist on database 'BayshoreColdwell'

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Applicati__7E6CC920' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Membershi__1367E606' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Paths__44FF419A' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Personali__4AB81AF0' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Personali__4D94879B' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Profile__286302EC' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Roles__31EC6D26' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_SchemaVer__08EA5793' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_Users__03317E3D' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_UsersInRo__35BCFE0A' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> The index 'PK__aspnet_WebEvent___5FB337D6' was successfully dropped.

• 11/10/2006 11:09:03 AM   -> Index verification complete.

• 11/10/2006 11:09:03 AM   -> Ensuring Foreign Keys exist on database 'BayshoreColdwell'

• 11/10/2006 11:09:03 AM   -> Foreign Key verification complete.

• 11/10/2006 11:09:03 AM   -> Ensuring Assemblies exist on database 'BayshoreColdwell'

• 11/10/2006 11:09:03 AM   -> Assembly verification complete.

• 11/10/2006 11:09:03 AM   -> Ensuring Stored Procedures exist on database 'BayshoreColdwell'

• 11/10/2006 11:09:04 AM   -> Stored Procedure verification complete.

• 11/10/2006 11:09:04 AM   -> Finished verifying databases on server.

• 11/10/2006 11:09:04 AM   -> The meta data conversion to the SQL Server has been completed.

Keith Chisarik

If anyone ever needs it, here is a package file equivalent to running aspnet_regsql.exe to create ASP.NET 2.0 security tables, views, and stored procs.

Keith Chisarik