September 4, 2024 at 10:35 am
First of all, I'm aware that it's better to perform an out of place upgrade to reduce downtime and lower the risk of any issues that could arise during an in place upgrade.
I'm having some headache on planning to upgrade my SQL server which are running on Windows 2012 to a newer version of OS. Problem lies on the size of the databases which the server is hosting. There are about 6 databases with a total size of about 10TB being hosted on that old server. Due to the size, the steps of taking it's backups will run into hours or days which I don't think business will allow such a long downtime on a production DB. Is there any better solution on this?
One which I could think of is to take the backups of the old servers, then once backup is completed, request for a downtime, & take a quick Tlog backups. Copy everything over to the new server and begin the restoration with the tlog backups. This could reduce the downtime the business have to take.
September 4, 2024 at 1:57 pm
Log shipping would be your friend here, use the previous full and log backups to make your new copy on the new server then setup LS to do the usual copy and restores for you, then when it is time to make the switch, it's a simple backup the tail of the log with norecovery on 2012, then copy and restore that log to the new machine.
September 4, 2024 at 4:52 pm
Another option is to take a backup - copy to new server and restore WITH NORECOVERY. Prior to cutover, perform a DIFFERENTIAL backup, copy the file to the new server - restore WITH NORECOVERY.
Perform a final tail-log backup on the old server - copy transaction log backups to new server and restore, again with NORECOVERY. Once all backups have been restored perform a RESTORE [database] WITH RECOVERY to bring the database online.
If you also disable log backups at the same time as performing the differential - then you only have a single tail-log backup of the database to restore, which will be much smaller and will be fairly quick to copy and restore.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 5, 2024 at 6:24 am
First of all, I'm aware that it's better to perform an out of place upgrade to reduce downtime and lower the risk of any issues that could arise during an in place upgrade.
I'm having some headache on planning to upgrade my SQL server which are running on Windows 2012 to a newer version of OS. Problem lies on the size of the databases which the server is hosting. There are about 6 databases with a total size of about 10TB being hosted on that old server. Due to the size, the steps of taking it's backups will run into hours or days which I don't think business will allow such a long downtime on a production DB. Is there any better solution on this?
One which I could think of is to take the backups of the old servers, then once backup is completed, request for a downtime, & take a quick Tlog backups. Copy everything over to the new server and begin the restoration with the tlog backups. This could reduce the downtime the business have to take.
Second to Ant-Green
Implement log shipping and during the cut off time you have to make sure no connection is hitting the DBs and then run the backup jobs & restore after that. Verify the last backup & restore file, run the command "REstore with Recovery".
Make sure you move all other database objects to new server(jobs, logins, mail configuration, Extended Events etc)
Only negative of side by side upgrade is all application connections strings have to be modified and you have to make sure to inform all other dependent team to open the connectivity to new server.
September 5, 2024 at 7:03 am
Only negative of side by side upgrade is all application connections strings have to be modified
Get your network people to create a load balancer with a separate connection string. The LB should initially point at your old server. Change your application connection strings to the LB connection string. When you move to the new server just get your network people to fail-over the LB to the new server. (This happens quickly unlike changing CNAME in DNS.) The next time you upgrade the same trick can be used without changing any connection strings.
September 6, 2024 at 4:46 pm
The most important question is: How much downtime do you get?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply