First of many questions I am sure.



I am adding the security tables required to use ASP.NET 2.0's membership API to a database using aspnet_regsql.exe.



I then used the import function (SQL Server) in DDT to import these tables along with my database, it gets the tables but not the views or stored procs (of which there are about 45). So of course when I deploy it blows off all that good stuff I need for my app.



So here is the rub, I want to use DDT for my web applications (website) database. My thought is that I will manually add the views and stored procs to the metadata and then be able to use the DDT exclusively from that point on. I can just use SQL Management Studio's "CREATE TO" functions to get the stored procs and view definitions.



Do you guys see any issue with this? It will be a decent amount of work up front, but then I am centralized and able to use both ASP.NET role/membership based security and Strataframe. That is my thinking.

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 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.