Maurice Pelchat

Resist to adding unnormalized tables or denormalizing tables. It's like doing bad renovations practices on a house. You are going to fall into a spiral of ever growing costs in all aspects that refers to handling this data. Implement "contract tables". A "contract table" hides forever the database implantation to the application. These are tables fed by dedicated functions, to turn data in a contract form. The "contract" is often a temporary denormalized version of the data. It also has extra non-data columns and validation result columns as a way to make the application communicate with the database (aside from regular data). As you can expect from this architecture, what executes the contract? A trigger.

Again the scope of the migration, which isn't specified here, leads to very different answers. This is the problem with these AI generated questions. They seem to come from a general information management textbook.

This statement seems to fit better for an SQL upgrade than for any other case. If a software vendor migrates its app and database to a newer version, it can be tested in a test environment. Suggesting to develop monitoring tools means that they actually don't have any. If they deploy a new app and database without having automated testing, this is not the time at migration to start to think about it. The new product isn't probably as ready as it should, and those tools should've been introduced at development and put at use to test the migration effect when in development, and with sufficient volume.

Good automation tools must show every SQL it runs with (error messages if any). I'm used to this type of challenge and it is worthy to ease diagnosis in forethought when designing automation. This is especially true when SQL code generates SQL code that it runs Generated SQL with its comment must be easily readable.

Database migration is a vague requirement. Do you mean to migrate to a newer SQL version, or to migrate an existing database to a newer database for a newer application. Or more difficult, migrate half of a database to a newer application while keeping both schemas to support parallel features in the newer and the older. Each challenge may deserve very different level of planning.

You should elaborate on that a bit more Jeff. I experienced once the case of a horribly fragmented index. Its defrag went long but results were speaking. If you say that bad queries hurt much more performance than fragmented indexes, I would say YES. If you documented your remark in some article, I would be very interested to read it. I worked for all my career for an ISP. Our customers have varying degre of occasional DBAs which were forgetting to update their statistics. So among our customers we always had one with performance problems. I realized that it was better to make and give them a script (YourSqlDba) that was doing index maintenance only when needed, update stats, dbcc checks and backups. Support exceptions greatly reduced.