March 12, 2014 at 12:15 am
Hi Everyone,
I came to know that using SQL Server database mirroring, we can perform the upgrades with minimal downtime.
How that is possible? Can anyone elaborate ?
I have done some homework reading about this and wanted to confirm on this.
Assume I have old server is SQL Server 2005 Enterprise Edition and new server is SQL Server 2008 Enterprise Edition.
Now, I am going to Setup High Safety mode without witness. I dont want witness because, we have risk Automatic failover and I don't want that happen in middle of upgrade.
Also make sure logins , orphan users are fixed, jobs ,linked servers are created on the new server.
once everything is sync , initiate a manual failover.
use master
go
alter database <dbname> set partner failover;
go
Is this the way we can perform upgrades with minimal downtime ?
Again, once the upgrade is done and verified from error log, do we remove db mirroring and point the applications to the new server?
Please correct me if I am wrong and am I missing anything here?
Thanks in Advance.
March 12, 2014 at 3:22 am
What is the size of the database? Check the availability of downtime howmany hrs? Based on this you can check other options too like attach/detach, logshipping.
Yes, it is possible. Refer following link for further detail
http://www.sqlservercentral.com/Forums/Topic1452542-1550-1.aspx
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 12, 2014 at 6:11 am
Hi mascot,
I wanted to test this on testing server whether it is possible or not. I wasn't aware of the fact that we can also do database upgrade using database mirroring which can reduce downtime. I usually use the traditional backup restore or dettach/attach using scripts.
Again, thanks for pointing me to the url. Really helpful.
March 12, 2014 at 10:52 am
Oracle_91 (3/12/2014)
Hi Everyone,I came to know that using SQL Server database mirroring, we can perform the upgrades with minimal downtime.
How that is possible? Can anyone elaborate ?
I have done some homework reading about this and wanted to confirm on this.
Assume I have old server is SQL Server 2005 Enterprise Edition and new server is SQL Server 2008 Enterprise Edition.
Now, I am going to Setup High Safety mode without witness. I dont want witness because, we have risk Automatic failover and I don't want that happen in middle of upgrade.
Also make sure logins , orphan users are fixed, jobs ,linked servers are created on the new server.
once everything is sync , initiate a manual failover.
use master
go
alter database <dbname> set partner failover;
go
Is this the way we can perform upgrades with minimal downtime ?
Again, once the upgrade is done and verified from error log, do we remove db mirroring and point the applications to the new server?
Please correct me if I am wrong and am I missing anything here?
Thanks in Advance.
As your using Enterprise edition and to keep initial overhead low, set the mirror session up as asynchronous 😉
When youre ready to upgrade, switch to synchronous mode with no witness and when the database is synchronised perform a manual failover 😎
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2014 at 10:58 am
Thank you Perry 🙂
March 12, 2014 at 11:00 pm
In this process no reverse gear i.e. once you failover to 2008 R2 you can't again use 2005.
Hence, advisable to take take full backup and if required log backup in case if it reqire for 2005.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 12, 2014 at 11:04 pm
Hi Mascot,
Yes. you are right and have taken the backup of both application and system database backups.
Thank you.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply