So we're looking at using VS SSDT projects to maintain some code in source control, but it's been a LONG time since I used this. Normally use Red Gate Source Control, but for this effort it's not an option. So my question, is it possible to only deploy selected items? We have several environments we move code between as projects progress up to Production, and we'll have parallel efforts where code has to be moved up incrementally. So for example Project A may have 2 tables and three procedures and Project B will have 3 different tables and 2 different procedures. I need a way to promote changes for Project A and B independent of each other... but using the dacpac deployment model I'm not sure this is possible - it's all or none.
Can someone give any pointers? This has always been one big reason I don't use this method for source controlling SQL code, but maybe there's a way now -- this is my first time to do this with VS 2019.
Thanks for any advise.
As far as I am aware, and if I am understanding what you are saying, you can't do that. I am PRETTY sure that SSDT would push across all of the solution related changes.
What I would recommend is that if Project A and Project B need to be deployed independent of each other, they should be in different solutions. At least that is how I develop software. If Project A and Project B are related but independent, then they are their own solution. If Project A relies on Project B, they are part of the same solution, but in this case, when I deploy, I deploy changes to both A and B at the same time.
An alternate approach that you could do (which is how we do it where I work currently) is to keep the SQL code out of the solution. May not be as ideal as it can be harder to find objects that use those SQL objects, but it allows you to re-use objects without needing to update every solution that uses them
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
You could use the compare utility within SSDT to move only parts of a deployment. Automating that is likely to be somewhat difficult.
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
The Scary DBA Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
You could split the database into several projects, and only deploy the ones you need. You can link the projects together using same database references.
So DBProjectA would contain the 2 tables and 3 procs from project A, and DBProjectB would contain the 3 tables and 2 procs from project B. You could deploy either independently of the other, provided you keep the 'drop objects in target but not in source' checkbox unchecked.
If you need to reference objects from another project, that's when same database references come in. So, say project C includes 4 tables, and a stored proc that references some of them as well as a table from project B. You would then set up DBProjectC, with a reference of type 'same database' to DBProjectB. Any deployment of DBProjectC would then include everything from DBProjectB as well (as it should, otherwise you couldn't guarantee that everything still works). If you wanted a way to deploy the full database you could create a DBProjectAll project, that had references to all of the mini projects. This wouldn't need to hold any objects itself, but would deploy everything together.
Viewing 4 posts - 1 through 3 (of 3 total)