May 18, 2015 at 11:08 pm
Comments posted to this topic are about the item A Complex Database Project in Visual Studio SSDT
Hakim Ali
www.sqlzen.com
May 19, 2015 at 12:20 am
Hakim, Nicely explained and your stub approach clearly works well.
It is often assumed that a SSDT project is the same as a database, when actually multiple SSDT projects can be combined to create one physical DB. One SSDT can depend on another, using a reference for type of Same Server, Same database.
You can apply approach so that you have a logical grouping of objects per SSDT project, and then combine the SSDT projects into one DB. It can help work around circular dependencies between databases. Having your projects at a sub-database level means you can manage references at this lower level, rather than at the macro database level which all to often doesn't work as you know.
Good luck with your dependencies though!
May 19, 2015 at 1:21 am
So i'm kind of in a similar situation with a number of databases built up over several years with a ton of cross dependencies.
I'm thinking about having one solution per database where each would had references to the others via dacpac. And these references would be brought together by including a nuget package for each full DB schema. As long as when my dB changes I publish a new nuget package the others should continue to work provided they got the new package if the changes are needed to build.
What is the purpose for cutting down the schemas to only the relevant objects in your example? If it's just a dacpac does it really matter?
What do you think?
Good article. Not many people talk about anything difficult in ssdt...
May 19, 2015 at 7:09 am
shauntck 41953 (5/19/2015)
What is the purpose for cutting down the schemas to only the relevant objects in your example? If it's just a dacpac does it really matter?
I think the reason for using only the smaller subset of objects is because the article is specifically geared towards trying to handle the dependencies that the project requires to build successfully. It is merely to reduce some of the extraneous clutter that might cause confusion in the example.
Since these objects are noted as being so tightly coupled between the databases, you might not have a lot of changes to them... and your dacpacs would be rather static. So having it setup this way is an approach you can take. In the event that those objects are in flux and you are rebuilding the dacpacs frequently, you might be served better by including all the objects so you don't have to go through the exercise of keeping just the subset dacpac reference in sync.
That being said, we did something similar in our environment a while back due to the silo'd nature of our databases for a particular vertical. We used the subset dacpac approach as the referenced "shared" objects we going to change infrequently, or not at all.
shauntck 41953 (5/19/2015)
Good article. Not many people talk about anything difficult in ssdt...
Agreed. Most articles talk about the usage of database projects to keep the schema in sync and tout the advantages of source control. The downside is, that is the easy part, the more difficult portion is handling the data changes that might happen as you bring your database schema to current. This article on a way to handle more complicated inter-database reference dependencies is a breath of fresh air. 😀
May 19, 2015 at 7:15 am
Maybe I'm dense. I see how you resolved your reference problem but I don't see how you solved your dependency issue which is why those references are important. For example, if I've removed two columns from Table 1 on DB2 and my DB1 project doesn't know that because it has just a stub as a reference how do I resolve that? My DB1 project still thinks those two columns exist on DB2.
"Beliefs" get in the way of learning.
May 19, 2015 at 8:51 am
Darren Green-187877 (5/19/2015)
Hakim, Nicely explained and your stub approach clearly works well.
Thanks.
Hakim Ali
www.sqlzen.com
May 19, 2015 at 8:54 am
shauntck 41953 (5/19/2015)
What is the purpose for cutting down the schemas to only the relevant objects in your example? If it's just a dacpac does it really matter?
Somebody else also commented on this and that answer was correct and good. I'll add this: it helps eliminate objects that may have had further dependencies of their own. This lets me deploy with minimal work.
Hakim Ali
www.sqlzen.com
May 19, 2015 at 8:58 am
Robert Frasca (5/19/2015)
...I don't see how you solved your dependency issue which is why those references are important. For example, if I've removed two columns from Table 1 on DB2 and my DB1 project doesn't know that because it has just a stub as a reference how do I resolve that? My DB1 project still thinks those two columns exist on DB2.
If DB1 is still using those columns, then they cannot be removed or the compilation will throw errors. If DB1 is not using/referencing those columns at all, it does not need to know and does not care whether they exist.
Hakim Ali
www.sqlzen.com
May 19, 2015 at 8:59 am
SQL Muscle (5/19/2015)
This article on a way to handle more complicated inter-database reference dependencies is a breath of fresh air. 😀
Thank you.
Hakim Ali
www.sqlzen.com
May 19, 2015 at 10:16 am
We were in a similar situation, but I was able to use SQLPackage to extract a dacpac from all existing databases, then used that extracted dacpac as the DB reference needed to get the projects to compile properly. I could even generate a batch file to extract all of them in one pass so I could use them when creating the actual project.
May 19, 2015 at 10:28 am
My team uses SSDT for source control. We've tried resolving references between databases, but unfortunately they're just too interconnected. We wound up just ignoring the errors that the database projects generate and not including them in builds. It's sloppy, but it's good enough compared to either including all related databases or creating stub dacpacs. Ideally we'd be able to suppress the reference errors altogether.
May 19, 2015 at 10:31 am
Peter Schott (5/19/2015)
We were in a similar situation, but I was able to use SQLPackage to extract a dacpac from all existing databases, then used that extracted dacpac as the DB reference needed to get the projects to compile properly. I could even generate a batch file to extract all of them in one pass so I could use them when creating the actual project.
That's another interesting way to solve this problem. Nice.
Out of curiosity, are those dacpacs individually deployable? i.e. could you run just one of them to create a new database on a server that didn't have all the other databases? I'm guessing not...
Hakim Ali
www.sqlzen.com
May 19, 2015 at 10:34 am
john.leediii (5/19/2015)
Ideally we'd be able to suppress the reference errors altogether.
You can. Go into project properties, click on the "Build" tab, and where it says "Suppress Transact-SQL warnings:", put in the numbers of the error types that you want to suppress. So for instance if the majority of your errors are type SQL12345, put that in there. Then you can enable errors and warnings again, and all errors/warnings except those specific types that you suppressed will show up.
Hakim Ali
www.sqlzen.com
May 19, 2015 at 10:36 am
hakim.ali (5/19/2015)
Peter Schott (5/19/2015)
We were in a similar situation, but I was able to use SQLPackage to extract a dacpac from all existing databases, then used that extracted dacpac as the DB reference needed to get the projects to compile properly. I could even generate a batch file to extract all of them in one pass so I could use them when creating the actual project.That's another interesting way to solve this problem. Nice.
Out of curiosity, are those dacpacs individually deployable? i.e. could you run just one of them to create a new database on a server that didn't have all the other databases? I'm guessing not...
Never tried, but guessing that they wouldn't be. They worked well to get us past the initial stage of trying to get a project going and work well as DB references to publish the projects, but I never tried to push one individually.
May 19, 2015 at 10:40 am
Hakim, we did try suppressing the errors (71562,71502,72014,72045,70558,71558,71005,71561) - they still show up in the Error List, even if prefixed with SQL.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply