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?
September 24, 2024 at 8:08 am
Thanks for all the reply.
Just 1 more thing to clarify, if I were to upgrade my database from SQL 2012 to SQL 2019 & I want to retain all my jobs, users, server roles, etc. I will need to backup all my database including the system database & restored it into SQL 2019 right?
September 24, 2024 at 9:32 am
Thanks for all the reply.
Just 1 more thing to clarify, if I were to upgrade my database from SQL 2012 to SQL 2019 & I want to retain all my jobs, users, server roles, etc. I will need to backup all my database including the system database & restored it into SQL 2019 right?
No, definitely not. The best way to achieve this is to script out everything and execute it on the new server. Microsoft provides a stored procedure to transfer logins with passwords to new servers.
October 1, 2024 at 6:47 am
JasonO wrote:Thanks for all the reply.
Just 1 more thing to clarify, if I were to upgrade my database from SQL 2012 to SQL 2019 & I want to retain all my jobs, users, server roles, etc. I will need to backup all my database including the system database & restored it into SQL 2019 right?
No, definitely not. The best way to achieve this is to script out everything and execute it on the new server. Microsoft provides a stored procedure to transfer logins with passwords to new servers.
Hi Deubel, could you elaborate further on what sort of script i need to run?
So does this means I do not need to backups any of my system database from the old server over to the new server?
October 1, 2024 at 6:57 am
No, do not restore them from 2012 to 2019.
To transfer logins you can search for “sp_help_revlogin” this will generate you a script to move logins.
The best thing though is get DBATools and use that to migrate the objects you need.
You can do most of it in one hit with “start-dbamigration” but if you want to be selective you can use the respective functions in the module to do the individual pieces you need.
Otherwise, you need to go to each object you wish to transfer and right click then script it, and recreate that object on the new server.
October 1, 2024 at 7:08 am
Hi Deubel, could you elaborate further on what sort of script i need to run?
So does this means I do not need to backups any of my system database from the old server over to the new server?
Hi Jason,
just go to sql server agent -> jobs. That will display all jobs in the Object Explorer Details window. Select all Jobs, right click -> Script to.
That will script out every job.
October 14, 2024 at 7:28 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply