February 23, 2016 at 10:28 am
Hi all,
I've been presented with the challenge of handing a migration from our current SQL 2012 Web (64 bit) edition (Windows Server 2012 R2) to our new 2014 Enterprise (64 bit) running an AlwaysOn Availability Group (Windows Server 2012 R2). The big caveat of the challenge is that it is to be "zero downtime". There are three relatively small DBs that need to move (~75GB, 8GB and 5GB).
I was wondering if anyone else has been faced with a similar challenge?... and if so, how did they handle it?
I was hoping to configure log shipping from the 2012 instance to both 2014 SQL server instances, but I imagine I'll have to tear down the AG in order to do this. Then after all logs are applied to both instances, rebuild the AG. Does this approach work in theory?... or am I underestimating the effort associated with this?
Thanks in advance.
February 23, 2016 at 10:44 am
techtnt-630432 (2/23/2016)
Hi all,I've been presented with the challenge of handing a migration from our current SQL 2012 Web edition to our new 2014 Enterprise running an AlwaysOn Availability Group. The big caveat of the challenge is that it is to be "zero downtime". There are three relatively small DBs that need to move (~75GB, 8GB and 5GB).
I was wondering if anyone else has been faced with a similar challenge?... and if so, how did they handle it?
I was hoping to configure log shipping from the 2012 instance to both 2014 SQL server instances, but I imagine I'll have to tear down the AG in order to do this. Then after all logs are applied to both instances, rebuild the AG. Does this approach work in theory?... or am I underestimating the effort associated with this?
Thanks in advance.
1) Do you or your company have SUBSTANTIAL training/experience with AGs? If not you are running down a path laden with caveats, gotchas, provisos, limitations, etc. All kinds of ways you can mess things up to varying degrees.
2) There is no such thing as ZERO downtime to my knowledge. Especially using log shipping, which may be your only option since it is Web Edition. I have no idea about it's feature set. If it has database mirroring then synchronous mirroring will offer the fastest failover time).
3) I don't know that you can even configure a database for always on unless it is read-write, which it cannot be if you are log shipping or mirroring to it. You CAN log ship to multiple copies though which should allow for a fast config of the AG for the databases after you make the switchover since they will already be in sync.
4) You are dealing with viability-of-the-company stuff here. Consider hiring a pro to help you evaluate your needs, formulate a plan (forward and rollback), execute, establish monitoring, etc. I HIGHLY recommend Allan Hirt.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 24, 2016 at 4:57 am
techtnt-630432 (2/23/2016)
Hi all,I've been presented with the challenge of handing a migration from our current SQL 2012 Web (64 bit) edition (Windows Server 2012 R2) to our new 2014 Enterprise (64 bit) running an AlwaysOn Availability Group (Windows Server 2012 R2). The big caveat of the challenge is that it is to be "zero downtime". There are three relatively small DBs that need to move (~75GB, 8GB and 5GB).
I was hoping to configure log shipping from the 2012 instance to both 2014 SQL server instances, but I imagine I'll have to tear down the AG in order to do this.
Then after all logs are applied to both instances, rebuild the AG. Does this approach work in theory?... or am I underestimating the effort associated with this?
You're always going to have some sort of downtime here, not least when you reconfigure connections from one point to another, you can minimise the downtime which I think is what you really mean.
What leads you to believe you'll need to tear down the existing AG, this is not the case?
Dealing with the above, 2012 Web edition of SQL Server will only support Log shipping as a possible route, mirroring is only supported as a witness.
Given this you could log ship from 2012 to 2014 in restore mode. Once you're ready to switch it's a matter of taking a tail log backup on the 2012 database and restoring all logs to the 2014 database with recovery, the database is then online. You may then introduce the new database into the availability group.
To be honest, the database sizes you list above aren't exactly large, you should be able to just pick them up and move them (backup and restore) within a very short window of maintenance.
As Kevin has said you should really consider hiring a professional to help you achieve this.
TheSQLGuru (2/23/2016)
since it is Web Edition. I have no idea about it's feature set. If it has database mirroring then synchronous mirroring will offer the fastest failover time).
Web edition only supports database mirroring as a witness, so not an option here.
TheSQLGuru (2/23/2016)
Consider hiring a pro to help you evaluate your needs, formulate a plan (forward and rollback), execute, establish monitoring, etc. I HIGHLY recommend Allan Hirt.
I'm also available 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 24, 2016 at 8:34 am
Thank you both for your feedback.
To be more precise, yes, this is actually a "minimal" downtime solution and not a "zero downtime". (a couple minutes vs. 20+/- minutes)
I was more concerned with just the ability to do log shipping across versions (2012 Web --> 2014) and making sure I wasn't leaving any options off the table. (i.e. simple backup and restore, etc.)
In the meantime, I've got this working in our lab environment, but I wanted to be sure I wasn't missing a more obvious path without the moving parts. (removing and adding back DB to the AG, tail-log backup, etc.)
I appreciate the feedback.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply