I'm planning to move an on-premises SQL Server Web Edition instance to a new server. The main database is about 30 GB and supports a web application with moderate read and write activity. There are roughly 80 SQL Agent and background jobs, but those can be stopped during the migration window. The goal is to keep downtime to a few seconds or, at most, a couple of minutes.
Because Web Edition doesn't support Always On availability groups, I'm considering transactional replication, log shipping, or a backup-and-restore process followed by a tail-log backup. Replication seems like it might offer the shortest cutover, but I haven't used it for a full server migration before.
For anyone who has handled a similar production move: is replication worth the additional setup for a one-time migration, how difficult is the final cutover, and what unexpected problems should I plan for?
4 Answers
Log shipping has worked well for several migrations like this and is considerably less involved than setting up replication. Keep shipping transaction logs to the new server, perform a final log backup during the outage, restore it with recovery, stop the old application connections, and switch the application over. Do a practice cutover first so you know the restore sequence and timing.
The safest approach is usually to schedule a maintenance window during a low-traffic period. Stop the application and jobs, take the final backup, restore it on the new server, validate it, and then repoint the application. Beforehand, you can copy the bulk of the backup or database files and rehearse the entire process in a test environment so the final outage is limited to the last synchronization and cutover.
For a one-time move, transactional replication is usually more complexity than it’s worth. Log shipping is a reasonable option if you truly need a short outage, but for a 30 GB database, a scripted full backup, restore, and final tail-log restore may be simpler and faster than expected. Tools such as dbatools can help automate the process, but make sure you migrate server-level objects too, especially logins and permissions.
What makes replication a poor fit here? I’m trying to understand the trade-off rather than just choosing the simplest option.
The cutover can feel almost seamless if you prepare and rehearse it, but the database restore is only part of the job. Check application connection strings, DNS records, cached DNS entries, firewalls, TLS certificates, permissions, stored procedures, and any infrequently used services that connect to the database. Also recreate or validate SQL Agent jobs, linked servers, credentials, and other server-level objects before switching production traffic.
It’s worth comparing the effort and risk of weeks of replication setup and testing against a clearly planned maintenance window. A short, scheduled outage is often much safer than trying to achieve theoretical zero downtime with a complicated migration path.
Pre-copying the large files or taking an initial backup ahead of time can shorten the maintenance window. The final pass only has to transfer the changes made since that initial copy, assuming the process is tested carefully.
The client is pushing for minimal downtime, so I’m trying to find a way to reduce the outage while still keeping the process reliable.