User profile for way0utwest

I don't mean to distract you or avoid the question, but there are a couple things in here that I want to point out. First, there's no magic on how to make changes. When we alter a table, there are really only a couple things that can be done in SQL Server to change a table. All of us are bound by the rules of the database engine for making changes, and we can't avoid those rules in terms of meta data only changes, or rewriting pages, or anything else. Second, the issues with large tables exist with small ones, but because of the scale or duration of changes (or resources), we don't notice them as much. We also can recover quicker, just because a rebuild/copy/etc, of 100 rows in inherently less disruptive that it is with 1,000,000,000 rows. Now, some of the issues with DACPAC changes are that you lack control in how you would prefer to make these changes. Similar issues can occur with SQL Compare, though I think Redgate does a better job of warning you and giving you a script to review. With SQL Change Automation (SCA), if you are tracking these changes in VS as migration scripts, you have complete control over how to make the change. We can't mitigate a bad decision, like locking a 1,000,000,000 row table to add and change data or rebuild an index. You might have valid reasons to make that change. We do suggest ways to make some changes, but at the time of migration script import, you need to ensure that you review the method of making the change. The advantage of SCA is that we give you complete control to decide how the change should be deployed. We don't necessarily give you the best method for your environment, since the requirements and restrictions vary by application, but we allow you to write the code that suits you. This means you need to understand how to make large table changes effectively in your world. There numerous ways to approach problems and you ought to research the best ways. A few ideas - https://sqlstudies.com/2016/07/14/altering-a-column-in-a-large-table-a-case-study/ - https://www.sqlservercentral.com/Forums/Topic851917-1550-1.aspx - https://www.red-gate.com/simple-talk/sql/database-administration/changing-data-types-large-tables-int-bigint-conundrum/ - https://serverfault.com/questions/36696/how-to-speed-up-adding-column-to-large-table-in-sql-server Note there are other methods, and you might need to experiment with how the different methods of making changes affect your system. Then incorporate those in your process and use them in your migration scripts. If you are using SQL Source Control, what I would recommend is that you review the scripts for ALTER TABLE changes and perhaps rewrite them. This won't flow through in your development environments necessarily, as the SCA cmdlets will still do what they do, but in smaller environments, some of these long or lengthy deployments usually don't matter if you rebuild the environment. However, for deployment to QA, Staging, Production, for the moment of the change, you'll want a real script to be used. In these cases, I'd use the new pre/post options for the table changes, and then comment out the alter in the regular script if you don't want it. / comments

I don't mean to distract you or avoid the question, but there are a couple things in here that I want to point out.First, there's no magic on how to make changes. When we alter a table, there are r...

0 votes

I would definitely investigate with whoever handles the networking and verify that things are setup correctly between the system, DNS, and AD. / comments

I would definitely investigate with whoever handles the networking and verify that things are setup correctly between the system, DNS, and AD.

0 votes

Are you adding the virtual server or the node itself? Can you disclose some of the names/setup? / comments

Are you adding the virtual server or the node itself? Can you disclose some of the names/setup?

0 votes

We don't have an option to do this, though that's interesting. If you post this on UserVoice, perhaps some others would vote for this. I'm not sure this is a high priority, but maybe if there is enough interest, it would get on the enhancement list. / comments

We don't have an option to do this, though that's interesting. If you post this on UserVoice, perhaps some others would vote for this. I'm not sure this is a high priority, but maybe if there is en...

0 votes

That makes sense, as I wasn't sure how often the schema in those tables changes. Synonyms are a hack, and I understand here this being an issue. Exclude might work, but in talking with the team, they said this sometimes still pulls in dependencies, so you might need to see if this works for you. If it doesn't, maybe the really hacky way to do this is import the migration script and then comment it out completely. This would allow the migration to get deployed with zero effect. You could even include a comment inside the comment that this was an external change from a third party upgrade/patch. It's not the best, but for now, we don't have a good way to ensuring we can walk a dependency tree while also supporting just partial project changes. There is some work being done on dependencies, but no idea what this will go through the Compare engine, which is what is used here in the SCA process. / comments

That makes sense, as I wasn't sure how often the schema in those tables changes. Synonyms are a hack, and I understand here this being an issue.Exclude might work, but in talking with the team, the...

0 votes

I just found this in the docs as well. I haven't tried this, but will attempt to get to it. This might help you: https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automation/advanced-project-manipulation/partial-projects / comments

I just found this in the docs as well. I haven't tried this, but will attempt to get to it. This might help you: https://documentation.red-gate.com/sca3/developing-databases-using-sql-change-automa...

0 votes

Good to hear. Hope that continues to work for you. / comments

Good to hear. Hope that continues to work for you.

0 votes

I'm not sure you can do this. Perhaps the best way to try this (not sure if this works and can't test right now), is to create a synonym and use that? It might resolve the reference, but I'm sure you don't necessarily want that to be the object deployed to production. Unfortunately, part of the issue is that we want to ensure that the dependencies are evaluated, which means that if you filter out the table, we still see a view dependency. Since we use SQL Compare, this limits some of the choices. There is an /EXCLUDE switch that might work, but is there a reason you don't want to source control the tables? If they aren't changed by you, there's no issue as there won't be changes deployed. / comments

I'm not sure you can do this. Perhaps the best way to try this (not sure if this works and can't test right now), is to create a synonym and use that? It might resolve the reference, but I'm sure y...

0 votes

Can you post some of the log? Are you using the cmdlet to create the release db, or just point to the target and generate an upgrade script? / comments

Can you post some of the log? Are you using the cmdlet to create the release db, or just point to the target and generate an upgrade script?

0 votes

The build process always builds a new database. That's the point of build, to verify all the SQL works without issues. The release step is what does the upgrade step. Make sure your target is set to an Azure database, otherwise there are sometimes items included from the on-premises SQL Server perspective that cause issues in Azure SQL DB. / comments

The build process always builds a new database. That's the point of build, to verify all the SQL works without issues. The release step is what does the upgrade step. Make sure your target is set t...

0 votes

How can we help you today?

way0utwest

Comments