New SQL server built but no down time allowed
-
Just throwing this one out there...no idea too preposterous
I am in a situation of migrating a series of Server 2000/SQL 2000 boxes to bigger, better, stronger, faster etc. No significant software configuration changes between the boxes.
I get to a point, when all is tested and blessed with Holy Water then it is time for: DOWNTIME.
Down the old server, get fresh backups, do fresh database restores on the new box, rename the new server to the old name, update the DNS, bring it on live and see what happens. Generally it is a trusted and tried method that works for me.
We are not clustered. One box gives way to one new box. We do have replication going on.
Any ideas on how to pull this off in real time (read: no DOWNTIME) without tons of replication changes? Is there an easy way to mirror databases between two servers? Any thoughts on how you have done it in the past that worked pretty well?
Thanks,
Studdy
-
I can't imagine of having 0 downtime for migrating from server A to server B. There are ways in which we could pull disk out from the array and have a new one rebuild, but that still takes time.
mom
-
I wouldn't rename the server after bringing it online. Too many headaches with that. Just add a cname alias for the old server in DNS and point it to the new server.
The only way to change servers without any downtime is going to require you to change your replication.
I've done tons of migrations and downtime was always allowed. The amount of downtime was always estimated and squeezed as close as possible though.
Your friendly High-Tech Janitor... 🙂
-
"No Downtime" is not possible since only one of the servers can be up and any one time with the same name, the DNS and server rename are required and these will require a re-boot.
To avoid confusion regarding the meaning of "server", I will use "machine" for the "computer" and "instance" for the SQL Server instances.
You could reduce the duration of the down time by using the standby server technique described in Books OnLine.
Before the downtime:
1. Insure recovery mode is not simple
2. Transaction log backup are running.
3. Full DB backup on old
4. Restore database with NORECOVERY to new.
In the interm:
1. Keep apply any transaction logs with NORECOVERY to new.
Downtime Start:
1. Make SQL Server non-available to users
2. Run final transaction log backup and copy to new machine.
3. Shutdown old machine
4. Apply final transaction log with RECOVERY to new
5. Make DNS Changes
6. Rename new machine to old name
7. Reboot new machine
8. After restart, to SQL Server, drop and add instance name using sp_dropserver and then sp_addserver and then recreate any remote logins.
Done.
SQL = Scarcely Qualifies as a Language
-
So what I am hearing, and PLEASE correct me if I am wrong, is this:
If I am to the point where I can restore last nights full backups (meaning my new server is only hours behind) and the transaction logs were backed up and truncated as they should have been during last nights backups....
Then I am only a backup/restore of transaction logs away from being real time. (No need to restore the data itself.)
This would seriously cut my downtime if this is correct. Thats what you are saying, right?
Is my logic sound?
Studdy
-
What do you mean the logs were backed up and truncated? Please don't tell me you truncate the logs every time you back them up.
From Microsoft and the BOL...after truncating the log, you need to immediately do a full backup.
Let's say you do:
Full backup
Tlog1
Truncate log1
Tlog2
Truncate log2
Then for a restore all you can do is restore the full backup and tlog1. Tlog2 is not part of the previous set.
But otherwise, yes....
Apply your full backup using WITH NORECOVERY
Apply transaction logs WITH NORECOVERY until you are ready to go live.
Backup the original servers log and apply it to the standby server using WITH RECOVERY.
Shut down the original and start with the standby.
BTW-you can also put the original in read-only mode while you complete the new server. That will let your users continue to have access to the data, but not change anything.
-SQLBill
-
Why in the world can't you schedule some off hours maintenance/down time? Some sort of SLA?
Joe
-
This 'no downtime' brings up another question....what service pack level is your SQL Server at? Applying a service pack (and some hotfixes) requires a reboot (downtime).
-SQLBill
-
Earlier I mentioned log truncation. A full backup does this - not me. I was just thinking out loud at what all happens....
I have the latest builds on both boxes. Reboots are not a big issue. I just don't want to spend all night at work on this one.
And then, the next rebuild is the Server 2000, SQL 2000, Internet Commerce production box and it REALLY can't stand long down time as the company losses would mount up. So I was just trying to work through the fastest way possible to do things and had a few less critical boxes to get going first.
Your insight has been most valuable.
-
Assuming reboots/minimal off hours downtime isn't a big deal the biggest question would be the amount of data that you need to move from server A to server B.
Moving a 10GB database is a very different animal than moving a 500GB database. How much data do you need to move?
Joe
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply