User profile for Kendra_Little

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

0 votes

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

0 votes

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

0 votes

Hello, Thanks for your question. For stored procedures, they should all import/baseline successfully, but they will print a warning. This is because SQL Server has a feature called "deferred name resolution" for stored procedures: since stored procedures can create a table and then reference it in the same procedure, they don't require that objects exist when the stored procedure is created. Instead, name resolution in deferred until the first time the stored procedure is executed. Views and some functions are a different story, however. If a view references a table, SQL Server requires validation that the dependency exists, and if it can't find it (in the current database or another), it will give that error. Apologies if this comes off as pedantic! I know it may sound that way. I mention it only because it may mean that the scope of your issue is smaller than it might seem at first. Although the scope really will depend on how many views you have which have the cross-db references. [image] To get going, there are a few approaches you can take. I'm putting the simplest first. The final two options are more work but are great longer term. 1) If it turns out to be just a couple of views/functions and it's not important for you to have them in version control, you may simply filter them out and proceed without them. 2) You can create a schema-only copy of the dependent databases on your localdb instance -- potentially using SQL Compare to generate the script and deploy it. No data need be in dependent databases, SQL Server only needs to validate the schema. 3) You can restore databases or use clones, as described in this article. For the large data warehouse, you'd want something like our SQL Clone tool, an on-demand snapshot using SAN technology, or similar other data virtualization solution to make it fast -- OR if you have a scaled down copy of the DW which is used for development purposes, that could work, too. While this approach may seem like overkill, the reason it's wonderful in the long run is that you can immediately identify if indexes exist in the dependent database to support your queries, and if you've got a regular process to refresh the clones of dependent databases then you find out if there have been schema changes in them which impact you (even if that that depdendent database is not in version control or is managed by a team which is totally different than yours). 4) If you are also going to be adding the dependent databases into version control in the same solution, this article describe a "stubbing" approach to solving it. Hope this info helps! Kendra Edited to fix my weird formatting problem, then edited a second time to fix a bad link / comments

Hello,Thanks for your question. For stored procedures, they should all import/baseline successfully, but they will print a warning. This is because SQL Server has a feature called "deferred name r...

0 votes

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

0 votes

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

0 votes

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

0 votes

Hi Albert, To clarify, is your requirement to prevent that single migration from deploying *after* having created the Release Artifact? Kendra / comments

Hi Albert,To clarify, is your requirement to prevent that single migration from deploying *after* having created the Release Artifact?Kendra

0 votes

Hi there, What kind of problems are you encountering with large tables? Since we support several ways of working, it would be helpful to know the extension you're using to author the changes (SQL Change Automation for Visual Studio, SQL Change Automation for SSMS, or SQL Source Control), as well as the scenario where you're hitting the problem. Kendra / comments

Hi there,What kind of problems are you encountering with large tables? Since we support several ways of working, it would be helpful to know the extension you're using to author the changes (SQL Ch...

0 votes

Hi all, Apologies for the lack of resolution and /or recent updates on this issue. I'd like to do a quick recap of current existing options for lightning the impact of SQL Source Control on SSMS, then share some additional thoughts. First up, performance tweaks that may help readers As mentioned above, one option is to disable automatic refreshing in SQL Source Control. We also have two additional options which can be toggled that specifically may help speed up Object Explorer with some environments. You can either lessen the frequency or disable the rate at which changed objects are updated in Object Explorer. You can also test if the object locking feature may be what is causing the latency by disabling that feature. Finally, we have some tips for diagnosing/avoiding general latency here . Gathering more info I am curious to know if your use case specifically includes any of these, and if it might be related to the poor experience: Connecting to databases over a slow connection (whether cloud or not) Large static data tables Many encrypted objects Schemas with a large number of objects (this wouldn't just show in object explorer but would also cause a slow refresh time) We are looking to improve the experience with all of these, but it always helps to understand specific use cases. Plans and some background SQL Source Control is currently implemented as an x86 plugin in SQL Server Management Studio. This means that performance and functionality is always a balance -- and also that we don't have a simple way to resolve performance issues for every use case. But while this isn't a quick fix for us, this is definitely an area where we would like to improve. We are working on some new functionality which we plan to have available soon for a private preview for customer feedback. If you think you might be a good fit for the private preview, I'd love for you to get in touch with our team at databasedevops@red-gate.com. It would help if you could share a bit about your use case / any info related to the "gathering more info" section that you think might be suitable. Thanks very much for your feedback in this area. Cheers, Kendra ------ Kendra Little Redgate / comments

Hi all,Apologies for the lack of resolution and /or recent updates on this issue. I'd like to do a quick recap of current existing options for lightning the impact of SQL Source Control on SSMS, th...

0 votes

How can we help you today?

Kendra_Little

Comments