Sql-server – SQL Server Bulk Migration

Assuming you have everything properly configured, starting mirroring simply means the log rows for transactions not already at the mirror will be sent to the mirror database; once that has occurred the only further overhead would be writing to the mirror database. If the mirror is not very far behind the principal, then this step is generally very quick.

If you have mirroring configured in Synchronous (high safety) mode, that overhead might be enough to show delays in commits. Generally, transactions would be slightly slower since data must be written to both the primary log, and the mirror log before control is returned to the user.

If you have a maintenance window, it would be preferable to start mirroring during that window.

You can significantly lower the amount of downtime by running both current and new versions in parallel. Use something like Log Shipping, Always On Availability Groups or even replication to generate a copy of your database on a new instance that is running whatever newer version you want to upgrade to. I'd go with Log Shipping as it's the simplest, technically, and is available on any edition of SQL Server. Always On AG gets you closest in sync but take a bit more work to setup. Repl is kinda in between.

It may take a while for both databases to be "almost" in sync depending on how large the database is and your infrastructure but that is not as important since your system is online the whole time. Once you get to a steady state where all existing data from the original DB has moved to the new DB and you're left with only new transactions to move, then stop transactions on the original, let all data get moved to the new DB. Actual method/command depends on what approach you take but all are well documented on MSDN.

Verify all pending transactions have been pushed to the new DB then make the necessary changes so applications point to the new DB. Finally, run validation tests against the new DB to make sure everything works as it's supposed to (even if you've tested in your pre-prod/QA environment) and re-enable applications/transactions if all tested clear.

This puts the bulk of your downtime on whatever tests you need to run and you really should not skip that part. Cutting over from Log Shipping, AlwaysOn AG or replication is a matter of minutes, assuming no server, storage or network bottlenecks.