Circular Dependencies new to Composite Projects

Question

We previously used the Data Dude tools (currently using Sql Source Control) with a large enterprise collection of databases. We had a ton of partial projects due to circular dependencies from when the system was originally created. We are evaluating the new SSDT now that many of the features we were wanting exist. However, it is a large project for us to convert back to this process and teach a team how to use the new tools.

I was the resident expert on the old dbproj and partial project technology. However, this new composite project thing has me baffled.

I have few questions:

1) What is the difference between a composite and partial project way of doing things?

2) How do you create a composite project?

3) What are key differences from a deployment and design standpoint that I need to be aware of?

I have read several different articles and am still not clear on how to build and implement a composite project. The old partial projects were not actually partial projects for us. They were normal dbproj with importing a partial project that were just links to the object sql files. This was great because if the schema changed on the object it would break all of the other items using that partial project.

 

Answer 1

Hi Marty,

I'll answer your second question first because that may provide the understanding to enable you to answer the other questions. 

This article: Walkthrough: Partition a Database Project by Using Composite Projects explains how to create a composite project. It applies to dbproj projects but is still relevant for sqlproj (aka SSDT) projects (I'm afraid I do not know of an updated version of the article for SSDT).

Also, in case it helps, I have written about WHY one would use composite projects here which may or may not be useful.

Hope all that helps somewhat. Take a look and if you have more questions do follow up here.

regards
Jamie

---

ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 2

I have read the walk through several times. And I am hoping and begging that MS has not built something expecting us to import a database with thousands of objects only to say we must dissect those thousands of objects from the single database into many projects.

We used partial projects due to circular dependencies. Based on what I am seeing with composite projects I have to create shared (composite) projects for every object type that is used.

Meaning that if a table is used that also has a udt that I will now need to have 2 projects. 1 for tables and 1 for udts?

Which means if that is the case for one database I could end up with 5 or 6 separate projects?

Am I reading that correctly? 

Additionally after going back through the walk through I was going to do this step by step in Visual Studio. I then realized that article doesn't make since. It references composite projects but uses the old dbproj selection for sql server 2008. Sorry I notice you state this above but how does it apply, the menu options or nothing are the same for the dbproj as they are for ssdt?

Answer 3

We have the same issue with circular dependencies. So each database is separate into two projects. One contains all the components that do not have a dependency on an separate database: tables, views, SPs, etc, which we call "Primary". Then another project contains those components that do have a dependency, which we call "External". The External project has a reference to its Primary project of type "Same Server, Same Database". When we deploy, we say "Include Composite Objects".

Any other database that uses another database puts those components in an External project and references the Primary project of the database which it needs.

Answer 4

I have read the walk through several times. And I am hoping and begging that MS has not built something expecting us to import a database with thousands of objects only to say we must dissect those thousands of objects from the single database into many projects.

We used partial projects due to circular dependencies. Based on what I am seeing with composite projects I have to create shared (composite) projects for every object type that is used.

You don't have to create a project for each object type, no.

Meaning that if a table is used that also has a udt that I will now need to have 2 projects. 1 for tables and 1 for udts?

Which means if that is the case for one database I could end up with 5 or 6 separate projects?

In theory one might have to have that many projects but I would find that highly unlikely - it depends on the code itself and the nature of the circular references. I once had to move a solution containing about 10000 objects across 3 databases into VS2010 DB Projects and I didn't need more than two projects per DB. Your mileage may/will vary.

Am I reading that correctly? 

Additionally after going back through the walk through I was going to do this step by step in Visual Studio. I then realized that article doesn't make since. It references composite projects but uses the old dbproj selection for sql server 2008. Sorry I notice you state this above but how does it apply, the menu options or nothing are the same for the dbproj as they are for ssdt?

I can't remember about the menu options in .dbproj but I know of one thing that SSDT does that makes it significantly easier. In .dbproj you had to state whether you were specifying a 2-part, 3-part or 4-part name. SSDT simply gives you three plain English options:

• Same database, same server
• Different database, same server
• Different database, different server

Which I find a lot more useful.

JT

---

ObjectStorageHelper<T> – A WinRT utility for Windows 8 | http://sqlblog.com/blogs/jamie_thomson/ | @jamiet | About me

Answer 5

Richard I am not sure your scenario is a true representation of ours in regards to circular dependencies.

We have objects in DB1 that are referenced by DB2 

We then have objects in DB1 that reference DB2.

Some of those objects overlap.

e.g.
Databases:
DB1
DB2
DB3

DB1.tblCustomer is referenced by DB2.vwAllCustomers
DB1.vwAllPurchases references DB1.tblCustomer & DB2.tblProducts & DB3.LoyaltyPoints
DB3.sp_GetLoyaltyProgramDetails references DB1.tblCustomer & DB1.tblAddress & DB3.tblLoyaltyPoints

With all of these you still have udt's and udf's as well.

How do you handle these types of scenarios. And how does the primary project keep a reference to the objects that are in your external project when you only do a reference from external to primary?

Thanks again for the help! As much as I disliked partial projects I now see how good they were.

Answer 6

DB1.Primary does not have a reference to DB1.External because it doesn't need it. x.External references x.Primary as a composite reference. And we deploy DB1.External, which will include DB1.Primary with the "IncludeComposite" flag during deployment. Or you can deploy them separately.

In your case

DB1.tblCustomer would be in DB1.Primary, DB2.vwAllCustomers would be in DB2.External and reference DB1.Primary

DB1.vwAllPurchases would be in DB1.External and references DB2.Primary and DB3.Primary

Now, if a stored procedure in DB1 calls a view in DB2 which references DB3, this does break the model that an External can only reference a Primary, which is how we are breaking the dependency chain.

Answer 7

Richard did you all use partial projects previously or is this your first adventure into the MS db solution/project world?

I have read your response several times and I believe I understand what you are saying. However, it appears your (and many others) situation is not as complicated as ours. I am not sure my example is a correct representation of our challenge.

So based on your explanation (primary vs external).

When you say " do not have a dependency on an separate database:" does that mean those items do not depend on any other database? Or does it mean that no other database depends on them?

Here is a script to generate 2 databases I think represents our situation and curious how you would put these two in a solution where all warnings are treated as errors.

USE [master]
GO
/****** Object:  Database [DB1]    Script Date: 1/7/2013 4:40:15 PM ******/
CREATE DATABASE [DB1]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DB1', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB1.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DB1_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB1_log.ldf' , SIZE = 4096KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [DB1] SET COMPATIBILITY_LEVEL = 110
GO

USE [DB1]
GO
/****** Object:  UserDefinedDataType [dbo].[UDT1_DB1]    Script Date: 1/7/2013 4:40:15 PM ******/
CREATE TYPE [dbo].[UDT1_DB1] FROM [int] NOT NULL
GO
/****** Object:  UserDefinedDataType [dbo].[UDT2_DB1]    Script Date: 1/7/2013 4:40:15 PM ******/
CREATE TYPE [dbo].[UDT2_DB1] FROM [int] NOT NULL
GO


GO
/****** Object:  Table [dbo].[TB1]    Script Date: 1/7/2013 4:40:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB1](
[TB1_DB1_Col1] [int] NULL,
[TB1_DB1_Col2] [int] NULL,
[TB1_DB1_Col3] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TB2]    Script Date: 1/7/2013 4:40:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB2](
[TB2_DB1_Col1] [int] NULL,
[TB2_DB1_Col2] [int] NULL,
[TB2_DB1_Col3] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TB3]    Script Date: 1/7/2013 4:40:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB3](
[TB3_DB1_Col1] [int] NULL,
[TB3_DB1_Col2] [int] NULL,
[TB3_DB1_Col3] [int] NULL
) ON [PRIMARY]

GO





USE [master]
GO
/****** Object:  Database [DB2]    Script Date: 1/7/2013 4:41:28 PM ******/
CREATE DATABASE [DB2]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'DB2', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB2.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'DB2_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\DB2_log.ldf' , SIZE = 4096KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO




USE [DB2]
GO
/****** Object:  UserDefinedDataType [dbo].[UDT1_DB2]    Script Date: 1/7/2013 4:41:29 PM ******/
CREATE TYPE [dbo].[UDT1_DB2] FROM [int] NOT NULL
GO
/****** Object:  UserDefinedDataType [dbo].[UDT2_DB2]    Script Date: 1/7/2013 4:41:29 PM ******/
CREATE TYPE [dbo].[UDT2_DB2] FROM [int] NOT NULL
GO

/****** Object:  Table [dbo].[TB1]    Script Date: 1/7/2013 4:41:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB1](
[TB1_DB2_Col1] [int] NULL,
[TB1_DB2_Col2] [int] NULL,
[TB1_DB2_Col3] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TB2]    Script Date: 1/7/2013 4:41:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB2](
[TB2_DB2_Col1] [int] NULL,
[TB2_DB2_Col2] [int] NULL,
[TB2_DB2_Col3] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[TB3]    Script Date: 1/7/2013 4:41:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TB3](
[TB3_DB2_Col1] [int] NULL,
[TB3_DB2_Col2] [int] NULL,
[TB3_DB2_Col3] [int] NULL
) ON [PRIMARY]

GO









USE [db2]

/****** Object:  View [dbo].[VW1_DB2]    Script Date: 1/7/2013 4:41:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[VW1_DB2]
AS
SELECT  t1.TB1_DB2_Col1, t1.TB1_DB2_Col2, t2.TB2_DB2_Col3    
FROM    dbo.TB1 AS t1
INNER JOIN dbo.tb2 AS t2 ON t2.TB2_DB2_Col1 = TB1_DB2_Col1

GO

/****** Object:  StoredProcedure [dbo].[usp_DB2CallingDB1AndSelf]    Script Date: 1/7/2013 4:41:29 PM ******/


CREATE PROCEDURE [dbo].[usp_DB2CallingDB1AndSelf]

AS
BEGIN

SELECT
*
FROM dbo.TB2 AS t2
INNER JOIN dbo.VW1_DB2 AS v1 ON v1.tb1_db2_col2 = t2.tb2_db2_col2
LEFT OUTER JOIN DB1.dbo.tb1 AS db1t1 ON db1t1.TB1_DB1_Col2 = t2.tb2_db2_col2

END

GO

USE [db1]
go


/****** Object:  View [dbo].[VW1_DB1]    Script Date: 1/7/2013 4:40:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW [dbo].[VW1_DB1]
AS
SELECT  t1.TB1_DB1_Col1, t1.TB1_DB1_Col2, t2.TB2_DB1_Col3    
FROM    dbo.TB1 AS t1
INNER JOIN dbo.tb2 AS t2 ON t2.TB2_DB1_Col1 = TB1_DB1_Col1
INNER JOIN DB2.dbo.VW1_DB2 AS vd ON vd.TB1_DB2_Col2 = t1.TB1_DB1_Col2

GO


/****** Object:  StoredProcedure [dbo].[usp_DB1GetSelfViewHittingDB2]    Script Date: 1/7/2013 4:40:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_DB1GetSelfViewHittingDB2]

AS
BEGIN

SELECT
*
FROM dbo.TB3 AS t3
INNER JOIN dbo.VW1_DB1 AS v1 ON v1.TB2_DB1_Col3 = t3.TB3_DB1_Col3

END