Comments
Sort by recent activity
Hi fforgoso , The best way to do this is to create multiple deployments. Each deployment can contain the exact combination of changes that you would like. There are a couple of different techniques you could use to selectively split migrations (of any kind) into multiple deployments. Are you currently using a practice of branching and merging? If so, that type of strategy helps you control the flow of changes into your release pipeline. An example is described here: https://littlekendra.com/2020/01/10/why-i-like-the-release-flow-branching-strategy-with-git-for-database-devops/ Hope this helps, Kendra / comments
Hi fforgoso ,The best way to do this is to create multiple deployments. Each deployment can contain the exact combination of changes that you would like.There are a couple of different techniques y...
Hi Ben, Some first places I would look... Is it possible some code was accidentally deployed to the model database and created that schema there? If you're using a dynamically named database to build (you're not specifying the name and it automatically creates one with a guid-like name), when SQL Server creates the db it will automatically have what's in model before any code is deployed and it could cause this issue. If that's not it, could some code have been added to a pre-deployment script that is doing this? Hope this helps, Kendra
/ comments
Hi Ben,Some first places I would look...Is it possible some code was accidentally deployed to the model database and created that schema there? If you're using a dynamically named database to build...
Hi @Melissa7 , If you upgrade the plugin past version 4.0.19288, you get more control over this behavior. Prior to this version the SCA extension would always open in SSMS. On this version and in later versions, the SCA extension will only open by default if you had it open when you last closed SSMS -- so if you close the tab when you are done using it, it won't automatically load when you next open SSMS. Hope this helps! Kendra / comments
Hi @Melissa7 ,If you upgrade the plugin past version 4.0.19288, you get more control over this behavior. Prior to this version the SCA extension would always open in SSMS. On this version and in la...
Hi @sgtwickool, Thanks for getting in touch with your concerns. I've emailed you some options for proceeding outside of this thread. Cheers, Kendra / comments
Hi @sgtwickool,Thanks for getting in touch with your concerns. I've emailed you some options for proceeding outside of this thread. Cheers,Kendra
Hi @Ajeet , Just to be clear for other readers, this question about external databases is quite different from the topic of "External Tables" which started this thread. SQL Compare gives you a variety of options for comparing -- you can compare databases to other databases, to scripts, to snapshots of databases, etc. Depending on how your access to these databases works, there should be some way to do the comparison. For instance if you can connect via a VPN you might simply be able to compare the databases directly in the SQL Compare GUI. If there are more restrictions on how you connection, you might do something like write out one database to a scripts folder and then compare that scripts folder to the database on the other end-- but you do have a variety of options. Hope this helps, Kendra / comments
Hi @Ajeet ,Just to be clear for other readers, this question about external databases is quite different from the topic of "External Tables" which started this thread. SQL Compare gives you a varie...
Hi Steve, I actually recommend starting with SCA with version control -- I think you'll find that things like this are simpler, because you can choose to ignore things like fillfactor in the project settings in either SQL Source Control (if you prefer a state-first approach) or SQL Change Automation (if you prefer a migrations-based approach). If you start this way, you'll need to re-adjust how you handle things like this when you add in the version control bit, so it may not end up reducing overall complexity by much. That being said, I believe you should be able to specify things like IgnoreFillFactor and other SQLCompare options as additional -SQLCompareOptions when you create the release artifact. I believe the reason this isn't called out in the documentation is that most people don't need to do it (as it's handled by the source control project settings).
Hope this helps!
Kendra
/ comments
Hi Steve,I actually recommend starting with SCA with version control -- I think you'll find that things like this are simpler, because you can choose to ignore things like fillfactor in the project...
Hi tee, I suspect that the error you are seeing comes from a view or views which reference the linked server. A quick summary of why this is happening: When running a verify in SCA in Visual Studio, or when running a build with SCA, it ensures that all objects can be created in SQL Server successfully. Stored procedures enjoy a feature called "deferred name resolution", which means that the stored procedure can reference objects that don't exist at the time the procedure is created. Views and some limited types of functions don't have this functionality, so when SQL Server tries to create those objects it tries to resolve the items it refers to exist / check that the linked server is there. There are a couple of ways you can resolve this:
Create a synonym that points to the linked server resource, and modify the views and any impacted functions to refer to the synonym. This is a great long-term solution, because possibly you do want to validate code that goes across the linked server in other environments. You can "re-point" the synonym in each environment to whatever you want, and you get a lot of flexibility. Synonyms DO have deferred name resolution, so this also means you don't have to actually create the remote resource if you want.
Filter out the views / functions referencing the linked server from the project.
Hope this helps! Kendra / comments
Hi tee,I suspect that the error you are seeing comes from a view or views which reference the linked server. A quick summary of why this is happening: When running a verify in SCA in Visual Studio,...
Pre- and post- scripts are very similar to migration scripts, they are just executed each time you run them. I think this would be very similar to your current script, you would just have a "guard clause" detecting the conditions on which it would run. You can have multiple pre- or post- scripts, each with an individual name, to help you organize and manage the code long term. Depending on the complexity of what you are doing, that might or might not be useful. / comments
Pre- and post- scripts are very similar to migration scripts, they are just executed each time you run them. I think this would be very similar to your current script, you would just have a "guard ...
Hi there, Pre- and post-deployment scripts are run on each deployment. Is it possible for you to write the seed-date-reload script in a way that it detects if you want to reload the seed data (in case there are some exceptions when you would not want to do so in a deployment), and then put that into a pre- or post- script? Regarding the "delete" operation, I'm not sure what you mean in option one about the risk of loading seed data multiple times? Are you doing something like a truncate before you do the bulk insert? If you are doing an actual DELETE operation, is there a reason you couldn't TRUNCATE? I ask because DELETE logs row by row, which can be slow. TRUNCATE is logged but much faster as it just unhooks the allocated data in the background. Just curious if you could optimize this in some way, no matter which way you are running it. Kendra / comments
Hi there,Pre- and post-deployment scripts are run on each deployment. Is it possible for you to write the seed-date-reload script in a way that it detects if you want to reload the seed data (in ca...